Dynamic Excel charts

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. 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.)

One method to help reduce the clutter is to use a list box or combo box control on your worksheet and link this to your range of P&L line items. That way you can select the row you are interested in from the list box and see the relevant data displayed in the chart.

Dynamic Chart

There are some problems with this technique though. You need to set up the List Box control and then make sure it continues to refer to the correct data when you change the structure of your model. It also requires that all of your row labels are in the same column – as the List Box control only looks in one column for its data. I like to use multiple columns from my row labels, to give a kind of nesting or hierarchy effect – so this List Box technique does not work all that well for me. Read More…

Ditch the mouse

One of the things that distinguishes a power Excel user from a novice (or even intermediate) user, is the ability to get things done without using the mouse.

There are a bunch of sites and blogs which show you all of the available keyboard shortcuts in your chosen version of Excel, but becoming a better Excel user is not simply a matter of memorising all the keyboard shortcuts — it’s as much about knowing which shortcuts are the most valuable (in terms of time-saving, efficiency, design and accuracy), and when to use them.

Once you know how certain keyboard actions behave it is easy to start to design your spreadsheets in a way that lets you take advantage of them. For example, you may know that holding down Ctrl and then hitting an arrow key on the keyboard will jump to the next “block” in the selected direction (see right). When you hold Ctrl and hit the down arrow key the selection moves to the end of the current block or the start of the next block — a block being a group of non-blank contiguous cells. Read More…

Internal Rate of Return (IRR)

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.

The problem with the above rule of thumb is that it doesn’t help you work out the result you’re interested in for other combinations of inputs. This is because the relationship between the variables is exponential rather than linear. You either need a calculator, a ready reckoner, or a large brain. In any case, it is useful to first explore the relationship between IRR (r), years the investment is held (t), and the sale multiple achieved (m). Read More…

Solve circular references

Most people who use spreadsheets for financial modelling will have encountered the dreaded problem of circular references. In this post, we’ll show you how to remove circular references from your spreadsheet model using basic algebra.

(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.

The longer it persists the harder it is to find and fix. I could write a whole post on clever techniques you can use to find a rogue circular reference – but that’s not what this post is concerned with.

Today, we’re concerned with the problem of “intended” circular references. These are still problems for a number of reasons. Firstly, they require that you turn iteration on (Tools>Options>Calculation) so that Excel can hopefully hone in on an answer. If iteration is not turned on then Excel will not be able to calculate an answer and will display an error, pop up various windows telling you all about circular references, and even display a toolbar dedicated to helping you fix it. Read More…

Straight-line Depreciation

In reviewing a few models today, I was reminded how rare it is to find straight-line depreciation modelled in a concise, neat and accurate way. Although straight-line depreciation is notionally the simplest depreciation method, it is possibly the most difficult to model — as you’ll see below.

Firstly, let’s review what straight-line depreciation is. In its most basic form, straight-line depreciation expenses the original cost of an asset in equal annual amounts over a given number of years. For example, if an asset is purchased for $1,000 and you wish to depreciate it using the straight-line method over 5 years, then the depreciation expense will be $200 for each of the 5 years. Of course, you can introduce complexities such as depreciating less than a full year’s charge in the year of purchase, or taking into account a salvage value, but to illustrate this concept we don’t need to add these complexities.

Calculating the depreciation schedule for one asset or one purchase is easy.♦ The complexity arises when you have numerous purchases over numerous periods, and the schedules overlap. For example, assume you purchase assets totalling $1,000 in year 1, $500 in year 2 and $800 in year 3. And you now want to calculate the total annual depreciation expense in one schedule rather than in three separate schedules. The most common approach to calculating the total depreciation is to do this using a 2-dimensional matrix – as follows:

Depreciation Matrix

Although this schedule is nicely laid out and the calculations are accurate, this approach suffers from a number of problems. Read More…

Review of Google Docs

I’ve been using Google Docs spreadsheet more and more due to its awesome ability to share spreadsheets with multiple people via the web. You get to see in real time which cell someone else is editing and you can have multiple people editing a particular spreadsheet simultaneously.

I met with Rickard Wärnelid of Corality yesterday, and we got talking about collaboration functionality within spreadsheets. (Rickard subsequently posted some comments about using Excel’s shared workbook functionality on his financial model audit blog.) I don’t use Excel’s shared workbook functionality mainly because I do most of my modeling work alone. However, as Rickard points out, Excel spreadsheets that have this sharing functionality turned on have limited functionality – so much so that I could not see myself wanting to use it.

When I do need to work with others (e.g. a client, a colleague) I generally make sure only one of us is in the spreadsheet at any given time. This involves phone calls, emails or IMs to ensure that you are “it” and can safely open the model and make changes. Clearly this is suboptimal – something Google, Zoho, and others have latched onto with their web spreadsheet offerings. Now even Microsoft is coming to the party with Office 2010 – which by all accounts will provide similar collaboration functionality.

Of all Google Docs’ features, collaboration is the one that might get me to switch away from Excel. So, I thought I’d do two things: 1) publish a cash tracking spreadsheet I developed in Google Docs and have been using with my wife to track and understand what we are spending our cash on, and 2) list what I have found to be the good and the bad about Google Docs (vs Excel of course).

Cash Tracking Spreadsheet

Cash Tracking Spreadsheet

Read More…

What’s the difference?

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.

Fortunately, Excel provides a neat little shortcut to help you quickly check a range of cells for logical consistency. It is contained (buried?) in the Edit>Go To menu, which itself can be accessed using Ctrl G or F5.

Once you click the “Special” button you get a whole list of useful goodies, including the two items we are interested in: “Row differences” and “Column differences”. Unfortunately you can only perform one of these operations at a time, so if you’re checking a range which is two dimensional you’ll need to first do one and then come back and do the other.

How to find the error

As an example, let’s say you have a block of cells which should all have the same logical formula. Let’s assume you gave your spreadsheet to a colleague and they sent it back to you and now you suspect they messed up some of the formulae. Here’s how to quickly figure out where the error is …

Firstly select the range of cells you want to check (see right). You’ll notice that the top left cell is the active cell. We’ll be checking all other formulae in the range against this cell. Next, hit F5, click Special and select Row differences. After you click Ok, any cells in the block that do not contain the same logical formula as the cells in the first column (i.e. C) will be selected. It is probably best at this point to shade all of these cells some garish colour so that they stand out. Then go and fix them. Read More…

Design in Blocks

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.

One of the benefits of designing in blocks is that it becomes easier to see where areas of your model start and stop. It is also then easier to add new rows or columns to the block. Furthermore, it becomes much easier to navigate through your model, especially if you use keyboard shortcuts like Ctrl and arrow keys.

Design_In_Blocks_Example

But possibly the greatest benefit comes when writing formulae that refer to the block. In this case, including the blank row/column at the start and blank row/column at the end of the block will ensure that new rows or columns added to the block later will always be included in the formula.

In this  example, you can see that the total formula in cell D10 refers to the block of items above. Importantly, it includes the blank row at the start of the block (row 3), and the blank row at the end of the block (row 9). This means that when a new row is added at the start or end of the block it will be included in the total formula.

Use the INDIRECT route

The Excel INDIRECT function is one many people don’t know about. And for those who do, many/most will tell you not to use it. But I beg to differ. Used correctly and sparingly, this can be a great way to make your models more understandable, and also prevent errors creeping into your models in the future.

Let’s say you’ve selected cell E20 and want to refer to the cell above it. And let’s say that, no matter what you do to the cells above E20, such as insert rows, delete rows, delete cells, etc., you always want the formula in E20 to refer to the cell directly above it.

The way to achieve this is to enter the following formula

=INDIRECT(“R[-1]C”,0)

This is effectively saying “give me the value of the cell one row above me and in my column”.

The thing that many experts have against this function is that it is a “volatile” function. That is, it is recalculated every time Excel recalculates, regardless of whether or not the cells the formula is dependent upon have changed. Volatile functions can reduce the performance of large and complicated models. I find that many of our models are not overly large and do not suffer from slow calculation, so using INDIRECT brings benefits without any adverse effects on speed.

Note: the OFFSET function does a similar thing to INDIRECT, however, for the example used above I find the syntax of INDIRECT easier to understand. Try both and decide which works better for you.

Styles are more fashionable

One of the most powerful and most easily understood (but least used) features in Excel is Styles.

Styles allow you to define a cell format (things like number format, shading, borders, etc), give this format a meaningful name (such “input”) and then apply this format to cells in your workbook.  ”Ok ” — you say — ”But I can just as easily format cells individually on the fly, and I can even copy a cell’s format and paste this to other cells”. Yes, you can, but here’s why doing it like this is problematic:

  1. You have to find a “source” cell whose format you like, then click something or type Ctrl-C to copy, then find your “destination” cell or cells and then more clicks to paste this format. It’s a fair bit of effort.
  2. To change the format of all of these similar looking cells you have to repeat the whole process.
  3. You may want to share your model, or print it out, with certain shadings or formatting characteristics turned off. More effort. And how do you revert back to the way you want to see your model?

Well, Styles solve all of these problems and more.  Once you have created a Style (let’s say you call it “input”) then applying this to other cells in your workbook is often only a 2-click process. Then turning parts of the formatting off globally is easy – just modify the Style via the Style dialog (Format>Style). Also, if you use meaningful names then when you click on any cell which has a Style applied to it, you’ll see the name in the Style drop-down in your toolbar.  (You’ll probably need to customise your own toolbar first to get the Style drop-down to appear.)

Anyway, setting up your Excel environment to use Styles (not too hard), and learning how to use them (easy), is a really powerful feature and is highly recommended.

Disclaimer: I use Excel 2003, and have not migrated (downgraded?) to Excel 2007. Styles work a bit differently in 2007 and are given more prominence, but I’m still not sure that many people use them properly.

  1. Pages:
  2. 1
  3. 2
  4. 3
Popup

Register your Interest

Please register to keep up to date with our developments.

Register your Interest

Please register to keep up to date with our developments.

url to play tank trouble | http://russian-language-for-couples.com/uadreams-site.html