Home > Blog > Reconciliation in Dynamics GP: The Key to Data Consistency

Reconciliation in Dynamics GP: The Key to Data Consistency

Proper financial management is crucial for any business, and educating yourself and your team on reconciliation in Dynamics GP ensures the accuracy and integrity of your data.

When used properly, reconciliation processes help you identify and correct discrepancies in your system to ensure your financial data remains reliable and consistent. These tools are vital for streamlining workflows and avoiding manual error correction when dealing with:

• Subledgers
• General ledger
• Banking processes
• And more

In this blog, we'll provide you an overview of what reconcile utilities are and why they are essential. We will also dive into specifics by examining common reconcile utilities and their unique functions and applications. In the last section, we'll dig into the Reconcile to General Ledger utility and go through the specifics of that process.

What are Reconcile Utilities in Dynamics GP?

Reconcile utilities in Microsoft Dynamics GP are essential tools designed to save time, correct data discrepancies and fix issues that might arise when transactions don't update correctly. These utilities serve as a cross-check mechanism, ensuring that summary information aligns with detailed transactions. By recalculating and verifying data, GP helps maintain accuracy and integrity in financial records.

Things to Consider Before Reconciling in Dynamics GP

Prior to running a reconcile process in a GP module, there are a few general and important considerations to keep in mind to ensure the process goes smoothly:

1. The reconcile utility is most often found in the utilities section of each specific module within GP, making it easy to access and use for resolving data discrepancies.
2. It is important to note, changes created while running reconcile routines are typically permanent, so it's crucial to have a database backup before running a reconcile, as unintended consequences can occur.
3. Running reconcile regularly, like at month-end or year-end, can help identify and fix data issues. However, due to the complexities involved, it's generally best to use it only when you suspect data inconsistencies.

How the Reconciliation Process Functions within Specific Modules

Each module has its own set of reconcile utilities. In the following section, we will explore the reconcile processes for six of the most commonly used modules within GP, providing detailed insights into their specific functionalities and best practices for their use. This will help you understand where to find the reconcile routines and how to leverage these utilities effectively across different areas of your financial operations.

Reconcile Financial Information

You can find the Reconcile Financial utility by navigating Tools > Utilities > Financial > Reconcile. It offers three reconcile routine options:

Allocation Accounts

This option checks both variable and fixed allocation accounts. It also ensures all allocation and distribution accounts are valid, active, and correctly set up.

Summary Balances for General Ledger Accounts by Year

This process recalculates the summary balances for General Ledger (GL) accounts based on transactions in a selected year. It's useful for resolving discrepancies between summary inquiry reports and individual transactions. It's recommended to run this if you or another user make changes to your fiscal year or need to re-do a year-end close. If you want to reconcile multiple years, start with the oldest year first. If you are operating a newer version of GP, you can select the "all years" option.

Batch Totals

The batch totals reconcile utility updates and verify that your batch totals and the number of transactions match your batch data. It's recommended to use this option if batch summaries show incorrect totals or transaction counts.

Note: This utility does not generate reports or display a progress bar. So, once you see your cursor return to normal, the reconciliation process is complete.

Reconcile Payables Accounts

The Reconcile Payables Accounts process in the Purchasing module ensures accurate financial records by updating unapplied payment amounts, unpaid finance charges, current balance totals, and on-order amounts. Access this feature by navigating to Tools > Utilities > Purchasing > Reconcile. 

To reconcile payables accounts, you can select vendors based on the following:

• Vendor ID
• Vendor Name
• Vendor Class
• Type
• Payment Priority

Use the "Print Report" option to preview adjustments before making permanent changes. Once confirmed, finalize the reconciliation by marking the "Reconcile" option. There are three key reconciliation processes in this

The reconciliation process includes updating summary information such as unapplied payment amounts, unpaid finance charges, current balances, and on-order amounts. Additionally, you can recalculate vendor balances for the selected fiscal year and update batch headers and totals based on unposted transactions to resolve discrepancies and ensure accurate financial records.

These are three key reconciliation processes to be familiar with:

Summary Information

Summary information updates unapplied payment amounts, unpaid finance charges, current balance totals, and on-order amounts. This reconcile option is useful for correcting discrepancies in transaction applications and balances.

