• Sharebar

From Quora, the OP asked the following question:

Quote:
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:

Quote:
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).

Quote:

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-...Excel

Comments (23)

  • snakeplissken's picture

    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!

  • lasampdoria's picture

    Jesus Christ.

    This post is the Gospel.

    "Those who can give up essential liberty to obtain a little temporary safety, deserve neither liberty nor safety."- Benjamin Franklin

  • EURCHF parity's picture

    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.

  • In reply to TableTopper
    Asatar's picture

    TableTopper wrote:
    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).

  • iRX's picture

    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.

  • whatwhatwhat's picture

    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

  • mikesswimn's picture

    computerized wrote:
    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."

  • lasampdoria's picture

    Bookmarked this thread. A+ stuff

    "Those who can give up essential liberty to obtain a little temporary safety, deserve neither liberty nor safety."- Benjamin Franklin

  • In reply to G Spread
    pplstuff's picture

    Firefox wrote:
    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.

    I'll do what I can to help ya'll. But, the game's out there, and it's play or get played.

  • In reply to TableTopper
    pplstuff's picture

    TableTopper wrote:
    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...

    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.

    I'll do what I can to help ya'll. But, the game's out there, and it's play or get played.

  • In reply to EURCHF parity
    honeyoak87's picture

    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?