QuickBooks

QuickBooks Freight Allocator Utility

Written by Charlie Russell

How do you allocate freight costs to bills for inventory items that you’ve received in QuickBooks? If you want to enter a “landed cost” for these items, it can be a chore. Let’s take a look at a program that can simplify this task – the QuickBooks Freight Allocator utility from Karl Irvin.

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:

Bill for items received in QuickBooks

Figure 1: Bill showing several items from one vendor.

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

Shipping bill in QuickBooks

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 Preferencesyou’ll need to set a preference in the Freight Allocator utility to identify this account as the clearing account that you’ll use.

QuickBooks Freight Allocator Utility preference

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.

QuickBooks Freight Allocator Utility

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.

QuickBooks Freight Allocator Utility details

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.

Applying to multiple 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.
Adjusted Bill in QuickBooks

Figure 7: Records are updated in QuickBooks.

Here’s the Expenses tab (Figure 8):

Adjusted Bill in QuickBooks: Expenses detail

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.

Clearing Account in QuickBooks

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.

About the author

Charlie Russell

Charlie Russell has been involved with the small business software industry since the mid 70's, and remembers releasing his first commercial accounting software product when you had an 8-bit microcomputer with one 8 inch floppy disk drive. He has a special interest in inventory and manufacturing software for small businesses. Charlie is a Certified Advanced QuickBooks ProAdvisor with additional certifications for QuickBooks Online and QuickBooks Enterprise, as well as being a Xero Certified Partner. Charlie started blogging about QuickBooks in 2008 (Practical QuickBooks) and has been writing for the Accountex Report (formerly the Sleeter Report) since 2011. He retired from accounting and QuickBooks activities in early 2018.

Visit his CCRSoftware web site for information about his QuickBooks add-on products. He is also the author of the California Wildflower Hikes blog.

14 Comments

  • Like all of Karl’s utilities it is a nice one. I understand that allocating freight based upon the item cost is really the only way this kind of thing can work, and for most folks it will be fine.

    But if you are ordering things which have a high price/low weight along with low price/high weight it just does do justice to the requirement – so I think that should be something noted for users to think about.

    It would be nice if you could enter in a field the % of freight to be allocated and the total amount of freight, rather than allowing the program to make those decisions for you.

    Jim

    • dang it, should read
      But if you are ordering things which have a high price/low weight along with low price/high weight it just does **not** do justice to the requirement

    • Thank you, Jim. Note a few things:

      -You can enter a factor to have some items get a greater percentage than others.
      -This is the first release, I know that Karl is looking for feedback from users and commentors about what changes or improvements people want.
      -Everyone has their own way of doing things, this is one way that will help some people.
      -Karl mentioned to me that he already had requests from people to be able to store a factor of some sort in a custom field, that could be applied. That doesn’t mean he’ll be doing that, but it is something that has been asked for.

      I have a number of things that could be added or changed that I have (and will be) passing on to Karl. It’s version 1, a good start!

  • Hi Charlie, Jim. I’m glad someone like Karl came out with a tool that solves for this issue that many clients run into. I’m a devotee of Karl’s utilities, but like Charlie, I see room for improvement. It’s still only based on item amount. How about quantity, weight or some other metric? There are also still a lot of steps involved, and that you have to, ‘manually divide the amount to different bills’ only expands the potential for errors. As an inventory specialist, I’d say it plugs one of the holes in the QuickBooks inventory workflow process, namely spreading the freight across the items based only on cost, but could still be greatly improved. Come on Karl; show us some more magic!

  • This is a great tool. Thanks for writing about it. I’m assuming it will work for other costs too? not just freight? I have a client that uses a WIP account for all costs related to a publication. The publication is set up as a job so she can get a report of total costs. Then she does an inventory adjustment to reduce WIP and increase the actual inventory for the total cost to publish. This seems like it would be way easier.

    • The main issue, MB, is that there is one clearing account that is used. So you would have to get those costs, which may be on different bills for different kinds of expenses, posted to that same clearing account. That can be done, but you just have to think it out.

  • We bought Freight Allocator tool. When try to use it, date is restricted to Nov 2013 and can’t actually load even though there is a bill for Nov 2013.
    Although we bought the Freight Allocator, the screen says: UnlicensedDemo
    Perhaps that is why it is only accepting Nov 2013 transactions.
    We also have licensing question for 2nd user.

    • Pat, you need to direct that question to the developer of the product. It sounds like you haven’t entered the license/registration information into the program.

  • We purchased the utility and are using it but we have one issue. We receive our freight charges directly on our vendors’ bills. We need a workaround that will allow us to allocate the freight but still have the total reflect properly so that the bills show accurately and distribute to the proper accounts at the same time. Any suggestions would be appreciated.

  • We are using Quickbooks Enterprise. Our main issue isn’t the time it takes to manually distribute the freight costs among the items on the bill, but the value discrepancy created in our Inventory Offset account because now the $ on the bill doesn’t match the $ for the item receipt. Not only do we have to manually calculate and distribute freight costs to each item, but also we have to go back and edit the PO to reflect new values. Then we have to un-associate/save/re-associate the item receipt to the PO to force it to recalculate values assigned to the receipt. Is there a better way?

    • I don’t have a good answer for you at this time, Shannon. I greatly dislike the Enhanced Inventory Receiving feature and usually recommend that people not use it due to multiple problems that can occur. It has multiple flaws. Unfortunately, once you turn it on, you can’t turn it off. I have several articles here about issues with EIR…