Data Validation

Data is one of the most important parts of any implementation. The old saying of “Garbage In / Garbage Out” can be applied here. If we load bad or poor-quality data, the outputs of that data will be bad or have poor-quality. The more data validation that is done during implementation, the better the overall outputs will be. Taking ownership of the data begins with data validation. It will also provide a smoother transition and reduce or eliminate issues at Go Live. Data and data validation are key to a successful Go Live. In this page, we describe methods that will help you better understand why you are validating and how you can use features within Deacomto assist in this process.

Roles & Responsibilities

There are several roles that are involved with data validation. It’s a team effort. The Data Specialists, the Product Specialists, Subject Matter Experts (SMEs) and End Users have critical roles to play in data validation. The Data Specialist’s role in validation is the same for every section so we will discuss here. It begins with the load file. Along with the Product Specialist (PS), they will review the file and determine where the data will reside in Deacom. The Data Specialist’s responsibility is to confirm record counts and validate the information in the file loaded to the fields mapped in the data profile. This is the first level of data validation. The data profile will be modified to fix any issues found during this validation. However, the file may be missing key information that the data specialist does not know exists or during implementation new data is identified that is required to support a new process. Data Specialists can also provide queries that can be used to export data to an Excel spreadsheet where you can easily filter or sort and review many records at one time. Note: these spreadsheets are for validation only and will not be used to update the data. The root cause of any data issue will be researched, and the data profiles updated so the data can be updated, and new records will come in with the proper changes and existing records will be updated. The Product Specialist’s role in validation is to review the data loaded for every section and help discover, during Standard Operating Procedures (SOPs) validation, if we are missing any key data, while at the same time validating these processes using the imported data. This data will also be used in Mock Go Live and Conference Room Pilots (CRPs). We will discuss in each section our recommendation on who from the company’s team should be involved and why, but only the company knows who the best person is.

Additional Resources: The attached documents list the Deacomreports and report parameters you should use to validate data against your legacy system.

Note: MS Word is required.

Import key setup

Chart of Accounts - The Chart of Accounts are the general ledger accounts used by a company to determine how much money was spent and received. These accounts allow companies to accurately and correctly report on expenses and profits in the general ledger. Almost every area of the system touches the general ledger. Vendors have the accounts payable (AP) account on them. Customers have accounts receivable (AR) accounts on them. Items have numerous accounts like inventory, adjustment, production, expense and revenue accounts. When most transactions are performed, there can be several general ledger postings that happen. If the accounts are not correct, the system will not be able to provide accurate reports and could misstate revenue and/or expenses. It is critical if changing the Chart of Accounts that the mapping file contains the proper mapping. During the validation of the general ledger, trial balance, profit and loss and balance sheet reports are compared to legacy reports to ensure the accounts contain the correct data. If a mapping error is found, it is fixed in the mapping file, and the data is reimported. Having accurate historical data for year over year comparison is vital to the accounting team and others for historical analysis.

Overrides - There are several places in Deacomwhere you can setup General Ledger (GL) overrides. The primary location is at the Facility level. These overrides allow the Deacomsystem to change the general ledger account used by applying specific overrides configured on the GL Overrides tab on the Facility record, driven by the facility field on transactions. Some or all Facilities can use the same or different general ledger accounts. For example, there is a base inventory account of 11000-00-000 but Facility A’s inventory account is 11000-01-000 and Facility B’s account is 11000-02-000. If the GL Overrides are not validated, inventory will be showing in the wrong general ledger account, which will overstate or understate each facilities’ inventory.

Units of Measure - Deacomhas a centralized unit of measure (UOM) functionality. The advantage of this setup is that the conversion factor can be setup once instead of multiple times. The setup of the unit of measure conversion factors affects Items, Purchase Orders, Sales Orders, Formulas, and Jobs, so it is crucial that it is setup correctly. It is important to discuss and validate the UOM table early in the implementation. Why? Because it affects all areas of the system including imported Sales Order, Purchase Order and Inventory data. A change in the UOM table can cause incorrect reporting to happen. For example, an imported invoice has a qty of 1 Drum with a sales unit of Each. The drum holds 55 gallons. The item stock unit at the time of import was Each. This invoice would show 1 Drum was taken out of inventory. If the stock UOM is changed to Gallons after the import, the invoice thinks it took 1 gallon out of inventory when it should show 55 gallons. Reporting would be incorrect.

