*BYU Student Author*: @Parker_Sherwood

*Reviewers*: @Jonathan_Weston, @Erick_Sizilio

*Estimated Time to Solve:* 15 minutes

We provide the solution to this challenge using:

Need a program? Click here.

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)
- 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?

**Data Files**

**Solution**