How to do a Monte Carlo simulation with QlikView

Over my summer holidays I read three books that I can recommend to everybody working with uncertainty and risk in the broadest sense of the definition. The books are the following:

1. “Flaws of Averages” by Sam Savage
2. “The Failure of Risk Management and how to fix it” by Douglas W. Hubbard
3. “How to Measure Anything” by Douglas W. Hubbard

Following reading those books I started a discussion in a LinkedIn Group about implementing some of the lessons of the above mentioned book with the help of the business intelligence software I am using at work: QlikView. After some back ‘n forth between the participants of the discussion I have created a simple draft proof-of-concept. In this post, I would like to describe the core principles that I implemented in the proof-of-concept Monte Carlo analysis that I developed with QlikView. A detailed description of the model and its context can be found in “How to Measure Anything” by Douglas Hubbard (p. 74-78). However, in brief the model deals with the lease of a piece of equipment that should result at least in a 400,000 cost saving in order to break-even.

Before I continue, I would like to introduce QlikView to those of you who are not familiar with it. QlikView is so-called in-memory Business Intelligence platform that is available as a desktop application for Windows (works fine via VMWare Fusion on the Mac) and there is an complete range of server products for Enterprise implementation. The beauty is that BI tools developed in the desktop environment can easily deployed on the server and therefore be used enterprise wide.  Last but not least, from QlikView 9 onwards you can download a copy and play within for free; the only problem is that you may have some difficulties exchanging files with colleagues and friends (buying a license will resolve this).

I set myself the following success criteria for the Monte Carlo application I want to build in QlikView:

• Users should be abel to insert values through the user interface;
• Based on the new user input and predefined model, the application should be able to generate a set number of new scenarios.

The user interface

The user interface has two component. First, the input section which you can see in the left column in the enclosed image. Here the user can define the lower and upper bound of the 90% Confidence Interval (Hubbard, 2007 and 2009). Second, the column on the right provides some basic information about the results of the scenarios. The entire data set is available so you could also think about creating histograms and many other reports that allows you to analyze the results of the simulations. The report with the title “Risk of not break-even” shows  what the chances are based on estimated intervals defined on the left that his project will break-even.

The script and work-flow

In order to start a simulation, the user needs to define his/her estimated cost saving intervals. After those are completed the script needs to be reloaded. Depending on the number of simulations you would like to perform this takes from a few seconds (e.g. 10,000 scenarios) to a few minutes (e.g. 5:01 for 10,000,000 scenarios).

The script is short and relatively simple. Everybody with a basic understanding of SQL and Excel formulas should have no problems understanding it. The basic aim of the script is to create a table with all the scenario’s variables. The script has three parts:

1. Loading the variables as defined in the user interface and calculating the mean and standard deviation for each variable; four in total.
2. Generate the scenario’s variables on the basis of the in the previous part set standard deviation and mean.
3. Include the ‘business logic’ in the model and calculate the overall cost savings; this could also easily be done in the user interface and therefore does not necessarily have to be done in the script.

I have copied the script at the end of the post.

Final remarks

This is all that is needed to setup a basic Monte Carlo analsyis in QlikView. More advanced and business realistic models can be build based on those principles. QlikView has a very strong in loading external data sources (that is what a BI platform is all about after all) and has an amazing in-memory calculation engine. The only draw back I can see at this stage is the limited number of probability distributions that QlikView has build in. However, expanding this should not be a big deal for QlikTech once we identified which distributions are relevant. All in all, QlikView seems to be a powerful platform to perform Monte Carlo simulations with.

I am looking forward to hear your responses to the concepts outlined above and please leave a comment or send me an e-mail if you have any questions. Unfortunately, I am unable to upload the QlikView file that I created to the WordPress server but if you would like to have a copy please shoot me an email and I will send it to you in an e-mail.

Last but not least: The script

//Loading the variables as defined in the user interface and calculating the mean and standard deviation for each variable