Vendor Balances for Fiscal Year

This reconciliation process recalculates the vendor balances and summary information for the selected fiscal year. It also ensures vendor balances accurately reflect all transactions for the year.

Batch Headers Based on Unposted Transactions

You will run this process when updating the batch header and totals based on the unposted transactions saved in the system. This process is helpful if batches are "missing" transactions or batch totals are incorrect.

Note: Using the print report option is a good precaution to review changes before finalizing. Ensure to mark the "Reconcile" checkbox to apply the changes once verified.

Reconcile Purchasing Documents

The Reconcile Purchasing Documents option in the Purchasing module allows you to update the header information on unposted Purchase Orders to align with the corresponding line items. This feature is especially useful for correcting errors with totals or distribution amounts that do not match the header. Access this function via Tools > Utilities > Purchasing > Reconcile Purchasing Documents.

Here are the key options to run this reconciliation:

Print Report Only

Use this option first to generate a report that previews the changes, allowing you to verify the results before making any adjustments.

Reconcile and Print Report

After reviewing the "Print Report Only" output, select this option to apply the changes and update the purchase order header information to match the line items.

This tool is beneficial for resolving discrepancies in purchase orders, such as mismatched distribution amounts or totals, ensuring that your purchase orders are accurate and ready for invoicing and receiving.

Reconcile Receivables Amounts

The Reconcile Receivables Amounts feature in the Sales module ensures accurate customer balances and transaction amounts by updating current customer information and outstanding document amounts. Access this function via Tools > Utilities > Sales > Reconcile.

To reconcile receivables amounts, select customers based on criteria such as Customer ID, Customer Name, Customer Class, Customer Type, Salesperson, or Sales Territory. Start by using the "Print Report” only option to generate a report and verify the changes before making any adjustments. Once you confirm the report, mark the "Reconcile" option to apply the changes and update the receivables accounts.

This process ensures accurate customer balances and transaction amounts, leading to precise financial reporting and effective customer account management.

Below are two notable options to run this reconcile:

Current Customer Information

Updates current customer balances based on aging period definitions using the Reconcile Date as the "Age as of Date."

Outstanding Document Amounts

Corrects discrepancies in applied amounts or remaining amounts for transactions, ensuring the applied document amounts balance with the current document amounts.

By utilizing the available options, you can ensure your receivables accounts are accurately reconciled, leading to precise financial reporting and effective customer account management.

Reconcile - Remove Sales Documents

The "Reconcile - Remove Sales Documents" utility is a powerful tool found in the path Tools > Utilities > Sales > Reconcile > Remove sales documents. This utility helps update header information on unposted sales orders to match purchase order lines and move completed sales orders to history if the options are marked to keep history.

This utility ensures that header information on unposted sales orders matches the details in the PO lines, resolving issues where sales orders fail to transfer to invoices properly. It also moves completed sales orders from an open status to history if the history option is marked, helping maintain a clean and organized lookup window by removing completed documents from the active list. Before processing normally, use the "Print Report Only" option to preview changes. This allows you to verify the results without making any actual updates.

The window for this utility is somewhat deceptively named "Reconcile - Remove Sales Documents" because it performs two distinct functions:

Reconcile Sales Documents

This process updates the header information to ensure distributions match the totals on the document. It is particularly useful when transferring an order to an invoice is problematic or when batches are not providing the correct information.

Running this utility can often fix these issues, ensuring smoother transaction processes.

Remove Completed Sales Documents

This function shifts completed sales orders from an open status to history. Keeping the option marked to maintain history will help clean up your lookup windows, making it easier to manage active documents.

Note: Utilize the "Print Report Only" option (vs. Running Reconcile) to identify what changes will be made before committing to the reconcile process. This preliminary step can prevent unintended changes and ensure you are fully aware of the utility's impact.

By following these steps and utilizing the "Reconcile - Remove Sales Documents" utility effectively, you can maintain accurate and organized sales order records, ensuring smoother operational processes within your sales management system.

Reconcile Inventory Quantities

The "Reconcile Inventory Quantities" process is located at Tools > Utilities > Inventory > Reconcile. This utility is essential for maintaining accurate inventory levels by verifying and updating current quantities (On Hand, Allocated, Available, etc.) for each site. You can run it for a specific item, a range of items, or all items.

