In: Economics
5) Draw the trend analysis based on following sales data for four years and analyse the fifth-year projected sales. Mention the possible relationship between the sales and the year on the basis of linear regression. Also mention the R-square value and its interpretation.
Year |
Sales |
1 |
900 |
2 |
1100 |
3 |
1200 |
4 |
1450 |
5 |
Excel regression summary output as follows.
SUMMARY OUTPUT | ||||||
Regression Statistics | ||||||
Multiple R | 0.9880 | |||||
R Square | 0.9761 | |||||
Adjusted R Square | 0.9641 | |||||
Standard Error | 43.3013 | |||||
Observations | 4 | |||||
ANOVA | ||||||
df | SS | MS | F | Significance F | ||
Regression | 1 | 153125 | 153125 | 81.6667 | 0.0120 | |
Residual | 2 | 3750 | 1875 | |||
Total | 3 | 156875 | ||||
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | |
Intercept | 725 | 53.0330 | 13.6707 | 0.0053 | 496.8174 | 953.1826 |
Year | 175 | 19.3649 | 9.0370 | 0.0120 | 91.6795 | 258.3205 |
Estimated linear trend equation: Sales = 725 + 175 x Year
When Year = 5, Trend value of sales = 725 + 175 x 5 = 725 + 875 = 1600
R2 measures the proportion of variation of Sales, that is explained by the independent variable Year, using the regression model. Since R2 is 0.9761, it means that 97.61% of the variation in sales is explained by the model, which is considered a good fit.