Excel spreadsheets in School budgeting - a cautionary tale (2001)

School governor Peter Wolstenholme provided AccountingWEB members with a cautionary tale of the dangers that can lurk unseen within budget documents constructed with Excel.

In a posting on AccountingWEB's Any Answers section Wolstenholme explained how his school faced a £30,000 shortfall because values in a budget spreadsheet had not been added up correctly.

With help from several of AccountingWEB's Excel gurus, he managed to identify the underlying problem was due to budget figures bein

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments

Software is the way to go

steve_gts | | Permalink

The problem with Excel is that it's too easy to make mistakes, then with complex spreadsheets it's nightmare to find them (assuming you even know they are there)

I agree with a previous poster, that software is the way to go, although the Sage product isn't called Winforcast any more, it's now Financial Forecasting
http://www.ledgerservices.co.uk/financial_forecasting.htm

AccountingWEB's picture

Excel - has it good and bad points

AccountingWEB | | Permalink

The initial point about receiving a printed and signed budget that contains an error must mean the responsibility lies with somewhere around the people that signed it.

On all the other comments: I work with Excel every day and quite often with Sage Winforecasting. The good thing about using an accounting package is that it enforces double entry and so numbers can't be missed out of a calculation.

However it does not have the flexibility that Excel has for charting, presentation and the usage of macros/VB.

There is no one single perfect solution, but if the user is competent and diligent then Excel should be adequate.

regards

Richard
www.rsrconsultants.com

AccountingWEB's picture

Avoiding £30,000 spreadsheet construction errors

AccountingWEB | | Permalink

The problem with cross-checks is that the cross check may also ignore text which looks like a value.
I always advise people never to construct a formula without knowing what the result should be!
How can you ensure this? Simple, always test every formula with simple 'dummy data' which will ease the problem of spotting an incorrect result.
Incidentally, in later versions of Excel '=A1+A2+A3+....etc' should indicate an error if a 'value' is actually text (or a Label in Excel terminology).

Sage Winforecast

Anonymous | | Permalink

As a Chartered Management Accountant I have in the last 25 years prepared Budgets,Projections and Cash Flows using at first,manual, and then Lotus and Excel spread sheets.

Since I became a Sage dealer I have used and sold Sage Winforecast.Although I have a vested interest in selling the product, I highly recommend this Sage software product to lessen the possssibility of this sort of mistake happening when Budgets are prepared.

I know from experience as a Management Accountant that panic that sets in when you get a phone call from some anaylist pointing out a mistake in the spread sheet forwarded by you in a Glossy bound presentation folder.

Kind Regards

Dennis Mc Court

AccountingWEB's picture

Spreadsheet Errors - The wrong tool for Budgeting

AccountingWEB | | Permalink

Just imagine how many more errors there are in budget spreadsheets all over the UK! KPMG's work in this area showed over 90% of speadsheet systems had an undetected error. It is no wonder there is great interest in commercially developed budgeting systems with a central database and web data entry and reporting.

AccountingWEB's picture

No more errors....

AccountingWEB | | Permalink

...just use Sage Winforecast for a few hundred pounds from http://www.accountingsystems.co.uk

AccountingWEB's picture

EXCEL FLAW OR EXCEL FEATURE

AccountingWEB | | Permalink

SOME WOULD SAY IT’S AN EXCEL FLAW, THEN AGAIN SOME (THE MORE IT KEEN) WOULD SAY IT’S EXCEL FEATURE!

YOU DECIDE!

Nuff Said!

gmitch100 | | Permalink

If these planks have a shortfall in their budget why not sack the pratt who caused the shortfall in the first place because he/she didn't understand a basic Excel function.

Surely the most sensible solution offered so far? Presumably Excel Idiot has alraedy resigned thus avoiding my radical, but nevertheless brilliant budget balancing solution?

Only complete bottoms will respond to this . The thread is closed.

Big Sean Canary

VBA Alternative

dwagstaffe | | Permalink

Just to add to the variety of work arounds, here is a VBA alternative:

Sub NumericText()

Dim Consts As Range
Dim Forms As Range

On Error Resume Next

Application.ScreenUpdating = False

Set Consts = Selection.SpecialCells(xlConstants, xlTextValues)
Set Forms = Selection.SpecialCells(xlFormulas, xlTextValues)

For Each cell In Consts
If IsNumeric(cell.Value) Then cell.Value = Val(cell.Value)
Next cell

For Each cell In Forms
If IsNumeric(cell.Value) Then cell.Value = Val(cell.Value)
Next cell

End Sub

