Rees, Principles Of Financial Modelling

Jul. 18, 2018 9:18 AM ET
Brenda Jubin profile picture
Brenda Jubin
466 Followers

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.

This article was written by

Brenda Jubin profile picture
466 Followers
Brenda Jubin is an independent trader and investor with an academic and business background. She taught philosophy at Yale and was dean of Morse College, one of Yale's twelve undergraduate residential colleges. She then founded Brevis Press, a company specializing in academic press book production. Throughout she invested in stocks and mutual funds. She has now settled into the life of a full-time trader and investor. She also writes the blog Reading the Markets (http://www.readingthemarkets.blogspot.com).
Follow
To ensure this doesn’t happen in the future, please enable Javascript and cookies in your browser.
Is this happening to you frequently? Please report it on our feedback forum.
If you have an ad-blocker enabled you may be blocked from proceeding. Please disable your ad-blocker and refresh.