Excel formulas

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?

There are lots of different ways of building a solid scenario manager for efficient scenario analysis, and some are better than others. Most people would agree that the OFFSET is the most common Excel function for this purpose, followed by LOOKUP and INDEX, but every now I see a new creative, or silly, way of solving this rather simple problem by using obscure Excel functionality.

Excel workbook with 13 different ways of creating a Scenario Manager

I have put together an Excel workbook with different ways of creating a Scenario Manager - i.e. to pick up the relevant value based on the selected scenario. My standard way of doing this is with an OFFSET as I like that you can expand the range of scenarios without having to re-code the function. From a transparency point of view though I would say that using LOOKUP would make the most sense.

Which Excel functions are acceptable from a best-practice point of view?

My preferences (not just from a financial model audit point of view but also as a user/builder of models) are OFFSET, LOOKUP and INDEX.

The worst offenders are definitely

• SUM (array)
• Nested IF (I can’t believe people still do this..)
• Choose (very old-school)
• MMULT (over-engineering to the n:th degree)
• ‘+’ (Although there are not complex Excel functions, it still gets very messy and hard to update)
• INDIRECT (I have never seen this one in practice, and I really hope it stays that way…)
• PRODUCT (array) (again, over-complication a simple problem)

List of all 13 solutions in Excel

All 13 solutions are listed below (and in this Excel Workbook: “Scenario analysis for freaks - 13 silly options”). The cell references in the list below will probably make more sense once you have had a look at the workbook.

• =OFFSET($F9,0,$D$7-1)
• =SUMIF($F$7:$J$7,$D$7,$F11:$J11)
• =SUMPRODUCT(($F$7:$J$7=$D$7)*1,$F12:$J12)
• ={SUM(($F$7:$J$7=$D$7)*$F13:$J13)}
• =LOOKUP($D$7,$F$7:$J$7,$F14:$J14)
• =IF($D$7=$F$7,F15,IF($D$7=$G$7,G15,IF($D$7=$H$7,H15,IF($D$7=$I$7,I15,IF($D$7=$J$7,J15,”N/A”)))))
• =CHOOSE($D$7,F16,G16,H16,I16,J16)
• ={MMULT((D$7=$F$7:$J$7)*1,TRANSPOSE($F$17:$J$17))}
• =($D$7=$F$7)*F18+($D$7=$G$7)*G18+($D$7=$H$7)*H18+($D$7=$I$7)*I18+($D$7=$J$7)*J18
• =INDEX($F19:$J19,0,D7)
• =INDEX($F20:$J20,0,MATCH($D$7,$F$7:$J$7))
• =INDIRECT(CHAR(96+COLUMN(F$21)+$D$7-1)&ROW())
• ={PRODUCT(IF($F$7:$J$7=D7,$F22:$J22,1))}

I know that offset is a controversial topic, so it would be interesting to hear other people’s views on how to best construct a scenario manager. Feel free to email me a file with your preferred approach (or more ways of how NOT to do it) and I will post it on the blog.

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

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

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 you are not in a position to engage an independent model audit company to perform an independent financial model review then the following check-list may be helpful. I do not attempt to delve into high level analytical checks but rather strictly focus on algebraic and structural errors that can be easily detected and therefore avoided.

Inconsistent formulae across rows

A solid financial model should have the formulae copied across all columns. Not having consistent formulae significantly increases the risk of error in your model. Even though on face value the model may be right, the inconsistent formula makes it very hard for anyone else to update assumptions or amend the model.

The most efficient way of detecting inconsistent formulae across rows is to purchase a financial model review add-in. There are a number of different products on the market and they all have their strengths and weaknesses. I would recommend downloading a free trial before purchase to make sure that that particular add-in works for you.

If you don’t want to purchase a model review add-in then you can explore the powerful, but often overlooked, built-in Excel functions

• ‘Trace all dependents’ (CTRL + SHIFT + ])
• Trace all precedents (CTRL + SHIFT + [)
• Row or Column Differences (CTRL + SHIFT + \) and (CTRL + \)

These functions are also accessible via F5 (Goto) → Special.

Complex formulae that are hard to understand

Overly complex formulae with nested IF, OFFSET, INDEX, MMULTS, ISERROR, etc, and other functions, etc, significantly increases the risk of an error in your model. This can be easily avoided by taking advantage of the many rows available splitting the formula into smaller, manageable parts.

The following is an example of what not to do:

=IF(G$4<=OpStartDate,0,SUMIF(Operations!$D$4:$AO$4,EOMONTH(G$4,IF(MOD(MONTH(G$4),3)=0,0,3-MOD(MONTH(G$4),3))),Operations!$D163:$AO163))/3

Check for unused assumptions

One very simple check to perform is to make sure that all assumptions in your assumption sheet have been used in calculations. If there are unused assumptions then there is an increased probability that you have mistakenly linked in an incorrect assumptions to flow into the model calculations.
To perform this test you could either use a spreadsheet review add-in or Excel’s built-in tracing functionality.
Sums that exclude lines that should have been included

It may sound unlikely, but one of the most common errors is SUM-functions that do not sum the right rows. Simply work through your SUM-functions to make sure that they have been coded correctly.

Review timing errors

A very efficient way of finding errors in your timing section is to simply change the timing assumptions and check it against changes in graphical representations of the model. Any unexpected spikes, troughs or unreasonable movements in outputs should be investigated. Experience has shown that Timing is a common source of errors and it is recommended that close attention to be paid to model Timing.

Mix of assumptions and calculations

A good model should have all assumptions located in a dedicated section of the model separate to the calculations. Financial model review software will have the functionality to distinguish inputs as opposed to calculations, but you can always use F5 → Special → Formulas → Numbers if you do not have one.

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!