 Part 1. Introduction to MM4XL
 Part 2. MM4XL Tools
 1. Strategic Tools
 BCG Matrix
 Brand Mapping
 Brand Switch
 Decision Tree
 Forecast Manager
 McKinsey Matrix
 Profile Manager
 Quality Manager
 Risk Analyst
 Risk Analyst Expert in a Few Minutes
 Introduction to Decision Analysis
 Introducing Risk Analyst with an example
 1. How to run Risk Analyst
 2. Simulation Never heard of it
 3. Examples
 4. Functions
 1. Property Functions
 2. Utility Functions
 3. Distribution Functions
 mmBETA (Scale, Shape)
 mmBETAGEN (Scale, Shape, [Optional: Lower], [Optional: Upper])
 mmBINOMIAL (Trials, Successes)
 mmCHI2 (Degrees)
 mmDISCRETE (InputRange, Probabilities)
 mmERF (Mean)
 mmERLANG (Scale, Shape)
 mmEXPON (Mean)
 mmEXTVAL (ModalValue, StDeviation)
 mmGAMMA (Scale, Shape)
 mmGAUSSINV (Mean, Scale)
 mmGEO (Trials)
 mmHYPERGEO (Sample, Defects, BatchSize)
 mmINTUNI (Lower, Upper)
 mmLOGISTIC (Mean, StDeviation)
 mmLOGNORMAL (Mean, StDeviation)
 mmNEGBIN (Failures, Successes)
 mmNORMAL (Mean, StDeviation)
 mmPARETO (Location, ModalValue)
 mmPARETO2 (Location, ModalValue)
 mmPERT (Lower, ModalValue, Upper)
 mmPOISSON (Mean)
 mmRANDBETWEEN (Lower, Upper)
 mmRAYLEIGH (ModalValue)
 mmSTUDENT (Degrees)
 mmTRI (Lower, ModalValue, Upper)
 mmUNIFORM (Lower, Upper)
 mmWEIBULL (Life, Shape)
 Probability functions
 Technicalities
 Sources
 2. Analytical Tools
 Business Formulas
 mmBASS, Bass Diffusion Model
 mmBEI, Brand Equity Index
 mmBEP, BreakEven Point
 mmBEPR, BreakEven Point with Fixed Rate of Return
 mmBUYRATE, Purchase Rate Model
 mmCAGR, Compound Annual Growth
 mmCHIp, Chi Squared Test
 mmCODING, Coding of variables
 1. Customer Satisfaction
 2. Database Functions
 mmDHMS, Number to Time
 mmEI, Evolution Index
 mmEXPECT, Expected values
 3. Forecast Errors
 mmGROWTH
 mmGROWTHBACK
 mmGRP, Gross Rating Points
 mmHERF, Herfindahl Index
 mmINTERPOLE, Linear Interpolation
 mmLEARN, Learning Curve
 mmMSAR, Market Share to Advertising Ratio
 4. Opportunity Index
 5. Performance Ranking
 6. Project Management
 mmPREMIUM, Price Premium
 mmPRESS, Product Performance Index
 7. Price Indexes
 8. Queuing Theory
 mmRANGE
 mmREBUY, Repeat Purchase Rate
 mmREBUYS, Estimated Number of RePurchases
 mmRELATIVE
 mmSAMPLE, Sample Size
 mmSAMPLEMIN, Minimum Sample for Significant Values
 mmSEASON, Seasonality Indexes
 mmSHARE
 mmSIGNIF, Significance Test
 mmVARc, Coefficient of Variation
 Cluster Analysis
 CrossTab
 Descriptive Analyst
 Gravitation Analysis
 Proportion Analyst
 Sample Manager
 Segmentation Tree
 Variation Analyst
 3. Charts and Maps
 mytest
 Version MM4XL
 contribute
 copytest
 css
 emails
 excel market analysis software
 finance
 download dbx
 download eu
 download manual
 download na
 download removed
 finance dbx
 finance eu
 finance manual
 finance na
 finance removed
 NO TITLE
 NO TITLE
 help
 Part 1. Introduction to MM4XL
 Part 2. MM4XL Tools
 1. Strategic Tools
 BCG Matrix
 Brand Mapping
 Brand Switch
 Decision Tree
 Forecast Manager
 McKinsey Matrix
 Profile Manager
 Quality Manager
 Risk Analyst
 1. How to run Risk Analyst
 2. Simulation Never heard of it
 3. Examples
 4. Functions
 1. Property Functions
 2. Utility Functions
 3. Distribution Functions
 mmBETA (Scale, Shape)
 mmBETAGEN (Scale, Shape, [Optional: Lower], [Optional: Upper])
 mmBINOMIAL (Trials, Successes)
 mmCHI2 (Degrees)
 mmDISCRETE (InputRange, Probabilities)
 mmERF (Mean)
 mmERLANG (Scale, Shape)
 mmEXPON (Mean)
 mmEXTVAL (ModalValue, StDeviation)
 mmGAMMA (Scale, Shape)
 mmGAUSSINV (Mean, Scale)
 mmGEO (Trials)
 mmHYPERGEO (Sample, Defects, BatchSize)
 mmINTUNI (Lower, Upper)
 mmLOGISTIC (Mean, StDeviation)
 mmLOGNORMAL (Mean, StDeviation)
 mmNEGBIN (Failures, Successes)
 mmNORMAL (Mean, StDeviation)
 mmPARETO (Location, ModalValue)
 mmPARETO2 (Location, ModalValue)
 mmPERT (Lower, ModalValue, Upper)
 mmPOISSON (Mean)
 mmRANDBETWEEN (Lower, Upper)
 mmRAYLEIGH (ModalValue)
 mmSTUDENT (Degrees)
 mmTRI (Lower, ModalValue, Upper)
 mmUNIFORM (Lower, Upper)
 mmWEIBULL (Life, Shape)
 Probability functions
 Risk Analyst Expert in a Few Minutes
 Introduction to Decision Analysis
 Introducing Risk Analyst with an example
 Technicalities
 Sources
 2. Analytical Tools
 Business Formulas
 1. Customer Satisfaction
 2. Database Functions
 3. Forecast Errors
 4. Opportunity Index
 5. Performance Ranking
 6. Project Management
 7. Price Indexes
 8. Queuing Theory
 mmBASS, Bass Diffusion Model
 mmBEI, Brand Equity Index
 mmBEP, BreakEven Point
 mmBEPR, BreakEven Point with Fixed Rate of Return
 mmBUYRATE, Purchase Rate Model
 mmCAGR, Compound Annual Growth
 mmCHIp, Chi Squared Test
 mmCODING, Coding of variables
 mmDHMS, Number to Time
 mmEI, Evolution Index
 mmEXPECT, Expected values
 mmGROWTH
 mmGROWTHBACK
 mmGRP, Gross Rating Points
 mmHERF, Herfindahl Index
 mmINTERPOLE, Linear Interpolation
 mmLEARN, Learning Curve
 mmMSAR, Market Share to Advertising Ratio
 mmPREMIUM, Price Premium
 mmPRESS, Product Performance Index
 mmRANGE
 mmREBUY, Repeat Purchase Rate
 mmREBUYS, Estimated Number of RePurchases
 mmRELATIVE
 mmSAMPLE, Sample Size
 mmSAMPLEMIN, Minimum Sample for Significant Values
 mmSEASON, Seasonality Indexes
 mmSHARE
 mmSIGNIF, Significance Test
 mmVARc, Coefficient of Variation
 Cluster Analysis
 CrossTab
 Descriptive Analyst
 Gravitation Analysis
 Proportion Analyst
 Sample Manager
 Segmentation Tree
 Variation Analyst
 3. Charts and Maps
 images
 img
 Test pages
 js
 licenses
 lightbox
 logs
 marketing resources
 picture_library
 plesk stat
 press release
 res
 brochures
 copytest
 icons
 links
 proudly_serve
 seminars
 tabs
 tools
 test
 apacheasp
 cgi
 coldfusion
 fcgi
 miva
 perl
 php
 python
 ssi
 treeview_img
 weyou


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 1PktEntry.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. 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.83.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. 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 1239, 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, N121 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 finetuning 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%. 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 $13 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. 