Excel Best Practices To Avoid A Black Box Model
I build a models/automated spreadsheets for my team as one of my tasks. I'm pretty good with Excel and can make my spreadsheets highly dynamic. This is necessary at times for 1) repeatable/foolproof processes and 2) property managers (lol).
This means that I often find myself using multiple nested functions like sort>filter>unique or iferror>if>sumproduct>sumif>indirect. The formulas never go beyond one or two lines in the formula bar. But they're still so robust (convoluted) that I can't imagine myself, let alone someone else, coming back and decoding it just to make one adjustment to the formula.
What are your best practices for creating easily digestible formulas that still perform really dynamic functions? Any resources or general tips?
Nested IFs are just the worst but sometimes the easiest route to a goal in excel. There are generally always a way around them, it just depends on how you are breaking down the info prior to going into a formula and knowledge of other functions. But at the end of the day it doesn't matter if it's ugly, if it works, it works.
Here is a detailed thread on peoples most used formulas on the job
https://www.wallstreetoasis.com/forum/real-estate/excel-formulas-you-us…
I know better by now than to use nested IF's. My issue was moreso with nesting different functions because there are several "steps"/actions I'm trying to complete in one fell swoop.
And the issue isn't getting something to work now. I can get my formulas to work. The problem I guess I'm having is what coders typically call "documentation". How do I know what the hell any of this formula I wrote 2 years (or even months) ago is doing, without spending hours parsing through it and potentially creating major bugs?
Now that I think about it, anyone know of an Excel plugin that lets you break out your formulas into different lines and lets you add comments to them (like Power BI and any relatively robust coding programs have)?
Sorry, for some reason my brain read 'nested ifs' in your post.
I know you can break out your formula line by line with Alt+Enter but as for adding notes, I've never seen anything of the sort but there seems to be a plugin for everything these days.
Instead of making long formulas, use “helper rows.” This will show each step in the process and logic you are using as opposed to putting it in one formula. The person who made the models at my firm (who has since left) used 2/3/4/5 lines in the excel top bar to write his formulas and our acquisitions analyst has been untangling the web and making a new template model to use over the last twelve months. While the old models work really well, having to sort the logic is a pain in the a*s and just takes longer to make changes. The helper rows means someone can go one row at a time and quickly determine what it is that you did in each row, to figure out what the final row (summation/subtraction/product/division of what’s above, is actually doing).
You state above that you want an excel formula or program to break rows out etc or tell you what you did.
You can always leave written comments to tell you what you did. However, if you think you will have trouble and you wrote it - unfortunately that probably means someone else will have more trouble. While I’m sure it works and it is probably a good model - you should probably take the time to re do it in the helper row fashion I list above. That way you, or the person who takes over after you, can actually understand what you did. Good logic and code in excel usually, though not always, also means it is easy for anyone to pick up and quickly determine what you did.
Very helpful. I think helper cells is the move. Thank you!
I tell junior analysts this all the time. Getting all fancy with complex formulas is cool and all, but it is a real pain in the ass for me when auditing your model. Plus makes it way more likely you will mess something up with a bracket in the wrong place or something.
You can use as many rows and columns as you want. Stop putting 10 formulas in a single cell that is pulling data from 5 different tabs and 2 external workbooks.
Using a check table or check tab is helpful too.
If you have long formulas that are hard for you to review and audit -- then you're doing it wrong.
If you find your formulas are too long - break them up into steps. If a formula is doing 3 things - break it up into 3 formulas in 3 different columns or rows.
TLDR: modeling with baby steps is always best, and easiest to audit and follow what's happening. Also use good descriptions for what is happening in each column / row. If you can't pick up the model you're working on 6 months from now and quickly trace back how it works -- then you aren't taking small enough baby steps.
Depending what the model is for, i usually always go the route of having 1 sheet for every flags/triggers of fixed/time based assumptions.
Some people prefer to put their flags on their calculation sheet but it makes the calculation sheet overly crowded with different outcome of each rows which makes it inaccessible for newer analyst/3rd parties to read.
Use only 1 string of IF(AND(OR if possible, never use IF within an IF since once the model is broken, fixing them would be a nightmare.
My advice is similar to other posts in this thread...
Always follow baby steps logic on each of your models. Makes the model easier to understand, audit, and develop. Having 1 line of super complicated formula for the sake of clean looking model is one of the worst thing you can do. Especially if the model gets thrown around. People get intimidated immediately and coworkers will hate you for it.
In cells and columns,
Paste Special's mystic force,
Data alchemy.
Just want to echo the other poster's comment about helper rows/columns - this makes things so much easier to audit.
Also, please please think three times before using named ranges. I know they can be convenient but there's nothing worse than stepping into a workbook full of 500 convoluted named ranges if you're just trying to understand what's going on.
Agree with previous comments - you always need to know what the end goal is. If you know already that this will be a highly complex lease-by-lease roll-up of a large portfolio or of a company and you want to run sensitivities on your underwrite you need to build the model with formulas that are not taxing on Excel/CPU capacity. Some best practices from my side:
- Avoid circularities at all cost
- Avoid offset & indirect function, also minimize sumproduct
- As a result of the above don't use one single sheet per asset but rather model assets out horizontally, you can always roll-up with an ID to look at single asset cash flows
- Use flags / dummies to indicate calculation inputs (e.g. acquisition & exit dates/ lease event dates/ inflation index/ occupancy flags etc.) which makes formulas also easier to audit
- Use binary condition brackets instead of if functions where possible, for example (A1=B3)*"rest of formula"
I get what you're saying about Excel black box models. They can be super tricky! One thing I’ve learned over time is to always document your formulas and assumptions. If someone else ever has to look at your work (or even if it’s you in the future), you’ll want everything spelled out so they don’t get lost in the weeds. Another tip is to break down complex formulas into smaller steps. It’ll make your model easier to follow. Oh, and using templates can help keep things organized. I’ve been using the “141 Free Excel Templates and Spreadsheets” site for a while now. It’s got tons of templates that help prevent things from getting too complicated.
Clear and dynamic Excel formulas are key! Best practices include using helper columns to simplify logic, naming ranges for better readability, and adding comments/notes for complex cells. Avoid over-nesting; break formulas into smaller steps. For collaboration, structured formatting and a formula breakdown in a separate tab can make adjustments much easier.
What sort of comments for formulas? and where do you put them in the model?
Est enim ut pariatur adipisci. Et qui labore architecto. Officia vero iusto ipsam quis. Quibusdam dolorem quis qui. Dicta minima quia accusamus optio.
Eum in pariatur eius dolorem nemo dolor. Eligendi adipisci molestiae sit perferendis autem enim. Rerum dolores sapiente aspernatur atque modi.
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...