3 Common Business Fraud Risks and How to Mitigate Them

3 Common Business Fraud Risks and How to Mitigate Them

 

LUTZ BUSINESS INSIGHTS

 

Business Fraud Risks

3 COMMON BUSINESS FRAUD RISKS AND HOW TO MITIGATE THEM

ROBERT KEENAN, CHIEF INFORMATION & RISK OFFICER

 

According to a recent survey, businesses lose about 5% of their revenue due to fraud. The average cost is $1.5 million per case. Too often, companies are unprepared or lack the appropriate internal monitoring controls to detect and prevent it. Therefore, it is vital for organizations to understand the different types of fraud and how to recognize suspicious activity.

 

What Is a Business Fraud?

Business fraud is also called corporate fraud. It refers to the act of deceiving an organization, business, or individual to gain a personal or financial advantage. Fraudulent activities can be hard to identify, especially for small businesses, as they lack the resources necessary to implement control measures. When fraud occurs, whether a small or large event, it damages the overall image of a company and therefore it is imperative to have controls in place. However, whether you are a small business or large corporation, there are some easy steps to take to mitigate fraudsters from taking advantage of the gaps in your company’s process and procedures.

 

What Are Some Types of Business Fraud Risks?

Business fraud presents itself in different forms, including:

1. Asset misappropriation/skimming

Asset misappropriation refers to the theft of company assets. It’s one of the most common types of business fraud, accounting for about 86% of reported cases. It includes issues such as missing inventory, forged checks, and missing accounts.

Skimming refers to taking money from an organization, customer, or company without recording the transaction. This form of business fraud mainly occurs in the accounting department or mailroom, where funds can be easily seized unsupervised. Because there is no paper trail, it is difficult to prove these transactions ever existed. For smaller companies, it can occur with cash transactions that are not properly handled.

2. Payroll fraud

About 27% of all businesses experience payroll fraud, and it’s 50% more common in small businesses than in larger ones. This form of fraud manifests in different ways. For instance, employees can lie about the hours they worked, productivity, and sales to get higher pay. In other cases, they may request an advance payment and not pay it back. Also, they may use co-worker(s) to manipulate attendance lists and clock them in and out, yet they are not at work.

3. Fraudulent invoicing/ billing

False invoicing is a popular form of fraud in which an employee creates fake suppliers or pays a legitimate supplier but channels the cash into a different account. On the other hand, billing fraud is when an employee submits fake personal inflated invoices for services or goods to the employer. For example, an employee creates and submits an invoice for payment of goods or services they never provided. For these reasons, business owners should always scrutinize invoices to limit fraudulent activity.

 

How Can Businesses Mitigate These Risks?

Business fraud can significantly affect your revenue, leading to losses and legal issues. Additionally, the reputational risk to a company that has been accused of or caught committing or allowing fraud to happen can be devastating.

Companies need to adopt different solutions to mitigate these risks, including:

1. Anti-fraud policy

Each company should create a comprehensive anti-fraud policy and implement it fully. Furthermore, they should train their employees on the matter, have them sign an acknowledgment form, and make known the consequences of violating this agreement.

2. Enhanced Communication

Businesses should strive to create clear lines of communication within their organization to prevent fraud. Companies who make fraud reporting an integral part of their culture typically see faster fraud reporting. You can even consider rewarding employees who raise red flags and report fraudulent activities.

3. Audits and Inspections

Another way of detecting fraud is to conduct regular and surprise audits and inspections. Such activities can help to detect fraudulent behavior and mitigate the risks. Businesses need to review their financial records regularly and update the technology used in the accounting department. Similarly, they should consider introducing internal controls to make it easier to detect and mitigate business fraud.

4. Digital management systems

Cultivating a culture of honesty is a great step in preventing fraud. Business owners need to learn each step on how their financial processes work and ensure there is accountability at each level. As your company grows, the complexity of your financial activities increases. A payroll management system can be an excellent remedy to mitigate fraud in such cases.

In addition, businesses can adopt document management systems to enhance compliance, data security and efficiency, and monitoring of document transactions and information exchange. These systems minimize manual work when capturing, organizing, and retrieving information. Along with this, businesses should create distinct data access rules to prevent misuse of information and limit document falsification.

