Davy Company is developing a cost function to estimate the manufacturing overhead rate for next year. You are the management accountant in charge of this project. You have discussed possible cost drivers with several engineers and managers familiar with operations of the company. Based on these discussions, you have collected data on overhead costs (in constant dollars), machine hours, and setups for the last 60 months operations. (To save you some time, I have put the data in an Excel file. Access the file from Canvas

Additional information: The factory was completely renovated in month 27, including the replacement of all of its machinery. The residuals were normally distributed, had a Durbin Watson statistic of 1.95, and showed a constant variance. Machine hours and setups may be significantly correlated. One of your co-workers is very familiar with multiple regression analysis and suggested using correlation analysis in Excel to explore the issue.

a. Determine a cost function of the form y = a + bx1 + cx2, where x1 is machine hours and x2 is number of setups over the appropriate period. Should the appropriate time period be all 60 months, or just the months after the renovation? Why?

b. Evaluate the cost function for multi-collinearity by completing a correlation analysis on the two independent variables over the appropriate time period.

c. Do two more regression analyses over the appropriate time period. On one, use setups. On the other, use hours as the independent variable.
d. Which equation is most appropriate? Why?

 

Data for Multiple Regression Analysis Setups head Cost 48 $ 18,364 61 $ 18,366 47 $ 18,687 52 $ 20,701 65 $ 24,204 73 $ 27,722 64 $ 23,904 58 $ 21,108 58 $ 23,710 58 $ 22,718 47 $ 20,689 42 $ 18,383 58 $ 18,366 44 $ 18,679 49 $ 20,722 62 $ 24,217 70 $ 27,706 61 $ 23,930 55 $ 21,107 58 $ 23,706 58 $ 22,716 44 $ 18,675 49 $ 20,701 62 $ 24,208 70 $ 27,706 61 $ 23,903 55 $ 21,092 58 $ 23,705 Months Hours Setups Overhead Cost Months 46 $ 53 $ Hours 113 129 17,413 17,904 24,695 33 118 2 34 167 189 64 $ 58 $ 60 $ 69 $ 46 $ 77 $ 54 $ 72 $ 66 $ 50 $ 58 $ 71 $ 3 35 108 130 148 173 4 140 19,065 20,656 36 37 182 6. 24,658 16,267 38 159 7 121 39 164 8 199 25,683 40 143 147 20,265 41 164 22,676 24,682 16,673 10 173 194 118 42 133 11 43 145 12 44 118 13 155 20,767 45 167 27,669 27,890 23,901 14 203 193 46 108 140 182 15 79 $ 60 $ 63 $ 52 $ 63 $ 53 $ 47 16 189 48 17 157 22,706 49 159 18 134 17,693 50 164 19 174 23,666 51 143 20 155 20,662 52 164 21 128 48 $ 18,360 53 133 177 118 64 $ 50 $ 55 $ 22 18,370 18,673 20,700 24,201 27,717 54 108 23 55 145 24 150 56 187 192 169 68 $ 76 $ 6 $ 61 $ 64 $ 25 57 164 26 56 169 27 164 23,913 59 148 28 143 21,091 60 169 29 164 23,696 64 $ 63 $ 53 $ 30 133 22,708 23,661 20,663 31 164 32 145