Skip to main content

Monte-Carlo Simulation with Risk Kit

By Uwe Wehrspohn and Sergey Zhilyakov

[Download]

 

Contents

1.  The need for rapid-prototyping of Monte-Carlo simulations

2.  Presentation of Risk Kit

3.  A profit and loss simulation

    3.1.  The deterministic starting point

    3.2.  The sources of randomness

    3.3.  Defining the model outputs

    3.4.  Configure the simulation

    3.5.  Run the simulation

4.  Plots

    4.1.  Statistics functions

5.  Sensitivity analysis

    5.1.  Sensitivity analysis via Risk Kit cell-function

6.  Conclusion

7.  Our Products

 

1.The need for rapid-prototyping of Monte-Carlo simulations

To top

Monte-Carlo simulations play an increasing role in finance, social sciences and risk management. Monte-Carlo simulations are a generic technique to estimate the distributions of arbitrary target quantities in complex environments. Among those are profit distributions, shareholder value distributions, portfolio loss distributions and generally the distribution of any target quantity that is subject to hazard.

However, to use Monte-Carlo methods in practice is often subject to severe difficulties such as the need to draw random variates, to program a model and to evaluate the simulation results. This requires the handling of advanced mathematical methods as well as of modern computer technology.

Risk Kit is a tool for the rapid-prototyping of risk analyses and Monte-Carlo simulations. It provides the components of quantitative risk management and allows linking them in a simple and intuitive way to complete models and risk analyses. Risk Kit allows users to efficiently access quantitative risk analyses and Monte-Carlo simulations. It accelerates the development time many times over as compared to conventional approaches.

All Risk Kit functions can be integrated in spreadsheet analyses as Microsoft Excel cell-functions. Moreover, Risk Kit functions can be called from VBA or the .NET programming language. This way Risk Kit can be integrated in a production environment.


 

This document describes how to perform Monte-Carlo simulations and risk analyses with Risk Kit1.

 

2.Presentation of Risk Kit

To top

Risk Kit is an add-in for Microsoft Excel. After installation it is available in the ‘Risk Kit’ toolbar in Excel (see Figure 1).

Figure 1 - The Risk Kit toolbar in Microsoft Excel

 

Moreover, the cell context menu now includes the ‘Risk Kit’ sub-menu that duplicates the Risk Kit toolbar (see Figure 2).

Figure 2 - Cell context menu

 

Table 1 describes all buttons from the Risk Kit toolbar.

Table 1 - Description of the Risk Kit toolbar

Button

Description

Opens the Function dialog that allows to choose Risk Kit cell-functions.

Opens the gallery of univariate distributions that allows to select a distribution to draw random variates or calculate the density, mass function, distribution function or inverse cumulative distribution function.

Opens the list of functions for stochastic processes.

Opens the list of statistic functions.

Opens the list of general functions, for example to create plots.

Opens additional functions grouped by multivariate distributions, rating methods and fixed income.

Opens recently used functions.

Opens the Calibration dialog that allows to calibrate univariate and multivariate distributions to empirical data.

Marks a cell as Input-cell.

Marks a cell as Output-cell.

Marks a cell as Plot-cell.

Marks a cell as Process-cell.

Opens the cell manager to show Risk Kit-function cells in open workbooks.

Opens the configuration dialog that allows the setting of simulation parameters, changing of the user interface language and others.

Starts the simulation.

Makes a single step of the simulation.

Resumes the simulation. The button is available if the simulation is paused.

Aborts the simulation. The button is available if the simulation is paused.

Opens the Sensitivity Analysis Form that allows making the sensitivity analysis.

The button represents a drop-down list that consists of six items:

  • ‘Arrange chart windows’ – Arranges the windows so that all charts are visible at once;

  • ‘Cascade chart windows’ – Cascades chart windows one behind the other;

  • ‘Close chart windows’ – Closes all chart windows;

  • ‘Delete charts from active worksheet’ – Deletes embedded charts from the active worksheet;

  • ‘Delete charts from active workbook’ – Deletes embedded charts from the active workbook;

  • ‘Delete charts from all workbooks’ – Deletes embedded charts from all open workbooks.

 

