Learn 100% online from anywhere in the world. Enroll today!

Financial Model Formatting

Guide to formatting financial models

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 being a great financial analyst.

 

financial model formatting

 

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 to 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 in Financial Model Formatting

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 neat 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. 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 Financial Model 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 and is often used to show the (,) as a 1000 separator
  • 0 – shows insignificant numbers and 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 as 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 it’s dealing with multiples, basis points, or years. Excel will read these as a “number” and not as “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.

 

Financial Model 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 properly and make the work easy to understand.

 

Move forward!

Thank you for reading this CFI guide to financial model formatting. Learn about the different elements within a financial model, or how to properly set one up, from the following CFI resources:

  • Financial modeling overview
  • Financial model formatting basics
  • Financial model formatting advanced
  • Financial model formatting numbers

Financial Modeling Certification

Become a certified Financial Modeling and Valuation Analyst (FMVA)® by completing CFI’s online financial modeling classes!