Logistics and Sales: Trends and Moving Averages

There are two ways to do planning:  you can use some kind of analytical software or take an educated guess, based upon your experience.  If you saw the Brad Pitt movie ”Moneyball” then you know that making a projection based upon one’s experience does not always work.  In the movie, Brad Pitt has come to Oakland to coach the A’s baseball team.  He hires a recent Harvard graduate who has some ideas about how one can use statistics to gain an advantage on the field.  This logical approach to baseball does not sit well with the old hand, tobacco-chewing, foul-mouthed, seasoned baseball scout.  His job is to scout the minor leagues and universities looking for new talent.  The scout says of one fellow, ”He can knock the ball out of the park.”  The newcomer from Harvard asks the more important question, ”Yes, but does he get on base?”  For those in UK, who might not know much about baseball, getting on base is what matters.  The coach listens to the statistician and not the scout.  The A’s turn around their dismal record, come in 2nd in the national tournament, and the old-time scout is shown the door.  (Note:  this blog writer does not care much about baseball either, a sport where 25 seconds of excitement are follow by 4 hours of boredom spent watching grown men spit and scratch their groin.)

This same thinking can be applied to business.  Analytics software is supposed to be the oracle (meaning prophet, not the database with a capital “O”) that looks at one’s data and tries to pick out some pattern to make a projection about the future.  These are commonly called forecast and trends.  But the software is only of any use, if you understand what you are looking at.  A misinterpretation of the data is like listening to the foul-mouthed baseball scout.

Here we provide a brief description of what analytics means.

There are several ways to spot a trend.  One is to use a moving average.  The other is to do what is called regression analysis.   You can do both in Microsoft Excel.

Suppose you want to know if your business is growing over time or a recent uptick in sales is just a fluke.  Your sales appear to be going up in a linear fashion.  But can you call this a trend or just a run of good luck?  Let’s see how we can project future sales given past ones.

Take a look at the sales data in the chart below.  Here have sales by week and average weekly sales by month.  We have taken the first four months of the year. The idea of a moving average is you can move the average along the calendar.  For example, it might be more relevant to your business to look at the past three months rather than the whole year.  So you maintain the data in a spreadsheet, but them you adjust the formula to include, say, January to March one measurement and then February to April for the next, thus keeping a three month window or three month moving average.

 

A B C D
1 week January February March
2 1 100 75 66
3 2 200 230 214
4 3 100 124 124
5 4 150 100 175
6 average 138 132 145

sales by month

F G H
2 January 138 1
3 February 132 2
4 March 145 3
5 April (forecast) 136 4

The average shown on row 6 of the spreadsheet is the weekly average sales for each month.  The data in column G is the same average shown up and down instead of horizontally.   (Note: We have plotted average weekly sales by month in a pivot table and shown it in the graphic above to make the data easier to see.)

Now we want to forecast sales for April.  We can use the Excel statistic function =trend to calculate this.  We give it two arrays:  (1) the month expressed as a number (H2:H4) and (2) average weekly sales during the month (G2:G4).

In the cell G5 we enter the function =TREND(G2:G4,H2:H4). This says take the month in column H and the sales in column G and then project the next month. If sales are increasing in linear fashion the you would expect the value for April to be higher than March. It is not. But this is too little data to make a positive declaration about the growth in sales. You would have to repeat this exercise over time to see if the sales are trending upwards.  Hence the idea of a moving average.

This is not the best example of how to apply regression analysis to a business problem. The whole point of regression analysis is to show how one value is related to another: from the statistical point of view, the month number and sales figure have nothing to do with each other.  To understand what we mean, consider another example.  This one model that does determine the relation between two inputs. Consider advertising dollars spent versus customer inquires to judge the effectiveness of your advertising campaign. The basic technique is the same as with the sales projection, except you would want to use the function =linest to plot a line that shows the relationship between advertising dollars and customer inquires.  That could give you a formula you could use like: customer inquires = ((0.023)(dollars spent) + 0.003).  Then take this to your ad agency and complain about their lack of good ideas.

One problem with our model is it does not take into consideration seasonal variation. It does sort of, in that the point of regression is to make an estimate given some series of values over time. By definition, this is an average that would toss out any large variation from the average, e.g., seasonal variation. Large variations can affect the average and, thus, the projection. So there is a need to build into our model seasonal variation, especially for a business that is seasonal, such as manufacturing sporting goods. We will discuss how to do do that in another blog post.