This articles contain 2 parts:
- Manual Process to include “landed cost” (which is the cost of inventory including all freight, insurance, and customs charges) in each inventory item’s cost. This is with the sole purpose of having a real accurate Inventory value, thus reporting the accurate COGS and profit margin.
- Using a 3rd party tool called Freight Allocator by Karl Irvin (q2q.us)
So first let me disclose that in the Intuit support site: http://support.quickbooks.intuit.com/support/articles/HOW12901 and the procedure is described as:
“1) Enter a Bill (to your Inventory Vendor) using standard Inventory Parts.
2) Include the freight as part of the cost of the inventory parts, spread out as applicable (for instance, with an item cost of $5.00 and a freight charge of $0.50, change the cost to $5.50).
3) On the Expenses tab, enter a Negative amount using an offset account equal to the total freight for all inventory listed. This will cause the bill to be the appropriate price for the original vendor.
4) Create the 2nd Bill to the freight vendor for the amount of the freight. Have it go to the same offset account that we put as a negative on the first bill.
The secondary bill will zero out the account, so the only expense will be recognized when the item is sold per normal cost of goods sold process for inventory items.”
To date, I have not been able to get away with simply giving this link to my clients and somehow they figure it out using the above description. So I will show you with screenshots, this procedure, step by step:
First, this is the scenario: We purchased 200 screwdrivers for $5 each, and 100 hammers for $7 each, so the bill would normally look like this:
But I also have two additional cost components: Packaging costs of the products which cost me $2 for each hammer and $1.25 for each screwdriver; and Shipping Cost, which was $653 to have them couriered to our warehouse. So that being said, those bills will probably look like this:
So, the issue with this is that the Inventory Costs will not include those additional costs which are referred to the “Landed Cost”, so an Inventory Valuation Report would look like this:
Inventory Value for $1700?? What happened to the $450 Packaging Costs and $653 Freight Costs? Well they are sitting there as an expense in the P&L:
The problem is that the Gross Profit for this product will be wrong! So how do we fix it?
First, you need to determine how much of the $653 Freight cost will be allocated to each product type… assuming that scredrivers and hammers cost the same to transport because they weight the same or take-up the same space, we’ll simply divide $653 by 300 = $2.17667. Then, we’ll take the packaging costs that were already broken down per unit and add them to the inventory costs getting us the following landed costs:
- Screwdriver $5 + $1.25 Packaging + $2.17667 Freight = $8.42667 Landed Cost
- Hammer $7 + $2 Packaging + $2.17667 Freight = $11.17667 Landed Coast
Great, now on to QuickBooks… So the original Bill needs to actually look like this:
So, the last step is to balance the bill so it goes back to $1,700 its real total, and we do that by using the same accounts that were used on the bills or checks paid to the packaging costs and the freight costs, and we’ll do that with negative amounts:
Now, we got the correct inventory valuation, check our this report:
So the Profit Margins will be reported Correctly as Cost of Goods Sold will not be affected immediately by Freight or Packaging cost, rather it will be affected when the actual item is sold. Which makes it much easier to calculate true Profit by Customer:Job and true item profitability.
Of course, my example Included two items and very simple calculations, plus this process is both time consuming… and like all manual processed, it is error-prone. So my good friend Karl Irvin came to the rescue and developed Freight Allocator, I will show you how this works.
With the same example, leaving the original bill at $1,700, I’ll launch the tool, and it looks like this:
After Syncing the Company File, Exporting the Vendor List, and Selecting the Bill.. you need to input the allocatable amount in the “FrToAllocate” field:
To simplify things, all external costs that affect the landed costs needs to be added in there, without regard of individual proportion or weight impact on the allocated Freight Cost. This tool will automatically allocated this cost based on the inventory cost, but you can change the allocation factor if you want to change the weight/proportion allocable in the “View Detail” Window:
And Lastly, click on “Update” and done! The Bill now looks like this:
Note: This tool will use an account called “Freight Clearing” and all bills and checks with expenses that add to landed costs such as:
- Outsourced Assembly
And others… Basically any expenses that will be allocated using this tolls needs to clear this account down to zero.
My opinion, is the product is a HOME RUN, ready to use, fast and well worth the $99. The best news is that this has been on the market for just months, as more and more users add their 2-cents, Karl will probably be adding features to help make the cost allocation much more precise. For now, the simplicity of it, may prove to be the most effective.
I would ask Karl, if its possible to bring in a Custom Field from QuickBooks Enterprise that would have the weight per unit, and then it could be used to calculate the Factor.. this is a bit sophisticated, but some of my clients may need this!