By Bastian Stroemsheim | April 4, 2023
Making minor adjustments to a current model can often evolve into a daunting task. On paper, it all sounds straight-forward and visually you know where everything should be placed. Yet, as you dive into the layers of known and unknown connections, the initial one-hour quick fix turns into 10 hours. In more severe cases, it breaks with no other options than to start over again.
As you build out sections of your financial model with more features or wrap your head around reconciling two models, consider adding these approaches to save yourself some time. As a quick reminder, in addition to building new models, Pivotal180 also offers mentoring and modeling support when it comes to adding new capabilities in current models. Submit an inquiry here or simply email alison@pivotal80.com if you’re interested in discussing this with our team.
Survey the field to understand what you’re working with
Mapping out the precedents and dependents can be tedious work, but it’s worth the investment as the model gets more complex. This provides valuable context when you’re in a position of deciding between building out the new section from scratch or repurposing the currently linked calculations to save yourself from re-linking again. Additionally, when removing sections, it’s tempting to remove everything at once to get a cleaner workspace. However, this easily causes REF errors where you lose the ability to see the linked reference. Building awareness flows into our next step – think carefully about the order of operations.
It all boils down to the sequence
Understanding the exact consequences for each modeling step is quite the self-discovery process. It starts with learning from past mistakes, and with experience turns into a mental exercise of laying out the pros and cons for each action. We’re not saying turn this into a chess match with Excel planning 20 moves ahead, but be conscious of it!
Two particularly annoying REF errors are linked to graphs and named ranges. If you remove rows feeding into an existing graph, you’ll receive an error message that could be hard to track down. Be one step ahead by changing the input values to the graph before removing. For named ranges, you can list all names and references through the shortcut ‘Alt+M+S+P’ and click ‘paste list’. This will give you an overview, and you can navigate to each of them by copying the cell reference and press ‘Ctrl+G’ and pasting the location. Once a named range is deleted by accident, it’s annoying to dig up the reference again.
Know how formulas become flexible
Adding more cases to a scenario manager with index formulas? Simply copying over the cases one column to the right will create tons of extra work. This means manually updating each index formula with a new range. Instead, cut the last case and paste it one column to the right. After this is completed, you’re free to copy across as you want. In other words, it makes a big difference if you copy over within the index formula, compared to if you copy over from the last reference. In essence, know when certain formulas fail to pick up the new information based upon the changes you make. This applies to lots of formulas that are based on ranges, even the sum formula.
What tools can make life easier for you?
At the end of the day, we want to work smarter instead of harder. One of the best ways to verify if any damage is done is to look at output metrics. If the IRR changes when removing something that shouldn’t change the IRR, you know something needs to be fixed. Working line-by-line to remove calculations while tracking a metric is helpful to remain confident in your model. To prevent switching tabs all the time to check for changes in the metric, you can copy and paste the returns as a linked picture (Alt+H+V+I). Now you can easily see if the returns are changing right next to where you’re working!
Please use the links below to find out about Pivotal180 and our financial modelling courses and services. Experience the Pivotal180 difference.
Share This Resource