Are you facing challenges of business fraud in your organization? Lutz can help your company become compliant to prevent and mitigate fraud. Contact us if you have any questions or learn more about our Risk Assessment services.

ABOUT THE AUTHOR

402.763.2973

rkeenan@lutz.us

LINKEDIN

ROBERT KEENAN + CHIEF INFORMATION & RISK OFFICER

Robert Keenan is the Chief Information & Risk Officer at Lutz with over 20 years of compliance and operational risk experience. He focuses on risk management, compliance, and security for the firm, and will partner with the operations team to drive process improvement and operational efficiencies for Lutz.

AREAS OF FOCUS
  • Risk Management & Compliance
  • Operations
AFFILIATIONS AND CREDENTIALS
  • Association of Certified Fraud Examiners
  • Society of Compliance and Ethics Professionals
  • National Society of Compliance Professionals
  • Certified Fraud Examiner
  • Certified Compliance and Ethics Professional
EDUCATIONAL BACKGROUND
  • BA in Finance, University of Oklahoma, Norman, OK
  • MPA, Drake University, Des Moines, IA
COMMUNITY SERVICE
  • Association of Certified Fraud Examiners - Heartland Chapter, Past Board Member

SIGN UP FOR OUR NEWSLETTERS!

We tap into the vast knowledge and experience within our organization to provide you with monthly content on topics and ideas that drive and challenge your company every day.

About UsOur Team | Events | Careers | Locations

Toll-Free: 866.577.0780Privacy Policy | All Content © Lutz & Company, PC 2021

3.16.22 | KPIs for Small to Mid-Sized Businesses | Recording

3.16.22 | KPIs for Small to Mid-Sized Businesses | Recording

 

LUTZ BUSINESS INSIGHTS

 

AM I READY TO SELL MY BUSINESS?

kpis for small to mid-sized businesses

3.16.22 | recording

Most organizations’ data is a collection of disparate systems and spreadsheets that make it difficult to get insight into the business. What if you could start to organize your data, arrange it, and then present it visually to gain insights, tell a story, and understand your business better? Now you can. In this presentation, Tony DeSantis of Lutz Tech will discuss how to unlock your data and create Key Performance Indicators (KPIs) for small to mid-sized businesses and take you through a deep dive of the Microsoft Power BI platform.

Key Takeaways:

  • How to Gather & Organize Your Data
  • Common KPIs Small to Mid-Sized Organizations are Measuring
  • How to Create & Utilize Dashboards in Power BI

Seminar Level: Intermediate

RECENT POSTS

SIGN UP FOR OUR NEWSLETTERS!

We tap into the vast knowledge and experience within our organization to provide you with monthly content on topics and ideas that drive and challenge your company every day.

About UsOur Team | Events | Careers | Locations

Toll-Free: 866.577.0780Privacy Policy | All Content © Lutz & Company, PC 2021

Sports Analytics + Lessons for the Business World

Sports Analytics + Lessons for the Business World

 

LUTZ BUSINESS INSIGHTS

 

SPORTS ANALYTICS

sports analytics + lessons for the business world

tucker zeleny, data analytics associate

 

Coming off an exciting NFL playoff season, with all of the stats and information surrounding those games and broadcasts, it seems appropriate to look to the sports world for some examples of how to create a winning analytics formula for your business. Sports, like most industries, has seen a massive data boom in recent years. That data brings with it many possibilities, but also some important decisions.

What should you analyze, how do you analyze it, and how is it presented? Much of the attention often centers on the data collection and preparation process, and what the end result will look like. And rightfully so, as getting the data right typically is the most time-consuming and important component to ensure you have a solid foundation. And the end user experience can make or break adoption of new reports.

But how do you maximize the value of your reporting to truly gain an edge? To do so, choosing the right key performance indicators (KPIs), metrics, or analysis is paramount.

With our experience in the sports analytics industry, there are two concepts to consider when making decisions on KPIs and choosing the proper analyses: content and context.

Content – What is the metric actually measuring?

Context – Does the metric take into account the proper situational context?