Contains a variety of example workbooks to quickly get started with Risk Kit.

Opens the Risk Kit add-in tools to open the online help, change the system language between English, French and German, create key-board shortcuts, send feedback to the Risk Kit developers, recommend Risk Kit to new users, solve problems and get information on the Risk Kit version used.

 

To do a Monte-Carlo simulation with Risk Kit of a hitherto deterministic model, one can proceed in four steps. Firstly, one or more inputs of the model have to be identified that become random variables in the simulation. Their previously deterministic value will be replaced by random variates that change their value in each simulation run. Through these variables randomness enters the model.

Secondly, the model outputs or target quantities need to be defined. They are calculated from the random inputs and maybe other deterministic inputs in the context of the model.

Thirdly, a number of simulation runs needs to be chosen so that, fourthly, the simulation can be started.


 

3.A profit and loss simulation

To top

3.1.The deterministic starting point

To give an example of how to turn a standard deterministic model into a Monte-Carlo simulation, we consider a basic profit and loss model.

Figure 3 – Deterministic profit and loss calculation

 

To calculate the profit and loss, we simply start with the company’s turnover (C4) and deduct all costs. In a first step, these are material (C5) and personnel costs (C6) and write-offs (C7) that lead to the intermediate result ‘Earnings before interests and taxes’ (EBIT) (C8). Further on the interest income (C9) and the extraordinary income (C10) are deducted from this figure to give the ‘Earnings before taxes’ (EBT) (C11) or the gross profit and loss. All formulas are simple sums and differences.

Since the actual material costs are largely variable and depend on the level of turnover, we model them as a relative quantity ‘in % of turnover’. Therefore, the total material costs in C5 = B5 * C4.


 

3.2.The sources of randomness

To top

After having defined the deterministic model, the first step to turn it into a Monte-Carlo simulation is to identify the sources of randomness in the model.

We suppose that the turnover, the material costs, the personnel costs and the extraordinary income are subject to risks (column B). The aim is to analyze their impact on total EBIT and EBT (column C). Note that risks can also occur as opportunities (e.g. random cost reductions) leading to an increase in income.

While a turnover of 1,000 TEUR was supposed in the deterministic model, we know by experience that this quantity is not stable, but that it fluctuates in practice between 900 and 1050 TEUR with 1000 TEUR being the likeliest amount. Thus, we choose the triangular distribution as an intuitive model for the fluctuation of the turnover.

Figure 4 - Modelling fluctuations of the turnover

To enter a triangular random variate into the model, we mark cell B4 and click the ‘Insert Function’-button on the Risk Kit toolbar.

Then this dialog appears:

Figure 5 - Functions dialog

Risk Kit provides more than 430 cell-functions in 7 categories:

  1. Univariate - Univariate probability distributions;

  2. Multivariate - Multivariate probability distributions. This list contains functions that supply random variates from standard multivariate distributions as well as from copula functions;

  3. Process - Trends and random paths and in some cases also point wise percentiles of stochastic processes;

  4. Statistics - Functions for calculating statistics of simulated distributions such as mean, variance, percentiles etc.;

  5. General - Functions to plot arbitrary probability distributions and stochastic processes, a function for dynamic sorting, functions for portfolio optimisation and calculation of the efficient frontier, and functions implementing delta-gamma engines;

  6. FixedIncome - Functionalities related to interest rate products including the handling of interest rate and spread curves, the calculation of forward rates, the calculation of cash flows for interest bearing products such as loans and bonds, the valuation of fixed and floating rate bonds and the calculation of default probabilities implied in bond spreads;

  7. Rating - Rating functions for small and medium sized companies in Germany, Austriaand the US.

Since we want to enter a triangular random variate, we choose the category ‘Univariate’ and select the item Triangular from the list.

Having confirmed with ‘OK’, six functions are displayed:

 

