Excel training

Financial modelling training on a shoestring

by Rickard Wärnelid on April 21 2009

3 comments »

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’

Course discounts for ‘private citizens’
Making our training more accessible to students and between-jobbers
For students and those between jobs, we will be offering course tuition at a 50% discount to our commercial prices effective 1 April 2009 - no, not an April Fool’s joke. With financial modeling as a marketable skill in these times, we believe our hands-on teaching will provide practical techniques for those looking to hone their skills a bit further. Who qualifies?

OFFSET function and tracing formulae

by Rickard Wärnelid on March 23 2009

7 comments »

The OFFSET function in Excel can be a great servant but a terrible master if you do not know how to properly harness its potential. When using OFFSET it is important to keep a few things in mind.

Most people don’t understand OFFSET

I have trained hundreds of financial modelers in the art of financial modelling. Something I have discovered over the years is that it is very easy to forget what it was like to learn financial modelling for the first time when everything was new and confusing. This probably explains why so many modelers go overboard with a function once they have picked it up as they find uses for their newly discovered knowledge. The OFFSET function is a great example of this, and it is not uncommon to see someone applying OFFSET left, right and center when they have figured out how it works.

Another observation from training analysts in banks and project developers is that only a very small minority of people understand OFFSET. And of those people who say they know OFFSET, only a tiny fraction are comfortable applying OFFSET to return a range (in conjunction with say MMULT or SUM). The result is that even if you are talented enough to code up something fancy using OFFSET, most other people will not appreciate your efforts since they don’t understand it.

To make other people’s life easier - avoid OFFSET when possible.

Tracing dependents doesn’t work as expected for OFFSET

Let’s analyze a simple example of the OFFSET function.

Cell C9: =OFFSET(C3,2,3)

OFFSET example

OFFSET example

The OFFSET formula correctly picks up the value (120) from the cell three columns to the right and two rows below cell C3. When tracing precedents of cell C9 Excel highlights cell C3 as this is the referenced cell in the formula. Using the tracing functionality on cell F5 does not give any clues whatsoever that this cell has been accessed via the OFFSET formula.

This non-traceable behavior of the OFFSET function introduces unnecessary risks in your model and should be avoided when possible.

For a more complicated application of OFFSET, have a look at the following formula.

Example of AVERAGE and OFFSET

Example of AVERAGE and OFFSET

Can you see how =AVERAGE(OFFSET(C4:E5,1,2,2,-2)) generates the result 125? I wouldn’t recommend this solution to anyone, but it is a good example of how a function can be used in a very confusing way.

Replace OFFSET with a simpler formula

One of the best properties of the OFFSET formula is that it is often replaceable by other formulae. It is often easy to replace OFFSET with LOOKUP, MATCH, SUMPRODUCT, IF, CHOOSE or SUMIF.

IF-fetishism and named-range-bonanza

by Rickard Wärnelid on March 10 2009

2 comments »

There are different schools of thought when it comes to saying when a formula is too long. I personally like the ‘the shorter the better’ way of thinking. Ideally, in my view, all formulas should be shorter than half the formula bar in Excel.

Every now and then I come across formulae that have been constructed with a complete disrespect for all financial modelling best-practice guidelines and common sense. Sure, one could technically get to the bottom of such an enigma - if you had a couple of weeks of nothing better to do and an obsession with decision tree analysis! These are three examples of what not to do and I don’t think they need any further explanation.

IF-fetishist

=IF(sen5_internal_refinance_option="Y",IF(mon_date1> sen5_refinancing_date,DF9,IF(AND(sen5_pmt_flag<>0, mon_date1<>sen5_issue_date),sen5_ob* IF(AND(mon_date1=EOMONTH(sen5_issue_date, ROUND(sen5_months_for_first_interest_calculation,0)), MOD(sen5_term_periods,1)>0), +1 +sen5_rate_periodic)^sen5_pmt_freq)* mon_date1Sen5_issue_date)/365,sen5_rate_periodic),0)), IF(AND(sen5_pmt_flag<>0, mon_date1<>sen5_issue_date),sen5_ob *IF(AND(mon_date1= EOMONTH(sen5_issue_date, ROUND(sen5_months_for_first_interest_calculation,0)), MOD( sen5_term_periods,1)<>0),(1+1+ IF(base_rate_override_option="Y", sen5_rate_periodic_ir_risk,sen5_rate_periodic))^ sen5_pmt_freq)* mon_date1sen5_issue_date)365,IF(base_rate_override_option="Y", sen5_rate_periodic_ir_risk,sen5_rate_periodic)),0))

Named-range-bonanza

=IF(EOMONTH(I$2,-Tax.VAT.Reimbursement) <>EOMONTH(Project.Close,0),- SUM(OFFSET(I114,0,IF(I2-Tax.VAT.Reimbursement* 30<Model.Start,0,-Tax.VAT.Reimbursement)): OFFSET(I114,0,IF(I2-Tax.VAT.Reimbursement*30< Model.Start,0,IF(AND(I2>=Constr.End, H2<Constr.End),0-Tax.VAT.Reimbursement, -Tax.VAT.Reimbursement)))),0)+IF(AND (EOMONTH(I$2,-Tax.VAT.Reimbursement)>= Project.Close, EOMONTH(I$2,-Tax.VAT.Reimbursement)< EOMONTH(Project.Close,6)),- OFFSET(I114,0,-ROUND((I$2- EOMONTH(Project.Close,0))/(Assumptions!$H$53/CtnP),0))/6,0)

Just plain wrong

=-IF(Debt.Bond1.Flag=1, J$434*IF(AND(J$434=1,K$434=0), I924+SUM(J920:J922), IF(AND(Debt.Bond1.Term*P- (J$11-1)<VLOOKUP($B919,Table.Depreciation,4)*P, Debt.Bond1.Term*P-(J$11-1)>=0),(I924+SUM(J920:J922))/ (Debt.Bond1.Term*P-(J$11-1)+1), (I924+SUM(J920:J922))/( VLOOKUP($B919,Table.Depreciation,4)*P ))),J$368*IF(AND(J$368=1,K$368=0),I924+SUM(J920:J922),IF( AND((Debt.Bank1.Term+Debt.Bank3.Term)*P-(J$11-1) <VLOOKUP($B919,Table.Depreciation,4)*P, (Debt.Bank1.Term+Debt.Bank3.Term)*P -(J$11-1)>=0),(I924+SUM(J920:J922))/ ((Debt.Bank1.Term+Debt.Bank3.Term)*P-(J$11-1)+1),(I924 +SUM(J920:J922))/(VLOOKUP($B919,Table.Depreciation,4)*P))))

If you have come across anything worse - let me know!

Search Blogs

Need a financial model audit?

Do you want to find our more about our financial model audit services??

Read more about Corality

Bloggers

Bing specialises in financial modelling in Excel and VBA and has a wealth of technical and analytica...

View their blogs

Graham specialises in advising both private and public sectors on the financial and commercial aspec...

View their blogs

Minh has extensive knowledge and experience in accounting and auditing within Australia and Vietnam ...

View their blogs

Peter is a UK qualified accountant with over 10 years experience and a strong background in financia...

View their blogs

Rickard has a wealth of project finance and financial modelling experience gained from a range of po...

View their blogs