cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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 12-10-2018

Accepted Solutions
Highlighted
Established Community Backer ***

@ meaghan Ouch First let me clear up some misconception...

@ 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.


9 Comments
Highlighted
Established Community Backer ***

@ meaghan Ouch First let me clear up some misconception...

@ 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.


Established Community Backer ***

You can still use Items, and get clarity for names of thi...

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.

Established Community Backer ***

The OP stated he is buying ingredients, double sided non...

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
Established Community Backer ***

"double sided non inventory items serve no purpose, since...

"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.


Established Community Backer ***

>>>... powerful use of items is better than marking "acco...

>>>... 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.
Frequent Contributor *

Re: @ meaghan Ouch First let me clear up some misconception...

 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

 

 

 

QuickBooks Team

Re: @ meaghan Ouch First let me clear up some misconception...

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.

Frequent Contributor *

Re: @ meaghan Ouch First let me clear up some misconception...

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. 

 

 

 

 

 

Moderator

Re: @ meaghan Ouch First let me clear up some misconception...

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.