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 January and February March-May, September-October June-August November and December 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. . Monthly Demand 5% of annual demand. 8% of annual demand. 12% of annual demand. 7% of annual demand. Item Front desk Housekeeping Supplies Energy . An alternative to regression is the industrial engineering method of cost estimation. Your regional manager provides you with the following insights: Cost Equation $7,800 $4.30/rental * Rentals $1.35/rental * Rentals $4,700 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). 5 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. Year Month oms Availa Rooms Rented Rooms Revenue 1 3,162 2,958 2 3 3,162 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2015 2016 2016 2016 2016 2016 2016 2016 2016 2016 2016 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 4 3,060 5 3,162 3,060 3,162 3,162 NH68SGAWNIOGAN-=0000 3,060 10 3,162 3,060 12 3,162 3,162 2 2,856 3,060 3,162 3,060 3,162 3,060 10 3,162 3,060 11 12 3,162 3,162 2,856 3,162 3,060 3,162 3,060 3,162 3,060 10 3,162 3,060 12 3,162 1,017 823 1,393 1,421 1,594 2,040 1,984 1,764 1,475 1,271 1,012 1,141 922 896 1,234 1,546 1,871 2,003 1,542 1,194 915 900 719 788 1,367 1,253 1,644 2,030 2,107 1,722 1,242 1,095 1,014 $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 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 HousekeepingOther Personnelotal Personne Supplies Other Expenses $4,597.00 $923.00 $3,119.00 $8,823.00 $3,539.00 $8,263.00 $2,979.00 $8,864.00 $4,781.00 $8,452.00 $4,461.00 $7,806.00 $5,294.00 $8,265.00 $7,222.00 $9,103.00 $6,772.00 $6,076.00 $10,321.00 $8,495.00 $9,282.00 $5,366.00 $9,845.00 $4,361.00 $6,573.00 $3,212.00 $7,325.00 $3,882.00 $9,201.00 $3,774.00 $6,058.00 $3,283.00 $4,117.00 $8,942.00 $5,622.00 $9,208.00 $6,982.00 $9,159.00 $8,095.00 $7,353.00 $5,259.00 $8,714.00 $4,624.00 $8,200.00 $3,461.00 $8,811.00 $3,009.00 $8,255.00 $3,083.00 $7,390.00 $2,710.00 $5,601.00 $7,880.00 $20,242.00 $268.00 $5,978.00 $17,220.00 $867.00 $7,976.00 $21,621.00 $1,561.00 $7,014.00 $19,927.00 $2,031.00 $8,357.00 $21,457.00 $1,486.00 $6,411.00 $21,898.00 $652.00 $6,995.00 $22,870.00 $2,327.00 $4,930.00 $21,327.00 $1,908.00 $6,803.00 $21,451.00 $620.00 $6,348.00 $20,554.00 $1,456.00 $6,632.00 $16,417.00 $972.00 $6,871.00 $18,078.00 $1,105.00 $6,536.00 $19,511.00 $1,089.00 $6,162.00 $15,503.00 $787.00 $4,150.00 $16,762.00 $1,108.00 $7,387.00 $21,951.00 $2,594.00 $8,249.00 $24,439.00 $4,525.00 $6,675.00 $23,929.00 $2,038.00 $6,878.00 $19,490.00 $1,342.00 $4,695.00 $18,033.00 $2,116.00 $7,697.00 $19,358.00 $436.00 $4,603.00 $16,423.00 $2,818.00 $6,827.00 $18,165.00 $421.00 $4,774.00 $14,874.00 $1,315.00 $5,316.00 $20,636.00 $641.00 $4,963.00 $17,177.00 $1,496.00 $6,281.00 $22,292.00 $4,095.00 $5,562.00 $22,252.00 $1,887.00 $7,998.00 $25,015.00 $2,718.00 $7,776.00 $22,792.00 $3,103.00 $6,945.00 $20,510.00 $1,860.00 $7,330.00 $21,042.00 $425.00 $6,410.00 $19,989.00 $1,252.00 $1,888.00 $2,549.00 $705.00 $2,866.00 $2,085.00 $2,140.00 $558.00 $2,711.00 $2,637.00 $4,052.00 $4,938.00 $3,592.00 $731.00 $1,919.00 $1,421.00 $2,305.00 $2,298.00 $2,730.00 $711.00 $1,296.00 $1,701.00 $2,649.00 $1,323.00 $2,647.00 $1,328.00 $2,158.00 $4,800.00 $6,311.00 $3,034.00 $2,833.00 $4,441.00 $2,231.00 $9,088.00 $1,222.00 $3,062.00 $350.00 $5,040.00 $1,425.00 $7,471.00 $2,352.00 $1,975.00 $1,010.00 $5,415.00 $1,399.00 $499.00 $5,523.00 -$3,224.00 $1,037.00 $5,662.00 $1,836.00 $2,145.00 $5,121.00 $4,563.00 $1,191.00 $7,513.00 $467.00 $1,836.00 $8,430.00 $1,800.00 $1,401.00 $1,641.00 $2,156.00 $2,710.00 $4,649.00 $1,822.00 $3,503.00 $3,307.00 $3,092.00 $3,271.00 $9,173.00 $1,698.00 $1,639.00 $3,750.00 $1,732.00 $2,513.00 $952.00 -$2,447.00 $4,228.00 $9,719.00 $7,916.00 $9,498.00 $4,298.00 $6,513.00 $8,792.00 $7,898.00 $7,830.00 $9,187.00 $6,382.00 $8,634.00 $8,153.00 $5,412.00 $9,269.00 $4,443.00 $9,066.00 $4,513.00 $4,094.00 Repairs Energy $5,269.00 $4,676.00 $6,848.00 $4,056.00 $5,934.00 $7,473.00 $6,389.00 $7,396.00 $3,312.00 $3,075.00 $6,315.00 $7,865.00 $4,019.00 $2,441.00 $6,408.00 $5,321.00 $3,182.00 $6,659.00 -$3,949.00 $3,523.00 $2,401.00 -$5,875.00 $2,241.00 $2,238.00 $2,313.00 $2,378.00 $2,420.00 Other Utilities $720.00 $2,581.00 $2,336.00 $3,294.00 $1,083.00 $5,854.00 $3,107.00 -$874.00 $2,464.00 $1,914.00 $2,480.00 $2,395.00 $2,935.00 $2,046.00 $2,431.00 $2,664.00 $2,931.00 $4,118.00 $2,295.00 $5,428.00 $2,786.00 $1,399.00 $1,994.00