This challenge requires the data analysis ToolPak. To learn how to install it, click here.
Overview
You work at Park’s Production, a simple manufacturing facility that makes one product. The facility has data on its first 500 production runs. The company doesn’t have much experience with managerial accounting, so they took a chance and hired you since you’ve had a class or two about the subject. At your most recent meeting with the production manager, you learned that the company is planning to allocate overhead based on headcount (at a rate of $1,000/head). You know that good cost drivers directly contribute to the cost being allocated. For example, headcount may be a good driver for a supervisor’s salary since supervisors are directly responsible for the people they oversee. Even though you’re new, you have your doubts about headcount being a good driver for all overhead costs such as depreciation, rent, supervisor salaries, etc.
You have some experience with the data analysis ToolPak and regression analysis and are ready to put it to use. You know that something is statistically significant if the p-value is less than 0.05. You also know that “R Square” in the regression output denotes a high correlation for values between +/- 0.5 and +/- 1. Values between +/- 0.29 and 0 denote a weak or nonexistent relationship. The best drivers, as a result, will show a “R Square” above 0.5 (since we expect a positive relationship between a driver and its cost).
It’s time to find out just how good headcount is as a cost driver—and if there’s a better option.
Instructions
Take a look at the rate determined in cell L1. Do you think this is the best way to determine the rate using headcount as a driver? Try another calculation (use columns H, I, J as needed) to see if there’s an argument that $1,000 is even the right number if using headcount.
Suggestions and Hints
Calculate the rate of manufacturing overhead/headcount for each production run, then average those. Is it the same as the initial estimate?
Now run a regression analysis on headcount (X variable) and manufacturing overhead (Y variable). Output the results to a new worksheet (see suggestions and hints for what the regression inputs should look like). What do you learn from the p-value? (Cell E18) What do you learn from the R Square? (Cell B5)
Suggestions and Hints
Once you open the analysis ToolPak, choose “Regression”. Be sure to include labels (check the checkbox) because that increases readability.
Now run a regression on direct labor hours (X variable) and direct labor (Y variable). Are the results what you would expect?
Suggestions and Hints
You should notice that direct labor hours and direct labor costs are perfectly correlated. This makes sense for a place that pays hourly wages.
Try at least on more regression to see if there’s a variable that is better than headcount at predicting manufacturing overhead costs. Put your R Square in the comments!
Suggestions and Hints
Try machine hours and manufacturing overhead.
Bonus: Create a column to estimate the overhead expense for each production run. You’ll need the intercept coefficient (B17 of the output), the x variable coefficient (B18 of the output), and the driver for each production run. What else could you do to impress the production manager?
I used the machine hours on my regression table and the r coefficient was .91 so it is a better indicator than head count. The file was too big so I attached a couple screenshots
I chose machine hours as the best estimate of overhead expenses.
This makes sense both from a logical standpoint as well as supporting that idea with the regression. There is a high correlation between machine hours and overhead unlike the number of employees.
The average is 1,138.55 so I think the 1,000 is a decent estimate for the proposed manufacturing overhead rate.
From the small p-value, I conclude the hypothesis is statistically significant and I would conclude from the R Square that there is not much of a connection between the manufacturing overhead and headcount because the R Square is so small (0.0126).
Yes, the results are perfectly correlated which makes sense because the direct labor hours and direct labor itself should be highly connected with each other.
Yes, I did machine hours and manufacturing overhead and found the R Square to be 0.9135 which is higher than the previous R Square of 0.0126.
After doing some quick math analyses, I learned that machine hours (even without a regression analysis) appears to share the closest connection with manufacturing overhead. After doing a regression, I found that it very closely correlates to manufacturing overhead, much more than any of the other potential drivers. Challenge61_Data_Jeffrey_Winters.xlsx (69.6 KB)
My R-square value is 0.9133 for “Machine Hours” and “Manufacturing Overhead”. While the p-value was incredibly small on all of the regressions (meaning the observed relationship is statistically significant), it does not attest to the practical significance of the results. The high R-square value indicates that the model using machine hours is a better fit.
Machine hours were the best fit for allocating manufacturing overhead as it is closely correlated to overhead cost. I got an adjusted r squared of 0.91339 for machine hours.
0301_Taylor_Challenge61_Data.xlsx (89.9 KB)
Machine Hours is the best cost driver with MOH with an R square value of .913. The R square value between headcount is much lower than the relationship between Machine Hours and MOH. I was also able to create a graph showing a fairly linear relationship between Machine Hours and MOH.
