2 3.33 points eBook 10 Print Problem 5-60 (Algo) Interpretation of Regression Results: Simple Regression Using a Spreadsheet (LO 5-4, 5, 9) Hartman Company's Lucas plant manufactures thermostatic controls. Plant management has experienced fluctuating monthly overhead costs and wants to estimate overhead costs accurately to plan its operations and its financial needs. Interviews with plant personnel and studies reported in trade publications suggest that overhead in this industry tends to vary with labor-hours. A member of the controller's staff proposed that the behavior pattern of these overhead costs be determined to improve cost estimation. Another staff member suggested that a good starting place for determining cost behavior patterns is to analyze historical data. Following this suggestion, monthly data were gathered on labor-hours and overhead costs for the past two years. No major changes in operations occurred over this period of time. The data are shown in the following table. Labor-Hours Overhead Costs $2.741.720 Month 251.650 2 238, 550 References 2.375, 480 3 193.100 2.400, 400 4 271.550 2.590, 870 324.050 3.072.070 6 291.050 2.618. 420 7 271.550 2.480, 490 251.670 9 232.025 2,745,810 2,821,600 10 343,700 3,437, 960 186.050 2.189,590 12 232.000 2,550, 930 13 382.920 3.603,970 14 376,550 3,405,040 15 291.040 3,016, 750 16 396.050 17 356,670 3.638.590 3,554, 150 18 323.870 3. 191,070 20 21 283283 19 389,495 317, 300 3.401.970 3,219,770 343,560 336, 990 382,930 376.370 3,495,680 3,207, 510 3.600,880 3,636,900 Required: a. Use the high-low estimation method to estimate the overhead cost behavior (fixed and variable portions components of cost) for the Lucas plant c. Using Excel, compute regression coefficients to describe the overhead cost equation. d. Use the results of your regression analysis to develop an estimate of overhead costs assuming 410,000 labor-hours will be worked next month.