Business Forecasting Case Study

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

Case Study 41

Case Study: Forecast analysis

A case of Macquarie Mountain Estate Co-op, Ltd

Table of Contents

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

Determination of MAD, MSE 10

Multiple regression 10

Forecast using Winter/exponential method 12

Recommendations and Conclusions 14

14Creating a competitive advantage

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:

Business Forecasting Case Study

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.

Business Forecasting Case Study 1

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

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.

Business Forecasting Case Study 2

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.

Business Forecasting Case Study 3

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.

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

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 2

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.

https://www.youtube.com/watch?v=grIVP-Mh6dg