Last week I talked about the importance of establishing the scope and purpose of a model. Now you have that sorted, you may be itching to dive in and start ‘coding’, but wait!

START AT THE END

There are key decisions that you need to take at the start of the development process about how your model will be structured; this is often referred to as architecture. To stretch the design and construction analogy a little further, architects think about look and feel, design and layout, accessibility, ease of use etc. the coding (ie logic and formula building) is more analogous to the engineering disciplines of planning the routes for cabling, air-conditioning ducts, water supplies, drainage and so on.

A good way to plan a model is to start at the end – ie with the outputs. All your assumptions and calculations will ultimately feed into reports, so a good first step is to establish what you want to get out of a model and what questions you want it to answer.

So before we get stuck into the detailed calculations, we need to adapt our knowledge of scope and purpose discussed last week into a design layout for the model. The Code has quite a lot to say on this and some of it has been covered well elsewhere on our website – for example separating inputs, calculations and outputs and using a linear flow of logic in the model. We’ll explore a couple of the other areas here, which may be less obvious.

3D MODELS

You can think of a spreadsheet as a three dimensional matrix – a cuboid of cells. Whilst we tend to work in two dimensions – ie on a single worksheet, we have a third dimension to work with as well – across multiple sheets. However only a limited subset of Excel functions work across the third dimension and I normally stick with SUM for that purpose.

Nevertheless, we can and should use the 3D structure when designing our model. When modelling a simple company, you can use the sheets to group together types of calculation (balance sheet calcs, financing calcs etc) but for more complex businesses, we’ll look at an example of how you can use the third dimension for a different purpose.

When modelling a group of companies, or a portfolio, we often find we have a number of business units that have similar characteristics. They may not be identical, but the nature of financial reporting means that the company will normally have designed a reporting format that works for the whole group; at the level of the income statement, balance sheet and cash flow statement, the layout for all companies in the group should be identical, even if some entities have blanks (ie redundant lines).

A portfolio of assets owned by a fund may be less well aligned in terms of reporting lines and some mapping may be necessary to achieve a unified output format.

Structurally, once you have a unified layout for the financial statements, you can then think about how you build calculations for each business unit to produce those. Aim to keep as much common ground as possible between business units unless there are some which are radically different. For example a portfolio of wind farm assets can be very similar, a portfolio of mixed renewable assets including wind, solar and hydroelectricity will have more differences and a group like Centrica will contain businesses that are very different from each other. In those cases, group similar business units together and create a master calculation for each category.

THE ONE SHEET WONDER

When modelling many similar business units, one approach is to use a single sheet for each unit. The calculations on this sheet will culminate in a set of financial statements and key performance indicator reports that are identical for all. The beauty of this approach is that you can sum through these outputs to give an aggregated report for all business units, or sum through units in each category or geography to give you regional discipline based summaries, then sum through these intermediate reports for the entire group.

This is not always the answer, but it works for many groups and portfolios. When using this approach, you need to be quite rigorous about how you lay out the calculations on these sheets. Since you will be summing through the financial statements, I like to put the reports at the top, so that they don’t move each time you add a new line of calculation. Whilst this might be seen as a contravention of the ‘read like a book’ recommendation in the Code, it is a worthwhile trade-off – all other calculations flow from top to bottom – Think of it like a newspaper, where the headlines are at the top and the detail below.

Once you have an output format for your key reports, you can turn your attention to producing this output for each business unit. There are a couple of elephants in the room here – one is that the group reporting currency may be different to the operating currency of the business units. The second is that if you have acquired businesses, the pre-acquisition revenues and reserves will not be consolidated. You will also need to eliminate intercompany trading and lending.

These problems can be tackled by having two sets of financial statements for each business unit: The first will be a full set of statements in local currency. The second will reference the first and be converted to the reporting currency and have a mask (using 1/0 flags – more on this in a later blog) to prevent pre-acquisition details feeding through.

Intercompany trading should be on separate rows from third party trading so that the intercompany activity can be eliminated. This can be done on an eliminations sheet with the same financial statement layout as all the business units, however this will only have the entries for eliminations. Later in the Code, we learn about sign convention. If you are using the convention we use at Numeritas (assets and revenues positive, liabilities and costs negative) then the elimination sheet will have negative values in revenue and positive values in costs so that when ‘3D summed’ with the other business unit sheets, their group totals will be reduced by the intercompany values.

MODELS SPREAD ACROSS MULTIPLE WORKBOOKS

First of all, most models can be contained in a single workbook and this is the first choice in most situations. The Code mentions that if you spread a model across multiple workbooks, you should make it clear and obvious which values are linked to and from other workbooks.

We’ll explore this in a moment, but first, let’s consider why you would want to do this and why I would advise against it in most circumstances:

Reasons that might make it worth using multiple workbooks for a model:

  1. Geographically distributed users cannot easily access the same model on a shared network.
  2. Multiple users need to access workbooks at the same time and for extended periods.
  3. Some information is sensitive and should be restricted to access only by authorised users.
  4. The individual component files are for different purposes, with summary outputs being consolidated.

 

There may be other reasons, but challenge whether they really need to be separate. Some of the downsides to multiple file models are:

  1. Version control becomes more difficult.
  2. There is a high risk of corruption of data between the different files.
  3. Variables that are common to all files are difficult to keep unified.

 

In situations where you are convinced this is essential, take note of the discouraged approaches in the Code: “Avoid referencing cells in one workbook from another”

Cross file links are particularly vulnerable to corruption. Users can copy the files onto a personal drive to work on them and save updated versions back to a shared drive, which creates a new branch (ie the original file is still linked). They can insert rows or columns, which corrupts the referencing of the links or move files to different locations.

In any situation where data needs to transfer between files, there is a strong argument for having what the Code calls a ‘landing page’. I like to think of these sheets as an airlock between two Excel files. They are identical in layout and have a one-to-one relationship with the matching cell in the other workbook. Whether these are linked (not recommended) or data is copied and pasted between them, this mitigates the risk of corruption if calculations in either file are updated, requiring insertion of rows or columns. We have another blog on this subject which goes into more detail.

If you haven’t already, download our whitepaper that describes how Numeritas complies with each section of the Code.

download our complete guide

Next week we’ll look at the other half of Layout and structure; Navigation and inputs.