Financial model audit

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..

On Sunday night I was contacted for a last minute change of schedule of the pre-conference financial modelling workshop of Terrapinn Corporate Finance World at the Marriott in Sydney. The organised presenter, Nick Crawley, MD of Navigator had cancelled due to health issues following his trip last week to Nigeria.

Project finance modelling and model audit processes

Given my background of being a co-founder of Navigator Project Finance I had no problems sticking to the agreed agenda as organised by Navigator Project Finance, and I quite enjoyed working through material that I was part of developing yet haven’t been actively involved in for some time. Working through a sample project finance model for a wind farm model reminded me of many late nights in my project finance days sweating over time-critical transactions including Acciona, Oxiana, Ma’aden Infrastructure, LynasCorp, CopperCo, Reliance Rail…

A model audit perspective on project finance modelling methodologies

Project finance is a field where the model is of higher significance than in many other areas of finance. The underlying projects and industry sectors are also often regarded as more operationally complex than more typical corporate finance sectors.

The combination of strict reliance on the project finance model and the detailed operational calculations and highly engineered debt structures results in an environment where good modelling is highly rewarded, and bad modelling is an absolute career-stopper. This was the background of the presentation for the Terrapinn Corporate Finance World financial modelling workshop in Sydney earlier this week.

Making a project finance model ready for transaction time

In the Terrapinn Corporate Finance World financial modelling workshop we worked through a sample Navigator project finance model with the perspective of outlining critical functionality at the time of transaction. To make sure that your project finance model is ready to go through the stress of a senior debt transaction, the following, very high-level, check list outlines the minimum functionality required.

• Clear tab structure so that users of your financial model quickly understands the structure
• Disclaimer sheet outlining confidentiality and acceptance
• Cover sheet with transaction descriptions and contact details for transactional and/or modelling queries
• Log sheet mapping changes in key inputs/outputs to person/time/file
• Summary sheet tailored to the current audience. In some cases this should be prepared purely for a banking audience (stronger focus on key ratios, DSCR, LLCR, PLCR, etc) and on other occasions an external equity focus is required (IRR, NPV, upside scenarios, capital requirements, exit multiples, etc)
• Assumption sheet with ALL inputs (they should definitely not be scattered through-out the model). Assumptions should be clearly grouped into relevant sections with headings and sub-headings
• Scenario manager to facilitate quick, consistent and automated generation of scenario analysis outputs
• If macros are required, there should be a Macro sheet with clear documentation of how the macros are to be used, and when
• Integrity checks confirming the integrity of the financial model at all times. Ideally this should also be linked to a master-check which automatically checks the integrity of all pre-defined scenarios in the scenario manager all the time using Data Table functionality. The result from Integrity Checks should be clearly indicated on all work sheets, including the Summary sheet to avoid people relying on the financial model in scenarios where the integrity is flawed.
• Model audit should be cinsidered and formulas should be consistenly copied across the rows and there should be no hard-coded values over-riding calculation
• Calculation sheets should be professionally presented with simple calculations and limited off-sheet references. Line summaries are critical
• Timing, flags, escalation/growth/cpi should be calculated only once and then brought into the relevant sheets
• Consistent presentation should be maintained (using Excel Styles makes this a hundred times easier)

In the majority of large transactions the information pack that is circulated will include a number of written documents (Information Memorandum (IM), etc) and a financial model.

The presentation of the Information Memorandum is pretty much always spotless and has been prepared by a professional print-marketing company. Everyone agrees that the presentation of this information is critical to the success to the transaction.

Presentation of the financial model in a corporate finance transaction

Unfortunately the financial model that comes with the written documentation often ignores the great marketing intentions of the team who put together the written collateral. Often the colours, fonts, and sometime even numbers(!) don’t add up to what has been presented in writing.

How to align the Information Memorandum with the financial model

Aligning your financial model to the information memorandum is not always easy, but if you consider this from the start then it is reasonably straight forward. They way to think about it is ‘I need to prepare this model so that the marketing team can copy-paste certain outputs into the Information Memorandum’.

Suggested actions

1. Use Excel Styles to achieve perfect consistency
2. Apply a tailored colour-scheme aligned with your corporate profile (ask Marketing for the RGBs)
3. Use logos as taglines as per the marketing guidelines (it doesn’t have too be complicated so prepare a draft and show it to marketing and adjust accordingly)
4. Prepare a cover sheet which links the model to the Information Memorandum
5. Prepare a Summary Sheet with all the outputs that are presented in the IM
6. Ensure that there is built in Scenario Functionality so that a user can replicate the scenario analysis outputs in the IM
7. Perform an independent review (financial model audit) of the financial model - it would damage the deal if an error is prepared in the model at this stage
8. Review the model for typos, incorrect spelling, lack of units, incorrect acronyms, etc
9. If macros are used - use proper Error Handling to give tailored error messages should something not work. You really don’t want an investor to get the error message ‘Error -45234′ but rather ‘Unfortunately the intended actionwas interrupted. Please contact your contact at XXX a phone number YYY for further assistance’.
10. Password protect the file (or, if you have a large file - zip the file and password protect the zip-file)
11. Create a log sheet so that investors can track changes between different versions
12. Use a Comment System to enable more efficient communication with investors

With the global financial crisis slowing down we are seeing a significantly increased level of activity in the transaction  space. Most notably the mining / resources sector is now again full of life. PPP / infrastructure is continuously strong and is representing the majority of work at Corality at present.

We need to look after our clients to keep them happy

With the increased activity comes an increased work load for our team members and we are now looking for a business developer to take on the responsibility of looking after our key clients to ensure that they know that we are always available for their projects.

Facilitating an easy engagement process

Working with Corality on a model audit engagement should be easy from a client’s perspective. Being responsive to requests and facilitating a no-nonse engagement process is at the core of what we do to respond to our client’s frustrations with the accounting firms’ model audit teams. The responsibility of the new person would be to ensure that client’s know that we are available and to assist in engagement matters and to generally keep clients happy.

Recruitment process?

We are not looking at running a big formal recruitment process at this stage as we prefer to have a more tailored approach and I would be interested in discussing this opportunity with anyone who believes it could be an interesting role. As always, we can make the role fit around the person - not the other way around.

Personal attributes of a business developer for financial model audit services

• experience from a transaction environment and understands the process of financing of projects $100m - 1 billion
• interested in building an international network (Europe, the Middle East, Asia, Australia)
• proven experience in business development

The role will be Sydney based for the first 12 months and can then be discussed. Options in the future include London, Frankfurt and Singapore. Should you also happen to love financial modelling and model audits then that is of course a big bonus, but if not then we can probably sort that out.

Interested? Give me a call on +61 2 9222 9222
(No recruiters please).

Money? We will tailor a package based around the risk appetite of the candidate. The right person will be willing to set high targets and get highly rewarded if they are reached.

Over the last few weeks we have been recruiting to fill two positions as Analysts at Corality specialising in financial model audits. While reviewing, short listing and interviewing candidates it has become clear that the majority of candidates are from three distinct categories: I have below tried to outline typical strengths of the different background when it comes to working successfully as a financial model auditor. Clearly everyone is different and I have had to generalised a bit to arrive at the conclusions below.

Accounting / audit / finance

The most common background by far in the field of financial modeling and financial model audit is accounting, audit or finance. The strength of this background is pretty clear given the similarities in process, structure and work performed in financial audit compared to financial model audit and this is also why it is so common to see auditors moving into the field of model audits.

The shortcomings of this particular background is in my view that other backgrounds may have stronger financial mathematics and numerical skills (actuarial), and stronger technology skills including Excel coding, tools, tricks (science, computing).

Science / computing / technology / engineering

A lot of people start out studying science and then working in a technical role for a few years before getting into financial modeling (often via an MBA, CFA or similar). Once the financial skills have been absorbed the result is candidates that have a ‘complete package’ of numerical excellence and financial awareness. Sure, I would agree that I am terribly biased here given my own background of a M.Sc. in Physics and a M.Bus.. in Finance, but anyways…

The main shortcoming of this group of candidates in general is the lack of client interaction, presentation of work outputs and customer service. (again, I have been guilty of all of the above when starting out in finance)

Actuarial studies

Actuarial studies give students a good mix of financial knowledge and ability to solve complex problems. These skills give them a great starting platform for a successful career in financial modeling and financial model audits. Actuarial students often have a very strong technical Excel background which certainly helps when starting out in financial modelling.

The shortcomings are not too dissimilar to the science category and for similar reasons. Students opting for actuarial studies tend to be more on the geeky side of life given their interest in mathematics etc. Trust me, I have a genuinely geeky background and know one when I see one.

A combination of actuarial studies, an outgoing personality and a couple of years in a role where complex problems are mixed with client interaction and written presentations generally result in very strong candidates for a financial modeling career.

Programmers - Good financial model audit material?

I would encourage more programmers to look into the financial model audit sector, in particular those who have been involved in structured software testing where the concepts of ‘release version’, bug control, bug reporting etc. These concepts are related to programming as well as model audits and in my view programmers have a lot of the core skills required to do a good job in financial modelling. Sure, these skills would have to be coupled with studies in finance to be able to make sense of the analysis, but this shouldn’t cause too much of a problem.