Member Since: 25th Jun 2009
2nd Jul 2018
Very timely Simon - there is a conference in London next Thursday 6 July on spreadsheet risks and there's a case study from an insurance company which covers their self-assessment experience. http://www.eusprig.org/annual-conference.htm The European Spreadsheet Risk Interest Group (EuSpRIG)
At the recent Edinburgh ISACA conference, Christopher Rentrop gave a session on using Cobit 5 to manage end-user-computing ("Shadow IT") where he stressed the bottom-up involvement of pilot tests, and the need to assure the quality of the self-assessment programme.
Accountants often have an advantage over other modellers in that they are automating data processing tasks and have reconciliations they can rely upon to spot errors. Such as, from last period to this. With no easy way to check, others are prone to expectation bias - if they get the answer they want they believe it, but it could be wrong.
As for other standards, there is a commercial certification body called Spreadsheet Safe (spreadsheetsafe.com) that offers a training course followed by an online multiple-choice quiz where the pass requirement is 70%. (Disclaimer: I am one of their accredited trainers and the content remarkably resembles my book "Spreadsheet Check and Control") . A number of public sector bodies who are really motivated to avoid embarrassing mistakes put new hires through this each year.
Basically, people don't know what they don't know, and often simple tools and shortcuts to check your own work are most effective because they are easy to adopt and embed the habit of self-reflection on your work.
16th Jul 2016
17th Jun 2016
That looks like time series delay correlation, Simon.
Probably is a routine in Mathlab. In Excel , maybe do a Data Table where the variable is the lag and you can see what lag produces the highest correlation?
12th Oct 2012
Why not all the same hymnsheet?
If it was a DfT projection error, would not all the bidders have been working from the same data?
What was different about Virgin that they realised something was wrong?
And why did Virgin take the legal route rather than simply pointing out a flaw that inflation was not taken into account which would have blown it open earlier?
2nd May 2012
why those in the profession refuse this concept
Because the perception of the immediate expense is larger than the perception of the possible risk. It needs IT resources to define and maintain these standard formats. As soon as one person wants something which is not in the standard set, they either have to wait for IT to deliver it, or do it themselves, as best as they know how, in the time they have, for the purposes for which they want it that day, which may be different another day.
13th Apr 2012
When it's unavoidab;e
"products designed to supplant or manage spreadsheets within finance" are undoubtedly very good and to be preferred to Excel hack work. But for those who have no canned alternative, they have to find other ways to manage the risk. Hence the need for the annual conference of the European Spreadsheet Risk Interest Group (EuSpRIG). They meet to learn new ways next July 5 & 6 in Manchester.
14th Oct 2011
If you're going to click in each cell to enter the text, you may as well do one more click on the Font colour button, change it to red once, and then it stays at red for any subsequent click. Just one more click per cell. Or Ctrl+Y if you prefer to use the keyboard.
If you change the Normal style, that changes the display of all cells.
If you define a new style, you still have to remember to apply it, so that's still a click,
14th Oct 2011
Common problems with Styles
Very often styles multiply when cells are copied & pasted from other workbooks or other instances of Excel. If you wait till you get the error message "too many cell formats", it's too late, you have over 4000 different cell formats. I've seen workbooks with over 60,000 styles, but not all were in use.
It may be useful to have a macro that checks for too many styles (eg over 100) and deletes the unused ones. This code
Dim sty As Style, wbTemp As Workbook
' First, remove all styles other than Excel's own.
' they may have arrived from pasting from other workbooks
For Each sty In ThisWorkbook.Styles
If Not sty.BuiltIn Then sty.Delete
'Second, revert the remaining styles to Excel's default for a new workbook
Set wbTemp = Workbooks.Add
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Another idea is to check whether the Numberformat of the Normal style is not General. I have seen workbook style corruption where it acquires a Date format.
if activeworkbook.styles("Normal").NumberFormat <> "General" then show some message and offer to reset it to General
13th Nov 2009
i before e except...
in words like "weird" and some names 8-)
Thanks for the reference, John.
Eusprig welcomes other contributions to our horror stories page.
For more, read the comments at Debra Dalgleish's Horror competition: