Question

1. Suppose we have the following annual sales data for an automobile dealership:

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 1

ANSWER:

Given that,

The linear trend model that we want to fit is,

,

where t=trend, is the intercept, is the slope and is a random error

Using Excel data--->data analysis-->regression

get this

ans: The estimated simple trend model is

To estimate a quadratic trend model,

We create a new column which is the square of trend, and use Excel--->data--->data analysis--->regression

get this

The estimated quadratic trend model is

Using these 2 models we get the estimates for years 2009 to 2018 and we calculate the RMSE as

Prepare the following sheet

get this,

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