Reconciling Inventory with the GL

Last Modified on 04/22/2022 4:46 pm EDT

This guide explains how to audit your inventory and reconcile inventory variances. It is intended for advanced users.

aACE tracks data that can help you identify problems in your business. Auditing inventory can both help you confirm accurate numbers and also identify weaknesses in your workflows. We recommend that you audit inventory quarterly.

This is a guide for auditing your inventory values as they compare to the general ledger. The goal is to reach a point where you can reconcile the value of the open inventory lots to the asset value for inventory in GL Accounts. By reconciling inventory with the GL, you will be able to explain any differences between these two values.

Since inventory and GL balances are moving targets, we recommend first conducting a preliminary audit in a backup copy of your system. This allows you to preview the accounting impact of the entries, identify data that needs to be fixed, and so forth. Then you can replicate the entire process in the live system with confidence.

Steps of the Inventory GL Audit Process

1. Review the Underlying Concepts

This guide assumes that you are conversant with the following aACE concepts:

Understand the Quantity Values

Correctly interpreting the audit and resolving issues depends on your understanding of inventory lot, usage, and transaction quantities:

If needed, review the relationships between inventory lot, usage, and transaction records.

Understand Unallocated Inventory Usage

Unallocated inventory usage is when the COGS Reconciliation process cannot find an inventory lot record to associate with an inventory usage record.

If needed, review the details of unallocated inventory usage and of resolving unallocated inventory usage with inventory adjustments.

Understand Accrued COGS

Unallocated inventory usage represents the use of a product that cannot be accurately accounted for as a cost of goods sold (COGS) entry in the GL because an inventory lot cannot be found. However, not accounting for the cost of this usage in some way carries the immediate risk of skewing your financials.

The accrued COGS reconciliation process minimizes the potential impact of unallocated inventory usage by creating temporary entries in the GL for the estimated COGS. These entries are automatically reversed once the true cost is found.

2. Run the COGS Process Twice

Manually run the COGS reconciliation process, entering today’s date for the Limit-To Date. Entering a Limit-To Date causes the process to evaluate every inventory lot record in the system. Depending on the amount of data in your system, this process may take a substantial amount of time to complete. Be sure to plan accordingly.

After the first process is complete, run the COGS reconciliation process again without a Limit-To Date, which should run relatively quickly. This second run without the Limit-To Date helps ensure that any post-dated records are evaluated.

Note: Throughout your audit process, you will also need to re-run the COGS reconciliation process any time you create or adjust inventory transactions. These ad-hoc reconciliations do not need to have the Limit-To Date set, so they should run quickly.

3. Resolve Unallocated Inventory Usage

Review the Inventory Usage module to find and resolve any unallocated inventory usage. Remember that unallocated usage is a symptom of an underlying problem with your business processes or data. Be sure to identify and correct the root causes.

Note: If this step required you to create or adjust any inventory transactions, re-run the COGS reconciliation process.

4. Eliminate Accrued COGS

After you resolve unallocated inventory usage, it may be necessary to zero out accrued COGS entries in your system (if any).

To determine whether your system has accrued COGS entries, navigate to the General Journal module, set the Type search field to "Accrued COGS", and click the Search icon ().

If the search shows any Pending entries, delete them. If there are any Posted entries, manually run the accrued COGS reconciliation process with the Limit-To Date left blank. (As noted above, leaving this date blank focuses on records that have changed since the last time the process was run, resulting in a quicker process.) This reverses any accrued COGS entries associated with the previously unallocated inventory usage you resolved in Step #3.

If there are no accrued COGS entries in your system, proceed to Step 5.

5. Reconcile Inventory Lots and GL Inventory Account(s)

