In large organizations, many different people are responsible for updating, creating, and deleting data in spreadsheets. Each time a spreadsheet is changed, there is a risk of losing the integrity of the data because of the number of cell references and relationships between each data item in the spreadsheet (and among many different spreadsheets). It is very difficult to maintain proper audit controls, change management, transparency, and accuracy when spreadsheets are used as the foundation for corporate reports and analytics.
We aren’t concerned about the spreadsheets that are used for a one-off project or to do some quick calculations. The risk is when companies distribute a spreadsheet template to different departments or when each part of the organization manipulates the data that they get from their Oracle® E-Business Suite (EBS) or any other enterprise system because the data coming from the system is not quite the way they want to report their operations. Whenever data is extracted from a system and transformed or changed, there can be a loss of integrity. There are no standards and change control mechanisms to validate that the formulas are correct or that all of the relationships are maintained, and there are no standard procedures for data reconciliation. There is the same risk in writing scripts to migrate data into a new instance as is customary for a reimplementation. In this article, we break down the risk factors and suggest safer, alternative methods for achieving data consistency and control over sensitive information.
Data Governance is defined by DAMA International as:
The exercise of authority and control (planning, monitoring, and enforcement) over the management of data assets.
Good data governance is dependent on policies and procedures – primarily regarding who has access to the data and what type of access is allowed. Some of the collaboration platforms attempt to create an environment that elicits policy administration and procedure execution, but the policies and procedures must have already been decided on and put in place by either someone or a committee of organizers. Among the decisions that must be made are who is responsible for what data, who owns the data, who has access to the data (and what types of access those individuals have), and who does not have access to the data. Permissions-based access can be administered through collaboration platforms, but when a project involves hundreds of spreadsheets or hundreds of pieces of code that are spontaneously created, updated, and deleted, managing permissions can be a daunting task even with the right policies and procedures in place. Now that the potential for security and data integrity exist, accountability at the user level becomes crucial.
Stewardship is akin to accountability. A data steward is responsible for maintaining the accuracy of the data. Data stewards validate that data in a source is consistent with that in a target. The source can be an existing system, and the target can be another system or a report, or the source can be a module like Accounts Receivable where the target is the General Ledger. Data stewards are accountable for the creates, updates, and deletes of data, and they are also responsible for reconciliation. In most cases, data stewards already exist in an organization, whether or not they are formally recognized as such (sometimes they don’t even formally recognize it themselves). It is a good idea to make it formal so that it becomes enforceable as public knowledge. This can be accomplished in three steps:
- Create a system of record of the names of the people and the data that they steward.
- Write the steward responsibilities into their job descriptions.
- Evaluate the people on their executed ability to be responsible for the data.
Through user responsibilities and security in EBS, there is some accountability as well as some record of when the data was updated and by whom. Once the data is moved outside the “secure” environment, it is very difficult to maintain accurate records and almost impossible to validate the accuracy of the resulting data.
Assumptions and Inconsistencies
When the governance and stewardship are not clearly defined, many people assume the roles, and it is not clear where the responsibility lies. If each of five employees are under the same assumption that they are the one responsible and accountable for a particular spreadsheet, segment of code, or piece of data, then different data requirements can result in multiple changes to the same piece of information. The integrity of the information is compromised as an update by one employee is changed or deleted by a second employee under the assumption that the second is the steward of that data. Furthermore, the first employee may not even be aware of the changes that were made to his or her changes.
Segregation of accountability is necessary to eliminate the dependence on assumptions. The golden rule: There must be one, and only one, steward held accountable for each data element. As with any hierarchical organization, some overlap must occur (i.e., a manager will be the steward for all of the pieces of data for which his or her team members act as stewards), but the hierarchy and roll-up groups must be clearly defined else the organization’s data may contain myriad inconsistencies.
Getting Through an Audit
The more inconsistencies that you have, the more expensive your audit will be. Your audit firm will have to spend a lot of time reconciling source data. Additionally, if the data resides in multiple sources, the auditor will need to spend time gathering the information and analyzing data in different formats. The Management Discussion and Analysis (MD&A) section of the annual report will be much more extensive because of the need to explain differing methodologies and assumptions.
The structure of Oracle EBS can help companies standardize on accounting methods and can provide full drill down capabilities at the transaction level. However, if good accounting practices are not built into the structure, even using a standard ERP can make the audit an expensive nightmare. For example, as a company expands globally, they may set up entirely new production instances of EBS in each of their new locations – it is the fastest way to get operations up, running, and ready for business. When this happens, inconsistencies arise in the way the new instances are configured. Some legal entities that exist in the primary instance are left out, new business groups that are absent in the primary instance are added, and new charts of accounts are created to comply with local regulatory and statutory requirements. While the new instances were set up to run the same business, the information is not in the same format as in the first instance. If a legal entity is spread across multiple ledgers, then it is necessary to perform consolidations (either through spreadsheets or by using the GL Consolidation function). As soon as you extend outside of the posting directly from the subledgers into the GL you add time and money to your audit and lose the governance, controls, and stewardship of the data because there is uncontrolled data manipulation.
If you have multiple charts of accounts with values having different meanings, you lose consistency and accuracy. Even within the same chart of accounts, there are different types of information in each segment, the rollups are not clean, and the results are not transparent. Finally, if you have reimplemented and have to maintain a sunset instance, your auditor will have to translate between your production instance and the sunset instance to comply with Sarbanes-Oxley (SOX) and International Financial Reporting Standards (IFRS) requirements. When data comes from disparate sources, the reports required to present information in a particular form can take thousands of person-hours to create when the sources of information are so different.
Many disparities that arise are not the fault of any data steward but the fault of the disparate systems themselves. Data migrations by writing custom code or spreadsheets are often employed in an attempt to align the information systems and make them more compatible, but we have already seen how quickly the data integrity can be compromised.
Setting up the proper level of data governance and stewardship in the planning stages of an E-Business Suite implementation is the solution to this problem, but often companies implemented their EBS to mimic the legacy systems that they were replacing and did not implement the needed controls. There are new software products on the market that give organizations the ability to change setup configurations in ERP instances that have been in production for many years and enable them to achieve data governance that was lost during the initial implementation. Even with ideal governance structures in place from the start, growth strategies such as mergers, acquisitions, and divestitures can introduce disparate data to an organization. In these cases, the new parent company must take the initiative of absorbing the acquired entity’s people, culture, and systems, applying its own data governance and stewardship policies and procedures to the new company as a whole, and transforming the fused information architecture of the existing and absorbed systems to both align with the current business and enable the ability to grow and change in the future. New software coupled with trend identification will take care of the transformation and future preparation (no promises about the people and culture).
IT analyst firm Gartner maintains that companies need to be proactive in reacting to changes and recognizing the early indicators and patterns that can provide visibility into potential future opportunities and threats. These trends aren’t spotted from monitoring the traditional economic indicators, emerging markets, channels, or customer preferences but arise from an expanded environmental analysis that includes the entire landscape of people, processes, and information. The three components of Gartner’s Patterned Base Strategies are Seeking, Modeling, and Adapting. Predictive analytics techniques and business intelligence systems are instrumental during the Seeking phase, but they are only as effective as the underlying information. Disparate data resulting from poor data governance and stewardship as well as from poorly planned systems expansion produce disparate business intelligence. In order to prepare for and prosper from opportunities realized though Patterned Based Strategies, it is imperative that the data being used to model future scenarios is discrete, unambiguous, and full of integrity.
Organizations are constantly changing, and with them their policies and procedures. Information systems grow. Opportunities evolve. It is necessary for data governance and stewardship definitions to evolve with an organization, and such an evolution is made accessible in an organization that has data transparency, audit-ability, aligned business processes, and a focus on mitigating risk. Transformation software from eprentise is the only enabler of all of these characteristics. Take the initiative to learn more about eprentise software in order to get your organization back on track and to prepare it for adapting to inevitable change. Also, keep an eye out for your opportunity to try our upcoming Audit Automation software (expected release Q1 2021), by our sister company, Crystallize Analytics, which will help you stay in tune with your audit, with dozens of substantive procedures and full drill down visibility into your transaction data.