THE RISK EXPLORER USER GUIDEThis user guide provides an overview of the Risk Explorer tool contained within the Guide to Project Evaluation Series.

The Risk Explorer is an Excel Visual Basic for Applications (VBA) tool that enable users to identify, assess and analyse risk in project evaluation. It should be noted, however, that the tool is intended as a tutorial and not as a comprehensive risk analysis environment.

This guide provides an introduction to the key elements of the risk explorer, with selected screen-shots to illustrate the functionality and the interface between the explorer and the user.

PLEASE NOTE:

To perform risk analysis we require the use of analytical techniques and tools which may include user-designed spreadsheet calculations, or more advanced software functions. The tutorial tool (Risk Explorer) described in this manual illustrates the approach using an illustrative Excel based model and the use of third-party software from Palisade called @Risk. A simpler version of the Risk Explorer, called “Risk Analysis Example”, that does not require use (and thus availability) of the @Risk Software package is provided in Part 8 of the Guide. A user-designed (separate file) spreadsheet (developed by Tony Boyd and his team at Vicroads) is supplied with it as the calculation engine to perform the required simulations. The application of a triangular distribution is the only option of analysis within this stand-alone calculation engine.

1 Main menu

The Risk Explorer’s main menu screen is the starting point for the user. The main menu shows the series of steps the user goes through on the right hand side:

Step 1 - selecting variables

Step 2 - assigning level of risk

Step 3 - quantifying risk.

The additional ‘buttons’ on the left hand side of the top-level form provide an introduction to key concepts and principles, and an @Risk tutorial. The tutorial uses an external proprietary software package called @Risk.

2 Step 1: Selecting variables of interest

From the main menu, click ‘benefit variables’. The ‘Select Variables: Benefits’ screen is displayed. This screen helps the user to consider a range of variables that may present risk to project benefits, and to identify which variables are applicable to his/her project.

The first step is to view the variable types to be considered, using the drop down list at the top left. The ‘variable types’ are different areas that may present risk to a project. Click on a variable type to display a description of the variable type. Displayed in the ‘variable’ box

are a number of variables of that variable type. Figure 2 shows the ‘Select Variables: Benefits” screen.

Click on the variable, then click ‘Add >’ to add it to your list of variables.

Click ‘Remove’ to take variables off your list of variables.

Repeat adding variables for all the ‘variable types’ in the drop down list.

Click ‘SAVE’ to save your list when you have identified all the ‘risk bearing’ benefits variables applicable to your project.

Click ‘Return’ to return to the main menu.

This process is repeated to select variables presenting risk to project cost.

Figure 3 shows the ‘Select Variables: Cost’ screen.

Click on the variable, then click ‘Add >’ to add it to your list of variables. All variables can be added by clicking on ‘Add All >’.

Click ‘Remove’ to take variables off your list of variables.

Repeat adding variables for all the ‘variable types’ in the drop down list.

Click ‘SAVE to save your list when you have identified all the ‘risk bearing’ cost variables applicable to your project.

Click ‘return’ to return to the main menu.

3 View selected variables

From the main menu, click ‘View Selected Variables to display the list of these variables. This action allows the user to view their choices, before moving on to the next step. Changes can also be made by returning to the select variable screens and removing variables from the list, or inputting new ones.

4 Assign Level of Risk

From the main menu, click ‘Assign’ to go to the ‘Assign Level of Risk’ screen. At the top of this user form, variables that have been selected are displayed. For the variable, descriptions are displayed of what constitutes a high, medium or low risk. Using this information, and the judgement of the user, the user selects a high, medium or low risk rating to the variable.

Click ‘Next’ to go to the next variable.

Click ‘Return’ to go back to the main screen.

From the main form, the list of variable assignments can be viewed by clicking on ‘View assigned variables’. This will display the assignment of variables to the low, medium and high categories. Clicking ‘Process’ copies the high risks for the next step in the process.

5 Quantify Risks

Each high risk variable is now considered more closely. From the main screen, selecting the ‘Describe variables’ button takes the user to the following form:

This screen allows the user to record information about the decision making process. Information can be recorded to describe the variable, implications to the project and any

recommended actions to mitigate risk. This information is saved and may be printed as part of a report later on (using the ‘Save’ and ‘Print’ buttons).

Once this information has been saved, the user can return to the main form and click on ‘Quantify’ to bring up a user form where each high-risk variable of interest can be quantified.

As shown above, a variable is selected from the drop down box (‘Data Scope’ is the example variable). This variable is assigned a triangular distribution’ in the example screen-shot provided.

In summary, the steps to assign a distribution to a variable are:

Select the variable from the first drop down list. Select the distribution you wish to use from the second drop down list. The appropriate boxes will be highlighted as cyan colour according to your selection. Use

these boxes to describe the distribution of the variable factor.

The ‘View Spreadsheet’ button is used to go to the worksheet where simulation can be initiated.

As shown above, the user can specify equations of interest in the yellow scratchpad area of this worksheet.

The variables of interest need to be placed into the blue column. As shown two example variables located at cells N5 and N6 within the yellow scratchpad area are selected.

The contents of cell N5 =RiskOutput("Output Cell") + N3/N4

This tells @Risk that the variable in cell N5 should be simulated. The equation is simply the ratio N3/N4.

In order to run an @risk simulation the user clicks on the large ‘Simulate’ button in the worksheet.

This asks two questions:

1. Enter simulation parameters – number of iterations (for example, 300 as shown)

2. Select sampling type

The simulation begins to run as shown in the bottom left of the following screen shot. As shown, 90 iterations of the 300 iterations required have been completed.

Simulation results can then be examined using the drop down select box (as shown in the following diagram).

Examples of previous @Risk simulations are also provided in the above diagram.

The user can return to the main menu using the ‘Return to Tool’ button in the worksheet.

At this top level, the user may elect to save the simulation using the ‘Save As’ button.