use scattergraph method, high low method, and the least square regression

247 Cost-Volume-Profit Relationships EXHIBIT SA-5 A Scattergraph Plot for Brentine Hospital Using Microsoft Excel 5:2.000 $10,000 58,000 Maintenance cost 56.000 54.000 52.000 2,000 2000 6.000 4000 Patient Day To prepare a scattergraph plot in Excel, begin by highlighting the data in cells B4 through CIO (as shown in Exhibit 5A-4). From the Charts group within the Insert tab, select the "Scatter" subgroup and then click on the choice that has no lines connect ing the data points. This should produce a scattergraph plot similar to the one shown in Exhibit SA-5. Notice that the number of patient-days is plotted on the X-axis and the maintenance cost is plotted on the Y-axis. As we saw verified earlier in Exhibit SA-1. the data is approximately linear, so it makes sense to proceed with estimating a regres- sion equation that minimizes the sum of the squared errors. To determine the intercept a, the slope b, and the R', begin by right clicking on any data point in the scattergraph plot and selecting "Add Trendline." This should pro- duce the screen that is shown in Exhibit 5A-6. Notice that under "Trend/Regression Type" you should select "Linear." Similarly, under "Trendline Name" you should select "Automatic." Next to the word "Backward you should input the lowest value for the independent variable, which in this example is 5000 patient-days. Taking this particular step instructs Excel to extend your fitted line until it intersects the Y-axis. Finally, you should check the two boxes at the bottom of Exhibit 5A-6 that say "Display Equation on chart" and "Display R-squared value on chart." Once you have established these settings, then click "Close." As shown in Exhibit SA-7, this will automatically insert a line within the scattergraph plot that minimizes the sum of the squared errors. It will also cause the estimated least-squares regression equation and to be inserted into your scattergraph plot. Instead of depicting the results using the form Y = a + bx, Excel uses an equivalent form of the equation depicted as Y = bx + a. In other words, Excel reverses the two terms shown to the right of the equals sign. So, in Exhibit 5A-7. Excel shows a least-squares regression equation of y = 0.7589x + 3,430.9. The slope b in this equation of $0.7589 represents the estimated variable maintenance cost per patient-day. The intercept a in this equation of $3,430.90 (or approximately $3,431) represents the estimated fixed monthly maintenance cost. Note that the R is approximately 0.90, which is quite good and indicates that 90% of the variation in maintenance cost is explained by the variation in patient-days. * To insert labels for the X-axis and Y-axis, go to the Layout tab in Excel. Then, within the Labels group, select Axis Titles. mi B EXHIBIT 5A-4 The Least-Squares Regression Worksheet for Brentiine Hospital d Maintenance Patient 1 Cost 2 Days y 3 Month 4 January 5,600 $ 7,900 5 February 7,100 $ 8,500 6 March 5,000 $ 7,400 7 April 6,500 $ 8,200 8 May 7,300 $ 9,100 9 June 8,000 $ 9,800 10 July 6,200 $ 7,800 11 Least squares regression