DECISION & RISK ANALYSIS



ALLOWING FOR RISK in SPREADSHEET MODELS

Tutorial on Risk Analysis with @RISK[1]

Kiriakos Vlahos

London Business School

1. NOVADUCT NEW PRODUCT APPRAISAL

A simple example of launching a new product is employed to illustrate step by step how risk analysis is undertaken with @RISK. The objective in Novaduct is to examine the robustness of decisions based on Net Present Value calculations, when some of the inputs change. First, the cashflows and resultant NPV are calculated in a small spreadsheet model, NOVA1. Next, the uncertainty about certain key assumptions is modelled explicitly by deciding on suitable probability distributions. To start, the relevant details of the new product launch and the associated spreadsheet model are set out in the next paragraphs.

1.1 Details of Novaduct's New Product

Management are trying to decide whether or not to launch a `novelty' product which is expected to have a market for the next five years. The following information about the product has been gathered:

i) The expected market size is 8,000,000 units in the first year with 2% growth per annum thereafter.

ii) A 15% market share is anticipated in the first year, growing linearly by 0.3% per annum afterwards.

iii) In the first year, the price is likely to be £7 growing by 6% annually thereafter.

iv) The expected variable cost per unit is £5 increasing by 3% per annum. Fixed costs are expected to start at £2,000,000 and to grow annually at 3% as well.

v) An initial investment of £2,500,000 is planned. A discount rate of 15% is usually taken in calculating the Net Present Value for projects of this kind.

These figures represent the `single figure estimates' for the product's future prospects in a growing market. This combination of estimates is referred to as the base case. Management require two measures of profitability for the venture - the present value of the cashflows and the rate of return. A simple spreadsheet model can be quickly built, (see NOVA1 in Figure 1) with the cashflow modelled in £000s). As can be seen, the base case for Novaduct has an NPV of 1312 (or £1,312,000) and an IRR of 30%.

1.2 Optimistic, Pessimistic & Base Case Estimates

It can be assume that the estimates for initial market size and market share are firm, but there is uncertainty about market share increase (currently 0.3% in cell B17) and market growth (currently 102% in cell B18). Optimistic, pessimistic and base case (most likely values) values for these parameters are set out in cells E17..G18 (see Figure 1.1). These can be used to determine the worst and best values for the NPV of the proposal (see cells E19..G19). However, it is extremely unlikely that all the worse (or best) circumstances will occur together.

[pic]

Figure 1.1: Nova1 spreadsheet

1.3 Uncertainty about Market Growth & Market Share Increase

Further discussion about some of the assumptions in the estimates persuades management that more explicit modelling of uncertainty is required for the variables, market growth and market share increase.

Firstly, market growth is most likely to be a 2% increase but could range from a 10% decrease to an 8% increase following a roughly triangular distribution. (A triangular distribution is one for which the probability density function takes a triangular shape. The lowest and highest values determine the base of the triangle, the modal or most likely value determines the highest point of the triangle). This form of distribution is quite plausible: the most likely values are around 2% but values as extreme as -10% and +8% are possible but not so likely to occur.

Secondly, market share increase can be assumed to be uniformly distributed between -0.2% (shrinkage) and 0.8% (in contrast to the single figure 0.3% assumed before). This means that growth is equally likely to take any value in the range -0.2% to 0.8%.

Armed with these assessed distributions,

Market Share Increase: Uniform on -0.2%, 0.8%

Market Growth: Triangular on 90%, 102%, 108%

the entries in assumption cells (B17 and B18) in NOVA1 are amended to their probabilistic equivalents using @RISK.

2. USING @RISK ON NOVADUCT'S MODEL

2.1 Getting Started

From the “Start” menu choose “Programs”, “Palisade Decision Tools”, “@Risk for Excel”. This option starts Excel and then loads the @RISK spreadsheet add-in. The effect of operating with @RISK is to add an @RISK menu and another toolbar to Excel (the @RISK Toolbar) and create two initially hidden separate windows (the @RISK Model Window and the @RISK Results Window) which will be described later.

[pic]

Figure 2.1: The @RISK Toolbar (the icons are explained in the appendix)

2.2 Adding Probabilistic Values with @RISK

First the Novaduct spreadsheet model file NOVA1 is opened, and a copy saved to your home drive (or disc) C: (or A:) say. Note that you will probably want to save your simulation results on the same drive as your model so ensure that you have enough free disc capacity. Next, the entries in certain cells (Market Share Increase and Market Growth) are changed into variables with probability distributions. The steps are:

