Clients often ask us if it is best to monitor assets separately or group them into one operating model. The short answer is that it depends on a multitude of things.
Suppose the model will be used to report to some senior lenders. In that case, it will probably make more sense to have an asset-specific model because the lenders for that project will only care about that one or not all of the others you might manage.
But the answer becomes a little more nuanced if your model is to monitor a portfolio of assets or conduct a shareholder valuation.
Which method is best?
A key advantage of having only one model is that everything you need is in one spreadsheet. You spend less time copying data between models, and if there are inter-company movements, there’s less risk of error of using the wrong model or misaligning cells.
Another advantage comes when running scenarios, for example, an inflation increase scenario, can mean a single input change rather than an input change in ten different places, followed by copying ten sets of results into a consolidation model. If you see something unusual in the results for one of the assets, it’s also easier to track back through and work out what is driving it if the inputs and calculations are all in one file.
One model for many projects seems like an ideal approach.
However, there are disadvantages to having only one model. The file size can quickly become unwieldy if the asset calculations are complicated and can’t be simplified. The model can then rapidly become exceptionally slow and perhaps even unusable.
Therefore, it is essential to consider the limits of Excel and the scalability of the multi-asset model approach. While your model might be reasonably small now, if you’re expanding and plan on putting more assets in the model in the future, this approach may break down at some point.
It would help if you also considered your company setup. Suppose different people or companies manage the assets; then adopting a multi-model approach is probably more appropriate. It will avoid ending up with multiple versions, with many amendments, of one shared model, and no one model genuinely reflecting reality.
The key is automation.
While spending one or two hours each quarter to update the model of one asset manually may be acceptable, it is no longer the case when it takes 30 hours to update 15 assets, doing the same task repeatedly. That is when you should aim to automate most of your modelling.
When selecting the best approach, you should conduct some cost-benefit analysis of automating part of your modelling. Unwinding historic balances, populating historic data, or validating changes are good examples of tasks you should automate. Each modelling approach will have a different impact, both in the time gained and the initial setup cost.
By nature, the multi-asset modelling approach is more suitable for automation. We have recently developed an interface for one client that imports a large set of trial balances en masse, reducing the process of updating the historic figures for a model with a large number of assets in to a matter of seconds. This interface uses a combination of Excel and PowerQuery, and proves that as automation tools become more and more powerful, understanding which tool is correct for the job is paramount.
Aggregator Model
If, for various reasons, you do want to stick with multiple models, you can still put some effort into making sure the process of transferring data between models is as easy as possible.
Generally, with multiple assets and models, you will have a model that sits above them all and aggregates all that data into a set of consolidated results.
The main tip is to ensure that the output sheet used to export data from each model matches, as close as possible, if not identically, the input sheet for your aggregator model. So, all you have to do is copy data from one model to another as many times as you need.
If elements need manual adjustments because the inputs don’t add up or match in structure and format, then the time you spend manually hacking things about will go up multiplicative with the number of assets you have. Therefore, having a transfer sheet in each model that owners are not allowed to amend, except to make sure that it works correctly, will simplify the task of transferring data into the aggregator model.
Model Dashboard
Another solution is the one used in our dashboard approach, which uses a macro to copy and paste the data from the individual models into the portfolio-level one.
Not only it runs much quicker than you do it manually, but it also means it extracts much more data. So instead of only having key metrics like your DSCR, return, and shareholder cash flows, you can pull virtually anything you’d like, so the aggregate model can show a fully consolidated set of statements.
Another advantage is that the macro can also pull out sensitivities in addition to extracting the base case. So you can see what happens if inflation goes up by 5%, the tax goes up, or interest rates change in the current base case and these hypothetical views. While this is relatively easy to do if you have all your assets in one model, it is difficult to achieve if you have them across multiple models.
So before deciding to monitor assets separately or group them into one operating model, consider how your team operates, the nature of your assets, potential portfolio growth and the ability to automate repetitive manual tasks.
Our modelling team will happily guide you to find the best approach for your specific requirements and support you in developing the optimum solution. Contact us to discuss your project specifics.
