Davy Company is developing a cost function to estimate the manufacturing overhead rate for next year. You have 60 months of data on overhead costs
Davy Company is developing a cost function to estimate the manufacturing overhead rate for next year. You have 60 months of data on overhead costs and cost drivers. You plan to use regression analysis to develop the cost function. You discussed possible cost drivers with several engineers and managers familiar with operations of the company. They all agreed that machine hours and setups were plausible cost drivers, but one manager observed that many of the machines were replaced and processes involving setups were reorganized in month 23 of your data. Inflation has been negligible during his time.
1. Do a multiple regression over the appropriate time period, where the cost function is of the form y = a + b1X1 + b2X2, with X1 as machine hours and X2 as setups. Should the appropriate period be all 60 months, or just the months after the renovation? Why? 2. Evaluate the cost function for multicollinearity by completing a correlation analysis on the two independent variables over the appropriate time period. 3. Do two univariate regressions over the appropriate time period, where the cost function is of the form y = a + bX, where X is a. Setups b. Hours 4. Which cost equation is most appropriate? Why? 5. Use the equation identified as most appropriate in part 4 to estimate overhead cost when machine hours are 150 or setups are 60. Limit the precision of the estimated cost to two decimal places.
Which cost equation is most appropriate? Why? Use the equation identified as most appropriate in part 4 to estimate overhead cost when machine hours are 150 or setups are 60. Limit the precision of the estimated cost to two decimal places.
Data for Multiple Regression Analysis Seruns SUMMARY OUTPUT (Hours and Setups) Regression Statistics Multiple R 0.857241245 R Square 0.734862552 Adjusted R Square 0.719266231 Standard Error 1462.982918 Observations 37 Hours Setups Hows 1 0.736005581 1 ANOVA ak F 47.11768728 Sawicane 1.58128E-10 Regression Residual Total Ays 100846882.2 2140319.018 2 34 36 201693764.3 72770846.6 274464610.9 Honths 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 Intercept Hours Setups Coeficients 5745.694313 2.084696153 279.7723547 Standard ETV 1 Star P-vale 1842.311833 3.118741469 0.003686743 16.53046196 0.126112395 0.900385775 43.18568242 6.478359008 2.07428E-07 Lower 42 Lone 2001.666205 9489.722421 - 31.50924441 35.67863671 192.0084888 367.5362207 Lower 2015 2001.666205 -31.50924441 192.0084888 Lopes 9489.722421 35.67863671 367.5362207 1 2 3 + 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 8 39 10 11 12 33 14 35 16 37 18 19 CO 31 F2 3 -4 -5 -6 -7 8 9 50 51 2 Hours Setups rerhead Cost 150 55 $ 20,700 192 681 $ 24,201 169 76 $ 27,717 164 67 $ 23,913 143 61 $ 21,091 164 64 $ 23,696 133 64 $ 22,708 164 63 $ 23,661 145 53 $ 20,663 118 48 $ 18,364 167 61 $ 18,366 108 47 $ 18,687 140 52 $ 20,701 182 65 $ 24,204 159 73 $ 27,722 164 64 $ 23,904 143 581 $ 21,108 164 58 $ 23,710 133 58 $ 22,718 145 47 $ 20,689 118 42 $ 18,383 167 58 $ 18,366 108 44 $ 18,679 140 49 $ 20,722 182 62 $ 24,217 159) 70 $ 27,706 164 61 $ 23,930 143 55| $ 21.107 164 581 $ 23,706 1331 58 $ 22,716 108 44 $ 18,675 145 49 $ 20,701 187 621 $ 24,208 164 70 $ 27,706 169 61 $ 23,903 148 55 $ 21,092 169 58 $ 23,705 SUMMARY OUTPUT [Hours) Regression Statistics Multiple R 0.638420322 R Square 0.407580508 Adjusted R Square 0.390654237 Standard Error 2155.379883 Observations 37 ANOVA ok Sanicance 2.12325E-05 Regression Residual Total Ays F 111866425.6 111866425.6 24.07975764 162598185.3 4645662.438 274464610.9 35 36 57 58 59 60 Intercept Hours Credients 10101.90685 80.90373047 Standard Ency 2526.985428 16.48703241 Star 3.997611833 4.907112964 P-valle 0.000314366 2.12325E-05 Lower 95% 4971.853697 47.43327526 LES 15231.96 114.3741857 Lower 95045 4971.853697 47.43327526 Lisney 15231.96 114.3741857 SUMMARY OUTPUT Setups) Recession Statistics Multiple R 0.857168902 R Square 0.734738527 Adjusted R Square 0.727159628 Standard Error 1442.268916 Observations 37 ANOVA ak Variance 1.27026E-11 Regression Residual Total 1 35 36 SS Ays F 201659724 201659724 96.94528258 72804886.95 2080139.627 274464610.9 Intercept Setups cestiver's 5828.269719 283.780825 Standard Ency Star 1697.646381 3.433147082 28.82170751 9.846079554 P-value 0.001550123 1.27026E-11 Lawey LE 2381.864342 9274.675096 225.2696481 342.2920019 Lower E0% 2381.864342 225.2696481 Le RE 9274.675096 342.2920019 Data for Multiple Regression Analysis Seruns SUMMARY OUTPUT (Hours and Setups) Regression Statistics Multiple R 0.857241245 R Square 0.734862552 Adjusted R Square 0.719266231 Standard Error 1462.982918 Observations 37 Hours Setups Hows 1 0.736005581 1 ANOVA ak F 47.11768728 Sawicane 1.58128E-10 Regression Residual Total Ays 100846882.2 2140319.018 2 34 36 201693764.3 72770846.6 274464610.9 Honths 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 Intercept Hours Setups Coeficients 5745.694313 2.084696153 279.7723547 Standard ETV 1 Star P-vale 1842.311833 3.118741469 0.003686743 16.53046196 0.126112395 0.900385775 43.18568242 6.478359008 2.07428E-07 Lower 42 Lone 2001.666205 9489.722421 - 31.50924441 35.67863671 192.0084888 367.5362207 Lower 2015 2001.666205 -31.50924441 192.0084888 Lopes 9489.722421 35.67863671 367.5362207 1 2 3 + 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 8 39 10 11 12 33 14 35 16 37 18 19 CO 31 F2 3 -4 -5 -6 -7 8 9 50 51 2 Hours Setups rerhead Cost 150 55 $ 20,700 192 681 $ 24,201 169 76 $ 27,717 164 67 $ 23,913 143 61 $ 21,091 164 64 $ 23,696 133 64 $ 22,708 164 63 $ 23,661 145 53 $ 20,663 118 48 $ 18,364 167 61 $ 18,366 108 47 $ 18,687 140 52 $ 20,701 182 65 $ 24,204 159 73 $ 27,722 164 64 $ 23,904 143 581 $ 21,108 164 58 $ 23,710 133 58 $ 22,718 145 47 $ 20,689 118 42 $ 18,383 167 58 $ 18,366 108 44 $ 18,679 140 49 $ 20,722 182 62 $ 24,217 159) 70 $ 27,706 164 61 $ 23,930 143 55| $ 21.107 164 581 $ 23,706 1331 58 $ 22,716 108 44 $ 18,675 145 49 $ 20,701 187 621 $ 24,208 164 70 $ 27,706 169 61 $ 23,903 148 55 $ 21,092 169 58 $ 23,705 SUMMARY OUTPUT [Hours) Regression Statistics Multiple R 0.638420322 R Square 0.407580508 Adjusted R Square 0.390654237 Standard Error 2155.379883 Observations 37 ANOVA ok Sanicance 2.12325E-05 Regression Residual Total Ays F 111866425.6 111866425.6 24.07975764 162598185.3 4645662.438 274464610.9 35 36 57 58 59 60 Intercept Hours Credients 10101.90685 80.90373047 Standard Ency 2526.985428 16.48703241 Star 3.997611833 4.907112964 P-valle 0.000314366 2.12325E-05 Lower 95% 4971.853697 47.43327526 LES 15231.96 114.3741857 Lower 95045 4971.853697 47.43327526 Lisney 15231.96 114.3741857 SUMMARY OUTPUT Setups) Recession Statistics Multiple R 0.857168902 R Square 0.734738527 Adjusted R Square 0.727159628 Standard Error 1442.268916 Observations 37 ANOVA ak Variance 1.27026E-11 Regression Residual Total 1 35 36 SS Ays F 201659724 201659724 96.94528258 72804886.95 2080139.627 274464610.9 Intercept Setups cestiver's 5828.269719 283.780825 Standard Ency Star 1697.646381 3.433147082 28.82170751 9.846079554 P-value 0.001550123 1.27026E-11 Lawey LE 2381.864342 9274.675096 225.2696481 342.2920019 Lower E0% 2381.864342 225.2696481 Le RE 9274.675096 342.2920019