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.
https://www.quora.com/What-are-the-best-practices-when-modelling-in-Excel
Good stuff
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
Jesus Christ.
This post is the Gospel.
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 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.
Totally agree on this. As far as I am concerned anyone who loves VBA does not know how to program. I have never encountered such an unintuitive, buggy, and slow IDE and this includes old school SPSS. If it cant be done in excel (and lets face it, 95% of stuff can be done with clever combinations of vlookups, pivot tables and offset) then don't use excel at all. While in theory they are good, hybrids never seem to work out well in practice.
Good thread :)
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).
Anyone who programs should get into these few habits:
1) Always name, date, and state the purpose of the script at the top of each subroutine
2) Leave ample notes throughout your code. This is an important habit to get in if you often are coding long macros. It will help others when trying to debug and it will help you in case you don't look at the code for 3 months and then someone calls you ask wtf is wrong.
3) Limit use of "On Error Resume Next"... This should be self-explanatory, you'll never spot your errors if you're not triggering the debugger.
4) Assume users know NOTHING about VBA (which is true among 95+% of Excel users). Put a clearly labeled button somewhere if you expect people to run this on their own. They probably don't know alt+F8 or alt+F11.
---Personal preference--- The longest macros I've written are in the ball park of ~40 pages. I tend to have one 'master macro' and call all other subs from the master (with application.screenupdating = false/true and enableevents only specified in the master). This lets me breakdown long tasks into smaller parts (following the first three suggestions for each sub). the result is an easier to audit/follow program. It also lets you test, piece by piece, each part of your program to make sure you're doing exactly what you think you're doing.
Wtf at no circ just build a switch
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.
I dunno... I don't necessarily agree or disagree with respect to how you keep your circularity settings. It's all preference really... I have built plenty of these types of models, with as many rows and financing structures as you mentioned, and I always have my iteration turned on. It's a personal preference but it's pretty easy to spot where your circular references will come from anyway. With respect to the monthly vs. annual models, it doesn't make much of a difference which one it is, often times your model simply won't balance unless you have a circ, which is the entire point of inserting one.
Of course, the majority of people who build models according to best practices link up their interest expense calculations after the rest of the model architecture is built, so no circularity happens anyway, and you have the switch turned off when linking so there isn't a way for the circ to malfunction.
Then you switch it off when you want to check your model... Fucktard
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?
While named ranges are fine and dandy in a lot of situations, consider this: You're an analyst and you've been emailed a model that was created outside your organization (read: you can't contact the person who made the damn thing). This model has hundreds of named ranges and these are referenced through the entire workbook. Now you, as the analyst, are tasked with understanding this piece of shit and you have to go to the name manger every time you need to find where something links to.
A few names aren't that bad, some people just go to the extreme and try to name everything and auditing a model becomes a nightmare. A lot of times, the author names something that might be intuitive to them, but it's not intuitive to anyone else.
A good model should be transparent, intuitive, and elegant.
This is a good synopsis. Typically, a best practice is that if you are naming references for ranges or specific cells, that you have a page that will list what and where the named cells or ranges are. This way you can know, for instance that "Units" is found in B5 of the "Assumptions" tab.
A good rule of thumb is also to make your named references abundantly intuitive, i.e. "WACC," "Currency," Current Year" etc. so that it is pretty obvious what you are referring to. You can also trace back to named cells or ranges with F5. As long as you aren't going to the extreme, naming ranges and cells all over the place, it can actually be a big time save in a large model as opposed to direct linking to the cell every time, that was the purpose of the invention of the named cell/range.
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
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.
Bookmarked this thread. A+ stuff
Full disclosure - I work with these guys (not standing to profit here from the referral but still). Anyway, founder is extremely bright and has built a career teaching Excel modelling best practices as training for financial services and other firms.
http://www.fi-mech.com/
what is this flame - no named ranges? derp
i do not accept that is best practice. unless you assume all future users don't know how to press F5
Quaerat quasi ad doloribus dignissimos reprehenderit voluptas. Nihil et fugit maxime optio sint. Nisi vel inventore dignissimos est quos praesentium beatae illo. Placeat vel et corrupti nam. Natus aliquid et iste excepturi.
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...
Est eveniet nobis reprehenderit accusamus voluptas perferendis quia. Harum voluptatibus voluptatem qui ipsam eum vel. Laboriosam ea repellendus voluptas iure porro nisi non voluptates. Iusto dolore accusamus et nam dolorem.
Ut et dolorem sequi dolor. Nesciunt ad corrupti corporis at cumque. Impedit ut qui accusamus. At saepe saepe ab sapiente.
Consequatur praesentium libero voluptate excepturi quam. Amet repellendus eos laudantium voluptas a. Odio ipsum minus tempora vel aperiam iste quisquam facilis. Molestiae provident sunt est dolor in. Quia nesciunt vitae earum aut omnis. Nisi consequatur inventore omnis minima voluptates esse ut. Aut ut beatae consequuntur.