Investment banking & Venture Capital aren’t easy jobs; you need to have a complete mastery of sectoral knowledge attained through in-depth research and financial models, which we believe is the top skill set required to succeed in those positions.
Financial modeling and preparing pitch decks are the non-core tasks that you would, later on, find yourself doing the whole time.
Once you have researched the sector and the company in which the company lies, preparing the financial models becomes much easier.
When we say easier, we refer to the financial model’s assumption sheet.
However, apart from the assumptions, you need to look at many different perspectives to make the financial model appealing.
The reason is quite simple: in real life scenarios, you are working on the same financial model year after year. Apart from this, the model should also be easily understandable to your colleagues should you abruptly leave the organization.
Since most Investment Banks are associated with startups for a long time, preparing detailed and clean financial models becomes extremely important.
What is a Financial Model?
Financial Models are detailed summaries of company finances in the form of a spreadsheet detailing the historical business data and forecasting future projections.
The different types of financial models that you would prepare are:
- Three Statement Model
- Discounted Cash Flow Model
- M&A Models
- Initial Public Offering Models
- LBO Model
Even though each of these models has a different purpose, the initial precursor to them is building the three-statement model, i.e., the cash flow statement, balance sheet, and income statement.
Generally, most people begin with the Income statement, build the historic revenues and cost, and then build the projections using the key drivers or the assumptions.
Later on, the balance sheet and cash flow statement are built, which obviously depends on interlinking the values in all three statements.
Apart from this, a user can also prepare a depreciation schedule, working capital schedule, PPE & intangible schedules, equity schedules, and even interest schedules.
Tips for preparing Financial Models
This article will guide you on the tips and tricks you must follow to prepare financial models.
KISS ASS:
Believe us, it is not what you are imagining. The acronym is “Keep it simple, silly to avoid sophisticated stories”.
Everything has a story behind it, from pitch decks to financial models, and this story is what gets sold to the investors. If the financial model can’t convey the story or general information, expect negative impressions from the person reading it.
A financial model should have easily readable formulas with a flexible structure in place.
Populating of values in financial models
The easiest way to differentiate between different values is by color coding the inputs, referenced values, and calculations in the financial model.
It is a Wall Street practice to hardcode values in blue while all the referenced values are represented in green.
Finally, all the calculations are kept in black font, differentiating them from the other two.
Add comments wherever necessary.
This one is our personal favorite. Let’s imagine a scenario – You return back to work on one of the financial models after a year, however, you are unable to remember the logic behind the assumptions.
Hadn’t it be wonderful if you had just added a couple of comments to explain what assumptions you made a year back so you can make the same assumptions again?
Alright! You have a good memory, but what if you are on leave and your colleague is working on the model? Think of it anyway, but a financial model that’s not well documented will always be difficult to use.
Just press the Shift + F2 key on your windows, allowing you to add a comment immediately. You might have learned about this in one of those Excel training sessions!
Let the model flow like water:
It is quite common sense that the financial model should flow from left to right direction and should be oriented from top to bottom direction.
There is quite an interesting story about why humans have developed to write from left to right.
Even though Hebrew & Arabic were initially written from right to left on stone tablets using the chisel on the left and the hammer in the right hand, the discovery of ink and paper enabled homo sapiens to interchange this writing orientation.
Left-to-right orientation enabled the writers to see what they were writing without smudging the ink, which wasn’t possible in the right to left.
This is how humans got used to left-to-right orientation and why even financial models are built in a similar way!
On the other hand, use consistent headings for the financial model, which makes the structure even better.
Formulas & errors in financial models
If you had a really good initial Excel training, one of the most tempting things to do is write a unique formula in each cell.
Always avoid writing unique formulas or rather just use one formula and drag it across the ‘n’ number of cells in which you are projecting revenue, or the cost of goods sold.
Another thing that you need to focus on is the errors that the model might return once you use different formulas to interconnect values.
Some of those might return the #REF! Error which basically means reference errors, while others would be #VALUE! or even #NUM! Error.
Doesn’t matter what errors you have; it is always advisable to work on them individually so that the final product that you have is free from all the errors.
Circularity:
A circular reference is created when the user references cells in the formula that ultimately is dependent on itself directly or indirectly.
For example, let’s take the B = A + B equation. We need to add two values, A & B, to get the result for B. However, we do not know the value of ‘B’ yet, which creates an indefinite loop called the circular reference.
However, the circular references can be tackled using Iterative Calculations. Excel will avoid displaying the circular reference error if the functionality is enabled from Menu > Options > Formulas > Enable Iterative Calculations.
Sometimes, if multiple sections of the financial model are interconnected all the way, an error can occur during the downstream calculations.
Such circular reference errors can be addressed using the IF function and VBA buttons that will automatically reset the calculations if there are circular reference errors in the financial model.
Add Table of Content & other explanations in financial models
Whatever sections are built into the financial model, ensure that you add a table of content in the beginning to help the model easily communicate with the reader.
This will also ensure that the reader knows what different sections are expected in the financial model beforehand and can also surely add any missing sections.
Any other explanations that you deem necessary for the model to work should be included in a separate spreadsheet or information boxes if you are uncomfortable with adding comments.
Now that you know the absolute essentials of building robust financial models, go out there and bring in some income in your next paystub!
