mytest > help > Part 2. MM4XL Tools > 1. Strategic Tools > Risk Analyst > 3. Examples > Example 2: Net Present Value
Risk Analyst Example 2: Net Present Value The workbook (Excel file) with the model described in this example is called 2-NPV.xls and can be found in the directory Examples at the location where MM4XL software was installed. This company is launching an innovative product. They know the competition will launch a competing product 6-12 months later, and have modeled a simple scenario to get an idea of the profitability of the product 5 years after launch. The measure of success is Net Present Value (NPV). The model The following Contributing Factor Diagram (CFD) shows a model concerning the 5-year Profitability of a new product (rectangle on the left in the picture) measured through the Net Present Value (hexagon on the right). The Net Present Value is found with an Excel built-in function that uses a yearly discount factor of 6% applied to the 5 years of a variable called Net Profit. In this example, the overall Cost is made up of the cost of development, the cost of logistics activities, and the cost of marketing. On the other hand, the Revenue of the venture is derived by multiplying the market share times the size of the market. From year 1 on, the market share is impacted negatively by the entry in the marketplace of new competitors. Modeling assumptions In the CFD we recognize two major sources of uncertainty, Market share and New competitor, and three educated guesses, Marketing costs, Logistic costs, and Development costs. Each of the 5 assumptions has been modeled with a Risk Analyst function. Before modeling assumptions, we entered fixed values in the cells hosting uncertain items. The following picture shows the Excel model used for this example. For the sake of explanation, in column B there is a shortened version of the formula used to model the assumption in the corresponding row. We arbitrarily split the model in 3 areas: Costs, Market, and Profit. In Costs, the upper area, three variables are added into Total costs (D12). Development cost in D9, and Logistic costs in D10 are what we call educated guesses, values for which there is no certainty but whose real value lies in a range we can assume with a good level of confidence. In this case the cost of Development will be roughly $1 million in Year Going (as shown with the model set to Show mode) down to $0.2 millions in year 4. A Uniform distribution ranging at different values over the years was used to model it. The cost of Logistics was modeled with a Uniform variate, but at a constant rate over time. The third variable, Marketing costs, has been modeled with a Triangular distribution that takes a declining shape over time. It starts at $6 millions in Year Going and lowers to $1.5 millions in the last year, due to the entrance of new competitors in the market. In the Market area of the model both the number of potential buyers (row 15) and the Growth rate (row 16) are fixed values entered by the analyst. In row 17, the number of new competitors is modeled with a Binomial distribution (discrete), built on the assumption that there may be a maximum of 3 competitors in the market with 40% as a constant probability for a new entry. The Binomial function 3, 40% returns values in the range 0-3 and has mode equal to 1 (you can find this information using the chart in the Wizard window). In the Profit area there are three items. The marketing manager of this project assumes that, if launched, this innovative product could immediately capture some 25% market share, which will start declining as soon as the competition enters the arena. We used a Normal variate to model the assumption because the manager assumes a lower chance of getting extreme values for the market share, so there is no need to spread risk on the tails of the distribution and a Normal variate fits well. In D 21 the Profit per customer is a fixed financial value. In row 22 Gross Profit is obtained with D15*D20*D21, and it has been modeled as an Output cell in order to evaluate its result against the other variables of the model. In row 24 the output variable Net Profit is obtained with D22 minus D12. In D26, Net Present Value is the decisional variable of this model and is found using the Excel built-in formula NPV with 6% interest rate and the Net Profit values in row 24 as the input range. As may already be understood, time series play an important role in this model. Risk Analyst offers the necessary tools to make an accurate and synthetic analysis of simulation data from distribution functions used in the form of time series (for instance, like the values in the range D24:H24). Simulation The final report of this model was made by simulating 1000 runs, although during the fine-tuning phase it is common practice to simulate only 100 runs in order to save time. The mmOPTNUM function run with the 1000 values simulated for the output variable Gross Profit (D22) returned 592 as the number of runs needed to stabilize the mean value of the series with an interval of 20 values. We kept using it with 1000 trials. Interpretation According to this model this project could return an NPV at 5 years ranging from 7 to 38 million dollars, with modal value at $15.3 millions. The following chart suggests that there is a probability of 33% that the most likely range within which the real value of the project could fall is $13.6-$18.5 millions, although a remarkable 27% probability extends the most-likely upper boundary to $25.1 millions. Management has made it known that they have no interest in projects contributing below $10 millions. In this project there is a probability of about 11% to return an NPV below this limit. To find the exact probability of return below a certain level we made an ascendant sort of the simulations produced for the variable NPV, and counted how many values lay below the chosen boundary. It is now a matter of risk attitude whether to accept the venture. Not surprisingly, we found from the Sensitivity report that NPV has a strong negative correlation with the values produced for the year 1 and 2 of the variable New competitor entry. The several time series we built using the function mmNAME help to show the tendency of the various variables across time. The following picture shows how the variable Net profit develops over time. The values used for tracing the colored regions correspond to the values in both reports Time series and Statistics. The chart is telling us that NP tends to stabilize after year 2 due to the entry of new competition in the market. Therefore, from a managerial perspective, action should be taken to prevent or at least delay the competitor entry. The longer our product stays free of competition the larger market share we can gain. The width of the interval between boundaries of the periods in this series points out the high uncertainty involved with the project (Y-axis going from $0 to $10 millions). The next picture is for the variable Gross Profit. It shows a slope with a marked declining tendency after year 1, again due to the effect of competition. It seems clear that management should focus on maximizing sales in the first 2 years. Developing share, however, might require more marketing investment, which might require modeling new scenarios with different levels of investment in order to evaluate the impact on the profitability of the whole project. Finally, a note on the shape of histograms. The three pictures below show, from left to right, the frequency distribution of the simulations for the variable Market share without competition (year 0), with and without competition (year 1), and with competition (year 3). It is interesting to notice how the shape of the distribution varies from a normally distributed one, to a combination of two normal distributions, to a scattered distribution. In year 1, the image in the middle, you can see that between the distribution on the left and the one on the right there is a gap of some 6 percentage points, which is the cost of competition being introduced to the market. It would be perfectly acceptable to raise legal hurdles to the entry of competition. |