347 647 9001+1 714 797 8196Request a Call
Call Me

Financial Modeling Case Study: How Mr. Frank Lost Million Dollars

May 28, 2013
, , ,

Attend our FREE Upcoming Webinar on “Learn Some of the Best Practices in Financial Modeling” 

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 off”.

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.

P&L Statement

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

Correct P&L Statement

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.

Correct P&L Statement Formula


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

Way Forward:

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.

For further info visit us at: Financial Modeling Master Class

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, matches mine or not! clip_image005

For any queries regarding the cash impact or financial modeling, feel free to put the comments in the blog or write an email to


About the Author

Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.


Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc

CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.

Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to and we will rectify it.

Popular Blogs: Whatsapp Revenue Model | CFA vs CPA | CMA vs CPA | ACCA vs CPA | CFA vs FRM

Post ID = 28221