Excel over-engineering

Some might say that Excel functions are a little bit over-engineered. Like the car manufacturer that brings out a new model each year with a slightly better engine and up-rated suspension, Microsoft (bless it) has been bringing out new versions for years. The result might be a fantastically high performing machine but not one that necessarily makes it easier to get from A to B. In Excel’s case the result is an awesome product but one that can be a little difficult to navigate. A typical challenge in Excel might have four potential solutions and that makes part of the fun just working out which solution to use.

Today’s problem: picking out the right piece of data from a data set

Today we’re going to imagine something that seems like it should be a very simple Excel challenge: picking a piece of data out of a list.  We’re going to give you four solutions, tell you our favourite but, ultimately, leave it to you to make the decision about which you like.  Here are some potential solutions:

·         Sorting the data manually

·         Pivot tables

·         The Sumifs function

·         The Sumproduct function.

For most delegates on our training courses there’d be something a bit new within the list above!

Today’s challenge: picking a piece of data out of a list

Imagine a set of data.  We’ve got a first column for employee name.  We’ve got a second column for expense category (e.g. “travel”).  We’ve got a third column for amount.  It’s a long list but imagine we want to work out what one of our employees (e.g. “John”) spent on travel with multiple trips away – we want to know his total spend on travel.

What if we didn’t have all the power of Excel?

Without knowing what Excel can do for us, what we might think of doing is sorting the data (Data > Sort in Excel).  First by employee, then by category.  That would group the data for us and make it easier to see how much John spent on travel – but Excel can do better than that.

Pivot tables

Some delegates on a financial modelling course know about pivot tables and, if they’re not familiar with them, we’re happy to help them get started.  In this case, you’d need to select the data and go Insert > PivotTable in Excel.

Pivot tables are great for quickly sorting and presenting data.  They will update as your data changes, but only if you right mouse click and click “Refresh”.

For the investigation into John’s travel spend, a pivot table would represent a bit of overkill.  There are some standard Excel functions that will successfully pick out the correct data from the list.

Sumif, Sumifs and Sumproduct

Some of our course delegates know about Sumif and Sumproduct.  Sumif kind of does what it says on the tin – it adds data if it meets a certain condition.  We have multiple conditions here though – that’s why we need Sumifs.  You want to use Sumifs for multiple conditions.

Some of our course delegates know about Sumproduct – it multiplies one list of data by another and sums the result.  Fewer of our delegates know that you can build conditionality into Sumproduct.  For our investigation, we could use that to tell us quickly how much John spent on travel.

Is there a best solution here?  Who’s for pivot tables?

It’s fun trying to navigate Excel, including any over-engineering!  Some people love pivot tables – the way they will sort all your data very quickly and present it nicely.  Others though realise that some of Excel’s standard functions are perfectly competent and capable of picking out the one piece of data you are interested in and plonking it into the cell you want.

Sumifs vs. conditional sumproduct

It’s hard to differentiate between these two formulas though.  Sumifs is designed exactly to do this job – so there’s a vote for that.  But then, if you can understand how the conditional sumproduct works, and pause for a moment to consider how simple that formula ends up being, then you’d probably be forced to vote for that.

Here’s to engineering

Shall we just pause for a moment to celebrate the power of engineering and the way it brings us all the powerful products we love to use?  If that results in a little bit too much engineering sometimes – so what? It’s the small price we all end up paying for fantastic solutions! 

Excel's XNPV formula

Have you come across Excel's XNPV formula before?

It's a bit like Excel's XIRR function. To get it working what you have to do is assign dates to cash flows.

The problem with Excel's standard NPV formula.

The problem with the standard NPV formula is that it discounts the very first cash flow you point the formula to, whether you want it to discount that starting cash flow or not. If you made an investment right at the start of a project (often you would) the standard NPV formula could easily trip you up. You wouldn't normally want to discount that first cash flow.

XNPV has its own problems though

Immediately Excel's XNPV function becomes potentially attractive. It assigns dates to cash flows and, if you experiment with it, you'll find it doesn't discount the first cash flow you point it to. However, XNPV comes with a problem all its own. XNPV never discounts the first cash flow, no matter what date you have assigned to the initial investment. Even if your project is delayed and pushed out further, XNPV stays the same. Effectively the formula is assuming that the date of the first cash flow is your valuation date.

Fortunately there's a simple fix (but you have to know about it)! If you set your XNPV calculation up carefully you can easily discount the cash flows back to today or any valuation date that you choose. All you have to do, at the start of the calculation, is include a zero cash flow with your start valuation date attached.

If you click on the picture below, you can download an example where you can see all that working.

Watch out for Excel's financial functions in your modelling

All of these Excel modelling functions (IRR, XIRR, NPV and the XNPV formula) are designed to be quick and easy to use. Unless you understand exactly how they work though, there is a risk you make a mistake in applying them (and reach the wrong decision).