* Using "Excel" do the following problem using the High-Low

Method and Regression Analysis and turn in the Excel file

including your recommendations.

*To run the regression using the Excel Program, use Tools, Data

Analysis, Regression and choose x cost driver and y costs we

want to predict. If you use regression for the first time, click

File, Options, in left column then click Add-ins, Analysis

toolpak, Regression just for setting up the regression program

once. If you use Apple or Mac computer, there are instructions

in Blackboard how to run regression analysis.

Problem: The King Corporation is developing a model to explain

and predict overhead costs. It produces only one product-line

so that a simple count of the number of units produced each

month may be a good measure of activity to begin with. The

company has collected data for the past twelve months:

Month Overhead Production

Cost Units

---- ------------ ----------

1 $254,500 40,000

2 184,500 24,000

3 165,400 21,000

4 178,000 23,000

5 192,000 25,000

6 225,000 31,000

7 210,000 28,000

8 230,000 30,000

9 195,000 29,000

10 224,000 36,000

11 200,000 32,000

12 240,000 38,000

Required:

1. Open a spreadsheet and enter the data.

2. Create another worksheet and enter High-Low method formula

to calculate variable cost per unit and total fixed costs.

Use y = a + b(x) formula. Enter a (total fixed costs) in

Cell 20B and b (variable cost per unit) in Cell 20C.

3. Create another worksheet and run regression (Least Squared

Method) and save the output.

4. Create another worksheet and save both outputs by linking

worksheets. Determine whether the High-Low method or

Regression Analysis is better to predict monthly overhead

costs. Explain which model is the better? Why or why not?

5. Use the results of your regression analysis in the y = a +

b(x) formula to predict overhead costs for January and

December of next year. Use a 12-month average of your

current year production units for the projection for both

months. Do you think these predicted overhead costs are

reasonable?