When building a model, do you prefer to have IS, BS, CF on one sheet or separately?

To current analysts (or former summer analysts):

When you build a model, do you personally prefer to have IS, CF, and BS all on one sheet (vertically listed) or separately on three different tabs?

What are pros and cons to these approaches.

Comments (37)

Mar 3, 2010

One sheet. Much easier to navigate in my opinion.

Mar 3, 2010
Mezz:

One sheet. Much easier to navigate in my opinion.

Should have clarified. This is the way I do it. Though the seperate tabs seems the be more popular.

Mar 3, 2010

Different tabs. Working Cap, Dep Schedule, D+I etc also on different tabs. Some of the numbers on the different tabs feed into your statements, which are interconnected. Styles can vary from firm to firm.

Mar 3, 2010

I have seen both...

I feel like BS, IS and CF on the same sheet looks neat...are there any disadvantages to having all 3 on the same sheet??

Obviously you would have se, wc, dep, debt and interest on separate tabs.

"Whenever I'm about to do something, I think, 'Would an idiot do that?' And if they would, I do NOT do that thing."
-Dwight Schrute, "The Office"-

Mar 3, 2010

One page with assumption/inputs

One sheet with everything, even the debt schedule.

Then output pages

Mar 3, 2010

Interesting comments.

As some of you know, I came from MS M&A...we were specifically told to NEVER model more than one statement per sheet. EVER. Now we had an 'Output' tab that had consolidated items from the 3 statements, but as far as modeling practices go, you should not model on one page for many reasons.

Mar 3, 2010
MoneyKingdom:

Interesting comments.

As some of you know, I came from MS M&A...we were specifically told to NEVER model more than one statement per sheet. EVER. Now we had an 'Output' tab that had consolidated items from the 3 statements, but as far as modeling practices go, you should not model on one page for many reasons.

Do you mind telling us some of the reasons why one shouldn't?

"Whenever I'm about to do something, I think, 'Would an idiot do that?' And if they would, I do NOT do that thing."
-Dwight Schrute, "The Office"-

Mar 3, 2010

Personal preference i guess. But I go with separate tabs.

Having them all on one sheet is easier to work with though...

Mar 3, 2010

