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]

Tags: iWork, Monte Carlo, Numbers, Statistics, Rocscience

When Apple first released iWork '08 I immediately reviewed Numbers. I love the way that Excel and other spreadsheets have changed the way that engineering calculations are carried out, but over the last 10 years very little has changed to the humble spreadsheet. Apple finally decided to change the spreadsheet application with Numbers, but when I reviewed it the first time, there were a few problems. I couldn't use iWork for all my spreadsheets because of problems with the plotting and a lack of easy scripting support. So for iWork '09 have my wishes been answered?

The first thing I tried as importing a two existing spreadsheets. The import is now, near perfect. The problems last time included custom cell formats disappearing, problems with the graphs and missing spreadsheet functions. Now the only errors that pop up when importing a spreadsheet are only relatively minor issues such as defining plot areas. On top of all this, Numbers now seems to run at a similar speed to Excel when running the larger spreadsheets used in engineering.

One of my biggest requests was better graphing. Good plotting of x against y data was not possible in the previous version of iWork. The latest version allows x and y data as well as labels to be referenced to spreadsheet cells. This totally changes the form that graphs can take and allows the user to create most of the key types of graph. Log scales can now also be use on both axes.


Both data series for graphs and formulas use a new format with coloured labels with attached drop down toolbars. These drop down menus don't add much functionality but they really do aid the reading of an equation more than the simple colouring currently used in Excel. ultimately I'd like to see an improvement to the way brackets are used in the formulae to make them even more readable, but this is another good incremental improvement.


The other new feature included is the formula list. The formula list is a complete list of all the formulas in a spreadsheet including a cell reference, a result and details of the formula. The list also includes a comprehensive find and replace feature. I'm not sure that I will use this feature much, but occasionally it might be useful to run through a spreadsheet to check for odd formulas in a series. It might also prove to be a useful to to review how spreadsheets work, allowing a viewer to quickly look through the types of formulae being used. What might be more useful in the future is this view to take on another form where the spreadsheet is set out in a graphical or flow chart way so that a review can easily see the flow of information through the chart. I know this type of view would have helped me in the past where previously I only had Excels formula auditing feature to help.


There are many new functions that have been added to the latest version of Numbers. Whilst many of the new functions are fairly specific and of limited use to the general user, the new statistical distributions will be useful to many engineers. Statistics seems to becoming more and more important to engineers as time goes by so the inclusion of a comprehensive range of distributions , including Beta distributions, Binomial distributions, Gamma distributions, Lognormal distributions, and T distributions, is a really useful feature.

The other key feature request I originally made was the inclusion of an Applescript dictionary. This would allow other features to be included in the spreadsheet, such as Monte-Carlo analysis. Brilliantly Apple have also done this. All iWork applications now have an Applescript dictionary which makes then scriptable. Analyses such as a Monte-Carlo analysis can now be undertaken using Numbers. Numbers really is now the only spreadsheet an engineer will typically need.

I can heartily recommend a trial of the new version of Numbers in iWork '09 if you use spreadsheets regularly in your working life.

Tags: Numbers, Apple, Applescript, iWork '09, Spreadsheets

I love Apple applications, they just seem to suit the way I work. Last months release of iWork '08 was a big step forward in the suite of applications, especially with the release of Numbers. I see this application as the start of one of the biggest changes that spreadsheet applications have ever had with the use of multiple tables on a single sheet. The effect of this will not be fully realised for a few generations of the software, but I wanted to find out how the current version of Numbers works for engineers.

To do this I recreated a standard spreadsheet I use on a regular basis for designing an unreinforced concrete section under axial load and bending. This spreadsheet allows me to check all of the basic features that I need in a spreadsheet for day to day use. It doesn't include some of the more advanced features that I use, but I know that Numbers is missing scripting and advanced plotting. As version 1.0, I don't expect these features and I know that Numbers will not be able to do everything I need it to do, but hopefully this will improve with time and newer versions.

First of all, here is the spreadsheet I created.

So how did I get on?

Well first of all the separate tables. It took me a few minutes to get used to them, but as soon as I did they are much more intuitive to use. You can concentrate totally on the table you are working on, forgetting about all the others. Once you've done the work setting up the spreadsheets you can then concentrate on how to lay them out. It really helps focus on what you are trying to achieve.

What is ultimately more significant is how the table can be modularised. To test this out I added another table to the spreadsheet which took into account reinforcement in the section. It was really easy, just add the table. What this means is that a library of standard tables can be created and just bolted together. Not only is this quick and easy, but if a standard library of verified tables is created, all you have to worry about when checking a new spreadsheet is how the table have been bolted together and not whether the contents of each and every cell is correct. The whole table concept, from the way they work to the potential ability to create a library of standard tables will make far more transparent and accurate spreadsheets.

The biggest problem I have with numbers is in the graph on my spreadsheet. The amount of control that you have over the type and format of the spreadsheet is an order of magnitude worse than it is with Excel. You can create beautiful looking graphs and charts, but the options are limited. I wanted to create a straight line between the points on my spreadsheet, but could only create a series of points. It is something that I will definitely be looking to Apple to improve in future versions.

My last gripe is a well known one, but there is a lack of support for either plugins or scripting. This is not surprising as most 1.0 applications released by Apple have limited support and things do improve with time. I hope that Applescript can be added for version 2.0, because the possibility of Applescript interfacing between Omnigraffle and Numbers is well worth waiting for. Once this happens the possibilities for quickly automatically generating standard plots and diagrams will mean there is a huge step forward in my productivity.

I think numbers is a big step forward for iWork, and a big step forward for spreadsheets. I will use it, but I have no doubt excel will be my tool of choice, but I look forward to seeing what can be achieved with version 2.0.

Tags: iWork, Numbers, Apple, Review