Measuring the Impact of Data Integrity Issues

Data is considered one of the most important assets for any organization in this digital era. It serves as a foundation for decision-making, business strategy, and reporting, as well as long-term planning. However, given the increasing amount of data being generated, the risk of data integrity issues has also increased significantly. Thus, it is crucial that organizations measure the impact of data integrity issues and ensure that proactive measures are in place to prevent these issues from occurring. Imagine that a company needs information about its inventory purchases to ensure that its production is supported, but finds out that the inventory data has mistakes and errors, making its forecasts inaccurate. This is the reason why there is a need for metrics measured by data quality scorecards.

In this chapter, we will cover the following:

• Why measure the impact of data integrity issues?
• Reviewing the relevant data quality...

Technical requirements

Before we dive into our discussion, we must have the following in place:

• Microsoft Excel 2016 or later (in case you do not have this installed on your machine, you may access Microsoft 365 for free on the web through this link: https://www.microsoft.com/en-au/microsoft-365/free-office-online-for-the-web)
• A web browser to access Google Sheets
• A Google account to access Sheets (free version)

The sample data quality scorecard in Microsoft Excel is saved in the Packt GitHub repository and can be accessed at this link: https://github.com/PacktPublishing/Managing-Data-Integrity-for-Finance/tree/main/ch03.

Note

The scorecard for Google Sheets can be accessed here: https://docs.google.com/spreadsheets/d/1wD759gINukpu9VJOJWECdG0PwcrWX_C-2DfsASZBxAc/edit?usp=drive_link.

Once these are ready, you can proceed to the following steps.

Why measure the impact of data integrity issues?

The impact of data integrity issues can have direct cost implications, indirect costs, and even lost opportunities for the business. Data integrity issues, especially in transaction data, may lead to direct costs to the business such as the cost of correcting errors, reworking processes, and potential regulatory penalties. The related costs given their nature could be quite significant.

Costs can also be indirect, such as a decrease in productivity, lower customer satisfaction, and harm to a company’s reputation. These can be quite difficult to estimate; however, they can still have a substantial impact on the bottom line. Problems with data integrity may also lead to lost opportunities, such as declines in sales or missed investment opportunities. Though these are quite challenging to quantify, they can still have a notable impact on the business’s financial performance.

Now that we have covered the different types...

Reviewing the relevant data quality metrics for financial data and transactions

In the previous section, we discussed the importance of understanding the impact of data integrity. In this section, we will cover the different data quality metrics that we will use later in our scorecards.

While data quality and data integrity are closely related concepts in the field of data management, they each focus on different aspects of how data is maintained and utilized within an organization.

Note

To help explain the difference between data integrity and data quality, imagine a shipping company that needs to deliver packages to a particular address. To get the package where it needs to be, the company has to ensure that the parcel is sealed properly and remains intact during transit. Not only that, it also has to guarantee that it is delivered in a timely manner to the correct address and that it fits the customer’s expectations. Connecting this with the concepts of data integrity...

Data profiling using a data quality framework

A crucial step in determining the quality of your data is data profiling. This entails examining your data to comprehend its composition and linkages. We will be discussing the data profiling features of business intelligence tools in the next two chapters. In this section, we will be using a data quality framework to accomplish data profiling by performing the general steps seen in Figure 3.2:

Figure 3.2 – General steps for data profiling

Let’s go through this, step by step.

Define the criteria for data quality

Determine the relevant data quality metrics that are important to the business. These are the indicators of accuracy, completeness, consistency, timeliness, and validity that we covered earlier in the chapter. To which metrics we will give more importance will be context-specific and depend on what the company aims to achieve.

Continuing our scenario at the start of this chapter...

Preparing a sample data quality scorecard in Microsoft Excel

In the previous section, we discussed using data quality frameworks to get a better understanding of the quality of the data we are working with. In this section, we will discuss how to decide which metrics to use and create a sample template in Excel, then apply it to a fictional scenario for a company encountering issues with its data.

Establish the data quality metrics to be used

To prepare a data quality scorecard in Excel, we must first establish the data quality metrics that we will use. We covered them earlier in the chapter and we will create a sample scorecard that includes KPIs for completeness, accuracy, consistency, timeliness, and validity.

Define the scale for scoring KPIs

There are two options we can use when scoring the KPIs. It can either be qualitative or quantitative.

Scoring the KPIs qualitatively involves using the high, medium, and low criteria as follows:

• High: The data receives...

Preparing a sample data quality scorecard in Google Sheets

Continuing with our example of the Pizza Factory, management noticed that it was receiving a lot of follow-up inquiries from its suppliers as to when the invoices they sent would be paid. Upon discussion with the finance team and looking at the accounts payable account, it noticed that some of the transactions had missing data, such as payment terms and due dates, and also noted that some invoices were duplicated in the system.

Management was worried that the late payments would affect its relationship with its suppliers and increase the risk of duplicate payments. In addition to this, management wanted to take advantage of potential discounts if it paid the invoices before their due dates. Apparently, the person who had resigned after handling the recording of the inventory had also been the one responsible for recording the accounts payable, which affected the quality and integrity of the data in the system. Given the...

Microsoft Excel and Google Sheets functionalities to improve data quality and integrity

In the previous sections, we covered how to create sample data quality scorecards in both Microsoft Excel and Google Sheets. In this section, we will discuss the built-in capabilities these tools have to enhance the quality and integrity of data. We will begin our discussion with version control, which is a way to monitor changes made to files. Next, we will talk about collaboration tools to enable multiple users to make changes to the same file and give read-write permissions. After that, we will cover the data validation functionality in Google Sheets, as we discussed this for Microsoft Excel in the previous chapter. Lastly, we will touch on conditional formatting, which is a very useful tool to help in reviewing movements and trends.

Version control

Users can make changes to a file and keep track of changes over time in both Microsoft Excel and Google Sheets. This gives a history of file...

Summary

In this chapter, we discussed why we need to measure the impact of data integrity issues and learned that these can have direct costs, indirect costs, and even lead to lost opportunities for the business. We then reviewed the relevant data quality metrics for financial data and transactions. We covered the KPIs of completeness, accuracy, consistency, timeliness, and validity, which serve as the criteria for measuring data quality. We then discussed data profiling using a data quality framework, which is a crucial step in determining the quality of data. After that, we prepared sample data quality scorecards using Microsoft Excel and Google Sheets, and discussed functionalities to improve data quality and integrity. Both these tools offer a range of features to address data quality and data integrity.

In the next chapter, we will cover the common data quality management capabilities of business intelligence tools, as well as learn how these tools can be used to manage data...

Further reading

For additional information on the topics covered in this chapter, you may find the following resources helpful:

• Redman, Thomas C. Bad Data Costs the US $3 Trillion per Year:

  https://hbr.org/2016/09/bad-data-costs-the-u-s-3-trillion-per-year

• Nagle, Tadhg, Redman, Thomas C, and Sammon, David. Only 3% of Companies’ Data Meets Basic Quality Standards:

  https://hbr.org/2017/09/only-3-of-companies-data-meets-basic-quality-standards

• Segner, Michael. 5 Helpful Data Quality Frameworks You Need to Know:

  https://www.montecarlodata.com/blog-data-quality-framework/

• Switch from Excel to Sheets > Collaborate in Sheets:

  https://support.google.com/docs/answer/9331169?hl=en

• Highlight Patterns and Trends with Conditional Formatting:

  https://support.microsoft.com/en-us/office/highlight-patterns-and-trends-with-conditional-formatting-eea152f5-2a7d-4c1a-a2da-c5f893adb621