Taming the Bull%#!&

Agent Log Entry 2012.02.27 : Special Agent Ethan Hunt: Security Clearance Level Theta 

The mission: identify and reduce demand variability upstream in the supply chain.  It became critical that we address this for a few reasons:

• Increasing inventory
• Unstable capacity planning
• Mistrust between nodes in the supply chain

Most of the field agents reading this are well versed in what is known as the "Bullwhip Effect".  If not, this is a good reference.  While people in my agency routinely refer to this phenomenon, none have yet tried to capture it, and distill it down to one single number as a metric to be tracked and improved.  This was the first step in my mission.

Figure 1

It is easy to report the previous 12 months of demand or actual orders, and then view it on a chart (see Figure 1).  The variability is painfully obvious in this view.  But what if we'd like to track a single monthly value for multiple process centers upstream in the supply chain?

To take the data I'd reported and charted, and begin statistical analysis, I knew I had to bring in a "big gun": Special Agent GK.  You can read the results of GK's past missions here.  We'll be referencing this information in a future mission brief about Safety Stock.

Gk came through just as I thought he would.  He suggested a selection of single value numbers for measuring the variation of an aggregate monthly demand signal:

• CV - Coefficient of Variation
• Standard Deviation as a percent of the average demand
• MAD % AVG
• Mean Absolute Deviation as a percent of the average demand
• AMR % AVG
• Average Moving Range as a percent of the average demand

He also mentioned the possibility of measuring the slope of the regression line to generate a single number to indicate trend.  Ultimately, we decided to separate the two measurements so that our variation value is not influenced by the slope of the regression.  You're probably wondering how one measures CV and Trend in the same data set, independently.  These are the steps:

1. Determine the number of periods (X) to consider, and collect demand data for them (Y)
2. Calculate "cross products", (X - Avg X)(Y-Avg Y) and (X-Avg X)^2
3. Calculate the Slope
4. Calculate the Intercept
5. Calculate the Regression line using the Slope and Intercept

My next entry will cover performing the above steps in Excel, and explain how we go from the chart on the left to the table of values and icons on the right!