Imagine a coach in the NFL evaluating a prospective quarterback. Total passing yardage may be a starting point, but yards per game or even yards per pass attempt will likely be more telling. Or, for that particular coach’s system, the percentage of completed passes may be more important for gauging future success. Contextually, the coach might want to consider the quality of opponents the player faced or even the quality of the player’s own teammates. Offensive proficiency in the NFL has also risen in recent years, so the benchmarks used to define high performance in the past may no longer be relevant.

While the problem you’re facing may be more simple or more complex than choosing the right quarterback, the same concepts likely apply to any business looking to leverage data to drive insight and make decisions. As you determine how best to measure your company’s production, progress, and success, here are three questions to consider:

 

1. What is the ultimate goal, and what available data will be most meaningful in showing progress toward that goal?

Back to our quarterback example, think about the LA Rams’ recent trade of Jared Goff for Matthew Stafford. Of course, the Rams’ ultimate goal is to win more games, and better players will help to accomplish that goal. Through the 2020 season, Goff’s Rams teams had won 59.5% of the games he started, compared to only a 44.3% winning percentage for Stafford’s Lions. But with several complicating factors (quality of teammates, coaching staffs, etc.), would the players’ past winning percentages truly be the best measure for judgment?

Using an advanced metric such as Quarterback Rating (QBR), which more accurately accounts for a quarterback’s contribution on the field, will do a better job of measuring each player’s impact on team performance. QBR (measured on a 0-100 scale where 50 represents a roughly average player) gives Stafford and his career average of 56.4 the clear edge over Goff (47.8).

Similarly, suppose you’re attempting to increase sales and would like to monitor your salespersons’ progress in doing so. Measuring overall sales will likely be the final benchmark for whether the goal was achieved. But perhaps tracking something like leads generated will provide more meaningful long-term insight into future sales and the true progress being made.

A salesperson who closed $5,000 in new sales over the previous month and generated 10 new leads might have done more to drive progress than another salesperson who generated $7,500 in new sales but only two new leads. You may want to incorporate product margins as well, as sales of higher-margin items will be more valuable to the business. Keep in mind that while traditional metrics are a good start, there may be less obvious data available that can help tell a more complete story.

 

2. Which “version” of your data will be most insightful?

Depending on the situation, you may need your data in its raw form, or it may require some adjustment. Without getting too deep into the weeds here, think about raw data as individual data points. That raw data could be adjusted by calculating a summed total, an average, or a percentage.

If a coaching staff is evaluating an opposing defense, should they look at the number of yards given up by that defense in each individual game? Or would yards per game, or even yards per play, average be more telling? Defense on third downs is usually of particular interest, as it measures a team’s ability to get the opposing offense off the field. However, knowing that the defense allowed 6.1 yards per play on third downs isn’t as useful without also knowing the distance to go for a first down in those situations. In this scenario, looking at each third down and indicating whether the offense gained a first down or not, then reporting that the defense allowed a third down conversion percentage of 73% may be more informative.

For your business, will it be more meaningful to look at your sales numbers with daily, weekly, or monthly totals? Or, if you’re trying to identify outliers that may be skewing a total or average, like an invoice amount of $10,000 when all other invoices are below $1,000, you may not want to aggregate at all. Maybe you’re grading your employees based on customer reviews. If the reviews are given on a scale of one to ten, is it more important to see that an employee has an average score of 8.7 or that 92% of that employee’s reviews have a score of eight or higher?

 

3. What is “good” and what is “bad”?

Even a well-planned and carefully designed metric may not mean much in a vacuum. Comparing benchmarks from previous periods or the current environmental standard can help add context.

An NFL quarterback passing for 25 touchdowns this past season may seem impressive without further information. But knowing that the league leaders have often been above 40 or even 50 touchdown passes in recent years adds context. When a major change occurs, whether the slower evolution of increased scoring over the past few decades or the more sudden league move from 16 regular season games to the 17 that were played this season, that benchmarking must adapt. In this example, comparing a quarterback’s season total touchdown passes to the league average for that same season (28.4 for quarterbacks that played at least 15 games in 2021) may be the best choice.

In the business world, comparing the current year to prior year sales can help add perspective. Be thoughtful when making these comparisons. Should the current month be compared to the prior month or the same month in the prior year? Does the metric in question need to be calculated in total or split by location, department, etc.? You may even want to include the comparison as part of the metric calculation. If your company filled 9,872 orders in 2021 and you’re comparing that to the 8,913 orders filled in 2020, reporting the year-over-year change of +959 might be more to the point than reporting the two numbers separately. If there are known industry or environmental changes that will affect comparisons across dates or locations, quantifying and accounting for those as best you can will improve accuracy as well.

