Automotive Sales Forecast

Sales Forecast Memo

The objective of this memo is to discuss the predictions of automobile sales in the US for the month of March 2012. The prediction is to take into account the historic data (provided) and current marketing environment.

At first, two approaches of the analytical (quantitative) method were used – moving average and exponential smoothing. The objective of doing so was to get an idea of the prediction based on historic data only. Once that was done, the marketing environment was taken into consideration - to see how it would effect the predictions made by the models. In general, there are a lot of underlying factors to consider when doing a sales forecast – mainly categorized into controllable and uncontrollable factors. Since this forecast is covers all automobile sales, not just for a particular company, it is safe to say that the controllable factors can be ignored (product mix, prices etc.). However, uncontrollable factors have a big play on this forecast – economy, interest rates, gas prices, industry trends etc.

As discussed earlier, the first step was to perform the two analytical approaches. These two approaches smoothed the data and found the underlying trend behind the noise. Table 1 and Table 2 in the Appendix shows the steps used to formulate this trend. Actual sales, the moving average and exponential smoothing are shown in Chart 1 in the Appendix. Purely based on this, the moving average would predict the March ’12 forecast to be 535,662 where as the exponential smoothing would predict it to be 574,439.

The automotive industry bases the sales predictions on the 3C’s – Cash, credit and confidence (www.autoobserver.com). It is a known that our economy is down. However, the unemployment rate is decreasing and it seems that the confidence is increasing. Having said all that, this factor itself has not changed any more than what is already accounted for in the model. The biggest factor, it appears, is the escalating gas prices this year. The gas prices are already high, and are expected to go up even more through the summer. This has to affect the prediction, more so than reflected by the model. To see how much (quantitatively) gas prices affect the sales, one must look at the history. Chart 5 shows average gas price in the last 4 years. This chart shows that a similar peak in gas prices was also observed in May/June 2011. Table 3 was created to see the percentage change (over the previous month). It is seen that there was a +12.62% increase in May 2009 and +12.48% increase in May 2010 over April 2009 and April 2010 respectively. Continuing in the same trend of month over month, one would have expected May 2011 to have an approximate sales increase by ~ 12%. Instead, May 2011 underwent a -10.42% change over April 2011; which is approximately 22% lower than the usual growth expected in May. This interestingly coincides perfectly with the spike in gas prices. Using this trend, the average trend in March of the past 2 years is an increase of 34% (35.52% increase in 2010 and 33.12% increase in 2011). So if the similar effect of gas prices is to be seen again, March 2012 should see 11% increase only (34 – 22) over February 2012 sales. This calculates to give a sales prediction of 665,971 in March 2012 (1.11 X 599,974).

In conclusion, even though the moving average and exponential smoothing models by themselves predict the sales in March 2012 to be 535,662 and 574,439 – I choose the prediction of 665,971, which adjusts the models for the gas prices spike and economic impacts.

APPENDIX

