When Things Don't Look Right in Cost of Goods Sold, Check Your Inventory - Part 2

This is a follow-up on my first post [When things don’t look right in cost of goods sold, check your inventory] , regarding my engagement with an office supply house with an overstate cost of goods sold and an understated inventory balance. This has been like untying a Chinese knot but I finally see daylight at the end of the tunnel

This has been a difficult engagement because of poor audit trails between the point of sale application and QuickBooks. For non-inventory sales, the POS application provides no means to link cost of a sales to the revenue side of the transaction in QuickBooks. In addition, the cost of goods side of the transaction often takes place in the following month as vendor invoices came in. Here is how I sorted this out…

I quickly established that the negative inventory balance was due to a series of audit adjustments, the offset of which was… the inventory account. The effect was that inventory was increased or decreased at an item level, with the offset being to the same inventory account at a summary level. If these adjustments have been properly posted to an inventory gains and losses account, the credit would would have been in excess of $200,000, which didn’t make any sense.

I then focused my efforts on testing the sales from inventory, where the POS provided a complete transaction for the sale, including the credit to sales, debit to a receiveable, credits to specific inventory items, and item level debits to cost of goods sold. I was able to tie these transactions in on a quantity level. There was a slight valuation difference because the POS uses a LIFO inventory valuation method while QuickBooks uses the average cost method. Therefore, inventory sales was excluded as the culprit.

This left me focusing on the weakest control point, the cost of non-inventory goods sold. I discovered a relationship between the records in QuickBooks and a POS report that would allow me to link transactions at a sales item level. Unfortunately, this required an item level match of 19,000 QuickBooks transaction lines and 9,000 POS transaction lines per month. To accomplish this, I exported reports from both QuickBooks and the POS application to Excel and wrote VBA code to based a complex set of matching rules.

It was in this process that I made I made my breakthrough. Early on, I had found a single vendor being paid an average $50,000 per month and not one of the bills was itemized; each contained a single debit to cost of goods sold. I had questioned these entries but was told they covered non-inventory sales only and that the number of items was too great to enter in detail.

However, in my matching process I matched both the POS to QuickBooks and QuickBooks to the POS to see reconciling differences on both sides. I discovered a large number of entries for this vendor on the POS side that were not recorded in the QuickBooks non-inventory sales. I ran a match for these POS entries against QuickBooks inventory sales and discovered most of them matched. Therefore, we DID have purchases from our “non-inventory” vendor for inventory items.

I checked all entries for inventory purchases and found no entries for this vendor. What was occurring was that, upon each sale from inventory, the POS system was generating as credit to inventory and debit to cost of good sold, and the payables clerk, upon receipt of the vendor bill was also debiting cost of goods sold for the part of the vendor bill that was for inventory purchases.

Each vendor bill should have been divided, on the expense side of the distribution with a single charge to cost of goods sold for the non-inventory items and, on the item side of the distribution, with an item level entry for inventory purchases. Hence, we overstated COGS and understated inventory. Problem solved? Unfortunately not.

We still have to deal with the inventory adjustments. It turns out that the payables clerk had instructed the inventory clerk to enter the inventory receipts for our problem vendor as an inventory adjustment. She did as instructed, increasing inventory by the items flagged on the vendor bill by the payables clerk, What was the offset? The inventory account, of course. This means we still have to go through about twenty four “inventory adjustments” and figure out what to do with them. I think the theory was that the offset to the inventory adjustments for the problem vendor would result in a debit to inventory at an item level and a credit to COGS to partially offset the debit made by the payables clerk. I’ll be explaining how and when inventory adjustments are used.

I have to say, this has been an incredibly frustrating project, with missing audit trails and “dead ends” at every turn. I could not have solved this without using Excel and VBA code. Fortunately, both QuickBooks and the POS application could export to Excel. Thanks for VBA, Microsoft.

Robert Guild is certified QuickBooks ProAdvisor in Austin, TX who conducts CPE courses for CPAs and individual training and group classes to QuickBooks users. His company at www.QBCoach.biz, maintains a sixteen-station QuickBooks lab, providing hands-on training. You can contact him directly at rguild@QBCoach.biz or follow him on twitter at QBPro