Monte Carlo Simulation | All things about Optimization

Revenue prediction and forecasts are typically done to be too right. They create a false sense of precision that ultimately falls short and does a disservice to managers who need accurate forecasts for planning. Hey, even meteorologists equipped with voluminous historical data and supercomputers cannot get it right all the time!

Why Monte Carlo Simulation?

Monte Carlo simulations can build valid probability models that give us much more accurate view into the future. There could be thousands of potential variables involved in monthly sales, and we cannot manage them all. MC simulation can help emulate real world combinations of few variables. Remember that MC simulation is not a magic bullet to create forecasts. For products that have seasonality and promotions, I typically use ARIMA, Stepwise Regression and Time Series techniques.

Revenue Prediction by Monte Carlo and Triangular Distribution

This simple example is driven by stochastic inputs. The stochastic input includes the units sold and sales price for a product without history. Since these values are non-deterministic, the user is asked to guess the best, worst, and most likely (mode) cases. A triangular random variable traverses the values that may occur between the best case and worst case values. For this revenue prediction application, we want to generate a random number from the triangular distribution for a given probability of this input. Hence, the triangular distribution is modeled with the following parameters:

a (worst case value)

b (best case value)

m (most likely value) or the mode; (a ≤ m ≤ b)

Both a and b serve as the boundary parameters (a < b)

Probability Density Function PDF can be defined as,

The inverse triangular distribution function is defined as,

We basically use this PDF to create values for the stochastic input such as units sold during the simulation runs. In every single run, these values are updated and recorded on a simulation calculation.

The spreadsheet in this post uses the above standard inverse cumulative method for generating a random number. Note that the Excel Rnd function is a pseudo random number generator and is not robust. You can use alternative uniform random number generator from tools such as Matlab or R.

The application is simple to use. Although few features require manual setup. You can begin by keying in best, worst and most likely (mode) of units sold and sale prices in columns C5 through E6. Enter total trials or simulation runs in column C10. I’ve simulated the revenue model using 5000 runs.

Next, start the simulation by clicking on the button. Higher number of runs makes your data set more realistic but it comes at the expense of computing speed and processor limitations. Cells B13 through E5012 are output of the simulation. The TraingleDist() takes as input the best case, worst case, and most likely values from the above input and outputs a random number that is found from this triangular distribution for a given probability.

The results are summary of basic stats from the simulation runs – Mean, Min, Max, and Sample Std. Deviation. They are in Cells K4 through K7.

Few Manual things to do:

So far, the VBA did the simulation from triangular distribution automatically. The histogram part requires few simple manual steps. If you plan on running the simulation for 5000 trials, you can ignore this step. There are 16 bins that represent the revenue data intervals. For number of trials other than 5000, simply change the following formula in cell K13.

=COUNTIFS($E$13:$E$5012,”>=”&J13,$E$13:$E$5012,”<“&J14)

Say you ran the simulation for 2000 runs, change the formula to

=COUNTIFS($E$13:$E$2012,”>=”&J13,$E$13:$E$2012,”<“&J14)

and drag it through K28.

The histogram plot is driven by the frequency distribution table above.

As you can notice that the results cluster around the mean of 24K. 5000 simulations may be unrealistic or not enough to get a good sample set. The limitations are mainly due to memory, and processor speed. Careful planning must be take into account for the long tail events. Questions to be addressed are – how to deal with just 16K revenue or how can a business support unexpected growth of 36K in revenue?

Download Revenue Prediction by Monte Carlo and Triangular Distribution spreadsheet

#excel-vba, #monte-carlo-simulation, #revenue-prediction

Your material handling system serves as oxygen of your business, so you want to do everything in your disposal to minimize downtime. The best way to increase system uptime is with a good preventative maintenance plan. As an engineer, you may have come across maintenance/performing repairs on Material Handling Systems such as Autonomous Vehicles, Industrial Robots, Conveyor Belts, or Machine Cells. Additionally, you probably have randomly generated demand, created inter-arrival rate of shipments, or simulated processing time in different machines etc. In this simulation study, I am going to estimate reliability of a material handling module of a warehouse over a period of time relevant to the system. The Weibull distribution is most commonly employed to determine reliability function.

Estimate System Reliability From Monte Carlo Simulation

A continuous distribution to plot a model fit from the failure rate of the system is created from a Monte Carlo simulation of 7000 trials. The idea behind Monte Carlo simulation is the generation of random events in a computer model, and this generation is repeated many times and counted for occurrence of a specific condition. The Monte Carlo simulation allows us to consider various aspects of system characteristics which cannot be easily captured by analytical methods such as K-out-of-N systems, redundancies, fault trees, repair and maintenance for components.

Furthermore, the reliability analysis from the Weibull distribution provides the information needed for identifying failures, troubleshooting, scheduling periodic maintenance and inspections. The restrictive modeling assumptions that had to be introduced to fit the models to available solutions, is thus avoided.

A series-parallel system of machines is considered here, each working in parallel and connected serially. To determine failure or Mean Time To Failure (MTTF) to be precise, consider each machine type to have k out of n system. Given that there are n machines in a machine type, the entire system fails when k of those n machines fail at any time. The simulation demonstrated here has 2 Warehouse Management Servers (k1), 3 Autonomous Guided Vehicles (k2), and 2 Robotic Palletizers.

Reliability Block Diagram

WMS – 2 Warehouse Management Servers in parallel

AGV – 3 Autonomous Guided Vehicles in parallel

RP – 2 Robotic Palletizers in parallel

MC – Machining Cell

As you can see in the block diagram, all individual have some sort of redundancy. The failure rate of the AGV depends on the failure of WMS components, similar dependency applies to two Palletizers and Machine Cell.

The failure rate of the above components was formulated as:

manufacturer provided reliability data. As evident, the failure rate almost doubles every 180 days from WMS.

The failure test runs for each day for 600 days. Each sub-system (components) should operate until it fails. Once it fails, it remains in failed status. The time that each component fails is recorded in the spreadsheet.

The cut sets are as follows:

{WMS1, WMS2}
{AGV1, AGV2, AGV3}
{RP1, RP2}
{MC}

Monte Carlo simulation of the system was conducted using Excel and the number of failures during 7000 trials was 990. The mean time to failure or MTTF of the system was 563 days. Look at the chart below, the probability of failure of the system converged to 14.15% after 7000 trials and it can be reasonably assumed that it will remain around 14.15% over a long period of time. The probability of failure is 0.1415 and was accurate within 3% after 1600 trials.