Figure 6 - Univariate distribution functions

  1. A joined function where the function type is chosen through a parameter.

  2. The probability density function (PDF) in case of continuous distributions or the probability mass function (PMF) in case of discrete distributions.

  3. The cumulative distribution function (CDF) and

  4. The inverse cumulative distribution function (ICDF) and

  5. Single random variate from the respective distribution and

  6. Array of random variates from the respective distribution. These functions are used in the ‘High speed’ simulation mode.

     

These functions are available for each univariate distribution.

Click ‘Single random variate’ to get a random number of the triangular distribution.

An alternative and often more intuitive way to select a distribution is to click the button ‘Univariate’ on the toolbar and open the distribution gallery.

Figure 7 - Distribution gallery

The gallery shows a preview icon for each distribution that illustrates a typical shape of the distribution’s density or mass function. The distributions are also ordered by name.

Hover the mouse cursor over a preview icon to get more information on the distribution. Also, a keyboard-shortcut to the distribution is displayed if one has been defined2.

Figure 8 - Distribution and shortcut information in the gallery

Click on a distribution to select it from the gallery and choose ‘Single random variate’ as described above.

You can now enter the distribution parameters.

Figure 9 – Function arguments of the triangular distribution

There are two ways to do so. You can either store the parameters in the Excel sheet and reference the respective cells. This is displayed in figure 9 where the parameter ‘a’ contains a reference to cell B17 with the most pessimistic turnover supposed, ‘b’ contains a reference to C17 with the likeliest and ‘c’ to D17, the most optimistic turnover supposed.

Alternatively, you could enter the parameters directly.

Figure 10 – Function arguments

The dialog allows you to specify an output name (for univariate distributions only). The output name will appear as legend for this distribution in charts and statistics outputs.

The dialog also offers you further information on the chosen function and its parameters. Firstly, you get a brief summary of what the function does. In this case it produces ‘random variates from a triangular distribution with parameters a, b and c’.

When you enter a particular parameter, information on its range of definition is displayed. In our case the parameter c is required to be greater or equal to b.

Below the chart you see the definition of the function that is actually plotted.

Finally, note the link to the online help in the lower left corner of the dialog and the function shortcut at the bottom.

By clicking on ‘Insert’, the function definition is entered into cell B4 and appears in Excel’s formula bar. Note that the total turnover in C4 now reflects the risk. Also, the material costs are already affected albeit their percentage is still constant at 50% since they also depend on the turnover.

You can manually redraw random variates by pressing <F9>.

Figure 11 – Cell containing a Risk Kit function

 

We proceed similarly for the other risks. For the relative deviation from the planned material costs, we assume a normal distribution with expectation mu = 50% and standard deviation sigma = 3%.

Figure 12 - Model assumption for the material costs

To enter the formula into cell B5, we place the cursor on this cell and open the Risk Kit function dialog. In the field ‘Search for:’ we enter ‘normal’ and confirm with ‘Search’ or press Enter. The result delivers all Risk Kit functions related to anything ‘normal’. If we mark a function, we get a brief explanation of its meaning. We, thus, find that the function ‘NormalD’ is what we are looking for, the normal distribution.

Figure 13 - The function search dialog

Confirming with ‘OK’ and choosing ‘Single random variate’ leads us to the form where we can enter the references to B21 for mu and to C21 for sigma. ‘Insert’ enters the function definition into B5. Note that you can abbreviate this process by writing or copying the function definition into the cell right away. The function dialog is just a supporting tool.

Figure 14 - Function arguments for the normal distribution

The material costs in absolute terms are then the simulated costs in % of the turnover times the simulated turnover.

Figure 15 - Linking the simulated material costs to the model

The personnel costs we model similar to the material costs. We assume a normal distribution with expectation 325 and standard deviation 20. However, we believe that personnel costs cannot be reduced easily below a certain level. We, thus, assume that they are truncated on the left-hand side at 300.

Figure 16 - Model assumption for the personnel costs

To turn this assumption into a function call, we open the function dialog and link the parameters mu, sigma and TruncLow to cells B25, C25 and D25, respectively.

Figure 17 - Normal distribution with truncation