Bill-to's

Why - Customers are key to any company’s success. Happy customers tell other people about the company’s products and services. It is important that the implementation cutover has minimal impact on that relationship and has little to no impact on standard processes. That is why it is important that imported data for the Bill-tos are correct. Acknowledgements and invoices will make it to the correct place, and the cash flow is not effected. Implementations can span months or years and the data is constantly changing. It is important to validate that information and take ownership to ensure it is maintained.

What to check - The best place to start checking is the basic information. For example, Name, Address, Email, Fax, Contact and Phone information. Other key fields including AR account, Terms, Credit Limits, PO Required and Sales Reps are also important. Each implementation is different, so it is important to check any other special setup that was configured. e.g. User Defined Fields (UDFs) or system flags. The data process is scientific and will load the data to the same fields every time. Where to begin? Start with the top largest sales volume customers. Check customers that have only one Ship-to, customers with multiple Ship-tos, and customers from different industries or territories. If you are bringing in inactive customers for historical data, check a few of those too.

How - The best way is to bring the customer up in the legacy system and bring the customer up in Deacom. Carefully compare the data for each field. If you find information is missing or in the wrong field, contact the data specialist and they will determine why this is happening.

Roles & Responsibilities - The best way is to bring the customer up in the legacy system and bring the customer up in Deacom. Carefully compare the data for each field. If you find information is missing or in the wrong field, contact the data specialist and they will determine why this is happening.

Ship-to's

Why - Ship-to data is crucial to the order process. It contains the necessary shipping information for Sales Orders (e.g. name, address, contact, phone and EDI data). Ship-to records also hold valuable default information that will be transferred to the Sales Order when it is entered (e.g. Ship via, Search fields and Sales Assignments). It is crucial for logistical purposes that the shipping information is validated so customers receive their orders at the correct address. If this information is incorrect, shipments will go to the wrong address and sales reports will have badquality data.

What to check - The best place to start checking is the basic information. For example, Name, Address, Email, Fax, Contact, and Phone. Other key fields transferred to orders include default Ship via, Freight Type and Search fields. Where to begin? Start with the top largest sales volume customers. Check that all their Ship-tos exist. Confirm the ST is linked to the proper Bill-to. Run reports by Search fields to confirm Shiptos are correctly set. If you are bringing in inactive Ship-tos for historical data, check a few of those too. If using EDI, is the correct information loaded. If customers have pricing orders or item cross references, they also need to be validated.

How - The best way is to bring the Ship-To up in the legacy system and bring the Ship-To up in Deacom. Carefully compare the data for each field. If you find information is missing or in the wrong field, contact the data specialist and they will determine why this is happening.

Roles & Responsibilities - Typically, Subject Matter Experts, Customer Service and/or Sales employees have the responsibility to validate the data. They use the data every day and can easily spot if key data is missing.

Vendors

Why - Vendors are key to sourcing the materials and services needed to make products, deliver services, and/or procure products for resale. There are many aspects to managing vendors from researching and sourcing vendors, as well as obtaining quotes with pricing, capabilities, turnaround times, and quality of work. If Vendors are not validated, purchase orders may be sent to the wrong address which results in a delay in receiving the materials or services needed. This may result in missed manufacturing deadlines and costly delays. If pricing contracts or specific vendor pricing is not validated, this can cause increased costs. Whether it’s the time it takes to resolve the invoice discrepancies or increased costs because old outdated costing was used, it is critical that all aspects of vendor management is validated.

What to check - The best place to start checking is the basic information. For example, Name, Address, Email, Fax, Contact and Phone information. Other key fields include Terms, Payment method, default Ship via and Freight Type. Some vendors may require 1099 information; be sure to validate the vendor taxpayer ID or Social Security Number fields, 1099 box and 1099 form type fields if you plan to create 1099s from Deacom. If the vendor has Remit To information, be sure the Remit To Name is filled in because it determines if Remit To information is used. If using any search or user defined fields specifically created for SOPs, those should also be validated. If vendors have pricing orders or part cross references, that information also needs to be checked. Where to begin? Start with the top largest purchase volume vendors then vendors in different territories or industries. If you are bringing in inactive vendors for historical data, check a few of those too.

