Financial model Auditor mandate
In order to know how to make someone else’s job easier, we first need to know what that person is doing.
In a very summarized way, we can say model auditor are supposed to perform mainly 3 tasks:
- Check the model mechanics: Validating the logic, arithmetic/formulaic accuracy and internal consistency of the Model; to ensure that the Model is designed and built in the most straightforward manner, does not contain unnecessary or excessive programming modules and has a user-friendly interface and intuitive internal structure, test the Model for cell reference redundancy
- Check the model Inputs: Ensuring that model inputs assumptions are consistent with all the Project agreements and reports.
- Check scenarios and sensitivities: Understanding and reviewing up to X sensitivity runs of the Model to ensure that changes to results accurately reflect changes to input data and to comment as appropriate thereon.
To lean more about what is required from a model auditor, check my post on how to draft an RfP for hiring a financial model auditor.
Model auditor Task 1: Check the model mechanics and structure
Example from a real model auditor contract:
How to make the task easier for the model auditor?
Apply standards in your models:
- separate inputs from calculations
- use color codes
- avoid using long formulas
- consistent formulas across rows
- avoid any hard-coding within formulas
- avoid circular reference
- Include a guide sheet
If you want to learn more about this topic check out my online course on “Project Finance Spreadsheet Design and Structure”:
Model auditor Task 2: Check the Inputs
Example from real model auditor contract:
That can be a time consuming task for the auditor and if you can make it easier for them to certify that the inputs are inline with the project documents, then you can save a great deal of their time and avoid additional costs.
How to make the task easier for the model auditor?
Include a Reference sheet within the financial model and for each input that goes into the model, indicate the reference file and even page number of where you’ve got the input from. Here’s an example:
You can download my suggested reference sheet and manual from the below link:
Model auditor Task 3: Check Sensitivities
Example from real model auditor contract:
How to make the task easier for the model auditor?
You should have sensitivities included in your model no matter if it is a requirement from auditors or not. The base case is just the best estimate of what might happen to the project. You’ll need to test different possibilities and basically do a stress test meaning increase costs and decrease revenue and see how far you can go before the project reaches default.
Also sensitivities is a mean for sanity check. You increase your costs and you expect equity IRR to go down. If you increase your cost and IRR goes up, then you need to double check your formulas or find the justification. And that exactly what the auditor will do.
- First of all they will check that the model has the ability to run different sensitivities
- Make sure that sensitivity parameters required by advisors are included and built in in the financial model
- the sensitivity results are accurate
You can easily integrate sensitivity analysis in your financial models by following my guideline and suggested template.
