Ask questions and get helpful answers.

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

  1. 👍
  2. 👎
  3. 👁
  4. ℹ️
  5. 🚩

1 answer

  1. 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.

    1. 👍
    2. 👎
    3. ℹ️
    4. 🚩
    👤
    Writeacher

Answer this Question

Related Questions

Still need help?

You can ask a new question or browse existing questions.