In September 2010, Prodiance held an annual user’s group conference in Orlando, Florida and we had an excellent turnout with representatives from several industries, including banking, insurance, capital markets, manufacturing, communications, oil and gas, and professional services. I thought a good way to share some of the key takeaways from the event was to summarize the best (and worst) practices for Spreadsheet and UDA control.
Top 5 Do’s for Successful UDA Management
1. Organize a UDA Steering Committee
To properly establish the tone at the top and send the message that controlling critical spreadsheets and user-developed applications (UDAs) is important to the business, you need to organize a steering committee. Members of the UDA Steering Committee should include an executive sponsor (e.g. CEO, CFO, CRO) and representatives from corporate governance, finance and accounting, tax, IT, internal audit, and any business lines using and developing the critical UDAs (e.g. in financial services LOBs typically include wealth management, asset management, investment banking, insurance, etc.).
2. Create a UDA Control Policy
I wrote about this in detail in a previous post and even offered to provide a sample template to anyone who requests it. Developing an effective UDA Control Policy is critical to the success of any project to help formalize the initiative, and to define expectations for users to follow when creating, updating, and working with UDAs that are considered mission critical. A good UDA Control Policy will define what a risky UDA is and list the key controls required. It will also list the minimum control requirements for users to follow for each level of risk. There are 12 key controls recommended by leading audit firms, but we have found that in practice most organizations implement 6 or 7 of these controls on average. The most common controls include back-up/archival, version control, change control, documentation, access control, segregation of duties, logic inspection. Advanced controls may include overall analytics, development lifecycle, security and data integrity (e.g. lock down), and input control.
3. Develop a UDA Operating Model
A UDA Operating Model is like a “controls cookbook” because it defines the required and optional controls to be implemented for mission critical UDAs, and provides guidance on how the controls will be satisfied/automated through the use of technology. It also includes details on how the chosen technology solution will be implemented, including standard configuration options (for software) and any best practice policies. If you are choosing a technology vendor for UDA control, make sure they can provide a UDA Operating Model template to use as a starting point.
4. Leverage Technology for Sustainable Controls
In order to manage complex spreadsheets, Access databases, and other UDAs, you will need a technology solution. It is impossible to control complex applications such as spreadsheets manually. Leveraging technology embeds controls into everyday business processes so that mitigating UDA risk becomes part of doing business as usual. Ironically, many organizations embark on UDA control projects and immediately start creating a (manual) inventory, relying on various user groups to provide a list of critical UDAs. The problem with this approach is that the inventory becomes quickly outdated as users create new UDAs on an ongoing basis. In fact, it may be outdated even while it is being created. Many aspects of UDA control can either be fully or partially automated, including discovery, inventory management, risk assessment, diagnostics, change and preventative controls, policy checks, exception management, and reporting. Automation allows end users to keep their day jobs, and provides visibility into the control environment for managers and auditors.
5. Remediate & Optimize!
Many organizations overlook the importance of making sure their critical UDAs are working properly, producing accurate results, and are free of any logic errors (a.k.a. logic inspection). There are a few keys to facilitating this process, including testing UDAs, documenting test results and remediating and/or optimizing UDAs. UDA testing can be automated to a large extent through the use of automated diagnostic tools such as Prodiance Spreadsheet IQ, alleviating manual hunting and pecking for errors and potential issues in UDA logic. Any results from the testing should be documented, and issues should be discussed with UDA owners along with any recommendations for remediation. Sometimes the results may indicate the UDA should be replaced with an IT controlled application (whether available off the shelf, custom or otherwise). In other cases, the UDA may require small corrections to formula logic or even complete redevelopment.
The Don’ts - 5 Surefire Ways to Fail
1. Don’t Boil the Ocean by Scoping 100% of UDAs
If you have 100,000 UDAs across multiple business units and geographies (as do many global firms), please don’t try to inventory and risk assess all of them. Many of these UDAs may be outdated and no longer used. The best approach to avoid boiling the ocean is to follow some best practices, including performing a search/scan for UDAs created or modified during the last financial close cycle. Any UDAs identified through this process are most likely mission critical to your business because they have a direct impact on financial reporting. Additional considerations include starting with one LOB (e.g. finance, tax, private investments, etc.), and de-duping spreadsheet versions created from the same template.
2. Don’t Overlook Training!
To sustain the work completed during remediation and optimization, you should also consider training users on spreadsheet and UDA development best practices. Many organizations overlook the importance of training because many spreadsheets and UDAs are developed outside the control of IT (i.e. software development lifecycle). However, there are some highly efficient, modular ways to develop spreadsheet models that provide built-in checks and balances where errors are much less likely to occur. Training on development best practices should be key component in any successful UDA control initiative.
3. Don’t Implement Everything at Once!
As mentioned above, there are 12 key controls recommended by leading audit firms. PwC paved the way here in defining the required controls back in 2004, and the same control requirements still apply. However, now that we have been through several global implementations and technology adoption is ramping up, we are smarter and more sensible. To this end, implementing all 12 controls in a single project can be overwhelming. We have learned that implementing UDA controls in a phased approach leads to success. For example, try focusing on 6-7 key controls for phase one, and considering additional or advanced controls for phase two. The most critical (must have) UDA controls include: access control, version control, change control, and logic inspection. Tackle these first as they are likely to satisfy auditor requirements.
4. Don’t Forget to Involve the Auditors and Regulators!
There is now an ever increasing list of regulatory mandates impacting the use of spreadsheets and UDAs, including the Dodd Frank Act, Solvency II, Basel II, SOX 404, NAIC Model Audit Rule, 21 CFR Part 11, and OMB Circular A123. Although none of these mandates specifically call out the need for spreadsheet and UDA control, we know from experience that any spreadsheets and UDAs having a direct impact on financial, actuarial, and regulatory processes are being scrutinized heavily by internal and external auditors and regulators including the SEC, OCC and FSA. So as part of your Spreadsheet and UDA Control initiative, make sure these parties are briefed and on your control policy and environment and bless it before you implement a solution. Getting these parties on board early in the process will result in less time spent on spreadsheet control issues during ongoing audits and investigations. There is huge ROI to gain in shortening annual audit cycles regarding UDAs.
5. Don’t Follow – Be the Leader in Your Market!
Scott Dillman, partner at PriceWaterhouseCoopers in New York, predicted that regulators will look to the top 1 or 2 companies within each industry to set an example for the rest of the market when it comes to implementing UDA controls. Based on his recommendation, taking a proactive approach to implementing Spreadsheet & UDA Controls appears to be the best route to success. Laggards are likely to be left behind the curve when it comes to regulatory inspections, or unprepared when a material error is uncovered. Don’t follow – Lead the pack!
I hope these ideas and best practices are helpful for your spreadsheet or UDA control initiative. I’d love to hear your comments and feedback!
Abstract
In a recent article published in Life & Pension Risk Magazine entitled 
The Prodiance team would like to congratulate DIRECTV on receiving the 
In 2006, a leading US energy provider performed an audit of spreadsheets and end-user computing applications and recognized the need to establish tighter IT controls. Many key spreadsheets used within finance and accounting operations were used in financial, regulatory and management reporting, and were considered in-scope for SOX 404 compliance. At the time, SOX testing for spreadsheets was a manual process evaluating access controls and security, documentation, change management and formula and link verification.
Prodiance ESM provided pervasive monitoring (24x7x365) of all changes to critical spreadsheets and automated change control through cell level audit trails and versioning. Prodiance Spreadsheet Compare was utilized by business analysts to compare changes between spreadsheet versions in a side-by-side fashion to help speed review and approval cycles. Prodiance Spreadsheet IQ provided automated spreadsheet diagnostics to help internal auditors accelerate spreadsheet error checking and the evaluation of links.
Eric Perry
