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
June 2, 2010
I had the privilege of being selected to present Sumwise at the Innovation Bay dinner last night. The presentation went really well and I received some good questions and valuable feedback.
One question at the end of my talk caught my attention. I had discussed how traditional spreadsheets such as Excel actually build in complexity and opacity, and I gave the example of a formula from a mortgage calculator spreadsheet I searched for and downloaded from the web. This formula calculates the repayments required for each payment period: Read the rest of this entry »
3 Comments | Financial Modelling | Tagged: Excel, Sumwise, Lookups, Innovation Bay, Mortgage Calculator | 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 21, 2010
I attended a presentation yesterday by Ari Klinger of OMG on online marketing. Although this was pitched as an introductory session rather than an advanced session, I was keen to attend as I have high regard for Ari and what he has achieved and figured I might pick up some tips and tricks. (By the way, OMG own 30,000 domain names in Australia and are steadily building content and directories against these domains.)
Here are some of the takeaways from this session: Read the rest of this entry »
Leave a Comment » | Marketing | Tagged: Online marketing, Social media | 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
December 9, 2009
Here’s a neat little trick to dramatically cut down the number of separate charts in a spreadsheet model.
Say you’ve got a monthly profit and loss statement with many rows of revenue and expense information. Once you’ve got all of your data in a worksheet you now want to analyse the information. Often you’ll want to select a particular line in the P&L and see a chart of the data. Well, you could just create a nice looking chart of that row of data and position it somewhere on your worksheet. Then you could repeat the process for every row of data that you’re interested in. Well this is lot of manual effort, plus it adds clutter to your worksheet. After all, you may only be interested in viewing one chart at a time. (Let’s assume for now that you don’t need to print these all out.) Read the rest of this entry »
Leave a Comment » | Financial Modelling | Tagged: Excel, Macros, Charts, Conditional formatting | 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
