Financial Model Formatting – Numbers
What is “Financial Model Formatting – Numbers”?
The big number categories in financial models are historical/hardcode numbers, assumption numbers, formulas, and formulas that link to another sheet. Model builders, reviewers, and users need to tell the difference between them immediately. This makes reading the model easier and making changes to the model much more straightforward.
Below are the color formatting styles commonly used in many financial models:
- Blue – Historical numbers, or any hardcoded numbers, are colored blue
- Blue with a colored background – Assumptions (which are hardcodes that could change) are blue with a colored background as well
- Black – Formulas or any calculation are colored black
- Green – Formulas that link to other sheets are sometimes colored green (if not, they are black)
Key Learning Points
- Formatting different number categories in a financial model make it more user friendly
- There are commonly used color conventions for different number categories
- Formatting different number categories make a financial model easier to edit
How Do You Format The Numbers In A Financial Model?
Here is an excellent example of a simple model with clear number formatting:
You can find the Excel shown above in the downloadable file accompanying this article and use it to practice your formatting.
Historical numbers – The first numbers entered in the model are the historicals, in rows 12 and 13 above. These are facts with a source that can be relied on, and they shouldn’t change.
They are also hardcoded inputs i.e. they haven’t been calculated from other numbers or cells. Hardcoded inputs don’t necessarily have to be historical numbers, but they usually are.
Assumptions – These numbers are hardcoded, but they are assumptions or estimates of what might happen in the future. For this reason, the numbers may change. These have a blue-colored font and usually have a colored background. The example above is a light blue background, however, different models use a variety of background colors.
Formulas – Cells that contain calculations, functions, or link to other cells are colored black. These can be seen in the diagram above in cells C5:D8, E12:G13, and in row 14. We have made the formulas from column G visible in column H. It does not need to be a complicated formula though. Even a cell that simply links to another cell? That’s a formula and will be colored black.
Links to other sheets – Some modelers like to show these in green, to make it obvious which cells are linking outside of the current sheet. This also makes it easier to work out which cells are just linking within the current sheet. This does not happen in all models though, in which case this type of number will just be colored black.
How Do You Color Code Financial Models In Excel?
To learn more about formatting, see our blog “Financial Model Formatting”, and to do it quickly and efficiently see the blog “Financial Model Formatting – Cell Styles”
Summary
Differentiating the color format of numbers makes it easy for other users to see which cells are which.
Formula cells may have complicated functions inside which could easily have errors and need careful checking.
Assumptions are much simpler, but we spend a long time trying to get them right as they have a large influence on the outcome of a model.
Historical numbers should be sourced and tied easily to a source document.
Formatting these correctly makes it much easier for users to work out what is going on, and they will be forever grateful if you do this from the start.