The Cheyenne Hotel in Big Sky, Montana, has accumulated records of the total electrical costs of the hotel and the number of occupancy-days over the last year. An occupancy-day represents a room rented for one day. The hotel's business is highly seasonal with peaks occurring during the ski season and in the summer. Occupancy-Days 1,736 1.904 2,356 960 360 Month January February March April May June July August September October November December 744 Electrical Casts S 4.127 S 4. 207 S 5.083 S 2.857 S 1.871 S 2.696 S 4.670 S 5.148 S 2.691 S 1.588 S 2.454 S 3,529 2,108 2,406 840 124 720 1,364 Use the above data to calculate the equation of a cost line (Y = a + bx) using least-squares regression You have 2 options for doing this: Option 1: Calculate the equation of a cost line by hand. See the appendix to Ch. 5 for a tutorial on how to do this. Potential bonus points...10 Option 2: Calculate the equation of a cost line using Excel. See the appendix to Ch. 5 for a tutorial on how to do this. Potential bonus points...5 You can only choose one option. 93 :* Slow Hi Education Adhesive Attorud any for h ouse ilmadsmbun poco mthout A Caroliw. E the classroom No . production or Steps to follow when using High-Low Method Step 1: Pick high and low levels of activity. Step 2: Calculate change in cost and activity for high and low levels Step 3: Calculate slope (b) = Change in Cost/Change in Activity Step 4: Calculate fixed cost using cost formula: Y=a + b(x) **must pick high or low activity level. You know b (from step 3), total cost (Y), and total activity (X). Solve for a (total fixed cost) 94 SMOGOW. HAN Education Ados osea Antorud any for h ouse the classroom fumerismbuno p icomthout prin cantoflow. Encada. Nbrproduction or Read Only - You can't save changes to this file. 96 * AV FAA CHA Awany fur A vitaminer Karin Nanti wa CHAGUO EM 5-97 Least-Squares Regression Method A method used to analyze mixed costs if a scattergraph plot reveals an approximately linear relationship between the X and Y variables. This method uses all of the data points to estimate the fixed and variable cost components of a mixed cost. The goal of this method is to fit a straight line to the data that minimizes the sum of the squared errors. 97 t MANFAA Awhiny fur www www. miner Kam na wale wa HAI GIONE Least-Squares Regression Method (Part 2) Excel can be used to estimate the intercept (fixed cost) and slope (variable cost per unit) that minimize the sum of the squared errors. The cost analysis objective is the same: Y = a + bX Least-squares regression also provides a statistic, called the R2, which is a measure of the goodness of fit of the regression line to the data points. 98 :* MANFAA CHA Anny hur Kanincnt want w GIM Wwwm A ction or E Read Only - You can't save changes to this file. 98 * EMGOW Education A s sa further dismbuno pomoc without the Author w h any for house o le Grow. E the classroom ation Nbrproduction or Least-Squares Regression Method (Part 3) A regression line of the form Y = a + bx is fitted to the data, where a (the intercept) represents the total fixed cost and b (the slope) represents the variable cost per unit of activity. In our Maintenance Cost example, The slope bis $6.02, and represents the estimated variable maintenance cost per hour of maintenance. The intercept a is $4,395.67, and represents the estimated fixed monthly maintenance cost. Note that the R2 is approximately 0.92, which is quite good and indicates that 92% of the variation in maintenance cost is explained by the variation in maintenance hours. 99 * Glo Grow H Eucalan Adats reserva. Adhanzad any for madismbuno mica mithout the parti Canai of l corusc v the classroom No reproduction or ow. Ecation. 5-100 End of Chapter 5 100 * SMS . Education .cores seca Andronized any for stor use the classroom No uma dimbuno pomica mithout the c on conseil d'Idiow. AEC . production or