Excel's Offset function

Previously we looked at how to use Excel's Sumproduct function to pick data out of a table in Excel. That article was in response to a question we had from a delegate on a recent financial modelling course. Some Excel users are unaware that Sumproduct has a special hidden power which makes the function helpful when picking data out of a spreadsheet.

The role of Offset in amalgamating e.g. quarterly data

Here's the example we developed in that first Sumproduct article. You can click on it and download the example:

The example works. The quarterly data gets amalgamated up successfully using Sumproduct. The very observant among you (some would say 'fussy' or 'a bit picky'; we're fine with that) will have noticed that we've broken one of Excel's little golden rules in putting that example together.

Good practice: set your financial model up so that you can fill your Excel spreadsheet across right

If you download the previous spreadsheet example and click on cells C11 and C12 you'll notice that we've typed those cell entries in manually. What we should have done at a minimum is shade the cells a different colour (the spreadsheet uses blue) to show that they're hard coded inputs. Shading the cells blue is one thing, but imagine the Excel spreadsheet were a lot lot bigger. Imagine it had 10s or even 100s of columns (imagine a 30 year monthly or quarterly financial model). It would get pretty painful typing those Y1, Y2, Y3 entries in manually for 10s or 100s of columns. We could improve the spreadsheet further if we could turn line 11 into a formula that we could fill to the right easily.

Excel's Offset function can help you 'jump' and amalgamate columns

In the example the Offset function could help us 'jump' 4 columns at a time. In the example below Offset, combined with Sumproduct, provides a more complete solution for amalgmating data. You can click on the image to download the example:

Embedding Offset inside Sumproduct

If we wanted to get carried away we could take the example further by embedding the work that Offset is doing (allowing us to 'leap' 4 colulmns at a time) inside the Sumproduct. But would that make the whole example any clearer? We think not. Probably better to have the extra line and the steps (Offset, Sumproduct) broken out separately and a bit more clearly.

Good Excel modelling practice guidelines

We've ended up with quite a sophisticated little example where we're combining Offset and Sumproduct to amalgamate data as efficiently as possible. But at the same time we've been trying to observe some simple good modelling practice rules as we go along. Here's a recap on the rules we've been trying to follow:

• Always colour code your inputs. Those cells where we've manually typed ('hard-coded') an entry are ones that we might want to come back to later and change. Those are our input or assumption (as opposed to our calculation and output) cells. It would be a good idea to highlight those input cells with a different colour. Essentially we're trying to give the user a clear message: "This is an input cell. This is something that you might want to change later."
• Set up your calculations so that you can always fill right. We spend a lot of time filling right in models - particularly in those models that run for a long time and have a lot of columns ("Ctrl" "R" is a great keyboard shortcut for those who regularly like to fill right). What we don't want to do is find ourselves making manual 'tweaks' to formulas as we work across to the right. The last thing we want is to have to make manual entries to extend the model to the right. What we should always be doing is thinking about how we can manufacture a calculation that can be filled all the way across the model. The last thing we want is two formulas sitting next to each other that are slightly different from each other. In such a case a new user could pick up the model and, being in the habit of filling left to right, fill the first formula right across the second. Having two different formulas sitting next to each other will prime a model for disaster. Always try and set your calculations up so they can be filled happily from left to right.
• Avoid nesting or embedding functions inside each other. Remember we had a bit of discussion about whether we should put an Offset function inside a Sumproduct? It might have made us feel clever and it would eliminate a line in our financial model. But getting into the habit of putting functions inside functions has a nasty side effect. Your functions become long, complex and hard for a new user (the one who's not as clever as you) to check and understand. For clarity's sake we'd say that it's much better to pay the price of a few extra lines in your model, perhaps consigned to a dedicated 'workings' section at the very bottom. We'd say that, for clarity's sake, it's much better to step a new user through any complexity line by line. That's going to help someone new use your model or check or modify what you've done.

Excel as an art form

Rules sound very dull and we do expect you to break them from time to time (life wouldn't be any fun if you didn't break the odd rule from time to time now would it?). But even great art seems to follow a few rules most of the time right? Paint generally gets applied to a canvass (except, perhaps, in the case of Tracey Emin's bed). There's usually a paint brush involved. Generally light colours tend to go on top of dark. We're not pretending that you're making great art with Excel but if you can get your models to the point where:

• You are able to do something a bit clever like employ just a few simple formulas to solve some pretty hefty data manipulation problems and, at the same time
• You are able to employ a few good modelling principles (the common language that enables other model users to understand what you've done)

Then we wouldn't be at all surprised if you discovered yourself sitting back and taking a long lingering look at what you've done, perhaps finding a self-satisfied smile creeping across your face. OK it might not be Leonardo da Vinci's Mona Lisa that you're looking at but it's still your very own private and somewhat wonderful creation.

Who says Excel financial modelling can't result in a thing of beauty? We think it can!