SPECIALISTS IN FINANCIAL MODELLING

New Female Financial Modelling World Champion

Yes, you heard correctly – there is indeed a Financial Modelling World Championships! The finals of Modeloff 2013 were held last weekend, and we now have a new world champion, Hilary Smart, who was the only female to make it to through to the final round.
(more…)

Learn to Love your Excel Errors

As I was scrolling through the index for my book recently, I noticed the #REF error jump out at me.  Why is it that the #REF strikes such fear into a modeller’s heart?  Probably because out of all common Excel errors, it is the one which is the most difficult to fix.  It means that your formula is referring to a cell that doesn’t exist anymore!  If someone has saved a model with a #REF error in it, it’s not very easy to correct because the formula that it’s referencing has gone; so the formula needs to be completely rebuilt.

As annoying as they are, I actually like seeing Excel errors in my formulas believe it or not, as it means that something isn’t working, and therefore I can fix it.  As I always tell my training course attendees – the only thing worse than finding a mistake in your model is NOT finding a mistake in your model and your model being wrong!  Worse than that is your boss finding the mistake in your model.  There’s no faster way to lose credibility as a modeller than for others to find formula mistakes in your model, and there are a number of strategies to reduce error that you can employ reduce this possibility in your modelling.

If you’ve attended one of my courses, you’ll know I’m quite fond of the SUMIF function is it’s much more versatile and robust than other popular functions such as a VLOOKUP.  One of my issues with the SUMIF though is that it does not warn you if you use mismatching ranges.  In the example below, the result in cell C13 will not return an error; it will simply give a wrong answer without any warning!

Excel Errors

Source:  Fig 6.6 on page 96 of Using Excel for Business Analysis

The thorough modeller in this example will check the totals as they work, and then put an error check below in cell C17 to make sure that any incorrect entry in the future is quickly identified.

Even better than the SUMIF is its big brother, the SUMIFS function which can handle multiple criteria and  –  critically –will warn the user if the ranges are mismatching, unlike the SUMIF.  It’s one of SUMIF’s annoying little quirks that it allows mismatching ranges and exactly why SUMIF allows this, is something I’ll never understand.   It’s highly unlikely that we’d deliberately want mismatching ranges and far more likely that it’s been done inadvertently (although I have been known it use it for staggering periods).

So instead of cringing at a horrid #VALUE! error value, or supressing it with an IFERROR function, we should instead embrace, use and perhaps even love our Excel error values!  Figure out what’s wrong, and get to the bottom of it.  An Excel error value is far more helpful than the alternative which is to have deceivingly innocent number values which are in fact incorrect.

Get to Know your Excel Error Values

Error values such as #REF!, #NA! or #VALUE! are Excel’s irritating but necessary way of telling us that something’s not quite right with your formula or function.  Although they can be frustrating and time-consuming to rectify, dealing with them is an obligatory part of modelling.  Instead of being ignored, supressed or yelled at, Excel errors should be heeded, taken care of and according to a recent article, perhaps even celebrated!

Excel errors should not be confused with error checks, which are formulas created by the modeller that have been designed to alert the user as soon as the model is not calculating correctly.  Excel error values are errors displayed by Excel when the formula has not been built properly.   For example, using the multiplication symbol “x” instead of “*” e.g. =I36xJ36 is an incorrect formula which will return the value #NAME? because Excel does not recognise it.  Other common sources of error are brackets (parentheses) that do not match, or missing sections for functions.

If you get an Excel error message, click OK on the error message’s dialogue box. Then either edit the formula on the Formula Bar to correct the formula, or click the Cancel button to clear it.

Here are some common error values you might get, and how to fix them:

######

The railroad tracks; my favourite!  This is not really an error, and very easily to fix. The result is too long to fit in the cell, so just make the column wider.

#DIV/0!  

You’re trying to divide by zero.  Check that the formula is dividing by is not empty, as this will translate to a zero value.

#NAME?

There’s a name in the formula Excel doesn’t recognise.  You may have named a range, and then spelt it incorrectly, or typed “VLOOKKUP” instead of “VLOOKUP”.

If you used a name you defined, check its spelling. You can avoid this error by selecting a name in the Name Box instead of typing it in.

If you typed in a function, check its spelling or verify that such a function exists.
If you are performing operations on text, make sure you have enclosed it in double quotation marks.

#REF!  

Your formula refers to a cell that no longer exists, due to a change in the worksheet.  This happens if you deleted cells referred to in the formula or pasted moved cells on cells referred to in the formula.

This is probably one of the more difficult errors to fix because you no longer have access to the cell that was deleted. You will need to re-enter the cell reference which is missing.

#VALUE!  

The formula is using a different type of reference to what the function was expecting. The most common cause of this is if a user tries to enter text into a cell instead of a number, or the formula is referring to a label instead of the numeric value.  For example: “TBA” or “Less than 10” instead of a numerical value.  Check to see that some fields are not referring to cells containing text.

#N/A   

This means that there is some data missing; the value the formula looking for is not available.  A common reason for receiving this error is if a LOOKUP cannot find the criteria it is searching for in the source data.   Make sure that the

function can find the data you’ve asked it to look for.

With the exception of the #### railroad tracks, all of these error values can be suppressed using the IFERROR function which will stop the error from showing.  So, for example, if the cell A2 contained a zero, the formula =IFERROR(A1/A2,0)  will return a zero instead of #DIV/0!.  Be careful, of course that you don’t suppress a real error which should be shown, and fixed!

What’s New in Excel 2013

Why do they keep changing my Excel? As we all rely on Excel every day for our financial modeling and analysis work, it’s only natural that any new release will generate a lot of excitement as well as anxiety amongst users.
(more…)

Which Version of Excel are you Using? Poll Results 2012

Now that Excel 2013 preview is available, I thought it was time to do another poll to see what version of Excel business users are on now.  It’s been around 18 months since the last poll on Excel Version Usage, and I was interested to see if there had been more of a shift towards the new versions of Excel.

(more…)