This spreadsheet assignment will require you to use multiple approaches to estimating manufacturing overhead
costs for a fictitious company, including the highlow 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 Setups 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 setup 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 HIGHLOW method:
Planned Volume Planned Planned Planned
Product (# of units) Direct Labor Hours Product Setups 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
HIGHLOW METHOD: Using the 12months' data for 2009 above, do highlow analysis to estimate parameters for the cost function based on:
Direct Labor Hours, Setups, 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 "AddIns..."
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 "Addins." In the "Manage" box, select "Excel Addins", 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 OKthe Regression dialogue box will appear.
(3) In the "Input Y Range" area, type c12:c24the range of the yvariable (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:d24the 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 varaiablecostwill 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 xvariable is always the cost driver(s)
#3 e12:e24 b300 and the yvariable 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 Rsquare Adj. Rsquare 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 Setups
#3 Manuf. Overhead Machine Hours
#4 Manuf. Overhead Dir. Labor Hours
Product Setups
#5 Manuf. Overhead Product Setups
Machine Hours
#6 Manuf. Overhead Machine Hours
Dir. Labor Hours
#7 Manuf. Overhead Dir. Labor Hours
Product Setups
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 13, which single cost driver is the best predictor of Manufacturing Overhead? Why?
(12) Using Regressions 17, 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 Setups 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 answer

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 repost and let us know what you think. Then someone here will be happy to comment on your thinking. 👍
 👎
 ℹ️
 🚩
👤Writeacher
Answer this Question
Related Questions

History
In the early to mid1800s, northern states had a thriving (agrarian / industrial) economy. They relied heavily on (farming / manufacturing) as well as on finance. In contrast, southern states had fewer (factories / resources). Their economy focused on

precalc
1. which of the following is a fourth degree polynomial function? select all that apply. a. f(x)= 4x^3  x^2 + 2x  7 b. f(x)= 5x^4 c. f(x)= 1 / 2x^4 + x^2 5 d. f(x)= 3x^4 + 2x^3 4x +1 2. which function below has the end behavior f(x) approaches neg.

pre calc
Select the function whose end behavior is described by f(x) approaches infinity as x approaches infinity and f(x) approaches neg. infinity as x approaches neg. infinity a. f(x)= 7x^9  3x^2  6 b. f(x)= 1/2x^3 c. f(x)= x^6  3x^3  6x^2 + x  1 d. f(x)=

language
Which is the best example of a complex sentence?(1 point) A. I ran into my teacher, and she did not give me my late assignment. B. I ran into my teacher later that day; she did not give me my assignment. C. Although my teacher did not give me the

Personal Finance
Check my work pls. 1. The components of ___ are variable costs and fixed costs. A. Entire cost B. Total cost* C. Complete cost D. Required cost 2. What is the margin of safety? A. How much sales can fall before a business starts making less 5% B. How much

Calculus
Find the limit lim as x approaches (pi/2) e^(tanx) I have the answer to be zero: t = tanx lim as t approaches negative infi e^t = 0 Why is tan (pi/2) approaching negative infinity is my question?

accounting
Logan Products computes its predetermined overhead rate annually on the basis of direct laborhours. At the beginning of the year, it estimated that 38,000 direct laborhours would be required for the period’s estimated level of production. The company

Calculus
The table below gives selected values of a twice differentiable function f(x) x. 7. 6. 4. 2. f(x). 0. 1. 2. 0 f'(x). 3. 2. 1. 7 What is the limit of f(3x1)/(x^2)4 as x approaches 2? What is the limit of f(f(x))/5x + 20 as x approaches 4?

Math
Assume it costs Microsoft $4,300 to manufacture 7 Xbox 360s and $8,290 to manufacture 14. Obtain the corresponding linear cost function. C(x)= (what formula do I use to get the cost function, because I can't get it) What was the cost to manufacture each

Managerial Accounting
Gardner Manufacturing Company produces a product that sells for $120. A selling commission of 10% of the selling price is paid on each unit sold. Variable manufacturing costs are $60 per unit. Fixed manufacturing costs are $20 per unit based on the current

finance
New project analysis You must evaluate a proposal to buy a new milling machine. The base price is $108,000, and shipping and installation costs would add another $12,500. The machine falls into the MACRS 3year class, and it would be sold after 3 years for

Ed tech Plz, plz help
The Second Industrial Revolution was not just marked by new inventions. New business practices were invented that transformed America’s economy. Between 1869–1910, the value of American manufacturing rose from $3 billion to $13 billion. The steel

career exploration
1. Which of the following is a true statement about the field of manufacturing? (1 point) A. Most manufacturing jobs require a college education. B. Most manufacturing jobs pay more than $50,000 per year. C. Most manufacturing jobs are expected to decline

Math Explanation
How can estimating be helpful before finding an actual product.

Maths
A number is chosen at random from the integers 10 to 30 inclusive .find the probability that the number is a) a multiple of 3 b) a multiple of 5 c) prime d) a perfect square.

Math
Which describes the end behavior of the graph of the function f(x)=2x^35x^2+x? a. f(x) approaches infinity as x approaches negative infinity and f(x) approaches infinity as x approaches infinity b. f(x) approaches negative infinity as x approaches

math
estimating with fractions and mixed numbers

Economics
PROBLEM SOLVING 1: "ANDREA'S SOFTWARE BUSINESS" I. Complete the following table: DO THE MATH Data Number of Programs Total Fixed Costs Total Variable Costs Total Costs Marginal Costs Average Fixed Costs Average Variable Costs Average Total Costs 0 $60 $0

Math
If the limit as x approaches 1 for (f(x)  7)/(x1)=8, evaluate the limit as x approaches 1 for f(x)

Math
Explain how estimating the quotient helps you place the first digit in the quotient of a division problem
Still need help?
You can ask a new question or browse existing questions.