1. Get the Novaduct worksheet in memory using: File Open

In the dialog box, check that the Drive is correctly set.

Specify: File name as NOVA1 [.xls]

then click OK

Then File Save As specifying the Drive as A: or C:

Change the entries in B17 and B18 to hold variables with distributions instead of values. The Market Share Increase variable is to be modelled as a `uniform' distribution; Market Share as a `triangular' distribution. [It is important to enter commas and decimal points with care otherwise the results can be surprising!]

The main steps are:

2. In B17, change entry for MS Increase to:

=RiskUniform(-0.002,0.008)

3. In cell B18, change entry for MktGrowth to:

=RiskTriang(0.9,1.02,1.08)

As with all spreadsheet Function names, the case is not important. The default settings ensure that these @RISK distribution functions display as the `expected values' (or mean values) of the distributions (0.3% and 100%).

4. File Save As NOVRISK say

5. Choose the Simulation Settings button (with red distribution & blue square) to display a dialog box with the current simulation settings. Notice some of the current defaults e.g. Iterations 100, Simulations 1. In the Sampling page, Standard Recalc part, note the ways of displaying numerical results in distribution cells. `Expected value' means that cells display the mean values in place of the distribution risk functions. This is the default display form.

Try choosing `Monte carlo' and press OK. Instead of mean values, actual sampling values are displayed in distribution cells. Press the recalculation key (F9) a few times to see further `Monte Carlo' results for NPV.

[pic]

6. Before moving on to run the simulation, choose Simulation Settings again and return the Standard Recalc to “Expected Value”.

3. RUNNING YOUR FIRST SIMULATION

The steps are to select the “output cells” (for which results will be collected); to specify the “settings” for the simulation (how many iterations); to run the simulation; then to view the results. Most of these steps are initiated via the @RISK Toolbar (see the Figure 2.1 diagram of toolbar buttons).

3.1 Selecting `Output' Cells

1. Select NPV in cell F13, click the Add Output button (with single red arrow).

Similarly, select IRR in cell F14, click the Add Output button again.

Finally select cell range C9..G9 which hold the (positive) cashflow values. (specify the name “Cashflow” when asked).

2. To view the outputs selected, click on the Display Inputs by Output @RISK button (with a man pointing to a grid) to get the @RISK Model Window displaying Inputs By Outputs.

[pic]

Inputs and outputs are listed on the left of the window. The outputs are the cells for which simulation results will be collected. The Inputs show cells that contain @RISK distribution functions.

3. Click the Show Excel Window button to hide the @RISK Model window and return to the NOVA1 spreadsheet.

3.2 Settings for the Simulation

4. Choose the Simulation Settings button to display a dialog box with the current simulation settings. We have already looked at the current defaults e.g. Iterations 100, Simulations 1. In the Standard Recalc part, we see that numerical results are currently displaying as `Expected values' i.e. that cells display the mean values in place of the distribution functions. [In the simulation, actual sampled values are used but it is not necessary to have them displayed on screen to effect this]. Leave the settings unchanged. We will experiment again with different settings later in the exercise.

5. Choose the Simulate button (with red distribution). On each iteration, @RISK draws new samples from each distribution and recalculates the spreadsheet contents. Values are collected for each selected output range. When the simulation is complete (after 100 iterations), the summary results can be seen in the @RISK Results window.

3.3 Viewing the Results Graphically

At the end of the simulation the @RISK Results Window is displayed. At the left hand side you can see a list of inputs and outputs. At the right hand side you see the Summary Statistics results. These include the minimum, mean and maximum values calculated for each output cell. First, explore the results by displaying them as graphs.

[pic]

Figure 3.1: The @RISK Results Window

1. Right-click on the “NPV” output at the left pane of the Results Window and from the menu select Cumulative, Assending Cumulative – Line. The following graph window is then displayed.

[pic]

The cumulative distribution of NPV outcomes shows the probability of NPV being less than or equal to any given value. For instance you can see that the probability of negative NPV is roughly 20%. The graph also displays the expected NPV (mean), which in this case is $913K. The pane on the right side of the graph displays important statistics about the results such as the mean and the standard deviation.

The red band at the bottom of the graph displays by default a range including 90% of the observations and excluding the top and bottom 5%. You can change the range by typing appropriate values in the entries Left X, Left P, Right X, Right P on the right hand side of the graph. You can also use these entries to calculate the percentiles of the distribution. For example if you type 50% in the Left P entry you will be able to read the value of the median in Left X. (The median is the value that leaves 50% of the observation above and 50% below it). On the other hand, if you type 0 in the Left X entry, you will be able to read the probability of negative NPV in Left P.

