Skip to content

Avoiding inventory integrity errors

Adapted from the Leahy Consulting handout "How can I avoid Inventory Integrity Errors?", part of the "Supercharge your Macola® ERP software" workshop series, 2014 and 2018.

It is not unusual for Macola® inventory to get out of balance with the accounting records, and we take the call regularly. Start from this: the Macola® software is self-balancing. It cannot get out of balance unless an error is made. Balancing problems are also rarely one error — several types usually stack up together.

Know which two reports you are balancing

When we talk about reconciling the general ledger and inventory, we mean balancing the G/L Trial Balance report to the I/M Frozen Stock Status report. Balancing anything else to anything else will produce differences that are not errors.

Inventory Management

1. Keying receipts, issues and adjustments on the manual transaction screen

Using the manual transaction screen usually creates problems. If the other Macola® programs are being used properly this screen should rarely, if ever, be needed — on-hand adjustments belong in the Physical Count and Cycle Count programs.

What to do. Avoid manual inventory transactions. Fix errors where they occurred instead — an O/E error belongs in an O/E credit memo. This is also not the place to key receiving or issue transactions.

2. Not knowing what the Frozen Stock Status total actually contains

Macola®'s stock status reports print every item at every location in the item/location file at that moment, multiplying on-hand quantity by unit cost — average or standard, depending on your inventory method. The report does not distinguish items that were expensed when purchased from items that were inventoried. Factory supplies, office supplies and tooling that were expensed on purchase still appear if you keep an on-hand quantity for them, and they have to be excluded before balancing to the general ledger.

3. Manually changing unit cost or on-hand quantity in the item/location file

Manual changes to these fields are not recorded in the Inventory Distribution file, so the G/L cannot follow them.

What to do. Never change cost or on-hand quantity in the item/location file. Turn on the I/M setup option Lock Protected Fields? and the possibility disappears.

4. Assigning the same item number to two different parts

Some manufacturers use one part number before and after plating, anodizing, heat-treating or machining. Part 123 is purchased for $3.00; the same number is used after $0.25 of heat treating, when the part is worth $3.25. The fit-and- function rule says these are two different parts.

What to do. Create one item number for the purchased item and another for the plated, machined or anodized item.

5. Mishandling negative on-hand quantities

Some companies ignore negative on-hand quantities on the stock status report, or force them to zero. The cost of those items has already been consumed into the financial statements, so the quantity cannot simply be zeroed.

What to do. Do not force the on-hand quantity to zero.

6. Assigning the wrong material cost type in the Item Master

An incorrect material cost type can charge an expense item to an inventory account, or expense an item that should have been inventoried.

7. Not performing an accurate physical inventory

If the last physical inventory was inaccurate, the beginning balance is inaccurate, and monthly reconciling stays difficult until the next one.

Purchase Orders and Receiving

8. Receiving at an incorrect or zero unit cost

Easy to do the first time an item is ordered: the purchase order is issued with no cost, intending to fix it before receiving, and then it is received at zero.

What to do. Never add a new purchased item to the item master without a unit cost — a phone call to the vendor gives you at least an estimate. If an item was received at zero cost and is still in inventory, reverse the entry immediately with a negative receipt, correct the purchase order cost, and re-receive it.

Customer Order Entry and Invoicing

9. Posting customer invoices at an incorrect or zero unit cost

Companies sometimes take orders for a new product before production starts and before the cost exists in the item/location file. Every order keyed during that window carries a zero cost, so cost of sales is zero even though the on-hand quantity dropped.

What to do. Never key a finished goods item into the item master and item/location file without a unit cost — if you plan to make it, the bill of material is known and the Costed Bill of Material report establishes the cost. Also change the O/E Setup screen to calculate cost of sales from the unit cost at the time of invoice posting rather than when the order was keyed.

Production Orders

10. Issuing production orders whose bills of material are wrong

Production workers pick components from experience rather than from the picking ticket. If the ticket is wrong, the right components are still consumed, but the backflushed components and the resulting unit cost are wrong and understated.

What to do. Require production and engineering to sign off on new and modified bills of material. Train production personnel to review picking tickets and production orders for errors and tell their supervisor. Errors of this kind surface in the next cycle count or physical count anyway — the question is how long that takes.

General Ledger

11. Using incorrect G/L accounts in the Material Cost Type/Location file

Inventory items can end up assigned to expense accounts, or the reverse.

What to do. Print the Material Cost Type/Location Account file list and review every account number. See The set-up screens that hold G/L accounts.

12. Assigning two opposite entries to the same account

If quantity adjustments are assigned to the inventory account, both the debit and the credit go to the same account and the adjustment vanishes.

What to do. Print the I/M Material Cost Type/Location report and review each setup.

13. Incorrect journal entries, or forgetting to reverse monthly accruals

What to do. Avoid journal entries directly to your main inventory account. Set up a separate account for adjustments.

14. Forgetting to post I/M transactions from the subledger to the G/L

This happens when inventory transactions are posted into a month that has already been closed.

What to do. Print the I/M to G/L Distribution report for a date range before the current month and confirm everything has posted.

15. Keying manual journal entries and forgetting to post them

Reports that find these errors

We have developed custom Crystal reports that can be printed each month or each day to pinpoint these errors:

  • Inventory transactions at zero cost
  • Inventory transactions at the incorrect cost
  • Inventory transactions over a dollar threshold, which pinpoints high-value transactions that may be errors
  • Inventory stock status by material cost type — the material cost type says whether the item was expensed or inventoried
  • Non-inventory items in the item master file
  • Inventory Transaction Activity, which shows how and when an item's on-hand quantity became zero
  • Inventory items not counted in the last inventory
  • P/O receipts with zero or incorrect unit costs
  • Customer sales at incorrect or zero cost
  • G/L journal entries affecting inventory

Tip

Next step. Most of these checks are standing reports rather than one-off investigations. The Inventory module of Pulse Dashboard runs the exception views nightly against live Macola® data, and the Designer module will embed the Crystal reports above alongside them. Call (513) 723-8091.

Support & contact

Our team is glad to help with anything from a quick question to a full implementation.