Introduction
When we make a plan in supply chain, we like single numbers. After all, a plan needs to be executed. When placing a purchase order, we need an exact quantity to order. Production needs to know exactly how many parts to produce. We cannot use ranges or probabilities in execution. So it is understandable that planning tries to provide those same expected values.
Here is the problem. The world is uncertain and complex. All we know for sure is that there will be more uncertainty the further out we plan. And more uncertainty the greater the detail we plan. And as humans we intuitively understand this: If I ask you what are your plans for tomorrow, the answer will be a lot more detailed than if I ask you about your plans for the year.
So why in supply chain do we expect to plan with single numbers over days, weeks, months and even multiple years? Why do all forecasts and projections rest on a single value for any given product and period when the chance of the actual being exactly that number is very low?
There is a bias towards determinism. Our business culture favours unequivocal projections, whilst the real world pretty much guarantees that they won’t be met. Our economic system is based on Rational Expectations Theory with the belief if we only had more information, better mathematical models and the means to calculate then we could make accurate predictions.
Some industries have profit tied directly to probability and risk. Think insurance and investment. Risk Management is a critical part of how they operate. Although it came surprisingly late, with Risk Management becoming a widely adopted only in the 1980’s. The mathematics has been established since Blaise Pascal and Pierre de Fermat in 1654. Only 330 years later do we see risk being modeled broadly across in the financial sector.
Spreadsheets probably have a lot to do with this timing. Monte Carlo Simulation was invented in the 1940’s and we have had business computing since the 1960’s. But the empowerment of individuals to do large calculations based on custom models of risk only came in the 1980’s and 1990’s. Yet again, spreadsheets bring flexible computing to core, value-creating functions without being held back by monolithic, centralised computing. Hooray for spreadsheets!
It is 2026 and supply chain still hasn’t got that memo. Profits from supply chain operations are increasingly tied to the ability to manage risk and uncertainty. Your laptop and Excel today is 1000 times more powerful than the workstations that JP Morgan had in 1994 when they pioneered the concept of “Value at Risk”.
Yet most supply chain planning is based on expected values only. And then we complain that “demand is unpredictable” or “suppliers are not reliable”, when the plan never properly attempted to take those facts into account. Any plan that is subject to uncertainty but uses point values is implicitly assuming that the error is zero, or that it will cancel itself out.
Here is something you can do to change this. But first some background concepts.
Determinism
Determinism is the belief that everything that happens, must happen as it does. That all events within the universe can occur only in one possible way.
A deterministic system is one in which randomness has no role in determining future states. The same inputs and initial conditions will produce the same output, every single time.
99.9% of business software is deterministic. Most of what you’ll read about on this website is deterministic. Most of Planning is deterministic. We build deterministic planning tools in Excel because people want the same results given the same inputs.
And this is understandable. You’ll have a hard time selling software to decision-makers by saying, “This is highly realistic software. The thing is, you never quite know what you’re going to get.”
People crave certainty. As human beings, there are a number of ways that we get mislead about thinking about probability. Many of these biases are described by Behavioural Economics, an idea that overturns the traditional view that humans have rational expectations and economic systems are predictable.
Example: a $10,000 product seems cheaper if the first price we’ve been given was $20,000 versus $5,000. This is anchoring bias.
Another example: A test identifies a disease with 95% accuracy. You test positive. What is the chance that you have the disease? If your answer was 95%, then you’ve just fallen foul of another bias: Base Rate Neglect. (If the disease is prevalent in the population at a rate of 1 in 1000, then you are going to get 999*0.05=49.95 false positive results for every true positive, so the correct answer is 2%.)
This is a human thing. Even experts make the same mistake. A study of Harvard Medical School staff and students found that over half made the same mistake on exactly the same question above.
With these biases in decision-making and the complexity of modern supply chains, is it hard to see how a real-world system can be deterministic. The basic unit of decision-making is not rational and in complex systems, small changes can have large effects.
The real world has randomness and uncertainty. But calculating it is hard and takes time and energy. For most of human evolution, as our brains evolved, it has been useful for us to approximate this to deterministic rules. “If I stay here picking these berries, that Sabre Toothed Tiger is going to eat me! I’d better run!” is more successful than “There is a 63% chance that I will get eaten, on the other hand, the nutritional value of these berries is.. arghhh!”
The father of behavioural economics, Daniel Kahneman, characterised our thinking in terms of System 1 and System 2. System one is the fast, intuitive way of thinking that’s based on heuristics or rules of thumb. This is the one that is most prone to psychological bias. System 2 is the more deliberate, careful calculation that takes time and energy. We tend to economise with System 2 because it’s hard work.
My point here is that both System 1 and System 2 are still based on a deterministic view of the world. However carefully we think, we have what Kahneman calls a narrative fallacy, which is the tendency to impose causality and coherence on events often governed by chance. We construct simple and concrete explanatory stories that “assign a larger role to talent, stupidity, and intentions than to luck.” And focus on “striking events that happened rather than on the countless events that failed to happen.”
Faced with a world that is uncertain and a brain that craves certainty, there is another bias at work – Substitution bias. We avoid answering a difficult question by substituting it with an easier one. We avoid calculating the probability of various different scenarios and instead choose the one that we believe is the most likely.
So perhaps it is understandable that we have a tendency to avoid questions of uncertainty. And instead, address questions where the answer is deterministic. Hence the deterministic bias.
There is another reason why deterministic systems are so prevalent in business. To answer this, indulge me in a little thought experiment.
Imagine that you could predict future demand with 100% accuracy. What would be different? What would be the first thing that you would notice? Well, you wouldn’t be reading this blog post. If you could predict future demand, planning would be a solved problem. And like any other solved problem, it would be largely automated. You could just feed in your reliable forecasts into a set of business rules, and you would get a reliable list of the best things that you could do in order to meet demand. Sure, you’d still have some uncertainty, like, for example, less than 100% on-time deliveries and production reliability. But arguably the biggest problem in supply chain planning would be solved. So there’d be very little role for human planners on the demand side of the equation.
And here we have a first clue. With this little thought experiment, it sounds very similar to the marketing for countless software companies over the years. As we’ve seen, you sell a lot more software using promises of certainty than you do with admissions of randomness. Part of our deterministic bias comes from the billions that’s spent on marketing enterprise software. Spend enough money and you can get people to believe anything. Organisational culture and management thinking has grown up around a theory of rational expectations.
What’s more, if you think about a business system like ERP, it is built on a transactional history. Transactions give a clear, precise, deterministic view of what has happened. So it’s natural that if you use a transactional system to try and manage the future, you are going to get a picture of the future that looks very much like the past. The problem is, the past is purely deterministic. We have facts. The future has no facts.
So, if you think about it, this is a big problem. A large part of supply chain is managing uncertainty. To solve this problem, we rely on deterministic systems and carry a substantial deterministic bias, in the hope that ever bigger, faster systems are going to get us to that single perfect answer. Yet we know that they won’t.
We strive for perfect accuracy and perfect prediction. The inevitable error in our predictions it’s something bad. The difference between our plan and actual events? That is a failure of the planning process. Or incompetence. “Just work harder an smarter, and our deterministic dreams will come true!” Hence we continue to see planning like this:
This exclusive focus on expected values it’s a problem. If error equals failure, then it is usually something to be hidden or managed away. If, on the other hand, error is a fact about the real world and our ability to understand it, then it should have equal status with our expected values.
How about I show you a simple, intuitive way of representing uncertainty based on error that is almost guaranteed to give you better results than just focusing on a single set of expected values?
A Simple, Intuitive Way of Handling Uncertainty
So, what’s the alternative to a deterministic computer system? We would call that this a stochastic system. A stochastic system is a system that incorporates randomness or chance, meaning its behavior isn’t perfectly predictable and varies with probability. The bad news is it doesn’t give you one simple answer. The good news is that it’s going to be able to represent the real world much better than a system that assumes deterministic results. The bad news is that stochastic systems our complex and rely on statistics, which can get technical and intimidating.
So I’m going to show you a simple, intuitive way of thinking uncertainty. You will be able to build a simple stochastic system that will give you far better results than assuming that there is one true picture of the future. I’m going to use this system in two different ways to give you a way of testing Inventory Policy and another to testing your capacity plan. And we’re going to do all of this in Excel without any complex statistics.
The key to this method is based on three solid principles.
- Signal vs Noise. If you are trying to predict a future value based on some information, usually the historical series of that value, then there will always be a mixture of signal and noise. The Signal to Noise ratio is a good way to understand how predictable it is. This is why the debate between “striving towards forecast perfection” and “throw out all forecasts – they are all either lucky or lousy” is silly. Forecast accuracy is not binary and neither is uncertainty. The signal is the part carrying information, much like the sound of someone’s voice over a phone call. The noise is random variation or caused by events that have nothing to do with the value you are studying. Like static or the background noise from that same phone call.
- Sampling and the law of large numbers. If you have a coin, you don’t know whether it’s going to come up heads or tails. If you toss it a hundred times, you’ll have a pretty good idea of the likelihood of it being heads vs. tails. This is sampling.
- Understand the difference between uncertainty and variability. Faced with a difficult question, people tend to want to answer an easier one instead. So you will often find safety stock formulas and inventory policies tied to measured variability (easy to measure) rather than uncertainty (not as easy, but I will show you how).
Sampling
Sampling is really powerful because it’s explainable. Whether you’re tossing a coin, rolling a die, or drawing coloured balls out of an urn, there are clear examples that anyone can understand and relate to. In the case of supply chain planning, we are going to do exactly the same thing when it comes to our demand or supply. The main difference is: whereas a coin has a flat distribution of probabilities. 50/50 and dice have and even chance of 1/6, the distribution of error and uncertainty is going to require a different shape.
Sampling is simply drawing values at random from a distribution. Choose your distribution that represents the process that you’re modelling. And then generate random values that over time would fit this distribution. Here’s an illustration.
Uniform distribution is usually from a set of discrete values like a coin or dice. Normal is symmetrical (the “Bell Curve”) and highly favoured because that symmetry makes for simple maths. One last bias: normality bias, which is the assumption that all random values come from a normal distribution. Gamma is an example of a skewed distribution with a lower bound (minimum value) and a long tail (low probability of getting high value).
All we need to know about distributions can be seen from the video. Uniform is out as this would require us to define the range and all values inside the range would have equal probability and those outside would have zero. Not likely. Normal is problematic because even with medium variability there is a high chance of getting a negative value. If your model produces negative demand then there is a problem with the model. All gamma distributions have a lower bound at zero, the highest probability of getting a value inside a range around the mean (standard deviation, more on that soon) and a low chance of getting a very high value. This is why gamma is the most appropriate for modelling demand. Or perhaps another distribution called lognormal. But, of course, you can measure historical data and prove this for yourself by fitting it to various distribution types and choosing the best one. It’s just very unlikely to turn out that your demand is normally distributed.
I’ve written before about Monte Carlo simulation and sampling is the core process in this powerful technique.
In order to do sampling, you first need to define your distribution. Here is where we can go into complex statistics. But we do not need to. We can define the distribution with three things:
- Type. Is the distribution uniform, normal, gamma? There are many statistical distributions. I use the example to demonstrate that there’s only one that’s worth considering from this list when it comes to characterising demand. We’re going to use Gamma. Some demand models might use lognormal but we will do fine by using a gamma distribution.
- Mean. This is the average value that will emerge over the population of samples over time. In other words, if you sample from this distribution with a high enough number of samples, then the average of all of your samples is going to converge on this mean.
- Coefficient of Variability (CV). This is defined as the standard deviation divided by the mean. Standard deviation is simply a measure of how spread out the values are, so how tall or wide the shape of the distribution is. Here are some examples.
[ normal and gamma with different cv values]
I’ve used Python to develop these animated examples. However, you can explore different kinds of distributions with Excel using this downloadable example Distributions.xlsx. Or view online here.
Excel has formulas to be able to sample from a given distribution if you provide the parameters that define it. NORM.INV for normal, GAMMA.INV for gamma, RAND for uniform. Now, different distributions require different primary parameters, and they may not be the same as the ones you see here. But there are mathematical relationships between them that you can get from this downloadable example. I won’t go into the math here, it’s quite straightforward and they’re built into the formula that I’ve given you in the Excel tool.
Signal and Noise
Now once you’re armed with sampling, you could go forward and build a stochastic demand model using the chosen distribution and Excel formula that I’ve just given you. You could generate scenarios that vary randomly according to the distribution with a variability that matches your historical variability.
We have established that models of supply chain planning that assume reliable deterministic calculations are not going to work. All of those evangelists who promise ever more reliable forecasts and ERP-style calculations that give you an exact answer of what you need to buy, make, and hold in stock? They are misinformed or lying to you. Supply chain planning needs to take uncertainty into account. Humans aren’t very good at dealing with uncertainty.
For some people, this means you chuck out the forecast and set up your supply chain for something that you can measure, like variability rather than an unreliable prediction. This might be the DDMRP or lean just-in-time approach.
Like many evangelistic positions, both of these are less helpful than having a balanced approach to error and prediction. It’s wrong to say that all forecasts are reliable. But that doesn’t mean that all forecasts are terrible. It turns out that there’s quite a lot of useful space between those two extreme positions.
The problem is with lead time. Whenever you have a lead time, you are forced to make a prediction. Even if you are sizing your supply chain based on average demand, you are still predicting that the historical average is going to be applicable. The longer the lead time, the further you have to predict. For very short production lead times, short purchasing lead times, then a supply chain configured with demand variability and no prediction might work. But as long as you have a lead time, you need to make some kind of prediction about what demand and supply is going to be over the course of that lead time. Any inventory policy, including DDMRP places “demand over lead time” at the core of the buffer calculations.
Even if you simply use average daily usage (ADU), you are making a prediction that the average demand is stationary, and you’re using this to create a moving average forecast over the demand lead time.
Now, if you’re forced to make a prediction about what demand will be over the lead time, and you have many different items or products to predict. It is highly unlikely that all of those items are going to be exactly as easy or hard to predict. Some products will be easier to forecast than others. Even if you are just using a moving average, that moving average is going to produce less error for some items than others. So it makes sense to give prediction a go and measure the error rather than just measuring the variability.
For example, an item that sells very well at Christmas and nothing throughout the rest of the year has high variability but also high predictability. Here are two items with exactly the same variability and mean. Which one of these are you going to find easier to plan for?
This downloadable Excel file Signal_and_Noise.xlsx is designed to demonstrate that variability is a poor proxy for uncertainty. Using variability (CV, Coefficient of Variability, standard deviation / mean) for things like sizing inventory is going to give you identical policies for two very different products.
If you look at these two series, we have a high signal series in green above, and a low signal series in blue down below. The high signal series is a synthetic example demand pattern that’s based on a seasonal cycle. You can see the repeating peaks around Summer time, week 27 and Winter season around Christmas. There is some noise or randomness around this pattern, but the seasonal pattern is shown repeating.
Overview Video
The one below has much less of a discernible pattern. If you watch the video, you’ll see me regenerate these series, which just means that we’re recalculating with random values. The blue one will be jumping all over the place, whereas the green one stays with a recognisable pattern throughout every scenario.
Now, if my life depended on getting the balance right between service level and cost of inventory, and I had to choose between two series, I would be choosing the green series all day long. I’d want between 1.5 and 2 times the total inventory for the blue series than I’d need for the green series because the green one tracks up and down in a predictable way over the year. Whereas with the blue series, those peaks that reach 200 per week could come at any time and we need to be able to meet that level of demand throughout the year rather than just at the peak months.
However, I’ve carefully engineered the blue series to represent an average level of demand and variability that exactly matches the green series. You can see for yourself that if you repeatedly hit F9 and recalculate this file, the mean and CV will change every time you recalculate, but the values of green vs blue will be identical to 3 decimal places. I know of no better illustration of the problem in using variability as a way of sizing uncertainty.
Hence if you were to use the average and the coefficient of variability to size your safety stock, like most of the safety stock formulas want you to do, then you would be allocating exactly the same policy to each of these items.
So if using the variability is a bad idea, what should you use instead? Well, there’s a clue on the chart which is the forecast error. This is the percentage mean absolute error. We’re taking all of the errors over the validation period, measuring the difference, taking a mean, and then putting that as a percentage of the overall mean demand. The green series has an error that’s somewhere between half and three-quarters of the error that you’d see in the blue series. This depends on how much noise you put in the green series, and there is a box in E7 that you can play around with to see how they respond.
How to Use Sampling to Generate Demand Scenarios
So, I’ll give you a simple, as possible way to do this calculation for yourself. Don’t be intimidated by all the statistical terms that you might see on this chart. Or the dynamic array formulas that I’ve used to create it. I did not use our fast Excel development template for this example because I wanted a fast, responsive, dynamic chart that would make changes as fast as possible. The pivot tables that we would use in the development template would simply not be able to keep up.
Let’s say we wanted to generate scenarios for the last 52 weeks of this period, for the year 2026. We need to divide the time series into three zones:
- The training zone (weeks 1-104)
- The rolling validation zone which is weeks 104 to 156.
- And the scenario zone or test zone, which would be weeks 157 to 208.
The green series needs the first two years of history to properly train the forecast. The forecast is an ETS or error, trend, and seasonality. Because the seasonality runs on an annual cycle, we need at least two years to train the forecast model to understand where the peaks and troughs come. The rolling validation zone is where we forecast alongside the actual values. We’re careful not to bring the actual values into the forecast calculation, of course, so that the training for that forecast moves along and lags the forecast period by a number of periods that represent the lead time. So, for example, if we’re generating a forecast for period 105, we have a 6-week lag or lead time. That is using the data up to week 99 to generate that forecast. This is a reliable way of getting a measurement of forecast error. We do the same thing to the Blue Series, the only difference is we’re using a moving average forecast because there’s no seasonality pattern or trend to discern. We could use an ETS model as well, and it would just ignore the seasonality and trend and just focus on the error. But I wanted to give you something simple that’s going to perform almost as well. A moving average is actually a really good forecast for this kind of series.
The scenario or test zone is where we’re generating a hundred scenarios each by using the forecast value and then sampling from the error distribution to give us a reliable, real-world picture of how that demand may vary according to the forecast. That forecast error is essentially our uncertainty. We’ve measured how accurate our forecasting is, so we would expect there to be a band of error that follows the forecast expected value. In the light green and light blue at the right-hand side of each of these charts, I’ve plotted the 90% range, which is the zone in which 90 out of a hundred of those scenarios is going to fall. Now you can see the green series has a much narrower range and the range tracks the forecast, whereas because we’re using a moving average, and the moving average will necessarily be flat, and all the variation in the blue series is error then we have a much wider band in the 90% range.
The scenarios themselves are calculated in hidden columns between N and D, I, and then again between D, N, and H, I. So you can have a look at how those have been calculated.
The basic method goes as follows:
- Generate a forecast for the validation period, compare it with actuals. Moving Average or ETS using FORECAST.ETS function. Each period is going to have an absolute error, which is the absolute value of the forecast minus the actual.
- Create an average of these errors to give the “Mean Absolute Error”.
- Calculate the standard deviation of these errors. This gives the error variation that you see at the top of the chart. You can also calculate the CV of the error by dividing it by the mean.
- Characterise the distribution. As we said before, we are going to use a gamma distribution. This distribution requires two key values or parameters that will define the it.
- The first is called Shape or α. The gamma shape is the square of “Mean Absolute Error” divided by the square of the standard deviation (otherwise known as the variance).
- The gamma scale (ϑ) it’s calculated by the Shape divided by the “Mean Absolute Error”.
- Use GAMMA.INV formula with a rand value, and you’ve got a sampling method from a gamma distribution that matches your error.
- Generate the scenarios, all you need to do is take the scenario forecast. We’re generating a 52-week forecast based on the fixed training period, which is all of the actuals all the way up to week 156. We’re adding or subtracting the error, and we’re using the historical proportions of positive to negative to ensure that if there is any bias, we’re taking into account. This should be pretty close to 50%. And then you size the error using the gamma formula that I’ve just provided.
This is a relatively simple method that uses a fixed error distribution for every period in the forecast. To do it in a more sophisticated way, we would apply the error distribution to every period. So for example, the seasonal peak would have less error than the noisy middle. As with most models, the extent to which you represent reality needs to be balanced with the complexity of the model. I have tried to provide a simple-as-possible way.
Here is a little video where I have explained some of the formulas and talked a little bit about how this sheet is built.
Detailed Video On How It Is Built and The Scenario Calculations
So, there we have our theory and practise relating to uncertainty vs variability, and a practical way to take the certainty in the forecast and apply error so that you have a set of scenarios that represent the full population of situations you may encounter, rather than planning to a single expected value.
I have recently held webinars which use this technique in production-grade tools to both evaluate safety stock formula and capacity. We will be doing a few other articles that cover this in detail, along with a recording of a webinar that we recently produced.
I’m hoping that I’ve given you something that isn’t too technical, that even if you don’t have the statistical theory knowledge, which I didn’t when I first started doing this kind of simulation a few years back. This is as simple as I can explain it with a practical example that gives you, if not real-world examples, but certainly something that is representative of the different types that you’ll see in real data.
Download the file, see how you go, and if you have any feedback or questions, then you can use the comments or get in touch at [email protected].