We have been talking recently about extending Excel with Python. Here is a webinar recording that covers that discussion with a live demonstration and some examples.
I made the point that if you are comfortable with Excel and are getting some good results in planning there, you should only go to Python for the right reasons. And I suggested a very good reason is to do things that are just not practical to do in Excel.
Some examples I gave were: Optimization for Production Scheduling, Forecasting and Analytics, and Inventory Simulation.
We will be preparing some more content on how to do some of these in Excel and Python. And I would like to get feedback as to the areas that most interest you. You can provide this in a quick survey here.
In this article, I would like to give some background to Inventory Simulation- what it means, why you might want to try and the different levels of sophistication- starting with the simplest. I will also share some more thoughts on the other areas – Production Scheduling with Optimization and Forecasting – in subsequent articles.
There will be no download, code or implementation details here. Before we jump in to building the simulation, it is worth taking time to think through the process. The most important success factor in any kind of systems development is having a clear picture of what you want to achieve, down to the detail of function and features. It is also key to define the concepts and science that will be applied. Simulation is a model of the real world and like all models it won’t represent everything. There is no way it can. I will suggest some core features that would be a good start. I hope to walk you through the thought process and give a clear idea of what a basic simulation will look like and how it might evolve.
I recommend building this kind of project in stages. Start with something simple, make it work and then update it from there. It is more important to think about what we leave out than what we put in. The features left out of a basic model provides a long list of possible improvements for the next version. I am going to think through these choices and make suggestions about what is priority.
We are going to define levels of sophistication, starting with Level 0: the simple example that I used in the webinar. We will build up from there and come out with a clear specification for Level 1. I’ll develop and deploy Level 1 in an webinar and article that is coming soon – exact timing depends on the results of the survey. This topic could easily get up to Level 8 or higher, but we will be spend most of the time thinking about Level 1 and what might come next in Level 2. This way I hope to build it up gently and bring you along as I do it.
Inventory Simulation – What Is It? Why Do It?
Inventory simulation is a way to model inventory systems to determine the best policies for replenishment. A system here is a set of processes. In this context, inventory system is not software. It is the set of things that work together to get inventory where you need it. Inventory is going to include any item that is held in stock and used in the service of customer demand. It could be finished goods, produced components or purchased parts.
Most inventory planning is deterministic – i.e. you use one number for the demand and a fixed lead-time for the supply. It might vary item to item, but for each item, the demand is expressed by a single number to represent the quantity of demand in a given period. The lead-time is a single number that defines the expected difference between order date and due-date.
But life is not like that. If demand and supply were predictable, we’d need far less inventory (perhaps close to zero) and planning would not be the complex problem that it is. Nevertheless, you cannot place an order for a probability distribution. Your supplier will not be able to respond to a 50% chance of needing something. We make decisions deterministically, but we plan with uncertainty.
Simulation helps because instead of looking at one scenario, you can study the results of, say, a 1000 scenarios. Each scenario varies according to the likelihood of that thing happening. For example, if we forecast demand in a given week being 10, the chances of it being exactly 10 might only be 50%. The actual number might be 11 or 15 or 0. All we know is that it will be a positive number and some numbers are more likely than others. So we have a probability distribution.
If you can generate 1000 scenarios that match this distribution, and then run a certain set of policies, then you can see the likelihood of getting a stock-out with that policy. You can calculate the range of inventory levels that you would have. We have a probabilistic input and over many samples, we can get a probabilistic output. But the mechanism for each scenario is deterministic and works in a way that we can recognise and understand. You get the chance to run a financial year not once but a thousand times with each one varying with a level of uncertainty that matches the situation. Then you can do that again and again trying different things and seeing which works out the best.
In this way, we can be more scientific. But in a way that keeps the maths under control. There will be statistics involved, but we don’t need a higher degree in the subject to understand it. Let’s start with something that is familiar.
Monte Carlo Simulation
The most well-known method of inventory simulation is Monte Carlo. The name gives a hint of the randomness that it uses to better understand uncertainty.
Imagine you are at the roulette table in the casino. You can see 36 numbers around the wheel, plus a zero. If you place a bet on a single number, you know you have a 1/37 chance of winning. Assuming that you trust the casino to operate a true wheel with the same probability of landing on each number. If you are getting a good return on the bet (more than 37:1) then it is worth doing. You might bet at the house rates of return (36:1, worse) for fun, but you do it knowing that the house has the edge and by how much.
Now, imagine the wheel is crooked. The chance of the ball landing in “1” is very different to the chance of landing in “2”. Some numbers might come up 50% of the time, others 0.1%. All the the chances are based on hidden probability. You would be more nervous in placing any bets, right?
Most people would watch the wheel for as long as they could before making any bets. If you could get the data from 1000 spins of the roulette wheel then you would be much more confident.
This situation is a bit like inventory planning, but instead of numbers at the roulette wheel, we have stocking items. And many more than 36 items. You choose to put your company’s limited working capital on some of the items. You can bet on many items but you can’t spread it too thick. If that inventory moves quickly in service of the customer, then it pays off well. Well done, you made a good bet. If it moves slowly then that’s not so good, particularly if you had a shortage elsewhere. If the inventory ends up getting written off, then that is a disastrous bet. Every day, the planner is making bets. Wouldn’t you want to know about the chances of shortage and excess?
Inventory simulation is like getting 1000 spins of the wheel. But without risking any money.
Inventory Optimization
I should take a moment to connect inventory simulation with optimization. In this series of articles, I want to reclaim the term “inventory optimization”. If you do a search or look through text books, inventory optimization usually involves some inventory analytics, replenishment techniques and safety stock calculations. Use ABC XYZ analysis to segment your parts and then use this to determine which items justify the most attention. This approach can be helpful to get started, but it has some major flaws and false assumptions, which we will touch upon below.
Putting those flaws aside, the truth is that does not really do any real optimization. It helps supply chain professionals to allocate resources to the items that need it the most. But the inventory policy is set with guesstimates and rules of thumb. The policy is set item by item, but it lacks a holistic view of what is the best set of policies for all items. This might end up with a policy that is good enough and is how most inventory is planned, but it is a stretch to call it optimization.
In the field of Operations Research and other scientific disciplines, Optimization refers a mathematical process where you conclusively find the best solution. Or you fail to find it because your constraints do not allow it. For example, the CEO of the company might say “I want you to find the best policy that gives us > 99% service level with less than 60 days of inventory.” This might or might not be possible, even with the best imaginable policies. The desired service level combined with the uncertainty of demand and supply will determine a minimum level of inventory, in the best of all worlds . Optimization will tell you if the goal is feasible and if it is, what that policy will be. It might tell you what is the best you can get if the stated goal is infeasible.
Most applications of Optimization are deterministic. A simple problem with a small number of variables can be hard. For instance, one of the application areas of optimization is production scheduling. For n jobs that need to be scheduled into production there are n! possible schedules. 5 jobs is 5! = 1*2*3*4*5 = 120 different sequences. 10 jobs is 3,628,800 possible sequences. 60 jobs has more possible sequences than there are atoms in the known universe (8.3 x10^81)! . So even with deterministic calculations – in this example, assuming all processes are completed in their standard time with no variation – it is a hard problem to solve.
The application of Optimization under conditions of uncertainty is Stochastic Optimization. Stochastic means operating under conditions of randomness and uncertainty. And to do Stochastic Optimization it is almost a practical necessity to involve simulation.
So, with inventory simulation we can start with the most simple example and go all the way to really cutting edge techniques for optimizing inventory against financial and operational goals. We start at Level 0 with a very simple simulation. This article will discuss going to Level 2-3. By the time we are at Level 8, we will get to state-of-the-art Stochastic Optimization. But we will pick up some techniques from optimization science along the way, even at level 3.
Inventory Simulation in Excel Only
I have done these simulations in Excel and they can be very useful. You can take a complete planning system and pair it up with something that will generate simulated transactions and then run it as if “today” is 1-Jan-2024 and then increase the day by 1 and do it again and 365 times to simulate the year.
Naturally, even with the Fast Excel Method, an inventory planning tool might take anywhere from a few seconds (small data) to a minute or two (1000’s SKUs). So even if you repeat it with weekly intervals, that could take an hour. And that is just one scenario. So not practical for Monte Carlo. (at this point, I know that someone is going to try and prove me wrong!)
Inventory Simulation in Python
In Python, you can speed this up to run in seconds. So it can make sense to do a year of demand 1000 times. And then do that all again for 100 different policy combinations to choose the best one.
What’s more, we can start simple and build up step by step. Without complex systems development. The modelling of the supply chain can be built up and tested at each stage.
In the webinar, I demonstrated a way to do a simple Monte Carlo inventory simulation, using Python in Excel (the Microsoft 365 product). I did it without any coding. It did require some cut and paste from a generative AI Large Language Model (LLM). But we could get the initial result (far from perfect, but working) in the space of 10 minutes.
I would like to discuss how we might expand upon this to take it to the next level.
Level 0 – Simple Monte Carlo Simulation
Here are the characteristics of this simple model:
- A single SKU (Stock Keeping Unit – an inventory Item at a location).
- Daily demand that varies with a normal distribution (parameters to define the mean and a standard deviation).
- On-hand inventory that is depleted by daily demand.
- On-hand inventory is replenished with a simple re-order point and fixed order quantity.
- Replenishment is done against on-hand inventory.
- Lead-time for supply also has variation. We use a gamma distribution (parameters to define the mean and shape – more on this later).
- Supply orders can only be one pending at a time. This limits it to items with short lead-times.
As spell out the parameters of the model and the limitations, I hope you can already see where it can be improved and made more realistic. I imagine that any supply chain professional would like to see more realism and sophistication for it to be useful. And so we should expect for a 10 minute, low-code live demonstration!
Level 1 – Baseline Monte Carlo Simulation
Let’s discuss each of the changes that you might want to see and then we can prioritize what to include in the next level up.
A Single SKU Item
This is usually the first gap that gets noticed. We can’t do our items one by one, there are too many. Once we have the model working for one item, we could quite easily run a program to loop through all the items. This is an easy change, from a programming point of view. However, I suggest the baseline simulation should focus on doing a single item first. The reason is that we need to handle more parameters so that we can define exactly how the different items differ in the model. There is no point running the same simulation with multiple items if those items don’t differ in a way that matters. I’m going to suggest what matters most and these new parameters get defined below.
Daily Demand that Varies with a Normal Distribution
This assumption does need to be adjusted. Few supply chains have normally distributed demand. But hang on! Don’t the safety stock formulas assume normality, using a standard deviation for demand variability? Yes, and that is one reason why they might not work in practice. In most cases, demand is not normally distributed.
Without going into statistical theory, we can make this point a couple of ways. First, note that a normal distribution is valid for outcomes that are affected by a high number of independent factors. The classic example is a Galton Board, with a load of balls that cascade down through a series of pins.
These pins are independent- the chance of the ball going left or right is always 1/2. Supply chain demand, on the other hand, is affected by a high number of factors, but it is unlikely that they are all independent. We can imagine many reasons why multiple demand factors might be correlated with economy, competition, customer growth, interdependent products and so on.
Another reason to be suspicious of a normal distribution is illustrated below. Check the image of a normal distribution, mean=10 and standard deviation=5. This is just medium variability- the CoV (coefficient of variability, STDEV/MEAN=0.5, is not a big value, relatively speaking).
Observe that some results are negative. but demand cannot be negative. We would say it is lower bounded at zero. So we need a distribution that only returns positive values. And this lower bound at zero would be true if the mean was 100 or 1000, because CoV is a ratio. Any distribution that is bounded at one end but open at the other will be skewed. More like the Gamma or Lognormal below.
For modeling supply chain demand, a gamma distribution might be better. Or for variation with higher range of values, a lognormal. Each has the equivalent of a standard distribution so you can define the level of variability.
Don’t get hung up on the statistics and the details of how. In both python and Excel, generating these distributions is a different single formula or a single line of code and is easily swapped out. What is important to know here is that you should measure your variation and distribution shape from actual demand data and allocate each item to a distribution that fits the best. Talking about supply chain statistics means we have to be careful. So I will state the obvious- distribution here means the statistical distribution and has nothing to do with logistics and the movement of goods.
The variability of demand and the shape of the distribution can be a parameter, along with the mean and the shape/standard deviation. Just know that a normal distribution might not be the best and can generate negative demand which is naturally undesirable.
Note that there are still serious limitations in just using variation in demand as a proxy for uncertainty. There may be many reasons why demand goes up and down and not all of them are predictable. We will discuss this further below.
On-hand Inventory Is Depleted Only By Daily Demand
A good assumption, I’d suggest that we leave this one. You may not get demand every day, or even every week. When the demand is zero for that day, there is no change, when demand is non-zero it is depleted by that amount. We are not modelling shelf-life, shrinkage or any other ways for inventory to deplete. Note that demand might be customer or from production as dependent demand.
On-hand Inventory is Replenished with a Simple Re-order Point and Fixed Order Quantity
This is a simplistic aspect. A more realistic scenario might be a min-max system with both a re-order point and an order-up-to point. You may also have minimum order quantities to include and these are straight forward to add.
It is worth noting that all these policies need to be automatically followed in practice. If planners are making manual decisions based on personal judgment and qualitative data, the model will not be much use. Simulation should be used to create robust policies that can generate recommendations based on rules. If you want to go with a non-data driven approach then there is no point in simulating because we have no way to model the process.
We might also wish to model a periodic review policy where requirement is checked and orders placed every n weeks. This can reduce the cost of planning and administration for an acceptable trade-off with responsiveness.
Replenishment is Done Against On-hand Inventory
The alternative to this is to set policy against forecast demand. I would recommend setting a simple forecast based on a moving average. This is a pretty good benchmark and would be good enough for this level of sophistication. For more complex models, there is a range of other forecasting methods that could be included and python is a great gateway to accessing some world-class models. If we build the simulation model right, we should be able to swap the forecast method later by just changing a parameter or few lines of code.
Lead-time for Supply with a Gamma Distribution
This makes sense to keep. Just as with demand, you can measure the actual lead-times you get from suppliers and set the effective lead-time accordingly. As with demand, you can’t have negative or zero lead-time.
Supply Orders Can only be Pending One at a Time
In the simple model, a supply order gets raised when the inventory reaches the reorder point. And then no more supply until that order is received. In practice, for long lead-time items, we might have a pipeline of supply and we cannot wait until the first order is received before we place another. This is certainly something to change for this level. And any inventory projection should take pending into account.
Level 1 – Summary
This baseline model establishes some core aspects of the behaviour of a inventory item. We can choose the right demand pattern, select from basic inventory policies (Fixed Quantity, Min-Max, Periodic Review), project with a benchmark forecast, and model realistic supply with lateness and to handle supply over long lead-times. It still works on a single item, but now there are more parameters to be able to run this for any item in the target list.
This discussion gives a skeleton of specification and with a few more details it could be ready for implementation. The webinar shows an example of how this can be done without a python programming background by using LLMs to generate the code. The logic so far should be easy for an intermediate programmer to code without assistance. This specification and thought process is critical to giving clear instructions to either a programmer or LLM. The techniques for programming, whether from scratch or with assistance, are a topic for another article. I will be running future webinar events and video courses that will go through the implementation of Level 1.
It is better to get to Level 1 and gain experience with the model and different scenarios before specifying the details of the next level. The further we go, the more complexity we get, and possibly the more we tie the model down to a particular situation and set of scenarios. However, here follow some comments on possible areas of enhancement once Level 1 is stable and tested.
Level 2+ Considerations for What Comes Next
Multiple Items and SKUs – Level 3
It’s tempting to think that the next level might be a run through all the items, applying the right parameters that fit. However, by thinking the project through in this way, we can identify reasons why this might not be advisable.
Firstly, it is important to consider what are the key differences between product items versus scenario runs. When we run with the same item we are applying random variation to demand and supply. But the distribution of that demand and the mean values are fixed. When we run different items we need the model to apply a distribution, order policy, and mean values that suit the item.
This is where classification would come in. Before running the model on multiple items, we would need to measure the demand history of the target area and put them into classes based on their demand pattern. There would be no need to run a simulation for every item, as every item in the same class would have the same result. Choose the classes in a way that best suits the target business.
One option for classification is the SBC (Syntetos Boylan Croston) method which groups items into: Erratic, Lumpy, Smooth and Intermittent. However, this framework relies on 2 measures: the average interval between orders and the squared coefficient of variation. This model ignores the components of demand that have signal and are predictable- namely, trend and seasonality. It might make sense to do more work on this first, especially if your demand has a significant seasonality and/or trend, as most demand would have.
There is a fair amount of demand analysis here and doing some further work on the single item model would do a lot to inform that process. Hence we would put that to Level 3.
Demand Patterns – Level 2
The baseline model assumes that mean demand is flat. It also assumes that the forecast is not able to detect any seasonality or trend patterns that might be apparent in practice. So as we prepare for forecasting models that are more sophisticated than moving average, we might need to consider decomposition. It is possible to take a demand history of 2 years or more and decompose it into three components: Error, Trend and Seasonality. Trend and Seasonality are signal, error is noise.
So an ETS model can help study the target demand and decompose the demand so that it can be reconstructed for the model. Items that have a strong seasonality or trend can be modelled as such, and we would expect to see a lower inventory requirement than items with a strong component of noise. Likewise, we can use an ETS forecast like Holt-Winters to use in the model. ARIMA offers an alternative forecasting and decomposition method. We don’t need to be too concerned about forecasting selection, Python offers access to many prebuilt open source forecasting models. It is good to know the options and the broad approach and then learn by applying and comparing them.
It is also possible to use demand history more directly. On the one hand, you can’t just use the actual history as this would be going back to a deterministic model. You would end up overfitting your inventory policy to a history that will never repeat. But there is a technique called “bootstrapping” where you change the history in random ways that represents the underlying uncertainty but comes out with the same aggregate measures as a whole. For instance, the demand on any given day would be different to what you actually got, but over the year the average and variability would be the same.
Combine this with decomposition and you can answer questions like: What would happen if we had the same variability, but with a different trend? Or if we had new products E,F and G that behaved like A,B and C but had a steeper ramp up and more uncertainty? Or if demand suddenly exploded for 20% of our products and collapsed for another 20%?
BOMS, Manufacturing and Dependent Demand – Level 2
Inventory simulation can be used for finished goods, produced components or purchased parts. The simplest way would be to explode the product demand with the bill of materials and get the total demand for every item. Then we can ignore the BOM completely and treat every part as if it’s demand is independent. You might find that the demand for common components is more predictable as variations in product demand is evened out. This approach is sufficient for Level 2. There might be some considerations for a more sophisticated model like variability in production lead-times (affecting the demand profile of when the child item is required) and interdependence between items, for example finished products and the components and spare parts that go into it. But we can leave these considerations for a higher level.
Policy Selection – Level 2
Once we have the demand considerations above, it could be a good time to update the model to do some Policy Evaluation. For example, let’s say we have 2 parameters that set the amount of inventory cover we will get: the reorder point and the order-up-to point, AKA Min and Max. Thus far, we have applied numbers for these based on experience, estimation or some safety stock equation. Now it could be time to get the simulation to find the likely best combination.
If you imagine the two parameters, Min and Max at 90 degrees forming a grid. This gives a set of all possible combinations. We can run the same simulation scenarios for each square in the grid. We would need a way to measure the performance of each combination. This could be a cost function that puts a cost on each unit of inventory over time and also the cost of a stockout. The business needs to prioritize these two dimensions and it might vary for different items. Then we have the simulation run the scenario set for each cell in the grid, calculate the cost and then move on to the next cell.
This is known as Grid Search and with a reasonable interval between rows and columns, we can get to a likely best answer.
The policy itself can also be evaluated- with cost implications for items that are checked daily, versus those on a weekly or multi-week cycle of checking and order placement, it may be for stable and predictable items, a longer review period between orders might work out to be more cost effective than a more frequent checking that would have less latency.
Supply Shocks – Level 2-3
Just as demand can change in unpredictable ways, so can supply. The COVID epidemic, Suez canal blockage, port strikes, tsunamis and many other “long tail” risks can throw lead-times out by 2 or 3x. Or even require you to find other suppliers with a different cost per unit and supply terms. Simulation can address these risks by modelling the exact changes. Perhaps we define multiple sources for the same item or expedite costs that are triggered when shortages require it.
My suggestion is that you properly model the risk of late delivery under normal conditions. And then look at what would happen if those lead-times got less predictable. And what would happen if you went to longer lead-times that were more predictable. By adjusting the scenarios so that they demonstrate real-world supply lead-times, a simulation can be more realistic than a real-world planning process that expects a unattainable lead-time!
Summary
This article has laid out a possible path to go from a very simple Monte Carlo inventory simulation to one that could truly add value to a complex supply chain business. The aim has been to give an insight into the thought process that should go before any line of code is written. With clear vision of success and an comprehensive understanding of requirement, this simulation could be built up to Level 2 in a matter of days or weeks.
If you like this and want to know more, then please complete this short survey. If you have already filled it in and wish to amend after reading this article, then please do so with the same email, mention it in the comment and we will use the later entry.
This is Brilliant!