Rees, Principles of Financial Modelling

In the old days banks wanted applicants to be comfortable with Excel. Now they’re upping the ante. Citi, for instance, wants its incoming investment bank analysts to know Python. But Excel hasn’t gone the way of the dodo. It’s still incredibly useful for a range of financial tasks. The problem is that most Excel users have no idea how to go beyond basic formulas and engage the program as a high-level tool. In Principles of Financial Modelling: Model Design and Best Practices using Excel and VBA (Wiley, 2018) Michael Rees sets out to fill this void.

Rees’s more than 500-page book is divided into six parts: (1) introduction to modeling, core themes and best practices, (2) model design and planning, (3) model building, testing and auditing, (4) sensitivity and scenario analysis, simulation and optimization, (5) Excel functions and functionality, and (6) foundations of VBA and macros. As these part titles indicate, Rees first addresses financial modeling, from design to optimization, and then explains how to use Excel and VBA to implement the models. Complementary to this book is a website, which contains 237 Excel files.

Here, to give a sense of the book, I will summarize Rees’s distinction between database and formula-driven approaches to modeling.

Traditional models, for instance those used for cash flow valuation, are formula-focused. They “often have a small set of numerical assumptions, from which large tables of calculations are performed. Certainly, where a single value is used for an assumption across multiple time periods (such as a single growth rate in revenues that applies to all future time periods), arbitrarily large tables of calculations may be generated simply by extending the time axis sufficiently, even as the number of inputs remains fixed.”

Where a large volume of data is required, however, the appropriate model will use “database concepts, functionality or data-oriented architectures and modular structures. These include the structuring of data sets into (perhaps several) contiguous ranges, using a column (field)-based approach for the model’s variables (with well-structured field identifiers, disciplined naming conventions, and so on).”

Even though in practice these two approaches to modeling can sometimes overlap, with the modeler confronted with both large data sets and potentially many formulas, Rees contends that “at the design stage, the reflection on the appropriate approach is fundamental: an inappropriate choice can lead to a model that is inflexible, cumbersome and not fit for the purpose.”

Before they set out to build models in Excel, analysts would do well to read Rees’s book. With its help, they will avoid many pitfalls.