This spreadsheet assignment will require you to use multiple approaches to estimating manufacturing overhead

costs for a fictitious company, including the high-low method and regression analysis

This exercise will require you to perform regression analysis to determine the appropriate cost driver(s) to use
to forecast manufacturing overhead costs. During this exercise, you will run seven (7) separate regression analyses.
The instructions provided below are specific to the Excel software.

The following data will be used to forecast manufacturing overhead costs for next year.
You do not need to refer to the text for any other additional data.
(Repeated from Column C)
2010 Manuf. Overhead Dir. Labor Hours Product Set-ups Machine Hours Dir. Labor Hours
January $186,027 27,174 29 5,472 27,174
February $178,727 26,602 34 5,184 26,602
March $188,907 28,259 22 5,688 28,259
April $180,459 27,437 25 5,400 27,437
May $195,084 28,685 31 5,832 28,685
June $196,286 28,977 29 5,904 28,977
July $195,199 30,169 26 6,048 30,169
August $194,307 29,363 30 5,760 29,363
September $192,036 28,457 36 5,688 28,457
October $193,920 28,306 23 5,976 28,306
November $184,303 27,409 16 5,544 27,409
December $178,948 26,180 21 5,292 26,180
Total $2,264,202 337,018 322 67,788
$/unit of driver $6.718 $7,031.684 $33.401
per labor hour per set-up per machine hour
Plans for January, 2011, are to produce two products, Product W1 and Product Q2. Use the following information
to predict total overhead cost for January using three different versions of the HIGH-LOW method:


Planned Volume Planned Planned Planned
Product (# of units) Direct Labor Hours Product Set-ups Machine Hours
W1 90,000 22,500 5 4,500
Q2 10,000 2,500 25 500
Total 100,000 25,000 30 5,000


HIGH-LOW METHOD: Using the 12-months' data for 2009 above, do high-low analysis to estimate parameters for the cost function based on:
Direct Labor Hours, Set-ups, and Machine Hours. The first is done for you as an example.

STEP 1: Esimate Cost Equation STEP 2: Use Cost Equation to make a prediction

Ovhd Cost DLHours
High 195,199 30,169 (a) Predict January, 2011, overhead costs based on DLHours
(a) DLHours Low 178,948 26,180
Difference 16,251 3,989 Predicted Total Overhead Cost: $174,141

Estimated Unit Variable Cost (v) > 4.074 Total Cost Unit Cost
Estimated Total Fixed Cost (F) > 72,292 W1 156,727 1.741
Cost Function (TC = vQ + F) > TC = 4.074Q + 72,292 Q2 17,414 1.741


Ovhd Cost Machine Hours
High (c) Predict January, 2011, overhead costs based on Machine Hours
(c) Machine Hours Low
Difference Predicted Total Overhead Cost: $

Estimated Unit Variable Cost (v) > Total Cost Unit Cost
Estimated Total Fixed Cost (F) > W1
Cost Function (TC = vQ + F) > Q2


Next this exercise will require you to perform regression analysis to determine the appropriate cost driver(s) to use
to forecast manufacturing overhead costs. During this exercise, you will run seven (7) separate regression analyses.
The instructions provided below are specific to the Excel software.

(1) Set up Excel to run Regression Analysis:
For Windows XP Click on "Tools". If "Data Analysis" appears on the Tools menu (the last item) go to step (2).
If "Data Analysis" does NOT appear on the Tools menu, click on "Add-Ins..."
then click on the first line "Analysis ToolPak" -- an X will appear in the box beside it -- click OK.
For VISTA Click on "Data". If "Data Analysis" appears on the Data menu (at the far right) go to step (2).
If "Data Analysis" does NOT appear on the Data menu, Click the Microsoft Office Button (top right circle),
Click "Excel Options." Click "Add-ins." In the "Manage" box, select "Excel Add-ins", then click "Go".
(2) You are ready to run the regression analyses. Click on "Data Analysis" and scroll down to click on "Regression."
then click on OK--the Regression dialogue box will appear.
(3) In the "Input Y Range" area, type c12:c24--the range of the y-variable (the cost item to be predicted) and its label. NOTE: Instead of typing, you can just click and drag.
(4) In the "Input X Range" area, type d12:d24--the range of one possible cost driver (Dir. Labor Hours) and its label. NOTE: Instead of typing, you can just click and drag.
(5) Click on the "Labels" -- do this to let the computer know the top line of the ranges is supposed to be a label.
(6) Click on the "Output Range" and enter b200 (as the top corner of the regression report area), then click OK.
(7) The Regression Report (#1) has been completed for you and appears near the designated output area (see m160) below.
(8) Repeat Steps (2) through (6) for each cost driver/set of cost drivers as follows (the y varaiable--cost--will not change).
Use a different Output Range for each Regression you run:

Regression
Report Input X Area Output Range
#2 d12:d24 b250 NOTE: The x-variable is always the cost driver(s)
#3 e12:e24 b300 and the y-variable is always the cost
#4 c12:d24 b350 that you are trying to predict.
#5 d12:e24 b400
#6 e12:f24 b450
#7 c12:e24 b500
******************************************************************************************************************************************************************************************


(9) Complete the following table from the information provided in each of the Regression Reports (#1 has been completed for you):

Report # Y variable R-square Adj. R-square Fixed Cost X variable(s) Unit Var Cost
#1 Manuf. Overhead 0.815 0.797 41,787 Dir. Labor Hours 5.230

#2 Manuf. Overhead Product Set-ups

#3 Manuf. Overhead Machine Hours

#4 Manuf. Overhead Dir. Labor Hours
Product Set-ups

#5 Manuf. Overhead Product Set-ups
Machine Hours

#6 Manuf. Overhead Machine Hours
Dir. Labor Hours

#7 Manuf. Overhead Dir. Labor Hours
Product Set-ups
Machine Hours


(10) What is the cost function that would be used to estimate future costs for each set of cost driver(s) (#1 -- #7) ?
(NOTE: A cost function is a mathematical expression in the form cost = a1x1 + a2x2 + . . . + anxn + F).
Regression
Report
# 1 Manufacturing Overhead Cost = (5.230 x DLHrs) + 41,787
# 2 Manufacturing Overhead Cost = ?
# 3 Manufacturing Overhead Cost = ?
# 4 Manufacturing Overhead Cost = ?
# 5 Manufacturing Overhead Cost = ?
# 6 Manufacturing Overhead Cost = ?
# 7 Manufacturing Overhead Cost = ?

(11) Using Regressions 1-3, which single cost driver is the best predictor of Manufacturing Overhead? Why?


(12) Using Regressions 1-7, which cost driver or set of cost drivers is the best predictor? Why?


(13) Plans for January, 2010, are to produce two products, Product W1 and Product Q2. Given the following information,
about each product, calculate (a) estimated total overhead cost for the month of January using the cost functions for
Regression Reports #3 and #5; (b) total overhead cost from (a) allocated to each product; and (c) overhead cost per unit
for each product.

Total Predicted Total Predicted
Planned Volume Planned Planned Planned Overhead Cost Overhead Cost
Product (# of units) Direct Labor Hours Product Set-ups Machine Hours (using Report #3) (using Report #5)
W1 90,000 22,500 5 4,500
Q2 10,000 2,500 25 500 (a) Total Cost (a) Total Cost
Total 100,000 25,000 30 5,000 all products all products


(b) Total Cost (b) Total Cost
W1 W1
Q2 Q2

W1 (c) Unit Cost (c) Unit Cost
Q2 W1
Q2

Please note that we don't do students' homework for them. Be sure to go back into your textbook or use a good search engine. http://hanlib.sou.edu/searchtools/

Once YOU have attempted to answer YOUR questions, please re-post and let us know what you think. Then someone here will be happy to comment on your thinking.

To estimate the manufacturing overhead costs using the high-low method and regression analysis, follow these steps:

1. Use the high-low method to estimate the cost equation based on different cost drivers. The first example is given for Direct Labor Hours.

- High: Ovhd Cost = $195,199, DLHours = 30,169
- Low: Ovhd Cost = $178,948, DLHours = 26,180

Difference: DLHours = 3,989, Ovhd Cost = $16,251

- Estimated Unit Variable Cost (v): 4.074
- Estimated Total Fixed Cost (F): $72,292
- Cost Function (TC = vQ + F): TC = 4.074Q + $72,292

2. Repeat the high-low analysis for other cost drivers, such as Product Set-ups and Machine Hours, following the same steps as above.

3. Perform regression analysis using Excel to determine the appropriate cost driver(s) to forecast manufacturing overhead costs. Run seven separate regression analyses based on different combinations of cost drivers.

- Set up Excel to run Regression Analysis:
- Click on "Data" (for VISTA) or "Tools" (for Windows XP).
- If "Data Analysis" appears, click on it. If not, click on "Add-Ins," select "Analysis ToolPak," and click OK.

4. Run the regression analyses for each cost driver/set of cost drivers using the following input and output ranges:

Regression Report #2:
- Input X Range: Product Set-ups (e12:e24)
- Output Range: b250

Regression Report #3:
- Input X Range: Machine Hours (f12:f24)
- Output Range: b300

Regression Report #4:
- Input X Range: Direct Labor Hours (d12:d24), Product Set-ups (e12:e24)
- Output Range: b350

Regression Report #5:
- Input X Range: Direct Labor Hours (d12:d24), Machine Hours (f12:f24)
- Output Range: b400

Regression Report #6:
- Input X Range: Machine Hours (f12:f24), Direct Labor Hours (d12:d24)
- Output Range: b450

Regression Report #7:
- Input X Range: Direct Labor Hours (d12:d24), Product Set-ups (e12:e24), Machine Hours (f12:f24)
- Output Range: b500

5. Complete the table for each regression report with the following information: Y variable, R-square, Adjusted R-square, Fixed Cost, X variable(s), and Unit Variable Cost.

6. Determine the cost function for each set of cost driver(s) based on the regression analysis:

Cost Function for Regression Report #2 to #7:
- Manufacturing Overhead Cost = (v1 x X1) + (v2 x X2) + ... + (vn x Xn) + F

7. Analyze the regression reports (#1 to #7) to determine which cost driver(s) is the best predictor of Manufacturing Overhead.

8. Calculate the estimated total overhead cost for the month of January using the cost functions from Regression Reports #3 and #5.

9. Allocate the total overhead cost to each product based on the planned volume (number of units) for W1 and Q2.

10. Determine the overhead cost per unit for each product by dividing the allocated overhead cost by the planned volume for each product.

Note: The calculations and analysis should be performed in Excel as instructed.

To estimate manufacturing overhead costs for the fictitious company using the high-low method and regression analysis, follow these steps:

1. High-Low Method:

Step 1: Estimate Cost Equation
- Choose a cost driver, such as Direct Labor Hours, Set-ups, or Machine Hours.
- Identify the highest and lowest values for the cost driver and their corresponding overhead costs.
- Calculate the difference between the high and low values of the cost driver and overhead costs.
- Use the formula (Overhead Cost High - Overhead Cost Low) / (Cost Driver High - Cost Driver Low) to calculate the estimated unit variable cost (v).
- Use the difference between overhead costs and the estimated unit variable cost to calculate the estimated total fixed cost (F).
- The cost function will be TC = vQ + F, where TC is the total cost, Q is the quantity of the cost driver, v is the estimated unit variable cost, and F is the estimated total fixed cost.

Step 2: Use Cost Equation to Make a Prediction
- Use the cost function obtained from Step 1 to predict the overhead costs for a specific scenario by substituting the values of the cost driver in the equation.

2. Regression Analysis:
- Set up Excel to run Regression Analysis by enabling the Data Analysis ToolPak if it's not already enabled.
- Click on "Data Analysis" and select "Regression."
- In the Regression dialogue box, enter the input range for the y-variable (cost) and its label in the "Input Y Range" area.
- Enter the input range for the x-variable(s) (cost driver(s)) and their label(s) in the "Input X Range" area.
- Check the "Labels" box to indicate that the top line of the ranges is a label.
- Enter the output range for the regression report.
- Click OK to run the regression analysis.
- Repeat the steps for each cost driver or set of cost drivers for which you want to perform regression analysis.

3. Complete the Table:
- After running each regression analysis, complete the table by recording the report number, y-variable, R-square, Adjusted R-square, fixed cost, x-variable(s), and unit variable cost for each regression report.

4. Determine the Cost Function:
- For each regression report, write the cost function based on the x-variable(s) and their coefficients obtained from the regression analysis.

5. Identify the Best Predictor:
- Compare the R-square and Adjusted R-square values for the different regression reports to determine which cost driver or set of cost drivers is the best predictor of Manufacturing Overhead.
- The higher the R-square and Adjusted R-square values, the better the predictor.

6. Calculate Estimated Total Overhead Cost:
- Using the cost functions from Regression Reports #3 and #5, substitute the planned volume, planned direct labor hours, planned product set-ups, and planned machine hours into the respective cost functions.
- Calculate the total overhead cost for the given information.

7. Allocate Overhead Cost to Each Product:
- Divide the total overhead cost obtained in Step 6 between the products based on their planned volumes.
- Allocate a proportionate amount of overhead cost to each product.

8. Calculate Overhead Cost per Unit:
- Divide the allocated overhead cost for each product by its planned volume to calculate the overhead cost per unit.

By following these steps, you will be able to estimate manufacturing overhead costs using the high-low method and regression analysis.