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

Technical difficulty: 1

Model description

When competitors enter an established market, they have to build a reputation for their product and fight for market share with others that are already established. This takes time, so a realistic model should have a gradual loss of market share to competitors.

Consider the following problem: Market volume for your product is expected to grow each year by (10%, 20%, 40%) beginning next year at (2500, 3000, 5000) up to a maximum of 20,000 units. You expect one competitor to emerge as soon as the market volume reaches 3,500 units in the previous year. A second would appear at 8,500 units. Your competitors' shares of the market would grow linearly until you all have equal market share after three years. Model the sales you will make.

The figure above shows the model. It is mostly self-explanatory. The interesting component lies in Cells E14:L14 which divides the forecast market for your product among the average of the number of competitors over the last three years and yourself (the '1' in the equation). Averaging over three years is a neat way of allocating an emerging competitor 1/3 of your market strength in the first year, 2/3 in the second and equal strength from the third year on - meaning that they will then sell as many units as you. What is so helpful about this little trick is that it automatically takes into account each new competitor and when they entered the market, which is rather difficult to do otherwise. Note that we need three zeros in cells C12:E8 12 to initialise the model.