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!