QuickBooks Freight Allocator Utility

The Problem with Landed Costs

As George Keliher discussed in his article on Landed Cost with QuickBooks and Fishbowl Inventory, the “landed cost” of an item is the purchase cost of that item plus all the expenses of “landing” it to your business. This can include shipping, import taxes, agent fees, and more. The IRS wants you to calculate the landed cost of an item when you update your accounts.

Unfortunately, QuickBooks doesn’t make this easy. Often these costs aren’t included in the same bill as the items you’re receiving – you may pay a separate freight company, for example. If you receive multiple items in a bill and you have freight costs in a separate bill, how do you adjust the cost of the items? For example, here’s a bill (Figure 1) for several items from one vendor:

Figure 1: Bill showing several items from one vendor.

Along with this, I have a separate bill for shipping of the item (Figure 2):

Figure 2: Separate bill for shipping.

I want to allocate that $100.00 shipping bill to the various items I received. QuickBooks doesn’t provide a way to do this easily. I have to get a calculator out and apportion the values to each line item in the receipt and make an adjustment. This is a time-consuming manual chore.

QuickBooks Freight Allocator Utility

This is where the QuickBooks Freight Allocator utility by Karl Irvin can help. It allows you to easily allocate freight (and other costs) to each item in a bill, handling the allocation to the items automatically and making the appropriate adjusting entries.

The first thing we’ll do is create a “clearing” account to temporarily hold the freight costs. In my example (Figure 3), I’ve created an expense account called Shipping Cost Clearing Expense. As you can see in the freight bill above, when I enter the freight bill, I use this clearing account to hold the amount (rather than my normal “shipping” expense account).

In Preferences, you’ll need to set a preference in the Freight Allocator utility to identify this account as the clearing account that you’ll use.

Figure 3: Set your Preferences the first time to identify your clearing account.

Now we can use the QuickBooks Freight Allocator utility to apply the $100.00 of freight cost (which was posted to the clearing account) to the items in the bill.

This is a typical product from Karl Irvin, where you run through the steps as listed from top to bottom. If you’ve used any of his other utilities, you’ll find this very familiar.

As shown in Figure 3, first, connect to the QuickBooks company file. Next, you’ll click the Copy button to load the QuickBooks vendor list. This shouldn’t take long; most businesses have a relatively short list of vendors. You only have to do this the first time you run this utility, or whenever you’ve made changes to your vendor list in QuickBooks.

The next step is to retrieve the bills from QuickBooks you want to work with. You can filter the list of bills by vendor name, date range, or bill ref number range. In my example, I’ve selected a date range, and three bills show in the window. I’m applying a single freight bill to a single bill that has multiple items. The freight bill was for $100.00, so I enter “100.00” in the FrtToAllocate column for the appropriate bill.

Clicking the Allocate button will apply that $100.00 to the specified bill. You can see how the program allocates the cost by clicking the View Detail button.

Figure 4: How to allocate your freight cost.

As you can see in Figure 5, the program has taken that $100.00 and spread it out proportionally, based on the item received cost per line. The total cost of the bill was $2,510.00, the item that was received for a total of $10.00 was given $0.40 of the freight cost, and so forth.

Figure 5: The program takes an amount and spreads it out proportionally, based on the item received cost per line.

There are several things you can do to alter the results when looking at the detail window:

• You can change the amount to allocate.
• You can change the “allocation factor” to give higher weight in the allocation to any particular line. If some items are noticeably heavier than others, increase the allocation factor for that item and it will receive a higher proportion of the freight cost.

If you make any changes on this screen, you’ll click the AllocatedFrt button to apply them.

Note that you can apply a freight amount to multiple bills at the same time. For example, if the freight bill applied to two item bills, you can enter an amount to allocate for each of the bills, as shown Figure 6. In this case, you have to manually divide the amount to the different bills.

Figure 6: Applying a freight amount to multiple bills at the same time.

Results in QuickBooks

Let’s take a look at how this utility has updated the records in QuickBooks (Figure 7). Note the following:

• The total amount of the original bill remains the same.
• The received cost in the detail lines are adjusted as the utility had shown.
• A credit to the clearing account is added on the Expenses tab, to balance the adjustments to the received Items details.

Figure 7: Records are updated in QuickBooks.

Here’s the Expenses tab (Figure 8):

Figure 8: The Expenses tab.

If we look at the Account QuickReport for the clearing account (Figure 9) you can see two transactions. One is the original freight bill (which isn’t adjusted by this utility). The other is the clearing posting from the bill. The clearing account is “cleared,” brought back to zero, so there’s no expense showing in the financial statement for the freight charge – the amount has been moved into the asset account for the items that were received.

Figure 9: The transactions are displayed in the Account QuickReport.

I Like It!

Sure, you can do all of this manually: You can create the clearing account, post the freight bill to that (just as you do here), manually allocate the freight amount across the various items in the item bill/receipt, and then add the freight to the bill’s expense tab. However, what I find in most businesses is that it’s a chore that people skip because it takes too many steps. You have to calculate the proper freight amount across all of the items, and if you have a large number of detail lines in the receipt, it can be a pain in the rear. Things that are a hassle to do tend not to get done.

Karl Irvin’s QuickBooks Freight Allocator utility makes this a very simple task, and from what I see, it does the work flawlessly. There may be a few tweaks that could improve this, and I expect that Karl will be taking feedback from users on this to expand the program’s functionality. This is the first release of the program.

The Freight Allocator utility works with the US versions of QuickBooks Pro, Premier, and Enterprise, 2009 through 2014. The price is $79.00.