Financial Modeling Case Study
Everything went well, until it collapsed!
Frank Romano working in the financial services industry for 12+ years served in senior positions at the boutique investment bank and worked with big Fortune 500 clients. At the age of 40, Frank decided it was time for him to carve out a space for himself in the industry. He quit his lucrative job, hired a couple of fresh graduates and started his own Boutique IBank, Finmoco LLP.
He already had developed close relationship with some of his previous clients. The first person to whom he pitched, Joe Longo, a longtime client of Frankâ€™s agreed for an M&A advisory for his company. Frank was to prepare a model for the target company and a structure for the deal. Joe would personally present the proposal to the board, and if everything went well, Finmoco would have earned a million dollar fee for the deal! Hmmm starting Investment Bank would finally make him a rich man!
Frank provided his inputs and told his analysts to start preparing the model, while he pondered over how he would use the million dollars! Maybe he can get a good villa to live near Manhattan!
The model was done and Frank gave a cursory look at the assumptions. Most fresh analysts goofed up on the assumptions. But he had hired a Gem. Quite pleased with the outcome and immediately mailed the model to Joe.
A week later Frank received a curt line from Joe, â€œThe deal is office.
A bewildered Frank hurriedly opened the Excel sheet and it took him just seconds to identify the sloppy blunder that cost him his million dollars.
Frank sat there holding his head in his hands and staring at the screen in disbelief!
There is no space for Sloppy work in Million Dollar Deals
In the P&L statement, the analyst had first projected the revenue for future years, then operating expenses, and used that to calculate EBITDA. From the EBITDA he subtracted the estimated amounts of Depreciation and Amortization. He calculated EBIT from this and after assuming a suitable interest rate, he estimated the annual interest payments.
So far so good! And this is how the P&L statement looked.
Table-1: P&L Statement of Target Company
The Analyst just didnâ€™t handle the boundary condition and the Taxes Got negative. This is enough to get you a Million Dollar Slap on the wrist!
This is commonly occurring mistake in modeling in Excel.
Handle the Boundary Conditions
Table-2: Correct P&L Statement of Target Company
To correct this, the formula for the Tax column was changed. The correct formula is shown below. It now ensures that in case of negative EBT, a value of zero is taken for the Taxes particular.
What can we learn Today?
While building financial model one has to give attention to details and make use of best practices in the financial modeling.
Formulas entered into excel should be written such that they account for all possible input values. e.g., in this case we modified our formula to ensure that we treat negative values of EBT differently when calculating tax.
IF function is Excel is particularly useful for condition based calculations.
Other best practices
Some other best practices to follow when doing financial modeling in Excel are:
- Color coding should be used to make proper differentiation between assumptions and calculations
- Input cell naming: Ensure that whatever cells are used to input data into the model are named so that they can be referenced easily
- Proper linking: Cells should be linked from left to right and top to bottom throughout the workbook in formulae
- Particular year should be specified in the same column throughout the workbook
- Grouping: Use the Group feature instead of deleting or hiding rows that you donâ€™t want to display
This series gives you a flavor of possible errors in financial models and best ways to avoid such errors. To learn more about financial modeling, consider having a look at this exclusive offer. EduPristine, a name trusted by Fortune 500 Companies, is launching its time tested Financial Modeling Master class in US.
This 2 Day Financial Modeling Master class, aims at imparting its participants the ability to apply modeling techniques in a wide range of practical scenarios. Candidates will learn the latest tools and techniques of building financial models using excel to depict financial statements and investment analysis.
Or Call +1 347 647 9001 / +1 551 226 2928 to know about latest Offers & Discounts.
Templates to Download:
I have created a template for you, where the subheadings are given and you have to link the model to get the cash numbers! You can download the same from here. You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).
Also you can download this filled template and check, if the information you recorded.