The FP&A Trends Webinar. Five Critical Roles for Building a World-Class FP&A Team
Click here to view details and register
By John Sanchez, Keynote Speaker, Corporate Trainer and Author
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.
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 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 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 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.
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.
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?
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.
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.
Financial model definitions can be tricky. Financial models are often dependent upon numerous functional areas and...
During the recent years, the level of detail and precision which financial modeling in the business...
In this article, I want to make the case for data-driven planning to describe the 7...
Although there are some substantial differences between Financial Modelling and Predictive Analytics, both help us cope...
We will regularly update you on the latest trends and developments in FP&A. Take the opportunity to have articles written by finance thought leaders delivered directly to your inbox; watch compelling webinars; connect with like-minded professionals; and become a part of our global community.