Lies, damn lies, and statistics!

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

I am a big fan of statistics.  They have helped me escape from many dangerous missions.  My last post referenced some statistical elements that many agents working their dangerous supply chains may not be familiar with.  I'd like to first review some of those, and then finish explaining how I built a metric for demand variation that is compressed down to one number.

I won't be at this location long enough to explain the details of everything I referenced in my last communique.  I will, however, cover the following elements:

• Slope
• Intercept
• Regression

Slope is normally described by the ratio of the "rise" divided by the "run" between two points on a line.  This is easy to calculate if we have a straight line, but we know our demand signals are not linear (See Figure 1 in my last entry).  This means we need to generate the linear line that passes "through" our volatile demand signal.

Excel can accomplish this for you automatically, but what if we'd like to view the data that is generated, and actually perform other formulas against it?

Last time I mentioned calculating "cross products":

(X-Avg X)(Y-Avg Y)

(X-Avg X)^2

X represents our time periods, and Y represents our demand in dollars.  Figure 1 displays our sample data and the cross products are on the right in Figure 2.

Figure 1

Figure 2

With the data in Figure 2, we can easily calculate the Slope by dividing the SUM of the dollars in Figure 2 by the sum of the squared values on the right of Figure 2.

Once we know the Slope, calculating the Intercept is also easy.  In this case we need the Y-Intercept, which is the point where our linear line would cross the Y Axis.

The Intercept can be calculated by first calculating the average of column X (Figure 1).  Next calculate the average of column Y (Figure 1) and multiply it by the Slope value we calculated above.  So if b is our Slope, then the equation looks like this:

AVG(X)-b*AVG(Y)

With the Slope and Intercept calculated, we can actually formulate the data necessary to chart our Regression line right through our volatile demand signal.

Hang on for a second...

Their belts are blacker than the
inside of a coffin at midnight

Figure 3

OK, I am back.  I thought I heard the footsteps of ninjas and would have to change location.  Must have been my imagination.

Where was I?  Oh yes, the Regression line.  If the Intercept is a and the Slope is b, then we can use the following formula to calculate our data points for plotting:

Y = a + b*X

Using this formula and the data generated in Figures 1 & 2, we can generate the data in Figure 3.

You can test the quality of your formula by simply picking two consecutive values, and if the difference between them is equal to the Slope, you should see a straight line when you chart the values.

Hopefully, if you overlay the Regression line with the existing demand data, your chart will look something like this:

Figure 4

You'll instantly notice I only generated the Regression, or as we're calling it, Trend line for the most recent 6 months.  This was another decision I made with the help of Agent GK.  There's no special secret to making this decision; it just seemed reasonable with respect to the data.

My next entry will explain the final steps of our journey:

• Using the data generated above to calculate the difference between each data point and the Regression line
• Calculating the Standard Deviation of those error values
• Calculating the Coefficient of Variation with the Standard Deviation value.

Time to move.  February metrics are ready to be reported.

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!

There must be more of me out there...

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


I've decided I must begin tracking my movements.  This mission is compromised, and I have to ensure what I have learned is passed on.


Our inventory is increasing at the same time we're struggling to improve our on-time arrival metrics.  None of the planning teams trust the demand signal they're receiving and they spend more time validating and buffering than executing.


Blame is pervasive, and due to the discombobulation of our factories and our contract manufacturers in our enterprise planning system, nobody has visibility of what the actual customer needs are.


We have a lot of data and very little information.  We have excellent people that are spending too much of their time on tasks that don't add value to the end customer.


I was selected to fix all of this.  I am a few months into the mission and now concerned that this mission really is impossible.


In any case, I will press on.  I don't have a choice.  The entire supply chain is relying on me.


Over the next few weeks, I will be sharing what I learn about the supply chain, as well as my top secret methods for improving it.  Progress will sometimes seem painfully slow; but that is often the road one must take to achieve high quality results.  Future missions will include:

• Measuring demand variability
• Creating a balanced supply chain scorecard
• Once we have demand visibility, what do we do with it
• Misunderstandings of safety stock

There will be more, but I must move from this location shortly.  For now, please share your comments and questions.  They will help ensure the success of my Supply Chain Mission: IMPOSSIBLE!