1. Right-click on the “IRR” output at the left pane of the Results Window and from the menu select Histogram, Histogram. The following graph window is then displayed.

[pic]

This is a standard histogram of the IRR outcomes. Use the Left X entry on the Stats pane to calculate the probability of IRR being less than 15%. (should be about 20%).

3.4 The Summary Graph

One further graph type is included in @RISK which takes the form of a summary of the histograms obtained for a range of cells. The procedure is as follows:

1. Right-click on one of the cashflow outputs in the list of outputs at the left hand side of the @RISK Results Window. In the pop-up menu select the Summary Graph option to get a `distribution summary graph' similar to that shown below. This graph aggregates the main features of the histograms for each of the five cells C9..G9. The central line joins up the mean cashflow values of results for each cashflow cell. The inner band joins up the values of mean plus/minus 1 standard deviation; and the outer band joins up the 5% and 95% percentiles for each of the five cells. It is also possible to look at the histograms for the individual cells as it was done for the IRR and NPV results.

[pic]

Graphs can be exported to Excel by right-clicking on them and selecting the appropriate options. Similarly simulation statistics can be cut-and-pasted into Excel.

3.5 Saving Simulation Statistics

Then from the @RISK Results window, choose File Save As to save the simulation results. (This causes a file NOVRISK.rsk containing the current simulation results, the graphs and the simulation settings to be saved). [Alternatively, click the Save button on the @RISK toolbar to File Save].

Note that it is also necessary to save the model file NOVRISK. Click the Show Excel Window button to hide the @RISK Model window and return to the spreadsheet and File Save or File Save As to save the .xls model file.

3.6 Deciding on the number of iterations

Determining the required number of iterations is a difficult problem in Monte-Carlo simulation. Typically, you successively increase the number of iterations until the results do not change substantially from one simulation to the next. @RISK has a feature by which it monitors the convergence of results and automatically stops when a user defined convergence criterion is met. This feature can be activated from the Simulation Settings dialog box. In the Iterations section instead of typing the number of iterations, select “auto” from the drop-down list as in the figure below. Then rerun the simulation and explore the results.

[pic]

This concludes the introductory exercise on applying @RISK to Novaduct. This elementary introduction does not cover some of the more advanced features of @RISK. Mention should be made of @RISK's ability to handle relationships between inputs in which correlation plays a significant part e.g. that demand may well be linked to price. Another useful analysis is identifying combinations of input ranges that lead to extremes of performance - referred to as scenario analysis.

Appendix

@RISK Add-in Toolbar

The following icons are shown on the @RISK toolbar in Excel:

|Icon |Function Performed and Command Equivalent |

|[pic] |Open a saved @RISK simulation |

| |Command equivalent: File menu Open command |

|[pic] |Save the current @RISK simulation, including results and graphs |

| |Command equivalent: File menu Save command |

|[pic] |Add or edit probability distributions in the formula in the current cell |

| |Command equivalent: Model menu Define Distributions command |

|[pic] |Add the current selected spreadsheet cell (or range of cells) as a simulation |

| |output |

| |Command equivalent: Model menu Add Output command |

|[pic] |Display current output cell(s) along with all distribution functions entered in |

| |the worksheet in the Outputs and Inputs list |

| |Command equivalent: Model menu List Outputs and Inputs command |

|[pic] |Select cells in Excel containing @RISK distribution functions, output functions |

| |or statistics functions |

| |Command equivalent: Model menu Select @RISK Functions command |

|[pic] |View or change the simulation settings, including # of iterations, # of |

| |simulations, sampling type, standard recalc method, executed macros and other |

| |settings |

| |Command equivalent: Simulation menu Settings command |

|[pic] |Display reporting options |

| |Command equivalent: Results menu Report Settings command |

|[pic] |Simulate the current worksheet(s) |

| |Command equivalent: Simulation menu Start command |

|[pic] |Display the Model window with outputs, inputs and fitting data |

| |Command equivalent: Model menu Show Model Window command |

|[pic] |Display the results of the most recent @RISK simulation in the Results window |

| |Command equivalent: Results menu Show Results Window icon |

-----------------------

[1] This tutorial requires @RISK version 4 or higher. You also need to get hold of the tutorial file NOVA1.xls

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download