ACC 430 Module 5 Forecasting Assignment Example

Forecasting Through the School-Year Spike: Testing Three Methods on 36 Months of Distributor Sales

[Student Name]

Southern New Hampshire University

ACC 430: Data Analytics for Financial Professionals

Module Five Assignment

[Instructor Name]

[Date]

The organization, setting and figures below are a composite written as a model document. No real employer, client, colleague or patient is described.

Forecasting Through the School-Year Spike: Testing Three Methods on 36 Months of Distributor Sales

Introduction

The distributor's purchasing manager orders paper, chemicals and liners months ahead, and the operations manager hires seasonal warehouse staff each spring. Both need a monthly sales forecast they can trust. The current practice, adding 3 percent to last year's total and dividing by twelve, misses the most important feature of the business: school districts buy heavily in July and August to prepare for the new year. This assignment explores 36 months of sales, compares three forecasting methods on data held out from fitting and produces a 12-month forecast with a range.

Exploring the Data

Monthly sales over 36 months averaged about $9.8 million, rising about 3 percent a year. The seasonal pattern repeats each year: July and August run about 28 percent above the monthly average, driven by school restocking; December runs about 12 percent below, as schools close and offices slow down; other months sit near the average. A decomposition of the series into trend, seasonal and remainder components confirmed that the seasonal pattern is stable in shape and grows roughly in proportion to sales, which favors a method that models seasonality explicitly (Hyndman & Athanasopoulos, 2021).

Methods Compared

The first 30 months were used to fit each method and the last six held out as a test.

The seasonal naive method simply repeats last year's figure for the matching month. It captures seasonality but not growth.

The 12-month moving average uses the mean of the latest twelve months as next month's figure. It captures the level but smooths away seasonality entirely.

Holt-Winters exponential smoothing, in its multiplicative form, maintains estimates of the level, the trend and a seasonal index for each month, updating them as each new month arrives and giving more weight to recent data. It is designed for series like this one.

Accuracy on the Holdout

Table 1. Forecast Accuracy on the Six-Month Holdout

Mean absolute percentage error is used because it is easy for managers to understand and sales are never near zero, which avoids its main weakness. Hyndman and Koehler (2006) caution that percentage errors behave badly when actual values are small and recommend scaled measures for comparing series of different sizes; for a single series of large monthly values, the percentage error is adequate, and the mean absolute error in dollars is reported alongside it. On both measures, Holt-Winters roughly halves the error of the alternatives. The moving average performs worst because it forecast August as an ordinary month.

The Forecast

Refit on all 36 months, Holt-Winters forecasts sales of $121.2 million for the next 12 months, about 2.7 percent above the last 12. July and August together are forecast at about $25.6 million. Prediction intervals at 80 percent give an annual range of roughly $117 million to $125 million, and each monthly forecast carries its own interval, wider in July and August when variation has been greatest. Richardson et al. (2021) stress that communicating uncertainty is part of communicating a forecast; a single figure invites decisions that assume it will be exactly right.

What Could Break the Forecast

The forecast assumes the future resembles the past. Three events could change that. The company's largest school district contract, about 6 percent of sales, renews in May; losing it would cut July and August sharply. A competitor has opened a warehouse in Tucson. And supplier price increases on paper products, if passed through, would raise dollar sales without changing volume. The finance team will adjust the forecast when any of these is known rather than waiting for the model to learn from actual data months later.

Why Not a Regression or Machine Learning?

Two other approaches were considered. A regression with a time trend and monthly dummy variables would capture the same seasonal pattern and is easy to explain, and on the holdout it performed similarly to Holt-Winters, with an error of 5.2 percent. Holt-Winters was preferred because it adapts automatically as new months arrive, giving more weight to recent behavior, which matters if the school spike grows or shrinks. A machine learning model using many inputs, such as weather, school calendars and economic indicators, could in principle do better, but with only 36 monthly observations it would have more parameters than the data can support and would likely overfit, fitting past noise rather than future patterns. For a series this short and this regular, a method designed for seasonal data is the sensible choice, and its simplicity makes it easier for purchasing and operations managers to trust.

Monthly Detail

The annual figure matters less to operations than the monthly shape. The forecast puts June at about $10.4 million as schools begin ordering, July at $12.9 million, August at $12.7 million and September back near $10.1 million, with December at about $8.8 million. The jump from June to July, about 24 percent, is the figure that drives warehouse hiring and the timing of large paper orders.

Uses

Purchasing will use the monthly forecast to schedule orders of paper and liners for June arrival, ahead of the school spike. Operations will hire seasonal warehouse staff for June through August based on the forecast volume, not last year's headcount. Finance will use the monthly figures in the cash budget, since receivables from schools peak in September and October. Each month, actual sales will be compared with the forecast and the model refit, and the error tracked, so that the team learns whether the method keeps working.

Conclusion

Tested on six months it had not seen, Holt-Winters exponential smoothing forecast the distributor's sales with a 4.8 percent average error, about half that of the simpler methods, because it models the July and August school spike and modest growth. Its 12-month forecast of $121.2 million, with an 80 percent range of about $117 to $125 million, gives purchasing, staffing and cash planning a pattern and a measure of uncertainty they did not have.

References

Hyndman, R. J., & Athanasopoulos, G. (2021). Forecasting: Principles and practice (3rd ed.). OTexts.

Hyndman, R. J., & Koehler, A. B. (2006). Another look at measures of forecast accuracy. International Journal of Forecasting, 22(4), 679-688. https://doi.org/10.1016/j.ijforecast.2006.03.001

Richardson, V. J., Teeter, R. A., & Terrell, K. L. (2021). Data analytics for accounting (2nd ed.). McGraw Hill.