Posts tagged: sales forecasts

Why most sales forecasts suck…and how Monte Carlo simulations can make them better

Sales forecasts don’t suck because they’re wrong.  They suck because they try to be too right. They create an impossible illusion of precision that ultimately does a disservice to managers who need accurate forecasts to assist with our planning. Even meteorologists — who are scientists with tons of historical data, incredibly high powered computers and highly sophisticated statistical models — can’t forecast with the precision we retailers attempt to forecast. And we don’t have nearly the data, the tools or the models meteorologists have.

Luckily, there’s a better way. Monte Carlo simulations run in Excel can transform our limited data sets into statistically valid probability models that give us a much more accurate view into the future. And I’ve created a model you can download and use for yourself.

There are literally millions of variables involved in our weekly sales, and we clearly can’t manage them all. We focus on the few significant variables we can affect as if they are 100% responsible for sales, but they’re not and they are also not 100% reliable.

Monte Carlo simulations can help us emulate real world combinations of variables, and they can give us reliable probabilities of the results of combinations.

But first, I think it’s helpful to provide some background on our current processes…

We love our numbers, but we often forget some of the intricacies about numbers and statistics that we learned along the way. Most of us grew up not believing a poll of 3,000 people could predict a presidential election. After all, the pollsters didn’t call us. How could the opinions of 3,000 people predict the opinions of 300 million people?

But then we took our first statistics classes. We learned all the intricacies of statistics. We learned about the importance of properly generated and significantly sized random samples. We learned about standard deviations and margins of errors and confidence intervals. And we believed.

As time passed, we moved on from our statistics classes and got into business. Eventually, we started to forget a lot about properly selected samples, standard deviations and such and we just remembered that you can believe the numbers.

But we can’t just believe any old number.

All those intricacies matter. Sample size matters a lot, for example. Basing forecasts, as we often do, on limited sets of data can lead to inaccurate forecasts.

Here’s a simplified explanation of how most retailers that I know develop sales forecasts:

  1. Start with base sales from last year for the the same time period you’re forecasting (separating out promotion driven sales)
  2. Apply the current sales trend (which is maybe determined by an average of the previous 10 week comps). This method may vary from retailer to retailer, but this is the general principle.
  3. Look at previous iterations of the promotions being planned for this time period. Determine the incremental revenue produced by those promotions (potentially through comparisons to control groups). Average of the incremental results of previous iterations of the promotion, and add that average to the amount determined in steps 1 and 2.
  4. Voilà! This is the sales forecast.

Of course, this number is impossibly precise and the analysts who generate it usually know that. However, those on the receiving end tend to assume it is absolutely accurate and the probability of hitting the forecast is close to 100% — a phenomenon I discussed previously when comparing sales forecasts to baby due dates.

As most of us know from experience, actually hitting the specific forecast almost never happens.

We need accuracy in our forecasts so that we can make good decisions, but unjustified precision is not accuracy. It would be far more accurate to forecast a range of sales with accompanying probabilities. And that’s where the Monte Carlo simulation comes in.

Monte Carlo simulations

Several excellent books I read in the past year (The Drunkard’s Walk, Fooled by Randomness, Flaw of Averages, and Why Can’t You Just Give Me a Number?) all promoted the wonders of Monte Carlo simulations (and Sam Savage of Flaw of Averages even has a cool Excel add-in). As I read about them, I couldn’t help but think they could solve some of the problems we retailers face with sales forecasts (and ROI calculations, too, but that’s a future post). So I finally decided to try to build one myself. I found an excellent free tutorial online and got started. The results are a file you can download and try for yourself.

A Monte Carlo simulation might be most easily explained as a “what if” model and sensitivity analysis on steroids. Basically, the model allows us to feed in a limited set of variables about which we have some general probability estimates and then, based on those inputs, generate a statistically valid set of data we can use to run probability calculations for a variety of possible scenarios.

It turns out to be a lot easier than it sounds, and this is all illustrated in the example file.

The results are really what matters. Rather than producing a single number, we get probabilities for different potential sales that we can use to more accurately plan our promotions and our operations. For example, we might see that our base business has about a 75% chance of being negative, so we might want to amp up our promotions for the week in order have a better chance of meeting our growth targets.  Similarly, rather than reflexively “anniversaring” promotions, we can easily model the incremental probabilities of different promotions to maximize both sales and profits over time.

The model allows for easily comparing and contrasting the probabilities of multiple possible options. We can use what are called probability weighted “expected values” to find our best options. Basically, rather than straight averages that can be misleading, expected values are averages that are weighted based on the probability of each potential result.

