Spreadsheet controls 222
_at_Denis: I didn’t want to mention to implement a control over a control. But it is subject of testing as I would consider this committee as a entity level control.
As always the answer is 'it depends… ’
You’ll name it men…
Regarding Controls over Minutes and Policy Setting.
Minutes and Policies used as input in key controls/processes should be authorised. That is as far as I would go. Off course there are still risks concerning: Were the persons autorised to authorise that policy, where is that documented, who signed that document. Are there any measurements to prevent fraude with that document (copy protection), how is version management on policies/memo’s implemented, who autoried that etc etc.
Please keep in mind that SOx is looking at key controls.
My thought is that if an organization has too many spreadsheets, an effort should be made to do the following:
- Consolidate the data.
- Populate the data into a database
- Create a web application portal to access the data.
If a group is preparing a whole consolidation with something like excel, one could ask if that is the appropiate tool for such a task…
TooTall last edited by
I would think the issue with spreadsheets are twofold. Who are the ‘author/developers’ of the spreadsheets, and who are the ‘users’ of the spreadsheets. One would have to use the same kind of controls they use for other software development (file management, security, versioning, documentation, etc) for the ‘author/developer’ types.
A potentially more interesting aspect is more on the User side. If there was a way that these spreadsheets could be converted into a Java or .Net Enterprise application, then the compliance issues of access, security, auditing, etc all go away because now these are applications running in an enterprise environment (thin client/server-based).
We have classified our spreadsheets based on complexity and risk (low risk, moderate, high) and have indicated if they pertain to either a key control or not. Spreadsheets associated with high risk and all that involve a key control will be tested for design and operating effectiveness. Spreadsheets are equivalent to any other system so we’re ensuring we have adequate controls around those ‘key’ or high risk spreadsheets (i.e. password protection, version control etc.). The classification of spreadsheets (ie. developing an inventory of them) was a time consuming task but we felt it had to be done. We ignored any spreadsheets employees use for individual or sub-analyses.
We are just at the stage of finding all the spreadsheets that people may be using that are not documented, but the end product is going into our financial systems. I was wondering if you have any tips on how to move through this process. Did you start with the major apps and work your way out to the business groups or the other way around.
Do you have any good check lists or questions you used when interviewing people to find out what they were using? What about tools or other resources that you might have used. If you have anything that might help me in the search, I would greatly appreciate the direction.
angie last edited by
In our case, European locations only, we did ask each of our Finance people to provide a copy of each of the spreadsheet they were using to input data into our financial systems.
All these were then agregated, tested for accuracy of formulas and saved.
Nothing has been mentionned by the Big 4 auditors during their testing…
Might be too easy or not very compliant with SOXA requeriment, but that is indeed what we just did.
TooTall last edited by
The Use of Spreadsheets: Considerations for Section 404 of the Sarbanes-Oxley Act
Many companies rely on spreadsheets as a key tool in their financial reporting and operational processes. As a result, the use of spreadsheets is an integral part of the information and decision-making framework for these companies. In developing and using spreadsheets, companies need to balance their ease and flexibility against the importance of reliable information for management’s use. The requirements under Section 404 of the Sarbanes-Oxley Act increase the focus on controls related to the development and maintenance of spreadsheets. As users of spreadsheet applications such as Microsoft Excel or Lotus 1-2-3 have become more sophisticated, so have spreadsheets. Once used to support simple functions such as logging, tracking and totaling information, spreadsheets with enhanced formulas and built-in advanced features are now used to support such business functions as complex valuation models. The use of macros and multiple spreadsheets which are linked together allows users to build very complicatedand sometimes convolutedmodels and other business functions with minimal or no documentation. In addition, these complex spreadsheets are not normally supported by the same control environments as formally developed or purchased applications. For example, the developers and users of spreadsheets are usually not trained in structured programming, testing, version control or systems development life cycles, and spreadsheets are rarely restricted from unauthorized access by security controls.
The use of spreadsheetsand, more importantly, the lack of controls over spreadsheetshas been a contributing factor in financial reporting errors at a number of companies. These examples highlight the importance of understanding how spreadsheets are used in a company’s financial reporting process and evaluating the controls over spreadsheets as part of the company’s overall Section 404 process.
How Are Companies Using Spreadsheets?
To assess how companies are using spreadsheets, it is helpful to categorize both the uses and complexity of spreadsheets. The uses of information contained in spreadsheets can be grouped into the following categories:
· Operational: Spreadsheets used to facilitate tracking and monitoring of workflow to support operational processes, such as a listing of open claims, unpaid invoices and other information that previously would have been retained in manual, paper file folders. These may be used to monitor and control that financial transactions are captured accurately and completely.
· Analytical/Management Information: Spreadsheets used to support analytical review and management decision-making. These may be used to evaluate the reasonableness of financial amounts.
· Financial: Spreadsheets used to directly determine financial statement transaction amounts or balances that are populated into the general ledger and/or financial statements.
The complexity of spreadsheets may be categorized in the following manner:
Low: Spreadsheets which serve as an electronic logging and information tracking system.
Moderate: Spreadsheets which perform simple calculations such as using formulas to total certain fields or calculate new values by multiplying two cells. These spreadsheets can be used as methods to translate or reformat information, often for analytical review and analysis, for recording journal entries or for making a financial statement disclosure.
High: Spreadsheets which support complex calculations, valuations and modeling tools. These spreadsheets are typically characterized by the use of macros and multiple supporting spreadsheets where cells, values and individual spreadsheets are linked. These spreadsheets might be considered applications (i.e., software programs) in their own right. They often are used to determine transaction amounts or as the basis for journal entries into the general ledger or financial statement disclosures.
Practical Steps for Evaluating Spreadsheet Controls
Implementing a process to ensure appropriate controls over spreadsheets is a critical element of compliance with Sarbanes-Oxley Section 404. There are five high-level steps to implementing such a process:
- Inventory spreadsheets
- Evaluate the use and complexity of spreadsheets
- Determine the necessary level of controls for key spreadsheets
- Evaluate existing as is controls for each spreadsheet
- Develop action plans for remediating control deficiencies
An action plan should be developed for each control gap identified. These action plans should increase the controls over the spreadsheet to the necessary controls based upon the use and complexity of the spreadsheet. Key elements of an action plan include:
- Assigning responsibility for actions in plan
- Establishing required remediation dates
- Prioritizing remediation efforts
For complex spreadsheets that support significant accounts and disclosures, consider whether these systems should be migrated to production processing environments to provide an adequate level of control. Given the potentially large number of remediation items relating to spreadsheet controls, it is recommended that these efforts start with high priority items, defined as items related to financial spreadsheets containing complex calculations which support significant accounts and disclosures.
Many companies rely on spreadsheets as a key component in their financial reporting and operational processes. However, it is clear that the flexibility of spreadsheets has sometimes come at a cost. It is important that management identify where control breakdowns could lead to potential material misstatements and that controls for significant spreadsheets be documented, evaluated and tested. Perhaps more importantly, management should evaluate whether it is possible to implement adequate controls over significant spreadsheets to sufficiently mitigate this risk, or if spreadsheets related to significant accounts or with higher complexity should be migrated to an application system with a more formalized information technology control environment. Understanding how spreadsheets are used and the adequacy of related controls is a critical part of management’s assessment of the effectiveness of its internal control over financial reporting under Section 404.
Note: Knowledge Dynamics provides a software product that can automatically convert Excel Spreadsheets into Java or .Net Enterprise Applications for migration/control needs. For more information call me on 614-286-8229.
Quite a few of us involved at this time in spreadsheet control evaluations it appears. Hopefully, you’ll see where I’m going with my scenerio and question below:
I’ve read through the PWC documentation on the use of spreadsheets and considerations for 404. The understanding I have is we should be concentrating on those spreadsheets that are used in our financial reporting process. However, we have spreadsheets that management uses to make decisions, such as budgeting and forecasting related, and business decisions could be negatively impacted by the reliance on an erroneous spreadsheet used in this process. Ultimately, however, a bad decision translates into an accounting entry that ends up reported correctly given our controls over our accounting processes. Would it be your opinion that this budgeting and forecasting spreadsheet should be identified and controlled within the 404 guidelines, even though our financial reporting process was not impacted?
However, we have spreadsheets that management uses to make decisions, such as budgeting and forecasting related, and business decisions could be negatively impacted by the reliance on an erroneous spreadsheet used in this process.
From a SOX point of view we do not care if management makes bad business decisions. :oops:
Ultimately, however, a bad decision translates into an accounting entry that ends up reported correctly given our controls over our accounting processes. Would it be your opinion that this budgeting and forecasting spreadsheet should be identified and controlled within the 404 guidelines, even though our financial reporting process was not impacted?
You’ve answered your own question. A bad decision may result in an accounting entry but if that entry accurately reflects the actual transaction then there is no impact.
That said there is nothing to stop management looking at things that are important but do not have a financial statements impact. However, the trick here is to maintain visibility on why you are looking at something - if you are not doing it for SOX you will want to seperate out things like testing, gaps, etc, and you will not need to have your suditor look at the non-SOX bits.
lekatis last edited by
What would Economics be without assumptions?
Yes, good old economics assumptions. Usually the first one is ‘assuming perfect competition’ :roll:
‘If you put two economists in a room, you get two opinions, unless one of them is Keynes, in which case you get three.’ - Winston Churchill.
ObieOne last edited by
Thanks for all this great information.
I am just starting to creat a document for my company outlining guiding principles for end user computing controls around the use of spreadsheets in particular. Does any one have an outline for something like this?
Any help would be appreciated. Thanks kindly.
beej last edited by
Maybe this is wishful thinking, but one topic that has not been touched on is whether or not setting up a control to check end user computing should even be implemented. Section 404 just states that management reports on whether they have an adaquate control structure in place, and that the controls are working effectively. For any material amount/journal that is arrived at by a spreadsheet, I would asssume every company already has a control for some level of review of that supporting documentation. So, if a spreadsheet has an error in it, those errors should be caught anyway by this level of review. An additional control to check all spreadsheets that resulted in a material impact to the financials appears redundant to me. I understand the concern that there are so many errors already in so many spreadsheets, but in every case where this resulted in a material error, the supervisor just failed to do an adaquate review.
I wonder if the big accounting firms are just making up controls for them to test for more fees. 8O
Syed last edited by
I’m in the process of testing key spreadsheets. During my initial investigation there are number of key spreadsheets relating to operational and analytical which help management make informed decisions but have no impact to the financial accounts. They are key but do they fall under SOX Spreadsheet testing given that they have no impact to the accounts.
streetfox last edited by
To mention it again, IT has to focus on key controls. It is not the decision of IT which spreadsheets are SOX relevant or not. Neither IT has to do something like a spreadsheet inventory and then assess which are in scope or not. This is a business side decision. If in a bigger company of course.
Or do you expect that IT which always liked chaos theory is able to not do it in that case :twisted:
The business knows exactly which spreadsheets are used to reconcile data from bigger systems, or just reconciling the data from their closing seasons process.
This is just a waste of time and manpower.
After you have identified the spreadsheets you can assess them like the PwC document shows. But be aware if taking safeguarding of assets into account. There might be formulae in this sheets which IT just CAN’T test. There is much business knowledge into them. What should IT do? Higher an actuary? This should be adressed by an peer review in the team which created the spreadsheet. (You might have a problem with the segregation of developer and tester - but there is one rule: Knowledge over independency, if your management takes that risk in their decision - off you go)
Then you just have to ensure that the accessrights to the location of the spreadsheet is properly set (reliance on ITGCs). And maybe its a good idea to create something like ‘light’ ITGCs for End-User-Applications.
Versioning, ChangeMgmt, AccessControl
SOXBriefs last edited by
You may need to reevaluate whether or not these spreadsheets are part of your key processes. If a process is merely in place to help manangement make good operational and financial decisions and does not affect the financial statements, by definition the process itself is not key and therefore, the spreadsheet does not need to be reviewed.
Key controls should only be controls that have an effect on the financial statements as reported to investors. Now that the PCAOB released new guidance in May, you should have good ammunition for removing some of these controls from your list when you discuss this with your auditors.
Also, I would be careful in designing a spreadsheet testing plan. The PwC guidance provides a list of all controls that could be in place for spreadsheets, however trying to implement every one of those controls may actually keep your employees from doing their jobs. You should be able to argue that thru the use of entity-level controls such as good staffing and review practices you should be able to reduce the number of controls necessary over your spreadsheets.
Syed last edited by
Thanks for your response. No, you’re absolutely right. If I looked to implementing all the controls recommended by PWC I think I won’t have many friends in the company.
I’m taking the reasonable assurance view that as long as there is a review taking place by both the user and senior management, there is adequate access control, passwords in some places, appropriate naming convention is being used to reflect current version and any changes are being logged and approved than I’m happy to go with that. These controls in my mind are good busines practice. Furthermore, as you rightly pointed out with adequate ELCs in place such as good staffing we can safely avoid the situation of becoming an over controlled, cumbersome, inefficient working environment.
Its worrying to think that some auditors are taking a very conservative approach which can lead to a very counter productive environment for businesses to operate.
I’m finding that there is a severe lack of consistency in how spreadsheets should be managed in line with SOX. I’ve been told to look those regardless of whether they have an impact or not to the financial statements but because they are part of a key control on an operational level, therefore one should look it as part of this spreadsheet testing.