financial modelling

Financial models are used for a number of reasons depending on the situation. All too often I see financial models being developed on the implicit assumption that they are to be used to analyse a project or transaction on a stand alone basis.

The analyst’s model vs. the reality of management

When taking a step back from the detailed view of an individual financial modeller or analyst it becomes clear that hardly any financial models are analysed on a pure stand-alone basis. In a vast majority of cases the output of a financial model will be rated against alternative projects, peer competitors or against alternative asset classes.

Rating one project/transaction vs peer comparison

When constructing a financial model for stand-alone analysis of one transaction it is a common argument that one can simply define key metrics as you see them fit. For example, if you only have pre-tax, real cashflows then it is a very simple solution of the analyst to present to management and pre-tax, real, IRR.

On a stand-alone basis, this doesn’t present any problems but when the IRR from the current project is benchmarked against peers or alternatives, then having a consistent definition is critical or management runs the risk of unknowingly pick the wrong projects or transactions to proceed with.

Create financial modelling standards and associated workbook templates

A very simple, yet effective, solution the problem stated above is to ensure that there are financial modelling standards in your organisation. Even something as simple as creating a standard template output sheet can make a huge difference for the consistency of outputs, and it will only take a good financial modeller a couple of hours to put together.

Suggested areas to focus on to ensure consistency (absolute minimum)

• IRR (pre/post tax, real/nominal)
• NPV (pre/post tax, real/nominal, calculation date)
• Cashflows (real/nominal)
• Assumptions (prices curves, CPI, expansion options, hedging, production, reserves, life of assets)

In short, the standard output sheet should include enough information to allow a reader to make an informed decision, and compare this to other transactions prepared in the same format.

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)

Charles Darwin’s ‘survival of the fittest’ is a concept that most people with some sort of rational background agree to. Would Darwin say that these principles are working in financial modelling?

Let’s say that Charles Darwin was given the task to set up the rules for evolution of financial modelling as an industry and think for a minute about which things he would change.

Genetic degeneration through ‘keeping it in the family’

Many organisations have their own internal ‘financial modelling style’. This is great of course in that it ensures internal consistency and therefore reduces modelling risk.
There is a risk however that in the efforts of keeping a financial modelling standard intact an organization stops the intellectual exchange of thoughts with the outer world and simply ignores outsiders’ comments on how the financial modelling standard could be improved.

There is a particularly big risk of genetic degeneration when a financial modelling team is managed by the same person for an extended time. The vast majority of financial modellers (including myself) would like to think that they can be objective and have the capability to review external input and incorporate it into their own world.

Sadly, this generally only results in minor tweaks to an existing modelling standard as most people are reluctant to completely change the way they do things (true for me as well….).

What would Charles Darwin recommend to avoid genetic degeneration of financial modelling standards?

• Schedule annual brain-storming sessions where the internal financial modelling standard is re-invented from scratch
• Ensure that new joiners to the team document their initial thoughts on the internal standards and suggest improvements based on previous experience
• Ask your clients, investors, lenders, model auditors or friends what they think about your financial modelling standard
• Reward change and invention through google style ‘time to think’. Allocate 10% of your time to develop new ways of building financial models, or even simpler, to review how other people are already doing it

Supporting genetically defect financial models

Many second grade financial modelling standards survive too long due to the support they are getting from within certain parts of an organisation. This is generally (not surprisingly) the case when the standard is used to generate a large amount of money, i.e. the financial models are used to win bids and therefore the sacrifice of low modelling standard is easy to live with.

This argument is fine as long as you would destroy the models post bid, but to this date I haven’t seen this happen. Bid models hang around for a number of years with a confused CFO trying to de-code what the deal team was really trying to do…

Are there ways of re-addressing the support for poor financial modelling standards?

• Review the progress of your financial modelling standard through the life cycle of a financial model - pre-feasibility, feasibility, bid, equity raising, debt analysis, covenant monitoring, budgeting, debt re-financing, divestment, re-structuring. How much is the poor financial modelling standard really costing you in lower productivity, staff retention and pure frustration?
• When you identify which stage is causing the biggest stress on the financial modelling standard then you need to bring this problem back to the source. Don’t just fix it in the current model that is having problems, but rather go back and update your financial modelling standard documentation, training material and templates to avoid the same problem for others in the future

Lack of predators to test the ‘fitness’ of a financial modelling standard

Most financial modelling teams don’t expose themselves to predators - i.e. people who are trying to kill your financial models. Many financial models are used for analysis by external parties but generally the feedback is in regards to the numerical output rather than on the financial modelling standard.

What you get then is similar to a situation where there are no predators for a certain species on an island and this species can grow without limitations. A bit like with the Kangaroos in certain areas of Australia.

How can you create virtual ‘predators’ within your team or organization?

• Create a reward program for complaints on your financial modelling products to encourage external parties to give you constructive feedback on what is not working so well with the financial model you built for them
• Have a semi-public screening of recent models for a wider audience every month/quarter and take the time to listen properly to the feedback in a relaxed environment (bring beer)
• Give new joiners to the team the challenge of ‘killing’ the current standard by building something that’s bigger and better. Reward ambition, not results.

“We don’t even have a financial modelling standard!?”

Look, if your team doesn’t have a modelling standard already then don’t be too worried. That is a great opportunity to create one that really fits your needs, and it is almost always easier to start from scratch than to improve something that already exists. My key tip before starting would be “stop, right there!”

Why would you develop something new when there are people out there who have devoted their lives to financial modelling and you can simply adopt their standard? You wouldn’t start learning C++ just because you want to write a document and need to develop a word-processing software so why would you develop a financial modelling standard from scratch?

I have been active on a number of financial modelling websites over the years. So far I haven’t found anything that gives me what I am looking for so with the team at Corality I decided to do something about it.

So, we have put our brains together and came up with a new website with content provided by financial modelling experts representing a range of different views.

www.fimodo.com

Corality aims to improve financial modelling standards world-wide

In our job as financial model auditors we see a lot of financial models. Every now and then we see some really good financial models build with a consistent methodology, but unfortunately this doesn’t happen too often.

We find that even the worst financial modelling examples can be significantly improved with some simple pointers to what good modelling really is. Hopefully Fimodo can help improve this situation.

Recent financial modelling articles at Fimodo

• Can Fimodo change the financial modelling world?
• ‘Edit directly in cell’ - Quick Excel Tip
• Group and Outline - Essential tool for large financial models
• The 7 deadly sins of financial modelling
• Input structure for depreciation rates and asset lives
• Sense-checking delay scenarios in a project finance modelSpot-checking a project finance model in 60 seconds

Who should be involved in Fimodo?

The first person to be invited was Nick Crawley, Managing Director at Navigator Project Finance. As an absolute world-leader in project finance modelling and a passionate evangelist of best-practice modelling he has been a great supporter through-out the developing phase. Two authors don’t make a collaborative website however so Fimodo needs more contributors!

If you would like to be involved, or know someone who should, drop me an email or give me a call to discuss. Please note that we are looking for real experts here, not people who are passionate but still learning - there are plenty of sites out there for this group already. We are aiming to keep the quality at a very high level to give the highest level of benefit to people following the site.

What’s in it for us?

Many people have asked me the same question - ‘why are you doing this’? The short answer is ‘the world needs this’ and it takes a relatively small effort from us at Corality to get it started.

If it leads to a successful collaboration between financial modelling experts that benefit the tens of thousands of financial modellers out there then that would certainly make me sleep well at night.

What are you waiting for - check out www.fimodo.com now!

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