Having an adequate supply of component parts is critical to a manufacturer. If you run out of something, you can’t build your assembly. If you can’t build your assembly, you can’t fulfill customer orders. So let’s talk about how to avoid this if you are using QuickBooks.
Projecting the “demand” for component parts can be a very complicated process. What assemblies do you need, and when do you need them? If you have subassemblies, those have to be built before the final product can be built. How long does it take to build them? How long does it take for purchased parts to arrive? The problem is complex – and there are programs available that can help you with this complicated process – generally referred to as “MRP” or “ERP” systems (Material or Enterprise Requirements Planning). Unfortunately, these kinds of programs are very expensive to purchase, and even more expensive to implement.
Within the framework of QuickBooks we have limited options. There isn’t a formal forecasting system that lets us project future sales or other needs. There isn’t a time phasing feature that lets us specify the time it takes to build an assembly or subassembly, or what the typical time is to acquire a particular part. However, there are a few things that you can do.
One of the ways to do some analysis is to use the Inventory Stock Status by Item report and pending builds. This does NOT involve any “time phasing”, but it can give you some help.
Setting Up
To start with, let’s make sure that we have enabled the quantity available calculations in your Inventory preferences.
This is a helpful setting that will let us use the quantity available calculations in our simple forecasting system. Note that both boxes are checked.
Here is a Bill of Material for an assembly I’ll use as an example:
Sales Orders as Forecasts
If I want to project future demand for component parts, I have to forecast future demand for the assemblies that I’m going to sell. One of the very useful features in QuickBooks is the sales order system You can enter sales orders, either for actual orders that are being placed or for “forecasted” orders that you anticipate in the future. These won’t reduce the on hand inventory balance, which is your accounting balance, but if we have set up the preferences as shown above they will affect the quantity available calculation, which is a planning number, not a financial number.
Looking at the Open Sales Orders by Item report for my 0050-MC pool assembly, you can see that I’ve added two sales orders.
The first is an actual sales order that a customer has placed, for June 10th (the current date is May 19th). I’ve also decided to add a “forecast” order – I’ve created a dummy customer named “Forecast” and I added a sale of 6 pool covers for July.
Pending Builds
Taking the information in the sales report, I’ll create pending builds for the items on the dates that are shown in the report. Note that I’ve stated that it is a “forecast” in the memo field. In this case it is automatically set to pending because I have a parts shortage. If it isn’t marked pending automatically then you can use the Edit menu to change it to pending status.
I’ll add a second pending build for July 1st.
Looking at the Pending Builds report we can see the forecasted builds.
Inventory Stock Status by Item
Now let’s look at the Inventory Stock Status by Item report. It is very important that you set the time range of the report to all, or it might not include your forecasts.
For the main assembly 0050.MC you can see that we have 11 on sales order.
For the component part TUCA-4X2 you can see that we have a demand for 22, and a projected shortage of 14.
For the subassembly WHAS you can see that we have a demand for 22 and a projected shortage of 16.
This is a very simple means of trying to project component shortages. It tells you that to be able to complete the products that you want to sell in the period of time that you are working with, you have some shortages. It shows which items, and how many you are short. Be careful with this, however. There is information that you do NOT see here. Without any “time phasing”, you don’t have a good picture of WHEN you need these items, or when you should purchase them. You don’t want to purchase too soon, but you also don’t want to purchase too late.
A good MRP or ERP system would tell you the WHEN in addition to the HOW MANY you need to purchase. The problem is, these kinds of systems are expensive and involve a lot of work to implement.
Subassembly Demand
In our example we have several subassembly items as components to the higher level item we are selling. QuickBooks allows you to include assemblies as components in a higher level assembly, but it doesn’t do any processing of subassemblies. In our sample, for instance, you see that you need 22 of the WHAS wheel assembly. That in turn requires additional parts, but the demand for these parts doesn’t show in these reports.
You can, if you can identify the subassemblies, add pending builds for these items. Look in the Inventory Stock Status by Item report and create a pending build for each inventory assembly that has a for assemblies value. The WHAS item requires 22 subassemblies (and you are short by 16), so issue a pending build for these as well.
Third Party Help
As I mentioned, there are several MRP and ERP programs that you can find in the Intuit Marketplace that can help with these projections. Costs vary, and you will find that most of the advanced solutions will manage inventory outside of QuickBooks.
Another useful product is CCRQBOM. For the sake of full disclosure please note that my company produces this product. CCRQBOM is not an MRP/ERP product, and it will not “time phase” your component demand. However, it does provide you with several useful tools, the price is much lower than the MRP/ERP products, and it works entirely with your QuickBooks inventory data rather than setting up a completely separate inventory system.
One very useful feature – you can see a full level view of a multiple level assembly. This is useful in seeing what components are used in subassemblies.
Another useful feature is the requirements report. You can enter your forecasted demand for a final assembly (or multiple final assemblies) and the program will calculate how many component items you need. You don’t have to enter pending builds, you don’t have to enter forecasted sales orders (although that still can be a useful tool). More importantly, this can generate full level requirements automatically.
The report shown below was generated by telling the program that I needed 11 of the 0050-MC assembly. The program tells you how many of each component you need (quantity), how many you have available, and how many you are short. The build column tells you how many of the subassemblies you need to build.
Look at the WHAS subassembly. It says we need 44 of them, rather than the 22 that our earlier report indicated. The reason for this is that the WHAS subassembly is included not only in the higher level 0050-MC, but also in the required PU-1800 subassembly. CCRQBOM makes it easy to find these “hidden” demands from lower level assemblies.
Hi Charlie,
Could you tell me if there is a way to tie an inventory assembly to an inventory item so once you complete a build it would show up in the inventory under the main part number?
Thank You,
Alicia
Alicia, I’m not sure what you are trying to accomplish. If you can explain in more detail that would be helpful.
Sorry,
It is my understanding you can not use the same item name or number. We get inventory shipped in and we also produce the same items. We need to use a different item name/ number for the assembly item. Once we build the item is there a way to tie it to the item name/ number that we mainly use?
Thank You,
Alicia
Alicia, I’m still not sure that I understand the question/problem. However, let me try this, tell me if I’m on target or not.
You are manufacturing an item, let’s call it “Widget”. You also sometimes buy that same item, the “Widget”, but you buy and sell it under a different name, “Cog”. You really have just one item, but sometimes you call it “Widget” and sometimes you call it “Cog”. There is only one balance on hand for them both together. Is that the situation?
If so, create an inventory assembly item “Widget”. Create a group item “Cog”, and assign it a component of one “Widget”. Now you can buy or sell either a Widget or a Cog, and you can build a Widget, but the quantity on hand you have is just one balance for them all together.
You’re close.
Sometimes we Buy the ‘bwidget’ from our parent company…at a higher cost, sometimes we Make the ‘mwidget’, but from the outside it should always look like the same part. So the customer is always ordering just a ‘widget’…the problem is that quickbooks only allows you to have 1 part number “widget”. Can we merge the ‘bwidget’ and ‘mwidget’ into a ‘widget’? so quantity on hand is one part number yet still distinguishing the cost of goods?
Alicia: If you want to track the two items separately, you have to have separate items. If you don’t distiguish between the items once you have them in hand, then it is one item. QuickBooks uses average costing – if you buy the item at one cost, but manufacture it at another cost, and it is one item, the cost is all rolled up into that one item. The total COGS value is always accurate, but if you sell the combined item it is drawing on the average cost.
That is a bit rambling, hope it makes some sense.
If it is just that you want to see the same name on the invoice you give the customer, but otherwise treat them totally separately, then add a “custom field” to the item list for “User Item ID” or something like that. Put the name of the item in that field – both items can have the same value. Add that column to the printed invoice in place of the “real” item ID. Then the customer sees this ID instead of the “real” ID.
Thank you so much for your help Charlie. The information you have given is very helpful.
I’ve accomplished the inventory component forecasting by downloading the list of inventory items to Excel spreadsheet. I run sales report each month on our finished goods, and log that data onto another spreadsheet that calculates our average weekly sales of all finished goods. I then link all the components of those finished goods to the average sales data. I group the components by vendor and assign the estimated lead time for each vendor. This spreadsheet can now tell me what quantity of each component is needed to fufill the expected demand (based on historical average) for finished goods. I took awhile to set up but it’s a pretty cheap and good substitute for one of the MRP systems you’re describing.
Lots of ways to solve the problem, Mike, and if you have one that works for you, that is great! Sounds like a lot of work, though, and a lot of people don’t have the time or the knowledge to do that. Which is why a canned software solution works for some. Glad you have something you like!