Understanding the Unit Cost Logic in the COGS Reconciliation Process

The unit cost value used by the COGS reconciliation process is affected by several factors. Understanding these details can help explain the results you see on various reports.

Open & Closed Purchase Orders

The status of the related PO affects the unit cost of an inventory lot due to the accrued inventory functionality. 

To maximize accuracy, aACE uses the Accrued Inventory account to manage the balancing credit for goods that are received, but not yet purchased. These accrued inventory calculations must use the related PO's Unit Cost, since there might not be a purchased value recorded yet or because the purchased value may change. Because the accrued inventory entries represent the value of the inventory lot on the GL, the accrued inventory and the inventory lot values must match. Therefore, the accrued inventory entry and the inventory lot calculations use the same unit cost.

When a PO is closed, any accruals generated by the PO are reversed. We can then determine the actual unit cost using this calculation:

The following screenshot shows an inventory lot while the related PO is open. The inventory lot's ~Current Value is calculated using the PO's Unit Cost, as noted by the bold highlighting in the Ordered / Purchased section, as well as the italicized note in the Inventory COGS section. This results in a total lot value of 20 * $699.00 = $13,980.00.

The next screenshot shows the unit cost change when the related purchase is opened and the freight charges are included. The inventory lot's Current Value is then calculated using the actual cost from the purchase, as noted by the changed bold highlighting in the Ordered / Purchased section (and the italicized note being removed). This results in an updated total lot value of 20 * $724.00 = $14,480.00:

While the PO for this product is open, the inventory lot value will be calculated using the PO's estimated cost (i.e. a total of $13,980.00); however, the value in the GL will still be calculated with the purchase cost (i.e. a total of $14,480.00). The difference between these costs ($11.25) will display as a variance on the Detailed Inventory Lot Reconciliation Report used for reconciling inventory with the GL (see below):

When the PO is closed, this variance will be eliminated.

Impact of Unit Cost Changes on COGS Entries

Changes to unit cost values can result in multiple COGS entries for the related inventory lots. For example, an open PO uses the estimated unit cost and a closed PO uses the actual unit cost (as described above). If these two costs are different, the COGS entries while the PO is open will show the estimated unit cost. When the PO is closed, the COGS entries will be updated to show the actual unit cost and aACE will generate another COGS entry to account for the difference.

Suppose a customer buys units of a certain product. The goods are sourced from an inventory lot associated with a PO that is open. When the COGS reconciliation process runs, it generates an entry calculated using the estimated unit cost: 5 * $1.00 = $5.00.

When the related PO is closed, the actual cost is used to re-calculate the COGS entry: 5 * $1.497 = $7.49.

When the COGS reconciliation process runs again, it generates an entry for the difference between the initial and the updated Total Cost (i.e. $7.49 – $5.00 = $2.49). In the general ledger, you can review the two COGS entries: one for the initial COGS value ($5.00) and another for the difference ($2.49): 

Rounding Logic

Unit costs for products in aACE can extend up to six decimal places; however, general ledger (GL) entries are limited to two decimal places. When unit costs with extended decimal places are used in calculations that end up on the GL, they must be rounded to the second decimal place. By default, aACE rounds up. This can cause slight variations between inventory lot values and inventory GL account values.

Example of Rounded Calculations

Suppose a product had a unit cost of $1.035. Purchasing ten units would generate an inventory lot valued at $10.35, as shown in this screenshot:

If we sold all ten units at one time, the COGS value would match the inventory value:

However, with a similar inventory lot (i.e. 10 units at $1.035 each), if we sold the units one by one, each order would calculate at 1 * 1.035 for a rounded price of $1.04, as shown in this screenshot:

In the preceding screenshot, the total in the Inventory COGS section (highlighted with the purple oval) is calculated using Quantity Purchased * Unit Cost (10 * 1.035 = $10.35). This is not the sum of the COGS entries (highlighted with the green oval). Manually totaling the COGS entries (10 * $1.04) gives a total COGS value of $10.40, which is a .05 variance from the inventory lot value. This .05 variance displays on the Detailed Inventory Lot Reconciliation Report:

When auditing inventory, you can generally prevent these small rounding differences from displaying on the report by setting the Omit Variance value to a small value (i.e. $1.00).

Manually Eliminating Rounding Variances

Rounding variances are not errors, per se. They are a natural result of calculations using numbers with more than two decimal places. 

However, you can use a general journal entry to remove rounding variances if needed (e.g. to generate a Detailed Inventory Lot Reconciliation report that shows zero variances or if the total value of the rounding variance reaches a level that necessitates it).

Note: Only take this step if you are certain the variance is arising from rounding issues.

This GJ entry should include: 

1. An entry item that credits the LIC's cost-of-sales account, increasing the account by the rounding variance value (i.e. so the GL Value will match the Lot Value on the report)
2. A balancing entry item that debits the LIC's inventory account

Be sure to specify the LIC on each line of the GJ entry and include descriptive notes about the change.