OPS 330 Week 4 Forecasting and Optimization Example

Spring Rush and a Product Mix Puzzle: Forecasting Demand and Optimizing Production at an Oklahoma Trailer Plant

[Student Name]

University of Phoenix

OPS/330: Strategic Operations and Logistics

Week 4 Assignment

[Instructor Name]

[Date]

Red Dirt Trailer Works, its order history, forecasts and model figures are composites written for a model paper.

Red Dirt Trailer Works, the composite Oklahoma trailer builder followed in this course, needs two kinds of answers. Sales and purchasing need to know how many orders to expect each week, so they can quote reliable dates and buy aluminum on time. Production needs to know which trailers to build when welding and assembly capacity are limited. This paper applies forecasting to the first question and optimization to the second.

The Demand Pattern

Two years of weekly dealer orders show an average of about 22 orders a week but a strong seasonal pattern. Ranchers buy before spring calving and fall shipping, so orders run high from February through May and again in September and October, and low in summer and winter. Seasonal indices calculated from the first year are about 1.35 for spring, 0.80 for summer, 1.10 for fall and 0.75 for winter, meaning spring weeks average 35 percent above the yearly average.

Comparing Three Forecasting Methods

Each method forecast the second year's weekly orders one week ahead.

Naive: next week equals this week. Mean absolute percentage error (MAPE): about 24 percent.

Four-week moving average: MAPE about 19 percent. Smoother, but it lags behind seasonal turns, under-forecasting every February and over-forecasting every June.

Exponential smoothing with seasonal indices: deseasonalize each week's orders, smooth them with a factor of 0.2 and multiply the smoothed level by the coming week's seasonal index. MAPE about 11 percent.

Gardner (1985) reviewed exponential smoothing methods and concluded that they were robust, simple and competitive in accuracy with more complex methods across many data sets. Makridakis and Hibon (2000), reporting a large forecasting competition, reported that elaborate statistical techniques often lost to plain ones when tested on thousands of real series. The seasonal smoothing method is simple enough for the sales coordinator to update in a spreadsheet each Monday and clearly the most accurate of the three.

Using the Forecast

With the forecast, the planner can see that next spring's weeks will average about 30 orders against a welding capacity of about 26 after the Week 1 improvements. Orders above capacity will join the backlog, so quotes in spring should lengthen by the expected queue. Purchasing can order aluminum against the forecast and the reorder point from Week 3. Fildes and Goodwin (2007) found that managers frequently adjust statistical forecasts by judgment, sometimes improving them but often adding bias; Red Dirt will allow adjustments only when sales staff record a specific reason, such as a large dealer order already confirmed.

A forecast that knows it is spring beats a cleverer one that does not.

The Product Mix Question

In spring, demand exceeds capacity, so the plant must choose what to build. Two families dominate: stock trailers (S) and living-quarters horse trailers (H). Per trailer, a stock trailer needs about 12 welding hours and 14 assembly hours and contributes about $3,600 toward fixed costs and profit; a living-quarters trailer needs about 20 welding hours and 36 assembly hours, because of cabinets, plumbing and wiring, and contributes about $9,200. After the Week 1 improvements, welding has about 330 hours a week and assembly about 420. Spring demand allows up to about 20 stock trailers and 8 living-quarters trailers a week.

The Model

Decision variables: S and H, trailers of each family built per week.

Objective: maximize contribution, 3,600 S plus 9,200 H.

Constraints, written first in words: welding hours used cannot exceed 330, so 12 S plus 20 H is at most 330; assembly hours used cannot exceed 420, so 14 S plus 36 H is at most 420; stock trailers built cannot exceed 20 and living-quarters trailers cannot exceed 8; neither can be negative.

Solving It

Checking the corner points of the feasible region gives the answer. With S at its demand limit of 20, welding allows H up to 4.5 but assembly allows only about 3.9, so the corner is S = 20, H = 3.9, contributing about $72,000 plus $35,900, or about $107,900 a week. With H at its limit of 8, assembly allows S up to about 9.4, contributing about $33,900 plus $73,600, or about $107,500. The first corner is slightly better, building about 24 trailers a week. In both corners, the assembly constraint is fully used while welding has spare hours.

What the Model Says

Three findings matter. First, after the welding improvements, assembly has become the binding constraint: the bottleneck has moved. Each additional assembly hour is worth about $257 of contribution, about what a stock trailer earns per assembly hour, so overtime in assembly at a loaded cost near $45 an hour would pay off. Second, the two families earn almost the same contribution per assembly hour, about $257 and $256, so the plant can accept living-quarters orders when dealers need them without much loss. Third, the plant should not invest further in welding until assembly capacity grows.

Testing the Answer

Before acting on the model, the production manager tested it against two real spring weeks from last year. In one, the plant had built mostly living-quarters trailers to satisfy a large dealer and had run assembly overtime every night; the model explains why that week felt so strained. In the other, the plant built mostly stock trailers and finished early on Friday, a sign that assembly was not binding when the mix tilted toward simpler trailers. Both fit the model's logic, which gave the team confidence to use it for planning.

Sharing the Results

The sales coordinator now receives a weekly note showing the forecast, the planned mix and the expected backlog, so dealers can be told honestly when a living-quarters trailer ordered in March will ship. The production manager uses the model's assembly finding to plan overtime in advance rather than react to it.

Assumptions and Limits

The model assumes constant hours and contribution per trailer, while real trailers vary with options. It treats trailers as divisible within a week, which is acceptable for planning over several weeks. Demand limits come from the forecast and carry its error.

Conclusion

Seasonal exponential smoothing forecasts Red Dirt's orders far better than simpler methods and gives sales and purchasing a reliable basis for quotes and material buying. A two-variable linear program shows the best spring product mix and reveals that assembly, not welding, now limits contribution. Together the two analyses tell the company when demand will exceed capacity and where the next improvement should go.

References

Fildes, R., & Goodwin, P. (2007). Against your better judgment? How organizations can improve their use of management judgment in forecasting. Interfaces, 37(6), 570-576. https://doi.org/10.1287/inte.1070.0309

Gardner, E. S., Jr. (1985). Exponential smoothing: The state of the art. Journal of Forecasting, 4(1), 1-28. https://doi.org/10.1002/for.3980040103

Makridakis, S., & Hibon, M. (2000). The M3-Competition: Results, conclusions and implications. International Journal of Forecasting, 16(4), 451-476. https://doi.org/10.1016/S0169-2070(00)00057-1