9 Replies Latest reply: Sep 1, 2016 7:37 PM by Olivier d'Assier

# Quantitative Risk Analysis - Monte Carlo Simulation in Qlik Sense

What is it?

This demonstration is a quantitative risk analysis of a single project that demonstrates the speed of the Qlik engine and the ability to not only interact with the model but also drill through to the detailed model output.

The model in this case is the "Total Profit Equation" and an analysis of the range of possible outcomes provided the model assumptions.

Total Profit = (Price * Quantity Sold) - (Variable Costs * Quantity Sold) - Fixed Costs

This is a simple example for demonstration purposes.  The technique can be ported into virtually any model for many other use cases.  Here are some examples I captured from a document I found on the web (written by Natalie Humphreys, Associate Head of Actuarial Program at University of Texas):

1. General Motors (GM) uses simulation to estimate both the average return and the riskiness of new products.
2. Wall Street uses to price complex financial derivatives and also determining Value at Risk (VaR) of an investment portfolio
3. Procter & Gamble models an optimally hedged foreign exchange risk
4. Financial Planners determine an optimal investment strategy for their clients' retirement

All interesting (and possible) and this particular model is simply a basic template to head down the path.

How to get started:

Step 1: Install Qlik Sense Desktop and download the .qvf file to qlik/sense/apps directory

Step 2: Go to Branch.Qlik.Com and install the following Qlik Sense extensions:

Step 3: Make adjustments to the inputs

Step 4: enter the number of Simulations you want to run/analyze

Step 5: Click on the Reload button

I find it best to interact with this app through my browser, to navigate through the browser, you type in the following url: http://localhost:4848/hub/my/work (make sure Qlik Sense Desktop is running)

• Thanks to Brian ( @bbu for building the baseline Monte Carlo Simulation in Qlik Sense.  I have enhanced his great work and am posting here for the broader community.
• Thanks to hic for his description on how to build a histogram in Qlik.  This was a great improvement and here is his post that i used (Note that it worked slightly different in Qlik Sense but similar/same result: Recipe for a Histogram
• A Normal Distribution is used for every variable.  I kept it this way to keep it simple but hic also talks about distributions in Qlik here: Monte Carlo Methods
• The current file is set at 1,000 simulations but feel free to move it to 100,000 to see how long it takes to run a larger model set.  What about 1 million simulations?
• At Qonnections 2016 this year, Deloitte presented a model of a gold mine that forecasted a full balance sheet, income statement and cash flow of a gold mine with every variable being simulated.  Further, it uses Qlik's powerful Time Value of Money functions to calculate the Net Present Value of Free Cash Flow generated by the model.

Enjoy,

Paul V.S.

• ###### Re: Quantitative Risk Analysis - Monte Carlo Simulation in Qlik Sense

Hi Paul,

Great post, thanks for taking the time to share.  I got all the steps right until 3 - (new to Qlik Sense and could not tell how to do that.  My app loads but all of the boxes on the left have "Invalid Visualization" so I am unable to play with this example.  Can you advise on how to trouble shoot this?

Much obliged.

Olivier

• ###### Re: Quantitative Risk Analysis - Monte Carlo Simulation in Qlik Sense

Hello again.  I fixed it.  Seems I was missing an extension.  All good now.

• ###### Re: Quantitative Risk Analysis - Monte Carlo Simulation in Qlik Sense

Hi Olivier, I can't make it work. I think is the variable extension that doesn't work in my case.

I've downloaded the extension and put it in the right place, with the same name as the zip, called "variable".

Am I doing something wrong?

Regards, Marcel.

• ###### Re: Quantitative Risk Analysis - Monte Carlo Simulation in Qlik Sense

Hi Marcel,

Apologies, I was on vacation and just saw your email on my return.

Are you getting the same error message I was (i.e. “Invalid Visualization”) where the graph boxes are not showing?  I found you also need to have the Qlik-Sense-D3-Visualization-Library-master extension for the graphs to work.  Do you also have the “variable.css”, “Variable.js”, and “Variable.qext” files in the Extensions folder?

Cheers,

Olivier

• ###### Re: Quantitative Risk Analysis - Monte Carlo Simulation in Qlik Sense

Hello Marcel/All:

I have corrected the Input Variable extension in the original post, please try it and I apologize for any confusion.

PVS

• ###### Re: Quantitative Risk Analysis - Monte Carlo Simulation in Qlik Sense

Hi Paul,

Thank you for this revision.  Works perfectly on my side now.

Cheers,

Olivier

• ###### Re: Quantitative Risk Analysis - Monte Carlo Simulation in Qlik Sense

HI Paul,

I'd like to use this template to build a new simulation.  Excuse the dumb question but what should the data file for this app look like (or is there a need for one at all since the model can be build within the app)?  When I go to Edit the Total Profit (result) widget (i.e. the box with the "597.39" value), and click on "fx" for the expression, the click on the "filter by table" drop down menu, I see the name of a table "Monte Carlo", with all of the associated fields below.  But when I click on "add data" and look for the table under "Connections", I see the "Monte Carlo Simulation" file name for the path and the associated .qfv file but this isn't a data table (more like a script).  Apologies for he beginner question, but how do I change the data used for this simulation?  Do I have to create a new excel sheet with the same headers or simply use the last sheet in the app to change the variables (names and values)?  And how do I change the model (i.e. the Total Profit formula) to something else?  Can you share the data table in excel so I have an example to work from? thanks,

Olivier

• ###### Re: Quantitative Risk Analysis - Monte Carlo Simulation in Qlik Sense

hi Olivier,

You can use a STORE statement on the Monte Carlo table to extract the structure. However, that won't be required to create a completely new / different simulation. Your business logic should drive the data structure / model that you want to use in your simulation.

• ###### Re: Quantitative Risk Analysis - Monte Carlo Simulation in Qlik Sense

Got it, thanks.

Olivie