How can I properly adjust end of year inventory in quickbooks online?

jamifsud · ‎October 15, 2018

I'm working to setup inventory / COGS tracking in Quickbooks Online. All of my items are setup in Quickbooks and the relevant items have inventory tracking enabled already (they have had this enabled for some time).

I would like to use inventory / COGS reporting for all of the current year (2017) and I have an accurate inventory count for end of year 2016. I also have sales transactions in QBO for 2017.

I would like to add my 2016 year end inventory totals to Quickbooks so that the reporting (of COGS and inventory amount) are accurate. Can I simply add an inventory adjust for every item and set the adjustment date to 12/31/2016? Will this adjust my books properly? Will the transactions that occured after 12/31/2016 be properly reflected in the inventory?

Rustler · ‎October 15, 2018

OK if you used periodic inventory in 2016, Your income tax form in the cogs section will show an ending inventory balance for that year - that is the number you have to work with when valuing what is on hand to start 2017.  Use your physical inventory of what is on hand now, plus any that were sold this year as the starting qty for 1/1/17
see the pic for how an inventory item screen is set up.

This is going to be weird is some ways but it is due to the way QBO works

1. create a dummy bank account called clearing, zero balance
2. plus menu>other>inventory adjust
2a. select the clearing bank as the adjustment account
2b.  set the adjustment date to 1/1/17
2c.  set the new qty block to zero for all items and save
3.  Note the final balance in the dummy clearing bank account and compare it to the ending inventory number from the cogs section on the income tax form.
3a.  IF the clearing bank is higher, then create an expense from the plus menu, and as the account use opening balance equity, enter the difference amount - double check the clearing bank balance to insure it matches the number from the IRS form
3b.  IF the clearing bank lower, make a deposit, enter the difference amount, and use opening balance equity as the source (from ) account for the deposit
4.  If you have inventory items on hand that are not created in QB create them now, enter a starting qty of zero, cost of zero and an as of date of 1/1/17
5. create a dummy vendor called dummy
6. insure this setting is on, in company settings>expenses>bills & expenses turn on the items table and purchase orders.
The balance in the clearing bank account is the total value of your on hand inventory as of 1/1/17 so be sure that you portion that balance out across all items before you start step 7
7.  menu plus>expense, select the clearing bank account as the payment source
7a.  half way down the screen is item details, click that if the item table is not visible
7b.  on each line, select an inventory item, enter the qty on hand as of 1/1/17, and the total cost of that qty
7c.  I do not know how many items you can do on one transaction, but I would do 10 at a time and save, then do it again for the next 10 so that no work is lost if a connection breaks

When you are finished, check the balance in the clearing bank account, it must be zero, if so make the account inactive, and make the dummy vendor inactive too.

You now have starting inventory with value and qty in the FIFO inventory asset account in QBO.

Now enter your sales of inventory items since 1/17, you can use a generic customer you create and a sales receipt to do it.

View solution in original post

Rustler · ‎October 15, 2018

OK if you used periodic inventory in 2016, Your income tax form in the cogs section will show an ending inventory balance for that year - that is the number you have to work with when valuing what is on hand to start 2017.  Use your physical inventory of what is on hand now, plus any that were sold this year as the starting qty for 1/1/17
see the pic for how an inventory item screen is set up.

This is going to be weird is some ways but it is due to the way QBO works

