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

Technical difficulty: 1

Model description

Some products are essentially a once in a lifetime purchase, e.g. a life insurance, big flat screen TV, a new guttering system and a pet identification chip. If we are initially quite successful in selling the product into the potential market, the remaining market size decreases although this can be compensated to some degree by new potential consumers entering the market. Consider the following problem: there are currently PERT(50000,55000,60000) possible purchasers of your product. Each year there will be about a 10% turnover (meaning 10% more possible purchasers will appear). The probability that you will sell to any particular purchaser in a year is PERT(10%,20%,35%). Forecast sales for the next 10 years.

The figure above shows the model for this problem. Note that C14:C22 is subtracting sales already made from the previous year's market size but also adding in a regenerated market element. The Binomial distribution then converts the current market size to sales. In the particular scenario shown in the figure the probability of selling is high (26%) so sales start off high and drop off quickly since the regeneration rate is so much lower (10%).