Using Monte Carlo simulation for purchasing decisions can allow for overall cost reduction by selecting optimum order quantities. In this article we will explore how to use simulation to minimize the total cost of a purchased component.
When using purchased components, the purchase price is only part of the total cost. Carrying cost also should be considered. Carrying cost is the cost for holding the component in inventory.
In our example, we have a quote from a vendor that lists price based on quantity ordered. Prices are good for two years, so we will model two years of orders. The goal is to find the right balance of quantity to purchase each time an order is placed that will minimize total cost. As quantity ordered increases, part cost decreases, but carrying cost increases because there is more initial inventory after each order.
To complicate matters, the component has an uncertain demand. This is where Monte Carlo simulation for purchasing decisions comes in to play. We will treat demand as a random variable and use simulation to find total cost. In our example, total cost for a month is the inventory carrying cost plus the purchased cost of the units used.
The model of our purchasing decision is shown below.
In our model, we will simulate 24 months of activity. The demand for the component is modeled as a log-normally distributed random variable with μ = 6.62, and σ = 0.25. The probability density function of demand is shown below.
Carrying cost is assumed to be .083% per month. To calculate carrying cost, we use the average inventory for each month (start + end/2).
If inventory goes to 0, a new order is placed. The quantity of the order is determined by the decision variable in cell B4. The value of the quantity decision variable is one of the values in the range D4:D7. Each simulation we will run uses one of these values for order quantity.
We will run separate simulations for each price/quantity combination to determine which one has the minimum total cost.
Simulation Results
Simulation Master has a feature to run consecutive simulations. Four simulations were run, one for each price/quantity combination. Each simulation was started with the same random number seed so that each simulation had the same demand random variable values. The results are shown below.
Analysis
Mean Total Cost
The mean total cost for each simulation is:
Sim 1: 186,750
Sim 2: 182,584
Sim 3: 181,267
Sim 4: 181,426
Sim 1 had the lowest order quantity (1000) and sim 4 had the highest order quantity (5000). In sim 1, the low order quantity has the highest unit price, which caused sim 1 to have the highest mean total cost.
In sim 4, unit price was lowest, but additional carrying cost resulted in a higher total cost than sim 3.
Looking at the Spectrum of Outcomes
Now is the point where Monte Carlo simulation for purchasing decisions excels. We looked at the mean total cost earlier, but means don't tell us about the spread of outcomes, or the likelihood of where the outcomes fall within the range of minimum and maximum.
In the table below lists minimum, maximum and selected percentiles in between.
From the percentile table we see that a purchase quantity of 3000 is lowest for all categories, except 90th percentile and maximum. A purchase quantity of 5000 is lowest at the 90th percentile and maximum, although only slightly lower than the 3000 values.
From the model, we can see that cost of parts used is more dominant than carrying cost. A purchase quantity of 3000 has the lowest cost at the low end of total cost outcomes. For 1000 and 2000, the higher purchase price causes a higher total cost. For 5000, the higher carrying cost overrides the decrease in unit price, hence 3000 is the best balance of unit price and quantity.
At the high end of total cost outcomes, demand is high so we are turning over inventory at a higher rate and carrying cost is diminished somewhat. Since 5000 has the lowest unit price, at the high end, the penalty for carrying cost is less, and results in the lowest total cost at the 90th percentile and maximum total cost.
Conclusion
Simulation 3 had the lowest total cost over most of the range of outcomes, with the exception of the 90th percentile and above. Since we want to minimize cost in as many cases as possible, we should select a purchase quantity of 3000. This is an example of how using Monte Carlo simulation for purchasing decisions can lower total cost.
Excel is a registered trademark of Microsoft Corporation. Used with permission from Microsoft.