Bit short of time at the moment so I thought I would cheat a little by replaying some of my blogging on another discussion site at FSN.co.uk (Financial System News) where the editor Gary Simon retains a continuing interest in the issues of operational spreadsheet use.
For the full discussion on spreadsheet control processes click here.
Establishing 'adequate spreadsheet processes' is a mixture of culture, process and technology and I would suggest the following 9 steps:
1. Recognise that operational spreadsheets exist. There are many CFOs who simply don't realise how many spreadsheets actually populate their financial processes....I think they begin to believe/hope that the large IT budgets they keep signing off must have eliminated them.
2. Do not be lulled into a sense of confidence by the lack of recent material errors. The reality is that this lack of mistakes has a cost to the organisation in terms of many working/evening/weekend hours of checking/correcting by diligent employees.
3. The next step is to realise that there is no magic wand (=vendor software solution) that can eliminate all operational spreadsheets in the business so it becomes a question of which spreadsheets to live with, how you live with them, and for how long.
4. The next objective should then become the definition of what spreadsheets are important – this equals discovery/risk assessment in our jargon. While technology can help, this process requires engagement by the business and often external consultants – otherwise you just land up with a list of a million spreadsheets that is no use to anybody. At this point it often makes sense to improve the structure of important but poor quality spreadsheets in terms of inputs/outputs/functional areas/use of protection so as to minimise the likelihood of future errors (this has become known as 'remediation' in our world).
5. The next step is to determine what constitutes anomalous behaviour in a spreadsheet and report it succinctly to the right individuals. For those organisations bound by compliance requirements an audit trail of activity may be sufficient but without focused exception reporting there will be far too much data to review as part of ‘business-as-usual’.
6. In effect what one should be doing is to automate all the manual checks that employees would otherwise conduct. Not only does this save time, automatic checking can be done far more regularly/consistently than any manual check. Some checks require no input from the business (e.g. alerting the appearance of cell errors). Other checks require more attention to detail (e.g. that static data has not been changed and that dynamic data - such as foreign exchange values - have changed). All of these checks can be automated.
7. Use the new understanding of the content and data of the spreadsheet to eliminate other spreadsheets (e.g. those used for reconciliation) as these checks can also be automated.
8. Use the new transparency to highlight specific spreadsheets for replacement by new systems/upgrades i.e. align business need with IT road map.
9. Recognise that spreadsheet creation, operational use and eventual replacement is a continuing journey so establish continuing processes that recognise this ‘conveyor belt’ of innovation through to systemisation