Start With the End in Mind

Starting with the end in mind is one of the simplest ideas that is frequently ignored. I have seen so many analysts begin designing a financial model without having a clear understanding of the purpose of the model. A simple example may make this clearer. 

When I was a young financial analyst in the mergers and acquisitions department of the largest company in its industry. One of my primary jobs was to prepare valuation models. Sometimes, we were in the preliminary phases of an acquisition, researching the target and the competitive landscape. In those scenarios, my goal was to establish an acquisition purchase price that was a good deal for our company. 

Many times, however, the deal had already been agreed upon by the CEOs of our company and the target company. They had roughed out a price based on a few simple rules of thumb like revenue or cash flow multiples. If that was the case, my goal was simply to build a financial model that resulted in a valuation aligned with what was already agreed upon. To be clear, if the model and resultant valuation showed the agreed-upon price was a bad deal, our CEO would use that information to renegotiate the deal. Still, usually, we just built the model to hit a target outcome. 

Understand your end goal before you start building any financial model, or you may spin your wheels and end up rebuilding it.

Types of Financial Models

There are a wide variety of financial models you could use. I’ll focus on just three, and when you should consider using each.

Judgement-Based Models

Judgement-based models, sometimes referred to as human models because they rely on human judgement or expert judgement models, are frequently used in situations where resources make it impractical to use other models and where experts are able to provide the requisite inputs for the model. In this type of model, you, as the modeller, would ask the expert for the value to input in your model. It’s simple and quick, but you better have a good expert, or your results may not be very useful.

Statistical Models

Statistical models are fueled by historical data and statistical analysis. Regression models are an example of statistical models, and spreadsheets have functionality that allows you to do regression analysis as part of your financial modelling. 

Risk management professionals frequently use this type of model, as do weather forecasters. When used properly, these models can be very accurate and reliable, but you always need to keep in mind that there will always be unforeseen circumstances that cause variability.

Driver-Based Modeling

Driver-based modelling is characterised by using formulas that rely on a thorough understanding of the relationship between the independent and dependent variables used to model outcomes. Driver-based models make what-if analysis a breeze when they’re designed properly.

Designing Models: A Few How-Tos

Here are a few of my favourite tips to keep in mind when you’re designing your financial models. Organisation and documentation are critical factors in effective model design because if something is complicated enough to require a model, it’s likely the model will get complicated enough to get unwieldy if you aren’t careful. 

Organisation

Organise your assumptions in one place, and make sure you explain them clearly. Assume someone will have to use your model without the knowledge you had when building it because that’s likely to happen. You will eventually get promoted, change jobs, or leave your position for various other reasons, so plan for this eventuality by documenting your model well.

Number Every Line, Even Blank Ones

One way I have made things easier on myself and others when designing models is to simply number every line item in the model, including blank rows. Yes, spreadsheet programs have rows and columns identified in the program on the screen, but when was the last time you had to refer to a report without those to reference? It’s much easier to direct someone to line eight instead of trying to estimate how far down the page to focus someone on some obscure expense line item, like COS-Other?

Range Names Are Your Friend

When using spreadsheets, use sheet and range names and make them descriptive. A simple example will say all that need to be said about this. Which of these formulas is easier to read and understand?

=Assumptions!L6*IF(Allocations=0,Allocations!$L$7,Allocations!$M$7)
=Sheet3!L6*IF(B4=0,Sheet5!$L$7,Sheet5!$M$7)

You can tell just from the names used that this calculation involves allocations calculated based on certain assumptions on another tab. That is valuable information when you’re reviewing a spreadsheet, and you don’t want to spend a lot of time just understanding the basic flow of the model.

Technologies Role in Financial Modeling

Many people are still using spreadsheets for financial modelling. Consider when it may make sense to transition your models to a different tool. Large-scale modelling in spreadsheets may become problematic for a variety of reasons. There are many tools available that have been purpose-built for financial modelling that are improvements on the standard spreadsheet tools most modellers use. Many offer free trials, so do your homework and choose the right tool.

Knowing your goal and the appropriate type of model to build, you’ll be off to a great start. Follow some of the basic how-tos I shared, and you’re on your way to improving your model building.