2. Estimating the hotel's cost function Next week is the Area Manager's Meeting. Your GM wants to look good in front of her boss, the district managers and other general managers. To begin your analysis, you must estimate the hotel's cost equation. The accounting department provided you with three years (2015-2017) of monthly data. These data consist of the following items: Item Rooms available Rooms rented Rooms revenues Other revenues Total revenues Front office Housekeeping Other personnel Total personnel Supplies Other expenses Repairs Energy Other utilities Description A measure of capacity. Rooms out of service (for repairs) lower this measure. Number of rooms sold each month (i.e., sales volume) Total revenues received for rooms rented. Includes vending machines, Internet access, laundry, etc. The sum of room revenues and other revenues. Wages for the general manager and the front desk staff. Wages for housekeepers, housekeeper supervisor and laundry worker. Wages for the maintenance worker, payroll taxes, benefits, etc. The sum of front office, housekeeping and other personnel expenses. Expenses related to cleaning supplies, in-room amenities, coffee, etc. All expenses not otherwise classified (e.g., advertising) Expenses related to paint, plumbing supplies, electrical supplies, etc. Expenses for gas, electricity and water. Expenses for trash pickup, cable television, telephone equipment, etc. 3. Required 1. Identify the cost object (i.e., the dependent, or y variable). 2. Identify the cost driver (i.e., the independent, or x value). 3. Plot the data to assess whether a linear (mixed cost) model is appropriate. Are there any unusual data points that should be removed or transformed? 4. How much data should be used in the cost equation? Must you adjust for inflation? 5. Estimate the cost equation. 6. Evaluate the cost equation. Sunrise Hotels (B) 1. Case background January 2018. The GM has asked you to prepare a CVP analysis to aid in discussing next year's financial expectations at the upcoming Area Managers' Meeting. The meeting brings together 45 hotel managers and three district managers from the Southwest Region. From your managerial accounting class, you learned that break-even sales volume is computed using fixed costs and the unit contribution margin. Since you will be forecasting break-even for this year (2018), you need to make several adjustments to the three years of historical data you obtained. These adjustments are described as follows: Price. You examine the average selling price (i.e., average daily rate) for the past two years and decide that the hotel's room rate for next year will continue the same trend; in other words, you will realize the same percentage increase (or decrease) that was experienced in the past year. For example, if your rate increased 8% from 2016 to 2017, you will then forecast the 2018 room rate to be 8% above 2017's room rate. Costs. Your hotel's variable and fixed costs are obtained from the (A) case, in which you estimated the hotel's cost equation. After consultation with industry experts, you predict fixed costs will be the same next year. Further, you expect the variable cost per room-night to increase 4% due to inflation. Based on your analysis of local market data, you believe that sales volumes will remain flat; that is, the number of rooms rented in 2018 will be unchanged from the 2017 sales volume. 2. Required 1. What are the assumptions of CVP analysis? 2. Compute the breakeven sales volume for 2018. 3. Calculate the margin of safety. 4. Determine the degree of operating leverage. What happens to leverage at different levels of planned sales volume? Hint: Compute operating leverage at sales volumes of 50% and 120% of the 2018 planned sales volume. 5. Draw a CVP plot and label the different attributes of the plot. Provide a brief interpretation of your figure. 1. Case background Recently a commercial laundry service, The Washouse Company, approached you with an offer to clean and fold the hotel's linens and towels. Washouse is an established laundry service that does laundry for area hospitals, restaurants, repair shops and a local gym. They will pick up and drop off laundry each weekday with next-day turnaround. The quoted price is $0.32/pound. Sunrise will be billed weekly with payment terms at 7 days after receipt of invoice. Each rental generates 3.5 pounds of laundry. Since its founding, all Sunrise Hotels did their own laundry. Each property has a commercial washing machine, dryer and folding machine. Recently you read an article in Hotelier Monthly that extolled the benefits of outsourcing achieved by many full-service hotels. Your hotel has a washer, dryer, folder (iron) and water treatment equipment with a book value of $25,500. Purchased 4 years ago, the equipment was estimated to have a useful life of ten (10) years. If you outsource the laundry, the equipment can be sold. The salvage value is $4,500. Space for the laundry room in most properties is obtained by using two guest rooms at the rear of the property. If the laundry is outsourced, the laundry room can be converted to guest rooms. Conversion costs would be about $30,000 for furnishings (bed, drapes, table, television, etc.), fixtures (lights, toilet, shower, etc.) and flooring. Rooms are remodeled every seven (7) years. Laundry supplies (detergent, bleach, etc.) were estimated using a regression model for all cleaning supplies. You have determined that the variable cost is $0.19/rental. Variable costs for laundry room water, gas and electric were estimated at $0.12/rental. Reviewing financial and operational data you learn that repair costs averaged $4,725 annually over the past five years. The laundry department is supervised by the Head Housekeeper and 25% of her wages have been allocated to the laundry department. The head housekeeper is paid $9.30 per hour and works full- time (2,000 hours annually). The laundry worker is paid $8.06 per hour. The laundry room can process 80 pounds of laundry per hour. 2. Required 1. Use the relevant cost framework to analyze the outsourcing decision if you expect 60%occupancy. What should you do? 2. Re-do the analysis with the assumption that occupancy remains the same as 2017. 3. What qualitative factors should be considered? 1. Case background January, 2018. 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 Monthly Demand 5% of annual demand. 8% of annual demand. 12% of annual demand. 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 Front desk Housekeeping Supplies Energy Cost Equation $7,800 $4.30/rental Rentals $1.35/rental Rentals $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 January 1. Assume January 2019 room sales will be 900 rentals. Other Supplies. Paint, light bulbs, coffee, etc. average about $1,000 per month. 2. 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). 1 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. Case background It is January 2019 and you are reviewing the 2018 income statement for your property. Next week, you and six other general managers will meet in El Paso, Texas with the regional manager for an annual performance review meeting. You gather the data provided below and use variance analysis to understand your results prior to the meeting. Data from an industry consultant show that the market size in 2018 was 73,363 rooms, and you used this information in calculating your 2018 sales forecast (in Exercises 2 and 3). Today, in January 2019, market data revealed that 60,575 rooms were sold in your area by you and your competitors. Calculate actual (1) market share and (2) average room rate so that you may use these numbers to create flex budgets that enable you to report (1) market size and (2) market share variances. Variances are computed relative to the budget. Use the budget you prepared in the Sunrise Hotels (D) case. The following table was obtained from your year-end income statement: Rooms Rented Revenues Controllable Costs Front Office 112,191 Housekeeping 56,165 Other Personnel 79,594 Total Personnel 247,950 Supplies 20,601 Other Expense 28,792 Repairs 37,218 Energy 60,795 Utilities 29,394 Total Expense Controllable Profit 15,713 $ 593,058 $424,750 $ 168,308 2. Required 1. What are the Level-0, Level-1 and Level-2 variances for your property? What are the market size, market share and sales price variances? Hint: Prepare multiple flexible budgets, gradually relaxing assumptions from the static budget to the actual results (the "Turn-one-dial-at-a-time" approach). 2. What are the price and efficiency variances (i.e., Level-3 variances) for housekeeping expense? To do this, you obtained the following data from the company controller: Housekeepers worked a total of 7,247.1 hours. The standard (budgeted) wage rate was provided in the budgeting case, Sunrise Hotels (D). Sunrise Hotels (F) 1. Case background Mid-January, 2019. To finish your presentation for next week's meeting, evaluate your performance. To do this, you obtained the following supplemental data from the company controller: Allocated corporate expense Depreciation expense Interest expense Exceptional items Average operating assets (total assets) Average current liabilities $93,528 $ 37,129 $ 1,395 SO $ 408,244 $17,350 Your hotel's controllable profit was provided in the Sunrise Hotels (E) case. The firm's required rate of return is 8% and its weighted average cost of capital is 6%. Assume the corporate income tax rate is 30%. 2. Required 1. Compute your return on sales (ROS). 2. Compute total asset turnover (TAT). 3. Compute return on assets (ROA). 4. Compute residual income (RI) and economic value-added (EVA). Appendix A. Data Front 2015 2 2015 3 3,162 1,393 2015 4 2016 7 2016 8 900 $37,519 719 $27,762 788 $26,742 $389 $27,131 $7,390 $2,710 $4,774 1,367 $43,897 $772 $44,669 $9,719 $5,601 $5,316 2,856 3,162 3,060 1,253 $39,692 3,162 1,644 $54,763 3,060 2,030 $78,221 3,162 2,572 $105,477 3,162 2,107 $86,730 3,060 1,722 $70,093 $1,836 $8,430 $2,395 $534 $40,226 $7,916 $4,298 $4,963 $17,177 $1,496 $1,800 $1,401 $1,641 $2,935 $862 $55,625 $9,498 $6,513 $6,281 $22,292 $4,095 $2,156 $2,710 $4,649 $2,046 $962 $79,183 $8,792 $7,898 $5,562 $22,252 $1,887 $1,822 $3,503 $3,307 $2,431 $1,312 $106,789 $12,624 $12,189 $6,979 $31,792 $4,633 $3,889 $1,387 $6,864 $2,876 $1,038 $87,768 $7,830 $9,187 $7,998 $25,015 $2,718 $3,092 $3,271 $9,173 $2,664 $710 $70,803 $6,382 $8,634 $7,776 $22,792 $1,698 $1,639 $4,094 $2,931 $569 $49,401 $8,153 $5,412 $6,945 $20,510 $3,750 $1,732 $4,118 $2,295 $884 $43,480 $9,269 $4,443 $7,330 $21,042 $952 $5,428 $2,786 $561 $39,650 $9,066 $4,513 $6,410 $19,989 2018 by T. Calderon, J. Hesford, N. Mangin and M. Pizzini. $425 $2,513 $1,252 ($2,447) $4,228 $1,399 $1,994 Rooms Rooms Rooms Other Total Other Other Year Mo. Available Rented Revenue Revenue Revenue Office keeping Personnel Personnel Supplies Expenses Repairs Energy Utilities 2015 1 3,162 1,017 $39,920 $180 $40,100 $8,823 $3,539 $7,880 $20,242 $268 $4,597 $923 $5,269 $720 2,958 823 $29,777 $365 $30,142 $8,263 $2,979 $5,978 $17,220 $867 $3,119 $1,888 $4,676 $2,581 $52,099 $244 $52,343 $8,864 $4,781 $7,976 $21,621 $2,031 $2,866 $2,085 $4,056 $3,294 3,060 1,421 $51,618 $450 $52,068 $8,452 $4,461 $7,014 $19,927 $2,031 $2,866 $2,085 $4,056 $3,294 2015 5 3,162 1,594 $64,288 $655 $64,943 $7,806 $5,294 $8,357 $21,457 $1,486 $2,140 $558 $5,934 $1,083 2015 6 3,060 2,040 $87,321 $869 $88,190 $8,265 $7,222 $6,411 $21,898 $652 $2,711 $2,637 $7,473 $5,854 2015 7 3,162 1,984 $101,024 $1,396 $102,420 $9,103 $6,772 $6,995 $22,870 $2,327 $4,052 $4,938 $6,389 $3,107 2015 8 3,162 1,764 $80,967 $705 $81,672 $10,321 $6,076 $4,930 $21,327 $1,908 $3,592 2015 9 3,060 1,475 $56,488 $834 $57,322 $9,282 $5,366 $6,803 $21,451 $620 $1,919 2015 10 3,162 1,271 $46,754 $643 $47,397 $9,845 $4,361 $6,348 $20,554 $1,456 $2,305 2015 11 3,060 1,012 $37,747 $556 $38,303 $6,573 $3,212 $6,632 $16,417 $972 $2,730 2015 12 3,162 1,141 $38,376 $271 $38,647 $7,325 $3,882 $6,871 $18,078 $1,105 $1,296 2016 1 3,162 ,922 $34,426 $341 $34,767 $9,201 $3,774 $6,536 $19,511 $1,089 $2,649 2016 2 2,856 896 $32,728 $864 $33,592 $6,058 $3,283 $6,162 $15,503 $787 $2,647 $1,328 2016 3 3,162 1,189 $42,630 $336 $42,966 $8,823 $4,303 $8,614 $21,740 $1,232 $3,097 $2,077 2016 4 3,060 1,234 $40,373 $574 $40,947 $8,495 $4,117 $4,150 $16,762 $1,108 $2,158 $4,800 2016 5 3,162 1,546 $58,119 $820 $58,939 $8,942 $5,622 $7,387 $21,951 $2,594 $3,034 $2,833 $4,441 ($5,875) 2016 6 3,060 1,871 $74,540 $927 $75,467 $9,208 $6,982 $8,249 $24,439 $4,525 $2,231 $9,088 $1,222 $2,241 3,162 2,265 $99,660 $1,012 $100,672 $11,306 $6,909 $8,832 $2,929 $6,880 $4,380 $9,074 $3,119 3,162 2,003 $85,785 $1,200 $86,985 $9,159 $8,095 2016 9 3,060 1,542 $64,104 $1,025 $65,129 $7,353 $5,259 $6,878 2016 10 3,162 1,194 $48,663 $370 $49,033 $8,714 $4,624 $4,695 $18,033 2016 11 3,060 915 $36,997 $504 $37,501 $8,200 $3,461 $7,697 $19,358 2016 12 3162 $505 $38,024 $8,811 $3,009 $4,603 $16,423 2017 1 3,162 $463 $28,225 $8,255 $3,083 $6,827 2017 2 2017 3 2017 4 2017 5 2017 6 2017 7 2017 8 2017 9 2017 10 3,162 1,242 $48,832 2017 11 3,060 1,095 $42,596 2017 12 3,162 1,014 $39,089 House- Other Total $731 $6,315 ($874) $1,421 $7,865 $7,396 $2,298 $4,019 $3,312 $711 $2,441 $3,075 $1,701 $6,408 $2,401 $1,323 $5,321 $3,182 $6,659 ($3,949) $6,017 $10,191 $6,311 $3,523 $27,047 $6,675 $23,929 $2,038 $3,062 $19,490 $1,342 $1,425 $350 $5,040 $2,238 $7,471 $2,352 $2,313 $2,116 $1,975 $436 $1,399 $2,818 ($3,224) $18,165 $421 $1,836 $14,874 $1,315 $4,563 $1,010 $5,415 $2,378 $499 $5,523 $2,420 $1,037 $5,662 $2,464 $2,145 $5,121 $1,914 $1,191 $7,513 $2,480 $20,636 $641 $467 $3,103 $1,860 Year Month Rooms Available Rooms Rented Rooms Revenue Other Revenue Total Revenue Front Office Housekeeping Other Personnel Total Personnel Supplies Other Expenses Repairs Energy Other Utilities 2015 1 3,162 1,017 $ 39.920 $ 180 $ 40,100 S 8,823 $ 3,539 $ 7,880 $ 2015 2 2,958 823 $ 29,777 $ 365 $ 30,142 $ 8,263 $ 2,979 $ 5,978 $ 20,242 S 17,220 $ 268 $ 4,597 $ 923 $5,269 $ 720 2015 3 3,162 1,393 $ 52,099 S 244 $ 52,343 $ 8.864 $ 4,781 S 7,976 S 21,621 $ 2015 4 3,060 1,421 S 51,618 $ 450 S 52,068 $ 8,452 $ 4,461 $ 7,014 S 19,927 $ 867 $ 1,561 $ 2,031 $ 3,119 $ 1,888 $ 4,676 $ 2,581 2,549 $ 705 $ 6,848 $ 2,336 2,866 $ 2,085 $ 4,056 $ 3,294 2015 5 3,162 1,594 $ 64,288 $ 655 $ 64,943 S 7,806 $ 5.294 $ 8,357 $ 21,457 S 2015 6 3,060 2,040 $ 87,321 S 869 $ 88,190 $ 8,265 $ 7,222 $ 6,411 S 2015 7 3,162 1,984 $ 101,024 $ 1,396 $ 102,420 $ 9,103 $ 6,772 $ 6,995 S 2015 8 3,162 1,764 $ 80,967 $ 705 $ 81,672 $ 10,321 $ 6,076 $ 4,930 $ 2015 9 3,060 1,475 $ 56,488 S 834 $ 57,322 S 9,282 $ 5,366 $ 6,803 S 2015 10 3,162 1,271 $ 46,754 S 643 $ 47,397 S 9,845 $ 4,361 $ 6,348 S 2015 11 3,060 1,012 $ 37,747 S 556 $ 38,303 $ 6,573 $ 3.212 $ 6,632 $ 2015 12 3,162 1,141 $ 38,376 $ 271 S 38,647 $ 7,325 $ 3,882 $ 6,871 $ 2016 1 3,162 922 $ 34,426 $ 341 S 34,767 S 9,201 $ 3,774 $ 6,536 $ 21,898 S 22,870 S 21,327 S 21,451 $ 20,554 S 16,417 S 18,078 S 19,511 $ 2016 2 2,856 896 $ 32,728 $ 864 $ 33,592 $ 6,058 $ 3.283 $ 6,162 $ 15,503 S 2016 3 3,162 1,189 $ 42,630 S 336 $ 42,966 $ 8,823 $ 4,303 $ 8,614 S 21,740 $ 1,486 $ 652 $ 2,327 $ 1,908 $ 620 $ 1,456 $ 972 $ 1,105 $ 1,089 $ 787 $ 1,232 $ 2,140 $ 558 $5,934 S 1,083 2016 4 3,060 1,234 $ 40,373 S 574 S 40,947 S 8,495 $ 4,117 $ 4,150 S 16,762 $ 1,108 $ 2016 5 3,162 1,546 $ 58,119 $ 820 S 58,939 S 8,942 $ 5,622 $ 7,387 S 21,951 $ 2,594 $ 2016 6 3,060 1,871 $ 74,540 $ 927 $ 75,467 $ 9,208 $ 6.982 $ 8,249 $ 24,439 S 4,525 S 2016 7 3,162 2,265 $ 99,660 $ 1,012 $ 100,672 $ 11,306 $ 6,909 $ 8,832 S 2016 8 3,162 2,003 $ 85,785 $ 1,200 $ 86,985 $ 9,159 $ 8,095 $ 6,675 $ 2016 9 3,060 1,542 $ 64,104 S 1,025 $ 65,129 $ 7,353 $ 5,259 $ 6,878 $ 2016 10 3,162 1,194 $ 48,663 $ 370 S 49,033 $ 8,714 S 4,624 $ 4,695 $ 2016 11 3,060 915 $ 36,997 S 504 $ 37,501 S 8,200 $ 3.461 $ 7,697 S 2016 12 3,162 900 $ 37,519 S 505 S 38,024 S 8.811 S 3,009 $ 4,603 $ 2017 1 3,162 719 $ 27,762 $ 463 $ 28,225 $ 8,255 $ 3,083 $ 6,827 S 2017 2 2,856 788 $ 26,742 S 389 $ 27,131 $ 7,390 $ 2,710 $ 4,774 S 2017 3 3,162 1,367 $ 43,897 S 772 $ 44,669 $ 9,719 $ 5,601 $ 5,316 S 2017 4 3,060 1,253 $ 39.692 $ 534 $ 40,226 $ 7,916 $ 4,298 $ 4,963 S 2017 5 3,162 1,644 $ 54,763 S 862 S 55,625 $ 9,498 $ 6,513 S 6,281 S 2017 6 3,060 2,030 $ 78,221 S 962 $ 79,183 $ 8,792 $ 7,898 $ 5,562 $ 27,047 S 23,929 S 19,490 $ 18,033 $ 19,358 $ 16,423 S 18,165 S 14,874 S 20,636 S 17,177 S 22,292 $ 22,252 $ 2,929 $ 2,711 $ 2,637 $ 7,473 $ 4,052 $ 4,938 $ 6,389 $ 3,592 $ 731 $ 6,315 $ 1,919 $ 1,421 $ 7,865 $ 2,305 $ 2,298 $4,019 $ 2,730 $ 711 $ 2,441 S 1,296 $ 1,701 $ 6,408 $ 2,649 $ 1,323 $ 5,321 $ 2,647 $ 1,328 $6,659 $ 3,097 $ 2,077 $ 6,017 $ 2,158 $ 4,800 $ 6,311 $ 3,034 $ 2,833 $ 4,441 $ 2,231 $ 9,088 $ 1,222 $ 6,880 $ 4,380 $ 9,074 $ 5,854 3,107 (874) 7,396 3,312 3,075 2,401 3,182 (3,949) 10,191 3,523 (5,875) 2,241 3,119 2,038 $ 1,342 $ 2,116 $ 436 $ 2,818 $ 421 S 1,315 $ 641 S 1,496 $ 4,095 $ 3,062 $ 350 $5,040 S 1,425 $ 7,471 $ 2,352 $ 1,975 $ 1,010 $ 5,415 $ 1,399 $ 499 $ 5,523 $ (3,224) $ 1,037 $ 5,662 $ 1,836 $ 2,145 $ 5,121 $ 4,563 $ 1,191 $ 7,513 $ 467 S 1,836 $ 8,430 $ 1,800 $ 1,401 $ 1,641 $ 2,156 $ 2,710 $ 4,649 $ 2,238 2,313 2,378 2,420 2,464 1,914 2,480 2,395 2,935 2,046 1,887 $ 2017 7 3,162 2,572 $ 105,477 $ 1,312 $ 106,789 S 12,624 $ 12.189 $ 6,979 $ 2017 8 3,162 2,107 $ 86,730 S 1,038 S 87,768 $ 7,830 S 9.187 $ 7,998 S 2017 9 3,060 1,722 $ 70,093 $ 710 S 70,803 $ 6,382 $ 8.634 $ 7,776 $ 2017 10 3,162 1,242 $ 48,832 $ 569 $ 49,401 $ 8.153 $ 5,412 $ 6,945 S 2017 11 3,060 1,095 $ 42,596 $ 884 $ 43,480 S 9,269 $ 4,443 S 7,330 $ 31,792 $ 25,015 $ 22,792 S 20,510 S 21,042 $ 4,633 $ 2,718 $ 3,103 S 1,822 $ 3,503 $3,307 $ 3,889 $ 1,387 $ 6,864 $ 3,092 $ 3,271 $ 9,173 $ 2,431 2,876 2,664 1,860 $ 2017 12 3,162 1,014 $ 39,089 S 561 $ 39,650 $ 9,066 $ 4,513 $ 6.410 S 19,989 $ 425 $ 1,252 $ 1,698 $ 1,639 $4,094 $ 3,750 $ 1,732 $ 4,118 $ 2,513 $ 952 $ 5,428 $ 2,931 2,295 2,786 (2,447) $ 4,228 $1,399 $ 1,994 2018 Budget Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Room Rate % of annual demand Rooms rented Revenue Expenses: Front Office Housekeeping Other Personnel Personnel Total Supplies Other Expenses Repairs Energy Utilities Total Expenses Profit 2018 Budget Rooms rented Units Soap: Soap used Beginning Inventory Ending Inventory Purchases Required (bars) Shampoo: Soap used Beginning Inventory Ending Inventory Purchases Required (bottles) Amount ($) Soap Shampoo Total purchases Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2018 Variances Actual Static Budget Variances Rooms rented Revenue Expenses: Front Office Housekeeping Other Personnel Personnel Total Supplies Other Expenses Repairs Energy Utilities Total Expenses Profit 2018 Variances Flex Budget Actual Variance Flex Budget Rooms rented Revenue Expenses: Front Office Housekeeping Other Personnel Personnel Total Supplies Other Expenses Repairs Energy Utilities Total Expenses Profit 2018 Plan 2018 Actual Variance Sales Volume Variance Static Budget 2018 Housekeeping Variance Performance Ratios Additional Notes for the Sunrise Hotel Case The case is broken into six parts (parts A-F) with each part building on the previous. It covers the following key topics from our course: Cost behavior and cost estimation (A), CVP analysis (B), Relevant costs and outsourcing (C), Budgeting (D), Variances (E), and Performance measures/ratios (F). This case is to be completed in groups, as assigned, and is worth 300 points (or 30% of your overall grade). Each group will complete a set of schedules in the provided Excel file and upload it to Canvas. All group members (four or less in a group) will receive the same grade unless the group communicates a concern about one or more of the participants. The case is due Tuesday, December 12. Use this document to provide your group additional clarity on what is explained in the case and its requirements. In addition to the requirements in the case, add any additional requirements I have included in red below. Check figures are also noted to help you along the way. Please use proper labeling and formatting to highlight your answers - answers without support will not receive full credit. If you need additional guidance, I am available to help. The quality of your open responses will have a strong impact on your group grade, so please do not focus solely on the numbers. Case A Read the introduction to the case and then spend some time understanding the structure of the data across the rows and columns, as described in A.2. Note, Total Revenues and Total Personnel in the data set are separate subtotals of revenues or expenses, respectively. Create a total expense column adjacent to the data, as this will be utilized for many following calculations. The cost object is what we are trying to estimate which is total hotel costs and rooms rented is the cost driver - the primary activity that drives variable costs (no need to answer A.3.1. or A.3.2 as these are the answers). For A.3.3, create two scatter plots - a specific type of chart in Excel: (a) rooms rented by total expenses (y-axis), and (b) total expenses (y-axis) over time which gives you a time trend because the data is organized by month and year (the x-axis represents time but can just be observation count as opposed to forcing a date on the axis). What do these plots tell you about the cost driver - indicate on Plots tab? When estimating costs, consider whether all the data is appropriate to estimate the cost function (A.3.4). Identify three (3) observations that are outliers that would have undue influence on the cost function - these may (or may not) fall on the regression line but standout from the rest of the population. Create a second data set on the "Data without outliers" tab that removes the three outlier rows. Use the revised data to recreate plot (a) above on the Plots tab (which should now have three plots). Use the High-Low method to estimate the cost in A.3.5 based upon the revised data set. Check figure: using your function, total cost for 1,500 rooms rented should = $38,791 for a year. Ignore A.3.6. IMPORTANT!!!! Now that we have established the cost function, be sure to rely on the full data set provided for all future analyses. Case B You are predicting 2018 revenues and costs in this part. B.2.1 - develop and present/explain assumptions your group will use for the sales price (which is average revenue/room), variable cost/unit, and fixed costs given the new information presented in predicting 2018 revenues and costs-you have done the baseline work in Case A. Follow the Price bullet point carefully for sales price change. For B.2.2, your breakeven for the entire year should be provided, not for one month. For B.2.3, calculate the margin both in units and sales dollars for 2018. Check figure: margin of safety volume = 7,084 units. Ignore B.2.4 and B.2.5. Case C (probably the hardest part!) This is an annual analysis to determine whether to outsource laundry to save some costs. Remember the definition of relevant information (referred to as the "relevant cost framework") as you complete the requirements. C.2.1-go through all the facts in this part of the case to determine what is relevant Use the data to determine the # of hotel rooms available for the year to apply to the 60% occupancy. Given you are estimating at 60% occupancy, what would be the prudent decision regarding the conversion costs of the laundry rooms to rentable rooms-relevant costs? Start the outsourcing decision by determining total pounds of laundry per year at 60% room capacity. The Head Housekeeper will continue to work full time with the same total compensation even if the service is outsourced. Hint: for DL, you need to calculate total hours to be worked. Check figure: laundry supplies and relevant utility costs = $6,925. Check figure: your final answer to outsource for C.2.2 should be different from C.2.1. For C.2.3, also provide an explanation of what was learned from the two analyses. Case D Use the templates provided in the Excel file to complete the 2018 operating and monthly purchases budgets. We will use a "regression" approach (the table under "Costs"), so you can ignore any discussion about the industrial engineering method. As a result, you can also ignore the three bullet points under "Costs" for this part of the case (HOWEVER, they will help us in Case E which follows). Start with the # of rooms to be rented for the year, then use the spread provided in the case to calculate monthly rooms expected to be rented. Use the model shown in the case for the four expense categories, then use the following for the remaining categories: Other Personnel = fixed, 3-year average Other Expenses fixed, 3-year average Repairs = $1 per rental Utilities = fixed $1,350 Check figure: 2018 Total Budgeted Profit = $248,537 For D.2.2, use the Room Amenities note in the case for guidance. The total amounts will not tie to a budgeted line item created in D.2.1 because room amenities are only part of Supplies. You can ignore the comment on the "Other supplies". In January, for both products, assume beginning inventory equals January's ending inventory. Check figure: August Total $ Purchases = $1,023 Case E We are now in 2019, looking back at the 2018 budgets built in Case D and completing variance analyses. Level-0 variance is static budget vs. actuals for the profit line only, and Level-1 is the same comparison across all line items. You can combine these two levels into one analysis, as provided in the template. Ignore the comment on building multiple flex budgets. For all variances, use positive differences for favorable and negative differences as unfavorable. Check figure: Total Expenses variance = $33,095 unfavorable Level-2 variances require that you build a flex budget based upon 2018 actual rooms rented to generate the sales volume and flexible budget variances. The provided template is very similar to what we have done in the past; however, it is separated by expense categories like the budget, not variable and fixed. The flex budget should be based upon actual rooms rented, given in the case. Use the same rates provided in Case D to build the flex budget; not all expenses are variable! Check figure: Revenue sales volume variance = $67,108 unfavorable Check figure: Profit flexible budget variance = $25,356 unfavorable Based upon the level-2 variances, make at least four observations about what the results are telling you. As part of those observations, use the market information provided in the case background to help better understand what is influencing the revenue variances. For E.2.2, housekeeping is a personnel expense, so you are calculating direct labor variances, further breaking down the flexible budget variance for this line item. Use data given here and the Housekeeping note in Case D to get AP, AQ, SP, and SQ-some are given, and others need to be calculated based upon those numbers given and work done to build the flex budget. Check figure: your two variances should net to the total level-2 variance for this line item. Based upon the level-3 variances, make at least two more observations about what the results are telling you. Case F We have not spent any time on these ratios, but these should be familiar from financial accounting and can also be found online. Allocated corporate expenses are corporate-level expenses that must be shared across all the hotels and incorporated into net operating income (NOI) - hotel managers do not like this but is often the case with segment reporting. When calculating NOI, this total should be before interest and taxes. You can ignore RI and EVA. What observations can you make from these ratios? What would be helpful to know to make these measures more meaningful?