Every company wants to have reliable data. But because many companies maintain information in multiple systems, data accuracy or consistency problems are impacted by human error. The significant time and money needed to correct errors can be saved by implementing effective proactive and reactive controls.
During a recent client engagement, MorganFranklin consultants executed a straightforward data cleansing project. The client was a multi-billion-dollar company that grew inorganically through many acquisitions in a short period of time. The client had two systems that fell out of alignment—a time-tracking system and a payment/leave-tracking system. Over the years, data differences caused the company headaches and cost it money. Employees complained that their leave balances were inaccurate. Occasionally incomplete data led to double payments, especially at the end of an employee’s tenure when recouping overpayments was difficult if not impossible.
MorganFranklin’s accounting team addressed leave balance concerns and helped balance the books by figuring out double payments and missed payments. Meanwhile, our data and technology team used advanced data analytics software to match entries from the client’s two systems to identify data matches and the leftover incongruities and missing data. The team created custom workflows that ran within seconds to match entries based on employee ID, dates, hours, and amounts. The leftover incongruities and missing data -thousands of records- were then manually checked in databases and alternate data sources.
Our teams were able to clean up data sets for our client—as consultants could do for your company—but stopping there is not enough. The important questions are why did data issues exist and what can be done to prevent them?
Many companies (yours likely included) have data that exists in separate systems, such as Salesforce and Excel, Costpoint and ADP, or Workday and NetSuite. With two systems housing the same data, it becomes likely that databases will fall out of alignment unless there are controls in place. The easiest way to prevent misalignment is to make one database solely dependent on the other. For example, if NetSuite only pulls HR data from Workday (and no HR data is input directly into NetSuite), there may be inaccuracies but there will never be misalignment. However, even if you set up your databases that way, it is worth creating indicators to monitor whether correct procedures are being followed.
When our consultants meet with clients, we often find that executives assume their databases are structured in a way to avoid misalignment. But it is soon discovered that some employees break protocol and enter information directly into a database, usually when they need to make a quick, simple, time-sensitive change. The employee intends to go back later and sync the two databases post-crisis, but inevitably somebody forgets to make the change. Like death and taxes, human error is unavoidable.
Two types of controls are needed to assure data stays in sync: Proactive controls and reactive controls or indicators.
Proactive controls. The first way to assure alignment is to place controls on the inputs. For example, if an organization hires contractors based on different collective bargaining agreements (CBAs), each CBA will dictate which leave types are available to an employee. If an employee only has three of a possible 15 leave types available, proactive controls would only allow that employee to enter one of their three available leave types. This basic system rule will prevent obvious mistakes. Additionally, proactive controls could prevent terminated employees from entering hours, employees without available vacation hours from entering vacation hours, or an associate entering more than eight to 12 hours in a single day.
All modern data systems, from ERPs to CPMs, allow custom rules around associates inputting data. For rules that apply 80 to 90 percent of the time, create controls such that unusual data entries require management approval. For example, say that a company only permits employees to work eight- to 10-hour shifts. However, due to a COVID-19 exposure an associate is unable to come to work and contact tracing prevents additional employees from covering. The person on duty might work 14 hours until a replacement can be found. When that associate inputs 14 hours (a number not normally allowed), a message could pop up saying, “You have exceeded the expected hours input. All overtime hours will be sent to management for approval. Would you like to submit?” Upon submitting, that entry is held until a manager approves the exception. This type of control also prevents the more common situation where an associate meant to type eight hours and instead enters 88 hours.
Reactive controls and indicators. Reactive controls are often called indicators because they act more as red flags or alarms than preventative measures. The most basic form of reactive control is regular reporting. If an area manager gets a weekly report of sales by store, that manager should notice if a single store has an unusually high or low sales total on any given day. If a manager gets a weekly report on associate hours, it will be obvious when an associate’s hours spike or drop. (In the case of 88 hours being typed accidentally, the associate will show 120 hours where the manager expects 40.) These are the simplest reactive controls that rely on attentive managers to input weekly corrections.
Modern data visualization tools like Tableau and Power BI allow these indicators to go one step further. Not only would these tools save time through automation, they can also introduce more complex formulas and easy-to-read reporting. A manager can get a weekly report of sales with colored highlights indicating where sales are outside the expected range in reference to selected baselines (such as year-over-year sales or rolling averages). These advanced indicators make it easy to identify and correct errors. Alternatively, reports can be designed such that if a manager sees green indicators, the manager ignores the report. Only metrics in red would require action. In Tableau or Power BI, the manager can drill down into the red cell for additional insight to identify the root of the problem. In Excel, that usually requires manual digging and significant time. Tableau and Power BI also allow for real-time, in-depth, self-service reporting.
Data is your competitive advantage in the modern marketplace. If your company is experiencing issues with data governance, accuracy, or consistency, it makes sense to put proactive and reactive measures into place. MorganFranklin Consultants can help align your current data and set up controls to prevent future misalignment. Our diverse team of data and technology experts can help you leverage your data by simplifying, automating, and analyzing your data. Reach out today and let’s get a conversation started.
