Methods to ensure good spreadsheet design in order to reduce the risks posed by spreadsheets
The fundamental of GOOD SPREADSHEET DESIGN and enforcing GOOD SPREADSHEET DESIGN will help reduce these errors. And the basic concept’s associated with GOOD SPREADSHEET DESIGN are:
By using these basic concepts the spreadsheet you build will have an aspect and
With the regards to consistency. By being consistent in your design process, you eliminate some common errors so for example, consistency involves:
The other important aspect is to make sure that the look and the feel of your workbook is exactly the same. So if in one sheet of your workbook you use a column for one time period, try make all the sheets in that workbook, that same column the same time period.
Similarly with rows, if you have one row try make it one formula, and copy that formula all the way across.
From your experience with MAPS I am sure you can imagine the benefit this will have, and the ease with which you’ll be able to review a model where there’s one formula that goes all the way across, instead of having different formulas built, to handle different situations through the row
Just to emphasize the need for workbooks to look and feel the same, what we’ve got here is a simple spreadsheet with a summary section; Sales, Cost of Sales and Gross Profit. There’s a Sale summary where there’s a Unit Sold times Price, and a Cost of Sales area. And what you can see is
And now although its not the perfect way of doing it, you’ll see that it makes it a lot easier to understand where the formulas are coming from, so if you
Now for example, if we look at this cell here, well see that
Now it’s not the ideal way to find errors but it does make it significantly easier and you are quite likely to spot some sort of inconsistency, by building your models in a consistent and clear way
Getting back to other consistency ideas, it is highly recommended that you make use of Excel’s inbuilt functions. In the Function Wizard there are a number of useful functions, and what often happens is people don’t look through what Excel can do, and they try and generate their own functions, and then they use different techniques, different methods.
Excel has built a number of useful functions in it, and if you continue to use your functions not only will it be easier to understand for other users who use functions, but it will also be easier for you to make sure that your spreadsheet doesn’t contain any human generated errors
Another area is where you’ve got very complex formula, often you click on a cell and there’s a triple nested IF function, very complex, lots of brackets, lots of calculations. It’s often easier just to split it up over a number of rows or columns, just to make it fairly easy for the person to actually understand what is going on.
To emphasis this, here’s that same spreadsheet again
And so it is a bit of a process. You can use Spreadsheet Professional’s Formula Tracer, but you might not always have Spreadsheet Professional on the machines that look at it, and other people might not have it. So why not make it a bit easier to understand. Do the calculations in the area. So you’ll see here;
Now instead of having a complex formula you can look here, see that its referring to the correct year, know that its copied across correctly then your only concern is
It’s important to remember that a lot of people look at spreadsheets actually on a printout, and therefore this will be significantly easier to understand then some complex calculation built into one area.
Other areas with regards consistency. It’s useful to decide upfront on the format of the data you’re using. When you are designing a model, decide are you using whole numbers, are you going to use thousands, are you going to have millions. A lot of problems are caused because its not always clear what units are being used, and where you perhaps should be multiplying two whole numbers together, sometimes you’re applying, you’re multiplying the millions figure with the whole number. Make sure you know what units you are working in. Is everyone working in kilograms or pounds, kilometers, miles?
This causes a lot of problems because everyone works with what they are comfortable with, it’s not always clear on a spreadsheet. So even if you just have to make it perfectly clear, show what units are being used.
Try also be consistent with negative numbers, I like to show a negative as a negative. So where I have a Cost of Sales number, it would generally be actually a negative. And any calculations that need to be used on that will actually be an addition.
The reason for this is again focused very much on printouts. It’s not always clear on a printout where these should be adding or subtracting something. Maybe in some areas like Cost of Sales it’s obvious but what about interest is it interest received or interest paid?
So what I like to do is clearly show where the negative numbers are and where the positive numbers are.
The other concept with regard to GOOD SPREADSHEET DESIGN is separating the various areas of a spreadsheet. What this involves is to group like information with itself. So you can put all your Inputs together, now depending on the size of the spreadsheet what this might mean is that all the inputs will be on one sheet, and all the calculations on a separate sheet, and all the reports on a third sheet. Alternatively if it’s a fairly small spreadsheet, use the top section for Inputs, put the Calculations below that and then have a Summary report which brings together and shows what you are trying to achieve on your spreadsheet
If possible you can use colours as well, because it s not always that easy to separate out where Input and Calculations end. Be very specific and decide and upfront create a legend saying for example “ I like to use light blue as a colour for where my inputs are”.
Just a couple of other things,
To go back to the idea of colouring whole cells and not only the characters or the numbers. This is the way I like to do it. To have the whole number coloured in, some people make use of this method where the actual numbers or characters are in a colour.
The problem I have with that is, if for whatever reason this isn’t there, and we delete it, its not always clear that you need to put a number in there, and only the person who works regularly with the model would actually know that the Fixed Labour Costs need a number.
So I find that this is more explicit, and it’s more likely to actually result in a clean model with all the inputs entered.
Getting onto the third concept, integrity of calculations is important. So not only should the model be correct, but it should indicate to the user or someone reading it on a piece of paper, that it is correct.
As mentioned previously, it’s often senior people in the organization review models on a paper base, or it’s printed out in a report. When you build a model it’s useful to keep that in mind simply because they cannot go into the formula and see what you’ve done, so
And other design tips as shown;
Just another thing, it’s always useful to plan upfront, what you are trying to do and I recommend you literally just put a little drawing on a piece of paper, and I recommend you work with something like this
If you just take 5 minutes doing this you’ll be amazed at how much more structure your spreadsheet has, and as you can see the separation is set out for you – Input, Calculation, Output
As you use it more and more you’ll see it’ll get bigger and bigger, and your mindset will be built more in this way.
If you’re going to use a complex IF functions; again draw a quick picture. Specify the requirements and write down
In this case if it’s false there’s another IF function sitting there,
Once you’ve got the drawing setup its a lot easier to actually code the formula and know where the brackets start and stop
Occasionally when you have a spreadsheet that will be used for presentation you may want to neaten it up by either showing zeros in Excel or not showing the zero in cells that have a zero in them. We typically … Continue reading
If I understand the query below correctly, the one idea is to type some words in a cell, and then, while holding the ALT key down, click ENTER. Within the cell you will get another line. Hope this helps. Original query Hi, … Continue reading
The remarkable thing about Adrian's presentation is his ability to further change and adapt the focus of the course as issues arose. He was also able to keep the more skilled delegates challenged...– Alexander Proudfoot Consultants