TABLE 1: Calculations for Moving Average
|PERIOD |YEAR |MONTH |SALES |Moving Average (3 |Calculations - Moving average (3 periods) |
| | | | |periods) | |
|1 |2009 |MAR |445595 | | |
|2 | |APR |429770 | | |
|3 | |MAY |484028 | | |
|4 | |JUN |456206 |453131 | =((445595 + 429770 + 484028) / 3) |
|5 | |JUL |550388 |456668 |=((429770 + 484028 + 456206) / 3) |
|6 | |AUG |720483 |496874 |=((484028 + 456206 + 550388) / 3) |
|7 | |SEP |398235 |575692 |=((456206 + 550388 + 720483) / 3) |
|8 | |OCT |427325 |556369 |=((550388 + 720483 + 398235) / 3) |
|9 | |NOV |374677 |515348 |=((720483 + 398235 + 427325) / 3) |
|10 | |DEC |512566 |400079 |=((398235 + 427325 + 374677) / 3) |
|11 |2010 |JAN |361660 |438189 |=((427325 + 374677 + 512566) / 3) |
|12 | |FEB |399245 |416301 |=((374677 + 512566 + 361660) / 3) |
|13 | |MAR |541042 |424490 |=((512566 + 361660 + 399245) / 3) |
|14 | |APR |493565 |433982 |=((361660 + 399245 + 541042) / 3) |
|15 | |MAY |555153 |477951 |=((399245 + 541042 + 493565) / 3) |
|16 | |JUN |493473 |529920 |=((541042 + 493565 + 555153) / 3) |
|17 | |JUL |514943 |514064 |=((493565 + 555153 + 493473) / 3) |
|18 | |AUG |490791 |521190 |=((555153 + 493473 + 514943) / 3) |
|19 | |SEP |463202 |499736 |=((493473 + 514943 + 490791) / 3) |
|20 | |OCT |436354 |489645 |=((514943 + 490791 + 463202) / 3) |
|21 | |NOV |395689 |463449 |=((490791 + 463202 + 436354) / 3) |
|22 | |DEC |509591 |431748 |=((463202 + 436354 + 395689) / 3) |
|23 |2011 |JAN |380313 |447211 |=((436354 + 395689 + 509591) / 3) |
|24 | |FEB |482540 |428531 |=((395689 + 509591 + 380313) / 3) |
|25 | |MAR |642347 |457481 |=((509591 + 380313 + 482540) / 3) |
|26 | |APR |598627 |501733 |=((380313 + 482540 + 642347) / 3) |
|27 | |MAY |535075 |574505 |=((482540 + 642347 + 598627) / 3) |
|28 | |JUN |516588 |592016 |=((642347 + 598627 + 535075) / 3) |
|29 | |JUL |495603 |550097 |=((598627 + 535075 + 516588) / 3) |
|30 | |AUG |498090 |515755 |=((535075 + 516588 + 495603) / 3) |
|31 | |SEP |475519 |503427 |=((516588 + 495603 + 498090) / 3) |
|32 | |OCT |468533 |489737 |=((495603 + 498090 + 475519) / 3) |
|33 | |NOV |452482 |480714 |=((498090 + 475519 + 468533) / 3) |
|34 | |DEC |549306 |465511 |=((475519 + 468533 + 452482) / 3) |
|35 |2012 |JAN |457705 |490107 |=((468533 + 452482 + 549306) / 3) |
|36 | |FEB |599974 |486498 |=((452482 + 549306 + 457705) / 3) |
|37 | |MAR | |535662 |=((549306 + 457705 + 599974) / 3) |