The truncation of the distribution at 300 makes sure that no random variates are drawn below this level while on the right-hand side the distribution remains unchanged.

‘Insert’ enters the function.

 

Finally, the extraordinary income of our company is affected by credit risks. If counterparties default before the bills are paid, the company loses money.

Figure 18 - Model assumption for extraordinary income

We assume that the number of customer defaults in the fiscal year is Poisson-distributed with an expected number of 5 defaults. In case a default occurs, we further assume that the size of each loss is PERT-distributed with a minimum loss of 0, a maximum loss of 10 and the likeliest amount being 3.

Note that we have two sources of uncertainty here. Firstly, the number of defaults is uncertain each of which, secondly, causing a loss of an uncertain amount. This is a sum of a random number of summands of a random size.

We use the function dialog to find out that the call for the Poisson variate is

Poisson_variate(5)

and for the PERT variate

Pert_variate(0, 3, 5).

However, simple calls of these functions do not take account of the fact that the size of the losses in case of a counterparty default (the size of the summands) in this calculation is random. It is, therefore, not sufficient to just draw a Poisson and a PERT variate and multiply both because this would imply that all losses have exactly the same size.

To model a random number of summands of random size, Risk Kit supplies the ‘Compound’ function. Open the ‘Univariate’ function dialog, choose ‘Compound’ from the gallery and enter the above calls as arguments.

Figure 19 - Compound distribution in distribution gallery3

The second parameter for the loss function needs to be entered in quotation marks to prevent Excel from executing this function right away. The three parameters of the PERT distribution are then entered as Arg1 to Arg3.

Arg4 to Arg8 can remain empty. They are placeholders for other loss distributions with more than 3 parameters.

Figure 20 - Call of the compound function

Confirming with ‘Insert’ we get the now fully specified model.

Figure 21 - The fully specified model


 

3.3.Defining the model outputs

To top

Although the model is specified, we cannot run the simulation because the model outputs are still not defined.

As in the deterministic model, we are particularly interested in the behaviour of EBIT and EBT under these influences of risk. We, thus, choose these quantities as model outputs. To do so, we mark cell C8 and click on ‘Output’ on the Risk Kit toolbar.

Figure 22 - Defining output cells

Cell C8 is now orange to visibly mark it as output cell. Also you can give names to output cells adding the function ‘+OutputName(“My Name”)’. The name you assigned is taken in plots or other outputs as legend for the output cell.

We do similarly for the EBT in C11.

Figure 23 - The OutputName function

 

3.4.Configure the simulation

To top

You are now ready to run the simulation. Open the ‘Config’ dialog by clicking the toolbar icon or using the shortcut to set the number of simulation runs. 5000 runs are set by default. The screen is refreshed every 500 runs.

Figure 24 - Opening the Config dialog

You can activate the feature ‘Dump standard statistics’ to get summary statistics of the simulated distributions after the simulation has finished on a newly created worksheet ‘Output’. If you choose ‘Dump all simulation results’, results for each simulation run are dumped on a second worksheet. The feature ‘Create plot’ plots the distributions of the output cells if plots are defined (see below). The ‘Online graphics’ featureexecutes the Plot-cells at each screen-refresh so that you can see the output distributions develop. If the feature ‘Plot all output cells’ is activated, all output cells are plotted automatically. If you activate the feature ‘Reset seed‘, the seed of the random number generator is reset to its original value leading to sequences of identical random numbers in different simulations. If the feature ‘Add function definition as comment’ activated, function definition is added to a cell as a comment every time you define a function in the Risk Kit function wizard.

 

Figure 25 - Config dialog

Choose the context of the simulation from the drop-down list in the Config dialog. If your model is one Excel worksheet, you can choose the ‘Active sheet’ simulation mode. In this case, the worksheet with the model needs to be selected before the simulation is started. This option is set by default. Risk Kit also provides such options as ‘Active workbook’ and ‘All open workbooks’ which are used if the model is located on several worksheets in a workbook or if it is distributed over several workbooks, respectively. We select ‘Active workbook’ because we will later add result statistics on further worksheets.

