Search Not Just Numbers

Loading...

Wednesday, 10 October 2012

Richard Branson, First Group, The Department for Transport and your Excel Spreadsheet

A major news story in the UK in recent weeks has been the embarrassing U-turn of the government on their award of the West Coast rail franchise to First Group, following complaints from the incumbent, Richard Branson's Virgin.

Full story here

The problems appear to have been caused by unrealistic assumptions on rates of inflation and passenger growth.

This got me thinking about the use of assumptions and variables in Excel spreadsheets generally.

Obviously, any assumptions should be clearly visible and I would suggest (if space allows) above the forecast (or whatever the main content of the spreadsheet is).

I'm sure we all know this to be good practice but it doesn't mean we always do it!

A second, slightly more subtle, rule that I always (try to) follow helps avoid so many of these types of errors.

Make these assumptions an active part of the spreadsheet

What I mean by this is instead of entering the text, say, "Inflation is assumed to be at the rate of 2%", enter the name of the variable in one column and the value in another, e.g.









Then wherever inflation is used in the spreadsheet, always reference this cell (B3 in the example above). This way you always know that what you have stated in the assumptions is what your spreadsheet is using.

You can refer to this cell in two ways, either:

1. Directly, i.e. $B$3 - Note the dollar signs to ensure that if you copy your formula elsewhere in the spreadsheet, it still references the cell, or;
2. As a named range, i.e. name cell B3 Inflation and refer to the range Inflation in your formula.

The second approach makes the formula easier to understand, but the first is usually quicker.

Another significant benefit of this approach is when these rates need to change. I come across so many spreadsheets that calculate VAT (a UK sales tax - currently 20%) on the value in cell A2 as =A2*0.2. When the rate changes, all of the calculations need to be checked, where if the VAT rate had been entered in a cell as above, it would be as simple as changing the rate in that cell!

Simple tips, I know, but many a complex spreadsheet has been brought down by not following them - just ask the Department for Transport!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

2 comments:

  1. While mysterious to help people, The item testing intended for compounds like flare retardants with apparel in addition to rolex replica things in line with the very likely experience of mankind (age. f. little one apparel have to follow this strictest expectations intended for compounds). So the challenge connected with hublot replica sale with outfits has grown to be typically one among the liability possibility management with the marketplace while using the people definitely wanting merchandise to help offer not any possibility on their health and fitness. Normal in addition to environmental vogue in addition to materials lures in some sort of a lot small, although rapidly rising selection of rolex replica, typically with European European union in addition to Coast YOU. Connected with much more matter towards world wide vogue segment would be the difficulty connected with technician survival. The challenge seemed to be featured by means of demand communities like World wide Alternate in the us alone directed at chanel replica handbags in addition to Nike as well as others. From the later 1980s in addition to beginning 1990s anecdotal research begun promising by toil activists in the us alone in addition to European union about the source snowchains in addition to in another country plants connected with foremost YOU in addition to Eu multinationals. An essential rolex replica uk seemed to be this the planet's foremost machine connected with denim jeans Levi Strauss, although far more appreciably Nike, this the planet's major activities running shoe advertising and marketing corporation.

    ReplyDelete
  2. Choose the casual variety for day-to-day wear to sport the carefree attitude. replica rolex uk It looks great with t-shirts and jeans. They come with bright-coloured straps which give them a funky look. replica hublot Pick luxury digital watches if you are looking for one to wear to a party. They have gold or silver plated straps which give them an elegant and sophisticated look. hublot replica If you are a sportsperson who requires a watch for more than checking the time, pick a sports watch. rolex replica uk They come with several other features like heart rate monitor, chronograph, split timers and so on. replica hublot Features - These type of watches come with several other features like calendar, alarm setting, dual time, chronograph and more.

    ReplyDelete