Please answer Question #2, Data tables is below, please show all of your work:
Sunrise Hotels (D) January, 2017. Previously, you estimated a cost function for the property and completed a CVP analysis. Your supervisor has asked you to prepare a 2018 operating budget . Analysis of historical data suggests the following monthly room demand: Months Monthly Demand January and February 5% of annual demand. March-May, September October 8% of annual demand. June-August 12% of annual demand. November and December 7% of annual demand. Revenues. In Sunrise (B), you forecast the average daily rate and sales volume for 2018. Assume the room rate is constant throughout the year. Costs. Assume now that you ran regressions for each of the separate expense categories in your data file. Increasing the variable cost estimates by 4% (an assumption given in the B case) results in the cost formulas provided in the table below. Item Cost Equation Front desk $7,800 Housekeeping $4.30/rental Rentals Supplies $1.35/rental * Rentals Energy $4,700 An alternative to regression is the industrial engineering method of cost estimation. Your regional manager provides you with the following insights: The front desk is staffed twenty-four hours per day. Average wages of front desk staff are $8.00/hour. Your annual salary, as general manager, is $27,000. Housekeeping should clean a room in 20 minutes. However, preparing the laundry cart and taking linens to the laundry room mean that, on average, only two rooms per hour can be cleaned. The hourly wage rate for housekeepers is expected be $7.50. A laundry worker works 20 hours per week at $7.50/hour. Room Amenities. Rooms are stocked with two bars of soap and a bottle of shampoo. Soap costs $0.14 a bar and shampoo costs $0.25 per bottle. At the beginning of each month, you want 25% of next month's requirements on hand. Beginning inventory requirements are met on 1/1. Assume January 2018 room sales will be 900 rentals. Other Supplies. Paint, light bulbs, coffee, etc. average about $1,000 per month. Required 1. Prepare a monthly operating budget for 2018. Explain why you chose the regression or industrial engineering method to estimate costs. What are the limitations of each method? 2. Prepare a monthly purchases budget for room amenities (units and amount). s There are more than four accounts, but to make the exercise less tedious (and error prone), figures for the remaining accounts have been filled in on your spreadsheet. Further, two unusual observations are dropped. 1,475 Year Month $ms Availa Rooms Rented 2015 1 3,162 1,017 2015 2 2,958 823 2015 3 3,162 1,393 2015 4 3,060 1,421 2015 5 3,162 1,594 2015 6 3,060 2,040 2015 7 3,162 1,984 2015 8 3,162 1,764 2015 9 3,060 2015 10 3,162 1,271 2015 11 3,060 1,012 2015 12 3,162 1,141 2016 1 3,162 922 2016 2 2,856 896 2016 4 3,060 1,234 2016 5 3.162 1,546 2016 6 2016 8 3,162 2,003 2016 9 3,060 1,542 2016 10 3,162 1,194 2016 11 3,060 915 2016 12 3,162 900 2017 1 3,162 719 2017 2 2,856 788 2017 3 3,162 1,367 2017 4 3,060 1,253 2017 5 3,162 1,644 2017 6 3,060 2,030 2017 8 3,162 2,107 2017 9 3,060 1,722 2017 10 3,162 1,242 2017 11 3,060 1,095 2017 12 3,162 1,014 Rooms Revenue $39,920.00 $29,777.00 $52,099.00 $51,618.00 $64,288.00 $87,321.00 $101,024.00 $80,967.00 $56,488.00 $46,754.00 $37,747.00 $38,376.00 $34,426.00 $32,728.00 $40,373.00 $58,119.00 $74,540.00 $85,785.00 $64,104.00 $48,663.00 $36,997.00 $37,519.00 $27,762.00 $26,742.00 $43,897.00 $39,692.00 $54,763.00 $78,221.00 $86,730.00 $70,093.00 $48,832.00 $42,596.00 $39,089.00 Other Revenue $180.00 $365.00 $244.00 $450.00 $655.00 $869.00 $1,396.00 $705.00 $834.00 $643.00 $556.00 $271.00 $341.00 $864.00 $574.00 $820.00 $927.00 $1,200.00 $1,025.00 $370.00 $504.00 $505.00 $463.00 $389.00 $772.00 $534.00 $862.00 $962.00 $1,038.00 $710.00 $569.00 $884.00 $561.00 O O OWNER CANNEDOVOCA Total Revenue $40,100.00 $30,142.00 $52,343.00 $52,068.00 $64.943.00 $88,190.00 $102,420.00 $81,672.00 $57,322.00 $47,397.00 $38,303.00 $38,647.00 $34,767.00 $33,592.00 $40,947.00 $58,939.00 $75,467.00 $86,985.00 $65,129.00 $49,033.00 $37,501.00 $38,024.00 $28,225.00 $27,131.00 $44,669.00 $40,226.00 $55,625.00 $79,183.00 $87,768.00 $70,803.00 $49.401.00 $43,480.00 $39,650.00 Front Office Housekeeping ther Personnefotal Personnel $8,823.00 $3,539.00 $7,880.00 $20,242.00 $8,263.00 $2,979.00 $5,978.00 $17,220.00 $8,864.00 $4,781.00 $7,976.00 $21,621.00 $8,452.00 $4,461.00 $7,014.00 $19,927.00 $7,806.00 $5,294.00 $8,357.00 $21,457.00 $8,265.00 $7,222.00 $6,411.00 $21,898.00 $9,103.00 $6,772.00 $6,995.00 $22,870.00 $10,321.00 $6,076.00 $4,930.00 $21,327.00 $9,282.00 $5,366.00 $6,803.00 $21,451.00 $9,845.00 $4,361.00 $6,348.00 $20,554.00 $6,573.00 $3,212.00 $6,632.00 $16,417.00 $7,325.00 $3,882.00 $6,871.00 $18,078.00 $9,201.00 $3,774.00 $6,536.00 $19,511.00 $6,058.00 $3,283.00 $6,162.00 $15,503.00 $8,495.00 $4,117.00 $4,150.00 $16,762.00 $8,942.00 $5,622.00 $7,387.00 $21,951.00 $9,208.00 $6,982.00 $8,249.00 $24,439.00 $9,159.00 $8,095.00 $6,675.00 $23,929.00 $7,353.00 $5,259.00 $6,878.00 $19,490.00 $8,714.00 $4,624.00 $4,695.00 $18,033.00 $8,200.00 $3,461.00 $7,697.00 $19,358.00 $8,811.00 $3,009.00 $4,603.00 $16,423.00 $8,255.00 $3,083.00 $6,827.00 $18,165.00 $7,390.00 $2,710.00 $4,774.00 $14,874.00 $9,719.00 $5,601.00 $5,316.00 $20,636.00 $7,916.00 $4,298.00 $4,963.00 $17,177.00 $9,498.00 $6,513.00 $6,281.00 $22,292.00 $8,792.00 $7,898.00 $5,562.00 $22,252.00 $7,830.00 $9,187.00 $7,998.00 $25,015.00 $6,382.00 $8,634.00 $7,776.00 $22,792.00 $8,153.00 $5,412.00 $6,945.00 $20,510.00 $9,269.00 $4,443.00 $7,330.00 $21,042.00 $9,066.00 $4,513.00 $6,410.00 $19,989.00 Supplies Dther Expense: $268.00 $4,597.00 $867.00 $3,119.00 $1,561.00 $2,549.00 $2,031.00 $2,866.00 $1,486.00 $2,140.00 $652.00 $2,711.00 $2,327.00 $4,052.00 $1,908.00 $3,592.00 $620.00 $1,919.00 $1,456.00 $2,305.00 $972.00 $2,730.00 $1,105.00 $1,296.00 $1,089.00 $2,649.00 $787.00 $2,647.00 $1,108.00 $2,158.00 $2,594.00 $3,034.00 $4,525.00 $2,231.00 $2,038.00 $3,062.00 $1,342.00 $1,425.00 $2,116.00 $1,975.00 $436.00 $1,399.00 $2,818.00 -$3,224.00 $421.00 $1,836.00 $1,315.00 $4,563.00 $641.00 $467.00 $1,496.00 $1,800.00 $4,095.00 $2,156.00 $1,887.00 $1,822.00 $2,718.00 $3,092.00 $3,103.00 $1,698.00 $1,860.00 $3,750.00 $425.00 $2,513.00 $1,252.00 -$2,447.00 Repairs $923.00 $1,888.00 $705.00 $2,085.00 $558.00 $2,637.00 $4,938.00 $731.00 $1,421.00 $2,298.00 $711.00 $1,701.00 $1,323.00 $1,328.00 $4,800.00 $2,833.00 $9,088.00 $350.00 $7,471.00 $1,010.00 $499.00 $1,037.00 $2,145.00 $1,191.00 $1,836.00 $1,401.00 $2,710.00 $3,503.00 $3,271.00 $1,639.00 $1,732.00 $952.00 $4,228.00 Energy Other Utilities $5,269.00 $720.00 $4,676.00 $2,581.00 $6,848.00 $2,336.00 $4,056.00 $3,294.00 $5,934.00 $1,083.00 $7,473.00 $5,854.00 $6,389.00 $3,107.00 $6,315.00 $874.00 $7,865.00 $7,396.00 $4,019.00 $3,312.00 $2,441.00 $3,075.00 $6,408.00 $2,401.00 $5,321.00 $3,182.00 $6,659.00 $3,949.00 $6,311.00 $3,523.00 $4,441.00 $5,875.00 $1,222.00 $2,241.00 $5,040.00 $2,238.00 $2,352.00 $2,313.00 $5,415.00 $2,378.00 $5,523.00 $2,420.00 $5,662.00 $2,464.00 $5,121.00 $1,914.00 $7,513.00 $2,480.00 $8,430.00 $2,395.00 $1,641.00 $2,935.00 $4,649.00 $2,046.00 $3,307.00 $2,431.00 $9,173.00 $2,664.00 $4,094.00 $2,931.00 $4,118.00 $2,295.00 $5,428.00 $2,786.00 $1,399.00 $1,994.00 3,060 1,871 Sunrise Hotels (D) January, 2017. Previously, you estimated a cost function for the property and completed a CVP analysis. Your supervisor has asked you to prepare a 2018 operating budget . Analysis of historical data suggests the following monthly room demand: Months Monthly Demand January and February 5% of annual demand. March-May, September October 8% of annual demand. June-August 12% of annual demand. November and December 7% of annual demand. Revenues. In Sunrise (B), you forecast the average daily rate and sales volume for 2018. Assume the room rate is constant throughout the year. Costs. Assume now that you ran regressions for each of the separate expense categories in your data file. Increasing the variable cost estimates by 4% (an assumption given in the B case) results in the cost formulas provided in the table below. Item Cost Equation Front desk $7,800 Housekeeping $4.30/rental Rentals Supplies $1.35/rental * Rentals Energy $4,700 An alternative to regression is the industrial engineering method of cost estimation. Your regional manager provides you with the following insights: The front desk is staffed twenty-four hours per day. Average wages of front desk staff are $8.00/hour. Your annual salary, as general manager, is $27,000. Housekeeping should clean a room in 20 minutes. However, preparing the laundry cart and taking linens to the laundry room mean that, on average, only two rooms per hour can be cleaned. The hourly wage rate for housekeepers is expected be $7.50. A laundry worker works 20 hours per week at $7.50/hour. Room Amenities. Rooms are stocked with two bars of soap and a bottle of shampoo. Soap costs $0.14 a bar and shampoo costs $0.25 per bottle. At the beginning of each month, you want 25% of next month's requirements on hand. Beginning inventory requirements are met on 1/1. Assume January 2018 room sales will be 900 rentals. Other Supplies. Paint, light bulbs, coffee, etc. average about $1,000 per month. Required 1. Prepare a monthly operating budget for 2018. Explain why you chose the regression or industrial engineering method to estimate costs. What are the limitations of each method? 2. Prepare a monthly purchases budget for room amenities (units and amount). s There are more than four accounts, but to make the exercise less tedious (and error prone), figures for the remaining accounts have been filled in on your spreadsheet. Further, two unusual observations are dropped. 1,475 Year Month $ms Availa Rooms Rented 2015 1 3,162 1,017 2015 2 2,958 823 2015 3 3,162 1,393 2015 4 3,060 1,421 2015 5 3,162 1,594 2015 6 3,060 2,040 2015 7 3,162 1,984 2015 8 3,162 1,764 2015 9 3,060 2015 10 3,162 1,271 2015 11 3,060 1,012 2015 12 3,162 1,141 2016 1 3,162 922 2016 2 2,856 896 2016 4 3,060 1,234 2016 5 3.162 1,546 2016 6 2016 8 3,162 2,003 2016 9 3,060 1,542 2016 10 3,162 1,194 2016 11 3,060 915 2016 12 3,162 900 2017 1 3,162 719 2017 2 2,856 788 2017 3 3,162 1,367 2017 4 3,060 1,253 2017 5 3,162 1,644 2017 6 3,060 2,030 2017 8 3,162 2,107 2017 9 3,060 1,722 2017 10 3,162 1,242 2017 11 3,060 1,095 2017 12 3,162 1,014 Rooms Revenue $39,920.00 $29,777.00 $52,099.00 $51,618.00 $64,288.00 $87,321.00 $101,024.00 $80,967.00 $56,488.00 $46,754.00 $37,747.00 $38,376.00 $34,426.00 $32,728.00 $40,373.00 $58,119.00 $74,540.00 $85,785.00 $64,104.00 $48,663.00 $36,997.00 $37,519.00 $27,762.00 $26,742.00 $43,897.00 $39,692.00 $54,763.00 $78,221.00 $86,730.00 $70,093.00 $48,832.00 $42,596.00 $39,089.00 Other Revenue $180.00 $365.00 $244.00 $450.00 $655.00 $869.00 $1,396.00 $705.00 $834.00 $643.00 $556.00 $271.00 $341.00 $864.00 $574.00 $820.00 $927.00 $1,200.00 $1,025.00 $370.00 $504.00 $505.00 $463.00 $389.00 $772.00 $534.00 $862.00 $962.00 $1,038.00 $710.00 $569.00 $884.00 $561.00 O O OWNER CANNEDOVOCA Total Revenue $40,100.00 $30,142.00 $52,343.00 $52,068.00 $64.943.00 $88,190.00 $102,420.00 $81,672.00 $57,322.00 $47,397.00 $38,303.00 $38,647.00 $34,767.00 $33,592.00 $40,947.00 $58,939.00 $75,467.00 $86,985.00 $65,129.00 $49,033.00 $37,501.00 $38,024.00 $28,225.00 $27,131.00 $44,669.00 $40,226.00 $55,625.00 $79,183.00 $87,768.00 $70,803.00 $49.401.00 $43,480.00 $39,650.00 Front Office Housekeeping ther Personnefotal Personnel $8,823.00 $3,539.00 $7,880.00 $20,242.00 $8,263.00 $2,979.00 $5,978.00 $17,220.00 $8,864.00 $4,781.00 $7,976.00 $21,621.00 $8,452.00 $4,461.00 $7,014.00 $19,927.00 $7,806.00 $5,294.00 $8,357.00 $21,457.00 $8,265.00 $7,222.00 $6,411.00 $21,898.00 $9,103.00 $6,772.00 $6,995.00 $22,870.00 $10,321.00 $6,076.00 $4,930.00 $21,327.00 $9,282.00 $5,366.00 $6,803.00 $21,451.00 $9,845.00 $4,361.00 $6,348.00 $20,554.00 $6,573.00 $3,212.00 $6,632.00 $16,417.00 $7,325.00 $3,882.00 $6,871.00 $18,078.00 $9,201.00 $3,774.00 $6,536.00 $19,511.00 $6,058.00 $3,283.00 $6,162.00 $15,503.00 $8,495.00 $4,117.00 $4,150.00 $16,762.00 $8,942.00 $5,622.00 $7,387.00 $21,951.00 $9,208.00 $6,982.00 $8,249.00 $24,439.00 $9,159.00 $8,095.00 $6,675.00 $23,929.00 $7,353.00 $5,259.00 $6,878.00 $19,490.00 $8,714.00 $4,624.00 $4,695.00 $18,033.00 $8,200.00 $3,461.00 $7,697.00 $19,358.00 $8,811.00 $3,009.00 $4,603.00 $16,423.00 $8,255.00 $3,083.00 $6,827.00 $18,165.00 $7,390.00 $2,710.00 $4,774.00 $14,874.00 $9,719.00 $5,601.00 $5,316.00 $20,636.00 $7,916.00 $4,298.00 $4,963.00 $17,177.00 $9,498.00 $6,513.00 $6,281.00 $22,292.00 $8,792.00 $7,898.00 $5,562.00 $22,252.00 $7,830.00 $9,187.00 $7,998.00 $25,015.00 $6,382.00 $8,634.00 $7,776.00 $22,792.00 $8,153.00 $5,412.00 $6,945.00 $20,510.00 $9,269.00 $4,443.00 $7,330.00 $21,042.00 $9,066.00 $4,513.00 $6,410.00 $19,989.00 Supplies Dther Expense: $268.00 $4,597.00 $867.00 $3,119.00 $1,561.00 $2,549.00 $2,031.00 $2,866.00 $1,486.00 $2,140.00 $652.00 $2,711.00 $2,327.00 $4,052.00 $1,908.00 $3,592.00 $620.00 $1,919.00 $1,456.00 $2,305.00 $972.00 $2,730.00 $1,105.00 $1,296.00 $1,089.00 $2,649.00 $787.00 $2,647.00 $1,108.00 $2,158.00 $2,594.00 $3,034.00 $4,525.00 $2,231.00 $2,038.00 $3,062.00 $1,342.00 $1,425.00 $2,116.00 $1,975.00 $436.00 $1,399.00 $2,818.00 -$3,224.00 $421.00 $1,836.00 $1,315.00 $4,563.00 $641.00 $467.00 $1,496.00 $1,800.00 $4,095.00 $2,156.00 $1,887.00 $1,822.00 $2,718.00 $3,092.00 $3,103.00 $1,698.00 $1,860.00 $3,750.00 $425.00 $2,513.00 $1,252.00 -$2,447.00 Repairs $923.00 $1,888.00 $705.00 $2,085.00 $558.00 $2,637.00 $4,938.00 $731.00 $1,421.00 $2,298.00 $711.00 $1,701.00 $1,323.00 $1,328.00 $4,800.00 $2,833.00 $9,088.00 $350.00 $7,471.00 $1,010.00 $499.00 $1,037.00 $2,145.00 $1,191.00 $1,836.00 $1,401.00 $2,710.00 $3,503.00 $3,271.00 $1,639.00 $1,732.00 $952.00 $4,228.00 Energy Other Utilities $5,269.00 $720.00 $4,676.00 $2,581.00 $6,848.00 $2,336.00 $4,056.00 $3,294.00 $5,934.00 $1,083.00 $7,473.00 $5,854.00 $6,389.00 $3,107.00 $6,315.00 $874.00 $7,865.00 $7,396.00 $4,019.00 $3,312.00 $2,441.00 $3,075.00 $6,408.00 $2,401.00 $5,321.00 $3,182.00 $6,659.00 $3,949.00 $6,311.00 $3,523.00 $4,441.00 $5,875.00 $1,222.00 $2,241.00 $5,040.00 $2,238.00 $2,352.00 $2,313.00 $5,415.00 $2,378.00 $5,523.00 $2,420.00 $5,662.00 $2,464.00 $5,121.00 $1,914.00 $7,513.00 $2,480.00 $8,430.00 $2,395.00 $1,641.00 $2,935.00 $4,649.00 $2,046.00 $3,307.00 $2,431.00 $9,173.00 $2,664.00 $4,094.00 $2,931.00 $4,118.00 $2,295.00 $5,428.00 $2,786.00 $1,399.00 $1,994.00 3,060 1,871
