cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Level 1

Adjusting COGS to compensate for negative inventory?

Hi,

I have a pretty tangled problem here.

My boss wants to run a P&L, and the COGS that is displaying is not accurate. I have discovered it's not accurate because some of the items (Inventory Parts) we make/sell had a blank spot in their Cost field, and COGS was assuming that we bought them for their sale price, since there was nothing in the Cost field (I think?). I entered $0.00 in the cost field, since their cost comes from their Ingredients account, which we enter as purchases separately. We don't directly "buy" any of the things we sell... so I think this is right? Either way, our accountant told me to remove all the costs when he did our taxes, so I did; I just apparently never entered a $0.00 cost into a few of the items... unfortunately, the most popular items, which causes massive discrepancies when we try to run a P&L.

Thinking it would fix the issue, I entered $0.00 in the items that had no Cost... but not only did it not fix the COGS for previous entries, it did not fix the COGS for the test invoice I was working with.

I've deduced this is because COGS is still pulling an Average Cost based on previous entries... but I have no idea how to change those previous entries.

As some background... we make products to order, so we don't enter a stock of inventory items (we're in prepared foods production, so the client orders --> we make exactly what they want --> we bill & ship). From the little bit of research I've been able to do, that seems to be causing a big problem as we have -10,000 items in stock... obviously a problem.

While I can see from trying to figure out the answer to this issue that not entering inventory is a huge problem, we... have done our taxes with this system before, and our accountant didn't offer us a better solution, or tell us to stop doing it... I think he just adjusted it at year-end with a journal entry. But that obviously doesn't help me fix this problem going forward...

Any advice you can offer is appreciated; to reiterate after this whole mess, my boss just wants to run a fairly accurate P&L and the COGS is wildly off because of inventory/Average Cost discrepancies. How do I edit Average Cost, or compensate for years of negative inventory?

Solved
Best answer December 10, 2018

Best Answers
Highlighted
Level 15

Adjusting COGS to compensate for negative inventory?

@ meaghan

Ouch

First let me clear up some misconceptions.  The cost block on the item screen, when inventory is used correctly, has nothing to do with the accounting, it is a reference number used to pre-fill an order.  Used correctly, are the key words.  When you do not specifically order and stock the item, an inventory bug that has existed for over a decade activates.  Instead of using the average cost established by ordering and stocking the item, QB uses whatever number you entered in the cost block when you use or adjust that item.  For more on this fake cost being posted see this:
http://onsale-apparel.com/Rustler/warning-inventory-bug

In your business model, you should not be using inventory at all.  You get an order, buy what you need, and fill the order.  What you buy should be posted directly to COGS, or I would use a materials cost expense account instead (or if the ingredients account is an expense account that is even better).   Then you use a service item to sell what ever it is you made.

Because you say this has been a problem for more than one year, this situation is almost impossible to fix for this year without having a massive amount as a current year adjustment, most likely that amount will translate into income for this years P&L ( a negative expense [cogs] on the P&L is an increase in income).

This problem also affects the company balance sheet, if you run that report I would expect the inventory asset account to be negative -true?  Unless your tax guy has been making journal entries to inventory asset double click on it to see.  Journal entries should NEVER be made to inventory asset.

If it were me, going forward I would enter purchases and use the expense account I mentioned in the seconded paragraph, and not use any inventory items going forward.  let your tax guy handle this one more time as he has been.

I would create a new company file, export accounts, customer and vendor lists (file>utilities>export>lists to iif) and import them into the new file (same menu tree).  Then create the service, non-inventory, sales tax, discount, other charge items you use normally in the new company item list - but no inventory items.

After the end of the year, post starting balances to the accounts, and use that new file going forward.  Burn the old file backup to a CD for historical purposes against an audit.

IF, you want to see what the impact would be trying to fix this, create a back up from the file menu first.  Then use inventory adjust, set the adjusting account to COGS, and increase the qty of all items to zero.  Then run your P&L.
Restore from back up afterwards

I suggest you print this page and sit down with the boss.


View solution in original post

11 Comments 11
Highlighted
Level 15

Adjusting COGS to compensate for negative inventory?

@ meaghan

Ouch

First let me clear up some misconceptions.  The cost block on the item screen, when inventory is used correctly, has nothing to do with the accounting, it is a reference number used to pre-fill an order.  Used correctly, are the key words.  When you do not specifically order and stock the item, an inventory bug that has existed for over a decade activates.  Instead of using the average cost established by ordering and stocking the item, QB uses whatever number you entered in the cost block when you use or adjust that item.  For more on this fake cost being posted see this:
http://onsale-apparel.com/Rustler/warning-inventory-bug

In your business model, you should not be using inventory at all.  You get an order, buy what you need, and fill the order.  What you buy should be posted directly to COGS, or I would use a materials cost expense account instead (or if the ingredients account is an expense account that is even better).   Then you use a service item to sell what ever it is you made.

Because you say this has been a problem for more than one year, this situation is almost impossible to fix for this year without having a massive amount as a current year adjustment, most likely that amount will translate into income for this years P&L ( a negative expense [cogs] on the P&L is an increase in income).

This problem also affects the company balance sheet, if you run that report I would expect the inventory asset account to be negative -true?  Unless your tax guy has been making journal entries to inventory asset double click on it to see.  Journal entries should NEVER be made to inventory asset.

If it were me, going forward I would enter purchases and use the expense account I mentioned in the seconded paragraph, and not use any inventory items going forward.  let your tax guy handle this one more time as he has been.

I would create a new company file, export accounts, customer and vendor lists (file>utilities>export>lists to iif) and import them into the new file (same menu tree).  Then create the service, non-inventory, sales tax, discount, other charge items you use normally in the new company item list - but no inventory items.

After the end of the year, post starting balances to the accounts, and use that new file going forward.  Burn the old file backup to a CD for historical purposes against an audit.

IF, you want to see what the impact would be trying to fix this, create a back up from the file menu first.  Then use inventory adjust, set the adjusting account to COGS, and increase the qty of all items to zero.  Then run your P&L.
Restore from back up afterwards

I suggest you print this page and sit down with the boss.


View solution in original post

Highlighted
Level 15

Adjusting COGS to compensate for negative inventory?

You can still use Items, and get clarity for names of things you buy and sell, but this is NonInventory. Even you clearly stated you don't Stock and hold anything.

What you want is to replace the use of those Inventory Items with Two Died Noninventory items. That way, you still get to use Cost on purchases and Price on sales, and Quantity. You will link the left side for expense to COGs and the right side to income.

Now you use this item on purchases, and that flows directly to COGS. You can mark this is billable to the customer, or it might be something you sell that is different than something you bought, of course.

And you don't need a Cost value entered into an item unless that will always be the same. That is a Helper tool for placing your orders.

Please see my attachments. This all is what you should be using.

Highlighted
Level 15

Adjusting COGS to compensate for negative inventory?

The OP stated he is buying ingredients, double sided non inventory items serve no purpose, since he makes products to order from the items purchased he is not selling each ingredient.   I did suggest he use non inventory items to track purchases
Highlighted
Level 15

Adjusting COGS to compensate for negative inventory?

"double sided non inventory items serve no purpose, since he makes products to order from the items purchased he is not selling each ingredient.   I did suggest he use non inventory items to track purchases"

We know you and I disagree on how the powerful use of items is better than marking "account entries" as "track expenses into income." We also know you don't use as many of the Item tools and reporting as I do.

We see a person who clearly thought inventory items should be used, which indicates a desire for lots of Name-based reporting on these activities. There are many times when people think they must use Inventory, to get Item Names, but when they get into a corner, we help them realize the "inventory" was the error, but the "item" is still a tool they can use.

Once again, this is a Contributory, not a Combative, discussion.


Highlighted
Level 15

Adjusting COGS to compensate for negative inventory?

>>>... powerful use of items is better than marking "account entries" as "track expenses into income." We also know you don't use as many of the Item tools and reporting as I do<<<