1. create a dummy bank account called clearing, zero balance
2. plus menu>other>inventory adjust
2a. select the clearing bank as the adjustment account
2b.  set the adjustment date to 1/1/17
2c.  set the new qty block to zero for all items and save
3.  Note the final balance in the dummy clearing bank account and compare it to the ending inventory number from the cogs section on the income tax form.
3a.  IF the clearing bank is higher, then create an expense from the plus menu, and as the account use opening balance equity, enter the difference amount - double check the clearing bank balance to insure it matches the number from the IRS form
3b.  IF the clearing bank lower, make a deposit, enter the difference amount, and use opening balance equity as the source (from ) account for the deposit
4.  If you have inventory items on hand that are not created in QB create them now, enter a starting qty of zero, cost of zero and an as of date of 1/1/17
5. create a dummy vendor called dummy
6. insure this setting is on, in company settings>expenses>bills & expenses turn on the items table and purchase orders.
The balance in the clearing bank account is the total value of your on hand inventory as of 1/1/17 so be sure that you portion that balance out across all items before you start step 7
7.  menu plus>expense, select the clearing bank account as the payment source
7a.  half way down the screen is item details, click that if the item table is not visible
7b.  on each line, select an inventory item, enter the qty on hand as of 1/1/17, and the total cost of that qty
7c.  I do not know how many items you can do on one transaction, but I would do 10 at a time and save, then do it again for the next 10 so that no work is lost if a connection breaks

When you are finished, check the balance in the clearing bank account, it must be zero, if so make the account inactive, and make the dummy vendor inactive too.

You now have starting inventory with value and qty in the FIFO inventory asset account in QBO.

Now enter your sales of inventory items since 1/17, you can use a generic customer you create and a sales receipt to do it.

View solution in original post

jamifsud · ‎October 15, 2018

This is incredibly helpful. Thank you Rustler! For my own understanding of what is happening here I have a question. I understand the flow of value through the steps provided above and the rational makes sense. At a high level what is the difference between these steps outlined above and setting the inventory values to their proper amount on 12/31/16? I'm assume it has something to do with the way QBO works?

Rustler · ‎October 15, 2018

QBO uses FIFO which is average cost per purchase, but QBO does provide a way to designate the purchase date you are adjusting, nor does it allow for a value adjustment at all

Malcolm Ziman · ‎October 15, 2018

" nor does it allow for a value adjustment at all"
Right no value adjustment , but when you first set up an item that is when you capture the value. It takes the qty and multiplies by the initial cost and creates an entry dated the date you enter: debit Inventory and credit Opening Balance Equity. Opening Balance Equity needs to be cleared out after all the opening balances are entered.
You can also do a batch upload of Inventory Items with Qty AND Initial Cost.
Given the import capability, I don't see the need to create a " dummy clearing bank account " and go through all the steps, as you suggested above.

Rustler · ‎October 15, 2018

@ jam

When you set up the inventory items, did you change the as of date to 1/1/17?

If not, then that is a problem, QBO will not allow transactions for inventory before the as of date.

Are you taxed as a sole proprietor or partnership?

If you have a the current correct starting account balances entered, how did you do that without a starting inventory? Are the owner/partner equity accounts correct in QBO as compared to what they were in your previous books?

In 2016 did you buy the items for inventory as inventory assets or did you use an expense account for the purchase?

Your accurate count of inventory for 12/31/16 - does that include cost per item on hand?

jamifsud · ‎October 15, 2018

Thanks for getting back to me Rustler!

We started using QBO last year to process invoices and unfortunately set our items to track inventory even though we didn't use it (didn't know what we were doing unfortunately). For the year 2017 we're going to track our inventory and all expenses through QBO in addition to invoicing. We're not concerned with our 2016 numbers as we didn't use QBO for anything other than tracking invoices / who owes us money. We're continuing our migration to QBO in 2017 and want to ensure that as of 1/1/2017 our opening balances are correct for inventory assets (quantities, purchase prices). This needs to be done retroactively as we have quite a few sales transactions for the year 2017 in quickbooks already.

To answer your questions:

1. I don't believe we have the correct starting balances entered. We need to get the correct starting balances for our inventory asset accounts. Quickbooks does match our current books however for 2017 we're only going to use QBO to report sales, A/R, inventory/COGS and all expenses. Our accountant will handle the rest.

2. In 2016 we used a purchases account (not via QBO) and in 2017 we'd like to use QBO's inventory asset method.

3. The inventory count does include cost per item.

Thanks for your help with this!

Rustler · ‎October 15, 2018

What type account, asset, expense etc, is the purchases account?  Look in the chart of accounts listing
If in 2016 you posted the purchase of what you are calling inventory to an expense type account, then the item still on hand have no value in 2017.

Have you created the inventory type items in QB yet?
If so what was the as of, the starting date, used on the item screen when you created it.  If you are not sure, open the register for inventory asset from the chart of accounts, and look for entries with the word START - that shows the start date.
And if you created the item already, did you enter a starting qty and/or cost?

I'll agree with Malcolm that you need some basic understanding of accounting to run a business, but small business accounting is pretty straight forward once you have those basics.  The rest is learning what to do, how to do it, and why in QB.

jamifsud · ‎October 15, 2018

For 2016 the Purchases account was an expense (which was not used in Quickbooks but rather tracked via our old accounting software - 2016 will be the last year that we use this method).  We use the periodic inventory system currently.

We have many inventory type items in Quickbooks already because we created the inventory items back in early 2016.  The start date for those inventory items is early 2016.  We did enter a starting inventory and cost for all items.

Absolutely agree regarding a basic understanding of accounting and QBO.  We've devised a plan with our accountant but we're not sure how to realize this in QBO.  If we can get the inventory asset account and quantity on hand values to reflect the 2016 end of year inventory we'll be in great shape to move forward.  Unfortunately we don't know QBO well enough to completely understand how to do this and what the underlying impact on our books will be within QBO.  It sounds like it is not as simple as adding an inventory quantity adjustment for 12/31/2016?

Malcolm Ziman · ‎October 15, 2018

You have to consider the other side of the entry. The posting increases the balance in the Inventory Asset account, but you have to tell QBO the other side. Where did this come from? Normally you increase inventory with a bill or check/expense and use an Inventory Item. So the other side reduces your bank balance or increases your payables. You can't use bank because that will change your bank balance. If you have already paid for the items, what expense/asset account did you debit for the payment?

As for the 2017 sales, the second part of the posting to every sale, apart from debit (increase) cash/AR, credit Sales, is to credit (reduce) the inventory asset and debit (increase) COGS, with the FIFO method (QBO) which considers the cost of every transaction (including adjustments) for the item, to come up with the current cost. So it is important you enter the correct cost - what you actually paid.

Rustler · ‎October 15, 2018

>>> with the FIFO method (QBO) which considers the cost of every transaction (including adjustments) for the item, to come up with the current cost.<<<

what does this mean?

QBO has no value adjust - which is a good thing. FIFO is the first purchase still on hand is what is sold or adjusted using inventory adjust. FIFO cost is average cost per purchase date. Increasing stock on hand using inventory adjust is wrong, but even if you do it, that is a new "purchase" entry in FIFO, you can not designate the purchase that you are adjusting

jamifsud · ‎October 15, 2018

Hi Malcolm - thanks for getting back to me. A bit of context:

We started using QBO last year to process invoices and unfortunately set our items to track inventory even though we didn't use it (didn't know what we were doing unfortunately). For the year 2017 we're going to track our inventory and all expenses through QBO in addition to invoicing. We're not concerned with our 2016 numbers as we didn't use QBO for anything other than tracking invoices / who owes us money. We're continuing our migration to QBO in 2017 and want to ensure that as of 1/1/2017 our opening balances are correct for inventory assets (quantities, purchase prices). This needs to be done retroactively as we have quite a few sales transactions for the year 2017 in quickbooks already.

If we don't care about 2016 numbers and we set the adjustments for 12/31/2016, can we safely ignore the companion journal entires knowing that 2017 will be correct?

Malcolm Ziman · ‎October 15, 2018

"(didn't know what we were doing unfortunately)." and obviously still don't. This is complicated. I commend you for trying but I don't see you getting to understand it here. If you want your books to be useful, you will have to study accounting - a 4 year degree or even a masters or doctorate, unless you are one of those genius people that can learn a language or learn how to play the piano in 7 days. If not, luckily there are people who have spent years studying the subject and will do this for you for a fee, freeing you up to play golf or climb Mount Everest or even grow your business using the reliable numbers you now have from your books.

mbrede · ‎November 08, 2018

Topics

Training

Community

Resources

How can I properly adjust end of year inventory in quickbooks online?