While there may be certain industry standards depending on your company’s domain and reporting need, every business will have its unique aspects. Asking these questions as you navigate the analytical process will help keep your team on track and make your organization truly data-driven. If you have any questions, please contact us or learn more about our data analytics services.

ABOUT THE AUTHOR

Tucker Zeleny

531.500.2004

tzeleny@lutz.us

LINKEDIN

TUCKER ZELENY + DATA ANALYTICS ASSOCIATE

Tucker Zeleny is a Data Analytics Associate at Lutz with over six years of experience in analytics. He is responsible for interpreting and analyzing data, data cleanup, advanced analytics, and data science to support customers’ business functions.

AREAS OF FOCUS
  • Data Analytics
  • Data Visualization
  • Data Management
  • Predictive Modeling
  • Sports Analytics
EDUCATIONAL BACKGROUND
  • Ph.D. in statistics, University of Nebraska, Lincoln, NE
COMMUNITY SERVICE
  • Journal of Sports Analytics, Editorial Board Member
  • MIT Sloan Sports Analytics Conference, Volunteer

SIGN UP FOR OUR NEWSLETTERS!

We tap into the vast knowledge and experience within our organization to provide you with monthly content on topics and ideas that drive and challenge your company every day.

About UsOur Team | Events | Careers | Locations

Toll-Free: 866.577.0780Privacy Policy | All Content © Lutz & Company, PC 2021

Fantastic Family Meetings + The Key is in the Planning

Fantastic Family Meetings + The Key is in the Planning

 

LUTZ BUSINESS INSIGHTS

 

Family Meetings

Fantastic family meetings + the key is in the planning

LISA STRUTZEL, FAMILY OFFICE SERVICES DIRECTOR

 

As a family office professional, I can’t say enough good things about family meetings. Practicing effective communication is the glue that holds families together and conducting family meetings is one way to ensure the bond adheres. However, having a fantastic family meeting is more than just gathering the family around the table for a discussion. Proper planning and coordination need to take place to ensure the experience is worthwhile.

ESTABLISHING PURPOSE AND GOALS

The first step in the planning process is to determine why a meeting is needed. What’s the purpose of the meeting, and what goals do you want to accomplish? Family meetings can encompass one or a variety of themes – family development, family business, fun. A good way to get group buy-in is to survey attendees to get their consensus on what they want the primary focus of the meeting to be. Ultimately, the meeting purpose needs to be relevant and engaging enough to motivate people to attend.

The below are examples of meetings I’ve facilitated with differing themes and durations.

Example 1: Family Business Theme (4-hour duration)

Purpose: Provide heirs with a general overview of the estate plan.

Goals:

  • To clarify the intentions behind the plan structure.
  • To explain the plan mechanics (estate tools utilized).
  • To provide beneficiary instruction.

 

 Example 2: Multi Theme (all-day event)

Purpose: Promote family bonding and engagement.

Goals:

  • To develop a Family Mission Statement.
  • To provide education on various topics.
  • To work through situations causing conflict.

 

FORMALIZING THE AGENDA

Once you know what you want to accomplish in the meeting, you’re ready to formalize the agenda. The amount of time allocated to the meeting will dictate the breadth of the discussion. If the meeting length is a half-day or less, it’s likely only one or a couple of topics will be discussed. If the meeting is designed to be an all-day or multiple-day event, more diverse and interactive content will be required to hold people’s attention.

How you begin and end a family meeting is critical. Opening the discussion with a bonding activity is a good way to engage the group and set the tone for open dialogue. People tend to judge experiences based on how they felt at its peak and end (Peak-End Rule). Therefore, ending on a positive note will leave family members with a favorable impression of the experience you are striving for.

Once you have the agenda finalized, it should be distributed to family members prior to the meeting. That way, everyone has time to think about and prepare for the topics to be discussed, and you have a chance to make modifications if necessary.

COVERING ALL THE BASES

