# 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