Podcast – Excel Tips for Best Practice Modelling

Some of you will know that ever since Indian Excel guru and blogging sensation Chandoo visited Australia in 2012 to run his extremely popular Advanced Excel and Dashboard courses, I’ve been trying to convince him to come back Down Under and run another series.  We are hoping that he’ll find time to make another visit in 2015 and we’ll certainly keep you posted and hope to announce it soon.  He hasn’t agreed to come just yet, but as we were chatting last week, he did talk me to into joining

Tool Selection: Why do we Use Excel?

We often hear it said that Excel is the “second-best solution” to a problem. There is usually a better, more efficient piece of software that will also provide a solution but we usually default to the “Swiss army knife” of software, Excel, to get the job done.

Deficit Levy Tax Calculator

If you’re interested in how the new deficit levy will impact your tax in the new financial year, then take a look at our latest Excel Income Deficit Levy Tax Calculator.  Our tax calculators have always been our most downloaded posts on the site and I’m not sure if it’s because you’re all so interested in financial modelling tax tier calculations, or you just want to know how much of your salary goes in tax!


Improving Excel Memory and File Performance

If you have sluggish Excel models, it might not just be the file size that is slowing things down.  Often it’s the memory being used and when things get really bad, you might even get a “Not enough Memory” or “Not Excelenough system resources to display completely” error message. When you get this frustrating message, the only solution is to close Excel and restart it, so let’s look at some ways to improve the performance of your Excel models.

Using Excel in the Age of Big Data

We are constantly being told that we are living in the Information Age.  The Industrial Revolution marked the beginning of the Industrial Age and similarly, the Digital Revolution started the Information Age.

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.

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.