What are the best Excel practices?
From Quora, the OP asked the following question:
What are the best practices when modelling in Excel?
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 from Excel experience 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 trading).
Governing tenets:
- Know your tool (What's Excel good and bad for)
- Know your audience (Fellow Excel 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 DCF 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 Excel 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 Excel. Anything involving higher math, iteration, or external data sources is retarded to run in Excel.
Comments
Good stuff
Good stuff
See my other WSO blog posts>
fuckin amen. except for
fuckin amen.
except for naming ranges. i've never been bothered by other people doing that as long as it's not too hard to figure out what the range is and the name is rather self-explanatory. but that's just me
Remember, once you're inside you're on your own.
Oh, you mean I can't count on you?
No.
Good!
Jesus Christ. This post is
Jesus Christ.
This post is the Gospel.
"They who can give up essential liberty to obtain a little temporary safety, deserve neither liberty nor safety."- Benjamin Franklin
no circular references? how
no circular references? how the hell has he modeled debt accurately without them?
should be changed to 'circular references are ok as long as you go to file --> options --> formulas --> auto calculate except for data tables --> enable iterative calculations, limit iterations to 100x'
External data sources can be
External data sources can be most adequately dealt with using PowerPivot (standard in Excel 2013, downloadable in earlier versions). Spend 1-2 hours setting up the feeds/CSV names/database connections/whatever, then it's a 2 second refresh job from there on, and fully functional so much easier to debug. I left finance before I knew about it, but it would be interesting to see if you could set up financial statements in there and output the models as PP pivot tables (which are both incredibly intuitive, and almost impossible to screw up for a beginner).
Re: named ranges: I had to deal with quite a lot of summarizing KPI tables from an intelligence tool, a special request from HQ who wanted it done a particular way. No way to get the original data, so no way to set up a PP feed. I set up instead a logical, structured way of naming ranges in a tab, which picked up the 950 or so named ranges and their location, and used this to write the 9 output tabs required in a way that allowed me to refresh everything fast instead of spending several hours copy pasting. Because the data structure was well thought out, I could debug any changes extremely rapidly, like someone changing the name of a metric on the original tool. 2h work setting it up, then 10-20 seconds on a no bug day, and maybe 3 minutes to update on a bug day.
Re: iteration or higher maths (basically the meat of playing with data), would love to hear from any Barcap folks who have used their version of fully functional language in Excel. Basically improving on http://neilmitchell.blogspot.sg/2007/03/hsexcel.html
The Quora top poster advises moving on from Excel for more advanced work, but 1. Excel is so widely used and understood that you need a really strong reason for trying to implement a different UI for the 99.9% of stakeholders who won't get why 2. Excel supplemented by the right tools (data preprocessing, PowerPivot, etc.) is actually really powerful 3. for what you are doing in most areas of finance, you only need basic arithmetic and a very clean, clear way of presenting it as the content (e.g. the validity of assumptions) is far more important than what you do with it. An Excel table is easier to understand and debug than an R or pandas data frame for most humans.
Edit: I never use VBA these days unless I absolutely have to (like the above job). It's both an ugly and limited language, and counter intuitive for most people to read and understand what you are doing vs. say Python.
Good thread :)
Good thread :)
To go along with this... Are
To go along with this...
Are there any VBA best practices? Interested, because I feel as though I could incorporate it more within what I'm doing, but don't exactly know how to prioritize...
TableTopper: To go along with
To go along with this...
Are there any VBA best practices? Interested, because I feel as though I could incorporate it more within what I'm doing, but don't exactly know how to prioritize...
Asume the user has 0 knowledge of any kind of VBA. Basically if it's not automated or with a nice big button, don't use it (unless you are the only person using the model).
See my other WSO blog posts>
Wtf at no circ just build a
Wtf at no circ just build a switch
to all you noobacabras going
to all you noobacabras going off about the circ refs because you think you are some sort of modeling expert given your shitty banking internships and wall st prep classes - here are a few pieces of advice that will save your ass one day.
first, the circs really don't matter unless you are building out some sort of shitty annual model (in which case you definitely need a switch and should only turn it on and enable when printing / not when building your model) - if you are building a monthly model the balance fluctuations will be minimal thus your circs yield zero benefit...
second, enabling circs/iterations in the excel options just invites problems, i.e. you accidentally building in a circ to some part of your model that shouldn't have it but excel didn't warn you because you've enabled circs and now you have a model that isn't functioning properly because you wanted your debt schedules / interest expense to be 0.0001% more accurate. trust me on this guys, when you inexperienced children get to building out a fully integrated monthly model with numerous operating and financing scenarios, revenue and cost builds, etc... all in all totaling in excess 3000 rows, mistakes can be and are likely to be made. build in every possible check you can and for the love of god do not enable circs. again, the 0.001% increase in your accuracy is so minimal compared to what could happen if you have a circ in your operating model / revenue build / etc and not receiving the warning because it is enabled.
Then you switch it off when
Then you switch it off when you want to check your model... Fucktard
Could you please elaborate on
Could you please elaborate on the "no named ranges" part? What's wrong with having dynamically named ranges when you're working across different worksheets?
good thread. definitely one
good thread. definitely one of my bigger weaknesses. i really need to balance finishing things as quickly as possible and building things so they're scalable because someone asks for something completely different than what they originally wanted a few months later and im fucked kind of shit if oyu know what i mean ok yah
computerized: Build highly
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 think this speaks to the importance of commenting. Will some users be lost no matter how many comments you include? Sure, sadly yes. But, commenting your code is extremely important if you expect your model's life expectancy to be meaningful. Comment, comment, comment. Don't be that guy who doesn't.
"My caddie's chauffeur informs me that a bank is a place where people put money that isn't properly invested."
Bookmarked this thread. A+
Bookmarked this thread. A+ stuff
"They who can give up essential liberty to obtain a little temporary safety, deserve neither liberty nor safety."- Benjamin Franklin
Full disclosure - I work with
Firefox: Could you please
I'll do what I can to help ya'll. But, the game's out there, and it's play or get played.
TableTopper: To go along with
I'll do what I can to help ya'll. But, the game's out there, and it's play or get played.
pplstuff: Firefox: Could
what is this flame - no named
iRX: to all you noobacabras
1/2 of the WSO Bash Brothers
"Licensed to Ill It"
We all know Bro J did it...
pplstuff: Firefox: Could
1/2 of the WSO Bash Brothers
"Licensed to Ill It"
We all know Bro J did it...
EURCHF parity: Edit: I never
Morpheus: Have you ever had a dream, Neo, that you were so sure was real? What if you were unable to wake from that dream? How would you know the difference between the dream world and the real world?