• Category:
• Document type:
Case Study
• Level:
• Page:
4
• Words:
2267

Case Study 41

Case Study: Forecast analysis

A case of Macquarie Mountain Estate Co-op, Ltd

Executive Summary 3

Introduction 4

Methodology 4

5Reason for choosing a particular method

6Other Methods

Results 6

Company sales 6

Determination of the pattern of data followed 7

Autocorrelation (Smoothing out the seasonal irregularities) 8

Determination of a trend pattern 9

Multiple regression 10

Forecast using Winter/exponential method 12

Recommendations and Conclusions 14

14Differentiation

Appendices 14

14Appendix 1

16Appendix 2

Appendix 3 (Naïve seasonal method) 16

Appendix 4 16

References 17

## 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 Volume (%) Year 1 (2006) year 2 (2007) Year 3 (2008) Year 4 (2009) Year 5 (2010) Year 6 (2011) year 7 (2012) Year 8 (2013)

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:

 Volume (%) Company Sales Year 1 (2006) 4,375.63 4,361.01 4,346.40 4,340.78 year 2 (2007) 4,335.16 4,425.10 4,515.04 4,480.19 4,445.33 4,538.65 4,631.96 4,721.90 Year 3 (2008) 4,811.84 4,766.87 4,721.90 4,694.92 4,667.94 4,690.42 4,712.91 4,735.39 Year 4 (2009) 4,757.88 4,892.79 5,027.70 5,212.08 5,396.46 5,373.97 5,351.49 5,420.07 Year 5 (2010) 5,488.65 5,513.38 5,538.12 5,550.48 5,562.85 5,710.13 5,857.41 5,903.50 Year 6 (2011) 5,949.60 6,053.03 6,156.46 6,209.30 6,262.14 6,307.11 6,352.08 6,397.05 year 7 (2012) 6,442.02 6,409.42 6,376.82 6,389.18 6,401.55 6,400.43 6,399.30 6,374.57 Year 8 (2013) 6,349.83 6,389.18 6,428.53

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

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 Adjusted R Square Standard Error Observations Significance F Regression 19616790.51 Residual 91574.67933 Coefficients Standard Error Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 35.88523175 14.63613052

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:

 Volume (%) Company Sales Forecast Running MAD Running Sum Forecast Error Tracking Signal Year 1 (2006) 4344.152 -0.75001 0.861758 year 2 (2007) 4365.017 0.298575 4332.063 0.696917 4347.072 4431.032 2.517636 Year 3 (2008) 4426.248 4.186346 4530.349 5.681195 4649.607 4.936862 4601.107 3.380914 Year 4 (2009) 4519.136 4.985296 4640.636 6.510696 4773.813 7.956201 9.331001 Year 5 (2010) 5066.321 9.447762 5042.408 10.85193 5204.958 11.91005 5300.821 12.94972 Year 6 (2011) 14.17103 5539.957 15.32909 5676.749 16.54788 5843.745 17.48705 year 7 (2012) 5912.584 18.54484 6027.016 19.58975 6138.349 20.42731 6160.859 21.27447 Year 8 (2013) 6186.063 22.20399 6244.001 23.06626 6272.363 10069.30 24.00473 6331.029 6331.029 6.126631 Year 9 (2014) 5064.823 5064.823 -1326.56 -1.77022 4051.858 4051.858 -5378.41 -6.33164 3241.487 3241.487 -8619.90 -9.37145 2593.189 2593.189 -11213.09 -11.5884 2074.551 2074.551 -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.

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.

