Financial Model Formatting: Colors​

Color coding is essential to a financial model

Why is formatting important?

Formatting, which refers to the way the 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 as such 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.

Examples of this in practice can be found in our financial modeling courses.

 

financial model formatting

 

Conditional formatting

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 highly disastrous when a company incurs a loss in a certain period, the user can set conditional formatting for the net 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, the cell will be formatted in a bright red. This conditional formatting allows users to quickly spot any red flags within the model.

The great thing about conditional formatting is it bestows a great deal of creativity to the user in how they 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.

 

 

Move forward!