Problem 3: The sales of a company in million dollars) for each year are shown in the table below.
| x (year) | 2005 | 2006 | 2007 | 2008 | 2009 |
|---|---|---|---|---|---|
| y (sales) | 12 | 19 | 29 | 37 | 45 |
a) Find the least square regression line y -ax+b.
b) Use the least squares regression line as a model to estimate the sales of the company in 2012.
I used Excel to solve this question.
Que.3
Step.1 Enter data in Excel
Step.2 Go to 'Data' menu ----> 'Data Analysis' ----> Regression.
Step.3 New window will pop up on screen ---> enter X and Y range and output range. You will get following result.
| x | y | SUMMARY OUTPUT | ||||||||||
| 2005 | 12 | |||||||||||
| 2006 | 19 | Regression Statistics | ||||||||||
| 2007 | 29 | Multiple R | 0.998868138 | |||||||||
| 2008 | 37 | R Square | 0.997737557 | |||||||||
| 2009 | 45 | Adjusted R Square | 0.996983409 | |||||||||
| Standard Error | 0.730296743 | |||||||||||
| Observations | 5 | |||||||||||
| ANOVA | ||||||||||||
| df | SS | MS | F | Significance F | ||||||||
| Regression | 1 | 705.6 | 705.6 | 1323 | 4.57036E-05 | |||||||
| Residual | 3 | 1.6 | 0.533333 | |||||||||
| Total | 4 | 707.2 | ||||||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |||||
| Intercept | -16830.4 | 463.4969112 | -36.3118 | 4.59E-05 | -18305.45403 | -15355.34597 | -18305.45403 | -15355.34597 | ||||
| X Variable 1 | 8.4 | 0.230940108 | 36.37307 | 4.57E-05 | 7.665045508 | 9.134954492 | 7.665045508 | 9.134954492 | ||||
Que. a
Least square regression line is
Y = 8.4 X - 16830.4
Que.b
When X = 2012, Sale of the company Y is computed as follows:
Y = 8.4 * 2012 - 16830.4
= 70.8
The sales of a company in million dollars) for each year are shown in the table below.