Building an Effective Financial Model

Early in my career, I took delight in the process of creating large, elaborate financial models. My days were spent building large excel files with multiple dynamic switches covering all types of scenarios, huge chunky sensitivity tables, multiple tabs with large wieldy spreadsheets and different colour codings and conditional formatting formulas built in.. I liked it. It felt like a complicated, sophisticated thing to learn and do.

"I'm almost done with my 50MB, 30 tabs, 10 scenarios model. I just need to fix all these #REFs in the output page"

As I’ve grown in my career, I’ve learnt how to drill in on the things that matter and simplify. This was very largely influenced by a senior Managing Director of mine who gave me some sage advice one day when looking at my model.

Models should be output focused and simple. The purpose of any model should be to facilitate quick and easy decision making, with the ability to hone in on just the few factors that make or break a deal.

Now, I try to keep models as simple as possible, with extra focus on building a clear output page. The output page is the most important page - whether it’s you yourself, your boss, or investor analysing the model, the output page tends to be the first port of call. Typically, other stakeholders don’t have the time nor patience (nor interest) to wade through the other tabs (eg. rent roll, calculations etc).

The output page should, in a very clear fashion, communicate to the reader:

1. The projected operating performance of the asset during the investment period on a yearly basis: Typically, this is contrasted against the historical metrics of the asset for context / meaningful comparison.

   1. The output page should clearly show both the current and projected figures of key metrics, such as occupancy and rents for an office asset, or average daily rate and occupancy for a hotel.
      

2. The key financial assumptions underpinning any investment, such as the intended leverage levels, cost of financing, holding period, capex provisions, currency impact (eg. what’s the expected FX appreciation/depreciation/hedging costs) and exit cap rate.
   

3. The results of the investment case: Measurement of the success of the investment based on typical metrics of profitability, eg. items like IRRs, equity multiples, cash on cash return, and profit (absolute profit, profit psf).

With the above, the reader would then be able to quickly ascertain whether the asset fits the appropriate investment profile and criteria.

A model that is overly complex without sufficient focus on the output tends to hinder decision making.

Example of a simple output layout

The models provided here are built with the above principles to keep it as simple and effective as possible. Hopefully, they will be able to help you in your own investment assessments.