Table 2: Calculations for Exponential Smoothing
|PERIOD |YEAR |MONTH |SALES |Exp Smoothing (a=0.8) |Calculations - Exp. Smoothing (a = 0.8) |
|1 |2009 |MAR |445595 | | |
|2 | |APR |429770 |445595 | |
|3 | |MAY |484028 |432935 |=(0.8)(429770) + (0.2)(445595) |
|4 | |JUN |456206 |473809 |=(0.8)(484028) + (0.2)(432935) |
|5 | |JUL |550388 |459727 |=(0.8)(456206) + (0.2)(473809) |
|6 | |AUG |720483 |532256 |=(0.8)(550388) + (0.2)(459727) |
|7 | |SEP |398235 |682838 |=(0.8)(720483) + (0.2)(532256) |
|8 | |OCT |427325 |455156 |=(0.8)(398235) + (0.2)(682838) |
|9 | |NOV |374677 |432891 |=(0.8)(427325) + (0.2)(455156) |
|10 | |DEC |512566 |386320 |=(0.8)(374677) + (0.2)(432891) |
|11 |2010 |JAN |361660 |487317 |=(0.8)(512566) + (0.2)(386320) |
|12 | |FEB |399245 |386791 |=(0.8)(361660) + (0.2)(487317) |
|13 | |MAR |541042 |396754 |=(0.8)(399245) + (0.2)(386791) |
|14 | |APR |493565 |512184 |=(0.8)(541042) + (0.2)(396754) |
|15 | |MAY |555153 |497289 |=(0.8)(493565) + (0.2)(512184) |
|16 | |JUN |493473 |543580 |=(0.8)(555153) + (0.2)(497289) |
|17 | |JUL |514943 |503494 |=(0.8)(493473) + (0.2)(543580) |
|18 | |AUG |490791 |512653 |=(0.8)(514943) + (0.2)(503494) |
|19 | |SEP |463202 |495163 |=(0.8)(490791) + (0.2)(512653) |
|20 | |OCT |436354 |469594 |=(0.8)(463202) + (0.2)(495163) |
|21 | |NOV |395689 |443002 |=(0.8)(436354) + (0.2)(469594) |
|22 | |DEC |509591 |405152 |=(0.8)(395689) + (0.2)(443002) |
|23 |2011 |JAN |380313 |488703 |=(0.8)(509591) + (0.2)(405152) |
|24 | |FEB |482540 |401991 |=(0.8)(380313) + (0.2)(488703) |
|25 | |MAR |642347 |466430 |=(0.8)(482540) + (0.2)(401991) |
|26 | |APR |598627 |607164 |=(0.8)(642347) + (0.2)(466430) |
|27 | |MAY |535075 |600334 |=(0.8)(598627) + (0.2)(607164) |
|28 | |JUN |516588 |548127 |=(0.8)(535075) + (0.2)(600334) |
|29 | |JUL |495603 |522896 |=(0.8)(516588) + (0.2)(548127) |
|30 | |AUG |498090 |501062 |=(0.8)(495603) + (0.2)(522896) |
|31 | |SEP |475519 |498684 |=(0.8)(498090) + (0.2)(501062) |
|32 | |OCT |468533 |480152 |=(0.8)(475519) + (0.2)(498684) |
|33 | |NOV |452482 |470857 |=(0.8)(468533) + (0.2)(480152) |
|34 | |DEC |549306 |456157 |=(0.8)(452482) + (0.2)(470857) |
|35 |2012 |JAN |457705 |530676 |=(0.8)(549306) + (0.2)(456157) |
|36 | |FEB |599974 |472299 |=(0.8)(457705) + (0.2)(530676) |
|37 | |MAR | |574439 |=(0.8)(599974) + (0.2)(472299) |

Table 3: Sales and Percentage change over previous month
|PERIOD |YEAR |MONTH |SALES |Percentage change over |
| | | | |previous month |
|1 |2009 |MAR |445595 | |
|2 | |APR |429770 |-3.55% |
|3 | |MAY |484028 |12.62% |
|4 | |JUN |456206 |-5.75% |
|5 | |JUL |550388 |20.64% |
|6 | |AUG |720483 |30.9% |
|7 | |SEP |398235 |-44.73% |
|8 | |OCT |427325 |7.3% |
|9 | |NOV |374677 |-12.32% |
|10 | |DEC |512566 |36.8% |
|11 |2010 |JAN |361660 |-29.44% |
|12 | |FEB |399245 |10.39% |
|13 | |MAR |541042 |35.52% |
|14 | |APR |493565 |-8.78% |
|15 | |MAY |555153 |12.48% |
|16 | |JUN |493473 |-11.11% |
|17 | |JUL |514943 |4.35% |
|18 | |AUG |490791 |-4.69% |
|19 | |SEP |463202 |-5.62% |
|20 | |OCT |436354 |-5.8% |
|21 | |NOV |395689 |-9.32% |
|22 | |DEC |509591 |28.79% |
|23 |2011 |JAN |380313 |-25.37% |
|24 | |FEB |482540 |26.88% |
|25 | |MAR |642347 |33.12% |
|26 | |APR |598627 |-6.81% |
|27 | |MAY |535075 |-10.62% |
|28 | |JUN |516588 |-3.46% |
|29 | |JUL |495603 |-4.06% |
|30 | |AUG |498090 |0.5% |
|31 | |SEP |475519 |-4.53% |
|32 | |OCT |468533 |-1.47% |
|33 | |NOV |452482 |-3.43% |
|34 | |DEC |549306 |21.4% |
|35 |2012 |JAN |457705 |-16.68% |
|36 | |FEB |599974 |31.08% |
|37 | |MAR | | |

Chart 1: All plots (sales, moving average & exponential smoothing)
[pic]

Chart 2: Actual Sales
[pic]

Chart 3: Moving average
[pic]

Chart 4: Exponential Smoothing
[pic]

Chart 5: Gas Price History (source: http://gasbuddy.com/gb_retail_price_chart.aspx)
[pic]