What are the best Excel practices?
From Quora, the OP asked the following question:
What are the best practices when modelling in
I have purposefully left the question open but feel free to leave advice for specific types of modelling
Arnav Guleria, Trader Gone Rogue, had this top voted answer:
I draw fromexperience in M&A modelling, debt modelling, PE modelling, venture equity/convertible/debt modelling, portfolio engineering, and equity valuation modelling. These best practices apply mostly in finance (banking, PE/VC, and ).
- Know your tool (What's good and bad for)
- Know your audience (Fellow mavens? VBA gurus? Or the guy who requests that you "email [him] back a file [he] accidentally sent you so [he] knows for sure you haven't kept it" and is convinced his computer's broken because "the bend in the wire is probably blocking the pointy 1s" (binary)?)
- Know your project life expectancy (will you be willing and able to support it for its usable life?)
You are building, essentially, a UI. Check out your favourite websites, annual reports, etc. for layout inspiration and colour schemes.
In no particular order:
- Calculated values (and titles) are black, user inputs/assumptions are blue, references/sourced assumptions are green
- Explicitly state all assumptions about the blue user inputs/assumptions (in comments)
- Comment aggressively
- Error-checks should be built-in
- No black boxes - make it easy to access intra-calculation values, e.g. if I'm doing A+B/C, I can have D export that directly, but have greyed out cells off to the side or a debugging page that states what A+B is. It's over-simplified here, but in some or WAAC or converts valuation problems, for example, those intermediate steps can be a godsend. I'm a fan of putting them greyed out off to the side since it helps the analyst using your model figure out when it's mis-behaving (implying that core model assumptions aren't being met).
- No circular references nor named ranges
- Very minimal use of font size changes (normal for everything, normal+ for page title, normal- for headers, etc.). NO random neon background cell colours - there are better ways to delineate spaces. Don't go bold happy.
- State units explicitly
- Remove gridlines when finished and protect all non-blue cells
- Pre-format every page for happy printing
- Build highly robust VBA - anything done in VBA over in the spreadsheet should account for the risk that if anything goes wrong 90%+ of users will be entirely hopeless at rectifying the situation
I have excluded most general modelling and data structure best practices.
Note on knowing your tool
Know when is the appropriate solution. Repeated, basic, calculations that vary ever so slightly between each stage and produce loads of meaningful output as the model chugs along are good for . Anything involving higher math, iteration, or external data sources is retarded to run in .