mytest > help > Part 2. MM4XL Tools > 1. Strategic Tools > Risk Analyst > Introducing Risk Analyst with an example

Risk Analyst

Introducing Risk Analyst with an example

The workbook (Excel file) with the model described in this example is called 1-Pkt-Entry.xls and can be found in the directory Examples at the location where MM4XL software was installed.

This company will consider marketing a new product if in area tests at least 20% of potential buyers will prefer it against the direct competitor brand A. So far, the investment has been relevant, and more is needed if the product will be marketed. With the help of a simple model the marketing manager is trying to decide whether to pursue the project further or cancel it. The measure of success is Net Profit.


The model

The first step to build a decisional model is to make it clear where its sources of uncertainty are, and a Contributing Factor Diagram (CFD) can be of help.

We are modeling the entry of a product into portfolio (rectangle on the left in the following picture), with the goal being to market it successfully. The success of the product is measured with its Net Profit (hexagon on the right). The Net Profit is derived by subtracting costs from revenue. In our example, the overall Costs are calculated by adding together the cost of developing the new product, the cost of testing the new technology, and the cost required to market the product. On the other hand, the Revenue of the venture is derived by multiplying the product market share times the size of the (growing) market. However, in order to gain market share the product has to pass the user acceptance test, which is a decisional hurdle imposed by management in order to consider a new project.

 Monte Carlo Simulation Software: Management Process Risk Analysis


Modeling assumptions

In the CFD we recognize two major sources of uncertainty, User acceptance and Market share, and three educated guesses, Marketing costs, Test costs, and Market growth. Each of the 5 assumptions has been modeled with a Risk Analyst function.

Before modeling assumptions, we suggest building the model in Excel entering fixed values in the cells hosting uncertain items. The following picture shows the Excel model used for this example. Cells D10, D11, D16, D22, , D24 and D26 contain Risk Analyst formulae that model our assumptions. 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 into 4 areas: Costs, Threshold, Market, and Profit.
Costs, the upper area, results in the addition into Total costs (D12) of three variables. Development cost (D9) is a value we know for sure because $2.7 millions has already been spent, so it does not need to be modeled. Test costs (D10) is what we call an educated guess, a value for which there is no certainty, yet its real value lies in a range we can assume with confidence. In this case the cost of testing will be roughly $1 million (as shown in the model set to Show mode), and to model it we used a Uniform distribution ranging between 0.9 and 1.1, where every value in the range has the same probability of appearing. The third variable, Marketing costs, will have a final value of around $3 millions, although it depends a lot on the kind of pressure that will be put in place, which in turn depends on the kind of acceptance the product will have. The selection of a Triangular distribution ranging 2.8-3.5 with modal value at 3 is compatible because it assigns decreasing probability of occurrence to more extreme investments and because there is no statistical evidence to apply a more rigorous probability distribution function.

 Monte Carlo Simulation Software: Management Process Risk Analysis

The Threshold section contains two variables concerning the limit imposed by the management for the inclusion of the product into portfolio: Required user acceptance (D15) and Product acceptance (D16). This latter variable is important in the model because it filters, so to speak, the allowance to market the product, and therefore to pick up revenues that will cover costs. D16 was modeled with a Binomial variate, which is discrete and returns integer numbers. It takes two arguments: Trials and Successes. We assumed the trials to be 100 potential buyers and the successes to be the proportion of potential buyers purchasing during the area test, that is between 15% and 40% with average 26% (this may come from an ad hoc survey, for instance, or it may be a guess). The Binomial function 100, 25% returns values in the range 12-39, has mode equal to 29, and about 10% of the values lie below the crucial number of 20 purchases out of 100 (you can find this information using the chart in the Wizard window).

The Market area of the model holds one single, fixed number concerning the potential buyers in the market (D19).

Finally, in the Profit area there are three items. The marketing manager of this project assumes that, if launched, the market share of this new product could be roughly in the range 9%-15% with a most likely value around 12%. In this case a Normal variate is used to model the assumption, rather than Triangular as done above, 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 D23 the Profit per customer is a fixed financial value. In D24 Gross Profit is obtained with D19*D22*D23, and has been modeled as an Output cell in order to evaluate its result against the other variables of the model. The last variable in D26 is the main output variable of the model. It is obtained with an IF formula: if D16 is larger than or equal to D15, then in D26 show Gross Profit minus Total costs, otherwise show only the costs incurred so far, which is D9 plus D10.


Building the model

The model building phase may require several changes before reaching a final version. Typically, you build the model by first typing formulae, values and labels directly in the cells of an Excel file. All assumptions are typed as fixed values, or text labels suggesting the assumption (for instance, N12-1 for the normal function used in cell D23). When the skeleton of the model is built you then access Risk Analyst using the formula bar in the Wizard window. This way, selecting distributions and adding property functions becomes a smooth and easy process. Working from the Wizard window is really helpful for experienced as well as inexperienced users when defining assumptions, because the chart in the form helps you understand the shape of a distribution and the range of values it covers.


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. Operational time, however, is a minor issue with this model, because it runs very quickly and even 1000 runs may take just a few seconds.

The mmOPTNUM function run with the 1000 values simulated for the output variable Gross Profit (D22) returned 900 as the number of runs needed to stabilize the mean value of the series with an interval of 20 values. It seems there is no real need to simulate more than 1000 trials with this model, although the old rule holds: the more, the better.


Interpretation

This project is measured in terms of the Net Profit (NP) it can generate, so the output variable of interest is in cell D26.

The following chart corresponds to the distribution on 1000 NP values gathered during the simulation. It looks like the combination of two different Pdfs. On the right is a normal distribution and on the left side is a spike made of one single bar. The bar on the left is generated by the NP values simulated when the estimated product acceptance lies below 20%.

 Monte Carlo Simulation Software: Management Process Risk Analysis

This chart suggests that there is a 9% probability that the product acceptance will be less than 20%, and this would return a net loss of some $3.6 millions. Moreover, there is a 1% probability that the NP will return a negative value even if the acceptance boundary is 20% and more (class -0.517 and -0.080). Therefore, the chance of failure for the project is 10%.

The most likely NP value will be around $1.67 millions, although the chance is high (43%) that the NP will be between $1.450 and $2.325 millions.

There is 75% probability that the final NP will be in the range $1.013 million and $2.981 millions, say $1-3 millions.

From the Sensitivity report we took the following chart that shows the correlation of the output variable NP with the Input variables in the model. There is no surprise in seeing that the NP exhibits a strong, positive correlation with the variable in cell D16, Estimated product acceptance, and with the variable Market share in D22. These are both variables impacting heavily on NP in a positive manner, that is, the higher the product acceptance, for instance, the higher the probability to sell and therefore to make profit.

When working with complex models made of many variables, the information coming from the correlations in the Sensitivity report of Risk Analyst may offer very useful support to the analyst seeking to identify the items with an impact on the overall outcome of the model or only on parts of it.

 Monte Carlo Simulation Software: Management Process Risk Analysis

Lifetime license:
MM4XL All Tools
Price: euro 238.00

Vote this tool
335 votes: vote vote vote vote vote
5 stars:
133
4 stars:
178
3 stars:
16
2 stars:
6
1 star:
2
We proudly serve
Your vote
vote1 vote2 vote3 vote4 vote5
Email:
Gender:
M
F
Age:
Position:
Department:
Comment: