I created an Out of Stock Alerting Process with Excel. Here’s What Happened.

I experienced first-hand the limitations of automating out of stock alerts using Excel. Here’s a real-life example of the out of stock process that I created.

Years ago, I read Eli Goldratt’s “Philosopher’s Corner…Somewhere in an office…Part 4” about a replenishment technique whereby you never stockout a customer. Basically, he described a scenario whereby you replenish a warehouse every week with what was shipped. The starting inventory value, which he called the buffer, should be enough for 4 weeks of demand. “If you’re not sure, add one or two. Then order what was sold each week. You never have 4 weeks of stock again, it falls to about 2 weeks, but you almost never run out. If you do, add the oversold quantity to the next week’s order and forget it.”

This seemed simple in Eli’s example, yet I ran into difficulty in applying it in real life with multiple warehouses in different countries with various lead times and multiple planners using varying software systems for execution, planning, and control.

Creating an Out of Stock Alerting Process Using Excel

Q. What kind of model would prove this concept for automation?

A. Experimenting in Excel: Download daily shipment data, then determine the maximum demand during lead time. (See Table I for example listed), sum the data for the first lead time period in days and record that number, (=Sum (F2:F13)). Then copy/paste that formula to the next cell, giving you = Sum (F3:F14) for the next value. These summations placed beside the daily demand column in column B gives the maximum demand during lead time. Using the Excel function MAX (G13:G31) gives the maximum demand for the lead time period for 30 days (MAXddlt). Create an ALERT in the formulas to point to a value you do not wish to exceed using conditional formatting. This could be the previous record.

Table I. MAXddlt example with >>ALERT<<

Now you can create a “Pull/Replace” model to test the theory using daily shipment data from your system. Starting with the Maximum Demand Lead Time Period for your initial buffer, enter the daily demand and replenish each week with what was shipped. You’ll find you never stockout unless daily demand exceeded the values of the initial buffer.

But rejoice! When that happens, you broke a new record and since you discovered it at the lead-time fence, you can recover, assuming Operations can make that quantity in the lead time. No worries as any Aussie would say.

Stock out alert Experiment Using the Maximum Demand Lead Time Period

Ready for the experiment? This first test will capture demand when it exceeds the Maximum Demand Lead Time Period (MAXddlt).

Find a customer willing to participate (we found someone with my TOC hobby). TOC = Theory of Constraints.
For convenience, use multiple SKUs with only this customer willing to participate.
Download SKU demand data. The more shipment history the better.
Determine MAXddlt for the initial buffer and capture SKU shipments each day.
Compare successive MAXddlt each day.
Document the occurrence when MAXddlt exceeds the initial buffer.

Experiment Results:

One day, one of the SKUs exceeded its Maximum Demand Lead Time Period. Elated at the discovery, I rushed to the Demand Manager. We detected abnormal demand at the lead time fence. This gives us time to recover and prevent a stockout.

Taking it to the next level, the Demand Manager calls the customer with the news and asks, “When were you going to tell us?”.

Back to 1) above, our customer took a minute on the phone, looked at the screen for the SKU in questions and said, “Oh yeah, we landed the Mack Truck account for K-mart.”

So, the power of the computer using this Alert to the humans prevented a stockout. Inventories stayed at the optimum and no one was stocked out. (NOTE: This assumes Manufacturing delivers the excess.)

Now for the crash landing with reality.

We did this in Excel and could not scale it up for automation, so it became a lost art.

Automating Possible Inventory Stockout Alerts

What if this process could be automated and applied to every SKU, even with multiple customers?

Obviously, you would want to start with high volume/high variability SKUs and coordinate with Customer Service, Sales, and Manufacturing to be able to respond to the abnormal demand. Planners would see the alerts from the day before and have time to react. In addition, this method limits excess inventory of slow movers (Low volume/high variability). With multi-tiered networks, the material may be deployed quickly from higher level warehouses and strengthen the postponement concept.

And don’t forget, the ALERT could also be modified to show a MAXddlt at lower levels, at say 50% of the MAXddlt. Or given the lower value sustained for 1 or 2 two consecutive months or whatever endless combination that makes sense for your business.

In today’s world, automating your system to create alerts with Maximum Demand Lead Time Period shows yet another enhancement to customize the visibility of your demand management system. This can help increase the visibility of the variability and volume of your business.

Enjoyed this post? Subscribe or follow Arkieva on Linkedin, Twitter, and Facebook for blog updates