I have no idea what you are getting at. That I do not use the same reports as you do has nothing to do with anything.  And I have never proposed that expense be posted to income

The reason you use a two sided non inventory item so that you can track BOTH sales income and expense for that SAME item.

A business who is NOT selling that item, does not need a two sided item.  Having a two sided item, when it is not required, opens the company file to a clerk choosing the wrong item on a sale.

>>>We see a person who clearly thought inventory items should be used, which indicates a desire for lots of Name-based reporting on these activities. <<<

Yes they clearly thought inventory should be used, and that has been addressed - but ...

indicate a desire for name reporting - that is quite a leap of supposition, when absolutely nothing was mentioned about reporting on items.
Highlighted
Level 2

Adjusting COGS to compensate for negative inventory?

 Thanks for the link. I just saw this.  I was on the phone with Quickbooks for over 4 hours and First technition  could not fix the problem  Quickbooks technician #2 supposedly fixed it. I’m not calling back as I’m overwhelmed and attempted to share that the inventory changed, like the screenshots you showed

 

, We have had a history of Qb since starting business over 20 years ago.   UPGRADING from Quickbooks Pro 2016 to Quickbooks Pro 2019 team started this total fiasco of these errors.-with the average cost....

 

1) I gave accountant a copy of the QuickBooks Pro 2016 backup since this shows the correct balance from the previous years - backups to the accountant.

2). ‘meanwhile we did download the qb pro desktop 2019.  the figures are off so currently we are ONLY USING for husband to use to invoice customer. The inventory average cost basis in wrong. So we really are going to have to repost all the invoices manually, which will be fine ONCE THE INVENTORY is correct. 

 

   Emailing myself a copy of the invoice husband posted to keep track , as this is all that will be wiped out once we start the new company. 

3) , is there a way for me to export the inventory in excel and is there a way to import the correct  daily average? 

4) Not an accountant and I did give account on our paperwork, I’m just trying to get a feel, as we are just standing here not being able to really use our QuickBooks,  we have one computer that is a desktop. My husband uses through work to do the invoice, he brings it home and I emailed myself a copy of the invoice. As this will no longer be in the 2019 Quickbooks. So as you can see we’re doing double work 

 

 I’m not that knowledgeable with importing exporting.  we are wanting to start the a new company file.  Can a person export the inventory to excel or do we have to enter the whole inventory by hand.  Thanks in advance for all your help . 

Dar

 

 

 

Highlighted
Moderator

Adjusting COGS to compensate for negative inventory?

Hello there, DarDar.

 

Allow me to join this thread and share some information about exporting items in QuickBooks Desktop.

 

Yes, you can export the inventory in excel by exporting the item list. However, there isn't a way to import and correct daily average.

 

Here's how:

  1. Go to Lists menu.
  2. Click Item List.
  3. On the Excel dropdown, choose Export all Items.
  4. In the Export window, choose whether to create a new worksheet or update an existing worksheet.
  5. Click the Export button.

Let me share with you this article that provides the steps on how to export item list: Export lists in QuickBooks Desktop.

 

As always, you can reach out to our Care Support Team if you need further assistance from our QuickBooks Desktop Support​.

 

Here's how:

  1. ​Sign in to your QuickBooks Desktop company.​
  2. Select QuickBooks Help.
  3. Click Contact us.

If there's anything else I can help you with exporting item list, just let me know. I'm always happy to help.

Highlighted
Level 2

Adjusting COGS to compensate for negative inventory?

Thanks, so how do I adjust the average cost, I did go to adjust inventory but it is "grabbing the daily 1) average balance.  Is there a step by step to correct.  Husband has new bill to post for this particular item, however it will calculate wrong.  How should this be handled. 

 

 

 

 

 

Highlighted
Anonymous
Not applicable

Adjusting COGS to compensate for negative inventory?

Hello, @DarDar.

 

Thanks for getting back. Let me step in and help share additional information about the average cost in QuickBooks.

 

In QuickBooks, the use of weighted average cost determines the value of your inventory and the amount debited to COGS when you sell inventory. The average cost is the sum of all the cost of all the items divided by the number of items. To know more about handling this, please refer to this related article: Understand Inventory Assets and COGS tracking.

 

