Serial Case C6-72Calculate and
Serial Case
- C6-72Calculate and compare cost estimates using high-low andregression methods (Learning Objectives 4 & 5)
This case is a continuation of theCaesars Entertainment Corporation serial case that began inChapter 1. Refer to the introductory story in Chapter 1, herefor additional background. (The components of the Caesars serialcase can be completed in any order.)
Caesar Entertainment Corporation’sForm 10-K contains a variety of data in addition to financialstatements. Below is a list that contains Caesars’ food andbeverage costs (adapted) taken from its Statements of Operationsfor the past 22 years. In addition, the number of hotel rooms andsuites owned by Caesars at the end of each of those 22 years hasbeen gathered from other information provided in the Form10-Ks.
|
Year ended |
Food and beverage costs |
# of hotel rooms & suites |
|
12/31/2014 |
$ 694,000,000 |
39,218 |
|
12/31/2013 |
$ 639,000,000 |
42,200 |
|
12/31/2012 |
$ 634,000,000 |
42,710 |
|
12/31/2011 |
$ 665,700,000 |
42,890 |
|
12/31/2010 |
$ 621,300,000 |
42,010 |
|
12/31/2009 |
$ 596,000,000 |
41,830 |
|
12/31/2008 |
$ 639,500,000 |
39,170 |
|
12/31/2007 |
$ 716,500,000 |
38,130 |
|
12/31/2006 |
$ 697,600,000 |
38,060 |
|
12/31/2005 |
$ 482,300,000 |
43,060 |
|
12/31/2004 |
$ 278,100,000 |
17,220 |
|
12/31/2003 |
$ 255,200,000 |
14,780 |
|
12/31/2002 |
$ 240,600,000 |
14,551 |
|
12/31/2001 |
$ 232,400,000 |
13,598 |
|
12/31/2000 |
$ 228,000,000 |
11,562 |
|
12/31/1999 |
$ 218,600,000 |
11,760 |
|
12/31/1998 |
$ 116,600,000 |
11,685 |
|
12/31/1997 |
$ 103,600,000 |
8,197 |
|
12/31/1996 |
$ 95,900,000 |
6,478 |
|
12/31/1995 |
$ 91,500,000 |
5,736 |
|
12/31/1994 |
$ 82,800,000 |
5,367 |
|
12/31/1993 |
$ 76,500,000 |
5,348 |
|
Caesars Entertainment Corporation Selected data from Form 10-K(adapted) |
||
Requirements (use excel)
- Using the high-low method, find the following cost estimates:
- Variable food and beverage cost per hotel room/suite
- Fixed food and beverage cost per hotel room/suite
- Perform a regression analysis using Excel. Use # of hotel rooms& suites as the X and the Food and beverage costs as the Y inyour regression analysis.
- What is the estimated variable food and beverage cost per hotelroom/suite?
- What is the estimated fixed food and beverage cost per hotelroom/suite?
- In your opinion, is the number of hotel rooms and suites a goodpredictor of Caesars’ food and beverage costs? Why or why not?
Answer:
High-low method
a. Variable Cost per unit = ($482300000 – 76500000) / (43060 -5348) = $10760.50 per room
b. Fixed Cost = $482300000 – 43060 x 10760.50 = $18,952,870
Regression Analysis
| SUMMARY OUTPUT | ||||||||
| Regression Statistics | ||||||||
| Multiple R | 0.967549051 | |||||||
| R Square | 0.936151166 | |||||||
| Adjusted R Square | 0.932958724 | |||||||
| Standard Error | 64792547.67 | |||||||
| Observations | 22 | |||||||
| ANOVA | ||||||||
| df | SS | MS | F | Significance F | ||||
| Regression | 1 | 1.23E+18 | 1.23E+18 | 293.2399 | 2.04E-13 | |||
| Residual | 20 | 8.4E+16 | 4.2E+15 | |||||
| Total | 21 | 1.32E+18 | ||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
| Intercept | 10021329.08 | 25746408 | 0.389232 | 0.701218 | -4.4E+07 | 63727395 | -4.4E+07 | 63727395 |
| X Variable 1 | 15283.50 | 892.5063 | 17.12425 | 2.04E-13 | 13421.76 | 17145.23 | 13421.76 | 17145.23 |
a. Variable Cost per room = $15283.50 per room
b. Fixed Cost = $10,021,329
c. R Square is 0.94 or 94% which explains 94% of thevariability, therefore number of hotel rooms is a goodpredictor
"Our Prices Start at $11.99. As Our First Client, Use Coupon Code GET15 to claim 15% Discount This Month!!"
