training course

When I have been presenting courses in the past for Terrapinn (project finance modelling) and Navigator Project Finance (financial modelling)  is has not been uncommon to have 1-2 model auditors from ‘the competition’ participating. This is not strange in any way as it is quite important for any financial model auditor to have a good understanding of why financial models have been constructed in a certain way.

The feedback has constantly been something like ‘well, it certainly was useful however it doesn’t cover everything I need in my role as a financial model auditor’. The obvious question to ask would be ‘are there courses that caters especially for model auditors’? There are certain topics that financial model auditors are interested in which I don’t think are covered in any one training course offered in the market (please let me know if I am mistaken here!).

Suggested agenda for a tailored course for financial model auditors

• Time-line of a model audit
• Discussion around suitable Scope of Works for a financial model audit
• Reliance standards (professional indemnity insurance, partnership structures, ‘agreed upon procedures’)
• Model audit (spreadsheet investigation) tools - benefits and differences (OAK, Spreadsheet detective, Spreadsheet professional etc)
• Efficient communication with the team performing the Tax and Accounting review
• Common audit processes (cell-by-cell, analytical review, model design etc)
• Pragmatic documentation standards
• Techniques to break down complex formulae (and tools for that (like BPM Traverse and similar)
• How to communicate issues with circularities to clients
• How to assist a client in preparing a model for audit (cleaning up, restructuring, documentation, commenting macros, etc)

Should Corality launch a ‘financial model audit’ course?

We have been discussing internally converting our internal model audit training program for new joiners into a public course, but so far haven’t done much about it. It would be interesting to know if anyone else has done this already or if the sector, which is dominated by ultra-traditional accounting firms, is protecting the intellectual property and are reluctant to sharing knowledge and procedures with the competition. I don’t really believe in protecting intellectual property to build sustainable advantage so maybe this is a good idea for us to pursue?

It is a well-known fact among financial modelling training companies that certain sectors of financial training benefit from economic slow-downs. The main reason for this counter-intuitive fact is that people are looking to polish their CV in case they would be made redundant.

Corporate financial modelling training is taking the biggest hit

Not surprisingly the upswing in individual training is offset by corporates and institutions reducing training budgets to cope with the threats of recession. With many of the banks on a zero training budget for 08/09 this will keep causing a lot of pain in the financial modelling training sector. The net effect for most financial training companies is a reduction in total participants, and the hardest hit are almost always companies catering for corporate clients with high fees and costly overheads.

How do you get a good deal on financial modelling training?

If you want to use the current environment as an excuse to get a better deal on financial modelling training then you should read this. There are a number of ways of getting more attractive pricing when organising financial modelling training and in combination they can be quite effective

• Have an open discussion with your training provider and ask them how you could bring the training fees down
• Register multiple participants
• Bundle up several training courses in one registration
• Use early-bird discounts where applicable
• Organise an in-house course (and provide catering, venue, printing, etc)
• Offer to work exclusively with one training course provider
• Ask for a last minute discount in the days before a course (just keep in mind that the best courses are often full due to the small group sizes)
• Find a smaller operator who can be more flexible on price
• Offer to provide testimonials or ‘success stories’ for marketing
• Wrap the training fees up with other consulting work (say you are using F1F9 for model builds, why not ask if they can give you a good deal on the training too?)
• Promise to recommend the course on LinkedIn, Facebook, your own website, etc (could be a tough one, but it’s worth a shot)

Universities market short courses in financial modelling

Many post-graduate universities offer short courses on financial modelling as part of their alumni and continuous learning programs. In Australia we have seen UTS Financial Modeling Certificate advertise frequently in the Australian Financial Review, and recently Macquarie Applied Finance have joined in.

Specialist modelling firms targeting individuals

Some of the smaller, niche training companies are now directly targeting individuals who are looking to prepare for a tougher job market. On the more aggressive end of the scale we have Financial Mechanics and Navigator Project Finance (including financial modelling in London) who both offer courses to individuals at 50% of the corporate rate. Fi-mech even made a big announcement about it, but Navigator seems to be quieter about this arrangement but they have also been reducing the fees for financial modelling training courses in London and Singapore.

Extract from Financial Mechanics newsletter, ‘The Swamp Fox’

The majority of the time at Corality we audit financial models or develop user friendly financial models for our clients. However, in a field that develops as quickly as financial modelling we need to make sure that we stay ahead of the competition - even in the long term.

Our method to constantly re-invent and develop our skill set is to experiment with Excel to develop new methods and applications that we had not earlier thought of. Every now and then we come up with something useful that can be integrated into our day to day practice of financial modelling. This time, I am not sure if that is what happened…

Using VBA to generate a cross-cursor

I presented a VBA training course in Sydney last week and one of the applications in that course is a dynamic cross-cursor for highlighting relevant data in a matrix format. (Here is an introduction to basic conditional formatting if you need one of those, and a bit more advanced here)

VBA cross-cursor with conditional formatting

The VBA exercise shows the power of the worksheet functions and how it can be used to improve model presentation.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Generate a circle around the selected cell

After the VBA course I kept experimenting with the same concept, i.e. updating the worksheet based on the properties of the selected cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Description: Returns column and row of the selected cell
'Author: Rickard Warnelid - Corality
'When to run: Automatically returns column and row of selected cell

    'Return selected column
    [CursorColumn] = Target.Column

    'Return selected row
    [CursorRow] = Target.Row

End Sub

Convert the VBA trick into a ‘Find the hidden treasure’

This time I thought I would expand the conditional formatting to generate a circle around the selected cell by back-solving the equation c^2 = a^2 + b^2. This gives a circle with a fixed radius. The second step was to apply a scaling factor inversely related to the distance of the active cell and a defined Target Cell. The scaling factor was then used to determine the size of the highlighted circle around the active cell.

Excel conditional formatting calculations

With “O17″ as the location of the ‘hidden treasure, the conditional formatting looks like this:

=SQRT((COLUMN(F20)-CursorColumn)^2+(ROW(F20)-CursorRow)^2)<$D$14

Graphical representation of combination of VBA and conditional formatting trick

Not even close...

Getting closer...

Even closer....

Found it!

I have been scratching my brain, but just can’t come up with a useful commercial application for this rather neat VBA trick. I suppose it could be used to really prove to your friends and colleagues that there is a real geek inside you, but that’s probably about it. None the less, it is a pretty cool feature given that it is generated with only two lines of VBA code.

Download the conditional formatting and VBA example here

If you can think of a useful application for this, please let me know!