Risk Kit allows implementing custom macros and executing them during a simulation. This approach allows updating values during a simulation, storing intermediate results and other. Specify which macros you want to execute in the Config dialog. Note the macros have to be in the active workbook and carry the indicated name:

  1. Execute macro ‘BeforeSimulation’ - the macro with the name ‘BeforeSimulation’ is executed before the simulation starts;

  2. Execute macro ‘BeforeEachRun’ - the specified macro is executed before each simulation run;

  3. Execute macro ‘AfterEachRun’ - the specified macro is executed after each simulation run;

  4. Execute macro ‘AfterSimulation’ - the macro with the name ‘AfterSimulation’ is executed at the end of the simulation;

  5. Execute macro ‘AfterOutput’ - the macro with the name ‘AfterOutput’ is executed after the output of statistics and simulation results.


 

Activate the feature ‘Dump standard statistics’ and confirm with ‘Save’ to close the Config dialog.


 

3.5.Run the simulation

To top

 

Figure 26 - Start the simulation

Now that the simulation is configured, you can finally start it. To do so, click on ‘Simulate’ on the toolbar or use the respective shortcut.

During the simulation, the number of runs already performed is displayed in Excel’s status bar in the lower left corner of the screen.

Figure 27 - Status information

You can abort the simulation by holding <CTRL><T>.

If you pause the simulation with <PAUSE>, you can restart or terminate it with the respective buttons on the toolbar.

Figure 28 – Aborting the simulation

Risk Kit also allows making a single step of the simulation by clicking the ‘Single step’ button on the toolbar. After a single step of the simulation has been finished, the simulation is paused.

After the simulation a new worksheet ‘Output’ is appended to the active workbook. This sheet contains one column with standard statistics for each output cell in the simulation. As header the output name is stated or the cell address, if no name was defined.

Figure 29 - Output statistics

Similarly, you can dump out all random variates generated in the simulation for the output cells, if you mark the ’Dump all simulation results’ check box in the Config dialog.

Figure 30 - All simulation results


 

4.Plots

To top

To get an impression of the shape of the EBIT and EBT distributions and also of the risk factors that go into the model, you can add graphs to the model by displaying charts in cells or by inserting a graph function in the worksheet.

The easiest way of visualisation is to click on 'Configuration' on the Risk Kit Toolbar and activate the check box 'Show in-cell histograms'. After a simulation, a histogram of the respective distribution is then displayed in each output cell without having to enter further functions. You can mark every cell whose distribution you want to see as an output cell and get the visualisation after a new simulation.

In addition, activate the box 'Online graphics' to see the development of the distribution during the simulation.

Figure 31 - Display histograms in all output cells

If you only want to display graphs for individual outputs, you can do this with the function 'PlotInCell'. For this it is not necessary to activate the checkbox mentioned above. Simply add the function 'PlotInCell' to the formula in a cell in the same way as the function 'OutputName'. The function returns the value 0, so it does not change the result of the formula. The function references the output cell whose distribution is to be plotted as the only mandatory parameter. The function can, but does not have to be in an output cell itself.

Figure 32 - The 'PlotInCell' function

The function has two more parameters.

 

Figure 33 - Parameters of the 'PlotInCell' function

They allow to specify the exact number of intervals of the plot (default value is 20) and the type of the graph (histogram, distribution function or reverse distribution function. Default value is "HISTOGRAM". Note the double-quotes).

Figure 34 - Various outputs of the 'PlotInCell' function

If necessary, set the font colour for the cells in which a distribution is shown to the background colour, in the example above to white, to make the 0.0 value the function returns invisible.

The second possibility of visualisation is provided by the function 'Plot'. This function computes and draws a histogram of each specified simulated distribution, their CDF, ICDF and reverse CDF functions, it estimates their kernel-density and draws a box-plot.

The Plot function takes many optional arguments, such as legends, the title, and the opportunity to embed a plot into a worksheet. Please refer to the online help section 7.3 for details. You also find the plot function in the Function dialog in category ‘General’.

Figure 35 - General functions

The dialog leads you through the function parameters.

