Financial Modeling Best Practices: Color Conventions

Hi guys,

What are color conventions that you guys follow to make your financial models idiot proof?
There are so many links and assumptions that I am finding it difficult to standardize.
Right now I have:
assumptions=blue
Hard coded inputs= blue with a background color
Drivers such as '% of revenue'= green
formulae= black
links to other worksheet in same workbook=black
referencing from other workbook=red

Please share!!

Financial Modeling Text Color Codes

First - it is important to note that each firm / group could have its own preferences - when working you should simply adhear to that format. If practicing modeling - the most important thing is to practice consistency.

Several users shared their preferences / experiences below:

leveredarb:
I'd just have one colour for all hardcodes, one colour for all formula, one colour for links within same worksheet, one for link to external sheets and one for hardcodes that aren't obviously hardcodes (basically non assumption stuff), then footnote the latter as well and explain why you did smthg non standard in the spot.

Devils Advocate - Investment Banking Vice President:
Hardcode = Blue
Formula = Black
Links to other worksheets within the same file = Green
Links to other excel files = Green and sometimes Purple

TylerDurden - Investment Banking Analyst:
Blue = input
Black = formula
Green = link to other sheets
Purple = links to other excel files
red = fix this shit.

User @rufiolove", a private equity associate, pointed out that Red can be used to indicate an important number:

rufiolove - Private Equity Associate:
Don't fuck with this input because it is important and there for a reason / critical plug.

Wall Street Prep:
Partial inputs (e.g =1*E5) are also sometimes formatted differently, but there's no universal convention for that.

Model Formatting Summary

Looking to become a modeling master?

Sign up for our financial modeling training course to learn all this and more. Check out our 15% off discount to Wall Street Prep's Financial Modeling Courses.

Financial Modeling Training Course

WSO Elite Modeling Package

  • 6 courses to mastery: Excel, Financial Statement, LBO, M&A, Valuation and DCF
  • Elite instructors from top BB investment banks and private equity megafunds
  • Includes Company DB + Video Library Access (1 year)

Comments (16)

Jan 1, 2012 - 2:04pm

u have a bit too much overlap/

id just have one colour for all hardcodes, one colour for all formula, one colour for links within same worksheet, one for link to external sheets and one for hardcodes that arent obviously hardcodes(basically non assumption stuff), then footnote the latter as well and explain why you did smthg non standard in the spot.

Jan 1, 2012 - 5:32pm

Blue = input
Black = formula
Green = link to other sheets
Purple = links to other excel files
red = fix this shit.

Jan 11, 2012 - 6:09pm

tylerdurden and devils advocate are right about the most common convention in ib.

partial inputs (e.g =1*E5) are also sometimes formatted differently, but there's no universal convention for that .

I highly recommend the free boost add-in for all excel formatting stuff. We developed it and rolled it out last year for all of our investment banking clients - basically makes a lot of the formatting hell completely seamless so, for example, if you get a model that isnt formatted with the color scheme you want, you just highlight the range of cells that you want to fix, hit a shortcut and it changes it based on your color preferences for inputs, formulas, partial inputs, links to other worksheets and files, as well as protections for "fix this shit" colors.

Matan Feldman Founder, Wall Street PrepLearn Financial Modeling
  • 2
Jan 11, 2012 - 9:07pm

Hardcode = Blue
Formula = Black
Links to other worksheets within the same file = Green
Links to other excel files = Green
Don't fuck with this input because it is important and there for a reason / critical plug = RED

Learn More

300+ video lessons across 6 modeling courses taught by elite practitioners at the top investment banks and private equity funds -- Excel Modeling -- Financial Statement Modeling -- M&A Modeling -- LBO Modeling -- DCF and Valuation Modeling -- ALL INCLUDED + 2 Huge Bonuses.

Learn more
Jan 12, 2012 - 12:19am

Lets hope rufio and tyler dont work in the same group :x

Array

Jan 12, 2012 - 7:56am

Historical hardcode input = blue
Estimates hardcode drivers & assumption = blue with yellow background
Formula = black
Links to other worksheets within the same file = green
Links to other excel files = green and bold (but typically avoid this; use data transfer sheets instead between models if possible)
Too lazy or tired to fix right now and hopefully will get back to later = red

Best Response
Jan 12, 2012 - 5:41pm
Alpine:
Historical hardcode input = blue
Estimates hardcode drivers & assumption = blue with yellow background
Formula = black
Links to other worksheets within the same file = green
Links to other excel files = green and bold (but typically avoid this; use data transfer sheets instead between models if possible)
Too lazy or tired to fix right now and hopefully will get back to later = red

Much better to use indirects... we never direct link to external sheets... indirecting is a far better solution, faster and you never have to worry about whether the link has been updated... all you have to do is make sure it's referencing the correct file, so if you saved down v10 of the last model, just change the hardcoded reference from v9 to v10. Cuts down so much time and if you are working in monster models as we often do in our group, it is infinitely faster. Our comps file outputs = indirect off our master comps file... pretty effin tight

Jul 23, 2013 - 5:02pm

I never understood the value of indicating links to other sheets or workbooks with separate formatting. If it is just to update links, I just search for a "!" or "]", but for everyday usefulness, I am most concerned about knowing which cells should be modified and which shouldn't. Am I missing something?

One distinction that is useful is to separate model drivers (assumptions) from other inputs (i.e, historical financials).

Jul 7, 2018 - 10:52am

Repudiandae voluptatem fugit cupiditate vel architecto provident a voluptatum. Sed optio quae doloremque. Dolore fugiat dignissimos ea necessitatibus enim deleniti illo. Delectus earum vel excepturi officia. Eum earum ex et nemo ipsum officia autem. Et aut explicabo odio voluptatum eligendi doloremque tempora. Officia sit molestiae sed necessitatibus earum inventore ea.

Eos nobis molestias quia molestiae similique impedit numquam earum. Iure facilis animi tenetur aperiam. Reiciendis magni mollitia nulla. Doloremque molestiae accusamus itaque tempora non ea. Consequatur ipsa laudantium non ducimus facilis nesciunt vel. Modi quas quod dignissimos id quisquam ex. Voluptatibus molestiae magnam non natus aut modi dolor.

Nostrum sit sit ea accusantium. Ullam id dolores commodi ea neque. Exercitationem vel autem eveniet molestias ipsum quos et.

Placeat praesentium harum id et dicta itaque. Voluptas odit et eum quis beatae. Fuga ipsum autem aut voluptas est rerum.

Start Discussion

Total Avg Compensation

October 2021 Investment Banking

  • Director/MD (10) $853
  • Vice President (39) $363
  • Associates (229) $233
  • 2nd Year Analyst (138) $155
  • 3rd+ Year Analyst (31) $147
  • Intern/Summer Associate (104) $143
  • 1st Year Analyst (502) $135
  • Intern/Summer Analyst (387) $83