The Perfect Last Year

Hype This!

The first thing people ask after reporting last week’s sales is what we did last year. Last year can provide some perspective, but how useful is one data point? Just as this year’s sale number contains all the peculiarities and randomness of a selling season, so would last year’s. The result is a very noisy data set. Comparing to a budget or projection could provide a better gauge of the business, but those numbers, besides having been created months ago, are tainted with forecasting biases, planning assumptions, and company expectations.

Sales were...noisy

Sales were…noisy

What we need is a benchmark, based only on actual data, minus all the noise (random variations, holidays, and promotions) and smoothed to a show its true trend – a perfect last year. There are many data smoothing techniques that try to do this, but simple moving average can work well. The chart below shows the TY/LY sales as 6 week moving averages.

The next question is how significant is the variance. A variance of down 15% on a highly erratic sales pattern would be less of a concern than being down 5% on a stable one. To answer this question, we can borrow a technique from stock trading. Bollinger bands help define a high and low by setting an upper and lower band based on the standard deviation of a set number of periods. In this example, the bounds are +/- half the rolling 6 week standard deviation.

Smooth, not pointy at all

Smooth, not pointy at all

These three lines can provide better context for sales data and help spot trends. In this example, according to the banded chart, a turning point seems to occur in week 19. After this week, sales never rose above LY’s rolling average. Yet looking at the weekly data, week 19 would have been a minor celebration (+2.3% to LY, +3.5% YTD). For the following weeks (20-25), the two charts tell very different stories. The weekly data becomes highly erratic (- 18%, +3%, +6%,-21%, +23%), with the YTD variance still at 2.1%. However, the moving average shows a worsening trend, a consistently down to last year, usually close to the lower bound.

By week 32, the YTD variance has flipped from +3.5% to -0.1%, though some weeks still show positive gains. The rolling average would have not only have identified this trend by week 25, but, based on the fact that the sales are at the low end of the band, hinted that things are pretty bad. Sure enough, the last 10 weeks were abysmal, with weeks down 20-ish% to LY. The store ended the year down 6% to LY.

The smoothed year, a year without any random events, or a perfect year could provide a better gauge for tracking your sales. Enough to cancel or reorder? Maybe not, but enough lead time to update your LinkedIn profile.

6 Comments

Add a Comment

Your email address will not be published. Required fields are marked *