You can reconcile quantities for a specific item, a range of items, or all items. This process ensures that the On Hand, Allocated, and Available quantities are accurate for each site. The utility cannot be run while transactions are being entered and will stop users from entering new transactions during the process, making it best to run after hours. At the end, a detailed report is generated, showing all changes and corrections made during the reconcile process.

Below are a couple of notable module options to consider:

Item Selection

You have the flexibility to select specific items, a range of items, or all items for reconciliation, allowing you to tailor the process to your current needs.

Verify and Update Quantities

The utility verifies and updates quantities, including On Hand, Allocated, and Available amounts, ensuring accuracy across all inventory sites.

Note: The reconciliation process must be run when no transactions are being entered. It will stop users from entering new transactions until completion, so it's best performed after hours. Ensuring all users are logged out helps prevent conflicts and interruptions.

At the end of the process, a detailed report is generated, showing all changes and corrections. This report provides a comprehensive overview of inventory adjustments, ensuring you are fully informed of the updates made.

Other Modules That Have Reconciles That Are Less Used

There are a few other modules with reconcile utilities that are less commonly used. Fixed Assets include some reconcile utilities, but these are mostly for verifying setup records and are rarely needed. Payroll and Human Resources also have reconcile functions, primarily for balancing employee totals for the year or synchronizing HR information with payroll. If you need more information or assistance with these specific reconciles, feel free to reach out to us at Stoneridge Software.

Reconcile to GL: Balancing your Subledgers

The "Reconcile to GL" routine, is found under Tools > Routines > Financial > Reconcile to GL. helps balance your subledgers by providing a spreadsheet of all transactions in the chosen subledger and their corresponding General Ledger (GL) accounts. This tool is essential for ensuring the accuracy of your financial records by reconciling payables, receivables, inventory, and bank reconciliation to the GL.

The "Reconcile to GL" routine allows you to reconcile subledgers, including Payables, Receivables, Inventory, and Bank Reconciliation. You can select multiple GL accounts for reconciliation and choose the date range, typically one month at a time. The tool categorizes transactions into matched, potentially matched, and unmatched, helping you identify discrepancies. However, while it identifies issues, corrections must be made manually. This tool is crucial for ensuring accurate financial records and maintaining a balance between subledgers and General Ledger.

The "Reconcile to GL" setup window allows you to configure the reconciliation process. At the top, the reconciliation number tracks how many times you've run this routine. You specify the date for running the reconcile and the date range for the accounts you are balancing. You then select the module (payables, receivables management, inventory, or bank reconciliation). On the right, the control accounts are displayed based on your system setup, but you can add or remove accounts as needed.

Finally, specify the output file location where the Excel report will be saved, ensuring it's a location you can access, such as your desktop or a shared drive.

The output of the "Reconcile to GL" routine is an Excel file that lists transactions from the receivables subledger on the left and corresponding General Ledger (GL) transactions on the right. While the balances at the top and bottom of the report are not always reliable, the key feature is transaction matching. Unmatched transactions indicate discrepancies, such as subledger entries not hitting the designated GL account. The spreadsheet's blue text fields are hyperlinks that allow you to drill back into GP for a detailed investigation. This tool helps identify if transactions were posted to incorrect GL accounts, in different months, or didn't reach the GL at all. This helps you pinpoint and solve issues.

Note: To correct discrepancies, verify dates, check GL accounts, and ensure transactions are posted correctly you need to manually correct the spreadsheet. This may involve correcting posting dates, ensuring proper use of control accounts, and addressing manual journal entries that bypassed the subledger.

Final Thoughts on Reconciling in Dynamics GP

Proper financial management is crucial for any business, and Microsoft Dynamics GP (GP) provides essential reconciliation tools to maintain data accuracy and integrity. The reconcile utilities in GP help identify and correct discrepancies across various modules, including financial, payables, receivables, purchasing, sales, and inventory. These tools ensure that summary information aligns with detailed transactions, supporting reliable financial records.

Want to Learn More Ways to Optimize Your Dynamics GP Environment?

Get in touch with Stoneridge Software! Our experts are dedicated to supporting you in utilizing all of the great features in Dynamics GP.

Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.

Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.