Functions
Sometimes it may be very tempting to use vba functions in your project model. However, if at all possible, it’s best to avoid doing so.
This is simply because:
- Vba functions can slow down your spreadsheet considerably, which may become cumbersome when it gets large, and
- Your model will probably need to go through a model audit prior to financial close, and model auditors don’t like functions – they’re more specialised than vanilla excel.
Procedures
On the other hand, I recommend building a toolbox of procedures and shipping it from project to project. Many of the procedures can be deleted prior to shipping the model off to the auditors, and are therefore not problematic as per bullet two above, but can save significant time during the model build. I make regular use of procedures to do the following:
- Circular reference resolution
- Copying the current cell across to the full row, linked to a keyboard shortcut
- Sculpting debt
- Sizing debt to fit funding requirements