Question

In: Statistics and Probability

Suppose we have the following annual sales data for an automobile dealership: Year                Sales            &n

  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

Solutions

Expert Solution

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.


Related Solutions

Suppose we have the following annual sales data for an automobile dealership: Year                Sales            &nb
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...
Suppose we have the following annual sales data for an automobile dealership: Year Sales Trend 2009...
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...
Cameron Bly is a sales manager for an automobile dealership. He earns a bonus each year...
Cameron Bly is a sales manager for an automobile dealership. He earns a bonus each year based on revenue from the number of autos sold in the year less related warranty expenses. Actual warranty expenses have varied over the prior 10 years from a low of 3% of an automobile’s selling price to a high of 10%. In the past, Bly has tended to estimate warranty expenses on the high end to be conservative. He must work with the dealership’s...
Cameron Bly is a sales manager for an automobile dealership. He earns a bonus each year...
Cameron Bly is a sales manager for an automobile dealership. He earns a bonus each year based on revenue from the number of autos sold in the year less related warranty expenses. Actual warranty expenses have varied over the prior 10 years form a low of 3% to a high of 10%. In the past, Bly has tended to estimate warranty expenses on the high end to be conservative. He must work with the dealership's accountant at year-end to arrive...
For the data below: Year Automobile Sales Year Automobile Sales 1990 116 1997 119 1991 105...
For the data below: Year Automobile Sales Year Automobile Sales 1990 116 1997 119 1991 105 1998 34 1992 29 1999 34 1993 59 2000 48 1994 108 2001 53 1995 94 2002 65 1996 27 2003 111 (a)           Determine the least squares regression line using Excel. (b)           Determine the predicted value for 2004. (c)           Determine the 3 year moving average. (d)           Determine the MSE for the trend line (in a) and the 3 year moving average (in c.) Which forecasting method is better?...
Scenario: Suppose you are earning $57,000 a year as a sales representative for a car dealership...
Scenario: Suppose you are earning $57,000 a year as a sales representative for a car dealership at some point you decide to open a used car dealership to sell used cars. You invest $30,000 of savings that have been earning you $2000 per year. And you decide that your new firm will occupy a parking lot that you own and have been renting out for $6000 per year. You hire one clerk to help you in the store, paying her...
Scenario: Suppose you are earning $57,000 a year as a sales representative for a car dealership...
Scenario: Suppose you are earning $57,000 a year as a sales representative for a car dealership at some point you decide to open a used car dealership to sell used cars. You invest $30,000 of savings that have been earning you $2000 per year. And you decide that your new firm will occupy a parking lot that you own and have been renting out for $6000 per year. You hire one clerk to help you in the store, paying her...
Chapter 11 Cameron Bly is a sales manager for an automobile dealership. He earns a bonus...
Chapter 11 Cameron Bly is a sales manager for an automobile dealership. He earns a bonus each year based on revenue from the number of autos sold in the year less related warranty expenses. Actual warranty expenses have varied over the prior 10 years from a low of 3% of an automobile's selling price to a high of 10%. In the past, Bly has tended to estimate warranty expenses on the high end to be conservative. He must work with...
1) Suppose we have a 6-year bond with a 9.5% annual coupon, and a $1000 par...
1) Suppose we have a 6-year bond with a 9.5% annual coupon, and a $1000 par value. The current yield on comparable securities is 8%.   What is the price of this bond? What is the duration? If interest rates were to rise to 10.11%, what is the convexity? What does this mean? 2) Given an YTM on comparable bonds of 7.5%, calculate the duration for the following bonds. Explain the differences. 5-year maturity, 5% coupon paid annually. 5-year maturity, 11%...
We consider data on weekly sales for coffee. Data has n = 18 weeks of coffee...
We consider data on weekly sales for coffee. Data has n = 18 weeks of coffee sales in Q units, the deal rate (D = 1 for usual price, = 1.05 in weeks with 5% price reduction, and = 1.15 in weeks with 15% price reduction), and advertisement (A = 1 with advertisement, = 0 otherwise) Model = log(Q) = B0 + B1D + B2A + u estimate by OLS -> log(Q) = 0.701 (0.415) + 0.756D (0.091) + 0.242A...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT