Viewing all posts under ‘Financial Modeling’ View All Posts →

Lessons from Dr Sheldon Cooper on user friendly software

I really like this video extract from the Big Bang Theory. (A very funny and intelligent sitcom if you haven’t seen it.)

Although it is designed to get laughs—and Jim Parsons’ (Sheldon Cooper) comic timing is spot on—it got me thinking about user friendliness of software and raised some serious questions like: What does “user friendly” really mean? Can it mean the same thing to all people? And, is there such a thing as software that is TOO user friendly?

My feelings on these issues can be best explained by telling you about the single most important piece of software that I use in my career—Microsoft Excel. Read More…

Sensitivity analysis in spreadsheets

Also called scenario analysis and what-if analysis, perhaps the most useful feature of spreadsheets is their ability to run multiple versions of key assumptions through a model and present the effect on the key outputs.

Here’s the scenario (excuse the pun) — let’s say you’ve built a whiz-bang model to help you decide whether it is worth making a particular investment. Just like every good spreadsheet model, it’s at the mercy of its weakest assumption(s) — and you can think of at least 4 or 5 key assumptions that you know are important, but which are uncertain. So, you go about plugging in different values for each of these, and observing the impact on the 2 or 3 result cells that you care about. What you’d really like is a neat and easy way to set up, view, and play around with, various scenarios of your investment model.

In this post, we’ll explore a few ways to undertake this kind of sensitivity or scenario analysis in Excel, and discuss the pros and cons of each. Read More…


If you’ve ever taken a basic accounting course, managed inventory, or bought and sold shares, then you will probably know about the first-in first-out (FIFO) method of calculating cost of sales.

The concept of FIFO is easy enough, but have you ever tried to get a spreadsheet to calculate your FIFO cost of sale automatically? It is surprisingly difficult. Let’s look at a simple example. Assume that we have four share transactions: 2 buys and 2 sells. The question is how do we get the spreadsheet to automatically calculate the cost of sale figures in the yellow highlighted cells?

Essentially you have two choices here. The first method is to write a macro or a user defined function (UDF) to run through your prior purchases and sales, and figure out the cost of sale of the items in question. The second way is to write a normal spreadsheet formula to do this i.e., without any background code. The latter approach is better for people who don’t like enabling macros or who aren’t comfortable with UDFs. The downside though is that you need a couple of calculation columns. Oh, and the formula is a monster!

Let’s take a look at how you can do this. Read More…

EuSpRIG 2010

We recently presented Sumwise at the annual EuSpRIG (European Spreadsheet Risks Interest Group) conference in London.

Our talk, titled Sumwise – A Smarter Spreadsheet, was well received and we had many questions and compliments following the presentation. For us this represented an important milestone as it was the first time we have discussed and demonstrated Sumwise to a public audience.

Here’s a screen shot of the simple Sumwise model that we referred to in our talk.

Sumwise model demonstrated at EuSpRIG conference 2010

The good news is that we are now firmly on track to open up Sumwise in a limited Beta release later this year. As I mentioned at the end of the talk, if you are interested in previewing Sumwise before the public Beta launch, please email us. Read More…

7 Excel lookup formulas

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.

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 More…

Mortgage calculator formula

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:

=IF(A55=”",”",IF(A55=nper,I54+D55,MIN(I54+D55,IF(C55=C54, E54,IF($D$10=”Acc Bi-Weekly”,ROUND((-PMT(((1+C55/ CP)^(CP/12))-1, (nper-A55+1)*12/26,I54))/2,2),IF($D$10 =”Acc Weekly”, ROUND((-PMT(((1+C55/CP)^ (CP/12))-1, (nper-A55+1)* 12/52,I54))/4,2),ROUND(-PMT(((1+C55/ CP)^(CP/periods_per_year))-1,nper-A55+1,I54),2)))))))

I suspect that this formula gives the right results, but the issue I have with it is “how do you know”? I challenge anyone to dissect this formula and fully understand what it is doing and whether it is correct. Just look at it! It ends with no less than 7 closing round brackets! Read More…

Cost drivers

We take a look at a common type of financial model — the business driver or cost driver model. This is where you forecast costs or expenses based on the level of some relevant business driver.

For example, you may want to forecast advertising expense based on projected growth in revenue. Alternatively, you might forecast your phone expenses based on the projected number of employees. Some common cost drivers include:

  • rate of inflation
  • number of employees
  • revenue
  • number of units sold
  • salaries and wages

So what is the best way to set up your model to achieve this? The method we prefer is the lookup table approach. The key components of this approach are:

  1. Set out all the cost drivers you’ll use in a section at the top of your model. (Rows 5-8 below.)
  2. A column that will contain the cost driver for each cost line. (Column E below.)
  3. A column containing the base levels of the cost drivers and the associated base levels of each cost line. (Column G below.)
  4. Write a formula to calculate the cost forecast based on the driver specified. This is the same formula for every cost line and follows the design in blocks approach discussed earlier. (Cells I11:K16 below.)

Here’s a simple Excel spreadsheet with these components:

Basically what this formula is saying is “find the cost driver level for this period, divide this by the cost driver level for the base period, and multiply this by the base cost level”. The use of INDEX and MATCH is my preferred approach for looking up specific values in another table.

You can download this model and play around with it yourself. Do you have any ideas for improving on this technique?

What’s wrong with using Excel range names

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 RevenueHardware, 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.

The key issue is that Excel’s range names need to be set up and then constantly checked and maintained to ensure that they are current and accurate. If I have named a whole range of rows Revenue, I need to be sure at all times that this range name references the correct rows. What if I add another row to the end? What if I move things around? What if I change my Revenue section to Expenses? All of these things can impact the definition of the range name. Ultimately, it is just another thing that can go wrong, and another thing that needs to be checked and maintained.

This view is supported by some research presented at EuSpRIG’s 2009 conference which found that the use of range names in Excel models actually reduces one’s ability to find and fix errors in an Excel model.

Of course, there are good uses for range names (global variables is one) and there are ways to get range names to automatically extend and adapt to model changes (dynamic ranges using OFFSET). But, on the whole, and especially in the hands of non-experts, they are pretty dangerous.

Top 7 Sins of Model Design

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. So here goes …

1. DO NOT have separate sections for Inputs, Calculations and Outputs

Many people say you should have physically separate sections for Inputs, Calculations and Outputs. While this sounds catchy and pleasantly modular, it leads to bigger problems than it solves. Every part of your model should be easy to understand and review in context. That means when you read a formula you should be able to see the inputs to this formula without having to flip back and forth to other worksheets. Keeping things together and in context does not mean confusing inputs from calculations though. Inputs should still be clearly identified (cell shading, colours, etc.) and where possible, separated from calculations using a blank row or column.

2. DO NOT use named ranges

Naming cells and ranges using your native language sounds terrific. Surely a formula that says Revenue – Expenses makes more sense than one saying X51-X173? Well, this is all nice in theory, but what if your range named Revenue is pointing to the wrong range? Naming ranges takes time, as does checking whether your range names are correct. Named ranges just add another layer of complexity between the underlying logic and the way this is expressed in your spreadsheet. Read More…

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…

  1. Pages:
  2. 1
  3. 2

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.