Nine things not to think about when building a FAST model

F1F9 FINANCIAL MODELLING EXPERTS

Author:

Andrew Berkley

Published:

10 Mar 2015

Comments:

0 Comments

Category:

At F1F9, we talk a lot about modelling fluency.  Just as I speak in English (without thinking “now I must speak in English”), so I model using FAST (without thinking “now I must model in FAST”).  Modelling fluency means that I can model while watching the television.

And because I have invested the time and energy and concentration to achieve fluency, so that gives me less to think about while I model.

So what happens to all that spare thinking time?  Where does it go?  Answer: I spend more time thinking about what I have to model (the “conceptual model”) because I don’t have to think as hard about how I am to model it (the “spreadsheet engineering”).

Here are nine other things that you do not have to think about if you are fluent in FAST modelling:

1. You are building a model that is designed to be subordinate to a contract. It is easy to fall into the trap that modelling is somehow real life.  It isn’t.

In the event of a dispute, the parties will look to the contract first and then the model. The contract always wins.

A fluent FAST modeller will work with labels that are unique. A fluent FAST modeller will make sure that their labels are also aligned – if not identical to – the definitions in the contract.

2. You are building a model that minimises visual clutter. Finance professionals do not have the best record when it comes to choosing what colours look good. Perhaps it is best that they stick to black and white.

Every colour in a FAST model is there for a reason – and if it possible to communicate meaning through just one signal, then we will use just one signal. That’s why inputs have a light yellow background but keep black font – even though there is a convention that says inputs should have both light yellow background and blue font.

That’s why a fluent FAST modeller does not mark their calculations in bold. You can tell it is a calculation since the row beneath the calculation will be blank (white background).

One signal is sufficient. Anything more risks being clutter.

3. You are building a model that names everything. I have had an excellent discussion recently with an Excel user whose ambition is to remove all direct cell references from his Excel models. His point is that when you type ” = F13″ you have no idea what is contained in F13 and, more important, what should be contained in F13. Location is of minimal importance; meaning is of huge importance.

His solution: use Excel Names.

In a FAST model, a fluent FAST modeller will use direct cell references but will recognise their weaknesses. They do not give them meaning through using Excel Names. Instead, they use the calculation block structure that presents – as clearly as possible – the meaning attached to a particular calculation: unique labels; links to ingredients used in the calculation set out immediately above (so that audit arrows are easy to trace); units; consistent formula copied across the columns; headings, sub headings and sub sub headings; worksheet names used like chapters in a book.

4. You are building a model that will likely use a lot of rows. The consequence of “extreme modularity” is that calculations are broken down into their constituent parts. Formulae become shorter (no longer than your thumb set out against the formula bar) and the technical complexity of the model is laid out down the vertical axis.

I would much rather review 10 calculation blocks with short formulae than 1 calculation block with a long formula.  It is easier on the eye; it is easier to print out and explain to a lawyer.

5. You are building a FAST engine, not a FAST carThe conventions associated with FAST are designed to make model calculations as easy to review and understand as possible. So it is important that they are applied to calculation worksheets.

There is nothing to stop a fluent FAST modeller stepping away from FAST and branding input sheets or output sheets or designing dashboards howsoever they think best. That way the end user will still recognise the car interior as belonging to their car. This can be important point in convincing senior stakeholders that the a model rebuilt to the FAST standard still belongs to them.

6. You will segregate different parts of the model according to their different purposesA model that needs to accommodate forecast data and actuals data needs careful design. Forecast data is calculation heavy; actuals data simply needs to be called up at the appropriate place. So I do not want to review forecast calculations that also include actuals data – unless the forecast is driven off the latest actuals.

A fluent FAST modeller will segregate according to purpose. When calculating ratios, ask for what purpose they are to be calculated? If for covenants as documented in a loan agreement, then the key question becomes “when?” On what date is the ratio to be calculated? Answer: align dates with what the loan agreement says.

If ratios are to be calculated for analysis purposes then the modelling might be quite different. Here the answer to the question “when?” might be whenever it is sensible and useful to do so e.g. whenever the denominator of the ratio is greater than zero.

7. You will minimise the amount that you type. A fluent FAST modeller is a great scavenger. They are always looking for things that they have modelled previously that they can recycle. They will make heavy use of “find and replace” (ctrl + H) in order to spot and minimise spelling mistakes. If a model contains a spelling mistake, what other mistakes have been allowed to creep in undetected?

And a fluent FAST modeller will never indulge in keyboard repetition – the sort of “tap – BANG!; tap – BANG!; tap – BANG!” keyboard technique that is too often the sign of someone that needs to review their working practices.

8. You will minimise the number of counterflows in a model. A FAST model is designed to be read from top to bottom and from left to right. Counterflows are links that refer to calculations on the same worksheet but below or to calculations on a different worksheet to the right. I think of it as looking ahead in your workings and knitting an answer back into the model. It increases the risk of circularlity.

A fluent FAST modeller will recognise that counterflows are necessary – especially when using corkscrews or calculating tax. But they will know when counterflows arise and will take steps to minimise them.

9. You, the fluent FAST modeller, hold the model’s intelligence. I do not expect my car engine to decide when to start. Instead, I expect the model to respond to my instruction. Hence, use macros as a last resort.

When building a model, a fluent FAST modeller will assume that the model is incorrect and needs to be checked. They will check the audit trail (Alt M P) as they put calculation blocks together; they will review calculations by charting them (F11); they will use row totals when modelling flows and flags.

They will remain in control of when numbers change. Hence, fluent FAST modellers work with calculation set to manual.

They will use the simplest functions they can find. Someone once picked me up when I commented that OFFSET was a poor function because it was too powerful. A fluent FAST modeller will build a model that uses brittle formulae – they break easily when garbage is fed into them. They show errors when the model is no longer working properly.

I described these as nine things not to think about when you are a fluent FAST modeller. However, thinking about them – in particular when you are not modelling – makes you a better modeller.

free financial modelling course 31 days to better financial modelling

Andrew Berkley
Andrew Berkley
With a background in business education and financial advisory work, Andrew leads the senior team at F1F9. He has been with F1F9 since 2013.