Monte Carlo
Adding Monte-Carlo analysis to iWork Numbers
03/02/09 06:03 Filed in: iWork
Monte-Carlo analysis is not a new method of
analysis. Scientists, engineers and project
managers have been using it for years in many
different fields to solve problems with multiple
varying inputs. As a tool it has proved to be
incredibly adaptable and reliable as a problem
solving method. It is simple to understand, works
with models of virtually any type and gives a good
understanding of a problem. So the question is why
is it not used more?
The first problem seems to be that it is not well understood. I think that few engineers actually have a chance to learn about methods if they don't have access to the tools to actually use them. Monte-Carlo analysis can only really be used with a computer model capable of running a Monte-Carlo simulation. It can be done manually, and for complex models sometimes it is necessary to just perform a few runs and manually compare the difference (ref. 1). This is not much different to performing a finite element analysis manually though, it can be done but there is little benefit for all the work that goes in. There are far better ways to solve the problem if manual methods are going to be adopted.
Tools are available to the engineer to undertake Monte-Carlo style risk analysis. The problem is that they are not accessible and they come with an enterprise price. Combining the the cost per seat of the software, the training cost and the learning cost, the price of getting up and running with these tools prevents engineers just having a go. It becomes a serious proposition to look at using Monte-Carlo analysis on a project and yet so often some fairly small decision could be well informed using the method. The tools should encourage the playful, explorative search for a solution as well as being capable of performing accurately, tightly defined, high quality analysis necessary when solving a significant problem.
Monte-Carlo analysis is also prone to a credibility problem. The outputs an always statistical. If you ask your programmer when the project will be complete it is much easier to deal with the answer '4 weeks time' rather than 'there is a 67% chance that the project will be complete in 4 weeks time'. Whilst the statistical answer is more informative, it can also hide many problems. If the project over-runs beyond the 4 week deadline it is easy to say there was a problem with the deterministic answer of '4 weeks time'. You can't readily say whether the statistical answer is right or not though.
For a project manager this would be a serious issue, because without some form of validation of the method being used, you can not have any confidence about using the methodology. Validation of a method only really comes with its regular use; repeated use gives both direct evidence of its performance as well as the personal, empirical understanding of how effective a method is.
To help with these problems I have started to develop a tool that will put Monte-Carlo analysis, as well as other similar tools, into the hands of any engineer. When Apple updated iWork earlier this year, Numbers finally got an Applescript dictionary. This means that automated tools can be created which hook into Numbers. The aim is to create a complete suit of automated tools that allows engineers, scientists and managers to create a model using the freeform approach of Numbers and then analyses the model using simple, intuitive but powerful tools to explore the behaviour of the model.
The project is in an early Beta phase and I would like as many people to try it as possible. People use these tools in many different ways and I need to understand how people will use it to understand how to develop it further, without impacting on the creative use of the tool.
If you are interested then please have a look at the Risk Engine page, download the application and try it out in your models.
Thankyou for your support.
References
1. One is not Enough, 2008, Rocscience Inc., [http://www.rocscience.com/library/rocnews/winter2009/One-Is-Not-Enough.pdf]
The first problem seems to be that it is not well understood. I think that few engineers actually have a chance to learn about methods if they don't have access to the tools to actually use them. Monte-Carlo analysis can only really be used with a computer model capable of running a Monte-Carlo simulation. It can be done manually, and for complex models sometimes it is necessary to just perform a few runs and manually compare the difference (ref. 1). This is not much different to performing a finite element analysis manually though, it can be done but there is little benefit for all the work that goes in. There are far better ways to solve the problem if manual methods are going to be adopted.
Tools are available to the engineer to undertake Monte-Carlo style risk analysis. The problem is that they are not accessible and they come with an enterprise price. Combining the the cost per seat of the software, the training cost and the learning cost, the price of getting up and running with these tools prevents engineers just having a go. It becomes a serious proposition to look at using Monte-Carlo analysis on a project and yet so often some fairly small decision could be well informed using the method. The tools should encourage the playful, explorative search for a solution as well as being capable of performing accurately, tightly defined, high quality analysis necessary when solving a significant problem.
Monte-Carlo analysis is also prone to a credibility problem. The outputs an always statistical. If you ask your programmer when the project will be complete it is much easier to deal with the answer '4 weeks time' rather than 'there is a 67% chance that the project will be complete in 4 weeks time'. Whilst the statistical answer is more informative, it can also hide many problems. If the project over-runs beyond the 4 week deadline it is easy to say there was a problem with the deterministic answer of '4 weeks time'. You can't readily say whether the statistical answer is right or not though.
For a project manager this would be a serious issue, because without some form of validation of the method being used, you can not have any confidence about using the methodology. Validation of a method only really comes with its regular use; repeated use gives both direct evidence of its performance as well as the personal, empirical understanding of how effective a method is.
To help with these problems I have started to develop a tool that will put Monte-Carlo analysis, as well as other similar tools, into the hands of any engineer. When Apple updated iWork earlier this year, Numbers finally got an Applescript dictionary. This means that automated tools can be created which hook into Numbers. The aim is to create a complete suit of automated tools that allows engineers, scientists and managers to create a model using the freeform approach of Numbers and then analyses the model using simple, intuitive but powerful tools to explore the behaviour of the model.
The project is in an early Beta phase and I would like as many people to try it as possible. People use these tools in many different ways and I need to understand how people will use it to understand how to develop it further, without impacting on the creative use of the tool.
If you are interested then please have a look at the Risk Engine page, download the application and try it out in your models.
Thankyou for your support.
References
1. One is not Enough, 2008, Rocscience Inc., [http://www.rocscience.com/library/rocnews/winter2009/One-Is-Not-Enough.pdf]
Monte Carlo analysis in Excel
30/11/08 19:48 Filed in: Analysis
Monte-Carlo analysis is one of those tools that I
have always found useful to keep in my pocket as an
engineer. I like to find an elegant solution to
problems limiting the number of variables and
amount of analysis that has to be done. Sometimes a
brute force approach can not however be avoided and
Monte Carlo analysis is a very effective way of
solving a complex risk or probability problem.
Because I don't use it much specialist tools such as @risk are not really an option for me. The basic version of Excel is however more than capable of performing Monte-Carlo analysis with relative simplicity.
The first stage is to build your model with the usual input and output parameters using the same format as you would normally do. Once the is done create a new worksheet for the input parameters that you want to use in he Monte-Carlo analysis. To generate the random numbers to put into the Monte-Carlo analysis you will need to use a Rand() function for each input parameter you want to consider. You can then use Excel's built in distribution functions to generate your output value or you can create your own - I will consider some of the different distribution functions in later posts.
If you have done this correctly, every time you amend your spreadsheet now the values should change on this worksheet. Now link the input values on your model worksheet to the values on the inputs worksheet. The entire model should now change each time values are adjusted on the spreadsheet. At this point it is worth taking time to check how your model is performing. Just pressing the delete key on an empty cell should run one Monte-Carlo step. It is worth doing this a few times because Monte-Carlo analyses often test the limit of a spreadsheet with combinations of very high and very low values.
It is possible to manually run a Monte-Carlo analysis manually with the spreadsheet in this form. The power of this solution is however when it is automated, and to do that we can make use of a Macro.
First create a new worksheet to hold the outputs from the model. Now create a new Macro. The typical code that should be used for the Macro is something like:
Sub MonteCarlo()
With Application
.Calculation = xlManual
End With
For Counter = 1 To 500
Sheets("Model").Select
Calculate
Output1 = Range("L3").Value
Output2 = Range("H17").Value
Set curCell = Worksheets("Results").Cells(Counter, 2)
curCell.Value = Counter
Set curCell = Worksheets("Results").Cells(Counter, 3)
curCell.Value = Output1
Set curCell = Worksheets("Results").Cells(Counter, 4)
curCell.Value = Output2
Next Counter
With Application
.Calculation = xlAutomatic
End With
End Sub
This Macro runs the Monte-Carlo analysis 500 times. The results from the analysis are taken from cells L3 and H17 from the worksheet Model. They are then put into columns 3 and 4 of the worksheet Results.
Post processing of the output data should be done in a separate workbook. If this is not done then re-running the Monte-Carlo analysis will take much longer as the post processing will be done for each step of the Monte-Carlo analysis.
Because I don't use it much specialist tools such as @risk are not really an option for me. The basic version of Excel is however more than capable of performing Monte-Carlo analysis with relative simplicity.
The first stage is to build your model with the usual input and output parameters using the same format as you would normally do. Once the is done create a new worksheet for the input parameters that you want to use in he Monte-Carlo analysis. To generate the random numbers to put into the Monte-Carlo analysis you will need to use a Rand() function for each input parameter you want to consider. You can then use Excel's built in distribution functions to generate your output value or you can create your own - I will consider some of the different distribution functions in later posts.
If you have done this correctly, every time you amend your spreadsheet now the values should change on this worksheet. Now link the input values on your model worksheet to the values on the inputs worksheet. The entire model should now change each time values are adjusted on the spreadsheet. At this point it is worth taking time to check how your model is performing. Just pressing the delete key on an empty cell should run one Monte-Carlo step. It is worth doing this a few times because Monte-Carlo analyses often test the limit of a spreadsheet with combinations of very high and very low values.
It is possible to manually run a Monte-Carlo analysis manually with the spreadsheet in this form. The power of this solution is however when it is automated, and to do that we can make use of a Macro.
First create a new worksheet to hold the outputs from the model. Now create a new Macro. The typical code that should be used for the Macro is something like:
Sub MonteCarlo()
With Application
.Calculation = xlManual
End With
For Counter = 1 To 500
Sheets("Model").Select
Calculate
Output1 = Range("L3").Value
Output2 = Range("H17").Value
Set curCell = Worksheets("Results").Cells(Counter, 2)
curCell.Value = Counter
Set curCell = Worksheets("Results").Cells(Counter, 3)
curCell.Value = Output1
Set curCell = Worksheets("Results").Cells(Counter, 4)
curCell.Value = Output2
Next Counter
With Application
.Calculation = xlAutomatic
End With
End Sub
This Macro runs the Monte-Carlo analysis 500 times. The results from the analysis are taken from cells L3 and H17 from the worksheet Model. They are then put into columns 3 and 4 of the worksheet Results.
Post processing of the output data should be done in a separate workbook. If this is not done then re-running the Monte-Carlo analysis will take much longer as the post processing will be done for each step of the Monte-Carlo analysis.
Seeding random numbers
22/11/08 13:52 Filed in: Analysis
Statistical modelling is part of the standard
engineers tool set. The desktop computer means
running scenarios with multiple inputs an easy task
for all engineers. Most of these tools use random
numbers to generate varying outputs. The mechanics
of generating random numbers is a mathematical art
to itself, but there are some key features when
using statistical methods that need to be
understood for effective modelling.
One of these key features is the seed. Random numbers are generated using algorithms that take an input value and generate a sequence of numbers that are apparently random. The input value is called the seed. Most programmes use the time when the first random number is generated as the seed; this then ensures a different sequence is generated each time a programme is run.
Overriding the use of the time as the seed does however have a number of benefits:
● It allows scenarios to be re-run, particularly important where scenarios are used to generate the design in a safety critical situation.
● It allows scenarios to be partially re-run. By using the same input parameter the scenario can be started in the same way, but using the seed, the random number sequence can be effectively re-set. This then allows a bifurcation in the model with a different outcome part way through the model.
● It gives repeatability to models, a key component of any analysis that is quality audited. If you are having your analysis checked and it doesn't have built in auditing features such as date stamped reports, your checker must be capable of recreating identical results to yours. The only way of doing this in a statistical model such as UDEC is to set the deed of the model prior to the runs so that the checker can recreate exactly the same model.
So with all that in mind, here are a few ways of setting random number seeds in your models:
Excel
With Excel the easiest way to generate random numbers with a seed is to use the Data Analysis tool pack. I usually find that when I'm working with Excel and random numbers I'm having to use visual basic. For me setting the seed programmatically in Excel using Randomize statement.
Apple Numbers 1.0
Bad news here, I don't know how to set the seed in numbers. It doesn't really surprise me given the lack of scripting associated with Numbers. I remain hopeful of many improvements in the next version of Numbers and this is definitely one of them.
Cocoa
In Cocoa just use the srandom() function. There are plenty of examples out there with this in.
Applescript
In Applescript just set the seed when you first generate a random number using set firstRandom to random number from 1 to 100 with seed seedValue. I've been using this with no problems on an Application that I am currently writing in Applescript Studio. One thing to be aware of though is the some item of function. This doesn't seem to respond to seeding so well so other methods of taking a random value from a list using a directly generated random number seem to work better.
One of these key features is the seed. Random numbers are generated using algorithms that take an input value and generate a sequence of numbers that are apparently random. The input value is called the seed. Most programmes use the time when the first random number is generated as the seed; this then ensures a different sequence is generated each time a programme is run.
Overriding the use of the time as the seed does however have a number of benefits:
● It allows scenarios to be re-run, particularly important where scenarios are used to generate the design in a safety critical situation.
● It allows scenarios to be partially re-run. By using the same input parameter the scenario can be started in the same way, but using the seed, the random number sequence can be effectively re-set. This then allows a bifurcation in the model with a different outcome part way through the model.
● It gives repeatability to models, a key component of any analysis that is quality audited. If you are having your analysis checked and it doesn't have built in auditing features such as date stamped reports, your checker must be capable of recreating identical results to yours. The only way of doing this in a statistical model such as UDEC is to set the deed of the model prior to the runs so that the checker can recreate exactly the same model.
So with all that in mind, here are a few ways of setting random number seeds in your models:
Excel
With Excel the easiest way to generate random numbers with a seed is to use the Data Analysis tool pack. I usually find that when I'm working with Excel and random numbers I'm having to use visual basic. For me setting the seed programmatically in Excel using Randomize statement.
Apple Numbers 1.0
Bad news here, I don't know how to set the seed in numbers. It doesn't really surprise me given the lack of scripting associated with Numbers. I remain hopeful of many improvements in the next version of Numbers and this is definitely one of them.
Cocoa
In Cocoa just use the srandom() function. There are plenty of examples out there with this in.
Applescript
In Applescript just set the seed when you first generate a random number using set firstRandom to random number from 1 to 100 with seed seedValue. I've been using this with no problems on an Application that I am currently writing in Applescript Studio. One thing to be aware of though is the some item of function. This doesn't seem to respond to seeding so well so other methods of taking a random value from a list using a directly generated random number seem to work better.