When you’ve determined why you are meeting and what you are going to discuss, you need to make sure other key planning aspects are covered:

  • Are you going to have a family member run the meeting, or are you going to hire an outside facilitator?
  • Who should attend the meeting – only lineal family members or are spouses invited?
  • Where will the meeting take place?
  • When are you going to meet?
  • Who is responsible for paying for the costs of the meeting?

It’s important to address all required steps to ensure a successful meeting.

WORTH THE EFFORT

Planning an engaging family meeting is well worth the time and effort. A fantastic family meeting is a catalyst that brings the family together, promotes communication, and creates a memorable experience worthy of repeating. If you have any questions please contact us or learn more by reading related articles on our blog.

ABOUT THE AUTHOR

402.763.2974

lstrutzel@lutz.us

LINKEDIN

LISA STRUTZEL, CPA, CAP® + FAMILY OFFICE SERVICES DIRECTOR

Lisa Strutzel is the Family Office Services Director at Lutz with over 14 years of past experience as a family office executive. She is responsible for assisting high-net-worth clients manage their family enterprise. 

AREAS OF FOCUS
  • Family Office Services
  • Financial Reporting
  • Philanthropy and Legacy Planning
  • High-Net-Worth Families
  • Aviation Matters
AFFILIATIONS AND CREDENTIALS
  • Certified Public Accountant
  • Chartered Advisor in Philanthropy, CAP®
  • Purposeful Planning Institute, Member
  • Nebraska Society of CPAs, Member
EDUCATIONAL BACKGROUND
  • BBA, Iowa State University, Ames, IA
COMMUNITY SERVICE
  • Chartered Advisors in Philanthropy (CAP®) Advisory Board Member
  • Metro Comunity College Applied Finance Institute Advisory Board
  • The Hope Center for Kids, Past President and Treasurer

SIGN UP FOR OUR NEWSLETTERS!

We tap into the vast knowledge and experience within our organization to provide you with monthly content on topics and ideas that drive and challenge your company every day.

About UsOur Team | Events | Careers | Locations

Toll-Free: 866.577.0780Privacy Policy | All Content © Lutz & Company, PC 2021

Top 5 Policies Your Company Should Have Documented

Top 5 Policies Your Company Should Have Documented

 

LUTZ BUSINESS INSIGHTS

 

top 5 policies your company should have documented

robert keenan, chief information & risk officer

 

Policies set the ground rules for how a company conducts itself and holds itself accountable. Without policies and procedures, the rules can shift from department to department or from employee to employee, creating confusion, a lack of cohesiveness, and potentially lead to legal ramifications within the organization.

Why It’s Important to Document Company Policies

Company policies and procedures establish expectations, provide accountability, and serve as the foundation for the character of a company.

1. Accountability

Employees who know what is expected of them perform at higher levels. Removing any ambiguity creates an environment conducive to success and provides for accountability.

2. Compliance

Formal policies and procedures help keep regulatory requirements in the forefront and expectations clear.

3. Trust

Company policies establish guidelines that set the standard for the code of conduct for both employer and employee. A company that sets policies in writing creates enforceable rules and establishes trust within the organization.

4. Training

Clearly defined policies reduce training time and ensure that equal information is available to every new employee.

5. Consistency

When policies and procedures are documented, they can then be universally applied. This lessens the risk of mistakes and financial consequences of inconsistent practices.

Top 5 Policies Your Business Should Document 

Your company may decide to document many policies and procedures, but these five must be among them.

1. Employee Code of Conduct

This outlines the duties and responsibilities of employees in all aspects of their work, behavior, and interactions with other employees as well as clients and potential clients.  It also clearly defines the discipline that employers may use when employees violate the policy, which may include anything from verbal or written warnings to termination. When this policy is communicated effectively to employees, any questions about the appropriateness of employee actions or disciplinary action are removed.

2. Use of Company Property

Abuse of physical and intellectual property is a real concern for companies. Protecting your property and data is a top priority. Use of company property policies may cover password requirements, reporting requirements for phishing attempts, property that can and cannot be used off-site, the use of external devices, and other topics designed to keep your data and company property safe.

3. Harassment & Discrimination

Harassment and discrimination are two subjects every business should take seriously. A policy may not only outline what constitutes harassment and discrimination and the consequences of non-compliance but also specifically encourage fair treatment regardless of race, gender, sexual orientation, or religious and cultural beliefs of another person in the workplace or with any associated independent contractors.

