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?

21 Comments

  • By Lee, January 12, 2010 @ 2:07 pm

    Awesome post! I’ve been frustrated with the sales process for years and this looks like a great way to avoid some of the problems with it. I’ve heard about Monte Carlo simulations before, but I’ve never actually seen one. I tried your file out and think it is very cool. I am sending it to our analysts today. Thanks!

  • By John, January 12, 2010 @ 3:32 pm

    This is great food for thought. I have also been frustrated by the lack of precision in the sales forecasting process – and more importantly, with the lack of acknowledgment of how imprecise it is. I have tinkered with this file for a little while now and I really like the concept of probabilities of different sales outcomes – and the thought of using these probabilities to make business decisions in advance rather than just “hoping” the forecast will be close to the actual.

  • By Kevin Ertell, January 12, 2010 @ 8:51 pm

    Thanks for your comments, Lee and John. I’m glad you found my example file useful, and I’d love to hear how it works for you in your environments. John, you make some interesting points about the lack of acknowledgment of the imprecision of the traditional forecasts methods. I hope we can get some people to rethink the effectiveness of the current way of doing business. Two of the books I mentioned in the article, The Flaw of Averages and Why Can’t You Just Give Me a Number?, are both written for managers and hope more and more people will read them. Thanks again for your comments.

  • By Andy (Ansar) Syed, January 13, 2010 @ 4:02 am

    This is really great post. I wonder why huge investments are made in SCM packages to forecast sales and automate various activities like order creation and replenishments etc. of supply chain. I understand the significance of the statistics, as I was involved in six-sigma projects that converts business problem into statistical problem. Then the statistical solution is converted into business solution.

    I have one question about the model. Everything remain same, the output of the model changes depending upon the number of simulations. Is there a threshold on the minimum number of simulations that can act as the efficiency of the model?

  • By Mike Giberson, January 13, 2010 @ 8:55 am

    I also think this is a great post. Monte Carlo analysis can be very useful – the “sensitivity analysis on steroids” remark is right on target as to why – and can be pretty simply incorporated into an existing financial model.

    Andy, there is no general rule, so rather for any model the analyst will have to judge when the results stabilize and additional repetitions would not change the outcomes.

  • By Adam Marshall, January 13, 2010 @ 10:10 am

    This article has just caused a rather lively debate..

    There’s a few points which are bothering me:

    1, Given that you have access to the full range of historical data, how come you limit the modelling to just 3 different scenarios? In reality, you would have a distn. of unique results rather than 300,000 appearing 7 times out of 10. It seems to me that you lose something by simplifying the engine bin values in this way.

    2, Following that – why give 275 and 325 equal weighting, when in the ‘Traditional Method’ sheet, your past results show the lower figure appearing twice as often? I’d say 20/10 rather than 15/15 there..

    3, Your method assumes no correlation between the three input variables. While this may sometimes be the case, a lot of metrics will be correlated. Why not run a Pearson or something across each variable combination, and then skew the RNG to those values? This would reduce the occurrence of your model using particularly unlikely scenarios too often…

    4, And finally.. I had to laugh out loud when I spotted the RAND() calculations are still in there.. since they change every time you refresh the sheet, this means that the final answer is slightly different every time you open the workbook! :p

    Regardless, this seems like the right kind of thinking to me, if you’re looking for insight rather than the ‘give me a number’ approach..

  • By Mark Evans, January 13, 2010 @ 11:25 am

    Hey, Kevin – this is truly thought-provoking stuff and something I’m really interested in I’ve had a couple of those books on my virtual bookshelf and this is making me want to go grab them right now!

    I was actually working on a Monte Carlo simulation approach to optimizing replenishment policy at Borders (what is the profit-optimal reorder point, reorder cycle, delivery method for a title with probability of x sales in a year?)

    I think it is very powerful and has great potential in powering business decisions and, in my experience, revealing counter-intuitive realities.

    Some of the limitations might be related to complexity of a probability-based forecast versus a single nominal target relative to the intended audience. Sometimes complexity can undermine acceptance and confidence in a target.

    Regardless, this is something all people who deal with business forecasts and corresponding business decisions should learn about.

  • By Jeff Haden, January 13, 2010 @ 3:59 pm

    Hi Kevin – really good post. Guess I’ll check out Flaw of Averages after all. Oh – was nice to meet you a month ago or so…if you don’t remember me by name, just think jockeys and locker rooms and innovative toilets… and that’s me.

  • By Kevin Ertell, January 13, 2010 @ 6:33 pm

    Andy: I agree with Mike’s point that there is no hard and fast rule for how many iterations to run and testing different iterations until you reach stability is a good idea. In general, 1,000 or so will be pretty good and 10,000 can be really safe.

    Mike: Thanks a lot for weighing in. It sounds like you’ve used Monte Carlo in practice. I’d love to know where you’ve used it and how it went over with your colleagues.

    Adam: Thanks a lot for your comments and detailed feedback. I really appreciate it. In response to each of your points:

    1. I agree with your point and think in a practical application we would go into more specificity with the Engine Bins. I was intentionally trying to keep it simple in this example in order to make the model more understandable.

    2. Good point. In practical application, we might want to draw our probabilities from both the previous year and the previous several weeks. In this case, to be honest, I just plugged some numbers in. 🙂 You’re right in your analysis, though.

    3. Excellent point about assumed lack of correlation. Again, I was trying to keep it simple for illustration purposes, but I completely agree that many variables are tied together and we should be conscious of such correlations when estimating probabilities.

    4. Oops. Thanks for calling this one out. I left the RAND() calculations in on purpose so anyone who downloaded the model could see how it worked and plug in numbers of their own. However, I meant to include a note in the instructions to draw attention to the fact that the numbers would change slightly with each refresh. I just added that new caveat into the instructions, so thanks again for pointing that out and sorry to anyone who might have been confused about the changing numbers.

    Thanks again for your comments and for helping me correct my mistakes. 🙂

    Mark: Thanks for your comments, and definitely do read those books. You’ll love them! I’d love to see what you did with Monte Carlo simulations on replenishment models. That’s definitely a good use.

    I totally agree with you that complexity can sometime undermine acceptance. I’m hoping to do my part to change the world with posts like this. 🙂 I think just because the metrics are complex doesn’t mean that can’t be expressed simply. Even if we only operated in terms shown in the pie charts (i.e. there’s a 73% chance we’re going to have a negative comp) that would be better than stating a very specific number we absolutely know we won’t hit on the dot.

    Jeff: Thanks for your comment. Definitely do check out Flaw of Averages. You’ll like it. And yes, I remember you and enjoyed meeting you as well. Although, I’m not sure I needed to be reminded of that disgusting story. 🙂

  • By Adam Marshall, January 15, 2010 @ 4:24 am

    Hi Kevin, Simple for the purpose of demonstration is always good – I’m glad that it turns out I understand it after all!

  • By Luis Ruiz, April 8, 2011 @ 1:17 am

    Hi Kevin:
    I have analyzed your Excel simulation and I have a doubt related with the probability values for each variable of your simulation. How do you know the probability for each variable value? How can you predict that the probability for traffic volume of 300K is 70%? Is there any method?

  • By Kevin Ertell, April 8, 2011 @ 9:22 am

    Thanks for your question, Luis. The probability of the input variables to the model come from historical data for each of those variables. Basically, it’s the percentage of their occurrence in the past. In the simple example I used, they all occurred in exact numbers previously. We could see that 70% of the time, traffic was 300,000, so we might might then assume there is a 70% probability that future traffic will be around 300,000. Is it perfect? No. But it’s much more accurate than using a straight average of past data and assuming the future data will also be the average (and this is particularly true if there is a lot of variance in the historical data). The Monte Carlo simulation then helps us take those rough probabilities more accurate by feeding all the variables in a model that produces a statistically valid data set. From that set, we can generate pretty accurate ranges and probabilities for the future. Does that make sense?

  • By Luis Ruiz, April 8, 2011 @ 10:59 pm

    Of course it makes sense. Thanks for your soon attention. It’s more clear for me.

  • By Michael, July 20, 2011 @ 8:42 am

    Kevin, I comprehend the probability on the ‘base business’ tab, however, can you explain how to use the rate %?

  • By josephine bucio, July 25, 2011 @ 9:49 pm

    hi.what are other application of monte carlo in real wold like in business.

  • By josephine bucio, July 25, 2011 @ 10:19 pm

    me and my friends are working in this topic about monte carlo for our thesis.but we cant find a definite topic that we wish to do.any idea about application of monte carlo?aside from your example?

  • By Kevin Ertell, July 26, 2011 @ 9:33 am

    Thanks for your questions, Michael and Josephine.

    Michael: the Rate % is just an estimated conversion rate. Does that make sense?

    Josephine: Monte Carlo simulations are useful any time you’re dealing with future uncertainties. In addition to sales forecasting, you can also use them for ROI calculations, project timelines, stock selections and other investments. I wrote another post a while back called “Why most project estimates suck…and how Monte Carlo simulations can make them better” (http://www.retailshakennotstirred.com/retail-shaken-not-stirred/2010/08/why-most-project-estimates-suck-and-how-monte-carlo-simulations-can-make-them-better.html) that address project timelines. I hope that helps.

  • By josephine bucio, August 20, 2011 @ 10:04 pm

    thank you Mr. Kevin.

  • By Kate, August 23, 2011 @ 9:47 am

    Hi Mr. Kevin. I just want to ask if there were already well-known companies/establishments that used the Monte Carlo Simulation Method in Sales Forecasting and found it more effective. We’re currently working on this topic and we want to know how effective and efficient this method will be in reality. Your reply will be much appreciated. Thank you Sir.

  • By Art Retti, October 28, 2014 @ 8:29 am

    Yes sir! Too many managers expect accurate forecasts. Don’t rely on your Quant or on your in-house statistician to get reliable forecasts. The first thing you need is a well polished crystal ball. In a dark room make a light shine on your new predictive sphere and see if you are dealing with a stationary or nonstationary process. Mixing fertilizers is a stationary process because it always yields the same results. Configuring portfolios is a nonstationary process. The best portfolio mix of today can turn into a fertilizer of sorts in a week or a month’s time. With all due respect, the Noble Jury who gave Prof. Markowitz his prize… Oh well, nobody is perfect.

    A stationary process is your relationship with your dog. A non stationary process is how you get on with your wife (husband). Stock market prices are a good example of nonstationary processes.

    If your scales fall off your eyes and you finally realize that you are facing a hopeless conundrum such as a nonstationary process, you may be tempted to fire your Math Phd forecaster and replace him with a fortune teller – or more frequently with your instinct and intuition.

    If you are lucky, this may work. Ome of us are just plain lucky…

    However, your best strategy should be different and slightly more scientific. Now that you’ve given up the illusion of accurate forecast, learn to live with a (very) powerful Monte Carlo simulation model which won’t give you a black or white answer about the future. But it will give you a range of possible events (figures) that you may have to face at the end of the year with a probability tag attached to them.

    You’ve given up your blind faith in accurate forecast and gained a vision of likely and unlikely gray areas. That’s a valuable bit of info because it will help you meditate on both the good and the bad events you see outlined on your new map. You’ll have to take time out to learn to meditate (if it’s something you are not familiar with) and take time out to do it regularly in the loneliness of your meditation chamber. Meditation may, of course, have collateral damage. Like discovering a foul smell emanating from some cookie monsters. Whichever way, you’ll thank the Lord that you discovered Monte Carlo on the way to Jerusalem.

    Anyway, good luck to you wherever you are and make good use of whatever Monte Carlo will bring you for Christmas.

Other Links to this Post

  1. Why most project estimates suck...and how Monte Carlo simulations can make them better | Retail: Shaken Not Stirred by Kevin Ertell — August 18, 2010 @ 9:45 am

RSS feed for comments on this post. TrackBack URI

Leave a comment

Retail: Shaken Not Stirred by Kevin Ertell


Home | About