How - The best way is to bring the vendor up in the legacy system and bring the vendor up in Deacom. Carefully compare the data for each field. If you find information is missing or in the wrong field, contact the data specialist and they will determine why this is happening.

Roles & Responsibilities - Typically, Subject Matter Experts, Purchasing and Accounting have the responsibility to validate their portion of the data. They use the data every day and can easily spot if key data is missing. The accounting group would validate AP account, Remit To information while Purchasing validates the other vendor data.

Items

Why - Item Master records are another key data element for a successful Go Live. The combination of flags on the Properties tab determine how Parts may be used throughout the system. This includes defining if they may be sold, purchased, taxable, usable in Direct Store Delivery, finished to WIP, serialized, included in MRP, etc. The setup of these items drives how the system functions. The items also affect the general ledger based on how the accounts are setup. If the accounts are wrong, this could potentially make the postings wrong in sales orders, purchase orders, production, etc. The UOM setup is critical to how inventory is stored and recorded on purchase and sales orders.

What to check - The best place to start checking is the basic information. For example, Item/Part Number, Category, Item Planner, UOMs, Item Type, Accounts, Costs and the Properties flags are the essential fields. Shelf life, Min, Max, UPC codes, Part forms, search field and UDFs should also be reviewed. Templates are used to set the majority of properties, so it is important to validate those templates. Where to begin? Start with the highest volume of supplies, raw materials, components and finished goods. Be sure to include maintenance parts and maintenance task items. If you are bringing in inactive items for historical formulas or orders, check a few of those too.

How - The best way is to bring the item up in the legacy system and bring the item up in Deacom. Carefully compare the data for each field. Be extra careful to review the accounts and costs. If you find information is missing or in the wrong field, contact the data specialist and they will determine why this is happening. Use the queries discussed previously to view many items at the same time. Filtering in Excel can be useful to quickly see items that are in the wrong category or inventory account.

Roles & Responsibilities - Typically, Subject Matter Experts, Customer Service, Purchasing, Production and Accounting have the responsibility to validate their portion of the data. They use the data every day and can easily spot if key data is missing. The Accounting group would validate the account information while Production validates the MRP data (min, max, etc), and Purchasing validates PO ordering fields (PO Qty, Min. Purchasable, Incremental PO Qty, Lead times, etc.)

Formulas

Why - Formulas or Bill of Materials (BOMs) are the list of the parts or components and quantities of each needed to produce a subassembly, finished good or perform a maintenance job. The parts required may include raw materials, components, subassemblies, and/or finished goods. BOMs are hierarchical in nature with the top level representing the finished product which may be a subassembly or a completed item. In Deacom, the Bill of Materials also contains information regarding costs and if setup the labor costs necessary to produce the item. It is critical that all formulas are validated before use. If the formula is wrong, it could be very costly as too little or too much material may be used, and the end result is not a useable product; resulting in large inventory expenses, or hazardous chemical reactions. Incorrect costs could affect the GL if in Standard Cost inventory mode and Standards were updated from incorrectly costed formulas.

What to check - The best place to start checking is the basic information. Confirm what item is being produced, the formula UOM, formula notes, the batch yield and unit cost. At the line level, confirm items, quantities and UOMs. If the formula has QC or Routings, check that the correct one is attached to the formula. Be sure to check if byproducts and/or scrap settings are needed. Where to begin? Start with the highest volume subassemblies, finished goods and, if applicable, maintenance formulas. To track validation, use features built in Deacomas discussed below.

How - The best way is to bring up the formula in the legacy system and bring up the formula in Deacom. Compare all key fields to determine the formula was imported correctly. Deacomsuggests using one of the two setups below:

Lab Only is a check box that prevents a formula from being used in Production until the formula is validated and the lab only box is cleared.

Work Flows can be setup for each area of the formula that needs to be reviewed. The work flow can be setup such that the formula cannot be used in Production until the work flow is completed. This method is the most complete method as many people need to validate the formula and can be working simultaneously to validate.

