In: Statistics and Probability
Year Sales Trend
2009 121 1
2010 187 2
2011 165 3
2012 134 4
2013 155 5
2014 167 6
2015 200 7
2016 206 8
2017 221 9
2018 231 10
We want to forecast sales for 2019 and 2020 using either a simple trend model or a quadratic trend model. Use a within sample forecasting technique to determine the best model using the RMSE measure discussed in lecture. Once this model has been determined, provide actual forecasts for 2019 and 2020. Report the two RMSE values in your pdf or fax submission along with the actual forecasts. Submit your Excel file used to create these answers
Answer:-
Given That:-
Suppose we have the following annual sales data for an automobile dealership:
We want to forecast sales for 2019 and 2020 using either a simple trend model or a quadratic trend model. Use a within sample forecasting technique to determine the best model using the RMSE measure discussed in lecture.
Given,
The linear trend model that we want to fit is,
Sales =
Where
t = trend
is the intercept,
is the slope and
is a random error
Using excel data -------> data analysis --------> regression
SUMMARY OUTPUT | ||||||
Regression Statistics | ||||||
Multiple R | 0.8254 | |||||
R Square | 0.6813 | |||||
Adjusted R Square | 0.6415 | |||||
Standard Error | 21.8688 | |||||
Observations | 10 | |||||
ANOVA | ||||||
df | SS | MS | F | Significance F | ||
Regression | 1 | 8180.148485 | 8180.148 | 17.10455 | 0.003272 | |
Residual | 8 | 3825.951515 | 478.2439 | |||
Total | 9 | 12006.1 | ||||
Coeffiicients | Standard Error | t Stat | P - value | Lower 95% | Upper 95% | |
Intercept | 123.933 | 14.939 | 8.296 | 0.000 | 89.483 | 158.383 |
Trend | 9.958 | 2.408 | 4.136 | 0.003 | 4.405 | 15.510 |
The estimated simple trend model is
= 123.9333 + 9.9576t
To estimate a Quadratic trend model
We create a new column which is the square of trend, and use excel -----> data ------> data analysis-----
SUMMARY OUTPUT | ||||||
Regression Statistics | ||||||
Multiple R | 0.8522 | |||||
R Square | 0.7263 | |||||
Adjusted R Square | 0.6481 | |||||
Standard Error | 21.6659 | |||||
Observations | 10 | |||||
ANOVA | ||||||
df | SS | MS | F | Significance F | ||
Regression | 2 | 8720.216667 | 4360.108 | 9.288449 | 0.010724 | |
Residual | 7 | 3825.883333 | 469.4119 | |||
Total | 9 | 12006.1 | ||||
Coeffiicients | Standard Error | t Stat | P - value | Lower 95% | Upper 95% | |
Intercept | 146.183 | 25.482 | 5.737 | 0.001 | 85.927 | 206.440 |
Trend | -1.167 | 10.643 | -0.110 | 0.916 | -26.333 | 23.998 |
Trend2 | 1.011 | 0.943 | 1.073 | 0.319 | -1.218 | 3.241 |
The estimated quadratic trend model is
= 146.1833 - 1.1674t + 1.0114t2
Using these 2 models we get the estimates for years 2009 to 2018 and we calculate the RMSE as
Prepare the following sheet
The RMSE values for
Simple trend: 19.560
Quadratic trend : 18.127
Since the quadratic trend model has lower RMSE value, it is the
best model
ans: Quadratic trend model is the best model
Sales forecast using the quadratic model for 2019 is 255.7
Sales forecast using the quadratic model for 2019 is 277.8
Thank you for your supporting.....Please upvote my answer.