A Simulation of Forecasting and Reorder Logic for MARS-IW as Contrasted to More Simplistic Systems

Charles J. Bodenstab, May 1996

I developed an Excel program to gain some insights on how MARS-IW operates on a variety of sales history patterns and how it compares to some other techniques that attempt to achieve the same objectives. This Excel program simulates how MARS-IW would react to a specific sales history, and then displays the results on easy to follow graphs.

For comparison purposes, I had the program also create a forecast based on the moving average concept, as well as a reorder point based on using the more simplistic concepts of using a fixed percent of the forecast (e.g. 50%) to establish the safety stock.

Four different sales patterns were studied:

Exhibit A - 1 & 2 were for an item that had a mid range sales level

Exhibit B - 1 & 2 were for a high volume, fast moving item

Exhibit C - 1 & 2 were for an erratic, slow moving item

Exhibit D - 1 & 2 were for a rapid growth item

The graphs with a 1 after the letter display actual sales and the forecasts. The graph with a 2 after the letter display the reorder points for the two concepts, again with the actual sales.

This Excel program is a good simulation of the two forecasting systems since it used only the past data to create a forecast, and then displayed that result on the graph with the actual sales that developed.

The program is only an approximation, however, of simulating the expected inventory quantities, since it displays the reorder points and not the actual resulting inventory. (How does one simulate the actual movement of sales and product arrivals on a day-to-day basis that would happen in real life?) Nevertheless, it comes close to reality, since I used a two week order frequency, and a two week lead time, for a total of one month. The inventory exposure therefore was about one month, which is consistent with the monthly sales data. Accordingly, we should expect the reorder points to have sufficient product to meet the months demand.

Exponential Average Vs. A Moving Average

Let's get one point out of the way. The two techniques of forecasting display very similar characteristics for all four types of demand patterns. There has been a tendency to think that exponential forecasting was the single differentiator between MARS, and the more simplistic moving average systems. This is not true as we shall see later. Both exponential averaging, and a moving average, will tend to do a nice job of forecasting. They both smooth out the erratic highs and lows, and they both tend to respond to increasing or decreasing trends in the sales volume. It so happens, that the exponential average forecast was slightly better throughout the studies, but not to any great degree. (As measured by the mean average error.)

Exhibits A - 1, B - 1, C - 1, and D - 1 all display these results.

Exhibit A - 1

Exhibit B - 1

Exhibit C - 1

Exhibit D - 1

The key benefit of exponential forecasting is more in the ability to use it for other critical functions. The technique, for example, allows us to build an automatic filter system, to filter out the truly wild points. Additionally, it is the foundation to build a much more effective seasonally forecasting system vs. the “use what you sold last year” approach of the simplistic systems.

The fact that there is not much difference between the efficiency of the two forecasting systems applies to some of the even more sophisticated techniques, (such as those that use an entire array of forecasts, and then select the one that supposedly has been doing the best for that item.)

Let's now discuss each of the graphs that display the reorder points.

EXHIBIT A - 2

Exhibit A - 2, shows sales history for an item that exhibits a medium demand level. Note that both systems are tending to bring in sufficient stock to cover just about all the months of demand except for the occasional peaks. Both systems would most likely have had a stock-out or two in each of the three spiked periods, which is consistent with giving a 97% fill rate.

Therefore, for a medium level demand item, there is no apparent advantage to either system. Actually, this is not too surprising, since even the simplistic reorder rule has a certain rational, and should work for a certain class of item.

Exhibit A - 2

EXHIBIT B - 2

The sales data for Exhibit B was deliberately picked to represent a highly popular product with strong demand.

Note again as I mentioned earlier, both the lead time and order frequency for all the examples was chosen as two weeks, thereby creating an "exposure period" of about one month. Consequently, any reorder targets in excess of a months demand are squandered.

A quick glance at the graph illustrates that the simplistic system of a fixed percent of the forecast as the safety stock brings in a considerable amount of excess inventory. MARS, in contrast, brings in just sufficient inventory to cover all the demand except for an occasional spike. MARS would be giving about a 99% fill rate in this example for about 30% less inventory.

The reason for this radical difference is due to the fact that a fast mover will generally have a lower level of variability, relative to its average demand. Therefore a fixed percent of the high forecast brings in excess stock. MARS, in contrast, customizes each safety stock to the individual characteristics of the item, and therefore recognizes that this fast mover requires less safety stock.

Exhibit B - 2

EXHIBIT C - 2

The sales history for this example is just the opposite of the prior one. It has very low demand with the characteristic high variability of unpopular items.

In this case the results are also just the opposite from the prior example. The simplistic reorder point technique results in excessively low inventory. This is due again to the reliance on the use of a fixed percent of the forecast. Since the forecast is low, the safety stock is very low. It therefore fails to account for the inherent unreliability of any forecast for low volume items.

While it is difficult to anticipant exactly what the resulting delivery performance would have been, it appears that about six or seven out of fifteen demands would have been missed (assuming no override) for an approximate fill rate of about 60%. MARS, however, is calling for a stocking level of about two to three items. This means that we would have satisfied about all but two or three of the peak demands.

Exhibit C - 2

EXHIBIT D - 2

The sales history in Exhibit D is the most interesting of all, and displays a situation that would be of concern to just about any distributor. This sales history is for an item that is moving at a fairly low rate, and then suddenly becomes popular, after which it stabilizes at a new higher level.

It is worth noting on the forecasts in Exhibit D - 1, that both forecasting systems seriously lag the actual sales, which is actually quite reasonable. Neither system can, or should, anticipate that the demand will continue to rise.

Exhibit D-2 illustrates the real key issue, however. For a product whose demand is suddenly rising, the fixed percent safety stock method simply does not react sufficiently to protect for incoming sales. The safety stock is nothing more than a percent of the forecast and therefore lags the rise in demand as does the forecast itself.

Note on this plot that during the period of April 1995 through September of 1995 the simplistic technique is seriously lagging, to the point that chronic out of stock conditions would prevail. This is a particularly deadly situation for the distributor, because not only will lost sales result, but the distributor will lose credibility in the marketplace as a reliable supplier. Ironically, once the sales level off, the system belatedly brings in excess stock when it is not longer needed, and then keeps it at an excess level thereafter.

Note that during the same period of surging demand MARS starts to "fatten up" the reorder amounts, and is in an excellent position to meet the new higher sales levels. It then stabilizes as the demand levels off.

This "fattening up" process occurs due to the following: In looking forward to April - 95, the system recognizes that the forecast error of the prior month had increased. Consequently, the safety stock was increased slightly to recognize that this item "had become less stable and easy to forecast". The reorder target therefore increased over the past pattern. This same process was then repeated again in May - 95 to an even greater degree when the April forecast was proven to be even further off.

In fact, the MARS reorder target overshoots the turning point, bringing in excess stock for a few months before it settles down to the new sales levels.

Exhibit D - 2

Summary

The various simulations provide considerable assurance that the internal logic of MARS does trigger the levels of inventory that are needed to hit the fill rates called for. Additionally, the system is capable of dealing with a wide variety of demand levels and patterns without human intervention.