My firm does it the same way MS M&A does (according to MonkeyKingdom's post). Personally, I also prefer having the statements on different tabs and one summary (output) tab. I even keep my debt schedules separate from my balance sheet tab.

Mar 3, 2010

Here are some of the reasons you should not model on one sheet...

  • Ideally, you would like to have your fiscal years in the same cell i.e. D4:D8 for example for all of your statements, you cannot do this with a one page model. You can set it up vertically, but scrolling up and down is very annoying
  • If you have a bust in the model, it is much easier to go to the specific tab and jump start your model. If your model busts on a one-tab model, good luck
  • Organization of schedules. Assuming you are building individual schedules, (wc, dep, shares, debt, etc), it would be very difficult to include those in the same tab.
  • Formatting. Let's say you need to change the font on row 12, so you shift+space and ctrl 1 font. If your model is set up horizontally (this is the WORST way to do it) then you are going to fuck up all sorts of formatting.
  • Navigating. I know ppl say 'oh one tab is so easy to navigate'...no its not. If you set your cursor to 'A1' on every tab, a simple ctrl+pgup will get you accross statements, and keep in mind it will be much easier to work with since all the Fiscal years will line up perfectly across tabs. (as opposed to freezing frames in a one page model)

-Hiding cells. In a good model you NEVER want to hide cells. This is bc if someone opens your model, there is no way from he/she to see what you are hiding...instead you need to group cells (alt+d+g+g). The problem with doing this on a 1pg model is that your side and top toolbars get huge.

I could go on and on, but I gotta get back to work!

Mar 3, 2010
MoneyKingdom:

Here are some of the reasons you should not model on one sheet...

  • Ideally, you would like to have your fiscal years in the same cell i.e. D4:D8 for example for all of your statements, you cannot do this with a one page model. You can set it up vertically, but scrolling up and down is very annoying
  • If you have a bust in the model, it is much easier to go to the specific tab and jump start your model. If your model busts on a one-tab model, good luck
  • Organization of schedules. Assuming you are building individual schedules, (wc, dep, shares, debt, etc), it would be very difficult to include those in the same tab.
  • Formatting. Let's say you need to change the font on row 12, so you shift+space and ctrl 1 font. If your model is set up horizontally (this is the WORST way to do it) then you are going to fuck up all sorts of formatting.
  • Navigating. I know ppl say 'oh one tab is so easy to navigate'...no its not. If you set your cursor to 'A1' on every tab, a simple ctrl+pgup will get you accross statements, and keep in mind it will be much easier to work with since all the Fiscal years will line up perfectly across tabs. (as opposed to freezing frames in a one page model)

-Hiding cells. In a good model you NEVER want to hide cells. This is bc if someone opens your model, there is no way from he/she to see what you are hiding...instead you need to group cells (alt+d+g+g). The problem with doing this on a 1pg model is that your side and top toolbars get huge.

I could go on and on, but I gotta get back to work!

  1. One tab models should have every column be a separate year / quarter. Income statement above balance sheet above cashflow, etc. The scrolling problem is easily solved: simply make column A blank and width 3 or so, then have the title of the statement / schedule in column A. Control up or down to easily navigate quickly.
  2. Not that much of a problem. REFs become rather easy to fix the more experience you get working / more knowledge you have of the model.
  3. Don't find this the case. Debt schedules, etc just go below the statements. Note every column should be the same year throughout the tab.
  4. One tab models shouldn't be set up horizontally
  5. See the second part of 1. above
  6. Unless you are nesting hidden rows or columns (hiding a set of rows within a larger set of hidden rows) which you shouldn't do all that much, you would only have a 2 "box" top and sidebar - same as a separate tab model

Back to work for me as well

Mar 4, 2010
MoneyKingdom:
  • Formatting. Let's say you need to change the font on row 12, so you shift+space and ctrl 1 font. If your model is set up horizontally (this is the WORST way to do it) then you are going to fuck up all sorts of formatting.

Horizontally???

Who would even think of that?

I agree with Mezz, MezzKet, Marcus, etc., one tab with separate output tabs.

Mar 3, 2010

All the same sheet is much easier..

--
"Those who say don't know, and those who know don't say."

Mar 3, 2010

I've personally done both and the more complex / large the model gets, the better it is to have seperate tabs because it can be quite a drag to look at all that info on one tab...

on the other hand, if the model is of manageable size, one tab is fairly easy and unlike what monkey kingdom says, you can always line up your cells for the years on a one-tabber, it's actually alot easier...

If you do go with a one tab model, i would suggest you always keep your Column A at about 1.5 width and mark the beginning of each section with an "x" this way when you go to the A column and hit ctrl-up/dwn you quickly cycle through the statements...

If you build a one tab model horizontally like monkeykingdom says, you should be shot dead because you're an absolute idiot... dumbest thing I've ever heard... and your entire model should have the same format, no reason why your tabs should differ in format (unless it's an output tab)...

Mar 3, 2010
MezzKet:

If you build a one tab model horizontally like monkeykingdom says, you should be shot dead because you're an absolute idiot... dumbest thing I've ever heard...

lol

Mar 3, 2010

Depends, I've been building models from scratch for years and I used to put each statement on a separate tab, separate tabs for D&I, D&A, etc... mainly because I thought it was so cool that I built a 10 tab model.

But I saw the light and I always build my whole model on one tab and separate output tabs. The main advantage is auditing your model. Its MUCH MUCH MUCH easier to make sure all your formulas are referencing the correct cells. Its also easier because I know Column H is X year, throughout the model. Its also much easier for formatting the model for print. Its also easier for me to build in checks along the way. I typically have my assumptions up top, then IS, then BS, then CF... then all the ancillary statements, and look-up tables all the way at the bottom.

As for crashing models.... all you need is a circ breaker on your where you total interest line from the D&I schedule feeds into your IS. =if(circbreaker=1, H250, "") If your model crashes you just set the circ breaker to 0, then back to 1 and you're good to go.

Benefits of having it on different tabs:
- its easier for non-bankers to navigate; try walking a CFO through a 3,000 row LBO model

Benefits of having it all on one tab:
- ease of printing
- ease of auditing
- you can Ctrl+H your entire model if you need to change something
- much faster when modeling / building from scratch

A few tips:
1- always use a cric breaker
2- have checks all over your model and have them all feed into one summary section which flags any error and the area (i.e. BS, IS, CF, D&I, etc...) so Q1-Q4=FYE, BS items: Q4=FYE, etc...
3- make sure the error flags are ridiculously apparent so you never print/paste a section of your model while any errors exist
4- use appropriate font color coding (blue hardcore, black formula, green link from another sheet)
5- take advantage of custom text formats... so a 0/1 toggle displays as Yes/No, or On/Off
6- learn to use offset, match, choose... they are incredibly powerful when used sparingly
7- build your model with a focus on catching mistakes
8- only use a handful of formatting styles
9- build a model so it can be easily used, adapted by others
10- begin a few columns in, so you can use the added margin space for notes/flags/toggles

I second MezzKat on the horizontal modeling and the "X" borders... this makes things alot easier/faster.

Mar 3, 2010
Marcus_Halberstram:

...
4- use appropriate font color coding (blue hardcore, black formula, green link from another sheet)...

...a Freudian slip?

"Whenever I'm about to do something, I think, 'Would an idiot do that?' And if they would, I do NOT do that thing."
-Dwight Schrute, "The Office"-

Apr 2, 2010
Marcus_Halberstram:

Benefits of having it all on one tab:
[...]
- you can Ctrl+H your entire model if you need to change something

If you change the options for the search to whole worksheet (instead of only one tab) you can also Ctrl + H a multi tab model.

Mar 3, 2010

hahaha

Mar 3, 2010

I think the jury is in, vertical tabs have more positives than draw backs... like Marcus said, a 10 tab model may look cool, but it's a pain to navigate change etc. etc... and some people ALWAYS fail at page setups, so the fonts and colums when printed never come out the same from tab to tab, I HATE THAT!

Unless you're monkeykingdom and you like to build horizontally, there isnt much upside to a multi-tab model, at least no real good reason to use it over a single tab version (unless you run out of rows). I think if you can build a single tab model, you've learned how to model cleanly and effectively, utilizing allocated space in a simple and visually appealing manner.

I know from the BBs I deal with regularly, they use standard one tab versions as their templates (aside from output and maybe one or two other tabs)...

And if you color code, most of your model will just be green due to intertab linkage, yea, really useful colorcoding knowing most of your tab comes from other tabs... maybe useful to distinguish one or two cells, but idiotic when your whole tab is green...

Mar 3, 2010

I think most of the pros/cons have been covered in the excellent comments above. Once you get good at modeling you can make your own decision which to use depending on the situation. Generally speaking, the more complex the model, the more likely you are to use multiple tabs. When I teach modeling and in my self study materials (http://www.ibankingfaq.com/financial-modeling-self... - sorry for the cheap plug), I always advise students and analysts/associates learning how to model to build it vertically. Through experience, I have found that this minimizes linking mistakes, makes it is easier to follow how the statements flow together and makes the model easier to debug.

Mar 3, 2010

This turned out to be a pretty great discussion.

Mar 3, 2010

I use single page for anything simple. The issue with single page for more complex transaction is that you often do balance sheet adjustments and incorporate stub period which can mean you have columns you use on some statements and not others.

Mar 3, 2010
jws43yale:

I use single page for anything simple. The issue with single page for more complex transaction is that you often do balance sheet adjustments and incorporate stub period which can mean you have columns you use on some statements and not others.

Great point.

Mar 3, 2010

I dunno what you guys are talking about.... it may take some finagling but 99.99% of deals can be modeled on one spreadsheet. I hardly think of a "complex" model one that includes a stub period.

While we're on the topic though... is there a shortcut to toggle between split frames in an excel? When you have a 1000+ row model, splitting it into 2 panes makes it much easier, but is there a way to toggle between the 2 without using your mouse?

Mar 3, 2010

Depends on how complex the model / the company

If you try to build a metals and mining company on the same sheet - ur fcked

Then again I have 4 screens so I can do that

Mar 3, 2010

No shortcut - split frames are not 2 separate sheets or anything - if you move past a frame you skip into the next one so excel doesn't differentiate

Mar 3, 2010
InassessableTangents:

No shortcut - split frames are not 2 separate sheets or anything - if you move past a frame you skip into the next one so excel doesn't differentiate

That defeats the purpose of splitting the frames.

Mar 3, 2010
Marcus_Halberstram:
InassessableTangents:

No shortcut - split frames are not 2 separate sheets or anything - if you move past a frame you skip into the next one so excel doesn't differentiate

That defeats the purpose of splitting the frames.

If you're splitting the sheet (Alt W S), use F6 to jump between the splits. If you're freezing the panes (Alt W F), dont think you can.

Mar 3, 2010

Marcus,

What are you modeling? How did it get to 1000+ rows?

Mar 3, 2010

a regular full blown LBO model is ~800 rows, doesn't take much to push it over by 200 rows...

Mar 3, 2010
MezzKet:

a regular full blown LBO model is ~800 rows, doesn't take much to push it over by 200 rows...

Yeah, you're right...

I'm rather used to having the complete consolidated financials on a single tab, with any valuation(s), assumptions, or other outputs on a separate tab.

It takes a while to get used to the stacked single-tab model, though.

Mar 3, 2010

Could be because I'm new to really complex models and maybe because i learned on a multi-tab, but i find them much more elegant and not really that difficult to navigate using Ctrl + [ and F5 to jump around.

A lot less scrolling to get through all the junk, can just ctrl page up and down. Maybe throughout time i'll try the single tab...

Mar 3, 2010

Just out of curiosity, do private equity firms (MM size) have standardized styling format for their models (i.e. color coding of headers, lines and etc.) I know banks do, but I feel like private equity firms may be less-centralized in terms of formatting because most of their analysts/associates come for a variety of different investment banks which have different styles and etc...and the fact that PE analysts and associates have more ownership over their work.

Any current PE analysts/associates+ care to answer?

"Whenever I'm about to do something, I think, 'Would an idiot do that?' And if they would, I do NOT do that thing."
-Dwight Schrute, "The Office"-

Mar 4, 2010

Different tabs for me and an Output sheet.

Mar 4, 2010
Comment