LET MaintenanceMean = ($(#Maintenance savings – Lower bound) + $(#Maintenance savings – Upper Bound)) / 2;

LET MaintenanceStDev = ($(#Maintenance savings – Upper Bound) + $(#Maintenance savings – Lower bound)) / 3.29;

LET LaborMean = ($(#Labor Savings – Lower Bound) + $(#Labor Savings – Upper Bound)) / 2;

LET LaborStDev = ($(#Labor Savings – Upper Bound) + $(#Labor Savings – Lower Bound)) / 3.29;

LET RawMaterialsMean = ($(#Raw Materials Savings – Lower Bound) + $(#Raw Materials Savings – Upper Bound)) / 2;

LET RawMaterialsStDev = ($(#Raw Materials Savings – Upper Bound) + $(#Raw Materials Savings – Lower Bound)) / 3.29;

LET ProductionMean = ($(#Production Level – Lower Bound) + $(#Production Level – Upper Bound)) / 2;

LET ProductionStDev = ($(#Production Level – Upper Bound) + $(#Production Level – Lower Bound)) / 3.29;

// Generate the scenario’s variables on the basis of the in the previous part set standard deviation and mean.

MonteCarlo:

REPLACE LOAD

RecNo() AS Instance,

NORMINV(RAND(),$(MaintenanceMean),$(MaintenanceStDev)) AS “Maintenance Savings”,

NORMINV(RAND(),$(LaborMean),$(LaborStDev)) AS “Labor Savings”,

NORMINV(RAND(),$(RawMaterialsMean),$(RawMaterialsStDev)) AS “Raw Materials Savings”,

NORMINV(RAND(),$(ProductionMean),$(ProductionStDev)) AS “Production Level”

AUTOGENERATE(10000);

//Include the ‘business logic’ in the model and calculate the overall cost savings; this could also easily be done in the user interface and therefore does not necessarily have to be done in the script.

LEFT JOIN (MonteCarlo)

LOAD

Instance,

ROUND(([Maintenance Savings] + [Labor Savings] + [Raw Materials Savings]) * [Production Level]) AS “Annual Savings”,

IF( (([Maintenance Savings] + [Labor Savings] + [Raw Materials Savings]) * [Production Level])<400000,’No’,'Yes’) AS “Break-Even”

RESIDENT MonteCarlo;

//Loading the variables as defined in the user interface and calculating the mean and standard deviation for each variable

LET MaintenanceMean = ($(#Maintenance savings – Lower bound) + $(#Maintenance savings – Upper Bound)) / 2;

LET MaintenanceStDev = ($(#Maintenance savings – Upper Bound) + $(#Maintenance savings – Lower bound)) / 3.29;

LET LaborMean = ($(#Labor Savings – Lower Bound) + $(#Labor Savings – Upper Bound)) / 2;

LET LaborStDev = ($(#Labor Savings – Upper Bound) + $(#Labor Savings – Lower Bound)) / 3.29;

LET RawMaterialsMean = ($(#Raw Materials Savings – Lower Bound) + $(#Raw Materials Savings – Upper Bound)) / 2;

LET RawMaterialsStDev = ($(#Raw Materials Savings – Upper Bound) + $(#Raw Materials Savings – Lower Bound)) / 3.29;

LET ProductionMean = ($(#Production Level – Lower Bound) + $(#Production Level – Upper Bound)) / 2;

LET ProductionStDev = ($(#Production Level – Upper Bound) + $(#Production Level – Lower Bound)) / 3.29;

// Generate the scenario’s variables on the basis of the in the previous part set standard deviation and mean.

MonteCarlo:

REPLACE LOAD

RecNo() AS Instance,

NORMINV(RAND(),$(MaintenanceMean),$(MaintenanceStDev)) AS “Maintenance Savings”,

NORMINV(RAND(),$(LaborMean),$(LaborStDev)) AS “Labor Savings”,

NORMINV(RAND(),$(RawMaterialsMean),$(RawMaterialsStDev)) AS “Raw Materials Savings”,

NORMINV(RAND(),$(ProductionMean),$(ProductionStDev)) AS “Production Level”

AUTOGENERATE(10000);

//Include the ‘business logic’ in the model and calculate the overall cost savings; this could also easily be done in the user interface and therefore does not necessarily have to be done in the script.

LEFT JOIN (MonteCarlo)

LOAD

Instance,

ROUND(([Maintenance Savings] + [Labor Savings] + [Raw Materials Savings]) * [Production Level]) AS “Annual Savings”,

IF( (([Maintenance Savings] + [Labor Savings] + [Raw Materials Savings]) * [Production Level])<400000,’No’,'Yes’) AS “Break-Even”

RESIDENT MonteCarlo;

Filed under: Analytics, Business Intelligence, LinkedIN, QlikView , Business Intelligence, Decision, Monte Carlo, QlikView, Risk Management

Future of AppleTV

In a recent post on Mashable.com the future of the AppleTV was discussed; following the relevant paragraph from their post:

“In the meantime, Apple TVs themselves are cheaper then ever, but Apple is strangely quiet about it. Is Apple TV the future of home entertainment or is it Apple’s blunder which will probably disappear from the market in the years to come?”  

Let us assume that the AppleTV is not a huge success, what can Apple do to remain in the living room entertainment? One option would be to upgrade the AppleTV and introduce it at MacWorld in January. But what would an upgrade look like. The functionalities of the current version of the AppleTV are limited and it is only a supplement to existing living room hardware and not a replacement. Apple could think about introducing a full living room solution, thus introduce the next version of AppleTV that includes a TV and radio tuner, a DVD player and the ability of using computer displays as a screen. This might drive the sales for Apple’s Cinema Displays and displays from other suppliers. Thinking crazy, a AppleTV solution based on the iMac could also be great solution.In other words, Apple has all (except of a TV tuner) the technology in its product portfolio to make a great product!In relation with this post, also see my earlier post about an alternative Apple living room strategy.

Filed under: Apple, Technology portfolio, product strategy , Apple, AppleTV, Consumer Electronics, Innovation, Living room, Opportunities, Technology, Technology portfolio

Google maps – not a map service for bicyclers

Without any doubt we can say that Google Maps is one of the best available maps services. Unfortunately, the suggested routes are aimed at car users and not at bike users. I often want to use Google Maps to find some directions to go somewhere. Unfortunately, I used a bike in most my commuting within the city (mainly Copenhagen in my case) and Google Maps provides routes that are optimized for car users. In other words, most of the directions I get from Google Maps are useless for my purpose. I would love to see that Google builds in an algorithm that can provide me with a bike route if I like.   

Filed under: Google, Web 2.0, Web services

How I would have entered the living room if I was CEO of Apple Inc.

A quick Keynote play about some thoughts I have had for a while about how I would have entered the living room if I was in charge of this strategy at Apple Inc.

Filed under: Apple, ipod, product strategy