Home // Video Tutorials // 3 - 6 (Intermediate) // 5 // Good Spreadsheet Design

Good Spreadsheet Design

Methods to ensure good spreadsheet design in order to reduce the risks posed by spreadsheets.

Good Spreadsheet Design

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:

  • You must design consistently and make sure that you are aware of what you do, and others can see clearly what you are doing
  • You must separate where Inputs can be included
  • Where Calculations happen
  • Where Outputs or reports are situated,
  • And you must make sure of the integrity of the calculations. Not only making sure that they are correct, but clearly showing any users or yourself that they are correct.

By using these basic concepts the spreadsheet you build will have an aspect and

  • it’ll be simple to look at
  • it’ll be easier to understand
  • it’ll be easier to review and
  • it’ll be significantly easier to rely on

With the regards to consistency. By being consistent in your design process, you eliminate some common errors so for example, consistency involves:

  • Cells should contain either data or formula, preferably never both So in the example down here, you’ve got two ways of calculating the Vat on a Sales Price The wrong was is in the Vat to actually hardcode the Vat percentage in.
  • So here
    multiplying by .14 although perhaps correct at the current stage, will add difficulty later, Spreadsheets do get used again and again, and to actually find all the references to .14 when it’s discovered that the Vat rate is now 15%, 16% will be extremely difficult. The better way to do it is you know you’ve got an Input, a Vat rate that will affect a couple of calculations.
  • Set up an input cell, colour, to show its an input cell and refer all formulas to that cell,
  • then if the Vat rate changes, its just a matter of changing one cell and you know you model will adapt all the calculations

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

  • in column C is Year 1 and column D is year 2
  • and you’ll see that on the Sales, C is Year 1, D is year 2
  • and similarly with Cost of Sales

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

  • click on the Sales formula
  • we’ll see that it goes to the Sales sheet and it uses C2 and C3, and obviously there can still be an error here, but at least if you have built it in the correct way you know that
  • column C is referring to Year 1 on all your sheets

Now for example, if we look at this cell here, well see that

  • this is taking the cost from the Cost of Sales sheet.
  • Its taking column F, and we’re in column F so we are happy with that,
  • times column F
  • and its adding column E in,

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

  • if you look at the Cost of Sales formula,
  • You’ll see that it makes reference to a number of cells and the calculation is actually happening at the Summary page.
  • So in order to actually track down if this is correct,
  • you can use your Auditing toolbar. But you now need to know that;
  • you are taking Cost of Sales which is C 2, which is that one which you’re happy with
  • then you just come back here, its multiplied by that one there
  • and obviously you’re happy with that

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;

  • here’s Total Variable Costs. It is a calculation multiplying that by that,
  • that’s the Fixed Labour Cost,
  • there’s your Total,
  • and now in the Summary sheet instead of having this formula here, you can just say equals,
  • go to Year 1,
  • enter
  • and then you can just copy
  • and paste it.

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

  • to go the Cost of Sales and just follow this back and make sure that you are happy that this is calculating correctly. And just by splitting the calculation over a couple more rows, it’s a lot easier to understand.

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,

  • fill in the whole cell and not only the characters, and we’ll get onto that a little bit,
  • preferably use light colours, because when you print out a spreadsheet if you use darker colours you can actually sometimes not see the number underneath, so use a light bule, light yellow etc
  • and where you can use protection. Protect cells that you don’t want to be overwritten, you don’t have to put a password on its just a warning so that you don’t accidentally overwrite it

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

  • Explicitly show that what has been happened.
  • Use error checks where at all possible and build them in so that it clearly states on the spreadsheet whether it’s ok or not.
  • You must Cross Cast Matrices just to make sure that the calculations going down are the same as the calculations going across. You’d be surprised at how many errors can be picked up this way.
  • If you’ve go a balance sheet, prove the balance sheet
  • If you’ve got a cash flow statement, compare it to the balance sheet, try not use balancing numbers
  • If you’re building a particular spreadsheet and there are general manual checks you do to assess the reasonableness and see if it works, build them into the spreadsheet. Its a lot easier to build them into the spreadsheet than to have to redo them manually each time
  • And use the Data Validation tool in Excel to control some to the inputs that go into it

And other design tips as shown;

  • multiple workbooks can cause problems, and I recommend you avoid them where at all possible
  • Develop a good naming convention for version control. Whether you like it or not there will be multiple versions of a spreadsheet and it changes regularly
  • Its useful to keep a backup of where you’ve been, but be specific about what the name of the current version is, include a date or time if that’s relevant
  • Make the inputs section as similar to any paper base inputs as possible. So if you normally used to capture information in a paper form, make it similar so that people don’t get confused. Don’t make radical changes to a process
  • If the formula is complex, split it over a number of rows or columns
  • And use the Protection feature in Excel to reduce the risks of accidental deletion of cells

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

  • you know you’re going to always need inputs in a spreadsheet,
  • you are going to have to do a few calculations and
  • you need a few outputs, Once you get to that stage work backwards, What are you trying to achieve. What are the outputs
  • You just list the reports you want to do,
  • then think through the reports you want to develop,
  • and work out what calculations are actually required to develop those reports and
  • list the calculations, and then depending what calculations you need,
  • you might need some other information in inputs and you can quickly list those inputs

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

  • what happens if it’s true and
  • what happens if its false.

In this case if it’s false there’s another IF function sitting there,

  • write the condition, what happens if its true,
  • what happens if its false.

Once you’ve got the drawing setup its a lot easier to actually code the formula and know where the brackets start and stop