## Appendix 1

 Y(89941) Trend comp Volume (%) Deseasonalize Prediction Year 1 (2006) 4,336.29 4,086.06 4,068.04 4,343.61 4,175.00 4,279.25 4,375.63 4,361.01 4,080.32 4,263.94 4,229.48 4,346.40 4,340.78 4,772.17 4,352.87 4,274.21 year 2 (2007) 4,335.16 4,425.10 4,218.85 4,441.81 4,422.22 4,515.04 4,480.19 4,299.74 4,530.75 4,643.89 4,445.33 4,538.65 4,805.71 4,619.69 4,582.35 4,631.96 4,721.90 4,488.22 4,708.63 4,623.53 Year 3 (2008) 4,811.84 4,766.87 4,968.67 4,797.56 4,776.41 4,721.90 4,694.92 5,001.73 4,886.50 5,008.53 4,667.94 4,690.42 4,443.02 4,975.44 4,935.23 4,712.91 4,735.39 4,268.39 5,064.38 4,972.85 Year 4 (2009) 4,757.88 4,892.79 5,149.34 5,153.32 5,130.59 5,027.70 5,212.08 5,177.23 5,242.25 5,373.16 5,396.46 5,373.97 5,531.10 5,331.19 5,288.11 5,351.49 5,420.07 5,770.57 5,420.13 5,322.18 Year 5 (2010) 5,488.65 5,513.38 4,968.67 5,509.07 5,484.78 5,538.12 5,550.48 5,712.51 5,598.01 5,737.80 5,562.85 5,710.13 5,730.58 5,686.95 5,640.99 5,857.41 5,903.50 5,871.33 5,775.88 5,671.50 Year 6 (2011) 5,949.60 6,053.03 6,152.11 5,864.82 5,838.96 6,156.46 6,209.30 6,072.28 5,953.76 6,102.44 6,262.14 6,307.11 6,564.78 6,042.70 5,993.86 6,352.08 6,397.05 6,301.83 6,131.64 6,020.82 year 7 (2012) 6,442.02 6,409.42 6,513.47 6,220.57 6,193.14 6,376.82 6,389.18 6,423.28 6,309.51 6,467.07 6,401.55 6,400.43 6,301.83 6,398.45 6,346.74 6,399.30 6,374.57 6,402.59 6,487.39 6,370.15 Year 8 (2013) 6,349.83 6,389.18 6,504.43 6,576.33 6,547.33 6,428.53 6,230.23 6,665.27 6,831.71 6,619.19 6,754.20 6,699.62 6,843.14 6,719.47 Year 9 (2014) 6,932.08 6,901.51 7,021.02 7,196.35 7,109.96 7,052.50 7,198.89 7,068.79 7,287.83 7,255.70

## Appendix 3 (Naïve seasonal method)

 67482.43

## Appendix 4

 Volume (%) Company Sales Year 1 (2006) 4,375.63 4,361.01 107,770.81 4,346.40 4,340.78 115,278.77 year 2 (2007) 4,335.16 4,425.10 18,201.11 4,515.04 4,480.19 11,648.71 4,445.33 4,538.65 103,387.38 4,631.96 4,721.90 50,558.65 Year 3 (2008) 4,811.84 4,766.87 18,201.11 4,721.90 4,694.92 163,810.02 4,667.94 4,690.42 68,031.72 4,712.91 4,735.39 272,126.86 Year 4 (2009) 4,757.88 4,892.79 135,982.54 5,027.70 5,212.08 77,738.98 5,396.46 5,373.97 8,089.38 5,351.49 5,420.07 99,094.95 Year 5 (2010) 5,488.65 5,513.38 293,649.68 5,538.12 5,550.48 100,515.65 5,562.85 5,710.13 14,742.90 5,857.41 5,903.50 8,498.91 Year 6 (2011) 5,949.60 6,053.03 30,759.88 6,156.46 6,209.30 4,550.28 6,262.14 6,307.11 62,293.31 6,352.08 6,397.05 26,942.70 year 7 (2012) 6,442.02 6,409.42 1,825.17 6,376.82 6,389.18 42,792.84 6,401.55 6,400.43 22,695.78 6,399.30 6,374.57 12,639.66 Year 8 (2013) 6,349.83 6,389.18 15,855.19 6,428.53 1,825.17 6,241.91 1,889,508.08

## 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.