Imagine you are playing golf. You start in a 450 yard hole. You walk up to the tee, and pull out your Super Duper Ultra Big Matilda Driver and hit the ball. The ball sales 300 yards straight down the fairway. You probably drive up to your ball and again you polite your Super Duper Ultra Big Matilda Driver and hit the ball. The ball sales beyond the hole and into a window in the clubhouse. Whoops.
But you would never make this mistake. Even someone who is only seen a few minutes of golf knows you would use the Super Duper Ultra Big Matilda Driver for every shot. Golfers have multiple clubs. Clubs designed specifically for long-range, mid-range, and short-range situations. A golfer chooses a club that is matched the distance the ball must travel to the hole.
But when it is time to forecast our business performance, we reach for the Super Duper Ultra Big Matilda Driver. Not exactly. But, we do use the default trend line. We assume that the programmers who coded the graphing tool wouldn’t put a tool in their product that could cause us trouble. And if they did, enough people would complain and get it fixed. But what if the problem is not error in the calculations? What if something else makes the trend line a problem for us to use?
What’s Wrong with the Default Trend Line?
The problem with the default trend line starts with the way it is calculated. The basic straight trend line that everyone uses is calculated using something called Linear Regression, usually using the least squares method. I’m not a statistician. And I don’t play one on TV. And neither are you. So instead of trying to explain the method let’s look at the problem a real statistician found with the results.
Statistician Francis Anscombe saw a problem when statisticians depended on statistical measures along to compare different sets of numbers. When he graphed the results he saw different information than the statistics would lead him to believe. In 1973 Anscombe published a paper to change the way statisticians looked as sets of numbers.
Anscombe created four sets of X and Y data. Each set had the same basic statistics. The mean or average was the same for the X values of all four sets. The mean was also the same for All of the Y values. The correlation coefficient between X and Y values was effectively the same across all four sets. Graphs of the four sets showed four dramatically different pictures(Exhibit 1.) But when the least-squares method was applied to all four data sets the same line was generated.
The data sets and graphs are known as Anscombes’s Quartet. Anscombe’s analysis and graphs show the problems statisticians can run into using just the statistical analysis of the numbers. The numbers have to be graphed to give any real insight. Statisticians use regression lines to show tendencies in the relationships between the X and Y variables. We use regression to project performance into the future.
How Can Anscombe’s Quartet Help Us With Projections?
Instead of dealing with Anscombe’s Quartet as a generic example, let’s make it more real. Image you are working with your Sales numbers and trying to project to the end of the year. You take the numbers you have and generate a line graph. You then enhance the picture by adding a trend line. Now let’s see what your graph would look like in each of Anscombe’s Quartet graphs.
Look at graph I. The points are arrayed on either side of the regression line. This is the kind of graph you might get when your Monthly Sales are growing at a consistent rate. The trend line approximates the direction of the Sales numbers. You probably would feel comfortable extending this line out to the end of the year as a projection. This is the best case of the four.
When you look at graph II you see a different situation. The points move up as the value of X increases until about 11 then they decrease. Your Sales had been increasing but are now dropping off at a consistent rate. The trend line indicates that the values should go up in the future. But the graph says otherwise, the most recent Sales numbers have been dropping. As a result, the trend line wouldn’t be useful for estimating future Sales.
In graph III, the points are in a line except for the second from last. That point is significantly above the preceding and following points. Your Sales are growing at a regular 5% per month. Then in the second to last month Sales jump by 30%. Only to drop back into the range of 5% monthly growth the next month. You probably had a one time large deal that created the positive spike in Sales.
But a one time deal is not going to have any long term effect on your Sales growth. When you look at the graph, you can see the points lining up. But the trend line assumes the one time spike is part of the pattern. The result is the trend line over estimates your Sales growth into the future. You should not be too comfortable with this line as a projection.
Finally graph IV shows a series of points all with the same X value and one point at another X value. The picture is one in which the monthly Sales numbers are all clumped into one month plus one normal month. If your Sales graph looked like IV, you have errors in your numbers. Maybe you have included multiple products for a single period of time. Or multiple markets. Or you have a lot of missing numbers. In any case a trend line generated from these numbers would be absolutely meaningless as a projection tool.
Anscombe’s Quartet is useful to those of us who are not statisticians. It is an object lesson that you cannot just blindly make useful projections based on the default trend line. If Anscombe had been a business person he would have seen something else. He would have looked at graph II and realized that extending the trend line would overstate the likely performance. Therefore the trend line would not be a good forecasting tool.
So what do you do? Skip forecasting? Hire a statistician? You could do either. Or you could look at the problem from a different perspective.
Are All Numbers The Same?
The Linear Regression trend line assumes that the value you have for every period is the same. The numbers from five years ago count the same as the numbers from the most recent months. Does this make sense? No! More recent numbers reflect the current situation better than old numbers.
Which Number Are Too Old?
Intuitively we know that recent numbers more accurately represent current conditions of your business than the numbers from five years ago. Why? Has some aspect of your business changed significantly?
- The Market you serve
- Your Business Model
- The Channels you use to sell your products
- How you Support your products
- Your Product Line
- The Cost structure
- Your Price Structure
A significant change in any aspect of your business makes the numbers from that point forward different from the numbers from earlier years. The numbers from before that period are old. So unless you are satisfied with bad estimates you need to create a more recent trend line.
Which Numbers Should I Select as Recent?
What is recent depends on all the changes that happen and the volatility in your business. I’m sure somewhere there’s a formula which will calculate a significant period of time on the basis of statistics.
But you are not building trend lines for pure mathematical purposes. You want a trend line to determine whether your current performance will lead you to achieve your annual goals and targets. What you need is a simple way to determine how much is the right amount of time use for your trend line.
Selecting the Right Amount of Time
When someone starts playing golf they don’t confuse things by trying to use every single club in their bag. Instead they generally pick a moderate club. When I started I used a five iron. And started learning how to swing the club to drive the ball down the fairway.
Using one moderate club allows the person to play and learn to enjoy the game. We can select a moderate period of time for a trend line.
In my experience one year is the five iron of significant periods.One year is the time frame we use assess whether or not we will achieve current priority targets and goals. One year includes all of the seasonal cycles your business has. A one year period trend line works whether the year is broken into 365 days, 52 weeks, or 12 months. But even our beginning golfer needs another club.
One Club Is Not Enough
Our golfer needs a club designed for short range shots. A putter. The putter helps the golfer make the short range shots into the cup. We need a trend line that will help us see the direction of short term performance.
How short is short? In my experience three months is a good time range for a short trend. The short trend shows the direction of the most recent three months of performance.
How Do You Use the Two Trend Lines?
Let me start with a question. How comfortable are you with a single trend line as a forecast of the future? In other words, in January are you comfortable you where your performance will be at the end of December? With a forecast based on the last 12 months. Or the last 3 months? Not likely.
Using both a moderate and a short range trend line together gives you a set of clubs to assess your performance measures. Combine the one year and three month trend lines on a single graph and you have a range of performance. One limit anchored by the 12 month trend line. And the other by the 3 month trend line. The volatility and direction of your performance will determine which is the upper limit and which the lower. (See Exhibit 2)
Having a range of performance gives you more information on potential future performance than a single trend line. The added information should improve your comfort level about your estimates of future performance numbers.
Take a Lesson from Golf
Don’t try to use your Super Duper Ultra Big Matilda long term trend line to estimate your future performance. Next time you need to project your future performance, apply the lesson of Anscombe’s Quartet. Graph your performance numbers. Enhance the graph with trend lines of one year and three months. Use the range between these two lines as your estimate of your future performance. Apply this simple adjustment and you will quickly improve your audience’s comfort with your projections.