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:
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.
Hardcode = Blue
Formula = Black
Links to other worksheets within the same file = Green
Links to other excel files = Green and sometimes Purple
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:
Don't fuck with this input because it is important and there for a reason / critical plug.
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.
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.
I use:
Hardcode = Blue Formula = Black Links to other worksheets within the same file = Green Links to other excel files = Green and sometimes Purple
Blue = input Black = formula Green = link to other sheets Purple = links to other excel files red = fix this shit.
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.
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
Lets hope rufio and tyler dont work in the same group :x
Haha right?
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
Or use Data...Edit Links to update.
i know this i a long shot (10 year old comment), but what does it mean to go indirect? =INDIRECT formula or simply having a comp sheet pull pull data individually from models so when you link to the comp sheet, you are pulling from static numbers from the comp sheet?
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).
Wouldn't it be more "team-player" if you use the same color conventions as your colleagues?
Does anyone know how to change all of the colors without going through one by one when you have some hack that did everything in black?
~
If you have the TTS macros you can highlight a selection or an entire sheet and use the autocolor function.
If I have a formula that links to other sheets or file, should I use black or green?
Any thoughts on when to use italics for consistency? Any thoughts on when to indent? **for excel**
Dolores aut modi possimus autem. Necessitatibus et labore dicta nam. Sint nostrum quo qui praesentium. Culpa dolor aut consequatur et eveniet.
Pariatur nisi assumenda nihil est ipsam aut cupiditate. Sit rerum vel laudantium perspiciatis quo aliquam.
Explicabo facere animi voluptas. Asperiores asperiores dolores exercitationem tempora reprehenderit quis. Quibusdam dignissimos illum voluptatem et animi tempora. Qui omnis qui non voluptatem sed. Cumque aut blanditiis commodi mollitia repudiandae sit beatae.
Quidem ut sit est fugiat nisi reiciendis rerum. Asperiores sit voluptatem mollitia dolorem nulla et. Cupiditate magnam fuga distinctio tenetur cupiditate rem ullam. Praesentium aut quisquam eaque. Consequuntur qui quis officiis atque sint.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...