Posts tagged: sensitivity analysis

Why most project estimates suck…and how Monte Carlo simulations can make them better

missed deadlinesHave you ever been part of a project that was late and over budget? I’d be surprised if you haven’t. We humans are famously bad at estimating the future, and project planning is heavily dependent on our ability to estimate the future. Most of us are optimists and some of us are pessimists, but very, very few of us are realists by nature. Monte Carlo simulations can be useful in our estimation process to help us become more realistic about our estimates, and that realism can significantly improve our ability to deliver results more in line with expectations.

We generally recognize our inability to accurately estimate large projects in one chunk, so we break them up into smaller milestones that are easier to estimate. While the work breakdown process is good, the confidence it gives us in our estimates can lead to larger problems. We don’t ask ourselves often enough how accurate we think those estimates are before stringing them together to determine project due dates. If we did, the conversation might go like this:

“How accurate do you think these milestone estimates are?”

“Pretty accurate. We certainly spent a lot of time discussing them and comparing them to past projects.”

“OK. But if you had to put a number on it, would you say they are 100% accurate?”

“Well, let’s not get crazy. I can’t be sure they’re 100% accurate.”

“So put a number on it. How confident are you that they’re accurate?”

“I still feel pretty good about them. I’d say conservatively that I’m at least 90% sure.”

At this point, we’re about to discover some pretty major problems with our assumptions. We typically string together a number of these milestones, which are dependent on each other, and call them the critical path. The end of the critical path is the project due date.

But if we’re only 90% confident our estimates for each milestone are correct, the likelihood of missing our date is pretty high. Let’s say we have five major milestones in our critical path, and we’re 90% sure each is accurate. To determine the probability that all five will come in as expected, we have to multiply .90 x .90 x.90 x .90 x .90. Even with these high confidence rates, we’re now looking at about a 59% chance of hitting our due dates and a 41% chance of missing them. And that’s with only five milestones and really high (and probably unwarranted) confidence in our estimates. The numbers only get worse from here.

So we start missing deadlines and inevitably either pump more money into the effort or start cutting scope. Our original business case and ROI justification for the effort are now inaccurate because it’s going to cost more and produce less benefits. Sound familiar?

Monte Carlo simulations can help us get a better handle on the probabilities of actually delivering on our timeline and budget estimates. Just as I previously demonstrated using Monte Carlo simulations for sales forecasting, a simulation focused on project estimates can essentially become a “what if” model and sensitivity analysis on steroids for project planning. 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 the entire project.

Great. So now we know how likely we are to miss our timeline and budget. So what?

Once we have a more realistic view our our project timeline and budget, we can do far more effective planning. We can develop contingency planning with full knowledge of the likelihood of needing any particular contingency. Having a better sense of potential budget increases or scope decreases in advance of the project start date will help us make better decisions about starting the project to begin with.

We’ll also be able to better plan our needs from other groups in the corporation who might be involved with the final project but not directly involved in the project. For example, we might need to fit a new product launch campaign into an already packed marketing schedule. Will new site functionality require training for customer service? We’ll need to plan time to pull agents off the phones for their training. Setting expectations with these external groups will greatly enhance at least the internally perceived success of our effort. And that certainly counts for something.

Why go through all this complication? Let’s just take all the estimates we get from the team and double them. That should help get ensure we stay within the timeline.

The “double the estimates” approach is one I’ve seen used before. While it does help create timelines that won’t be exceeded, overestimation can also cause problems. Any coordination with external teams will still be a problem if we end up needing them before we originally planned. And over-allocating time, resources and budget can drive up opportunity costs and limit our ability to produce meaningful results over time.

Monte Carlo to the rescue

I created a free, sample Monte Carlo simulation you can download for use in project planning. It illustrates on a small scale some of the possibilities that can occur with even a minor project. We see that even a five milestone effort with 85% confidence in the estimate of each milestone is expected to be more that 20% overdue. But we can also get a sense of the probabilities of various timelines and use it to refine overall estimates.

By understanding the probability of various delivery dates and project budgets, we can better plan scope, business models and contingency plans. We can better coordinate with other teams who will play a part in the ultimate success of the project once it’s complete. In short, we can become realists and, as a result, deliver much better business results.

What do you think? Would this sort of tool help in your planning? What other methods have you used to set better expectations and plan more accurately?

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?

Retail: Shaken Not Stirred by Kevin Ertell


Home | About