Roles & Responsibilities - Typically, Subject Matter Experts, Formulators, Production and Accounting have the responsibility to validate their portion of the data. They use the data every day and can easily spot if key data is missing. The Formulators confirm the formula has the proper items and is making the correct amount. The Accounting group would validate the cost information while Production validates the notes and batch ticket instructions.

Sales Orders

Why - Sales Orders in Deacomare used to define the items or services that will be sold to customers along with the appropriate Payment Terms, Delivery Dates, Quantities, Shipping Terms, and all other obligations and conditions. It is vital to the ensure open sales orders have the correct information so shipments to your customers continue without delay. It is also important that historical sales orders are validated to ensure reliable reporting.

What to check - The best place to start checking is the basic information. Confirm the legacy order number, the Bill-To and Ship-To, AR account, Items, Terms, Ship via, Order Dates, Unit Price, and total order amount. Be sure to confirm search fields, order notes and UDFs if applicable. Where to begin? Start with open orders since these will be involved with transactions after Go Live. Compare open order report from legacy system to open order report from Deacom. It is important to validate early as open orders will change after load files are prepared. Then move on to the historical orders/invoices. Working from a static test copy of your legacy system is the best as the data is not changing.

How - The best way to begin is bring up the sales order in the legacy system and bring up the sales order in Deacom. Compare all key fields including Due to Ship date, which is required for MRP, to confirm they match what was imported. Be on the lookout for any missing data. If multiple facilities are imported, run facility reports and confirm the facility is properly set. If using multiple currency, check that the currency is set correctly. Don’t forget to check Sales Assignments and Tax Regions if applicable.

Roles & Responsibilities - Typically, Subject Matter Experts, Customer Service and/or Sales employees have the responsibility to validate the data. The Accounting team should confirm the proper AR accounts are being used as invoices with an open balance must match the trial balance.

Purchase Orders

Why - Purchase Orders define the items or services to be provided by a single Vendor, along with the appropriate Payment Terms, delivery dates, item descriptions, quantities, Ship Via Methods, and all other obligations and conditions. It is vital to the ensure open purchase orders have the correct information, so deliveries of material or services continue without delay. It is also important that historical purchase orders are validated to ensure reliable reporting.

What to check - The best place to start checking is the basic information. Confirm the legacy order number, the Vendor, AP and suspense accounts, Items, Terms, Ship via, Order Dates, Unit Price, and total order amount. Be sure to confirm 1099 information, search fields, order notes and UDFs if applicable. Where to begin? Start with open orders since these will be involved with transactions after Go Live. Compare open order report from legacy system to open order report from Deacom. It is important to validate early as open orders will change after load files are prepared. Then move on to the historical orders/invoices.

How - The best way to begin is bring up the un-received purchase order in the legacy system and bring up the purchase order in Deacom. Compare all key fields including Due to Dock date, which is required for MRP, to confirm it matches what was imported. Be on the lookout for any missing data. If multiple facilities are imported, run facility reports and confirm the facility is properly set. If using multiple currency, check the currency is set correctly. Be sure to use these orders to perform transactions in Deacom.

Roles & Responsibilities - Typically, Subject Matter Experts, Purchasing and Accounting have the responsibility to validate their portion of the data. The Accounting team should confirm the proper AP accounts, expense and inventory accounts are being used. POs received but not invoiced (RNI) and invoices with an open AP balance must match what’s in the GL accounts at go live.

Inventory

Why - When inventory is loaded into Deacom, it is loaded in the DeacomStock Unit of Measure. It is critical to the Go Live that the costs and quantities are correct for the Stock UOM. If during process engineering, it is determined that the inventory UOM should change, the load files must match the system setup. For example, an item in the legacy system is stored by the bottle but now it will be stored by Case-10. The load file has 1000 when it should have 100 since the system knows 10 bottles are in each Case-10. The overall dollar amount might be correct, but the quantity is incorrect. The system will think it has 1000 Case-10 or 10,000 bottles. Any changes to UOM after Go Live must be done manually due to general ledger effects and the number of tables involved.

