Excel is one of the most widely used personal productivity tools used in Civil Construction. Excel has been with us since 1987. Excel fulfils a multitude of functions with a construction company from the simple to the extremely complex.

Our focus is on excels use as a modelling tool.

An Excel model has been defined as “a spreadsheet which makes quantitative estimates or forecasts based on a set of assumptions”. Inherent in this definition is that the spreadsheet consists of a inputs, calculations and outputs. More importantly, a simulation model is constructed so that changes to the inputs will derive accurately the required outputs without modification to the logic of the spreadsheet.

Before we can discuss Excel simulation models we need to wrench ourselves away from Excel as a What You See Is What You Get (WYSIWYG) tool. When learning to use Excel we are introduced to the WYSIWYG construct and many never leave it. When you build models within the WYSIWYG construct the formulas becoming much more complex and unwieldy.

So what is the alternative?

Lets breakdown the components of a model within Excel, they are:

Inputs, this is quantitative measures that are determined outside your model. What they are is determined by the scope of your model.
Reference, these are quantitative measures used within the model that are provided by others. They differ from inputs in that they remain the same when the model is used for different cases, for example, income tax rate schedules, conversion rates from one measure to another,etc.
Controls, a specialised form of input, attributes that modify the model to provide insights.
Outputs, the results of the model presented (formatted) for the end-users.
Calculation, the calculation logic required to arrive at the required outputs.

The extraction of the calculation logic from the other components has a number of advantages.

The calculations can be constructed in the most efficient way possible;
The calculations can be developed and tested in modules, so that you don’t have to wait to the end to test the whole model. And a module can be changed independently of other modules;
Calculations that maybe done more than once can be created as blocks that can be triggered by controls; and
Calculations can be hidden from display so that the logic of the model cannot be disrupted by end users.

The extraction of output from other components means that report formats can be developed for specific purposes without the need to repeat the complex calculations. Where the output of the model is used in other spreadsheets, dedicated formats can be used to simplify the task.

The extraction of controls from other components enables the end user to focus on attributes of the model and their impact on the outputs.

The extraction of the reference items simplifies their update. Often reference items are embedded in the calculations in a model. It can be difficult to identify their use and to test their accuracy.

Extraction of input from other components means that the inputs can be collected using simple cells. There is no need for formulas in the input component of the model.

The use of this construct to produce a model in excel makes the audit of the logic of the model much easier.

Mark has built simulation models for projects in excess of $A 500,000,000 and in excess of 5 years.