4. Workplace Health and Safety

The health and safety of employees should be a top priority for every company. Policies may cover everything from parking to the use of employee ID cards and how to operate machinery. Workplace health and safety policies are put into place to protect every individual involved, from the CEO to employees, customers, and vendors. It’s not only the right thing to do, but the legal consequences of not creating and complying with workplace health and safety requirements can be significant.

5. Vacation, Time Off, and Leave

Attendance policies define work schedules, and the disciplinary action employees face for violating the policy. This may include the method for verifying attendance, including vacation, leave, and time off, and reporting requirements for such time off.  If employers only allow a specified amount of time off within a designated time frame, that would be documented here as well.

Importance of Updating Company Policies

Despite the best efforts of a company to establish policies and procedures that lead to success, revisions will likely need to be made. Companies change and grow, and until policies are put into action, it’s impossible to know which ones will work for your company and which ones will fall short. Being open to feedback on how to improve your company’s policies tells employees you are flexible and care about getting it right.

Reviewing policies and procedures at least twice annually helps ensure your company’s policies are keeping pace with its growth and with the needs of its workforce.

Creating company policies may not be the most exciting part of your company’s journey, but it is a crucial one. Documenting your company policies can help clarify the foundational workplace principles for your organization while continuing to adjust and grow along with it.

Lutz Can Help

Lutz understands the organizational matters that cause you to lose sleep, and we’re here to bring you peace of mind. Contact us if you have any questions or want to learn more about our risk assessment services. For additional information on related topics, our consulting blog can help.

ABOUT THE AUTHOR

402.763.2973

rkeenan@lutz.us

LINKEDIN

ROBERT KEENAN + CHIEF INFORMATION & RISK OFFICER

Robert Keenan is the Chief Information & Risk Officer at Lutz with over 20 years of compliance and operational risk experience. He focuses on risk management, compliance, and security for the firm, and will partner with the operations team to drive process improvement and operational efficiencies for Lutz.

AREAS OF FOCUS
  • Risk Management & Compliance
  • Operations
AFFILIATIONS AND CREDENTIALS
  • Association of Certified Fraud Examiners
  • Society of Compliance and Ethics Professionals
  • National Society of Compliance Professionals
  • Certified Fraud Examiner
  • Certified Compliance and Ethics Professional
EDUCATIONAL BACKGROUND
  • BA in Finance, University of Oklahoma, Norman, OK
  • MPA, Drake University, Des Moines, IA
COMMUNITY SERVICE
  • Association of Certified Fraud Examiners - Heartland Chapter, Past Board Member

SIGN UP FOR OUR NEWSLETTERS!

We tap into the vast knowledge and experience within our organization to provide you with monthly content on topics and ideas that drive and challenge your company every day.

About UsOur Team | Events | Careers | Locations

Toll-Free: 866.577.0780Privacy Policy | All Content © Lutz & Company, PC 2021

Dirty Data? 6 Ways to Cleanse a Database

Dirty Data? 6 Ways to Cleanse a Database

 

LUTZ BUSINESS INSIGHTS

 

DIRTY DATA

dirty data? 6 ways to cleanse a database

tony desantis, data analytics manager

 

Organizations can only make truly effective strategic decisions when the data they review is dependably accurate. This level of accuracy is challenging to obtain and maintain, as data integrity tends to degrade over time. Inaccuracies tend to creep in through lack of controls within a system or process, staff error or inaccurate changes made by users, customers, etc. Businesses must be proactive to ensure they do everything possible to keep their database information clean and accurate.

What is Dirty Data?  

Dirty data is a name given to a specific type of information held in a database. It is data that may have spelling or punctuation errors. It could also be outdated, incomplete, or missing altogether. Bad data could be duplicate or inconsistent data, such as when the name of a state is spelled out vs. abbreviated in others.

How Dirty Data Causes Issues

Once you understand what comprises dirty data, it becomes clear how it can cause major issues for firms (most of whom heavily rely on this information for their strategic decision-making). With bad data, even minor reports may be significantly inaccurate, data security and privacy issues can arise, it becomes more difficult to aggregate information, and larger accounting and financial reports could contain significant inaccuracies.  

