QuickBooks Manufacturing Forecasts: Component Demand

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.

Related posts:

1. QuickBooks Manufacturing Tutorial
2. Manufacturing WIP in QuickBooks
3. QuickBooks Manufacturing Bill of Materials
4. Why Won’t QuickBooks Let Me Build This Assembly?
5. Tracking QuickBooks BOM Revisions

Tags: Bill of Material, Forecast, Inventory

Category: Manufacturing and Inventory, Working with QuickBooks