Figure 36 - Definition of the Plot function

The only non-optional parameter of the Plot function is a reference to an output cell of the simulation. Thus, you can very easily create plots by writing, e.g., ‘+Plot(C8)’ into an empty cell4.

Figure 37 - The Plot function

Note that two requirements need to be met for a plot function to be executed. Firstly, the cell containing the function needs to be marked as ‘plot’ cell over the toolbar. The cell then turns dark orange.

Figure 38 - Mark a cell as 'plot' cell

Secondly, in the Config dialog the check box ‘Create plots’ has to be checked.

Figure 39 - Config dialog

After a restart of the simulation a plot is created.

Figure 40 - Plot window

The output name ‘EBIT’ of the output cell is taken as legend for the distribution. Note that by right clicking on the chart, the legends etc. you have an extensive context menu that allows you to customize the picture, change colors, fonts, scaling, style and many others.

Figure 41 – Chart context menu

An axis properties dialog allows to set the number of decimal digits, separation between thousands, abbreviation, the number of labels etc.

Figure 42 - Left Y Axis Properties

 

You can switch between histogram, CDF, ICDF, Reverse CDF, density and box-whisker with <CTRL>-D or by clicking on the respective tabs or via the context menu (see Figure 41).

Use the controls on the right-hand side of the plot-window to change the appearance of the plot:

  • You can change the input range, i.e. the minimum and maximum percentile between which the distribution is plotted. If several distributions are displayed in one plot, the interval plotted ranges from the smallest minimum to the largest maximum percentile of theses distributions.

  • Activate the check-boxes to display the ruler and grid of the x- and y-axis respectively.

  • Choose the discretization of the x-axis (scale).

  • Activate the check-boxes to display the legend of the x- and y-axis.

  • Copy the plot to the clipboard in order to be able to insert it into other documents.

  • Activate check-box to show the title.

  • Activate check-box to show the legend.

On the right-hand side of the window you also find the ‘Statistics’ tab page with standard statistics. Double clicking on a row shows the statistics in the picture (see Figure 43). Double clicking on the header of the column removes the respective distribution from the graph. This is particularly useful if you have plotted several distributions into the same graph.

Figure 43 – Statistics in the chart

Risk Kit implements three different options for scaling the y-axis:

  1. Density function - the vertical axis is scaled so that the area of the histogram equals 1;

  2. Relative frequency - the vertical axis is scaled to show the relative frequency of the realizations within a bar of the histogram;

  3. Absolute frequency - the vertical axis is scaled to show the number of simulation results within a bar of the histogram;

Open the context menu of the left y-axis to choose the scaling option.

Figure 44 - Y-Axis scaling

You can copy this plot to the clipboard via the chart context menu and use it in your presentations.

If you want to embed the plot into a worksheet after a simulation, specify such parameters as outputChartCell and outputChartType of the Plot function (see Figure 45). The first parameter is a cell where the plot will be embedded. The second one states how the distribution will be plotted. In our case the distribution will be plotted as a histogram. You can also specify the width and height of the chart in pixels.

Figure 45 – Definition of the Plot function

Click ‘Update’ to enter the function into the cell.

After the simulation the plot is embedded into the worksheet.

Figure 46 - Embedded chart

 

Risk Kit replaces the embedded charts with new ones after re-simulation automatically.

Risk Kit also allows to delete all embedded charts and to close plot windows by clicking the respective menu item of the ‘Close chart windows’ drop-down button on the toolbar.

Figure 47 - Drop down menu of the 'Chart windows' button

Note that Risk Kit only deletes embedded charts which were generated by Risk Kit itself. All other charts remain untouched.

 

 

4.1.Statistics functions

To top

Risk Kit provides cell-functions which allow to obtain result statistics in worksheets so that you can create layouted reports. These functions can be found in the category ‘Statistics’ of the ‘Functions’ dialog.

Figure 48 - Statistics functions

 