What to check - The best place to start is checking the basic information. Confirm that the item number, quantity in Stock UOM, lot costs, extension, location and facility are correct. Be sure to check lot numbers and expiration dates if applicable. Often overlooked, confirm the correct inventory account is assigned. Where to begin? Start with highest volume inventory items, items whose Stock UOM changed from the legacy system, items that should have expiration dates, items that should be in quarantine and/or items issued to jobs.

How - Start with a Lots report from legacy system and compare to a lots report from Deacom. In Deacom, run a Lots report with all filter criteria set to All. Confirm the key fields are correct. Run Lots report by facility to confirm the inventory was loaded to the proper facility. Subtotal report by Account and confirm the proper items are in the proper inventory account. This will affect where the dollars are allocated in the general ledger.

Roles & Responsibilities - Typically, Subject Matter Experts, Production, Warehouse Managers, and Accounting have the responsibility to validate their portion of the data. The inventory value must tie to the general ledger so Accounting will want to confirm the inventory is in the correct accounts in the general ledger and confirm the value. If GL accounts have a facility segment, each facility’s Warehouse Manager can confirm inventory is in the correct location.

General Ledger

Why - Journal entries, which make up the General Ledger, provide an audit trail and a means of analyzing the affects of the transactions on an organization's financial position. Deacomcreates most entries automatically through system transactions such as purchasing, sales, and production. Historical journal entries are loaded so the general ledger has the current financial position. It is critical that the General Ledger be validated to ensure the accounts have the correct dollar amounts. If the GL is not accurate, financial reports would be inaccurate.

What to check - The best place to start is with the beginning balances. Today’s balance is calculated from the beginning balance and all the activity from then until today. Check the account numbers and the dollars amounts. If multiple facilities have independent general ledgers, check by running report by the facility segment.

How - Start with the beginning balances. Run a Trial Balance report for the beginning date and filter by the closing entry in the advanced filters, gl_descrip = Closing entry. Check that the account numbers and dollar amounts are correct. Then run these reports year by year. Each year’s activity builds the total for today. If accounts have the wrong amounts, it can be due to a bad load file or incorrect mapping table. Confirm the GL is in balance.

Roles & Responsibilities - Typically, Subject Matter Experts and Accounting have the responsibility to validate the general Ledger data. If department specific financial reports are generated, those departments should validate those reports.

Full circle

Below is the full circle for data validation.

Data Imported - Data is imported and validated by the Data Specialists and/or Product Specialist. This validation is a comparison to the load files provided and to confirm the data is going into the correct fields.

Data Validated - Once imported, it is important for data to be validated by the Subject Matter Experts and/or End Users. This validation is a comparison to the legacy system. Data specialists do not know what is missing if the file never contained the information.

Data Processed - Once the data is validated, it is time to use the imported data by doing transactions in Deacom. This ensures the imported data will transact properly with customer specific data. This builds the End Users’ confidence and confidence in Deacomis higher.

Postings - Once the data is used in transactions, it is imperative that the general ledger postings are reviewed. That will ensure the configuration of the items, customers, and facilities are posting the dollars to the correct accounts.

Reports - Once all the above is completed, run the reports needed to run your business. Are the proper results being captured? All necessary reports should be created and validated before Go Live.

Why data validation is critical

When the data has been validated...

Lower Risks - It has been proven to reduce the risks at Go Live. Less unforeseen issues arise if the data processes have been validated. Practicing with the validated data ensures daily transactions will be able to be completed and lowers overall risks.

Go Live Duration - It has been proven to reduce the amount of time required for the actual Go Live. If historical data is imported and validated before Go Live, it reduces what needs to be validated during the Go Live. If doing several phases, it will reduce the amount of time Production systems need to be down.

Customer Service - It will ensure customers will receive accurate orders and invoices. Customer payments can be applied to the correct orders.

Procurement - It will ensure vendors continue to receive accurate orders and send the material needed for Production. Payments will be sent to the correct Remit to.

Reporting - Accurate reporting is the result of validation, whether validating imported data or validating manually entered orders. Data that is accurate and usable is crucial to every company to make informed decisions.

Success - Validated data is the first step to a successful Go Live. The more accurate the data, the quicker the road to success.