Excel

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

In project finance transactions it is common practice to use debt sculpting when sizing the debt - i.e. finding an appropriate level of debt from the lenders point of view. In our VBA training courses I have found that one of the most appreciated methods is what we refer to as the ‘sequential goal seek’ so I would like to share this method today.

Debt-sculpting in Excel/VBA - alternative methods.

Debt sculpting can be done in a number of ways - often even using simple algebra. In other cases where there are circular logic due to look-back covenants or sweep mechanisms one is required to use a VBA macro. The example below outlines to core principles in how to use Goal Seek in combination with VBA to solve a range of cells to an optimal value - cell by cell.

Automate manual tasks in Excel with VBA

The example is an illustrative example of how simple it can be to automate a manual task without having to introduce any particularly complex VBA code. The focus in our example is on the VBA and Goal Seek rather than the actual Debt Sculpting, but hopefully both messages are clear.

VBA and named ranges

Before looking at the code we need to set up the named ranges in Excel. It is critical to ALWAYS work with range names in Excel even though it may cause confusion to novice users, as there is a great risk that your vba code will stop working over time otherwise.

DebtService: The highlighted yellow range in the image
DSCRDebtDelta: The DSCR Delta row
CountDeltas: The cell with the value ‘6′. This could easily be coded in VBA but for simplicity I have done it in Excel.

VBA Goal Seek example

Sub SeqGoalseek()

Dim i
 Dim NumberOfGoalseeks
 Dim rngDebtService As Range
 Dim rngDSCRDebtDelta As Range

 'Pick up the number of Deltas to goal seek
 NumberOfGoalseeks = [CountDeltas]

'Pick up DebtService range
 Set rngDebtService = Range("DebtService")

'Pick up DSCRDebtDelta
 Set rngDSCRDebtDelta = Range("DSCRDebtDelta")

For i = 1 To NumberOfGoalseeks

'Goalseek DSCRDebtDelta(i) by changing DebtService(i)
 rngDSCRDebtDelta(i).GoalSeek Goal:=0, ChangingCell:=rngDebtService(i)

'End Loop
 Next i

End Sub

How does the VBA goal seek example code work?

The VBA code will run a loop for 6 iteration (=the number of cells to optimise) and for each iteration optimise Debt Service (rngDebtService(i)) as a function of the DSRC Delta (rngDSCRDebtDelta(i)).

If you want to use this in a ‘live’ project finance model I would recommend you to refine the errorhandling and make sure that it is really stable. There is nothing worse than a VBA macro that only works sometimes….

Would you like to get a copy of the VBA goal seek workbook?

You could simply copy the code above and set up the range names and it will work. Alternatively, drop me an email and I can send you my version which may save you a bit of time.

Representing a corporate graphical profile in Excel is not always easy and can sometimes take a bit of work to get right. We have recently experimented a bit with the use of different fonts to improve the presentation of some recent financial models.

Using Webdings to insert new characters

To re-create the Corality ‘check’-symbol in the logo we created a list based on the Character ‘a’ and formatted as Webdings.

To insert a new symbol from an unusual font click Insert -> Symbol.

By choosing Webdings and the selecting the ‘check’ symbol we can achieve the a symbol that is similar to the Corality check.

Make sure to format the cells as Webdings to see the ‘a’ represented as a ‘check symbol’.

This is unlikely to revolutionize your financial modelling, but could be a nice effect for a financial model that needs to be prepared for professional printing, like an Information Memorandum of a pitch document.

EDIT: Enhanced functionality with built in colour formatting (Thanks Denver McCann at Numeritas for inspriration)

The image below should be read after you have read the discussion below (the comment from Denver McCann at Numeritas and my reply)

Custom Format with 'custom conditional formatting

There are numerous awards in the financial industry for strong performance and market leading developments and they are followed with a great deal of interest. This is not to dissimilar to the entertainment industry where awards are so common it is hard to keep up to date with them all.

One key differentiator between the types of awards between these two industries is that in the financial industry there are (to my knowledge) no awards for the worst performance.

Annual award night for the worst financial models

The financial modelling industry really needs some officially bad models to use as warning examples to junior modelers just joining the industry. At present most people are afraid of sticking their head out by saying that such and such build really bad models as it is not a very nice thing to say. It would be SO refreshing to hear this discussion in a bank negotiation:

“Did you hear that MegaBank won the award for the worst model of the year?”

“I am not surprised! They have never had any in-house capability for financial modelling and I am happy that this knowledge is now made public.”

The prize for the overall award - the worst financial model of the year - should be a hefty amount of money so that the person who built the model simply retires straight away without any intention of transferring his/her knowledge to someone else.

Special awards for particularly bad financial modelling

• The longest Excel formula
• The most confusing summary sheet
• The largest number of named ranges (I know who would win this one….)
• The longest recorded VBA macro
• The worst colour scheme
• The most incorrect model
• The biggest balance-sheet imbalance

Award night 2009 - send your nomination!

If no one else volunteers to host this event for 2009, I will do what I can to get this going.

Nominations anyone?

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.