How Accurate is Your Data?

If your firm seems to be struggling with data issues, you are not alone. Many organizations face problems that arise from dirty data. Companies who struggle with nonexistent data, unreliable data, or data discrepancies (no single source of truth) may want to consider whether their database(s) no longer have a high level of accuracy.

Strategizing the Data Cleansing Process

After recognizing you have a problem with dirty data, the next step is to develop a data cleansing strategy. Some points to consider when organizing this initiative include:

  • Deciding how clean is “clean enough.”
  • Identifying what data is actually dirty and how it affects reporting and analytics strategies.
  • Identifying the cause(s) of dirty data.

Not every company will require the same level of data cleanliness. For example, some will be willing to accept street addresses that abbreviate words such as “Street” or “Drive.” Others will have more strict requirements, for example, changing all abbreviated street addresses to reflect their full description. 

Companies must pay particular attention to data that is incorporated into their reporting and analysis efforts. There may be some instances of bad data that does not negatively affect reporting and analytics enough to justify a stringent cleaning effort. In other cases, it may be “mission-critical” to thoroughly cleanse bad data, as well as employ methods to ensure the newly cleansed data maintains its accuracy for these areas.

Lastly, if data is important enough to go through the cleansing process, it must be determined how it became dirty in the first place. Any processes that allowed the insertion of bad data must be corrected. Generally, the most common underlying issue is inaccurate data entry. Other areas that may need to be addressed are whether there are multiple sources that capture the same data, how data is captured (e.g., free-form text fields, vs. drop-down lists and checkboxes), as well as other processes with “holes” that allowed the input of bad data.

6 Ways to Clean Data

Not every step in the list below will be necessary for every company that needs to cleanse its database. The methods a company decides to employ will depend on the type of data they are trying to scrub.

How to clean a database: 

  1. Leverage in-house skill sets to write programs or scripts to address bad data. Tools such as Python, R, and SQL can all be used to merge, update, and delete data.
  2. Employ automated programs to identify and merge duplicate fields, records, etc.
  3. Consider the value of purging a database of specific records at a pre-determined time. This can help clean the remaining data and reduce the scope of data to be evaluated.
  4. Update outdated records and fields with newer data and determine whether it is necessary to retain old data. If you are deleting old information, create a change log or history file to store the outdated data.
  5. Enrich missing data by employing easily accessible in-house data. Some firms may want to consider employing a data service that already has the needed data, to allow for quicker updates.
  6. Standardize data by developing naming convention standards for data sources across the board. Also, determine which fields should be standardized, then employ tools that automatically look for opportunities to standardize fields (e.g., St vs. St. vs. Street). Once clean data is in place, ensure the input processes for the newly cleansed fields will only allow for the insertion of data in a specified format. 

In summary, having accurate data to draw from when making business decisions is vital. By diligently addressing all the issues associated with dirty data, companies will greatly improve the accuracy of their reporting and analysis efforts, both now and in the future.  

If you would like more information on how to clean and maintain the integrity of your organization’s data, please contact us. You can also read related articles on our blog.

ABOUT THE AUTHOR

Tony DeSantis

402.496.8800

tdesantis@lutz.us

LINKEDIN

TONY DESANTIS + DATA ANALYTICS MANAGER

Tony DeSantis is a Data Analytics Manager at Lutz with over 20 years of experience. He is responsible for interpreting and analyzing data, as well as designing report visuals in support of client engagements. In addition, he specializes in data management and the application of artificial intelligence to simplify business processes.

AREAS OF FOCUS
  • Data Analytics
  • Data Visualization
  • Data Management
  • Artificial Intelligence
  • Forensic Analytics
EDUCATIONAL BACKGROUND
  • BS in Finance and Operations, Minor in Management Information Systems, University of Delaware, Newark, DE
COMMUNITY SERVICE
  • Junior Achievement, Volunteer
  • Gilda's Club Chicago, Past Board Member

SIGN UP FOR OUR NEWSLETTERS!

We tap into the vast knowledge and experience within our organization to provide you with monthly content on topics and ideas that drive and challenge your company every day.

About UsOur Team | Events | Careers | Locations

Toll-Free: 866.577.0780Privacy Policy | All Content © Lutz & Company, PC 2021