Question

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.

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

Answer 1

74 75 Maintenance Cost 76 Patient Days 5600 7100 7900 77 8500 78 Scattergraph Month January February March April May June July 5000 7400 79 6500 8200 9100 80 7300 81 8000 9800 82 6200 7800 83

83 84 Maintenance Cost 85 86 12000 87 10000 88 89 8000 90 91 6000 92 93 4000 94 2000 95 96 97 0 0 1000 2000 3000 4000 5000 6000 7000 8000 9000 98 99

100 High Low method 101 102 103 Patient Days 8,000 5,000 3,000 High Activity Level Low Activity Level Change Maintenance Cost 9,800 7,400 2,400 104 105 106 107 Variable Cost Per Unit 0.80 Per Patient Days 108 109 Calculation of the fixed cost 110 111 112 Number of Units Total Cost Less Variable component Fixed component 8,000 9,800 6,400 3,400 5,000 7,400 4,000 3,400 113 114 115 116 Total Cost Equation a + b*x (Where x is the patient days) 117 $3400+.80 *x 118 119

Least Square Method 120 121 SUMMARY OUTPUT 122 123 124 125 0.946795243 0.896421232 126 Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations 127 0.875705478 128 291.7398734 129 7 130 131 ANOVA 132 df 1 Regression Residual Total SS MS F gnificance F 3683010.66 3683011 43.27244 0.001219 425560.7685 85112.15 4108571.429 5 6 133 134 135 136 137 138 139 140 Intercept Patient Days Coefficients 3430.942806 0.75893655 Standard Error t Stat P-value Lower 95%Upper 95%ower 95.09pper 95.0% 761.2417664 4.507034 0.006358 1474.109 5387.777 1474.109 5387.777 0.115371827 6.578179 0.001219 0.462364 1.055509 0.462364 1.055509

Total Cost Equation a + b*x (Where x is the patient days) 141 142 143 $3431+.76 *x 144 145 146