It is common practice in project, corporate and structured finance transactions for a financial model to need to accommodate more than just one project, company or instrument. Here are some good tips on how to handle this situation.
When do you need a multi-asset model?
If you are analysing a head entity that owns (or are looking to acquire) a number of assets of similar structure then a multi-asset model may be the best solution. If the sub-level assets can be defined within an identical framework of assumptions then this structure works really well and has some amazing efficiencies.
Typical multi-asset modelling situations
- The evaluation of similar mining or oil and gas assets which when consolidated constitutes the asset base for a financing.
- The construction of a power station with two generating assets, where the second generator is constructed later in the overall projects life
- Identical assets within a real estate portfolio.
This approach is also useful when modelling identical but sequential or migratory processes such as where a population element moves from one evolutionary state to another. We recently applied this when modelling the equitable re-location of tribal people in a remote emerging market mining project but it can also be used when modelling (un)employment situations.
How to structure a financial model with multiple assets
In order to make this exercise quite straightforward and efficient is to break away from the concept of a worksheet per category of logic, such as “Tax” or “Debt and to adopt what I call a “Silo” layout:
Using this approach one worksheet represents one asset in its entirety and here are the rules that must be obeyed to make this approach practical
- Each worksheet must be identical
- Data grouping must be used so that the sheets can be easily navigated
- Asset specific assumption must be driven from a single asset flag which is then used as part of a lookup of some description.
- When editing a worksheet edit all sheets are to be selected so the change applies across all assets.
The identical constraint means that even when there are components in one asset that are not in others, they must be modelled and made clear that the inputs are not used.
This approach is extremely powerful but needs to be used with caution and isn’t for an analyst new to financial modelling.
More advanced steps to multi-asset modelling
Once you are comfortable with this approach and have mastered data tables then it is a logical next step to move away from having a worksheet for each asset and instead having a worksheet called “Asset i” with the asset flag being the input variable for as many assets as you need.
This is a great way to get around the constraint of Excels 256 worksheets but also avoiding models becoming too fat with identical worksheets. It can be challenging at first to get your head around this concept but once you are onboard you will love it!
Summary & Warning
This approach can be used to crack some big modelling challenges and can be used in a transparent and readily checked manner. However if the rules are not followed then it can quickly cause issues.