This procedure changes the 'numeric text' cells in a selection to numbers. If you just want to highlight the offending cells by making their background colour red, replace the two instances of 'If IsNumeric(cell.Value) Then cell.Value = Val(cell.Value)' with 'If IsNumeric(cell.Value) Then cell.Interior.ColorIndex = 3'

David Wagstaffe
PAYExcel - The Office Payroll Solution
http://www.payexcel.co.uk

Had to comment...

neileg | | Permalink

As a public critic of accountants' obsession with Excel, I just had to come back on this one.

It's interesting to see the twin strand of these comments. One is from the ardent Excel fans, one from lay users.

To blame either the user, or the software is to completely miss the point. Those of us who use Excel every day to manipulate data are well aware of the flaws in the process, and the techniques you can use to trap these flaws.

To suggest, as some have, that Excel never gets the import process wrong, is at best naive. To believe that a spreadsheet provided by someone else will always give the right answer is equally naive.

Excel, and its rival spreadsheets, are personal productivity tools. Get out of this arena, and the problems are inevitable.

Importing text data into excel

AnonymousUser | | Permalink

I had a similar problem importing text data from a time recording system into excel. The figures remain text data even if you re-format the cells.

The solution is to enter the number 1 into a blank cell, then Edit Copy that cell and highlight the imported text cells, Paste Special and click multiply under the Operation option.

AccountingWEB's picture

Whose Spreadsheet?

AccountingWEB | | Permalink

Mmmmmm..difficult one. Who's got control? If I'm providing a model for someone else to use, I protect all the cells except the "entry" cells, and I add a password so that unauthorised changes can't be made. I then take responsibility. If my client asks me for the password so that he can amend the model, I ask him to release me from that responsibility in writing.
Basically, although not in any way suggesting a legal position, I'd like to say that if the user has access to the formulae and the formatting, then the writer of the model has no control over its end use, and therefore could not be held liable. If this workbook had (a) been formatted correctly, (b) had the entry cells ONLY unlocked, (c) been subject to password protection, the end user couldn't have changed the cell format - the unlocked cells would accept data, but the Format Cell option would be disabled. If the thing were delivered with this kind of fault, it might be easier to determine where the responsibility would rest. Deliver it wide open, and anyone could change it in theory...
So much of this is down to procedure - what we have here is a classic case of uncontrolled use of "programmable" technology. Inside an organisation it's OK - the worst that can happen is that someone gets a good kicking. Once it becomes inter-organisational, it has the potential to be very weak, and likely to cause a dispute. But this is my business - so I'm bound to say that, I suppose!

shurst's picture

Data validation

shurst | | Permalink

To go back to the technical issues of highlighting unexpected content in an Excel spreadsheet, I believe the data validation feature in more recent versions of Excel has already been mentioned. Just applying appropriate Data Validation criteria doesn't highlight existing data that contravenes the criteria. However if you select the area concerned and bring up the Auditing Toolbar, you should find a button that will circle data that doesn't comply with any data validation you have set up for the selected area.

Simon Hurst
The Knowledge Base
www.tkb.co.uk

AccountingWEB's picture

Set validation in Excel to prevent numbers being treated as text

AccountingWEB | | Permalink

If you go to Data, Validation, you can set allowed ranges of data for selected cells. If you only allow whole numbers or decimals, any text entered in your range will throw an error.

While I agree with the "blaming the hammer" argument, I think Excel should indicate if it is excluding any cells from a summation because they have text values as, obviously in this case, it's something users should be aware of.

AccountingWEB's picture

EXCEL Error warning Misleading

AccountingWEB | | Permalink

Your headline was totally mis-leading. It gave the impression that there was an inherent fault in EXCEL. That was NOT the case. If data is entered as TEXT it will not (and SHOULD NOT)be added-up in the total. What next, I suppose the school governor will expect a figure entered in alpha (eg One) to be added in the total! Don't blame EXCEL and send accountants into a panic, blame it on STUPIDITY of the user!

Do LEAs provide "Best Value"

Peter Wolstenholme | | Permalink

It has been interesting to see the ripples caused by my first posting to the site.

The question posed that I would still like an answer for is the one I have quoted below.

David Cornes , 07 March 2001 @ 12:35 AM
Whose Spreadsheet?
As chair of governors finance sub-committe of a local primary school ...
However the LEA also provide an annual Spreadsheet with which to calculate our annual delegated budget. This I have to accept as gospel as I have not got access to all the underlying regulations. If this spreadsheet is wrong who bears the responsibility?

The impression that I had gained was that because it is a printed out version of the spreadsheet that is signed by the Governors then it is their responsibility.

However the LEA provides the spreadsheet, and also the requirement to apply "Best Value" practices. I would like to know if the fault could be proved to lie with them where the responsibility lay.

AccountingWEB's picture

Change the ODBC options

