It is August 2018 and Speedy Automotive is one of four car dealerships located in rural Ontario.
Question:
It is August 2018 and Speedy Automotive is one of four car dealerships located in rural Ontario. The residents in the area replace their vehicles roughly every eight years. When they do replace their vehicles, they tend to visit the same dealership that sold them the first one, building a relationship with the dealership. This has kept sales relatively stable for all the dealerships in the area. Recently, however, customers have begun expanding their shopping options. Speedy Automotive believes this is due to the rise of the Internet. Customers have begun coming to the dealership with an exact idea of what car they would like and have a price in mind. When Speedy Automotive does not have the model available or cannot meet the price, customers have started going to competitors.
This trend has the general manager of Speedy, Sanj Perera, worried. Sanj runs the dealership like a small business and relies on repeat customers. Speedy is not a chain and has little access to outside capital, so cash flow is critical. If sales are low, Sanj has to take out a loan to pay the manufacturer for the vehicles. If sales are high, Speedy might lose potential customers due to a lack of inventory. Sanj has decided that he needs to be able to forecast this new variability to keep the business healthy.
Gladys Knight is the inventory manager at Speedy and has historically prepared the forecasts. She usually just takes the sales numbers from last year and adjusts them upward or downward based on her “gut feeling.” This has worked in the past, but Sanj has told her to look into other forecasting methods. Gladys is preparing the September 2018 sales forecast for Speedy and is not sure where to start. For the August forecast, Gladys used the sales numbers from August 2017. The August 2017 forecast was off by 6 cars and Gladys hoped the 2018 version would fare better. She assumed the sales representatives would always work a little harder if sales were low and they could always take vacation time if they were above target.
Sanj has told Gladys to look into various Time Series and select the best option, but Barb is not sure what they are. He also wants Gladys to determine the amount of profit actually being lost due to poor forecasting and potentially being lost. Gladys has access to sales and forecast data from the past 18 months and has already calculated various measures of error in Excel.
See attached print out of Speedy Automotive Sales Forecast History.
Answer the following questions.
- Which forecasting method / model was probably being used by Speedy Automotive historically? ( 1 mark)
- What is the forecast for September using each of the following techniques: ( 4 marks)
Month | forecast | actual sales | difference |
February 2017 | 20 | 22 | 2 |
March 2017 | 25 | 22 | -3 |
April 2017 | 30 | 34 | 4 |
May 2017 | 45 | 50 | 5 |
June 2017 | 50 | 47 | -3 |
July 2017 | 50 | 42 | -8 |
August 2017 | 55 | 49 | -6 |
September 2017 | 60 | 65 | 5 |
October 2017 | 35 | 35 | 0 |
November 2017 | 40 | 38 | -2 |
January 2018 | 20 | 16 | -4 |
February 2018 | 25 | 16 | -9 |
March 2018 | 30 | 49 | 19 |
April 2018 | 40 | 38 | -2 |
May 2018 | 50 | 55 | 5 |
June 2018 | 55 | 69 | 14 |
July 2018 | 65 | 75 | 10 |
August 2018 | 65 | 52 | -13 |
September 2018 |
TECHNIQUE | SEPTEMBER FORECAST |
| 52 |
| From march to august sale = 49 + 38 + 55 + 69 + 75 + 52 = 338 / 6 = 56.33 |
| From June to august sale = 69 + 75 + 52 = 196 / 3 = 65.3 |
|
- Looking at the “Difference” column in the data, what do you see as a trend in forecast errors? ( 1 mark)
- Which Time Series model should be used going forward? Explain why. ( 2 marks)
Time Series Model Name: |
Explain why it should be used: |
- Assuming:
- the average sale price for one car is $25,000
- the average car costs $15,000 to acquire
- all cars arrive on the 1st
- all cars remaining on the 31st are disposed of with a 50% sale price markdown
What was the loss in August due to poor forecasting? (2 marks)
What was the missed profit opportunity in August due to poor forecasting? (2 marks)
August loss = |
August missed profit opportunity = |
- Assuming Speedy only brought in enough cars to cover the forecast in June and July (and therefore could not sell more than forecast), what would have been the missed profit opportunity due to poor forecasts? ( 2 marks)
MONTH | LOSS IN PROFIT DUE TO POOR FORECASTING |
June | |
July |
Organizational Behaviour Concepts Controversies Applications
ISBN: 978-0132310314
6th Canadian Edition
Authors: Nancy Langton, Stephen P. Robbins, Timothy A. Judge, Katherine Breward