Managing Service Truck Inventory in QuickBooks

QuickBooks by itself does not support multiple locations. To do this properly you need to purchase an add-on program of some sort, which often will keep the inventory in a separate file. There are some simple workarounds that can be used, though. In this article I’ll explain one way that might work if you are trying to track parts that you have assigned to a service truck or other separate location.

Let’s start off with a simple item list. Just a few inventory parts. I’m oversimplifying things here to point out the process I’ll use.

I’m running an equipment repair business where I have service trucks that I send out to customer sites. My repair technicians will repair your equipment on site rather than bringing it back to our shop. The repair technicians check inventory out of my parts department and carry a significant dollar value of parts in each truck. At any given time I need to know:

1. What parts each truck has.
2. The value of the inventory that I have in the trucks.
3. The overall value of my entire inventory of parts

I don’t want to remove the parts from my total inventory count when I assign them to a truck. These parts represent a significant business asset that I want to control. Why? Here are a few reasons (there are many more):

• For accounting purposes, the parts in the trucks represent a asset value that is a part of my business – I cannot expense them when I put them in the truck.
• I need to control theft and waste. I want to be able to do an audit of a truck to see if that truck really does have the parts it should.
• If a truck is stolen, vandalized or destroyed, I need to know what the value of inventory was for insurance and accounting purposes.

Note that there are several very good service truck and field service management products available that work with QuickBooks that are listed in the Intuit Marketplace. In addition, Intuit distributes a field service management add-on to Enterprise. For the most part these are all fairly expensive (when compared to QuickBooks Premier, for example), but they also generally include many enhanced features that a service business could use. I’m describing a workaround that doesn’t involve additional cost (other than the time it takes to set up). Please note that this works with Premier or Enterprise, but not Pro.

Setting Preferences

To use QuickBooks properly we will want to set some preferences. Select Edit and then Preferences, select the Items & Inventory preference, and check the Quantity on Sales Orders preference.

This enables the Quantity Available calculation. If you are managing inventory with QuickBooks you know that you have a quantity on hand, which is the count of the items you have in your possession. This quantity goes up when you buy parts, and goes down when  you sell parts. The quantity available value is a management number (rather than an accounting number) – when you enable this preference it is the quantity that you have that isn’t committed to a sales order, or possibly to a pending build (if you use that preference and feature).

Using Sales Orders

We are going to use sales orders and the quantity available calculations to track the items that we have assigned to trucks but that have not been used on a job. These are not available in our parts department.

To start, create a customer record for each service truck. I would rather use an other name entry, but QuickBooks won’t let us use that in the approach I’m going to take. I prefer to track this by truck, rather than by employee, because any employee might take out a particular truck. I have created a customer type of service truck to identify these “customers”.

When parts are checked out to a truck, I’m going to add them to a sales order that I create for that truck.

I can have one perpetual sales order open for each truck if I wish, adding and deleting parts as they are consumed by the technician or issued to the truck. You also can issue a sales order for each parts issue if you wish, to give you some documentation on the actual transaction, but I find that this creates extra work and makes some steps more complicated.

If my technician says that some parts were used, I modify the quantity of that part in the sales order to reflect that. If I issue more parts, I add them to the sales order.

You may want to modify the sales order template that you use for the service trucks to remove the price/rate column, and possibly to add a feature such as a sales rep that represents transactions used for these trucks. This may be of use in some reports I’ll show later.

Effects of Sales Orders

How does adding a part to a sales order affect inventory? First of all, it is not a financial transaction. It does not decrease your quantity on hand, nor does it affect your financial statements. It is not a sale of the item, it is an allocation to the truck “customer”.

The main effect is that this impacts the quantity available, which is a management value. If I create an invoice you will see a small chart symbol in the quantity column.

If I click that I get a current availability window.

This shows that I have 100 of this item in stock, but that 22 of them are allocated to sales orders. That means that only 78 are available to be used in over the counter sales.

Inventory Reports

How do I know what parts should be on a truck? Just print the current truck sales order.

How do I know how many parts overall that I have committed to trucks or open sales orders? Run the inventory stock status by item report. You can see what has been allocated to open sales orders, and what is available for use elsewhere.

The on hand quantity still represents the total inventory valuation, both in my parts room as well as in the trucks. The available quantity represents the total inventory that is in the parts room. The on sales order quantity is the sum of the quantity on each truck as well as any sales orders I might be using for other sales activities.

Sales Orders for Trucks vs for Customers

If you only use sales orders for the trucks, this process works quite well. If, however, you are also using sales orders to manage your sales process for customers, things get a bit cloudy. The inventory stock status by item report shows the quantity on ALL sales orders, not just your trucks.

The reporting tools in QuickBooks can be very frustrating to use sometimes. I can’t always get the information the way I want from QuickBooks directly (which is why I write custom software). I tried adding fields to the truck sales order, such as a custom field or a sales rep (setting up a “truck” sales rep). But when I set up the inventory stock status by item report, and add a filter for those fields, it doesn’t change what is included in the report. That is because this report is a summary report, and summary reports can’t be filtered by data in transactions. So, here are a couple of workarounds if you are trying to get data on just the inventory that is in the trucks.

First, if you DO add a “sales rep” or customer field to the sales orders, AND you add a filter for the field in the inventory stock status by item report, if you double click on an item in the report to see the “QuickReport”, that report WILL show the effect of the filter. Confusing, and not quite what I want (it is just for one item), but this report includes the transactions so the filters work.

Second, you can look at a transaction based report like the open sales order by item report. Add a sales rep to the sales orders for just the truck orders, add a filter for that sales rep to the report.

The report will show just the sales orders for your “Truck” or “T” rep.

There are many other reports you can play with, and many variations of how you can try using sales orders to manage your service truck inventory. I’ve shown you one way that you can approach this without having to purchase additional software. This is a very simple approach to the problem. The Service Management add-on programs provide many more features that you may find very useful, and they should also be considered.