This section includes two different ways of modeling 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 expects that eventually you are going to sell annually between 650,000 and 1,090,000 widgets, but most likely 800,000 widgets. This can be modeled with a PERT or a Triangular(650000, 800000, 1090000) (in this example, we used several Triangular distributions, but could also have used a PERT, see here for a discussion about both distributions). 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 Triangular(35%, 40%, 50%) of the eventual annual sales. The sales after the first year are expected to grow roughly according to the following equation:
\delta_i = 1- Exp(-i/\lambda) |
where i is the year from project start, di is the fraction remaining that is achieved in year i and l= Triangular (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 our 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 our 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: Triangular(0.7%, 0.9%, 1.0%)
Expert B: Triangular(0.1%, 0.4%, 0.6%)
Expert C: Triangular(-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 UK_{t-1}/Inflation rate US_{t-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
Market Growth Model provides a solution to the model.
Considering the two scenarios (NPV(1) = normal, NPV(2) = "SuperLaunch"), 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.
The links to the Market Growth software specific models are provided here:
Example 2
There are between 1,000 and 2,000, most likely 1,500 owners of a particular type of property in the US state 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 (both uncertainties modeled with a Triangular distribution). 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 decreases 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?
Spreadsheet Sales Projection for a Finite Market provides a solution.
To model the number of people who will finally buy the product, we use the Binomial (Probability of purchasing, Market size) function. 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.
The links to the Sales Projection for a Finite Market software specific models are provided here: