Creating Flexible Financial Models
Adapting to changes and the power of flexibility in Excel financial models.
Each budding Excel modeler reaches a point in their professional development where they realize the power and importance of building flexibility within financial models.
Nowadays, I consider myself somewhat of an Excel modeling pro. However, that wasn't always the case. I still remember the exact model that taught me how crucial flexibility can be if approached incorrectly.
A story of 25 independent model versions
I started my career in external audit at EY, so I spent the first 4 years of my professional development without having to develop a single model. I reviewed many financial models and budgets as part of audit work but never had to build one on my own.
However, sometime in my 2nd year with EY, my dad introduced me to a client who had some inbound interest in selling their business and needed a rudimentary financial model to outline expected performance over the next 5 years, as well as use these expectations to calculate the present value of the business (using a blend of DCF and Terminal Value of the company).
It was a very straightforward exercise. It was a very simple family business, and it only took a few hours to build their model. After that, I was kind of hooked. I did a few similar projects for different clients of my dad and already felt like a pro.
After I left EY, I ended up in a local bicycle manufacturing company near my hometown. It was a very successful family business that had grown tremendously and was in dire need of an FP&A department.
Enter me, the one-man FP&A department.
One of the first tasks I was assigned was preparing an entire blown-out annual budget for the company. There was no one to guide me and no prior year working files (as is common in external audits) to leverage, so I had to open a blank Excel file and start from scratch.
The problem? No one told me what to include, what needs to be adjustable, and who will use the budget. And since I had my modeling experience, I spent a few days and devised a very detailed budget for the company. It had sales and production on a bicycle code level (a few thousand models were being offered), had every department's expenses broken down, and was a proud achievement for me.
I sent it to the CFO and CEO of the company. They said it was great and asked me to bump up the cost of raw aluminum (primarily used in wheel production) by 5% and 10% and show them the impact on EBITDA of both scenarios.
Not only did I not have an easy way to adjust the cost of raw materials, I had no way to work with scenarios. So, I rebuilt the model twice to show the two scenarios and then made a new comparison file to show the differences. Everyone was happy, and I now had 3 versions of the same budget model.
Over the next two weeks, I was asked to show a bunch of other scenarios, excluding certain business lines, including new ones, changing sales assumptions, COGS assumptions, hiring new people, etc. I followed my tried and tested approach, and I had more than 25 different model files in the end. Then, the CFO asked me to change another assumption - in all versions.
What a time to be alive, right?
I remember it vividly to this day. I return to my desk, sit in front of my computer, open a blank new Excel file, and start to rework the entire model from scratch, this time making sure everything is adjustable, each component can be switched on/off, and different scenarios can be compared.
So... Lesson learned.
How do we define model flexibility?
If you look online, you won't find a specific agreed-upon definition of model flexibility (which meant I had to come up with the below definition on my own instead of copy-pasting it from Investopedia).
Let me try.
Flexibility allows a model to adapt and respond effectively to changes in source data, assumptions, scenarios, and different stakeholder requirements without significantly reworking or redesigning the entire model.
Building flexibility within our model initially takes way more time. Still, it ensures we can accommodate the dynamic variations in business plans and conditions and user needs without sacrificing our model's accuracy, reliability, or ease of use.
When you think about model flexibility, you can reference the following list of questions:
How easy is it to modify model inputs (assumptions and source data)?
Can we easily scale our model (e.g., if it's a company doing M&A, how simple it is to add a new acquisition; if it's a manufacturing company, how easy can we add another product line, etc.)?
Can we switch between different scenarios (sets of assumptions) and compare?
Do we have the ability to switch specific model pieces on/off (e.g., can we quickly show what all outputs and results would look like if we decide to scrap our M&A plans)?
Can users that were not part of building the model easily work with the flexible parts (i.e., can anyone understand what they can change/turn on or off, or is our model user-friendly)?
Do we have built-in error handling (i.e., error checks ensuring calculations are correct and reasonability checks ensuring assumption changes don't break the model logic)?
Can we easily build additional reporting views (outputs) based on the evolving needs of the business?
A flexible model empowers the organization to make more informed and agile decisions and reduces the risk of relying on outdated or rigid models that may no longer accurately reflect the reality in which the business operates.
How to enhance model flexibility?
Model flexibility is something that seems trivial, and it may even seem funny to dedicate a whole publication about it. But as soon as you start working on more complex models, you realize it's far from mundane, and even though it seems like common sense, it actually requires some thought and planning to implement the correct level of flexibility within your models.
Here's a list of some things you can do to enhance your models' flexibility (mind you, we can probably come up with many more points if we decide):
Formulas and Dynamic References
Use formulas and dynamic references for anything that's not an assumption or source data.
This one also seems obvious initially, but it's far from it. For example, if you have the assumption of interest on newly utilized debt being 5% per annum, make sure to abstract this as an assumption in a single place and then link all interest calculations to this assumption. That way, any changes in the assumption will automatically flow through all calculations, providing greater flexibility.
Data Validation & Dropdowns
Excel comes with pretty excellent data validation capabilities straight out of the box. We can use this to improve the flexibility of our models in 2 ways,
The more straightforward way is to use data validation options to restrict input options and ensure data integrity. For example, we may restrict a debt interest rate assumption input to be between 2% and 15%. This slight adjustment allows us to provide flexibility to the model users while keeping model integrity and ensuring assumptions stay within reasonable ranges.
Another option is to use data validation to create simple dropdowns. These are very useful when we want to allow the user to select from a predefined list of options. For example, we may have a specific model piece we want to switch on/off. This is where we can use a dropdown. Conversely, we can allow the user to select between a set of scenarios.
Using the list data validation option (fancy talk for dropdown) directly changes the cell value, which means we can easily use an IF statement to see if we have to sum the model piece that can be switched on/off or use any LOOKUP function to draw the respective assumptions based on the scenario the user selects.
Assumption Sheets and Scenario Switches
As you progress in your career, you will face the challenge of building much more complex models. In some situations where we start having too many assumptions within our model logic and calculations, it makes more sense to abstract all assumptions in a separate worksheet. This has the added benefit of making it much easier for our model users to switch between scenarios with the dropdown functionality we discussed in the previous point.
We can easily add a dropdown listing all the available scenarios and then use a formula like CHOOSE to load the respective scenario assumptions. Implementing this dynamic scenario selection ensures our model has added flexibility to accommodate different users' requirements better.
Modular Design
I already mentioned this above, but it is so important that it deserves its own point. Whenever you work on a more complex model, you would inevitably have to split some of the modeling out of the primary model (e.g., calculate debt balances and interest expenses separately and then plug only summary values in the primary model, calculate right of use and lease liability under IFRS 16 separately, etc.). A great way to add another layer of flexibility is by allowing the user to switch all model pieces on/off.
By adding a simple IF statement around each value we add to our summary tabs, we can add a simple config table where users can adjust which model pieces will be considered.
Sensitivity Analysis and Error Checking
Flexibility is great but often opens the door to potential errors or flawed model logic. What I mean by this is that the more adjustable inputs we give the user, the higher the risk of the user messing up something within the model.
We can use Excel's integrated Data Table functionality to create sensitivity tables for the key model assumptions, showing us how different combinations of those would reflect on the bottom line. These can also help us identify any potential flaws in our model logic that may lead to hypersensitivity towards specific assumptions.
Consistent Formatting
This one is not directly linked to flexibility, but it definitely makes every involved party's life much more straightforward. Always remember, you are building and know the model intimately (ew!). However, most external model users will be completely lost when they open your file. Therefore, it's crucial to keep some basic formatting rules in place. For example, I always apply the same colors to the number cells in every model I work on:
Numeric values are in blue.
References to other worksheets/external files are in green.
Formula cells are in regular black.
It's nothing too sophisticated, but it makes a tremendous difference when you have to share your model file with less technical people.
This list can go on indefinitely as we niche down. Some other ways to enhance the flexibility of our models can be adding conditional formatting, doing what-if analysis, version control, etc.
The more important thing, and the thing I hope you will get out of this story, is how to think about introducing more flexibility into your models, making them more user-friendly, and driving more value to model stakeholders.
That's all for this issue. Let me know if I missed some of your preferred ways to introduce more flexibility into Excel models. I'm always on the lookout for new knowledge!
Thank you for sticking to the end, and extra brownie points for those who share this newsletter with a few friends. :)
Best,
D.