I have seen estimators develop several files or tabs to generate various versions of estimates for the same scope where there are several possible technical and commercial solutions. One example of this is when the client wants to a see what the overall cost of a pipeline project would look like if the diameter is varied and or the material cost is varied based on the supply source. In such a case, the alternative estimates are started as mirror images of the initial estimate but are considered separate estimates. The estimator soon ends up handling multiple estimates in various spreadsheets and tabs for minor updates. There is a high chance of making errors and not updating all the estimates accurately.

Alternatively, there can be only one estimate file, with the detailed estimate changing in response to specific technical and cost inputs. There can be an input table with various options (in separate columns) which is linked to the main detailed estimate (one at a time). In this way, any changes are only made once and would be automatically updated for all options, and the estimator will not have to handle multiple estimates or even separate files.

The only apparent drawback with this approach is that the detailed estimate can only be seen one at a time and the summaries and results of the various options cannot be compared. The results of each option may have to be printed manually into another spreadsheet for analysing. This may seem like a laborious process if there are several options to manage.

Alternatively, a very easy excel macro can help automatically print the results, for the various options, into another tab (or another area of the same spreadsheet) for ready analysis.

The macro will simply run the whole estimate repeatedly for each set of inputs provided. It will then print the required results in pre-assigned cells one after the other. In the above example, if the options are 20 different pipeline diameters with varying material unit cost, the macro would simply run the estimate 20 times, printing the overall results (or any breakups if needed), 20 times at a pre-assigned range of cells in the excel file. These results can then be analysed and presented in a tabular or graphical fashion as required.

A typical example macro looks like below:

In this example, the values from cells A10 to C10 (Range command) from the inputs tab are copied into the estimate tab in cells D14 to F14; the results from the estimate tab in range R49 to U49 are then copied in the output tab in the range G8 to J8. The whole process is repeated 20 times (using x as a counter in conjunction with the “Next” command), with inputs being collected from the next row (Offset command) and the estimate being Run and the results being printed in the output tab in the consecutive rows (using the Offset command again).

Steps:

• This does not need any prior knowledge of macro writing
• Go to the “Developer” ribbon in Excel -> click “Macro” -> give a name to your macro such as Macro1 and then press “Create”.

        (If the “Developer” button does not appear in the ribbon, go to File-> click “Options” ->          then go to “Customise Ribbon” and select “Developer”)

• Then write the command like the one shown in the example box above.
• Make sure you have the correct ranges for inputs and outputs from your estimate file
• Note that the input and output ranges can be in a single tab and not necessarily in different tabs.
• Also, they might not be ranges, but single cell references.
• Also make sure that the tab names are correctly spelled (I have made that mistake several times in the past)
• The Offset command moves from one input to the next and is also used to print the outputs in consecutive cells (or sets of cells)
• Save the macro
• Then Run the macro

The macro should take few minutes to write, and every time there is an update to the estimate, results of all the options can be printed in seconds by just running this macro and any updated analysis can thus be presented almost immediately. One caution here, during any subsequent updates, make sure that the original cells for the inputs and outputs are not changed, and if they have moved, the macro will not automatically update those, and you will have to change those references by editing the macro with the correct cell references.

I have been using this macro (maybe the only macro I use), for more than 10 years now and this has made the multiple estimate preparation and update process much easier and simpler.

In my view, this is one of the simplest ways to automate the generation of multiple estimates using Excel which does not need any knowledge of macro writing.

The in-house benchmarking graphs for various types of piling as shown in the piling blog were generated using this macro. (I cannot upload a macro-enabled file in WordPress. Try it yourself and see if works, and let me know if you have any questions).