Layout of a Vertical Model

Last week, we talked through a "core structure" for all your models, which were built out in a mostly "horizontal" fashion, which means the model moves from Right to Left, like this:

Output <--- Calculations <--- Data (from Right to Left)

This week, I want to expand on the "Financial Model" tab and talk through "vertical modeling," my modeling style of choice.

Imagine now, that we have clicked on the "Financial Model" tab from the first image.

This tab will often have hundreds of rows, and is modeled in a "vertical fashion," meaning each section sits on top of one another, all governed by a single set of dates at the top.

Schedule Layout

Within this vertical model, we have two primary schedule types:

1. Primary Schedules: the 3 Statements, plus key stakeholder items like EBITDA, Covenants, and Returns
2. Support Schedules: items that feed into the Primary Schedules like Working Capital, Bonus Accruals, Tax Accruals, Capex, Depreciation, Debt, etc.

Why It's Scalable

The beauty of having all your schedules stacked on top of each other is this: to make a multi-year model (usually by month), all you have to do is drag columns out to the right.

Doesn't matter if it's one year, three years, or ten years. It's just a duplication of existing columns.

If your structure for year one is correct, then you can confident that your structure for year 10 is correct as well, and that delivers such peace of mind.

From there, just make sure any horizontally-modeled support schedules link to the correct dates in your vertical model (and I often prefer an INDEX/MATCH or XLOOKUP approach as opposed to direct-linking).

Why Not Everything on One Tab?

The truth is, you could model everything on one tab if you wanted to, but I think a combo of horizonal and vertical is best.

I think it's easiest to house certain types of data in its own place with its own label, for example:

• Actuals
• Budget
• Headcount
• Vendor Contracts
• Detailed Revenue/Expense builds

I think seeing =ACTUALS!A1 is easier to understand than =A2204 (some row way off in Neverland), but the truth is it's a judgement call.

How much you want to include in the vertical model vs. housing in a horizontal schedule is up to you and also dictated by the audience receiving the information.

That said, my litmus test is generally this:

1. Put as much on the Vertical Model as possible, and then;
2. House complicated builds, confidential information, or easy updates (i.e., pasting Actuals) on their own tab, and have it feed back into the vertical model

Action Items

At the end of the day, it all comes back to design.

Look at your latest model, how does it flow?

Do you have something like this:

Output <--- Calculations <--- Data (from Right to Left)

Or perhaps more detailed:

Output <--- Vertical Model Engine <--- Horizontal Support Schedules <--- Data (from Right to Left)

See if you can move things around and optimize your structure.

That's it for today. See you next time.

—Chris

See it in action...

All of my Financial Models are built on the combination of vertical and horizontal modeling to make a highly-scalable financial engine. Go through the courses in detail or just grab the templates, it's up to you.

You can finish everything in a weekend:  see the courses here .

"⭐⭐⭐⭐⭐...This template and course did what I was attempting to do from scratch - combine all of the best "best practices" for 3 statement model building - into one straightforward model."