NOVA SOUTHEASTERN UNIVERSITY
The Wayne Huizenga Graduate School of Business and Entrepreneurship-Master's ProgramS

Assignment for Course: QNT 5040- Business Modeling

Submitted to: Dr. Tom Griffin

Submitted by: Prince A. Storr ps44@nova.edu

Date of Course Meeting: November 18, 2011

Date of Submission: November 18, 2011

Title of Assignment: Greaves Brewery: 10 Month Forecasting

CERTIFICATION OF AUTHORSHIP: I certify that I am the author of this paper and any assistance that I received in its preparation is fully acknowledged and disclosed in the paper. I have also cited any sources from which I used data, ideas, or words, either quoted directly or paraphrased. I also certify that this paper was prepared by me specifically for this course.

Student Signature: Prince A Storr

Instructor(s Grade on Assignment:

Instructor(s Comments:
Greaves Brewery: Ten Month Sales Forecasting
Case Synopsis Alex Benson, purchasing manager for Greaves Brewery in Trinidad was faced with a dilemma in early 2004. He encountered difficulty in forecasting sales for 2004; particularly because of the 2003 slump, government excise taxes and other factors such as decreased numbers in both tourist arrivals to the Caribbean island and beer exports to the U.S. As purchasing manager, Benson’s prime responsibility was maintaining adequate inventory levels for all goods and materials used in the company’s production processes, including the purchase of new bottles and the scheduling of deliveries (Erskine, 2004).
State the Assignment Question As purchasing manager, Benson was responsible for all goods and materials used in the company’s production process, inclusive of new bottles purchase. Benson had to be cognizant of the fact that orders for new bottles had to be ordered four months in advance to allow for supplier transportation and lead times. After comparing stock totals such as empty bottles, finished goods new bottle purchases and delivery records, Benson have to determine what new bottle quantities he must order for the next year.
Case Analysis Greaves Brewery was founded in 1924 on the southern Caribbean island of Trinidad. The beer with its excellent reputation became a favorite among tourists and as a result garnered modest export numbers into the United States in 2000. In regards to sales, the company traditionally experienced four peaks during the year: Carnival during February or March, Christmas, Easter and Independence which is celebrated in August. Greaves experienced its highest sales numbers ever in February of 2004. However, this alone could not determine the sales for the remainder of the year because the sales increase in 2003 was well below the trend average. Benson’s main task as purchasing manager was to ensure that an adequate supply of all goods and materials used in the company’s production processes was maintained. This was particularly important for the purchasing of new bottles and the scheduling of new deliveries. Benson was experiencing some difficulty in forecasting beer sales for 2004, mainly because of the 2003 slump, government excise taxes and other factors such as tourist arrivals and U.S. sales. Another important factor in Benson determining the correct order levels for new bottles is that he did not want to make the same mistakes as he did with past bottle purchases. The order of 66,000 new bottles Benson placed in 2000 caused the company a costly shortfall in 2001. He tried to recover for the following year by ordering 182,000 cases. Unfortunately, he increased his 2002 numbers by ordering 195,000 cases and this resulted in the highest empty bottle stock in the company’s history by the end of February 2003. He subsequently reduced his 2003 order to 122,000 cases. Obviously, ordering new bottle stocks could not be left up to just pure guessing or a “gut feeling”. In order to make a sound projection of what new bottle orders to place, Benson has to review his stock control sheets showing empty bottle stocks, finished goods stocks, new bottle purchases and delivery records prior to placing any orders. Additionally, he must take into account new bottle breakage numbers and the strong possibility of the current bottle design obsolescence due to the introduction of a new bottle design in late 2005 or 2006. Orders had to be placed four months in advance to allow for supplier and transportation lead times. He had to be certain of his orders because cancellation charges were very high and therefore reducing placed orders would not be a wise financial decision. He would be able to increase his order provided minimum order quantities and lead time were met. Standard ordering practice was to order seventy five percent of the yearly requirements in March with the remaining order being placed in August. Another factor that had to be taken into account prior to new bottle orders being placed is the empty bottle flows. These were either returns from the trade or new bottles from the warehouse. The warehouse space was limited to barely storing the returned bottles. This meant that if an excessive amount of new bottles were purchased, an extra cost may be incurred by having to provide storage space for the new bottles. Benson encountered difficulty in trying to determine the turnaround time for bottles. Turnaround time was the elapsed time from bottles being removed from storage through processing, finished goods, retailer, consumer, retailer again, and returns to storage. In the absence of reliable data, there were basically estimations as to the turnaround times and total stock replacements with new bottles. Benson always met with the plant and sales managers prior to placing an order for new bottles. He had previously discussed the problems regarding the forecasting with both managers but neither was in agreement with Benson’s predictions. Keeping in mind that his drop dead date for ordering was 4 March 2004, Benson requested a meeting with the two managers for 2 March and regardless of the uncertainties, he would have to have some convincing proposals ready for this meeting. Benson can utilize some forecasting techniques to determine what levels of new bottles to order for the upcoming year. The forecasting will utilize time series data, autocorrelation, runs test and moving averages with various spans to determine the best forecast numbers. Data Analysis to Support Decision Initially, the time series graph (Figure 1) was constructed to observe the seasonal trend of monthly sales ranging from January 1999 through February 2004. This graph demarcates the random component or noise that gives most series graph their asymmetrical, jagged edge posture.

[pic]
Figure 1: Time Series for Beer Sales The next graph which is the time series of Residuals (Figure 2) is closely related to the times series of Beer Sales graph meaning that there is much similarity. The only difference is that it is shifted down by the average of the residuals which is 290.0484 and has a mean of 0. Next a runs test was conducted to determine whether or not randomness exist in the Sales data. A runs test is one of the formal methods for testing randomness.

[pic] Figure 2: Time Series of Residuals

If there are too many or too few runs in the series, then we conclude that the series is not random. In this case it shows that the number of observed runs stands at 18. The number of expected runs under the supposition of randomness is 31. There are too few runs pursuant to the series of residuals. In addition, according to the P-Value this is 0.0003 in this case far below the average of 0.05 which indicates that the null hypothesis of randomness is rejected and as a result concludes that there is nil random noise (Albright, Winston & Zappe, 2010).

|StatTools Report | |
|Analysis: |Runs Test for Randomness |
|Performed By: |Prince Storr | |
|Date: |Thursday, November 17, 2011 |
|Updating: |Live | |
| | | |
| |Residuals | |
|Runs Test for Randomness |Data Set #1 | |
|Observations |62 | |
|Below Mean |30 | |
|Above Mean |32 | |
|Number of Runs |18 | |
|Mean |0.00 | |
|E(R) |31.9677 | |
|StdDev(R) |3.9005 | |
|Z-Value |-3.5810 | |
|P-Value (two-tailed) |0.0003 | |

Figure 3: Runs Test for Randomness The Autocorrelation graph (figure 4) shows whether the Sales variable is linearly related to any of the lags. The first two lags along with lag numbers 6 and 12 under the Autocorrelation Table are significantly positive which are 0.3521, 0.3675, 0.3707, and 0.5234. These lags are highlighted in bold because they are double the Standard Error which is 0.1270. [pic]
Figure 4: Autocorrelation of Residuals.
| |Residuals |
|Autocorrelation Table |Data Set #1 |
|Number of Values |62 |
|Standard Error |0.1270 |
|Lag #1 |0.3521 |
|Lag #2 |0.3675 |
|Lag #3 |0.0913 |
|Lag #4 |0.2143 |
|Lag #5 |0.1884 |
|Lag #6 |0.3707 |
|Lag #7 |0.1376 |
|Lag #8 |0.1267 |
|Lag #9 |-0.1275 |
|Lag #10 |0.2289 |
|Lag #11 |0.1940 |
|Lag #12 |0.5234 |
|Lag #13 |0.1615 |
|Lag #14 |0.1682 |
|Lag #15 |-0.1316 |

Figure 4 (cont’d): Autocorrelation of Residuals. A moving average (smoothing method averages) is the amount of observation in the past few periods, where the number of terms in the average is the span. To apply the moving averages technique we first need to determine a span, the amount of terms in each moving average. As you can see listed in the table below it clearly shows that the Span 7 Mean Absolute Error (MAE) is lower than Span 3 and Span 5. Therefore Span 7 model was chosen to run the exponential smoothing.
|Method |Mean Abs Error |
|Moving Average Span 3 |62.10 |
|Moving Average Span 5 |56.56 |
|Moving Average Span 7 |54.89 |

The Exponential Smoothing Table is listed below. The Winters model Mean Absolute Error (MAE) which stands at 26.05 is much lower than both the Simple model and the Holts model which is 52.67 and 59.01 respectively. Therefore, Winters model was deemed most suitable for forecasting the 2004 Sales from March to December (10 months period) for Greaves Brewery.

|Exponential Smoothing Method |Mean Abs Error |
|Simple |52.67 |
|Holts |59.01 |
|Winters |26.05 |

Winters results are demarcated in the Forecast table below.
|StatTools Report | | |
|Analysis: |Forecast | |
|Performed By: |Prince Storr | |
|Date: |Friday, November18, 2011 |
|Updating: |Live/Unlinked | |
| | | |
| | | |
|Forecasting Constants (Optimized) | | |
|Level (Alpha) |0.029 | |
|Trend (Beta) |0.000 | |
|Season (Gamma) |0.000 | |
| | | |
| | | |
|Winters' Exponential | | |
|Mean Abs Err |26.05 | |
|Root Mean Sq Err |37.12 | |
|Mean Abs Per% Err |8.91% | |
| | | |
| | | |

In the Forecasting Data Table presented below illustrates the forecast from January 1999 to February 2004. In addition, this table shows winters forecast sales from March 2004 to December 2004 (10 months) which is 3,516,910. Furthermore, including January and February sales which are 365,500 and 547,120 respectively will boost 2004 annual sales to 4,429,530. Note: Annual Forecast Sales for 2004 is highlighted in Bold in the table below.
|Forecasting Data |Sales |Level |Trend |Season |Forecast |
|Analysis: |Regression | | | | |
| | | | | | | |
| |Multiple |R-Square |Adjusted |StErr of | | |
|Summary |R | |R-Square |Estimate | | |
| |0.5889 |0.3469 |0.3360 |69.94514069 | | |
| | | | | | | |
| |Degrees of |Sum of |Mean of |F-Ratio |p-Value | |
|ANOVA Table |Freedom |Squares |Squares | | | |
|Explained |1 |155889.4925 |155889.4925 |31.8641 |< 0.0001 | |
|Unexplained |60 |293539.3624 |4892.322706 | | | |
| | | | | | | |
| |Coefficient |Standard |t-Value |p-Value |Confidence Interval 95% |
|Regression Table | |Error | | |Lower |Upper |
|Constant |-3124.301049 |604.9283274 |-5.1647 |< 0.0001 |-4334.337865 |-1914.264234 |
|Year/Month |0.092058958 |0.016308544 |5.6448 |< 0.0001 |0.059437013 |0.124680902 |

Conclusion Annual Total Sales for 2004 according to Winters’ method was forecasted at 4,429,530. The forecasted sales for the ten-month period ranging from March through to December 2004 are 3516,910. In the table below we took New Bottle Orders divided by Annual Sales to compute the percentage. Example: In 1995, 96 (New Orders) / 1845 (Sales) = 0.05% or 5%. We then calculated the average for the percentage which was 0.04%. Finally, we took the average which was 4% plus a half percentage point for breakage (less than one percent). The 4.5% was used to calculate the New Bottle Orders.

|Year |Ann Sales |New Orders |Percentage | |
|1995 |1845 |96 |0.05 | |
|1996 |2088 |70 |0.03 | |
|1997 |2345 |123 |0.05 | |
|1998 |2876 |71 |0.02 | |
|1999 |2465 |73 |0.03 | |
|2000 |3266 |66 |0.02 | |
|2001 |3325 |182 |0.05 | |
|2002 |3972 |195 |0.05 | |
|2003 |4078 |122 |0.03 | |
|2004 |4430 | 199 |0.045 | |
| | | | | |
| | |Average Percentage = 0.04% |
| | |Breakage estimate = 0.005% |

Note: Numbers in above table are in thousands of cases Based on the forecasted sales for the ten month period, it is concluded that Benson should order 199,000 cases of new bottles to ensure that he has an adequate supply for the coming year. It is noted that the annual sales for 2004 is trending way above normal and the forecast indicates the same. The obsolescence factor is of no significance because the changeover in bottle design is not expected until late 2005 or even into 2006. Adjustments can be made in March of 2005 when it is clearer as to when the new bottle design will come into effect.

| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |

References
Albright, Winton, & Zappe (2010). Business modeling: QNT 5040 Nova Southeastern University (selection from 3rd ed.).
Erskine, J (2004). Greaves Brewery Bottle Replenishment. From The University of Western Ontario Richard Ivey School of Business.

-----------------------
95