Model audit process

The Excel model audit team of Corality is based in Sydney, Australia. Most people appreciate that Sydney is one of the best places to live (in particular at this time of year - December - when Europe is not looking overly attractive and we have +28 degrees Celsius and sunshine) in the world but that is only one of the advantages of locating our financial model audit team here.

The model audit (model review) process of an Excel financial model

A typical model audit, or model review, of a financial model for a PPP/PFI/Project Finance transaction involves an initial spreadsheet review and an analytical review followed by additional iterations to resolve all outstanding spreadsheet issues satisfactorily. Typically in a bid tender process there are 3-5 Excel spreadsheet iterations in the model review process and the last two are often very minor.

Late nights of financial modelling for a bid tender process in PPP/PFI/Project Finance

By the nature of the high pressure environment of bid process in a PPP/PFI/Project Finance transaction the financial modeller in the deal team will be working on the financial model up until the very minute he leaves the office to go home a get a few hours well needed sleep. By that time the Excel project model is sent via email to the model auditors. This is obviously not a great structure from a bigger picture financial model risk perspective, but that is a different story.

The time zone advantage in using an Australian model audit company for European project finance/PPP/PFI projects

Unless you are working with a model audit team with seriously disturbed sleep patterns they are unlikely to be waiting at their desk at 1am waiting to potentially receive a new model…. (They probably would do this on the bid date, but not for the initial iterations). Should you work with an Australian model audit team then this would not be an issue. 1am in London is 11am Sydney time so we could get started straight away and complete the next model review iteration of the Excel financial model by 9am when the deal team modeller gets back to his/her desk to start the new day on the PPP/PFI/Project Finance model..

Since the launch of the brand Corality in Dec 2008 (post the spin-off from Navigator Project Finance) we have seen a big upswing on financial model audit mandates and we are now recruiting to get us ready for busy years ahead.

The last few months have been quite busy for the team mainly in the infrastructure/water/utilities space and there has been limited time to focus on recruiting. We are now overdue on filling the role as Senior Associate and will be conducting interviews over the coming weeks.

If you are interested in a new challenge in the field of financial model audit then please give me a call on +61 2 9222 9222 for a confidential discussion.

Key attributes for a Senior Associate in Financial model audit

In short, we are looking for someone who has significant experience in auditing financial models in a range of industry sectors and who would be comfortable in further developing our already leading processes and to train and coach junior team members. Based on this it is quite likely that you have a background in one of the accounting firms or in one of the more specialised model audit/actuarial firms.

Financial model audit outside the big accounting firms

Working in a specialised, non-accounting, firm has many advantages

• Have a direct and immediate impact on the business
• Flexible working hours and arrangements
• Less admin and reporting
• A more direct approach to implementing improvements
• Closer interaction with clients
• Instant recognition for good work and a quarterly bonus scheme
• A global client base

How do you know if this is right for you?

This is pretty simple question to answer for the right person. The infrastructure sector is, and will, be quite active for the coming years. If you can answer ‘yes’ to the following questions then I would very much like to have a chat with you.

Have you….

• Performed financial model audit on +10 infrastructure projects of USD/AUD/GBP +100m
• Trained and coached junior team members in the art of financial model audits

The job is based in Sydney, however if you are located somewhere else but would be interested in working in a fantastic city, live by the beach and enjoy the most comfortable climate on the planet then we could probably work something out to get you over here.

Salary expectations for this role

Getting the right salary is important for motivation. We have a pretty good understanding of how much the competition is paying and I am confident that we can give the right person a very good incentive to move away for your current role. This is all made even more attractive by a quarterly bonus scheme which makes the sometimes very intense work more enjoyable.

If you are worried about the salary, we could have a chat about your expectations early on in the interview to make sure that we are on the same page before getting more formal in the interview process.

Not all financial models need to be formally audited or reviewed by an independent party. In some situations the modeler or the company about to rely on the model simply wants to increase their confidence in the financial model. In these situations, there are several alternatives that may be more appropriate than a full formal model audit.

Model review vs model audit

The fees associated with a formal financial model audit are partly due to the extremely structured process involved in a financial model audit. By relaxing certain aspects of this process can achieve the same amount of work for a significantly lower fee.
An efficient approach to model review will differ to a model audit in the following ways

• Before first iteration the client reviews and amends for inconsistencies in maps
• Only one or two iterations
• Cell-by-cell review only performed on selected parts of the model
• Documentation limited to only errors and significant risks
• High level analytical check performed
• No opinion letter issued

This approach can typically be performed at a total cost of 20-40% of a formal financial model audit.

1. Generate consistency maps

If you do not have access to a model review add-in package then ask your model audit company to generate consistency maps for you. This is a very quick process and is the first step in reviewing a model for inconsistencies. You can then work through the outputs yourself to save money on external consultants.

2. Ask a colleague to review your model

This is the oldest trick in the book, but still works a charm. In the same way it is very hard to find your own spelling mistakes, it is very hard to find your own errors in a financial model. One very simple way of quickly improving your model is to ask a colleague (ideally someone who doesn’t really like you and who wants to find errors…) to review your model.

