Only ~6 months into first FT role as an analyst, but here the model efficiencies that I've been able to implement since I started. If these help you, that's great... use them. Share efficiencies that have helped you below so I can look good when I implement them ;)
1. Scenario analysis
Setting: My firm pastes cash flows into the back of an excel model, and uses other tabs to index match and clean up the raw data formatting to a presentable form. If we need multiple scenarios, we have 2-4 tabs in the back for cash flows.
Previously: we used find and replace to change the tab names in all the index match formulas to change reference tab.
Improvement: I used the indirect function along with the index match function to reference a cell that holds the tab name. Used a drop down list on that cell to easily change the reference tab between scenarios.
Additional note: Use scenario manager if more than a couple cells change between scenarios or to save base case. Really useful tool.
2. PPT Referencing to Excel
Setting: My firm uses PPT for all our pitch decks and pastes images of Excel cash flows and what not into
the deck. The problem is, the excel model changes 400 times every deal before the deck is final.
Previously: Every time the model changed, the analysts would go through the deck and update all the
images (as well as any references to the model like return metrics) with the newest version. Done at least
5 times per deal before final.
Improvement: When pasting images, choose paste special and click paste as link. This image will now
update whenever the model is changed. Keep in mind that if you do this with dozens of images and
choose to update images every time the file is opened, it takes a while. I only update all images once
before we send it out, then I break all links so the client can open without issue.
I know I have a lot to learn still so I'm looking forward to your replies. Also, I'm sure the two mentioned above can be improved on, I just used them because I've never seen anything quite like it before.