White Paper: Automating Spreadsheet Controls for Solvency II Model Compliance

Abstract
Spreadsheets, Access databases and other user-developed applications (UDAs) are front and center to Solvency II model development, providing flexibility and ample opportunities to optimize capital requirements. Absent the proper governance framework, these UDAs can be subject to a variety of unacceptable risks, including calculation errors due to faulty programming logic, non-compliance with the intent of the directive, and even fraudulent activity. This white paper examines the newly published governance mandates for Solvency II models, and offers a proven technology solution and best practices to help insurers and reinsurers in the European Union improve compliance while mitigating risk and driving significant process improvement.

Target Audience
CFOs, controllers, CIOs, COOs, CEOs, Chief Actuaries, VP IT Security & Risk, Certified Fraud Examiners, auditors, risk and compliance executives, spreadsheet developers, Solvency II project teams.

>>Download White Paper

PwC Promotes Spreadsheet Integrity Review

PricewaterhouseCoopers recently published an overview of their service offering for a Spreadsheet Integrity Review to help organizations assess spreadsheet risk and “…develop a long-term strategy to effect timely, accurate, and flexible reporting.” The overview asserts that spreadsheets are integral to an organization’s information and decision-making framework, yet they are often developed and supported in uncontrolled environments. This lack of control can lead to a variety of errors, including input errors, logic errors, interface errors, and others including incorrect cell references and broken or incorrect links.

The promotion of this service offering by PwC suggests the firm is continuing their focus in helping organizations mitigate Spreadsheet and End-user Computing Risk. It is also consistent with recent guidance from the Institute of Internal Auditors (IIA) in GTAG-14, recommending all organizations consider formal programs to audit and control mission critical spreadsheets and EUCs.

You can read the full overview here on PwC’s web site.

Addressing Compliance Controls for Solvency II Models

In a recent article published in Life & Pension Risk Magazine entitled Solvency II: Compliance Control, our resident domain expert, Mike Hoye, addresses how insurers and reinsurers in the European Union can avoid the pitfalls of addressing governance mandates from the FSA regarding the development and use of Internal and Standard Models for Solvency II. This article presents a best practices approach to managing complex models for improving accuracy and integrity, reducing risk, and improving efficiency and compliance.

There are potentially huge advantages for insurers to opt for an internal model rather than rely on Solvency II’s standard formula, but the governance challenges inherent in this approach are significant. – Michael Hoye, Senior Director of Enterprise Risk Services, Prodiance Corporation

>>Read Full Story

Spreadsheet and UDA Control: 5 Do’s and Don’ts for Success in 2011

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!

Basel III & Spreadsheets – The Perfect Storm?

The Bank for International Settlements (BIS) in Basel, Switzerland today announced the final rules for Basel III, a new global regulatory framework for banks. Building on the foundation of Basel II and similar to Solvency II in terms of focus on ensuring capital adequacy, Basel III also creates the perfect storm in terms of spreadsheet and end-user computing (EUC) risk. That is, banks leveraging spreadsheets, Access databases and other EUCs for computing the new capital requirements, risk-weighted assets, and liquidity (among other complex computations) are likely not prepared to satisfy auditor and regulator governance requirements mandates unless they have a controlled environment in place. Such EUCs are prone to input and logic errors, honest mistakes, fraud and almost impossible to manage (absent the proper controls) given the autonomy of users who can make changes to them.

To this end, Prodiance has been working with a number of global financial institutions to help them assess what is needed for effective spreadsheet and EUC governance for Basel II/III and Solvency II and how to implement best practices and leverage technology to help mitigate the risk of material errors while improving compliance with these new directives. You can read the press release from the BIS here or download a PDF of the final Basel III Accord. For more information on Prodiance ERM products and services, please visit our web site and stay tuned for further details on how Prodiance ERM technology, best practices, domain expertise and professional services offerings aligns with Basel III mandates.

If you have any anecdotes or comments on the new Basel III Accord, I’d love to hear from you. Finally, if your organization does not yet have a policy on End-User Computing in place, I would be happy to send you our template. Just drop me an email or leave a comment!

Happy holidays and safe travels!

Solvency II: Spreadsheet Governance Will Play a Key Role

The Solvency II Impact
According to the third annual Deloitte Solvency II Survey 2010, the regulation will have a significant impact on the insurance industry in the EU. Here are some supporting stats from the firms surveyed:

  • 34% will need to restructure or reorganize to support Solvency II initiatives
  • 49% will increase usage of actuarial operations
  • 70% will increase staffing by 10 or more FTE’s to support the initiative
  • 11% are considering relocating their firm outside the EU to avoid Solvency II compliance altogether
  • 49% will seek approval for their own internal (capital requirements) model

Spreadsheets & Solvency II
After carefully analyzing the English version of the 685 page directive, it’s clear there is a big focus on the accuracy, integrity and overall governance aspects of the Solvency II models. In fact, there are a number of articles requiring governance and effective internal controls in this area. Here is a quick rundown on mandates impacting the use of spreadsheets, Access databases, and other user-developed applications (UDAs) for Solvency II model development:

  • Article 44 – Requires governance over Solvency II model design ,testing, validation , and documentation.
  • Article 48 – Requires firms to have an actuarial function to oversee the adequacy of their Solvency II model, data, and calculation.
  • Article 82 – Calls for firms to ensure a high level of data quality, accuracy and completeness for Solvency II models.
  • Article 83 – Requires firms to compare model results against experience and identify deviations.
  • Article 115 – Requires firms to document both minor and major changes to Solvency II models.
  • Article 116 – Calls for firms to have systems in place to ensure the Solvency II model “operates properly on a continuous basis.”
  • Article 124 – Requires firms to perform model validation activities on a regular cycle.
  • Article 125 – Calls for proper documentation of the design and details of the internal model.
  • Article 236 – Requires transparency and governance for subsidiaries.

