Financial model color formatting, which refers to the way the information in a financial model is displayed, helps keep the visual element of the financial model clean and easy to read for the user. As stated previously, the three factors of a good financial model are consistency, efficiency, and clarity. Proper formatting aids in all three of these.
Recommended formatting: text color
The first and easiest method of formatting a financial model is to use a consistent color scheme to annotate different types of cells and data. Here is a recommended color scheme that is quite commonly known amongst financial analysts and other users of financial models:
Blue: Inputs, or any hardcoded data, such as historical values, assumptions, and drivers
Black: Calculations and references to the same sheet
Green: Calculations and references to other sheets (note that some models skip this step and use black for these cells)
Red: References to separate files or external links
Coloring the text and contents of cells helps the user understand the different types of data working within the model, as well as the flow between the different pieces of data. For example, blue text inputs will likely not have any predecessor data, so it becomes a logical starting point to follow formula paths.
Excel has a valuable feature known as conditional formatting. This lets the user set rules or thresholds for certain cells. If these rules are met or the thresholds are triggered, Excel will format the cells in a certain way that the user has set. This becomes useful for pointing out certain pieces of data within the model.
For example, if it is important to point out when a company incurs a loss in a certain period, the user can set conditional formatting for thenet income cells in a model. The rule will be set to check whether the cell’s value is less than or equal to 0, and if it is, then the cell can have a special background shading. This conditional formatting allows users to quickly spot any red flags within the model.
A great thing about conditional formatting is that it bestows a large amount of creativity on the user as to how they choose to implement such formatting. However, it is important to remember not to get carried away with conditional formatting. Ideally, it should only be used to pinpoint or highlight important information, as having too many colors appearing within a spreadsheet may detract from the clarity of the overall model.
Shading color formatting
Financial model color formatting can also be used to shade certain cells for different effects. In the screenshot below you will notice that the Long-Term Debt schedule uses three different levels of color formatting:
Heading – dark blue with white font color
Subheading – light grey with black font
Featured Line Item – light orange with black font
Each of these different styles helps the user of the model to clearly see the information contained in it.