How to Track Truck Inventory On A Spreadsheet

As you begin to distribute more merchandise, inventory turnover increases in both your warehouse and trucks. Therefore, the probability that your tuck inventory gets stolen, broken or goes to waste increases dramatically. That is why you should have a way to track truck inventory each morning, and track what happens with this inventory throughout the day.

Here are 6 essential steps that you can take to track inventory on your trucks and quantify Route Sales Representative performance on the inventory spreadsheet below.

Click to download the Free Inventory Spreadsheet

Step #1: Track truck inventory on each truck

Each morning your warehouse manager/checker picks, packs and loads inventory into each truck. The product quantities that your Route Sales Representative (RSR) leaves with in the morning should be recorded in the Start Qty. column on the inventory spreadsheet.

Step #2: Track quantities sold throughout the day

At each stop your RSR generates invoices for your customers. These invoices equate to sales. The quantities of each sold product should be recorded in the Qty. Sold column on the inventory spreadsheet.

Step #3: Track your dumps

Throughout the day, your RSR credits customers for stale, damaged or expired merchandise. When this merchandise comes back to the warehouse, it is not inventoried to be resold. Instead it is dumped and recorded in your accounting system as a loss. To track dumps out of each truck, input the dumped quantities into the dump column on the inventory spreadsheet.

Step #4: Record your returns

Just like there are dumps that won’t be inventoried when returned to the warehouse, there is also good merchandise returned that does get inventoried. Good returns should be counted and recorded in the Returns column on the inventory spreadsheet.

Step #5: Count the ending inventory

When the RSR comes back from his route, the warehouse manager/checker will count what is left on the truck as it gets unloaded. Whatever is left on the truck should be recorded in the end inventory column on the inventory spreadsheet.

Step #6: Quantify RSR Performance

In the inventory spreadsheet, the final component is the over/short column. The over short column is automatically calculated by the spreadsheet when all other values are inputted. If the driver is short, then the truck inventory was likely either stolen or lost. If the driver is over, then he over requested inventory or had inventory returned by customers.

Conclusion

Distribution companies strive to maximize profits while minimizing shrinkage and waste. Tracking your merchandise could mean the difference between success and failure.

So, you should start by downloading the Truck Inventory Spreadsheet here. And, consistently filling out each column to determine Route Sales Representative performance. If you are looking for something more fast easy to use to track truck inventory check our QuickBooks DSD Software