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

    Quantitative Risk Analysis - Monte Carlo Simulation in Qlik Sense

    Paul Van Siclen

      What is it?

       

      2016-05-20 16_19_50-Monte Carlo Simulation - Quantitative Risk Analysis - Single Project Example _ S.jpg

      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)

       

      Some add'l notes:

       

      • Thanks to Brian (https://community.qlik.com/people/bbu) @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.