In my attempt to rid financial models of copy paste iterative macros, probably the biggest resistance I come across is on the subject of debt sizing, especially when sculpting is used.
Of course, a macro can calculate the exact debt amount needed so that the debt is repaid to achieve a fixed cover ratio in each period.
However, macros are not automatic and need to be continually rerun if there are any changes to the model. In addition, I think that this method introduces a spurious accuracy. It gives a exact debt amount, to the nearest penny. But remember that the accuracy of any projections will never be that precise and any tweaking of, for example, the guesses of working capital days or future tax rates will produce a different answer.
My recommended approach is as follows:
- Set up debt sculpting so that the debt service is calculated from the projected cashflow to achieve a fixed cover ratio (except for the final repayment period where it will vary depending on how much debt remains).
- Depending on the funding method used, analyse the effect (at stepped intervals) of different loan amounts or percentage equity. Data tables will be able to provide this analysis and the indicators will be the final period’s cover ratio and the number of periods repayment.
- From this analysis and based on the most likely assumptions, pick the most appropriate loan amount.
Data tables are a powerful analytical tool, and often under-utilised. Iterative macros give a single answer and need to be continually recalculated if the model changes.
But be careful, because of the inflexible nature of macros, the two do not work well together. Even more reason to make sure that models are logical and that neither circular references nor copy paste iterative macros are introduced. Trust me, it can be done and models are much more useful if they can give instant results.