This category contains such functions as:

  • Maximum – calculates the maximum of the simulated values of an output cell;

  • Mean - calculates the mean of the simulated values of an output cell;

  • MedianSim – calculates the median of the simulated values of an output cell;

  • Minimum – calculates the minimum of the simulated values of an output cell;

  • Range5-95 – calculates the span from 5%- to 95%-percentile of the simulated values of an output cell;

  • Variance - calculates the variance of the simulated values of an output cell;

  • Perc – calculates a percentile, i.e. the value at risk of an output cell at the stated confidence level;

 

Go to an empty cell and choose the Mean function from the category ‘Statistics’. The ‘Function arguments’ dialog appears:

Figure 49 - Definition of the Mean function

The parameter OutputCell refers to the cell C8 that defines the model output. Press ‘Insert’ to enter the function into the cell.

Figure 50 - Mean function

Once the Mean function has been defined, it returns ‘Only defined after simulation’. The reason is that the distribution represented by the output cell is only known after the simulation has been performed.

You can also place result statistics on a different worksheet than the model to create reports which comprise simulation results. The report may contain statistics and embedded charts.

Note that you can define new risk or return measures by combining results with other quantities such as RAROC is risk adjusted return on capital or the mean EBT (under risk) on equity.

Figure 51 - Result report

5.Sensitivity analysis

To top

Sometimes it is of interest to see which random model inputs or intermediate results have the strongest impact on model outputs. To do a sensitivity analysis, mark all cells whose impact you want to consider as Input cells5. Select the cells with the values Turnover (B4), Material costs (B5) and Personnel costs (B6) and Extraordinary income (B10) and click the ‘Input’ button on the Risk Kit toolbar (see Figure 52).

Figure 52 – Defining input cells

These cells are now green to visibly mark them as input cells. Complementary, you need to mark all cells whose dependence on the input cells you want to analyse as output cells. We will analyse the EBIT and EBT which were marked as output cells before.

Note that input cells need not necessarily contain random variates that are directly redrawn during each simulation run, but may also represent intermediate simulation results that may depend on other cells.

Once the inputs were defined, click the ‘Sensitivities’ button on the Risk Kit toolbar.

Figure 53 - Opening the Sensitivities window

The following dialog appears:

Figure 54 - Sensitivity Analysis Form

 

Firstly, choose all input and output cells you want to consider from the lists. Secondly, specify the sensitivity type, for instance, as ‘Linear correlations’. The sensitivities will then be calculated as linear correlations, i.e. by the Pearson correlation coefficient. Thirdly and finally, click the ‘Analysis’ button.

Once the data has been simulated, Risk Kit displays the result in the following dialog:

Figure 55 - Sensitivity Analysis Result Form

 

Copy the results to the clipboard by clicking the button ‘Copy to clipboard’ and insert the copied results into a worksheet by pressing <CTRL><V>.

Figure 56 - Sensitivity Analysis results in a worksheet

 

The sensitivities are plotted in an overview chart and in individual charts for each output cell. You can switch between charts by clicking the respective tab page.

You can see from the chart that the material costs in our example have the strongest impact on EBT. In particular, an increase in material costs leads to a decrease in EBT. This intuitive fact is reflected by the negative sign of the sensitivity.

Figure 57 - Sensitivity Analysis Result Form

 

 

5.1.Sensitivity analysis via Risk Kit cell-function

To top

Risk Kit also provides the cell-function Sensitivity that allows making the same sensitivity analyses as with the sensitivity dialog, but embed the results into a worksheet and run it together with a simulation. This function can be found in the category ‘Statistics’ of the ‘Functions’ dialog (see Figure 58).

 

Figure 58 - Sensitivity function

 

Activate an empty cell and choose the Sensitivity function from the ‘Functions’ dialog. The dialog in Figure 59 appears:

Figure 59 - Definition of the Sensitivity function

 

The parameter SensiType states the sensitivities will be calculated as linear correlations. The parameter InputCells refers to the cells B4, B5, B6 and B10 on the worksheet ‘Model’ which represent the model input. The parameter OutputCell refers to the cell C11 on sheet ‘Model’ which represents the model output (the EBT in our case).

