Financial Model Formatting

Financial Model Formatting

Everything from color, numbers, formulas, and text have specific formatting logic when it comes to creating a financial model. Great financial model formatting is key to a great financial analyst.

 

A Consistent Color Scheme

For both text and numbers, the following schemes are essential –

  • Blue – best used for inputs that make up historicals, assumptions, and drivers (172.551 or =258.849+9.988-2.624).
  • Black – this color should be used for calculations and references on the same schedule or sheet (C4).
  • Green – references from another schedule or sheet should be exemplified in green (=Sheet1!B3).
  • Red – red represents caution and as such, should be used as a warning to another user or link to another model (=[TTS_v1.0xls]Sheet1!C3) .
  • Dark Red – a deep, dark red is appropriate for a formula linking to databases like Capital IQ (=CIQ($E$2, “IQ_TOTAL_REV”,IQ_FY)).

 

Exact Figures

Excel will always round for you according to the format. For example, if cash is entered as 25.382, excel will present it as $25.4. This will ensure that all calculations will be as accurate as possible while still looking great and clean.

 

Never Input the Same Number Twice

Excel will flow and be dynamic in this sense.

 

Don’t Embed Inputs in Formulas

For example – =F5*(1+10%) should not have the percentage symbol within the calculation. Instead, break out inputs into a separate line item. I.e. the 10% should be input into another cell (G7 = 10%) and should be referenced as – =F5*(1+G7).

 

Number Formatting Basics

Access: Ctrl+1 (Format cells), Number tab

When formatting numbers in a financial model, there are a few basic rules that help keep things simple and clean. To begin with, a good rule of thumb is to utilize excel’s standard types – number, currency, accounting, etc. By doing this, excel will register and display things such as negative numbers in parentheses as the program aligns the decimals between positive and negative numbers.

 

Custom Number Formatting

Access: Ctrl+1 (Format cells), Number tab, Custom

One should use custom formatting for percentages, firm specific formats and more creative presentations to guarantee numbers are computed appropriately in their correct format. A custom number format is created in “Type” after using the access method.

  • # – shows significant numbers if they are present. This is often used to show the (,) as a 1000 separator.
  • 0 – shows insignificant numbers. This is often used when showing decimals.

For example, the number 3.4 typed as 0.0 would be displayed as 3.4 where the number 3.4 typed as #,###.0 would be shown as 3.4.

 

Interpreting the “Code”

  • Semi-colon (;) – this is the code that stands a separator between the positive and negative format types. There can be up to three semi-colon format separators (positive ; negative ; zero ; text).

 

Example

POSITIVESEPARATORNEGATIVE
0.1%;(0.5%)

 

  • Underscore (_) – this creates a space the size of the next character and is used to align decimals for positive and negative numbers in a column.

 

Example

POSITIVESPACE ADDERNEGATIVE
0.1%_(0.0%)

 

 

Text with Custom Formatting

It is common to see letters or words after a number whether its dealing with multiples, basis points, or years. Excel will read these as a “number” and not as a “text”.  For example, the number 3.4 typed as #,##0.0x would be displayed as 3.4x and the number 150  typed as 0 “bps” would be shown as 150 bps.

 

Formatting Matters

Formatting matters because excellent analytical work and excellent presentation are both important for a multitude of reasons. For starters, your work represents you and your firm. Also, sloppy, inconsistent work may give the wrong impression about the work quality. Lastly, the numbers can be 100% accurate, but a poor presentation could lose a deal.

It is very important to take the few extra minutes to format and make the work easy to understand.

 

Move forward!

Learn about the different elements within a financial model, or how to properly set one up: