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.