This article should guide you in the right direction on how average cost is calculated. 

 

If you need further assistance, you can always contact our Desktop Care support. They have extra tools that can help you with the step by step process in handling average cost calculation.

 

Here's how to contact them:

 

  1. On your QuickBooks account, press F1 and select Contact us.
  2. Type in your question and select Search.
  3. Click the Start a Message tab.

 

 

Keep me updated if you have additional questions about your average cost. I'm always here to help however I can.

 

Highlighted
Level 1

Adjusting COGS to compensate for negative inventory?

Hello, 

 

Seems I'm not the first to have issues with inventory costs and reports?  I really need some help as we are trying to run an accurate P&L and Balance Sheet for potential investors who needed it last week! 

 

Background:  Manufacturing company using QBO with inventory tracking.  All raw materials are set up with cost used for Purchase Orders - not for a Bill of Materials, as there are no Assemblies in QBO. 

 

Inventory gains and losses are manually adjusted at time of physical inventory count.   We want to know how QB does these calculations for Raw Materials and Finished Goods in the Adjustment accounts? 

All Finished Goods are set up with COGM and wholesale price.  (These have both changed over time and starting date values have now been adjusted to more accurate COGM.  (I also did this in attempt to be consistent in the valuation calculations.)  I believe any COGM amounts that were different in the products at the time of actual Sales would be calculated or would they also be adjusted from my changes?

 

2 major problems: 

1.  Finished Goods total on Balance Sheet is way higher than physical Inventory and Valuation Detail Report. 

2.  Raw Materials total is higher than than actual physical Inventory and the  Valuation Detail Report.    

 

I've run all reports in Accrual, ensured there are no missing costs or other journal entries with "no item" that could cause valuation issues.

 

Boss only wants to show actual SALES COGM.  The Inventory Adjustment totals (above the line in COGS) inflate revenue and seem to need a manual adjustment to somewhere else (need advice so as not to inflate asset accounts).   He also feels Raw Materials used to create Finished Goods (which already have a COGM value) are not an "expense" and should simply be debited from the RM asset account - without crediting Finished Goods or going to an Expense account (Raw Materials Consumed)?  

 

Basically, the increase in FG and decrease in RM are not adding up.  Inventory Adjustment account is where the numbers are but don't know how to adjust. 

 

Help???

 

 

Highlighted
QuickBooks Team

Adjusting COGS to compensate for negative inventory?

Hi there, JTrammell,

 

I can clear things out for you. The Balance Sheet report draws information from the accounts whether they are associated with items or not.

 

The Inventory Valuation reports, however, draw information from items only. This means that transactions using inventory items show on both reports but transactions without inventory items show only on the Balance Sheet report.

 

Some examples of transactions that will show on the Balance sheet but not the Inventory Valuation reports are as follows:

  • Bills, checks credit card charges with the Inventory Asset account on the Expenses Tab.
  • Journal entries using the Inventory Asset account.
  • Inventory adjustments offset to the Inventory Asset account instead of a COGS account.

You'll want to make an adjustment to correct your inventory. It will also update your reports in QuickBooks. Follow these steps shown below to enter an inventory quantity adjustment:

  1. Select + New.
  2. Under Other, select Inventory Qty Adjustment.
  3. Enter the Adjustment Date.
  4. In the Inventory adjustment account drop-down, select the appropriate account.
  5. Select the products in the Product field drop-down.
  6. For each item, enter either a new quantity or a change in quantity.
  7. (Optional) In the Memo field, enter the details about the adjustment.
  8. Select Save and close.

Check out the Adjust inventory quantity on hand in QuickBooks Online page to learn more about the process above.

 

When using the inventory tracking in QuickBooks, you'll want to consider how it impacts your Balance Sheet and Profit & Loss report.

 

You may want to consider entering your items using the Bundle type to prevent adjustments in the system. It will be reported to COGS based on the purchased or value of the materials.

Need to get in touch?

Contact us