Of course, probabilities and ranges aren’t as comfortable to us as specific numbers, and using them really requires a shift in mindset. But accepting that the future is uncertain and planning based on the probabilities of potential results puts us in the best possible position to maximize those results. Understanding the range of possible results allows for better and smarter planning. Sometimes, the results will go against the probabilities, but consistently making decisions based on probabilities will ultimately earn the best results over time.

One of management’s biggest roles is to guide our businesses through uncertain futures. As managers and executives, we make the decisions that determine the directions of our companies. Let’s ensure we’re making our decisions based on the best and most accurate information — even if it’s not the simplest information.

What do you think? What issues have you seen with sales forecasts? Have you tried my example? How did it work for you?

How are retail sales forecasts like baby due dates?

Q. How are retail sales forecasts like baby due dates.

A. They both provide an improper illusion of precision and cause considerable consternation when they’re missed.

Our first child was born perfectly healthy almost two weeks past her due date, but every day past that less than precisely accurate due date was considerably more frustrating for my amazing and beautiful wife. While her misery was greater than many of us endure in retail sales results meetings, we nonetheless experience more misery than necessary due to improperly specific forecast numbers creating unrealistic expectations.

I believe there’s a way to continue to provide the planning value of a sales forecast (and baby due date) while reducing the consternation involved in the almost inevitable miss of the predictions generated today.

But first, let’s explore how sales forecasts are produced today.

In my experience, an analyst or team of analysts will pull a variety of data sources into a model used to generate their forecast. They’ll feed sales for the same time period over the last several years at least; they’ll look at the current year sales trend to try to factor in the current environment; they’ll take some guidance from merchant planning; and they’ll mix in planned promotions for the time period, which also includes looking at past performance of the same promotions. That description is probably oversimplified for most retailers, but the basic process is there.

Once all the data is in the mix, some degree of statistical analysis is run on the data and used to generate a forecast of sales for the coming time period — let’s say it’s a week. Here’s where the problems start. The sales forecast are specific numbers, maybe rounded to the nearest thousand. For example, the forecast for the week might be $38,478k. From that number, daily sales will be further parsed out by determining percentages of the week that each day represents, and each day’s actual sales will be measured against those forecast days.

And let the consternation begin because the forecast almost never matches up to actual sales.

The laws of statistics are incredibly powerful — sometimes so powerful that we forget all the intricacies involved. We forget about confidence intervals, margins of error, standard deviations, proper sampling techniques, etc. The reality is we can use statistical methodologies to pretty accurately predict the probability we’ll get a certain range of sales for a coming week. We can use various modeling techniques and different mixes of data to potentially increase the probability and decrease the range, but we’ll still have a probability and a range.

I propose we stop forecasting specific amounts and start forecasting the probability we’ll achieve sales in a particular range.

Instead of projecting an unreliably specific amount like $38,478k, we would instead forecast a 70% probability that sales would fall between $37,708k and $39,243k. Looking at our businesses in this manner better reflects the reality that literally millions of variables have an effect on our sales each day, and random outliers at any given time can cause significant swings in results over small periods of time.

Of course, that doesn’t mean we won’t still need sales targets to achieve our sales plans. But if we don’t acknowledge the inherent uncertainty of our forecasts, we won’t truly understand the size of the risks associated with achieving plan. And we need to understand the risks in order to develop the right contingency and mitigation tactics. The National Weather Service, which uses similar methods of forecasting, explains the reasons for their methods as follows:

“These are guidelines based on weather model output data along with local forecasting experience in order to give persons [an idea] as to what the statistical chance of rain is so that people can be prepared and take whatever action may be required. For example, if someone pouring concrete was at a critical point of a job, a 40% chance of rain may be enough to have that person change their plans or at least be alerted to such an event. No guarantees, but forecasts are getting better.”

Imagine how the Monday conversation would change when reviewing last week’s sales if we had the probability and range forecast suggested above and actual sales came in at $37,805k? Instead of focusing on how we missed a phantom forecast figure by 1.7%, we could quickly acknowledge that sales came in as predicted and then focus on what tactics we employed above and beyond what was fed into the model that generated the forecast. Did those tactics generate additional sales or not? How did those tactics affect or not affect existing tactics? Do we need to make strategic changes, or should we accept that our even though our strategy can be affected by millions of variables in the short term it’s still on track for the long term?

Expressing our forecasts in probabilities and ranges, whether we’re
talking about sales, baby due dates or the weather, helps us get a
better sense of the possibilities the future might hold and allows us
to plan with our eyes wide open. And maybe, just maybe, those last couple weeks of pregnancy will be slightly less frustrating (and, believe me, every little bit helps).

What do you think? Would forecasts with probabilities and ranges enhance sales discussions at your company? Do sales forecasts work differently at your company?



Retail: Shaken Not Stirred by Kevin Ertell


Home | About