An example of a Monte Carlo simulation risk analysis model for forecasting

Technical difficulty: 2

Model description

Time series variables are often affected by single identifiable 'shocks', like elections, changes to a law, introduction of a competitor, start or finish of a war, a scandal, etc. The modelling of the occurrence of a shock and its effects may need to take into account several elements:

1. When the shock may occur (this could be random);
2. Whether this changes the probability or impact of other possible shocks;
3. The effect of the shock: magnitude and duration

Consider the following problem: People are purchasing your product at a current rate of 88/month, and the rate appears to be increasing by 1.3 sales/month with each month. However, we are 80% sure that a competitor is going to enter the market and will do so between 20 and 50 months from now. If the competitor enters the market they will take about 30% of your sales. Forecast the number of sales there will be for the next 100 months.

The model for this problem is shown in the figure above and can be downloaded here. The Bernoulli variable returns a 1 with 80% probability, otherwise a 0. It is used as a 'flag', the 1 representing a competitor entry, the 0 representing no competitor. Other cells use conditional logic to adapt to the scenario.



The StepUniform generates integer values between 20 and 50, and cell E4 returns the month 1000 if the competitor does not enter the market, i.e. a time beyond the modelled period. It is a good idea if you use this type of technique to make such a number very far from the range of the modelled period in case someone decided to extend the period analyzed. A Poisson distribution is used to model the number of sales reflecting that the sales are independent of each other and randomly distributed in time. The nice thing about a Poisson distribution is that it takes just one parameter - its mean, so you don't have to think about variation about that mean separately (e.g. determine a standard deviation).