A Forecast of Ted Rallley's Company Auto Sales - Assignment Example

EXECUTIVE SUMMARY A forecast of Ted Rallley's company auto sales using three different models and accessing which among the three models is the best to forecast the sales has been made. The data being applied in the study was obtained for four years of the Ted Rallley's company. The models that are being used are regression model when the data has season, regression model when the data has no season, and additive holt-winter model. From this work, we will try to arrive at the most appropriate forecast model for the sales of the auto sales for the future years. The study will use the mean absolute deviation, the mean square error, mean error, and mean absolute percentage error. On this study, a plot of the forecast by the three models was obtained, and the results will be discussed. The root mean square error and the mean absolute percentage error will be the most method to be sued to compare and rank the forecast method. From the ranking of the forecast method, the study indicates that the regression with econometric variance is the most appropriate method of forecasting the sales of automobile parts. It also indicates that additive holt-winter model is the second best model in forecasting the auto sales parts of Ted Rallley's company. Background As the economy change keeps on declining, every play of the economic grows experience the falling trend. The automotive industry depended much on the economic boom. This decline in the economic growth has a negative effect to the automotive industry (Bruns, W. J., & Waterhouse, J. H.,1975).. The distributers of the automotive parts have continued to experience heavy losses. And capacity caused by cuts caused by the auto makers. The distributors are also facing costly energy and material constraints. It has been raised by the economic analysis that the automotive industry that used to rise over $72 billion has been of the pathetic point since the county has set it on the chapter 11 law of protection. There will be an increasing rise in the number of bankruptcies. A lot of challenges is faced by the distributors of the USA since it very difficult to penetrate the supply chain marker as the chains were established long ago with home marketers. With the economy continuously deteriorating everyone seems to be getting hurt financially, even the automotive industry, which has deepening the economic recession. Automotive part suppliers continued to experience heavy debt and overcapacity caused by production cuts by automakers, specifically including the big 3 (Ford Motor Company, General Motors and Chrysler). The suppliers are also being pressed by higher energy and input materials costs. It has been determined by Industry analyst that automotive companies that accounted for more than $72 billion in sales have filed for chapter 11 protections in 2008. The number of Bankruptcies will continue to rise as the years go by. Domestically, losing the big 3 to U.S affiliates of foreign- based manufacturers and imports in 2008 have caused a dramatic 50% drop in the market share. Most US suppliers are dependent on these three companies aforementioned. U.S suppliers are currently facing the challenge of penetrating automakers supply chains, mostly because these relationships have been long-established with home-market supplies. Ted Ralley is the director of a marketing research for a manufacturer of spare automobiles parts and it’s working on conducting a forecast for the upcoming year. Ted is aware of the forecasting errors and how costly they can be which is why these numbers must be as accurate as possible. In order to perform this forecast, Ted has collected the data on quarterly sales for the previous four years and ran several forecasts using time series forecasting methods. Ted noticed that economic activity and oil prices have impacted significantly the auto part sales and decided that the forecast will be more accurate using econometric variables. Problem There is decline in the auto sales of ted relly company. The marketing director decided to make a future forecast of the sales so as to be able to see the future of the company. Tedd relly uses two regression model the model with econometric factors and model without econometric factors. Ted also uses the model of additive holt- winter. Analysis Regression analysis with season In this part, we investigate the regression model of the sales using the regression model when the part has the season’s element. We will investigate the coefficient of these regression model and also it performance in term of root mean square error, and mean absolute percentage error. Table 1 regression statistics Regression Statistics Multiple R 0.977739 R Square 0.955974 Adjusted R Square 0.939965 Standard Error 4091506 Observations 16 Table 2: The analysis of variance of the regression model with season. ANOVA df SS MS F Significance F Regression 4 4E+15 1E+15 59.7136 2.17E-07 Residual 11 1.84E+14 1.67E+13 Total 15 4.18E+15 Table 3 The coefficient analysis of the regression model. Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 32572562 2597784 12.53859 7.4E-08 26854878 38290247 26854878 38290247 Time 3329077 228722.1 14.55512 1.56E-08 2825663 3832491 2825663 3832491 Q2 8474996 2902159 2.920239 0.013932 2087388 14862604 2087388 14862604 Q3 -7199241 2929073 -2.45786 0.031799 -1.4E+07 -752396 -1.4E+07 -752396 Q4 -1662507 2973388 -0.55913 0.587277 -8206889 4881876 -8206889 4881876 From the regression model of the automotive parts sales data, tF-statistics is equal to 59.7136 with a p-value of 0.000. This implies that the regression model with applied on seasonal data is significant. The regression model with season has used the Q2, Q3, and Q4 as the coefficient of the regression model. From table 3 of the coefficient of regression, we can observe that the value of t-statistic corresponding to coefficient Q2 is equal to 2.9202, with a p-value of 0.01393 which is less than the 0.05 level of confidence. We can therefore, say that the independent variable Q1 (second quarter sale is significant). The t- statistics of the independent variable Q3 is equal to -2.4578 with a p- value of 0.03179 which is less than the 0.05 level of confidence. This implies that the independent variable sales in the third quarter are significant. We can also observe that the value of the t- statistic corresponding to the independent variable sales in the fourth quarter is -0.55913 with a p- value of 0.5873. Since the p- value is greater than the 0.05 level of confidence, we conclude that the independent variable sales of the fourth quarter is not significant. Table 4 The model analysis of the regression with season ME 928439 MSE 16782193945861.00 RMSE 4096607.614 MAPE 4.93% U 0.4184 Table 4 explains the various measures that can be used to explain the efficiency of the model in forecasting the sales of automotive parts. From this table, we can see that the value of the mean error is 928439 , the mean square error is 16782193945861.00 the root mean square error is 4096607.614, mean absolute percentage error is 4.93% and the u is 0.4184. The regression model with economic factors Regression Statistics Multiple R 0.966253 R Square 0.933644 Adjusted R Square 0.917056 Standard Error 4809221 Observations 16 ANOVA df SS MS F Significance F Regression 3 3.91E+15 1.3E+15 56.28132 2.43E-07 Residual 12 2.78E+14 2.31E+13 Total 15 4.18E+15 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept -2.6E+09 6.35E+08 -4.09948 0.001474 -4E+09 -1.2E+09 -4E+09 -1.2E+09 M2 Index -4047000 58499296 -0.06918 0.945986 -1.3E+08 1.23E+08 -1.3E+08 1.23E+08 Non-Farm Activity Index 79666121 22207637 3.58733 0.003732 31279837 1.28E+08 31279837 1.28E+08 Oil Prices -4153617 731556.6 -5.67778 0.000103 -5747542 -2559692 -5747542 -2559692 The above regression analysis show the results of regression model with economic factors M2 index, Non- farm activity index, and the oil prices. From above regression model results, we can observe that the value of the F-statistic is equal to 56.28132 with a p- value of 0.000 which is less than the 0.05 level of confidence. Since the p- value is less than the 0.05 level of confidence, we can say that the regression model with economic factors is significant in forecasting the sales of automotive parts. The coefficient of the M2 index has t- statistics equal to 0.0691 with a p- value of 0.945986 which is greater than the 0.05 level of confidence. Since the p- value is greater than the 0.05 level of confidence, we can say that the independent variable M2 index is not significant in the model hence can be removed from the regression model of forecasting sales of automotive parts. The t- statistic corresponding to the independent variable non- farming activities is equal to 3.5873 with a p- value of 0.0037 which is less than the 0.05 level of confidence. Since the p- value is less than the 0.05 level of confidence, we are can conclude that the non- farming activities are very significant to the regression model with economic activities. The t-statistic of the independent variable oil prices is equal to -5.67778 with a p- value of 0.0001 which is less than the 0.05 level of confidence. Since the p- value is less than the 0.05 level of confidence, we can conclude that the independent variable oil price is significant to the regression model of the sales of automotive parts with factors. From the two regression model we can see that the regression model with economic factors has a coefficient of determination equal to 0.933644. This implies that the regression model can account for 93.364%. The regression model of sales of auto parts without economic factors has a coefficient of determination equal to 0.95597. This implies the regression model account for 95.597% of the error in the model. From this analysis we can conclude that the two regression model can be able to account for errors in the model but the regression model without the economic factors is better in terms of the amount of error it can account for. The table of efficiency statistics of the regression model with economic factors. ME 596707 MSE 10252222073273.30 RMSE 3201909.129 MAPE 3.19% U 0.3331 From the regression model with an economic factor the mean error is 596707, the mean square error is 10252222073273.30, the root mean square error is 3201909.129, and the mean absolute percentage error is 3.19%. The value of u is equal to 0.3331. The holt- winter method From the holt- winter method we can observe the following values of the statistic of efficiency of the model. The mean error is -1102668, the mean square error is 10741467948583.3, the root mean square error is 3,277,417.88, and the mean absolute percentage error is 4.015%. The value of the u is 0.31277. ME -1102668 MSE 10741467948583.3 RMSE 3,277,417.88 MAPE 0.040152944 U 0.312773338 The ranking of the models Regression with Seasons Regression with Econometric Vars HW Additive ME 928439.08 596707.40 -1102668.43 RMSE 4096607.61 3201909.13 3277417.88 MAPE 0.05 0.03 0.04 U 0.42 0.33 0.31 Rank 3 1 2 Forecast 2008 Q1 $ 89,166,875.88 $ 79,622,179.94 $ 81,055,901.43 2008 Q2 $ 100,970,949.13 $ 54,760,210.20 $ 94,873,277.14 2008 Q3 $ 88,625,789.38 $ 59,686,237.00 $ 77,843,049.30 2008 Q4 $ 97,491,601.13 $ 58,720,797.03 $ 102,067,895.31 From above ranking of the forecast model, we can observe that the regression model with econometric variables has the least root mean square error of 3201909.13. This implies that the regression model with econometric variable is the best model of the three forecast model of the sales of the automotive parts. The regression model with econometric variables has a mean error of 596707.40 which is the least absolute value of the mean error of the two models. This implies that the regression model of sales forecast of automotive parts is the best model since it has the least mean error. The value of the mean absolute error of the 0.03 which is least means absolute error. This implies that the regression model with econometric variable is the best model to forecast the sales of automotive parts (Onsi, M.,1973). The forecasted sales value of the automotive parts in the first quarter of 2008 is 79622179.936737, the forecasted sales of the automotive in the second quarter is 54760210.195111, the sales forecast of the regression with econometric variables is 59686237.0004121 for the third quarter. The value of the forecasted sales of 2008 fourth quarter is equal to 58720797.0325194. From these results, we can confidently say that the regression model with econometric variables is the best model. Conclusion and recommendation From the above analysis of the sales forecast of the automotive parts, we see that the regression model with econometric variables is the best forecast model of the sales of automotive parts. From this analysis, we see that the regression model with econometric variables has the least mean square error, the least root mean square error, the least mean absolute error and the least mean absolute percentage error. From this we recommend that the best model to forecast the sales of the automotive parts is the regression model with econometric variables. References Hilton, R. W. (1994). Managerial accounting. New York, NY: McGraw-Hill. Bruns, W. J., & Waterhouse, J. H. (1975). Budgetary control and organization structure. Journal of accounting research, 177-203. Onsi, M. (1973). Factor analysis of behavioral variables affecting budgetary slack. Accounting Review, 535-548. Horngren, C. T., Sundem, G. L., Stratton, W. O., Burgstahler, D., & Schatzberg, J. (2002). Introduction to Management Accounting: Chapters 1-17. Prentice Hall. Read More