Group and Outline – Essential tool for large financial models

The world of professional financial modellers have most core values aligned – simplicity, transparency, flexibility etc – but are divided when it comes to deep or shallow sheets.

As with everything else in the world there are pros and cons with both approaches.

What is a deep / shallow sheet?

A financial model is said to have a shallow tab structure when there are a smaller number of calculation rows on every tab. Some modellers work with the rule of thumb that every sheet should read like one page in a book and print on one page.

Image 1: Deep tab structure (smaller number of tabs)

Image 1: Deep tab structure (smaller number of tabs)

Image 2: Shallow tab structure (larger number of tabs)

Image 2: Shallow tab structure (larger number of tabs)

A deep sheet structure is based on a categorization of content into chapters instead of pages. Every sheet could contain thousands of rows.

Financial models with deep sheets

  • It is easy for a user to get an overview of the model structure without scrolling
  • Navigation is simple as you can see all sheets on one screen
  • There is no need to create a Table of Contents sheet or similar with hyperlinks to certain parts of the model
  • It is easy to assign responsibility for one sheet to one person
  • There is a risk that you loose transparency if Grouping is not performed successfully

Financial models with shallow sheets

  • There is a risk that excessive repetition of information is required
  • The number of off-sheet references may increase
  • With a good Table of Contents and navigation system the navigation can be very easy to use, in particular for novice users

Which structure is better?

You can’t say that one structure is definitely better. My preference is definitely to work with deep sheets but many very talented modellers do a good job with shallow sheet structure – it is just not the way I like it!

Work with Grouping to improve deep sheets

Working in a financial model where some sheets contains thousands of rows will quickly frustrate users (and make their scrolling wheel on the computer mouse red hot!). To avoid this I prefer to use the Grouping functionality.

The Grouping functionality introduces small ‘+’ symbols in the left hand column of Excel and clicking these Expands the particular section.

Image 3: A deep worksheet with Grouping

Image 3: A deep worksheet with Grouping

How to apply Grouping in Excel

Select the rows you want to group (Shift+ Space is a great Excel shortcut for this).

deep-tabs-2

Select Data -> Group and Outline -> Group

deep-tabs-3

Done!

The Excel keyboard shortcut for Grouping is Shift + Alt + Arrow Right. To Ungroup it is Shift + Alt + Arrow Left.

Recent posts by Rickard Wärnelid

Tags: ,

Comment on this Article