A common problem for many inventory-based businesses is recording receipt of inventory before that inventory is billed to a customer. In retail settings, pressure to promptly take care of customers can lead to pressure to produce an invoice that hasn’t yet been received. Since producing an invoice is often a necessary first step to getting paid, many firms prioritize invoicing over properly receiving inventory. When invoicing results in inventory levels going negative, the results are inaccurate financial reports.
To see how, let’s start with the sample data included with QuickBooks for a products-based business, Castle Rock Construction. First, let’s review the firm’s Balance Sheet and Profit & Loss before we make any transactions. We start with a total inventory value of $30,121.33. Our starting cost of goods sold is $3,610.50. Castle Rock has incurred costs of $10,211.23 for customer Kathy Abercrombie, the customer we plan to invoice. Next, let’s add a new inventory item called “COGS Test” with an opening balance of 5 units costing $5 each. QuickBooks debits inventory for $25 and automatically posts an offsetting credit of $25 to Opening Balance Equity; both entries are marked as reconciled. Our opening balances before creating the new inventory item are shown below.
At the time of the sale, QuickBooks had information on the average cost to that point in time. That cost was $5.00. But what if our cost for the item had changed and was now something other than $5.00? We can imagine that if we had to scramble to get this item for the Abercrombie order, we faced higher costs. Let’s enter a bill for 20 units dated after the date of the Abercrombie invoice. The date is very important, as we’ll soon see. The 20 units we received cost $7.50 each, or 50% more than our normal cost. Here’s our Balance Sheet and Profit & Loss after entering the vendor receipt.
By letting inventory reach negative levels, QuickBooks accounted for the transaction as if we sold 5 units that cost $5 each and 5 units that cost $7.50. In effect, even though QuickBooks uses the average cost method, this transaction was treated on a First In First Out (FIFO) basis.1 A look at the Transaction Journal for the bill confirms this. Once we entered the new inventory at higher cost, QuickBooks automatically applied the extra cost of $2.50 per unit to the 5 units that we did not have in stock. Our cost of goods sold is now $3,673.00, an increase of $62.50. Our total costs for customer Abercrombie are $10,261.23, and our inventory value is $30,233.83.
We obtain totally different results if we record the bill for receiving inventory before invoicing the customer. Our cost of goods sold in this case is $3,680.50, or $7.50 higher than reported by allowing inventory to reach negative levels. Our inventory value is $30,226.33, or $62.50 higher than reported when we recorded the invoice before the bill. Our costs for customer Abercrombie are now $70 higher than when we started.
All of these differences are due to the fact that by entering an invoice before a bill, we prevented QuickBooks from correctly calculating the average cost for our new inventory item. After all, we can’t expect QuickBooks to calculate average cost before we’ve entered that cost. By first entering the bill for inventory receipt and then recording the invoice, we stick to our inventory cost assumption. We started with 5 units costing $5 each, and we added 20 units costing $7.50. That produced an average cost of $7 for each of our 20 units. The results reported by entering transactions in the proper sequence are consistent with that average cost.
| Account | Invoice Before Bill ($) | Invoice After Bill ($) | Difference ($) |
|---|---|---|---|
| Cost of goods sold | 3,673.00 | 3,680.50 | 7.50 |
| Customer costs | 10,261.23 | 10,281.23 | 20.00 |
| Inventory valuation | 30,233.83 | 30,226.33 | 7.50 |
| Item average cost | 7.50 | 7.00 | 0.50 |
The bottom line: when customer invoices are entered before vendor bills, your financial reports will be inaccurate. In our example, overall cost of goods sold, customer costs, and inventory were under-reported. That resulted in overstating income. Our average cost for the new item was reported as $7.50, rather than the $7.00 obtained by applying the average cost method consistently. We built our example around a discussion of this problem in the QuickBooks knowledge base.
If you’ve already made the mistake of entering invoices before bills that have resulted in negative inventory levels, we can help get your financial reporting back on an accurate track.
(+3 rating, 3 votes)
Loading...
- In August 2008, the SEC announced that some companies can report under International Financial Reporting Standards (IFRS) by 2010 and that all companies will be required to do so by 2014. IFRS, unlike US GAAP, requires FIFO costing where individual items are not identifiable. [↩]
Very useful article. I plan on passing it along to several of my manufacturing and retail clients in Seattle that I help support and train in QuickBooks as an Advanced Certified QuickBooks ProAdvisor.
Wouldn’t there be an opposite issue doing this the other way as well though? If you already have 5 of the 10 items in stock and then you buy 5 more at a higher price the system would think that all 10 were bought at the higher price at the time of the invoice by waiting to create it right? So in fact you would be making more profit than the reports state.
10 @ $7.50 = $75.00
when in reality the actual COGS is
5 @ $5 and 5 @ $7.50 = $62.50
That’s a difference of $12.50 that actually went into your pocket.
Your notes are extremly detailed and helpful. Thank you. We have always done physical inventory count at the end of the year, but never adjust the value. Which lead to difference in amounts between Inventory Asset and inventory value.
How do I reverse this then?
Tracy, unfortunately, there’s no “one solution fits all” cure, and some of the cures would occupy entire articles on their own. The first step is to do a physical inventory and compare that to your QuickBooks inventory so you can identify the number of items with negative inventory. Then, try to get a sense of how frequently those items went negative by running reports with different date ranges. The number of problem items and the frequency with which they went negative will influence how you should proceed. Here’s a brief outline of several methods.
If both the number of negative inventory items and the number of instances they went negative are small, you can identify the vendor receipts and change the dates on those transactions to a date before your customer invoices. Generally speaking, this is the Intuit-recommended solution, but it has these drawbacks: if you change the dates on vendor receipts/bills, you’ll be changing your AP due dates and your financial statements. You may not want to do that. Also, this approach may simply be impractical if the number of affected items or transactions is large. You’ll need to re-run inventory reports to make sure that fixed the problem.
The second approach involves adjusting the quantity and value of each inventory item so that your physical count matches your QuickBooks valuation inventory report. Make any necessary quantity adjustments, and record any $ amounts as value adjustments. It’s a good idea to record these transactions in a new GL expense account (something like “2008 EOY inventory cleanup”) so you can track the impact of what you’re doing. When you’re done, re-run your inventory valuation report and make sure your inventory balance sheet account matches the total shown on the valuation report. As we discussed in the article, letting inventory go negative affects your cost of goods sold. The drawback to this approach is you’re recording a fix for that in the current year (when you do the physical inventory & make your adjustments) when the impact was over a longer period of time. If you had so many items go negative that you ruled out the first approach, your prior period net income may be off, and depending on the frequency of the transactions and the $ amounts, the discrepancy may be material.
There are other approaches that blend these 2 in different degrees, or we can run cleanup software that fixes the negative quantities and revises the expenses as well as an automated approach reasonably can. If you need more help deciding which method is best suited to your business, let us know.
I hate to admit this but we have been running negative inventory numbers for 4 years with QB. We are just now deciding to track inventory tied to customer orders. Any ideas on how to delete current inventory levels completely and start fresh.
I really like what I see so far. Keep it up.