June 9, 2010
Many spreadsheet users will know that when you need to pick out a value from a list, it’s a good idea to use a lookup formula. There are numerous ways to do this, but which is the best? Each formula or method has its advantages and disadvantages depending on the circumstances.
It’s hard to cover all of these circumstances, so we’ll focus on one specific yet common situation. Let’s say you have a unique list of items, say “fruit”. Each type of fruit has a numerical value associated with it — perhaps the quantity or price of the item. Somewhere else in your spreadsheet you want to enter the fruit item you’re interested in and have a formula return the specific value for this fruit item. Let’s further assume that the items are not sorted in any particular order.
The table below gives you the idea.
In this table, we want to enter the name of the fruit in cell B10, and we want the value in cell C10 to automatically pick out the correct quantity in the source data.
So, now that we’ve set the scene, let’s look at 7 different ways to do this in Excel.
Read the rest of this entry »
4 Comments | 
Financial Modelling | Tagged: Spreadsheets, Excel, INDIRECT, Design, Errors, named ranges, INDEX, MATCH, Lookups | 
Permalink
Posted by Darren Miller
May 19, 2010
I’ve been talking to a bunch of people about Sumwise recently and when they ask me to give them an example of what it does, I generally say something like this:
In Excel you might write the formula =Sum(X31:X37) to add up the items under the Revenue heading. One problem is that this is cryptic and doesn’t mean anything unless you confirm that the range X31:X37 refers to the items you want. And what if the items actually extend to row 38? It is very hard to check and correct this unless you carefully check and review your formulae.
In Sumwise though, there is no A1-style grid. Rather, rows and columns are referred to by user-defined labels such as Revenue, Hardware, or Jan. In Sumwise, the formula =Sum([Revenue.]) would add up all the rows underneath the Revenue row. The formula means what it says and does not need to be translated to be understood or checked.
The usual response to this is “don’t Excel’s range names do the same thing?” The short (and long) answer is NO! In terms of making your spreadsheet model more understandable and reducing potential errors, Excel’s range names actually compound, rather than solve, the problem. Read the rest of this entry »
Leave a Comment » | Financial Modelling | Tagged: Spreadsheets, Excel, OFFSET, named ranges, Sumwise | Permalink
Posted by Darren Miller
April 9, 2010
Everyone loves lists, so here’s one of my own — the top 7 things not to do when designing and building a financial model. Nothing controversial in this I hear you say? Fear not — this list will ruffle a few feathers. A few of these are on others’ best practice lists. Read the rest of this entry »
1 Comment | Financial Modelling | Tagged: Spreadsheets, Excel, Formatting, Styles, Design, Errors, named ranges, multiple worksheets, VBA/macros | Permalink
Posted by Darren Miller
September 1, 2009
Many people know the old rule of thumb that at a 10% annual return, money doubles every seven years. (Actually it takes 7.27 years, but I guess this wouldn’t be a great rule of thumb.)
People who work in private equity, venture capital, or any other area where investment time horizons are measured in years rather than days, weeks or months – are generally pretty interested in the IRR of their investments. You’ll often hear a VC say something like “… we need a 35% IRR to justify so and so investment.” There’s plenty of discussion and debate elsewhere about the relevance of IRR as an investment measure, and the benefits of using IRR versus NPV (net present value) – so I’ll avoid getting into this debate here. Read the rest of this entry »
1 Comment | Financial Modelling | Tagged: Spreadsheets, Excel, Algebra, Conditional formatting, IRR, Data tables | Permalink
Posted by Darren Miller
August 17, 2009
Most people who use spreadsheets for financial modelling will have encountered the dreaded problem of circular references. If you don’t know what a circular reference is, then this post is not for you – at least not yet. Perhaps bookmark this page and come back one day when Excel barfs at you for writing a formula which refers to itself.
Circular references are the financial modeling equivalent of the old philosophical causality dilemma, typically stated as “which came first, the chicken or the egg?”
Circular references in spreadsheets can be either a) intended, or b) unintended. If they are the latter, then there is a mistake in your model and the circular reference should be eliminated as soon as possible. Read the rest of this entry »
5 Comments | Financial Modelling | Tagged: Spreadsheets, Excel, Design, Circular references, Subscriber equation, Algebra, Errors | Permalink
Posted by Darren Miller
July 7, 2009
Following on from the previous post, once you’ve started on the path of setting out your model in blocks of related cells, you may become obsessed with knowing that all the cells in the block contain the same formula. After all, there’s no point in arranging your spreadsheet into nice blocks of logically consistent cells, if one or more of the cells in a block has a different formula to the others. Read the rest of this entry »
2 Comments | Financial Modelling | Tagged: Spreadsheets, Excel, Design, Blocks, Inconsistencies, Macros | Permalink
Posted by Darren Miller
June 16, 2009
One of the more important considerations when attempting to create a well designed spreadsheet model is the concept of designing in blocks.
“Blocks” is a colloquial rather than a technical term, and means a “range of contiguous cells that have something in common”. This could be something trivial like number format (currency, percentages, etc.), cell shading, and the like. Or it could be something more meaningful like a logically identical formula. Read the rest of this entry »
1 Comment | Financial Modelling | Tagged: Spreadsheets, Excel, Design, Blocks | Permalink
Posted by Darren Miller
