This is part 2 of our series on speeding up financial models. Part 1 covered function selection and how the way formulas are written makes a large difference to model performance and transparency. This part steps back to look at how a model can be structured to perform better. Many of the ideas apply the guiding themes of our modelling methodology.
Keeping it simple
A model should do what is required of it and no more. Extra complexity and functionality add clutter that gets in the user’s way and can slow the model significantly. Structurally, much of what slows a model down is functionality that gives the end user no benefit. The sections below give examples and our approach to keeping it simple.
Tailor the model to the end user
Design the model with the end user in mind and use only the functionality they need.
Periodicity is a good example. An asset with a 30-year life needs 30, 120 or 360 columns of calculations for annual, quarterly or monthly periods, which makes a large difference to calculation speed. Choose monthly periods only when the end user needs them. The length of the timeline is similar: a longer forecast is not always better. If you are not confident in the long-term forecast and the model does not need it, leaving it out avoids a misleading impression of accuracy.
Depreciation and tax are other common problem areas. How many depreciation categories does the required accuracy need? How much does deferred tax matter to the results, and how detailed do the forecasts of tax differences need to be? These calculations can become extensive, so if they will not materially affect the results, simplify them. The principle applies to every part of modelling: keep the end user in mind, and it becomes much easier to cut the complexity they neither want nor need.
Remove duplicated calculations
Never perform the same calculation twice. The principle is simple, yet most models break it. The obvious examples are dates and flags recalculated on each sheet, and escalation indices recalculated wherever they are applied. Subtler ones include pro-rated amounts, applied interest rates, days in period and parts of a formula that never vary along a row. The advice below helps avoid them.
Calculate step by step
Keep formulas clear and short, and break complex calculations into their parts. Doing too much in one cell raises the risk of error, reduces transparency and often slows the model. Separate parts can also be reused elsewhere, which cuts repetition.
Calculate timing items on a timing sheet
Calculate dates, flags, days in period, proportion of period, escalation indices and other timing items on a separate timing sheet and link to them wherever they are needed. Do not recalculate any of them elsewhere in the model.
Use a single cash flow waterfall
Models often calculate the cash available at different levels of seniority in the cash flow waterfall, especially in project finance. Modellers frequently recalculate the waterfall down to the required level for each of these, which creates a lot of duplication. Instead, set up one consolidated cash flow waterfall for the whole cascade and link each calculation to it.
Use helper columns
Do not recalculate in every cell any part of a formula whose result does not vary by cell. To turn an annual fixed cost into a monthly amount, for example, divide by 12 once in a helper column and refer to that result in each cell.
The same applies to more complex calculations. An INDEX-MATCH that matches a row label in a list always returns the same match number, so move the MATCH into a helper column and use INDEX alone, referring to that match number. Figures 1 and 2 compare the two.
Use 2D blocks and consistent calculations
Complex models often repeat a set of calculations, such as debt accounts for several facilities. Modellers tend to build several copies of the same mechanics, sometimes using INDEX or CHOOSE to make the copies easier, as in Figure 3.
The alternative is to build these mechanics as 2D blocks, which group each calculation step together for all facilities. This can improve calculation speed by reducing the number of unique formulas. It is less prone to error, easier to review and update, and no longer needs INDEX, because the items referred to are usually listed in order and can be linked directly (Figure 4). 2D blocks also need fewer rows, since headers are not repeated and the balance brought forward lines of corkscrew accounts can go.
Find and remove unused calculations
Models are often built from a template or a precedent model, which leaves many legacy calculations behind, kept for fear of breaking something or for lack of time to review. They can slow the model significantly and make it less transparent. We recommend reviewing every calculation in a model and removing anything it does not need. Excel add-ins help: they find unused calculations and trace dependents and precedents, so you can see the effect of deleting a calculation before you do it.
Sort and arrange your data
Depending on how the model uses its data, sorting it in ascending or descending order can make lookups much faster by moving the items you need closer to the start of the lookup range. You can also arrange the data to match the model timeline, which removes the need for date lookups, or group and separate it to shrink the ranges lookups search. How far this helps depends on the model, but in data-heavy models it can make a significant difference.
For functions that refer to ranges, such as SUMIFS, calculation time is proportional to the number of used cells referred to. Excel does not examine unused cells, but it does examine used cells that are irrelevant. A whole-column reference can be efficient if the column holds only relevant data, but often it does not. We usually recommend a table instead of column references: an Excel table adjusts its size as data is added or removed, which keeps the reference range as small as possible and the calculation as fast as possible.