Modelling Best Practices Resources

There isn't really a single agreed-upon standard set of best practices, as different firms have different model approaches and level of detail, but some of the tips I teach/try to hammer into my analysts:

1. Golden rule: Slow is smooth. Smooth is fast. Take your time every time at the start of a model to set everything up as cleanly as possible. An extra 2-3 hours on cleaning up the first turn of the model can save you 100+ hours of future comments.  
2. Never hardcode the same number twice in the same model. Always link to the original hardcode and cite the hardcoded cell to the source.
3. Separate tabs for projections and calculations vs. tabs for outputs. Never link to output tabs - only to the original projection tab. That allows you to output quickly while making big model changes.
4. Obsessively build in check rows as you model. If, for example, you have 5 revenue segments summing to the total in your projection tab and you have the same build laid out in your output tab, build a check row in your output tab to make sure the total revenue matches across tabs.  
5. Keep FactSet/CapIQ/Bloomberg pulls for charts of stock prices/multiples in a separate Excel from your main operating model. Those can take a long time to refresh and slow down your model.  
   1. Also, when pulling data for charts, try to pull as a range with start date and end date rather than pulling one day at a time. Massively saves you on refresh time.
6. When spreading financials over multiple years on different tabs, keep the years in the same columns so you can quickly check if everything is linked correctly by just glancing at the formula bars vs. the column you're currently at (e.g., if my model starts at FY2025 in column G on my main projection tab, I'll keep FY2025 in column G on my output tab that links to the original tab)
7. To the extent that it's practical (don't do this if you're summing 20 numbers obviously), for total rows, add individual rows one at a time rather than using a sum formula. If you calculate % growth or % margin underneath $ rows, this avoids accidentally capturing those percentages in the sum. Also, if you accidentally delete a row, this will #REF! out, allowing you to catch the error.
8. Never link to a different workbook (external links). Always double check for external links if you're copying + pasting from a different workbook by ctrl+F'ing for ".xls". Break them if you find them by copying+pasting values.
9. Never hide rows / columns with alt+HOUR / alt+HOUC. Group (alt+AGGR / alt+AGGC) and then collapse / expand (alt+AH / alt+AJ)
10. When refreshing sensitivity tables, don't just hit F9. Shift+F9 to refresh a certain selection to avoid freezing your Excel.
11. When working with circularity, build the full model first without any circular assumptions, then modify to incorporate circularity one piece at a time. Use only a single circularity breaker for each tab. Otherwise, it's very easy to get lost on where your model is breaking.
12. Reserve a different color-shading for cells with formulas that are an exception to the adjacent rows / columns so you don't ctrl+r / ctrl+d over it. Include a comment somewhere so it's clear.
13. If you're not sure if a methodology should be one method or another, it's 3AM the night before the big morning client meeting, and you have no one to ask to confirm, consider building it in as a chooser so you can flip to the other method quickly if needed (e.g., project D&A as % of revenue inclusive of revenue synergies or excluding revenue synergies?)
14. Bonus: The record macro function can be very helpful if you're doing repetitive keystrokes that can't be automated another way (e.g., multi-step fixed actions like copy+pasting a certain set of rows below the current set of rows, deleting the second row, then adding an indent to the third row, and then needing to do that 20x times).
15. Bonus 2: This is a personal trick, but when I save a model version, I like to screenshot and paste an image of the model to the side of the worksheet I'm on. That way, I can quickly compare and check when I have to update the model.