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, eg 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, eg 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.
  
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 (ie rows), you can produce the correct results clearly and accurately for that single row of data.

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 automate the loading of each data record in turn into this sheet, calculation, and exporting of 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 calculations for all records – there is no need to copy them down all the rows for each record
  • the calculations are easier to create and check, because you can spread them out across an entire sheet, and you can pull any data record in to look at it in detail
  • 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 (eg 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.

    An example will demonstrate best. See the menu item "Automating w/o code" above.

    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,
  • it can use the full power of the windows API to do amazing things like reading web pages and posting web forms, and
  • it is very compact, because all calculations are done in memory and not stored on a sheet


    • I will say again that VBA can process data very fast. Many of the speed problems found by developers are caused by too much interaction between the worksheets and the VBA code, which seem to run in different processes. For speed, you should do as much as work as possible in either VBA or the worksheet, and minimise the data traffic between them. For example, if you are putting out a table of results from VBA, don't fill the worksheet one cell at a time - use a single line of code to put the whole table on the sheet.

    The power of VBA 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.

     

    Note: Where possible, I've attributed ideas to the people who thought of them. If I have missed someone or made a mistake, please let me know.