Specify the parameter CreateChart if you want to embed sensitivity chart into a worksheet after a simulation. The parameter ResultCell refers to a cell where the chart will be embedded (cell A51 on the worksheet ‘Sensis’).

Since the sensitivity function returns an entire table as a result instead of a s single number, it is an array function in Excel. To enter it as such, choose ‘Insert as array formula’ from the drop-down list6.

Figure 60 – Defining the Sensitivity function

 

The formula appears in curly brackets in the formula bar (see Figure 60).

Now that the Sensitivity function has been completely defined, click the ‘Simulate’ button on the Risk Kit toolbar to start a simulation. Sensitivity results will be displayed after a simulation. You can format the results as any Excel cell.

Figure 61 - Sensitivity analysis results

 

 

6.Conclusion

To top

Monte-Carlo simulations offer a great flexibility to the creation and solution of almost arbitrarily complex risk models. Risk Kit supports the modeling and analysis process and greatly reduces the time and cost necessary to effectively use Monte-Carlo simulations. Risk Kit is easy to use and integrates into your habitual workspace.

Beyond the features we have shown in this example application, Risk Kit supplies one- and multi-dimensional stochastic processes, multivariate distributions and copulas.

Risk Kit is available with a single desk license (Professional) and with a network floating license (Enterprise). A free renewable license for a single desk version with reduced features (Light), but for commercial use can be obtained online at license.wehrspohn.info.

Please contact us for additional information.

7.Our Products

To top

The Risk Kit Suite is integrated into Excel and adds all the functions of modern risk management to the risk manager's workplace.

 

Risk Kit

  • Monte Carlo simulation with all practice-relevant one- and multi-dimensional distributions

  • Simulation of developments

  • Visualisation of risks

  • reporting

  • Calibration

Risk Kit R

  • Statistical analyses with R

Risk Kit Data

  • Online market data link to the European Central Bank, Federal Reserve Economic Data, the World Bank, Eurostat and Marketstack

  • Exchange rates, yield curves, commodity prices, share prices, economic and social statistics

 

The Risk Kit Suite is available in single-user and enterprise licences.

 

The Enterprise Risk Evaluator is a central platform for the enterprise-wide risk management process. It offers

  • Qualitative and quantitative risk capture and assessment.

  • Risk aggregation, risk bearing capacity, risk analysis

  • Integration with planning

  • User-definable reporting

  • Measures controlling and internal control system

  • Incident and claims management

 

The Enterprise Risk Evaluator can be combined with the Risk Kit Suite.

 

The Market Risk Evaluator is a market risk analysis system for banks. It contains

  • Market data import and consolidation

  • Cash flow calculations

  • Sensitivity calculations

  • Scenario analyses

  • Monte-Carlo simulation with various models

  • Portfolio and market-factor backtesting

 

The Credit Risk Evaluator is a tool for credit portfolio analysis also for banks. It offers

  • Several portfolio models including CreditMetrics and CreditRisk+

  • Single and multi-period models

  • Risk and return analysis

  • Country risk

  • Drill-down to any segmentation from individual transactions to the bank as a whole

 

In addition to the products, we offer consulting services, in particular on

  • Model design

  • Risk management methodology

  • Design of business processes

  • Risk analyses

  • Integration of our systems

  • Development of customer systems

  • Training courses

 

Please contact us if you would like more information or a web presentation.


1 You can download the example model from the login section. You can choose user name and password yourself.

2 To add or delete shortcuts click ‘Add-in tools – Shortcuts’ on the toolbar.

3 Note the second version of the compound distribution that also includes insurance thresholds.

4 You can plot several distributions at the same time by putting the references into brackets, e.g. ‘+Plot((C8,C11))’ to make it clear to Excel that (C8,C11) are the value of the first parameter of the plot function.

5 You can mark the same cell as an input cell and as an output cell at the same time. In particular, you can also use the function 'PlotInCell' on cells that are marked as input cells.

6 Note that you can also enter an array formula manually in Excel by marking the entire result range, clicking in the formula bar and pressing <CTRL><SHIFT><ENTER>.