AccountingWEB | | Permalink

When using the ODBC facility to bring data into Excel from an external database, there is an option to "Auto Format" the data (Data-Get External Data-Data Range Properties). It is a good idea to switch this option off so that all formats that you apply to the spreadsheet are preserved. This will avoid a repeat of this problem.

Coloured text - reply to Will Heard

AnonymousUser | | Permalink

Will Heard's solution, to "automatically" display text in a different colour, can be achieved by highlighting an appropriate range, possibly the whole worksheet, and using conditional formatting from the Format menu. A condition such as "not between
-1000000000000 and 1000000000000" should capture all the text.

AccountingWEB's picture

Don't be too quick to criticise users

AccountingWEB | | Permalink

As anyone who has tried to import data from an external accounting system into Excel will know, it frequently, inexplicably and inconsistently expresses numbers as text. The likely cause in this instance is that parts of the spreadsheet have been imported and parts input directly. (I would strongly advise a careful checking of any other spreadsheets tha may have been cut and pasted into this one).

The reality is that Excel, in common with many other packages, is not entirely reliable, or bug free - and, as it continues to evolve, probably never will be. Users should always be diligent about auditing their spreadsheets and recognise that the larger it grows, the older it is and the more people access it, the more likely it is to contain errors - human or otherwise.

AccountingWEB's picture

blame it on lack of training

AccountingWEB | | Permalink

I regularly use Excel, and have yet to come across errors ion reports for which I (or another user) am not directly responsible. Although I regularly import data from a variety of different applications I always make it a point to verify that numeric values are input where required. This is easily done by going to DATA and then selecting TEXT TO COLUMNS.

AccountingWEB's picture

Oh Dear, Oh Dear

AccountingWEB | | Permalink