3. High level analytical review

One very efficient approach of finding errors in financial analysis, and one constantly applied by credit committees all over the world, is to print a summary sheet and review it with a calculator. Ideally you should be able to calculate the NPV of your project based on key assumptions using a calculator in half an hour. If something doesn’t look right, assume that it isn’t and get to the bottom of it before someone else does - or worse - you don’t find the error and make a bad investment based on you analysis.

4. Prepare a ‘super-summary’

To facilitate a high-level analytical review it can be useful to prepare a ‘super-summary’. This is typically comprised of no more than five to seven lines of annual data - no more! This can then be used to calculate annual metrics useful for sense checking. A ‘super-summary’ can be used for the following purposes:

• Annual average prices overview
• Debt repayment analysis
• Annual trend analysis - revenue, costs, capex, tax, etc
• Annual profit margins analysis

A typical financial model audit involves 3-4 iterations before an opinion letter is issued. There is no strict definition of what an ‘iteration’ actually is and it is worth exploring this concept a bit more.

In an ideal scenario for a financial model audit engagement of three iterations this is the process

• Submission of initial model
• Model audit iteration one - report of findings
• Deal team revises the model and submits the second version
• Model audit iteration two - Check of amendments and report of new findings
• Deal team revises the model and submits the third version
• Model audit iteration three - Check of amendments and report of new findings
• All findings have now been actioned
• Opinion letter is issued

I work with a pragmatic approach to the definition of an iteration and work with our clients to assist them in making the decision when to submit a new version of the model. Excessively frequent submission of new model versions to the financial model audit team can make the process highly inefficient.

Strict rules as ‘every new file sent to the model audit team is an additional iteration’ is not pragmatic and is not in the interest of improving the process for the transaction team.

The total cost of a financial model audit is a topic I discuss with clients on a daily basis. Based on those discussions and our work auditing financial models we have found some simple rules that outline the main drivers of model audit fees.

• Complexity of formulae
• Size of the model (i.e. the number of unique formulae)
• Model structure and adherence to best-practice guide-lines
• Time pressure to complete the project
• Number of iterations

Complexity of formulae

The complexity of a formula can be translated into ‘how long does it take the model auditor’ to understand and to validate the correctness of the formula. It is just common sense that this formula

=SUM(C34:C38)

is a lot faster to validate than

=IF(F$11<YEAR(FirstFY),EFFECT(Assumptions!$E$628,4),IF(F$11=YEAR(FirstFY), EFFECT(Assumptions!$E$629,4),IF(F$11<YEAR(Firstfyops), EFFECT(Assumptions!$E$630,4),IF(F$11=YEAR(Firstfyops),EFFECT(Assumptions!$E$632,4),EFFECT(Assumptions!$E$631,4)))))

Size of the model (i.e. the number of unique formulae)

It is very difficult to give an indication of the ‘typical’ size of a financial model as it varies significantly depending on the model’s purpose and the sector to which it belongs. However, we find that the largest financial models (in terms of the number unique of formulae) are typically PPP/PFI bid models which can contain between 5,000-20,000 unique formulae.

Actively working to reduce the number of unique formulae in a model can significantly reduce your financial model audit costs. If you are uncertain of how this can be achieved, ask your financial model audit team next time you ask for a quote. They should be able to suggest some simple and practical techniques by which this can be achieved.

Model structure and adherence to best-practice guidelines

Providing a quote on a financial model audit job without having seen the actual model itself creates the risk that the quoted amount is vastly different to the incurred audit costs. This difference will inevitably need to be borne either by the client or the auditor. Either way, this unexpected blowout has the potential to damage an otherwise healthy client relationship and needs to be managed in a proactive way.

Different model audit companies address this problem in different ways. We find that the best outcomes are achieved when we work closely with our clients to guide them to using a best-practice approach to financial modelling which tends to push down the total costs of the engagement by effectively de-risking the scope of works. It is not unusual to see that some quick changes to a model mean a significant reduction in overall fees based on a pricing grid using unique formulas and/or complexity.

Time pressure to complete the project

It is not uncommon practice for an audit team to be provided with a financial model to be audited only days before a bid deadline. This creates an enormous amount of pressure on not just the financial model audit team, but the modeler and also the transaction team as a whole. Even small errors found at this late stage can have a critical impact on the deal structure and optimised funding structures.
A much more efficient approach is to work side-by-side with the financial model audit team throughout the bid process and this co-operative approach will lead to improved models with less errors and may even lower overall model associated costs.

Number of iterations

A typical financial model audit engagement involves 3-4 iterations and in the case of a successful bidding outcome, some additional work leading up to the financial close. It is important to have well defined iteration stages and avoid continuously bombarding the financial model audit team with new versions.

If you are unsure of at what stage and at what frequency you should be providing the audit team with updated versions of the model, ask your model audit team on their approach to this issue. An uncontrolled submission of new model versions can result in excessive model audit fees - an outcome that no one wants.