Here's How a Real Financial Model Actually Works

Contrary to what some people think, the modeling courses and classes you take online / at Unis will teach you the core basics -- but the reality is that they are not a good representation of the architecture of a real model. So I wanted to toss some clarity out there to my fellow monkeys.

Credibility: I've had extensive exposure to such courses during recruiting. I have 2 yrs exp in growth equity and 1 yr experience currently in IBD MM M&A (all AN-level). 

But before I get into the specifics let me just disclaim: I can only speak for my bank and the GE fund I worked at. So by all means, if you disagree or had different experiences, please fell free to share. 

Ground Zero) How it all beings:

For MM purposes, it all begins with something called a trial balance. This is the record of all of the company's general ledger accounts in a given period. It's what we use to build our own financial statements, with our own proprietary classifications of such accounts. Yes, that's right, because not all companies are audited in MM, we do not just take the yearly IS, BS, CFS and take those "as-is" from accountants. We actually check their work, re-classify things and build "sexier" and more concise financial statements.

If the financial statements are the nucleus, think of the trial balance as its chromosomes carrying DNA. If the TB is fucked, your Financial Statements will be fucked.

(This is not always the case for BBs where clients are audited by top tier accounting firms).

Part 1) The Model Architecture  

Financial modeling is like an art, there is no right way to do it. It changes from bank to bank, from monkey to monkey, from industry to industry, and from business model to business model. But I will try to lay out the most common architectures in here. It can be broken down into sections (or tabs, although I don't like that name because one could break and consolidate contents into more or less tabs): 

A) Base Data / Historical Financials Section:

This is where you will put in the consolidated historical FS's you made from the TB's. It typically has 5 years of historical data, and it can be monthly or yearly FSs. Normally yearly, but in some situations where cash flow matters a lot and where time is of essence, monthly can be more common (i.e: RX / distressed M&A).

B) The Projections Section:

This is the tab where you will pull all of the historical data in Section A and project the financials into the future. This is by far the most robust part of the model. It will contain things like the Revenue Build, Opex projections, Working Capital Roll-Forwards, D&A and Capex Schedules, Debt Schedules, Tax Schedules, and so forth. In my bank, we give this part a lot of detail not because it will have a huge effect in a valuation per se, but because it allows us (or potential buyers) to see exactly what affects what. For instance, how an increase in capex will influence the level of revenue growth for an equipment rental company, or how optimizing product mix in a fertilizer company will increase margins, so you know which products to focus most on.

The catch here is that you will add A LOT of granularity in this section. It's not uncommon to see 3000+ rows in this "tab". The focus here is to project things sufficient enough to build a cash flow statement later on, and to do it at a micro-level because that allows you to be more precise, so that the macro lines are more precise. And you will use something called a Control Panel to do it, which brings me to our next section.

C) The Control Panel Tab:

The control panel is... well, the control panel. It controls shit. In part B, you're pulling the actual numbers from part A, in the CP, you are pulling the historical margins and growth rates, and tossing them back in the Projections Tab, which will then use such percentages as multipliers for future forecast. For instance:

In the projections tab you pull historical R&D expense from historical years 2018 - 2023, and you will divide that by net revenue to find that, on average, R&D is 2.7% of net revenue. You will then hard-code percentages in the control panel that go in line with that 2.7% for the next projection years, and those will flow into the projections tab, and only then in the projections tab you will multiply the 2.7% for a given year by the net revenue projected for that year, to get R&D for that year. (Good to add that the whole model will be color coded accordingly, so in this case, 2.7% will be green).

This allows you to add different case sensitivity scenarios attached to the percentages themselves in part C, while not crowding section B with too much shit.  

D) The Operating Model Section:

This is where we will pull all of the historical years and projections together, but in a neat looking way that mirrors the FS's in part A. Here, you will have a consolidated historical and forecasted P&L, BS and CFS. You will then link things with part A, B and within the statements to make the balance sheet balance. You will also add something called a vertical analysis, and some credit stats, like leverage ratios and coverage ratios.

Part 2) The Valuation (I'll assume only DCF for simplicity)

Now we have everything we need to calculate Free Cash Flow (UFCF or LFCF). 

E) The DCF Tab:

This will typically pull EBIT, EBITDA or Net Income from part D, and adjust it to get to NOPAT, and then all the way down to UFCF/LFCF. At which point I assume you know what goes on from here: PV, TV, PV of TV, EV-to-EqVal bridge, etc. etc. Valuation is not the focus here. 

F) Comps Tab: This will flow into DCF tab if using he Multiples Method.

G) The WACC Tab: This will flow into the DCF tab and may also use some stuff from combs tab.

Finally, just want to add that it is all EXTREMELY organized and well formatted, color coded, and mistake-free (at least at my bank). 

Conclusion:

There's a million ways you can customize this. And yes, some people will say only a few key numbers matter (like revenue growth, LT g, WACC). But in reality, that's not true. Every number matters because the model helps you understand the business. There is no point in paying a great value for a great company if you don't understand the flows and can't manage the company post-close. In fact, when sharing the model with potential buyers, we don't even share the DCF tab, we give them everything up to part D and let them derive their own valuation (and that's industry practice).

So the whole model DOES matter. Although that is more of an educated opinion than anything else. Feel free to share your thoughts.   

Cheers and hope this is of help to the Padawans.