It is your favorite time of year, time to complete year-end inventory! It doesn’t matter if you are using a physical calendar year or a fiscal calendar year, iPoint will help you make sure inventory counts are accurate in both iPoint and QuickBooks!
Let’s take a look at the process.
Step 1. Make sure your allocated items in iPoint are accurate with your allocated items in real life.
- Run the Item Stock Summary Report (Dashboard > Reports > Sales Orders > Item Stock Summary).
- Check the Show Staged box.
- Make sure Ordered, Delivered, and No Action are NOT checked.
- This report will show only items that are allocated on sales orders, sorted by the sales order.
- Now compare this report with what you actually have in the staging section of your warehouse and make adjustments as necessary.
- Run the Items Allocated Report (Dashboard > Reports > Items > Items Allocated).
- This report shows every part in your inventory that is allocated to a sale order, work order, or invoice.
- Again, make sure that your actual inventory in allocation matches what iPoint things you have and make necessary adjustments.
Step 2. Do a full inventory count of your warehouse(s) and adjust iPoint so it’s accurate.
Full instructions on how to perform a physical inventory are available on the Physical Inventory page of the manual.
Step 3. Push your inventory totals to QuickBooks
If you aren’t tracking your inventory totals in QB you can ignore this step, congrats you are done with your inventory you lucky son of a gun. For the rest of you scallywags, time to trudge on.
Before we can push the inventory totals to QB, there are a few steps we need to do first.
- Create Invoices for all the delivered products. (If you do summary invoicing, this doesn’t matter. If you do RFP accounting, make sure your delivery invoices are up to date).
- Push all invoices to Quick Books. This is really important because when you push the invoice, QB counts that as delivery and deducts it from your stock levels. This gives you the most accurate numbers in QB before you have iPoint start making adjustments.
- Push all POs and item receipts to QB. Remember what I just said about invoices? The same reasoning applies here.
Ok, now that that stuff is out of the way, it’s time to update QB with your current stock levels.
- First, make sure your QuickBooks is in Single-User Mode
- Go to Dashboard > Inventory > Reports > Stock Levels By Location > Options > Advanced (tab) > Update QB Stock from iPoint (whew that’s a lot of directions!).
- Enter in what account you’d like the adjustment to hit or click Use Item COGS to use the account already ties to each item.
- Enter the class if you want (most people leave this blank if you don’t know what it is, just leave it blank).
- Select the date you want the adjustment to occur on. For more details on the click here.
Now comes the exciting part, click Continue!
You are in for a treat here. You get to watch as iPoint rolls through each and every one of your items and adjusts the inventory levels in QB. This thrilling process lasts about an hour for every 1000 items you have. You can still use your computer during this time, but iPoint will be locked up and you won’t be able to use it. And that’s it! If you’ve done all this you have now officially completed your year-end inventory.
Inventory Sync Counts
If you are the wiz accounting professional we know you are, you will likely be saying, “My inventory valuation in QuickBooks includes everything in my warehouse, not just the stuff you had me count on a physical inventory!”
Yup, we knew you were smart like that! iPoint does some careful math when it pushes your inventory counts to QuickBooks. iPoint pushes the On Hand quantity.
On Hand quantity = Available (stuff that is available to be sold) + Allocated (stuff that can’t be sold because it is already tagged for a specific customer)
The reason we don’t have you include allocated products in your physical inventory counts is that the physical inventory process cannot un-allocate things from a job. Let me explain.
You have 3 widgets allocated on a sales order for Mrs. Jones. But someone stole a widget for Mr. Smith’s job because he needs it today. The problem is they didn’t tell iPoint about this change. When we go to do our physical inventory, there are only 2 widgets on the shelf for Mrs. Jones instead of 3 that iPoint thinks. You will need to go to the sales order, remove the widget from allocation, and put it back into physical inventory. Then, you’ll need to allocate that widget out of inventory onto Mr. Smith’s job so that the iPoint inventory records are accurate with reality. That is a multi-step, manual process. And the Physical Inventory automation just isn’t that smart. We need the wiz accounting professional to do that. (Can you say “job security?”)
That is why we have you ensure allocations are correct in Step 1 above and then only have you count available inventory. That ensures both the allocated quantities and available quantities are accurate so that when you do your inventory sync to QuickBooks, the math will be accurate for your Inventory Asset Valuation.
Inventory Transaction Dates
The date you choose when syncing your inventory adjustments to QuickBooks could have some financial ramifications.
For the sake of our illustration, let’s assume the fiscal year for your company is the calendar year. As a result, your financials need to be accurate as of December 31st. You wait until after the year ends and the holidays are over to do your physical inventory count. You bring some of your team in on the weekend, armed with scanners and free pizza, to count everything in your warehouse and technicians’ vans. By January 15th, you have everything counted and are ready to push your numbers to QuickBooks.
If you were to use the January 15th date, the adjustment to your QuickBooks financials will be completed in the new fiscal year and don’t affect your financials for last year. That’s No Bueno!
But you are smart and make the adjustment for December 31st so the Journal Entry in QuickBooks lands in last year’s financials. Good thinking!
HOWEVER, there are transactions that have taken place in the new year! So any sales your team made since January 1 are going to have those inventory adjustments pushed to QuickBooks… on December 31! Oh My Goodness! This is too complex!
Never fear, there is a solution! (After all, you are working with iPoint, and we have thought of everything… or at least we think so!)
You will need to adjust the inventory transactions being referenced based on the date those transactions occurred. Let’s walk through those steps.
- Once inventory is complete, go to the Inventory Dashboard > Reports > Stock Levels By Location and run the report.
- Click on the Options button and enter December 31 in the Date End field.
- Click the Close & Filter button
- Now, click the Options button again and click the Advanced tab
- Click the Update QB Stock from iPoint button and you’ll see the screen detailed above.
- Note: Yes, we know you can get to this popup window another way. But this is the only way to use the update with the date parameters you just entered. So please stay here and don’t get to the QuickBooks Inventory Sync another way!
- Select your COGS, Class, and Adjustment Date of December 31
- Now, push the Continue button.
VIOLA! Now you are only pushing inventory transactions that took place prior to the end of the year, And your inventory adjustments on your QuickBooks financials are right as rain!
Note: When pushing item receipts to QuickBooks, the item receipt record has the same date as the original Purchase Order. So, if your PO is dated October 25, but the items on the PO were received November 3, then the item receipt (and therefore the inventory adjustment in QuickBooks) will be dated October 25 in QB. Typically, when the accounts payable clerk is matching the vendor invoice against the item receipt in Quickbooks, they will change the date of the receipt to the date of the vendor invoice so that terms are accurately reflected in A/P. It is important to be aware of these date differences when trying to reconcile inventory asset accounts at the end of your period.
The takeaway here: It is best to perform the Update QB Stock from iPoint process as close to the end of the fiscal quarter as possible.
Location, Location, Location
Savvy iPoint users will remember that you can also update the QuickBooks stock on the QuickBooks Sync List. But there really is a benefit to syncing here on the Stock Levels by Location report. Here are the differences.
- Syncing from the QB Sync module does not allow you to sync based on date. Although it is a much faster sync process.
- Syncing from the Stock Levels by Location report gives you the date control. However, because of that control, it is a much slower process.
Non-Inventory in QuickBooks
Many companies choose to keep track of inventory in iPoint and do not push their inventory counts to QuickBooks. There is a setting ( Settings > QuickBooks > Items ) that causes any inventory transactions to be synced to QuickBooks as Non-Inventory. This is all well and good, but our friends at the IRS still want you to report the asset value of your inventory. Never fear, we can help with that, too!
Once you have finished with the physical inventory count, do the following:
- Open the Stock Asset Value report found in Inventory > Reports
- This report takes a while to generate, so you may get a pop-up window letting you know the report is running.
- Feel free to work on something else. When the report is ready to view, you’ll get a pop-up notifying you that the report has been generated.
- The Asset Value Report shows every item based on your search criteria and provides three totals.
- Total Quantity On Hand – how many “things” or items do you have in inventory. These are Inventory items (not non-inventory).
- Total Cost Value – what is the total cost of the inventory items you’ve counted
- Total Retail Value – how much would you make if all the items sold at the rate listed on the individual item cards.
- Use these numbers to manually enter a journal entry in QuickBooks to reflect the change in inventory value over last year.