Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

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

by
18th Jun 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

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 being entered as right-aligned text values rather than as numbers. The text figures were not included within the =Sum calculation and rippled through to the school's budget submission.

"Excel is consistent in as much as if the total formula had read "=A1+A2+A3", the correct total would have been returned," commented AccountingWEB member Paul Mitchell. " However, when you use =Sum it just ignores the entries it considers to be text." While useful in building spreadsheets where the user wants to add up a range of cells that include numbers and words, it does carry the risk of creating the error that troubled Wolstenholme. The Forumla Wizard in Excel explains more about the Sum formula, he added.

Blame for the error rests with whoever created the spreadsheet, said Mitchell, which would make it difficult for the school to make any claim for restitution from the vendor. Perhaps the authority should look into its IT training practices, he said, adding: "The spreadsheet will only do what it is programmed to do. I think it is too easy to blame the software for a mistake that was made by a person."

Wolstenholme acknowleded that he often gets text values turning up in Excel spreadsheets when he extracts data from his accounting system using the ODBC mechanism. "Excel it does this with account numbers and cheque numbers/transaction reference numbers."

Accepting Mitchell's verdict, the unfortunate school governor added, "The most obvious deficiency I could see was the lack of cross-checks, and we are looking forward to seeing whether this years version of the spreadsheets contain any."

Neil Eglintine, himself a local authority accountant, warned that spreadsheets often take on a life of their own - and one that is not recorded in any formal documentation.

"By the time the sheet has been modified, linked and cross linked several dozen times, the author of any of it (and sometimes the original purpose) is lost in the shifting sands of timek," said Eglintine.

After suggesting a couple of tips to help spot such rogue entries, Eglintine added, "People have often asked why I cross-check calculations in computer generated documents. Perhaps the audit habit dies hard, perhaps [it is due to] coming from the slide rule generation, where a reasonablness check is always required."

This article and the Any Answers query on which it is based were first posted in 2001.

Tags:

Replies (35)

Please login or register to join the discussion.

avatar
By steve_gts
07th May 2009 09:37

Software is the way to go
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

Thanks (0)
aw_logo_2019
By Accounting WEB
16th Sep 2001 18:34

Excel - has it good and bad points
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

Thanks (0)
aw_logo_2019
By Accounting WEB
09th Jul 2001 10:36

Avoiding £30,000 spreadsheet construction errors
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).

Thanks (0)
avatar
By User deleted
18th Jun 2001 17:42

Sage Winforecast
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

Thanks (0)
aw_logo_2019
By Accounting WEB
03rd Apr 2001 08:46

Spreadsheet Errors - The wrong tool for Budgeting
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.

Thanks (0)
aw_logo_2019
By Accounting WEB
02nd Apr 2001 10:11

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

Thanks (0)
aw_logo_2019
By Accounting WEB
07th Mar 2001 13:58

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

YOU DECIDE!

Thanks (0)
avatar
By gmitch100
09th Mar 2001 22:52

Nuff Said!
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

Thanks (0)
avatar
By dwagstaffe
10th Mar 2001 11:56

VBA Alternative
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

Thanks (0)
avatar
By neileg
09th Mar 2001 12:02

Had to comment...
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.

Thanks (0)
avatar
By AnonymousUser
07th Mar 2001 13:07

Importing text data into excel
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.

Thanks (0)
aw_logo_2019
By Accounting WEB
08th Mar 2001 23:09

Whose Spreadsheet?
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!

Thanks (0)
Simon Hurst
By Simon Hurst
09th Mar 2001 08:32

Data validation
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

Thanks (0)
aw_logo_2019
By Accounting WEB
07th Mar 2001 13:27

Set validation in Excel to prevent numbers being treated as text
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.

Thanks (0)
aw_logo_2019
By Accounting WEB
08th Mar 2001 18:26

EXCEL Error warning Misleading
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!

Thanks (0)
avatar
By Peter Wolstenholme
08th Mar 2001 18:50

Do LEAs provide "Best Value"
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.

Thanks (0)
aw_logo_2019
By Accounting WEB
08th Mar 2001 07:32

Change the ODBC options
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.

Thanks (0)
avatar
By AnonymousUser
08th Mar 2001 09:46

Coloured text - reply to Will Heard
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.

Thanks (0)
aw_logo_2019
By Accounting WEB
07th Mar 2001 14:13

Don't be too quick to criticise users
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.

Thanks (0)
aw_logo_2019
By Accounting WEB
07th Mar 2001 15:24

blame it on lack of training
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.

Thanks (0)
aw_logo_2019
By Accounting WEB
07th Mar 2001 22:31

Oh Dear, Oh Dear
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.

Thanks (0)
aw_logo_2019
By Accounting WEB
07th Mar 2001 23:29

Learning the hard way
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!

Thanks (0)
aw_logo_2019
By Accounting WEB
08th Mar 2001 01:19

Work around solution
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.

Thanks (1)
aw_logo_2019
By Accounting WEB
07th Mar 2001 11:20

Excel problem at School
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.


Thanks (0)
John Stokdyk, AccountingWEB head of insight
By John Stokdyk
06th Mar 2001 19:26

Allow us a bit of journalistic licence
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.

Thanks (0)
aw_logo_2019
By Accounting WEB
06th Mar 2001 20:04

Headline on Story is misleading
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.

Thanks (0)
aw_logo_2019
By Accounting WEB
07th Mar 2001 11:56

Risks of software
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.

Thanks (0)
aw_logo_2019
By Accounting WEB
07th Mar 2001 12:35

Whose Spreadsheet?
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?

Thanks (0)
avatar
By AnonymousUser
07th Mar 2001 09:28

Will Heard
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?

Thanks (0)
avatar
By Fergus Stokes
07th Mar 2001 10:02

Accounting Web flaw blows 5 minute hole in days work!
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)

Thanks (0)
avatar
By AnonymousUser
07th Mar 2001 10:15

ATD in Excel
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)

Thanks (0)
avatar
By AnonymousUser
07th Mar 2001 10:39

Excel Data Management
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!

Thanks (0)
aw_logo_2019
By Accounting WEB
06th Mar 2001 14:10

Re Excel Errors
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.

Thanks (0)
avatar
By AnonymousUser
06th Mar 2001 16:35

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

Thanks (0)
aw_logo_2019
By Accounting WEB
06th Mar 2001 13:16

EXCEL "IS EASY" BUT GET TRAINED
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.

Thanks (0)