Your goal with this audit process, as mentioned earlier, is to explain the variance between the value of the open inventory lots and the asset value for inventory in your GL accounts. The overall strategy is to identify products that have the greatest variance (with Step #5) and then resolve those issues (with Step #6). Then we will repeat these two steps for additional products. This iterative approach is helpful because correcting the issues for one product sometimes resolves other variances.

You can easily identify products that have variance by generating the Inventory Lot / GL Reconciliation Report.

Understanding the Inventory Lot / GL Reconciliation Report

This report compares the inventory lot value for each product listed with the corresponding values in the GL, presenting the results in five columns. The following screenshot shows that the French textbooks have some variances. The total values are displayed on the last page of the report:

Understanding what the columns of this report represent can help you interpret it correctly.

Lot Value Column

This column displays the sum of ~Current Values in each listed product's open inventory lots.

To manually calculate this total value, navigate to the Inventory Lots module and click the footer link to Find Open Inventory Lots. The total value is displayed at the bottom of the list. This screenshot shows that the module also has the total value for open inventory lots as $5,290.90.

You can also manually calculate the inventory lot value for a single product — in the Inventory Lots module, search the Line Item Code field for the LIC's code:

GL Value Column

This column displays the sum of the asset entries for the product in the GL (excluding accrued COGS entries, which function as temporary placeholders).

To manually calculate this total value, navigate to the GL Accounts module and locate the primary inventory account (e.g. Inventory Assets). This screenshot shows that the module also has the total balance in the Inventory Assets account as $3,904.29.

You can also manually calculate the asset GL Value for a single product — in the General Ledger module, search the Account field for "1*" and the Line Item Code field for the LIC's code. Searching for "1*" (see screenshot below) uses the asterisk is an operator that represents any number of additional characters. This search will display all GL accounts that begin with a '1', which is the asset series:

If your system has accrued COGS features enabled, you must exclude any accrued COGS entries in order to get an accurate value. For small data sets, you can manually remove the transactions associated with accrued COGS. For larger data sets, it is easier to use an Advanced Search query using these three criteria:

General Ledger section, General Info Notes field — "Accrued COGS"
Matching Records toggle — Omit
FM Menu Bar — Search > Constrain Found Set

Variance & True GL Var Columns

These two columns are calculated for each inventoried item using separate formulas:

Variance: Inventory Lot ~Curr Value - GL value
Note: ~Curr Value = inventory lot Curr Qty * inventory lot unit cost
True GL Var: Purchased/Produced Value + Accrued Value - COGS Value

This slight difference in calculations is designed to highlight variances resulting from the unit cost calculation for open POs. For more details on how this calculation can affect variances, see Step #6 Example #1.

Note that a value in the Variance column might not signify an error — a PO may need to stay open for additional purchasing or receiving activity, as illustrated in the aforementioned example.

In contrast, a value in the True GL Var column always signifies an error, since the values used for this calculation (in the inventory lot) should have equivalent values in the general ledger.

Accrued COGS Column

This column displays the sum of accrued COGS entries (if any) associated with the product. Because in Step #4 of this process you reversed accrued COGS entries, this column should typically be zero.

Generate the Inventory Lot / GL Reconciliation Report

When you run this report from the Line Item Codes module list view, it generates results for the items currently shown in the list. This enables you to generate quicker reports on smaller found sets. However, be sure your found set includes both active and inactive versions of the desired LICs because inactive items may still be affecting the GL balances. You can also run the report from the detail view to inspect variances for a single item.

Locate the desired found set of LICs.
Note: If you are conducting a full audit, click the footer link to Find Inventoried Items.
In the menu bar, click Print > Open Report Selector > Inventory Lot / GL Reconciliation by Type, Code.
From the Omit Variance dialog, enter the amount of variance that can be ignored, then click OK. (See below for details.)
Enter a title for the report and click Continue.

The Omit Variance dialog allows you to constrain the report to products with a total variance greater than the Omit Variance value. It enables you to exclude products with small differences between the inventory lot value and the GL inventory account value (e.g. variances of less than a dollar, which are often due to rounding differences). This helps focus attention on problem records. (Note: If you leave the Omit Variance field blank, the report will include all products in the current found set.)

We recommend initially running the report with an Omit Variance value of “1” to exclude products with a variance less than $1.00. If using "1" yields too many records to work with effectively, you can re-run the report with a larger Omit Variance value. This will winnow the list to a smaller number of products, which you can then audit one-by-one.

For each product with a meaningful variance, conduct an audit using the instructions in Step #6 below. As you resolve the variance with one product, it may resolve others. We recommend starting with products that have a higher variance value since larger errors are often easier to identify and are more likely to have a significant impact on your financials.

6. Reconcile Products with Variances

To help audit a specific inventoried item, aACE provides the Detailed Inventory Lot Reconciliation Report. This report helps clarify the cause of inventory problems because the product-level discrepancy you are looking for often arises from one of these four sources of error:

COGS — The cost of goods sold in the open inventory lots is at variance with the GL.
Pur/Prd Value — The purchased/produced value in the open inventory lots is at variance with the GL.
Accrued Value — The accrued value in the open inventory lots is at variance with the GL.
Act/Rcvd Value — The purchased/produced value in the open inventory lots is at variance with the value of inventory received.

Generate the Detailed Inventory Lot Reconciliation Report

From the Inventory Lots module list view, locate all the inventory lot records for a single product.
In the menu bar, click Print > Open Report Selector > Detailed Inventory Lot Reconciliation Report.
Enter a title for the report and click Continue.

This report highlights variances in inventory lots. You can often identify the cause of an error by examining the related transactions. If you identify and resolve an error, you should also implement a long-term solution to prevent future problems.

Examples of Resolving Variances

Many variances are easy to identify and resolve by reviewing the related records. When reviewing transactions, it's helpful to ask the following questions:

Are there missing records, such as incoming shipments that weren't entered?
Are there incomplete transactions, such as open purchase orders that should have been closed?
Are there erroneous transactions, such as Beginning Balance adjustments that credit an inventory account?
Are there incorrect configurations, such as LICs with GL cost accounts specified in the Inventory Account field?

One type of problem will often affect multiple records/transactions and cause multiple variances. Once you identify and correct the first instance, you should review your system for additional records or transactions with the same problem.

Example 1: Act/Rcvd Value Variance — Act vs Est Unit Cost from Open PO

Summary

The most common source of variance in the Act/Rcvd Value column arises from a difference between the estimated unit cost in the PO and the actual unit cost in the related purchase(s). While the PO is open, an inventory lot's ~Current Value is calculated using the PO's estimated unit cost, but if the inventory is purchased at a different unit cost, it creates a temporary variance. When the PO is closed, the inventory lot's value is recalculated using the actual purchase amount and the variance disappears.

Example Situation

Suppose aACME Education created a PO for 50 textbooks with a unit cost of $25.99. This creates an inventory lot record:

However, the vendor has trouble filling the order and is only able to ship 48 textbooks at a slightly higher cost of $27.50. When the purchase is recorded and the partial shipment is received, aACE updates the inventory lot. But the PO remains open because POs with any variance between the expected and actual quantities do not auto-close. With the PO still open, the initial estimated unit cost (in bold) is still used. The note displayed in the lower-right also highlights this detail:

Audit Reports Highlighting This Variance

The Inventory Lot / GL Reconciliation Report shows the variance — the GL value is $72.48 higher than the inventory lot value. In contrast, the True GL Var column is $0.00 because the purchase value matches the GL value.

The Detailed Inventory Lot Reconciliation Report also shows the $72.48 variance. The report also includes a note adjacent to the PO transaction reference (e.g. "PO-50004") that the estimated unit cost is being used. This helps you quickly identify instances where the cause of the variance could be an open PO:

Immediate Resolution

Closing the PO will resolve this variance. This updates the inventory lot value to use the cost recorded in the PO's purchase(s), which matches the GL's value. The inventory lot now highlights the Purchased cost in bold as the amount being used for calculations, and the note regarding the estimated unit cost is gone:

Expanded Resolution

After you find one PO that should have been closed, you should search for other POs that can and should be closed. Identifying and closing these records may help resolve other variances.

Note: You might not be able to eliminate some variance values. POs causing this type of variance may need to stay open because additional purchasing or receiving activity needs to happen.

Long-Term Solution

The final step for this example might be to assign a team member to periodically review POs such as these and close them out.

Example 2: Pur/Prd Value Variance — Incorrect GL Account Assigned to LIC

Summary

For inventoried items, the LIC record should be configured with a Cost Account from a "5-" or "6-" series account, while the Inventory Account should have a "1-" series asset account. This helps ensure that the GL properly reflects inventory values. Mis-configuring an LIC will result in reconciliation errors.

Example Situation

Suppose that an LIC for one of aACME Education's textbooks had the Inventory Account mistakenly set to the "5001" Cost of Sales account:

When a purchase is entered for this item, the Cost account is debited instead of an inventory account. This results in no increase in the GL's inventory value:

Audit Reports

The Inventory Lot / GL Reconciliation Report shows a variance in the True GL Var column:

The Detailed Inventory Lot Reconciliation Report narrows down the possible causes for the variance, identifying that it's arising from the purchase value:

Additional Research

Drilling down to the PO and purchase record, we observe that the GL account from the purchase was not an inventory account:

We can also verify this erroneous configuration from the GL transactions for the purchase:

In the above screenshot, the debit should have been to an inventory account rather than a cost account. These details show that the cause of the variance is 'prior' to the PO, in the LIC record where the incorrect GL account was set for the Inventory Account field.

Immediate Resolution

The first step for resolving this incorrect configuration is to edit the LIC record, specifying an appropriate Inventory Account. This ensures that subsequent purchases of the product route the funds to the correct accounts.

The purchase causing the variance is still on the books though. To correct this, you must reassign the GL account for the posted transaction.

Extended Resolution

After you find one LIC that has an incorrect Inventory Account, you should review all LIC records and verify that they are associated with the correct inventory GL accounts. From the Line Item Codes module list view, click the footer link to Find Inventoried Items, then click Sort Options () > Inventory Account. This will display all inventoried LICs grouped according to the GL account specified in the Inventory Account field.

Fix each incorrect configuration as noted in the Immediate Resolution above.

Long-Term Solution

The final step for this example might be to train the team members who create LICs, helping them understand which accounts are appropriate for the Inventory Account field.

Reconcile the Next Product with Variances

After you resolve the variance issue with one product, repeat Steps 5 and 6 until all variances have been addressed.

After you have resolved as many variances as possible, if there are remaining issues where the cause and solution are not clear, contact your system administrator.

7. Reverse Manual Entries

After resolving discrepancies arising from your inventoried products, you can be confident that the GL is accurate on a product-by-product basis. As mentioned previously, the inventory audit's goal is for inventory GL accounts to match the GL Value column in the Inventory / Lot GL Reconciliation Report.

The next step is to make sure the inventory GL value isn't being affected by entries that aren't associated with inventoried products (e.g. manual entries to inventory accounts). In other words, all entries in your inventory GL accounts should be linked to inventoried LICs. Therefore, we need to double-check these entries and make sure they have been handled correctly:

Identify Manual Entries in Inventory GL Accounts

Navigate from Main Menu > Accounting > General Ledger.
At the list view, find all GL entries belonging to inventory-series accounts.
Note: Inventory accounts are typically set up with a consistent 2- or 3-digit prefix (e.g. "13-"). Searching the Account column for this prefix and the asterisk wildcard (e.g. "13*") will find all the inventory-series entries.
In the footer, click the link to Omit Inventoried Items, then sub-summarize by Account.

This yields a list of GL entries which did not appear in the Inventory / Lot GL Reconciliation Report from Step #5. The net balances must all be reversed:

Reverse Manual Entries in Inventory GL Accounts

Navigate from Main Menu > Accounting > General Journal.
In the menu bar, click New > Journal Entry.
Enter a relevant title (e.g. "Inventory Audit — Reverse Manual Entries").
In the Entry Items list, create the off-setting entries:
- For each GL account you identified above, enter a reversing entry for the same amount. (If the GL included entries with multiple values in the Office column, reverse them with a separate entry item for each GL Account / Office combination.)
- For the total value of these reversing entries, enter a balancing entry. (If you are unsure of which account to use, inspect the initial manual entries and use the account specified there.)
Click Save.

Example of Reversing Manual Entries

Suppose that after an inventory count was completed for June, a user tried to adjust inventory downward by manually crediting certain inventory accounts, and a similar manual entry was created after the July inventory count.

During the August inventory audit, these manual entries would cause credit balances not assigned to inventoried products to show up in the GL's inventory accounts. These values would not appear on the Inventory / Lot GL Reconciliation Report in Step #5. This screenshot shows $27,500.00 in inventory accounts, but not associated with an inventoried LIC:

To resolve this situation, the user conducting the inventory audit would create the following off-setting general journal entry:

This would help ensure that the inventory GL accounts match the GL Value column in the Inventory Lot / GL Reconciliation Report. Checking a second time for manual entries in the inventory GL accounts would show that the net balances are zeroed:

8. Resolve Incorrectly Assigned Asset Accounts

The next step in reconciling the inventory lot value and the GL inventory value is to confirm that no GL entries are assigned to incorrect accounts. For example, sometimes inventoried products are assigned to asset accounts that are not inventory accounts.

Navigate from Main Menu > Accounting > General Ledger.
In the footer, click Show All, then in the Account search field, search for "1*" to limit the list to asset accounts.
In the footer, click Constrain to Inventoried Items, then sub-summarize by Account.
Note: You can toggle the sorting order between ascending / descending by clicking the Account column heading. This quickly displays the GL Accounts with the lowest and the highest ID codes.
Confirm that either of the following details is true for each item in the list:
- The item's assignment to an inventory account is correct.
  OR
- The item is assigned to a non-inventory or an old inventory account which sums to zero.

If you find errors, correct them as explained above in Step #6, Example #2. To review, the immediate need is to correct the related LIC's configuration, preventing the problem from getting worse. (Note: You can quickly display the LICs using the footer links: Related Records > Line Item Codes.) The next step is to reassign the GL account for any posted transactions you have discovered. The long-term solution might be to train team members who create LICs to understand which accounts are appropriate for the Inventory Account field.

After you have resolved as many issues as possible, if there are remaining problems with COGS entries, contact your system administrator.

9. Confirm Results

From the Line Item Codes module, click the footer link to Find Inventoried Items, re-run the Inventory Lot / GL Reconciliation Report (with zero Omit Variance), then review the totals on the last page:

The preceding screenshot shows a Lot Value and GL Value that are fairly close. The Variance column highlights the small difference, which might be coming from open POs that still have remaining receiving or purchasing activity. (More research would be needed to verify this; however, the True GL Var column showing zero variance makes this explanation seem likely.) The Accrued COGS column also shows a small value (see below for details).

Visual Reconciliation

We can also manually compare the total value for open inventory lot and the total value for inventory GL accounts.

To manually calculate the inventory lots value, navigate to the Inventory Lots module, click the footer link to Find Open Inventory Lots, then scroll down to find the total ~Curr Value at the bottom of the list:

To manually calculate the inventory GL accounts value, navigate to the GL Accounts module and locate the inventory accounts:

Impact of Accrued COGS

Ideally, you won't have any accrued COGS values at this point (due to eliminating these entries in Step #4). If the Inventory Lot / GL Reconciliation Report does show accrued COGS values, make sure that when you visually reconcile your system, you add those values to the GL Value (i.e. positive accrued COGS increasing the GL Value; negative accrued COGS decreasing that value).

Conclusion

Once again, our goal in auditing inventory is to reconcile / explain any differences that affect this equation:

Total value of open inventory lots = Total value of inventory GL accounts – Variances – Total value of accrued COGS (if any)

Using the values from the preceding three screenshots, the equation would be:

1,069,926.28 (Lot Value) = 1,070,853.19 (GL Value) – 926.91 (Variance) – 280 (Accr'd COGS)

The variances can be explained as open POs with remaining activity to conclude and as accrued COGS values.

Thus, we can conclude that the example system is reconciled.

Additional Inventory Auditing

The system cannot be considered reconciled if:

The total value for open inventory lots and the total value for inventory GL accounts are not within an acceptable margin.
The True GL Var is not zero (or within an acceptable margin of error).

You should continue the audit process, repeating the steps in this guide.

If additional cycles through the audit process on your backup system do not help you resolve all errors, contact your system administrator for assistance. Please be able to provide a copy of your system and detailed information about the remaining errors.

How helpful was this guide?