Whilst most of us are aware that Excel is somewhat flimsy (Isn't Excel a brand of loo roll too?!), even I, an ardent Lotus user, fail to see the problem here.
The software did it's job, someone did not.
Any possibility that a business studies teacher designed the spreadsheet? (lol)
Good to know that tax payers money is being well accounted for.

AccountingWEB's picture

Learning the hard way

AccountingWEB | | Permalink

The thing about most "out of the box" applications is that the user knows just that little bit less than he needs to know at any point in time. If you're using a tool like Excel to calculate an answer that you're going to rely on, you must at least include a reasonableness check, if not a comparative, i.e. the good old across and down cast. The thing will only do as it's told!
If a numeric cell is formatted as text (Format Cell Number) with a numeric value already in it, then the Sum function will include it UNTIL the cell contents are changed. The use of + is not affected by this. That will add in a text value as long as it's a number expression. If on the other hand, you replace the "text" number with an alpha character, then a totalling cell that uses + to include this alpha value will return #VALUE!. I'd suggest it's a case of Microsoft trying to be helpful, but not keeping everyone happy all of the time. SUM and + are not related.
Formula example =IF(ISTEXT(A1)=TRUE,VALUE(A1),A1), will guarantee to return value for cell A1 whether it's numeric, text or label (i.e. preceded by a single quote).
Also - if you're extracting data from a database via a query or ODBC link, or a Microsoft Enterprise tool like Data Transfer Services, then the format of the destination cell in the spreadsheet will depend on the column type in the database. So if it's, say, a VARCHAR column, you'll get back a text string. If it's a SMALLINT you'll get a number. If it's DATETIME you might get the date number or the actual date.....
Motto - Excel's great for presentation of data. The minute you enter a formula, you're at risk. First question - what's the impact if I get this wrong? If you get a model from someone else....think it through!

AccountingWEB's picture

Work around solution

AccountingWEB | | Permalink

I do a dump from database to excel as part of my monthly accounts consolidation.

I import 2000 rows and one particular column of numbers always shows as as text. I use =a1+1-1 and copy down the formula to return the number value for each cell. My first attempt was =a1 but of course excel treated the new value as text too.

AccountingWEB's picture

Excel problem at School

AccountingWEB | | Permalink

It's the old adage; Garbage in, Garbage Out.

Since Excel is a numeric based piece of software, why would anyone enter numbers as text. It just isn't logical!

That's why I always check formulae on spreadsheets I get from other departments.

It's not Excel's fault. It's just dumb users.

John Stokdyk's picture

Allow us a bit of journalistic licence

John Stokdyk | | Permalink

David - we could argue semantics here, since the flaw was contained in an Excel spreadsheet - and there is no suggestion that the program is at fault, as the story confirms - I felt justified in using the phrase "Excel flaw". Headlines are meant to be short and snappy summaries of the story that catch readers' attention. It seemed to work in this case, since you made the effort to read the item.

2. Malcolm, I'm sure you've run across all the other MS Office Wizards - the things that insist you use a document designed by Microsoft rather than just give you a blank page to start from. My guess is that the formula Wiz probably steps you through creating a formula... but perhaps one of our human Excel wizards can tell how to activate it in a spreadsheet.

AccountingWEB's picture

Headline on Story is misleading

AccountingWEB | | Permalink

Your headline implies it was a problem with Excel.
It is not an Excel flaw that caused this problem, but a user lacking excel knowledge.

AccountingWEB's picture

Risks of software

AccountingWEB | | Permalink

This problem, namely 'system behaves differently to users expectations' is a common theme in software development.

Did Excel (in this case) deviate from the specified and documented behaviour ? it seems not, in which case the cause of the 'error' lies somewhere else.

In fact it lies in the lack of formal acceptance testing of the system, this problme isnt confined to Excel or Office, all software systems carry this risk.

If you skip formal design and/or testing then this is what can happen.

AccountingWEB's picture

Whose Spreadsheet?

AccountingWEB | | Permalink

As chair of governors finance sub-committe of a local primary school I prepare monthly budget reports using my own Excel spreadsheets. These are self-balancing as they have month and year-to-date Budget, Actual and Variance columns and check to the monthly data keyed in from the reports provided in .pdf form by e-mail from the LEA.
However the LEA also provide an annual Spreadsheet with which to calculate our annual delegated budget. This I have to accept as gospel as I have not got access to all the underlying regulations. If this spreadsheet is wrong who bears the responsibility?

Will Heard

AnonymousUser | | Permalink

I think Excel is a superb tool but then I only use about 1% of its capabilities and have not come across this problem before.

Is it not possible to command excel (automatically) to display entries it considers to be text in a designated colour on the screen (but print out in black and white) so at least you have the chance to spot the problem even if you are not so assiduous in cross checking? Would this help to get over the problem

If so please would someone tell me how. If not then could the excel people do this? Or am I such a novice at excel thet this question just displays my lack of understanding?

Fergus Stokes's picture

Accounting Web flaw blows 5 minute hole in days work!

Fergus Stokes | | Permalink

I read this piece to gain valuable information on a newly discovered software glitch, but only found a report on someones poor attention to detail. The content of the story is similar to "faulty pen writes innacurate figures on tax form" This one certainly deserves to be "..lost in the sands of timek .." (sic)

ATD in Excel

AnonymousUser | | Permalink

ATD - Attention to Detail!!!

Suggest checks/cross-checks are incorporated to remove the chance of user error & users are sent on Excel courses. This is not an Excel problem but a user problem!(I also viewed this article in the hope of gaining some valuable knowledge)

Excel Data Management

AnonymousUser | | Permalink

I work with enormous amounts of data coming from varying sources, and going into Excel. If you are ever unsure about the validity or constancy of the data, i.e. a mixture of text lines and value lines, there are a couple of easy actions you can take to avoid such errors.

The first action shows you where to find the text line. When you press the autosum icon to sum a line of figures, the formula will only sum values. It will stop when it hits a text line, therefore you would have to override the autosum and highlight the figures you require summed. E.g. you wish to sum A1 to A8 but A3 is a text value, if you press autosum it will only highlight A8 to A4.

The next is to use the '=Value()' formula. If you run this formula alongside the data it will automatically change all text value into true values. Then you can overwrite the original data with true values using the 'copy' and 'paste special' functions.

A tip I learned the hard way: Always check your data before continuing to manipulate it - it will save you hours later!

AccountingWEB's picture

Re Excel Errors

AccountingWEB | | Permalink

As the cowman's expression goes both Peter Wolstenholme and Paul Mitchel have got the wrong end of the stick. The Sum Formula calculates correctly in that it adds up numerics. A 10 in text form is just that - a bit of text. All spreadsheets, Lotus 123,Supercalc etc., do likewise. The real error, which thanks to Paul I am now aware of, is the use of =a1+a2+a3 which gives an incorrect numeric answer when a text "number is in the cell(s).There are worse problems in Excel than this - has anyone ever used the File Combining procedures which are utter rubbish and can introduce numbers/formulae to other formulae in spreadsheets.Thus destroying them.

hmmm

AnonymousUser | | Permalink

what's a "Forumla Wizard"?
Levo MLaclom Slowalw

AccountingWEB's picture

EXCEL "IS EASY" BUT GET TRAINED

AccountingWEB | | Permalink

The story goes to underline the dangers of recruiting or applying personnel on the basis that they "know Excel".
I thought I was an expert until recently when I attended a course and learned more about the many tools that are available within Excel. I'm confident that, had the school made use of the Auditing and Data Validation tools, they would have prevented the errors happening or found them out much earlier.