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 in line 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, but 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, 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 modeler 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 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 modeling.
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 is characterized 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 favorite tips to keep in mind when you’re designing your financial models. Organization and documentation are key 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.
Organize 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 a variety of 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 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 be said about this. Which of these formulas is easier to read and understand?
You can tell just from the names used that this calculation involves allocations calculated based on certain assumptions on another tab. That is useful 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 modeling. Consider when it may make sense to transition your models to a different tool. Large-scale modeling in spreadsheets may become problematic for a variety of reasons. There are lots of tools available that have been purpose-built for financial modeling that are improvements on the standard spreadsheet tools most modelers use and many offer free trials, so do your homework and choose the right tool.
By knowing your goal and understanding 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.