Growth in a market over time


This section includes two different ways of modelling the development of the market. The first example provides a simplified NPV calculation of the sales of widgets, where most of the uncertainty arises from the market trends. The second example produces a sales projection for a product that is in the market with a finite horizon.

Example 1

The finance director of the UK company you work for has asked you to determine an NPV for 10 years of cashflows from opening a new store in Times Square, New York (assuming no residual value, e.g. a lease end). The discount rate (your WACC - the weighted average cost of capital) is assumed to be 8.5%, and as a UK company you have to convert back the dollar profits to sterling. You can make profits from both selling your own brand as well as from selling other, proprietary brands.

Sales Volume

Management expect that eventually you are going to sell annually between 650000 and 1,090,000 widgets, but most likely 800,000 widgets. This can be modelled with a PERT(650 000, 800 000, 1 090 000). The initial total number of widgets you sell is assumed to be a percentage of this, depending on the money spent on the product launch (this is a decision variable, see below). If the management decides to spend the 'normal amount' for the product launch, they believe that the first year's sales will be Pert(35%, 40%, 50%) of the eventual annual sales. The sales after the first year are expected to grow roughly according to the following equation:

where i is the year from project start, di is the fraction remaining that is achieved in year i and l = Pert (0.8, 1.2, 1.9).

All units (own brand and proprietary brands) will be sold at a US$19.22 retail price, which is increasing with the rate of inflation.

Own brand/proprietary mix

Initially, our own brand products are expected to have a (25%, 28% 35%) share of all sales, but this share is expected to rise to (45%, 48%, 55%) by year 5 and this rise is assumed to be roughly linear.  The margins of the proprietary sales are 35.4% of the sales price, while the margin for own brand is 47.3%.

Cost of product

The cost per unit for proprietary product is fixed at US$14.01, irrespective of volume.  However, the cost per unit for own brand product is a function of volume, and an expert has estimated the following relationship:

         Own brand cost price (GBP)

Sales

min

most likely

max

200

7.51

7.71

8.20

500

 6.35

6.57

6.94

800

5.40

5.59

5.90

1100

4.59

4.74

5.02

Capex

The initial shop fit and launch are expected to cost US$(43.2, 43.3, 43.45) million.

Fixed costs

The fixed costs of this project are estimated to be US$2.15 million per annum.

Inflation

You have asked three experts for their opinion on the inflation rate for the next ten years.  The three experts believe that the inflation rate in the UK will increase roughly linearly, but have varying opinions on the degree of increase per year:

Expert A:                PERT(0.7%, 0.9%, 1.0%)

Expert B:                PERT(0.1%, 0.4%, 0.6%)

Expert C:                PERT(-0.2%, 0.4%, 1.2%)

The inflation in the UK this year is 3.3%.

Inflation rate in the US is roughly Normal(0.5%, 0.03%) lower annually than in the UK.

You will increase store prices by inflation.

Exchange rate

The US$:GBP exchange rate is currently 0.62.  Assuming that the pricing power parity (PPP) holds, the US$:GBP exchange rate can be estimated with the following equation:

Xrate (US$/GBP)t = Xrate (US$/GBP)t-1 * (Inflation rate UKt-1/Inflation rate USt-1)

In addition, the exchange rate is expected to change Normal(0,3) % of itself each year.

Decision option

You could spend an extra US$6 million on the launch (Superlaunch), in which case the starting sales volume is estimated to be (62%, 65%, 69%) of estimated ceiling, and then growing with the same equation as above up to this ceiling.  Evaluate the two options (planned launch or superlaunch), plotting the NPV distributions together on the same graph.  Which option, if either, should the company take?

What are the expected NPVs, and the probabilities of each achieving a positive NPV?

Discussion

The example model   Market growth model provides a solution to the model.

There are several key issues in this model that require special attention:

  • We are estimating the ultimate market size for the product using the PERT distribution. The market needs several years to reach its peak and the slope of the growth curve accounts for a large share in project's overall uncertainty.

  • Another key element is modelling the fraction of own brand sales. This is done again through a PERT.

  • To model the cost of production for each year we need to construct a trend curve to be able to find what the minimum, most likely and maximum values for the given amount of sales are.

The figure below shows the outcome distribution for two scenarios:

Both scenarios have almost a 100% probability of a positive NPV, i.e. of achieving the hurdle rate of 8.5%, which is encouraging. The Superlaunch option has a lower expected NPV. The correct way to use this graph is just to take the expected value of the NPV distribution when the discount rate is risk-adjusted, which is explained here.

Example 2

There are between 50,000 and 60,000, most likely 55,000 owners of a particular type of property in the country who could possibly purchase your product, and it is estimated that there is a 25% to 40%, most likely a 27% probability that each one will end up finally purchasing the product. With each year, you will make a sale to some of these customers, which of course leaves fewer potential customers the following year. In the basic situation, you can assume that the rate of conversion equals 12%. If you have mastered this, you can do an alternative calculation in which you assume that the probability of selling to a customer asymptotically decrease with the following relationship:

P (Sell to customer) = (1 + t/b)-a

where t is the year, b = 0.6 and a = 0.3.

How do we model this sales forecast?

The example model Sales projection for a finite market provides a solution.

The first version of the model (tab "basic") uses the constant rate of conversion equal to 12%. The second version (tab "Alternative") uses the steeper declining curve suggesting that the rate of conversion is much higher in the first year and decreases sharply later.

See Also

 

ModelRisk

Monte Carlo simulation in Excel. Learn more

Spreadsheet risk analysis modeling

Tamara

Adding risk and uncertainty to your project schedule. Learn more

Project risk analysis

Navigation

Enterprise Risk Management software (ERM)

Learn more about our enterprise risk analysis management software tool, Pelican

Enterprise risk management software introduction

FREE MONTE CARLO SIMULATION SOFTWARE

For Microsoft Excel

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

Download ModelRisk Basic now

FREE PROJECT RISK SOFTWARE

For Primavera & Microsoft Project

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

Download Tamara Basic now