It’s never a good idea to run out of important inventory items that you resell or use in manufacturing. In simple inventory control systems we often set a minimum stocking level that is used to generate a warning when an item falls below a particular on-hand balance, so that you can order (or build) more of the item just before you run out. In this article I’ll examine how QuickBooks handles this process.
When you edit an inventory item you can enter a value for a Reorder Point. This is the QuickBooks equivalent of what I am calling the minimum stocking level. Essentially, this is the quantity of this item that you want to have on hand at all times to prevent you from running into shortages. In an upcoming posting I’ll talk about some strategies for coming up with a good value for this field.
So, it’s obvious how this is used – you enter the value here and QuickBooks will order the items for you when you fall below that level? Well, not quite. QuickBooks won’t automatically cut purchase orders for you (a lot of arguments going on whether a program should do that or not), You do get a report that shows you what items to reorder, though.
Inventory Stock Status by Item
This is the report to look at when you are checking on your inventory stock levels. Here is a simple example:
Looking at the columns:
- Reorder Pt is the reorder point we entered in the item record, what I’m referring to as the minimum stocking level.
- On Hand is the quantity you have on hand as of the ending date of the report.
- On Sales Order shows the demand for this item on open sales orders – the number that you have promised to people.
- For Assemblies is the demand for this item on pending builds – the number that you need to be able to build all of the outstanding pending builds.
- Available is a calculated value, the current on hand value minus the demand (sales order and pending builds).
- Order will have a check mark if QuickBooks is recommending that you place an order for this item.
- On PO is the potential supply for this item – the number that you have on outstanding purchase orders. Note that you don’t know when these items will be received, although the next column gives you a clue.
- Next Deliv is the due date for the next PO. This is only partly helpful if you have multiple open PO’s for an item. Be careful with this, as the On PO value might not all be delivered on the Next Deliv date.
- Sales/Week is the number of items you have sold, on the average, per week in the period that the report covers. This sample report covers this month to date so only the sales of the item in the current month are included in the calculation.
When is “Order” Checked?
When does QuickBooks suggest that an item should be ordered? Let’s look at some samples in this report:
It appears that the check mark appears if On Hand plus On PO meets or falls below the Reorder Pt value.
The simple example is the Wheel part. Reorder Pt is 10, On Hand is 10, time to order some more. Looking at Cog you see that the values are the same as Wheel except that we have a PO coming in for 1, so it is not checked. Doesn’t QuickBooks take the Available figure into account, which includes your outstanding demand for the item from sales orders and pending builds? Apparently not. Look at item AB451 – you have an Available of -2 and an On PO of only 1, so we look to be short, but it isn’t checked. On Hand plus On PO exceeds the Reorder Pt figure.
If you would like to change this calculation you can easily export the report to Excel and create your own calculations based on the figures in this report. You might, for example, change the calculation to use the Available value instead of the On Hand value, depending on your business situation. Another nice thing you can do in Excel is to filter the report to show just those items that need to be ordered.
PLEASE KEEP IN MIND that the element of time is not included in these calculations! You don’t know when the PO’s are arriving (you know when the next PO comes, but not necessarily all PO’s). You don’t know when those sales orders or pending builds need to be completed. This report is a very simplistic view of your inventory and you have to use it with caution when ordering items.
What About Assembly Items?
You should be able to carry this same concept of a minimum stocking level over to Inventory Assembly items. These are items that you are going to sell, or possibly use in building other assemblies. You still have an On Hand value, demand from sales orders and other assemblies, supply from pending builds (instead of PO’s, unless you purchase the assembly). Can’t we use the Reorder Point for these items?
Unfortunately, in QuickBooks, the answer is “no”. We have a Build Point value instead of a Reorder Point, as you can see in the following screen:
Although this could be considered a synonym for Reorder Point, as you can see in this report the value doesn’t carry through.
It is very unfortunate that QuickBooks limits this report by not allowing us to do the same sort of calculations. At this time the best I can suggest is a complicated process. You can create a custom field to hold your own reorder point for both item types, export the item list to Excel (the Excel export sends the regular reorder point but not the regular build point, but you can export custom fields), export the Inventory Stock Status by Item report to Excel, merge the values from the item list into the report and do your own calculations.
If you like to play with databases you can also use the ODBC driver to extract this information, but it is very complicated. The reorder and build points are separate fields and only show in the inventory item type that they are used in. Creating a single unified column for a minimum stocking level is very difficult to do.
On a side note, this is one area that I am planning on addressing with an integrated application from CCRSoftware at some point in the future.
How To Set the Reorder Point?
One very critical question is how do I come up with a good value for the reorder point? This can be a simple process for some businesses, it can be very complicated for others. In my Calculating QuickBooks Reorder Points article I give you some ideas of how you might approach this.
About the Author (Author Profile)
Charlie Russell is the founder of CCRSoftware. He’s been involved with the small business software industry since the mid 70’s, focusing on inventory and accounting software for small businesses. Charlie is a Certified Advanced QuickBooks ProAdvisor. Look for Charlie’s articles in the Accountex Report blog, as well as his California Wildflower Hikes blog.