2. Regression Analysis Method

Illustration one

The following cost data has been obtained from the records of Olympia Computer Systems; a firm that assembles computers.

Month	Number of computers assembled	Total Costs incurred (Ksh.)
January	160	164,000
February	90	112,000
March	110	100,000
April	112	96,000
May	116	120,000
June	120	124,000
July	130	128,000
August	136	130,000
September	140	140,000
October	150	148,000
November	170	180,000

Required:

1. Use Excel to create a scatter plot. Identify any outliers and explain why they are outliers.
2. Use the regression analysis feature in Excel to generate a complete regression output.
3. Determine the regression line.
4. Use Excel to add the regression equation and related R² statistic to the scatter plot.
5. Use Excel to draw in the high-low line, and visually confirm the line by calculating the high-low equation.
6. Visually inspect the scatter plot and comment on which line (high-low or regression) is more representative of the data points.
7. Comment on why the two lines are different.
8. Comment on what the R² value means in general and what the R² value specifically tells about the regression line developed from this data set. How confident should a manager be in using this cost equation to estimate costs at different volumes?
9. Predict and compare cost estimates for the month of December when 182 computers were assembled using both the high-low equation and the regression equation.
10. If you were the manager, which method of cost estimation would you use and why?