One of the most powerful features that Sumwise introduces is the concept of row and column structure that can be referred to directly in formulas.

In Sumwise, rows and columns can be nested underneath other rows and columns. This enables you to collapse and expand various parts of your model, and focus in on the areas that you are interested in. The concept of structuring data is not new or unfamiliar (at least as far as rows are concerned), but Sumwise applies this concept to a spreadsheet and takes it to a whole new level (excuse the pun).

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…

If you’re reading this post, you’re on our new website, which we just launched today.

Our old site didn’t get into much detail about Sumwise, and this suited us fine while we were in semi-stealth mode. But, now that we’re starting to open up our modeling application to users, we felt we needed a site that said a bit more about the application, and about us.

We’ll be adding lots of content to our site over the next few weeks and months, so please connect with us, so that we can keep you updated. Of course, if you’d like early access to the beta, please register here so that we can set up an account for you, and send you some info.

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.

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…

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…

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:

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…

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:

Set out all the cost drivers you’ll use in a section at the top of your model. (Rows 5-8 below.)

A column that will contain the cost driver for each cost line. (Column E below.)

A column containing the base levels of the cost drivers and the associated base levels of each cost line. (Column G below.)

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?

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.

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.

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

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…