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.
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
| POSITIVE | SEPARATOR | NEGATIVE |
| 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
| POSITIVE | SPACE ADDER | NEGATIVE |
| 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:
