Financial Modeling Aesthetics - Color Coding
Formatting excel models is an art in itself. Properly formatting excel models is important because formatting makes it easy for others to read and understand the analysis. One would realize its importance when one sets out to make changes to the model created by him/her.
Working with Excel models is often collaborative, either in creation or in their use. It comes as no surprise that there are few universal standards for formatting points – such as color coding formulas vs. constants but not for other aspects. Different groups at different firms still use slightly different standards.
Proper formatting is most important and perhaps the soul for actual financial models than for other types of data sets (sales reports, customer details etc.,) but still makes your data in this case very readable and easy to use.
If you are an Investment Banking professional, formatting reflects your professionalism and that of your firm. Not just in Investment Banking Aesthetics play an important role in any client driven business. It always pays to make your analysis well and models properly formatted to make them easy-to-read and highly professional.
We’ll cover color coding in today’s article and go forward with Number, text and other important formatting options.
Color Coding in Models
Even if you’ve got everything wrong and your model is wildly inconsistent, get your color coding right. By color coding, you can convey the information of what can be edited and not, what is important and not, etc.,
This makes both auditing and reading the model very easy.
Links, Constants and Formulas
Blue Font: All manually entered (hard-coded) numbers; used for historical financial data and also for many input cells in the “Assumptions” part of models.
Black Font: All formulas; also used for formulas that include links to other worksheets or workbooks, but which are not direct links (i.e. if you’re adding two numbers that are both from other spreadsheets)
Green Font: All direct links to cells in other worksheets or workbooks
It is preferable to leave the text in Solid Black colour. Yes, technically it’s hardcoded but using Blue font makes it difficult to read text. However, if there’s an input cell that accepts text, we do use the blue font colour.
There is no built in formula to help you do this, you have to either record a macro or use VBA. Below are the steps to record the macro.
- Press Alt + W + M + R (or Alt + T + M + R, no equivalent on the Mac) to name and begin recording your macro.
- Press F5 (“Jump to Cell”) and then Alt + S on the PC, or ⌘ + S on the Mac, to go to the “Go to Special” menu.
- Press “O” (or ⌘ + O on the Mac) to select Constants and “X” to uncheck text.
- Now press Alt + H + FC (or Ctrl + 1 on the PC, or ⌘ + 1 on the Mac) and select a Blue font color for these constants.
- Press Esc.
- Now do the same thing, starting with F5, but select Formulas (F, ⌘ + F on the Mac) instead of constants and press “X” to uncheck text.
- Now press Alt + H + FC (or Ctrl + 1 on the PC, or ⌘ + 1 on the Mac) and select a Black font color for these constants.
- Stop recording the macro with Alt + W + M + R or Alt + T + M + R.
It is tricky to find links to other workbooks and worksheets, and most likely, VBA has to be used to get this working correctly.
The basic idea: search for “!” in each cell that contains a formula, and then change the font color to green.
But with the way macros work, you would need to modify this in the VBA Editor and make it a For
Each loop through all instances of “!” you find, and then change the font color for all of those.
Financial Analyst GSA PBS
Planning Manager Bata