Use Microsoft Excel to complete all requirements listed below for the following problem, and turn in your Excel file that includes your solutions for all problem requirements.  This problem involves cost behavior analyses by application of the High-Low Method and Regression Analysis.

 

REGRESSION REMINDER:  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, and at the bottom click on GO for Manage Excel Add-ins to check Analysis ToolPak just for making the regression program available in Excel.   If you use Apple or Mac computer, there are instructions in Blackboard how to run regression analysis.

 

PROJECT PROBLEM:

Island Enterprises 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	Units Produced	Total Overhead Cost
January	3,700	$15,300
February	4,300	$18,200
March	2,400	$13,100
April	4,000	$17,000
May	3,200	$14,900
June	1,400	$11,100
July	2,900	$13,300
August	3,500	$16,000
September	4,200	$17,400
October	3,900	$16,500
November	2,800	$13,200
December	2,500	$13,600

 

 

Requirements in Excel:

 

1. Open a spreadsheet and enter the data.  Label the worksheet as RAWDATA.
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.  Label this worksheet as HIGHLOW.
3. Create another worksheet and run regression (Least Squares Method) and save the output.  Label this worksheet as REGRESSION.
4. Create another worksheet and save both outputs by linking worksheets.  Label this worksheet as COMPARISON.  Determine whether the High-Low method or Regression Analysis better predicts monthly overhead costs.  Explain which model is the better and why you think that model is better.
5. Create another worksheet by copying your RAWDATA worksheet.  Label this worksheet PREDICTION.  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 projected units in January and in December of next year.  Do you think these predicted overhead costs are reasonable?  Why or why not?