mytest > help > Part 2. MM4XL Tools > 1. Strategic Tools > Risk Analyst > Risk Analyst Expert in a Few Minutes

Risk Analyst

Risk Analyst Expert in a Few Minutes

Risk Analyst is a compact but feature-rich tool for building and simulating Excel models with the Monte Carlo technique. The following brief instructions will show you how to run Risk Analyst for the first time, either using one of the example sheets that come with the tool, or building a model of your own.



Using an existing model

 Monte Carlo Simulation Software: Management Process Risk Analysis

In the MM4XL floating toolbar, you can open Risk Analyst either by clicking the button shown above, or by selecting Decision Analysis -> Risk Analyst from the MM4XL menu. On the form that appears, click the Options button. On the new form, select the file NPV.xls from the Open an example sheet listbox, then click Cancel twice to go to the new sheet.

If you need help, click the button on the Options form to access Help with Distributions. This provides quick online help, showing the syntax of each mmFUNCTION and offering suggestions on when to use them.

Step 1
If you are opening the file for the first time, you may see #NAME? displayed in all cells that contain a formula. (If this is not the case, go to Step 2 below.

To get the formulas to work, select each cell that displays #NAME?, press F2, and then press Enter.

When all the formulas work, press F9. You will see the figures displayed in green change their values. These figures are defined in the form of, say, a range of values rather than a single figure, and each time you press F9 a new value in the range is chosen. For instance, in D17, mmTRI(5.8, 6, 6.5) is producing figures in the range 5.8 6.5, with 6 being the most likely value. Every time F9 is pressed, a new value is displayed in the cell. These are called Random Numbers, and they are the building blocks of simulation.

Step 2
Open Risk Analyst to simulate your first model. On the main form, click the Simulation button. On the form that appears, click Run Simulation. In the lower left corner of the window you will see a message concerning the simulation trials. When the simulation runs are complete, the Preview form shows the results of the simulation in four pages.

On the Preview form, click the Export button to paste a copy of the chart to the sheet.

Click the Print button to access the form where you will define the simulation report. For this first exercise, select the Complete report option. Click the Report button to print the report either to the active worksheet or to a new one, depending on your preference.

Step 3
Interpret the results of the analysis. For information on interpreting the results, refer to the appropriate sections of this help chapter. A background in Decision Analysis would significantly speed up the learning process.


Building your own model


Note: Before proceeding, you should read the previous section Using an existing model.

To build a simulation model from scratch, you must identify and model the sources of uncertainty.

Step 1: Identifying the uncertainty
Say that we are modeling the Net Profit of a project, which is obtained by subtracting Cost from Revenue. If Cost is equal to 30% of Revenue, then Revenue is the main source of uncertainty in this project, and we can model it with an assumption (either based on solid facts and data or simply by using educated guesswork).

Step 2: Modeling the uncertainty
We can make a very basic assumption about future revenue for the project, such as that it will range between $18 and $23 millions, with the most likely value placed around $20 millions. This assumption can be modeled with the Risk Analyst formula:

mmTRI(18, 20, 23)


Step 3: Build the model
Open a new sheet. In cell A1 type the label Net Profit, in A2 type Cost, and in A3 type Revenue. In cell B1 type the formula +B3-B2, in cell B2 type +B3*70%, and in B3 type the following formula (Note: You may need to replace the commas with the separator character used by your Excel release. Also, the formula works after Risk Analyst has been launched at least once during an Excel session.):

=mmTRI(18, 20, 23)+mmOUTPUT()


Risk Analyst provides 27 different Probability Distribution functions (pdfs), 3 Property functions, and 3 Utility functions. The online help that opens from the Help with Distributions button on the Options form shows the syntax for each mmFUNCTION and offers suggestions on when to use them. mmFORMULAS can also be entered from the Risk Analyst main form using the Paste to Sheet button, which copies to the active sheet the contents of the Formula Bar to the right of the button.

Fitting data
When you are not sure which is the proper function to use, and you have data relating to the process you are modeling, you can use the Fitting Data option to help you select the function that best fits the data. Say, for example, that in the range B2:B6 we have sales values for five years: 20.2, 20.8, 21.5, 21.7, and 22.8, and we want to find a fit for the data. Open Risk Analyst and click the Fit Data button. Select range B2:B6, and click the Fit Data button. The Fitted Distributions field of the main form will display a list of fitted functions sorted in descending order of fit accuracy. From the list, select the function you want to use in order to show the chart, and click the Paste to Sheet button to paste its formula to the active sheet.

Step 4: Roll the model

 Monte Carlo Simulation Software: Management Process Risk Analysis

In the MM4XL floating toolbar, you can open Risk Analyst either by clicking the button shown above, or by selecting Decision Analysis -> Risk Analyst from the MM4XL menu. On the form that appears, click the Simulation button and on the form that appears, click Run Simulation to start simulating your first model with the Risk Analyst tool of MM4XL software.

There is much more to the Risk Analyst tool of MM4XL software than what has been described in this short introduction. Read this entire help chapter in order to uncover all the features and details of Risk Analyst.

Lifetime license:

Price: euro

Vote this tool
We proudly serve
Your vote
vote1 vote2 vote3 vote4 vote5
Email:
Gender:
M
F
Age:
Position:
Department:
Comment: