what is the preferred best practice approach to modeling a target capital structure in Modano. In some instances circular references cannot be avoided - for example when you target a Debt/Capital ratio. In this case distributions depend on debt drawn, which depends on target gearing, which depends on book equity which depends on distributions. This is usually solved with a macro - how easily can a macro be implemented in Modano? Are there any other approaches to a problem like this?
Good questions Neal!
This question is more of a financial modeling question than a Modano question, as you can build capital structure modules to do this any way you like. And if you want to use VBA macros you can also do so (see https://www.modano.com/forum/vba_modular_workbooks).
The capital structure modules developed by Modano include a range of debt and ordinary equity modules, but these really are just a starting point for more detailed capital structure modeling:
Personally, I usually start with a very simple debt module (usually the Debt (Amounts) module) and the Ordinary Equity (Amounts, Dividend Calculations) module, then customize the debt module as required, and insert and customize additional debt modules for each different type of debt facility and preference share instrument required by the underlying business.
In most complex cases, businesses have multiple tranches of similar types of debt – such as 3 senior bullet debt facilities and 1 preference share facility – so I use wehatever Modano debt modules I can and then customize them if required for certain types of debt. This means I can still easily add debt tranches by simply adding categories to the debt module of the required type of debt.
Regarding circular references, I’ve always avoided them like the plague, sometimes using quirky workarounds to avoid them and other times using prior period data as a proxy rather than current period data. I’d even prefer to use a VBA macro (perhaps event-triggered) than have a circular, as they just make everything volatile.
A good example of a workaround to avoiding a circular is provided in the Interest on Cash module which uses a proxy average cash at bank calculation (which is calculated in the Cash Flow Statement modules) instead of using the actual cash at bank and causing a circular reference.
Ideally, it would be great to talk about an advanced example of capital structuring, including preferential distributions modeling, but it’s taking time to create these examples. Anyone happy to upload one is more than welcome!
Have preferred equtiy. Its was a bit painful to add this in as adding another equity module didnt work as didnt link. Prashant talked me through it though. I hope you can fix it so that adding another equity modual auto links.