Useful time series models – Reduced sales over time to a finite market

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

Minimum software requirements: ModelRisk Basic edition

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%).

Download model

Click on the button below to download the Excel model. ModelRisk needs to be installed in order for the model to work.

Download model


For Microsoft Excel

Download your free copy of ModelRisk Basic today. Professional quality risk modeling software and no catches

Download ModelRisk Basic now


For Primavera & Microsoft Project

Download your free copy of Tamara Basic today. Professional quality project risk software and no catches.

Download Tamara Basic now