Forecast Analysis of Macquarie Mountain Estate Ltd - Case Study Example

Case Study: Forecast analysis A case of Macquarie Mountain Estate Co-op, Ltd Name Course Professor Institution Date Table of Contents Executive Summary 3 Introduction 4 Methodology 4 Reason for choosing a particular method 5 Other Methods 6 Results 6 Company sales 6 Determination of the pattern of data followed 7 Autocorrelation (Smoothing out the seasonal irregularities) 9 Determination of a trend pattern 10 Determination of MAD, MSE 10 Multiple regression 10 Forecast using Winter/exponential method 12 Recommendations and Conclusions 15 Creating a competitive advantage 15 Differentiation 16 Appendices 16 Appendix 1 16 Appendix 2 17 Appendix 3 (Naïve seasonal method) 18 Appendix 4 18 References 19 Executive Summary Macquarie Mountain Estate Co-op Ltd, has shown an increasing trend in the sales volumes of wines for last seven years. Both the tables and graphs show a relatively increasing trend with only a few recessions in the sales. The forecasted values show an increasing trend as well (see table1). With consistency in the current marketing plan, the company is likely going to achieve the figures in the forecast or even post better results. According to the market analysis, the firm should employ better forms of advertising as well as incorporate innovation and use of the current technology in their marketing in order to capture the market well. This paper use several methods to forecast the given data. It uses the winter method, the naïve seasonal method and the multi-regression method. This is because these are the best technique when it comes to forecasting. Fore decision purposes, we choose the multi-regression method for the forecast. It is a method that can relate the future and the present as well as come up with predictions of the future if most factors are held constant. From the forecasted results, the sales volumes rise towards 8 percent which is above the levels attained in other quarters. The other statistical methods would have been used in this task although they may not give rice to the results yielded by time series analysis. This is if because this method give an analysis of the past, present and the near future. There are instances where the other popular methods may not have been applicable in this exercise because they may only give statistics of a given data in the current status. With time series evaluation, one is able to gauge the results of a particular program especially when the values are plotted on a line graph. The advantages as well as the limitations of this method have been well-highlighted. Introduction Wine industry in Australia is the most established in the world. This paper is going to investigate the trend in the volume of sales volume of wine by Macquarie Mountain Estate Co-op, Ltd (MMEC) for the last seven years beginning from 2006 with a progression or a forecast for the coming years. This paper will investigate the status of the market currently as well as the expected growth in sales volumes for 2014 through 2015. There will also be a discussion on the environment which the company operates including the current market as well as the government controls and regulations regarding wine production and distribution. Macquarie Mountain Estate Co-op, Ltd, has the challenge of finding out the outcome in the forecast or the future sales volumes in the quarters of September 2014 to June 2015. There is data for the sales volumes of wines for the company for the last seven years which is a good baseline or background information for us to find a suitable conclusion (Box, et al, 2015). Through the application of the most suitable methods and techniques, this paper is going to find out the most likely sales volumes for the company for the said periods. Precisely, Microsoft excel application will be used in coming up with the projected figures as well mapping them on a map for analysis. Methodology For the purposes of this paper, we will use the time series data to arrive at the intended results. The method is suitable in this analysis because of the following reasons: The method simplifies comparisons between the past data and the future one. It relates the two in such a way that one can tell clearly what is likely to happen on the company’s operation in the foreseeable future. One is able to deduce graphs from the analysis so as to view the results in a more visual way making it easier to note and make a conclusion on the future outcomes. Reason for choosing a particular method It is the best method when it comes to budgeting for the future. One is able to tell with high level of certainty the likelihood of a drop or a rise in the operating expenses if all things are kept constant. The method can incorporate the current technological effects on business suggesting that it can the results are more reliable for business decisions. The method is the best in evaluating the current achievements of a firm. It is possible to tell the current and the past performance only in a simple graph. However, the method has some limitations as well. The variables used in the method are not mutually exclusive. This means that an event that has not been factored in the computation can ruin the final results. This aspect makes the method a little bit unreliable and calls for support from other statistical methods to come up with the best results. The method used in this paper produces several lags on the graph. To smoothen this, the method incorporates centered moving average form the sales volume of wins. This produces a relatively positive line graph as shown on the graph. There are various methods that can be used in data forecasting. For the purposes of this paper, time series analysis was chosen because it shows a little bit of consistency in the prediction of data. It can measure the past performance and in the same way it can also be used to prepare predicted values of the data in question. Other Methods Other forecasting methods are not suitable in this form of analysis and prediction because for instance simple linear regression of the said values can only give an analysis of the already completed job. For future jobs, such methods may be challenged in giving a viable prediction. Results From the calculations in excel, the results of time series projections show an increasing trend in the sales volumes of wines (see table 1). Basing our decisions on the results, the company is likely going to experience an increasing in sales for the coming year. Company sales The process starts by determining the company sales. This can be calculated using the industry sales multiplied by the company share of the market as shown below.     Industry sales=89941 Company sales       Yt(market share) Y(Company sales)=Y*89941 Year Quarter Volume (%) Sales 1 Year 1 (2006) 6-Jun 4.8 4317.17 2   6-Sep 4.95 4452.08 3   6-Dec 4.5 4047.35 4   7-Mar 5.21 4685.93 5 year 2 (2007) 7-Jun 4.67 4200.24 6   7-Sep 4.9 4407.11 7   7-Dec 5.3 4766.87 8   8-Mar 4.9 4407.11 9 Year 3 (2008) 8-Jun 5.5 4946.76 10   8-Sep 5.7 5126.64 11   8-Dec 4.9 4407.11 12   9-Mar 4.66 4191.25 13 Year 4 (2009) 9-Jun 5.7 5126.64 14   9-Sep 5.9 5306.52 15   9-Dec 6.1 5486.40 16   10-Mar 6.3 5666.28 17 Year 5 (2010) 10-Jun 5.5 4946.76 18   10-Sep 6.51 5855.16 19   10-Dec 6.32 5684.27 20   11-Mar 6.41 5765.22 21 Year 6 (2011) 11-Jun 6.81 6124.98 22   11-Sep 6.92 6223.92 23   11-Dec 7.24 6511.73 24   12-Mar 6.88 6187.94 25 year 7 (2012) 12-Jun 7.21 6484.75 26   12-Sep 7.32 6583.68 27   12-Dec 6.95 6250.90 28   13-Mar 6.99 6286.88 29 Year 8 (2013) 13-Jun 7.2 6475.75 30   13-Sep 7.1 6385.81 31   13-Dec 7.3 6565.69 Table 1: Company sales Determination of the pattern of data followed To determine the kind of pattern which the data follows, a graph is important. This will also guide I determining a good forecasted data. We can use the moving average as well as the centered moving average to test for seasonality and trend patterns. We can plot the company sales against the quarters. This gives us the following graph: Chart 1: Testing for the data patterns The above graph shows a Autocorrelation (Smoothing out the seasonal irregularities) One technique of smoothing the above graph is doing the moving average for the company sales for the four periods. We will us the following table prepared in excel sheet. To achieve even better or a smoother graph, a centered moving average can be calculated as follows: t Year Quarter Volume (%) Company Sales M.A CMA 1 Year 1 (2006) 6-Jun 4.8 4317.17     2   6-Sep 4.95 4452.08     3   6-Dec 4.5 4047.35 4,375.63 4,361.01 4   7-Mar 5.21 4685.93 4,346.40 4,340.78 5 year 2 (2007) 7-Jun 4.67 4200.24 4,335.16 4,425.10 6   7-Sep 4.9 4407.11 4,515.04 4,480.19 7   7-Dec 5.3 4766.87 4,445.33 4,538.65 8   8-Mar 4.9 4407.11 4,631.96 4,721.90 9 Year 3 (2008) 8-Jun 5.5 4946.76 4,811.84 4,766.87 10   8-Sep 5.7 5126.64 4,721.90 4,694.92 11   8-Dec 4.9 4407.11 4,667.94 4,690.42 12   9-Mar 4.66 4191.25 4,712.91 4,735.39 13 Year 4 (2009) 9-Jun 5.7 5126.64 4,757.88 4,892.79 14   9-Sep 5.9 5306.52 5,027.70 5,212.08 15   9-Dec 6.1 5486.40 5,396.46 5,373.97 16   10-Mar 6.3 5666.28 5,351.49 5,420.07 17 Year 5 (2010) 10-Jun 5.5 4946.76 5,488.65 5,513.38 18   10-Sep 6.51 5855.16 5,538.12 5,550.48 19   10-Dec 6.32 5684.27 5,562.85 5,710.13 20   11-Mar 6.41 5765.22 5,857.41 5,903.50 21 Year 6 (2011) 11-Jun 6.81 6124.98 5,949.60 6,053.03 22   11-Sep 6.92 6223.92 6,156.46 6,209.30 23   11-Dec 7.24 6511.73 6,262.14 6,307.11 24   12-Mar 6.88 6187.94 6,352.08 6,397.05 25 year 7 (2012) 12-Jun 7.21 6484.75 6,442.02 6,409.42 26   12-Sep 7.32 6583.68 6,376.82 6,389.18 27   12-Dec 6.95 6250.90 6,401.55 6,400.43 28   13-Mar 6.99 6286.88 6,399.30 6,374.57 29 Year 8 (2013) 13-Jun 7.2 6475.75 6,349.83 6,389.18 30   13-Sep 7.1 6385.81 6,428.53   31   13-Dec 7.3 6565.69     Table 2: Smoothing the graph Determination of a trend pattern The plot of the centered moving average results in the following graph which is smoother and removes the seasonality and irregularity components of the dat. Chart 2: after removing the seasonal irregularities Determination of MAD, MSE Forecast error=Actual values – forecast values Mean absolute deviation (MAD) is equal to the absolute figures of the errors. Multiple regression Using excel, the multiple regression analysis leads to the following results SUMMARY OUTPUT Regression Statistics Multiple R 0.938 R Square 0.881 Adjusted R Square 0.877 Standard Error 302.6 Observations 31 ANOVA   df SS MS F Significance F Regression 1 2E+07 19616790.51 214.22 6E-15 Residual 29 3E+06 91574.67933 Total 30 2E+07         Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 3997 111.4 35.88523175 1E-25 3769.3 4224.9 3769 4225 T 88.94 6.077 14.63613052 6E-15 76.51 101.37 76.51 101.4 Using the multi-regression analysis above, a forecast can deduced using the two coefficients (see appendix 1. Graph 3: showing forecasted figures The above tables and graphs make it easy for the analyst to deduce the likely outcome of sales easily. Forecast using Winter/exponential method The results of this method in excel produces the following graph. Graph4: showing forecast using winter The table of the data would look like the following:           Alpha 0.2                   A.D RMAD RSPE TS t Year Quarter Volume (%) Company Sales Forecast Abs. Dve Running MAD Running Sum Forecast Error Tracking Signal 1 Year 1 (2006) 6-Jun 4.8 4317.17 4317.17         2   6-Sep 4.95 4452.08 4317.17 134.910 134.910 134.91 1 3   6-Dec 4.5 4047.35 4344.152 296.807 215.858 -161.90 -0.75001 4   7-Mar 5.21 4685.93 4284.79 401.136 277.617 239.24 0.861758 5 year 2 (2007) 7-Jun 4.67 4200.24 4365.017 164.773 249.406 74.47 0.298575 6   7-Sep 4.9 4407.11 4332.063 75.046 214.534 149.51 0.696917 7   7-Dec 5.3 4766.87 4347.072 419.801 248.745 569.31 2.28874 8   8-Mar 4.9 4407.11 4431.032 23.923 216.628 545.39 2.517636 9 Year 3 (2008) 8-Jun 5.5 4946.76 4426.248 520.507 254.613 1065.90 4.186346 10   8-Sep 5.7 5126.64 4530.349 596.288 292.577 1662.19 5.681195 11   8-Dec 4.9 4407.11 4649.607 242.498 287.569 1419.69 4.936862 12   9-Mar 4.66 4191.25 4601.107 409.857 298.686 1009.83 3.380914 13 Year 4 (2009) 9-Jun 5.7 5126.64 4519.136 607.501 324.421 1617.33 4.985296 14   9-Sep 5.9 5306.52 4640.636 665.883 350.687 2283.22 6.510696 15   9-Dec 6.1 5486.40 4773.813 712.588 376.537 2995.80 7.956201 16   10-Mar 6.3 5666.28 4916.33 749.953 401.431 3745.76 9.331001 17 Year 5 (2010) 10-Jun 5.5 4946.76 5066.321 119.566 383.815 3626.19 9.447762 18   10-Sep 6.51 5855.16 5042.408 812.751 409.046 4438.94 10.85193 19   10-Dec 6.32 5684.27 5204.958 479.313 412.950 4918.26 11.91005 20   11-Mar 6.41 5765.22 5300.821 464.397 415.658 5382.65 12.94972 21 Year 6 (2011) 11-Jun 6.81 6124.98 5393.7 731.282 431.439 6113.93 14.17103 22   11-Sep 6.92 6223.92 5539.957 683.961 443.464 6797.90 15.32909 23   11-Dec 7.24 6511.73 5676.749 834.980 461.260 7632.87 16.54788 24   12-Mar 6.88 6187.94 5843.745 344.196 456.170 7977.07 17.48705 25 year 7 (2012) 12-Jun 7.21 6484.75 5912.584 572.162 461.003 8549.23 18.54484 26   12-Sep 7.32 6583.68 6027.016 556.665 464.830 9105.90 19.58975 27   12-Dec 6.95 6250.90 6138.349 112.550 451.281 9218.45 20.42731 28   13-Mar 6.99 6286.88 6160.859 126.017 439.234 9344.47 21.27447 29 Year 8 (2013) 13-Jun 7.2 6475.75 6186.063 289.689 433.893 9634.15 22.20399 30   13-Sep 7.1 6385.81 6244.001 141.810 423.821 9775.97 23.06626 31   13-Dec 7.3 6565.69 6272.363 293.330 419.471 10069.30 24.00473 32   14-Mar   0.00 6331.029 6331.029 610.167 3738.27 6.126631 33 Year 9 (2014) 14-Jun   0.00 5064.823 5064.823 749.375 -1326.56 -1.77022 34   12-Sep   0.00 4051.858 4051.858 849.450 -5378.41 -6.33164 35   12-Dec   0.00 3241.487 3241.487 919.804 -8619.90 -9.37145 36   13-Mar   0.00 2593.189 2593.189 967.615 -11213.09 -11.5884 37   15-Jun   0.00 2074.551 2074.551 998.363 -13287.64 -13.3094 Table 4: Winter calculations with error values Recommendations and Conclusions The three methods highlighted are useful in the said context although the multi-regression method is the best among the three. It gives a better picture of the sales in the four quarters as compared to winter and seasonal method. The regression method also has a favorable MAD and MAPE as compared to the other methods discussed above. The company can also consider the following for growth in the sales of the wines. Creating a competitive advantage It is true that the sales volumes show a positive increase in the coming one year. However, the company will be subjected to the same competition or even stiffer competitions in the future. The technology is changing as well. If the company has to be assured of consistency in the sales volumes as depicted in the graphs, then it has to invest in research and development for the best application of the latest technology in the industry. Differentiation The company should be able to employ differentiation in its products in order to counter react and conquer the competitors seen as offering the best products or having a better quality brand. The results are not a guarantee of better results; the company should work to surpass the set targets. This should only serve as a motivation for the company to work even harder and smarter. Appendices Appendix 1       Yt Y(89941)         Yt/St Trend comp   t Year Quarter Volume (%) Sales M.A CMA Yt/CMA St Deseasonalize Tt Prediction 1 Year 1 (2006) 6-Jun 4.8 4317.17     St*It 0.996 4,336.29 4,086.06 4,068.04 2   6-Sep 4.95 4452.08       1.025 4,343.61 4,175.00 4,279.25 3   6-Dec 4.5 4047.35 4,375.63 4,361.01 0.93 0.992 4,080.32 4,263.94 4,229.48 4   7-Mar 5.21 4685.93 4,346.40 4,340.78 1.08 0.982 4,772.17 4,352.87 4,274.21 5 year 2 (2007) 7-Jun 4.67 4200.24 4,335.16 4,425.10 0.95 0.996 4,218.85 4,441.81 4,422.22 6   7-Sep 4.9 4407.11 4,515.04 4,480.19 0.98 1.025 4,299.74 4,530.75 4,643.89 7   7-Dec 5.3 4766.87 4,445.33 4,538.65 1.05 0.992 4,805.71 4,619.69 4,582.35 8   8-Mar 4.9 4407.11 4,631.96 4,721.90 0.93 0.982 4,488.22 4,708.63 4,623.53 9 Year 3 (2008) 8-Jun 5.5 4946.76 4,811.84 4,766.87 1.04 0.996 4,968.67 4,797.56 4,776.41 10   8-Sep 5.7 5126.64 4,721.90 4,694.92 1.09 1.025 5,001.73 4,886.50 5,008.53 11   8-Dec 4.9 4407.11 4,667.94 4,690.42 0.94 0.992 4,443.02 4,975.44 4,935.23 12   9-Mar 4.66 4191.25 4,712.91 4,735.39 0.89 0.982 4,268.39 5,064.38 4,972.85 13 Year 4 (2009) 9-Jun 5.7 5126.64 4,757.88 4,892.79 1.05 0.996 5,149.34 5,153.32 5,130.59 14   9-Sep 5.9 5306.52 5,027.70 5,212.08 1.02 1.025 5,177.23 5,242.25 5,373.16 15   9-Dec 6.1 5486.40 5,396.46 5,373.97 1.02 0.992 5,531.10 5,331.19 5,288.11 16   10-Mar 6.3 5666.28 5,351.49 5,420.07 1.05 0.982 5,770.57 5,420.13 5,322.18 17 Year 5 (2010) 10-Jun 5.5 4946.76 5,488.65 5,513.38 0.90 0.996 4,968.67 5,509.07 5,484.78 18   10-Sep 6.51 5855.16 5,538.12 5,550.48 1.05 1.025 5,712.51 5,598.01 5,737.80 19   10-Dec 6.32 5684.27 5,562.85 5,710.13 1.00 0.992 5,730.58 5,686.95 5,640.99 20   11-Mar 6.41 5765.22 5,857.41 5,903.50 0.98 0.982 5,871.33 5,775.88 5,671.50 21 Year 6 (2011) 11-Jun 6.81 6124.98 5,949.60 6,053.03 1.01 0.996 6,152.11 5,864.82 5,838.96 22   11-Sep 6.92 6223.92 6,156.46 6,209.30 1.00 1.025 6,072.28 5,953.76 6,102.44 23   11-Dec 7.24 6511.73 6,262.14 6,307.11 1.03 0.992 6,564.78 6,042.70 5,993.86 24   12-Mar 6.88 6187.94 6,352.08 6,397.05 0.97 0.982 6,301.83 6,131.64 6,020.82 25 year 7 (2012) 12-Jun 7.21 6484.75 6,442.02 6,409.42 1.01 0.996 6,513.47 6,220.57 6,193.14 26   12-Sep 7.32 6583.68 6,376.82 6,389.18 1.03 1.025 6,423.28 6,309.51 6,467.07 27   12-Dec 6.95 6250.90 6,401.55 6,400.43 0.98 0.992 6,301.83 6,398.45 6,346.74 28   13-Mar 6.99 6286.88 6,399.30 6,374.57 0.99 0.982 6,402.59 6,487.39 6,370.15 29 Year 8 (2013) 13-Jun 7.2 6475.75 6,349.83 6,389.18 1.01 0.996 6,504.43 6,576.33 6,547.33 30   13-Sep 7.1 6385.81 6,428.53     1.025 6,230.23 6,665.27 6,831.71 31   13-Dec 7.3 6565.69       0.992 6,619.19 6,754.20 6,699.62 32   14-Mar   0.00       0.982   6,843.14 6,719.47 33 Year 9 (2014) 14-Jun   0.00       0.996   6,932.08 6,901.51 34   12-Sep   0.00       1.025   7,021.02 7,196.35 35   12-Dec   0.00       0.992   7,109.96 7,052.50 36   13-Mar   0.00       0.982   7,198.89 7,068.79 37   15-Jun   0.00       0.996   7,287.83 7,255.70 Appendix 2 Quarter St 1 1.00 2 1.03 3 0.99 4 0.98 Appendix 3 (Naïve seasonal method) MAD 222.93 MSE 67482.43 MAPE 4.38 Appendix 4 t Year Quarter Volume (%) Company Sales M.A CMA Error MAD MSE MAPE 1 Year 1 (2006) 6-Jun 4.8 4317.17             2   6-Sep 4.95 4452.08             3   6-Dec 4.5 4047.35 4,375.63 4,361.01 -328.28 328.28 107,770.81 8.11 4   7-Mar 5.21 4685.93 4,346.40 4,340.78 339.53 339.53 115,278.77 7.25 5 year 2 (2007) 7-Jun 4.67 4200.24 4,335.16 4,425.10 -134.91 134.91 18,201.11 3.21 6   7-Sep 4.9 4407.11 4,515.04 4,480.19 -107.93 107.93 11,648.71 2.45 7   7-Dec 5.3 4766.87 4,445.33 4,538.65 321.54 321.54 103,387.38 6.75 8   8-Mar 4.9 4407.11 4,631.96 4,721.90 -224.85 224.85 50,558.65 5.10 9 Year 3 (2008) 8-Jun 5.5 4946.76 4,811.84 4,766.87 134.91 134.91 18,201.11 2.73 10   8-Sep 5.7 5126.64 4,721.90 4,694.92 404.73 404.73 163,810.02 7.89 11   8-Dec 4.9 4407.11 4,667.94 4,690.42 -260.83 260.83 68,031.72 5.92 12   9-Mar 4.66 4191.25 4,712.91 4,735.39 -521.66 521.66 272,126.86 12.45 13 Year 4 (2009) 9-Jun 5.7 5126.64 4,757.88 4,892.79 368.76 368.76 135,982.54 7.19 14   9-Sep 5.9 5306.52 5,027.70 5,212.08 278.82 278.82 77,738.98 5.25 15   9-Dec 6.1 5486.40 5,396.46 5,373.97 89.94 89.94 8,089.38 1.64 16   10-Mar 6.3 5666.28 5,351.49 5,420.07 314.79 314.79 99,094.95 5.56 17 Year 5 (2010) 10-Jun 5.5 4946.76 5,488.65 5,513.38 -541.89 541.89 293,649.68 10.95 18   10-Sep 6.51 5855.16 5,538.12 5,550.48 317.04 317.04 100,515.65 5.41 19   10-Dec 6.32 5684.27 5,562.85 5,710.13 121.42 121.42 14,742.90 2.14 20   11-Mar 6.41 5765.22 5,857.41 5,903.50 -92.19 92.19 8,498.91 1.60 21 Year 6 (2011) 11-Jun 6.81 6124.98 5,949.60 6,053.03 175.38 175.38 30,759.88 2.86 22   11-Sep 6.92 6223.92 6,156.46 6,209.30 67.46 67.46 4,550.28 1.08 23   11-Dec 7.24 6511.73 6,262.14 6,307.11 249.59 249.59 62,293.31 3.83 24   12-Mar 6.88 6187.94 6,352.08 6,397.05 -164.14 164.14 26,942.70 2.65 25 year 7 (2012) 12-Jun 7.21 6484.75 6,442.02 6,409.42 42.72 42.72 1,825.17 0.66 26   12-Sep 7.32 6583.68 6,376.82 6,389.18 206.86 206.86 42,792.84 3.14 27   12-Dec 6.95 6250.90 6,401.55 6,400.43 -150.65 150.65 22,695.78 2.41 28   13-Mar 6.99 6286.88 6,399.30 6,374.57 -112.43 112.43 12,639.66 1.79 29 Year 8 (2013) 13-Jun 7.2 6475.75 6,349.83 6,389.18 125.92 125.92 15,855.19 1.94 30   13-Sep 7.1 6385.81 6,428.53   -42.72 42.72 1,825.17 0.67 31   13-Dec 7.3 6565.69       6,241.91 1,889,508.08 122.64 References Almarashi, A.M. and Kashif, M., 2015. Modelling and Continuation of Seasonal Time Series. International Journal of Intelligent Technologies & Applied Statistics, 8(4). Box, G. E., Jenkins, G. M., Reinsel, G. C., & Ljung, G. M. (2015). Time series analysis: forecasting and control. John Wiley & Sons. https://www.youtube.com/watch?v=grIVP-Mh6dg http://www.slideshare.net/sachin.bme.abs/time-series-analysis Read More