Spreadsheet & UDA Control Leads to Sustainable Governance
Many EU firms are seeking approval to use their own internal model (vs. the standard model, e.g. Lloyds). Internal models provide an opportunity to tailor capital requirements given the proper internal controls and governance processes are in place. Many firms are using spreadsheets for solvency, financial and actuarial models. As such, regulators (including the FSA, CEIOPS, etc.) will be more likely to approve use of internal models if they are accurate and managed in a controlled environment. The Prodiance Enterprise Risk Manager (ERM) System provides a comprehensive solution for spreadsheet and UDA control for firms seeking Solvency II compliance.

Cohmad Fined $200k in Madoff Case for Failure to Keep Spreadsheet Records

Yet another case of spreadsheet fraud surfaced today in the Boston Globe. Cohmad Securities Corp. was fined $200,000 for failure to cooperate with Massachusetts state investigators inquiring about Cohmad’s role in the Madoff ponzi scheme. Cohmad was founded in 1985 by Maurice “Sonny” Cohn and Bernard Madoff. Apparently, Cohmad failed to maintain proper books and records of their trading operations, including a spreadsheet used to track client’s Madoff accounts. The state was tipped off when they found out that Cohmad had received $37.4 million in fees from Madoff’s firm between 2003 and 2007, which accounted for 90% of their revenues.

Key Takeaways

  • Uncontrolled spreadsheets can expose organizations to the risk of fraud, leading to non-compliance and/or fines.
  • By maintaining an up-to-date inventory of all critical spreadsheets, Access databases and end-user computing applications (EUCs, a.k.a. user-developed applications or UDAs) and applying the proper controls, an organization can easily be prepared for these type of routine investigations.
  • Technology such as the Prodiance ERM System can help automate inventory management, risk assessment, remediation and control.

Of course, all of this assumes the intentions of the executive staff are moral to begin with. Enough said on this note.

Read the Full Story

New White Paper Addresses Guidance from the IIA’s GTAG-14 on Auditing User-Developed Applications

Download the White PaperNew Guidance from the Institute of Internal Auditors
According to the newly released Global Technology Audit Guide (GTAG®) 14: Auditing User-developed Applications from The Institute of Internal Auditors: “User-developed applications (UDAs) typically consist of spreadsheets and databases created and used by end users to extract, sort, calculate, and compile organizational data to analyze trends, make business decisions, or summarize operational and financial data and reporting results. Almost every organization uses some form of UDAs because they can be more easily developed, are less costly to produce, and can typically be changed with relative ease versus programs and reports developed by IT personnel.”

The GTAG 14 is careful to point out that “once end users are given freedom to extract, manipulate, summarize, and analyze their UDA data without assistance from IT personnel, end users inherit risks.” These risks include errors in UDA logic (e.g. honest mistakes), non-compliance with regulatory mandates, and even fraud – all leading to a high likelihood of material errors. GTAG 14’s primary emphasis is to provide direction to internal auditors on how to scope an internal audit of UDAs and assist management with developing an effective UDA control framework. It also outlines other considerations that internal auditors should address when performing UDA audits, including functional requirements for best-of-breed tools, and best practices for controls over UDAs.

New Prodiance White Paper Addresses IIA GTAG-14 Guidance
Last week, Prodiance launched a new white paper that summarizes how the Prodiance Enterprise Risk Manager (ERM) system and associated professional service offerings enable organizations to fulfill the IIA’s guidelines for identifying, monitoring, and controlling mission critical User-Developed Applications (UDAs). The new, complimentary white paper is entitled Addressing Guidance from the IIA’s GTAG-14 for Auditing User-Developed Applications and can be downloaded from the Prodiance.com web site via the following link.

Download the White Paper

New Guidance from the IIA on Performing Spreadsheet Audits

The Institute of Internal Auditors (IIA) just published a new Global Technology Audit Guide (GTAG 14) entitled Auditing User-developed Applications which encourages internal auditors to consider performing audits for critical spreadsheets used in financial reporting. The guide was authored by spreadsheet domain experts, and provides an overview of the challenges and risks organizations face with uncontrolled spreadsheets, Access databases and other user-developed applications (UDAs) and provides a roadmap with considerations for performing an audit. There are also several sample templates for defining what a risky spreadsheet is, as well as capturing documentation, control procedures, and more.

An IIA membership and login is required to download the new GTAG 14 guide for free. Alternatively, nonmembers can purchase a copy for $25 per the IIA from their bookstore.

>> READ THE FULL STORY

Congratulations to DIRECTV for Receiving the 2010 OCEG GRC Achievement Award

The Prodiance team would like to congratulate DIRECTV on receiving the 2010 Open Compliance and Ethics Group (OCEG) GRC Achievement Award. Announced during Compliance Week’s Fifth Annual Conference in Washington, D.C., May 24-26, 2010, the OCEG GRC Achievement Award recognizes organizations for innovative approaches to governance, risk management and compliance (GRC) to achieve Principled Performance®. DIRECTV was selected by a panel of industry experts from OCEG for implementing a sustainable solution to manage spreadsheet risk and compliance across the company based on Prodiance technology. Please join us in congratulating DIRECTV on this important achievement. We’re honored to have the DIRECTV team as active partners in the Prodiance user community, and we thank them for their involvement in driving GRC innovation.

Watch the video and read the full story here.

Next Page »


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 22 other followers

Follow Prodiance on Twitter

Prodiance on Twitter


Follow

Get every new post delivered to your Inbox.