Financial Modelling in Microsoft Excel/Design patterns

Design patterns are used where the same set of needs comes up over and over again. They are standard and well-tested approaches to designing models, and you can build up a library of patterns to make it easier to design professional solutions.

Please note that the names of the patterns below are not official names, because Excel has no generally accepted patterns, but they have been created for the purposes of this book. You may not find these names used anywhere else.

Tabular pattern

The tabular pattern below is one all spreadsheet users are familiar with.

This is the simplest and most obvious way to build models, and it is used for most business spreadsheets.

It has two main problems:

1. the formulae must be copied down correctly in each column – this is a common error

2. it can be hard to check formulae, when they are crammed into successive columns. There is little room for any explanation, users can only see one formula at a time (unless they turn on the formula view, and then you can't see the values), and users can only see a few columns at a time. This means that as a model gets more complex, this pattern can become very difficult to follow, and even unworkable.

Block pattern

In more complex models, it may be difficult to fit everything into one sheet using the tabular pattern above.

The model can be divided into sections such as tax, depreciation, revenue cashflows, etc., and each of these can be put into a separate "block", usually on a separate sheet. The results are then consolidated in a summary sheet.

This is a pattern commonly used in large finance models, e.g. for toll roads, power stations, etc., where a large number of cashflows are projected forward in time, and where the model can be broken up into logical chunks. The models can get very big, e.g. 15 meg, with 3,000-4,000 unique formulae.

The advantage is that each sheet can be checked independently, and each sheet is on a particular topic. When a problem is complex, it helps if you can check it a piece at a time.

More on project financing

While this approach helps break up a complex problem into bite-size chunks, it is important to check the connections between the chunks and ensure they link up properly, as large models can become very confusing. You also need to design the model very carefully because it will be very difficult to rearrange later.

Merge pattern

The following approach is a powerful alternative to the tabular pattern. It works well where you have to carry out a series of calculations for many rows of data.

You first build a calculation sheet based on a single record of data,

• showing that data at the top of the sheet,
• listing any assumptions you need (explaining as you go), and then
• setting out the calculations down the sheet, as spread out as you like.

The result is that, given any one of the data records (i.e. rows), you can produce the correct results clearly and accurately for that single row.

This has obvious benefits in making the calculations much easier to check than when they are crammed into columns on a single row, next to the data. Of course, there is no free lunch, and the disadvantage is that you can only do one row at a time, which is clumsy.

The next step is to use a dozen lines of VBA code to load each data record in turn into this sheet, calculate, and export the results for each record into a separate row on an output sheet. You end up with an output sheet with a row of results for each data record.

There are several advantages to this:

• you use a single set of worksheet calculations for all records – there is no need to copy them down all the rows for each record
• the calculations are much easier to check than the tabular pattern, because you can spread them out across an entire sheet, and you can pull any data record in to look at it in detail
• all the business logic is in the worksheet, not in VBA, which simply automates the process
• the data and results sheets are completely separate from the calculations sheet, and they have no formulae that can be messed up - this separation is a very important design principle
• if you want to print individual results for each record (e.g. you want to send out letters to each person), VBA can print a specially formatted output sheet for each record as it loops through them all

This approach requires more skill to build than the tabular approach, but the code can be made generic, so it does not require any changes, removing the need for programming skills.

VBA pattern

VBA can be used to read in the data and assumptions, carry out all the calculations, and then put the results back in the sheet.

This is a very powerful approach where there are large amounts of data, or where a worksheet would be very slow, because

• VBA can process data extremely quickly, and
• it can read from and write to data files, databases and websites,
• and it is very compact, because all its calculations are done in memory and are discarded once the results are put back in a sheet

The power comes at a significant price, because

• VBA requires sound programming skills
• it can be difficult to adapt later if it needs to change
• it can be very difficult to check, although one solution is to produce a test worksheet with selected outputs to make it easier to see what is happening

Generally, VBA should be used for complex models involving a lot of data, like a simulation or a very large datafile.