Freight costs to Excel

[v0n-tussle]

Seeking your help if anyone here knows how to allocate freight costs to inventory using excel? any help is appreciated!

 

[Kurt Zamo]

I had to build a landed cost tracker from scratch for my first job out of college and this question gives me flashbacks. There is more than one way to skin a cat here but it comes down to finding a fair basis to spread the cost. The most common way is to allocate the total freight charge based on the value of each different item in the shipment. The trick is to calculate a freight rate per dollar and then apply it to each inventory line. What method makes the most sense for the kind of inventory you are receiving?

 

[Elliott.sam]

You can allocate freight costs in Excel by spreading the total freight across your inventory based on each item's cost or quantity, just figure out what percentage each item represents of the total, then multiply that by the freight cost to get its share, then add that amount to the item's cost if you want the full landed cost, I could help set up a simple formula for you, if you want

 

[doodlebun]

I had to build a landed cost tracker from scratch for my first job out of college and this question gives me flashbacks. There is more than one way to skin a cat here but it comes down to finding a fair basis to spread the cost. The most common way is to allocate the total freight charge based on the value of each different item in the shipment. The trick is to calculate a freight rate per dollar and then apply it to each inventory line. What method makes the most sense for the kind of inventory you are receiving?

Is there a simple formula or template that works well for mixed shipments? And if items vary a lot in weight or volume, does the value-based method still hold up? Thanks

 

[Kurt Zamo]

If you're dealing with mixed shipments where weight or volume varies a lot, value-based allocation might not reflect the true freight burden for each item. You could try building a weighted average based on either weight or volume instead. That way, heavier or bulkier items absorb more of the freight cost.

Are you tracking weight or volume per item in your sheet already? If not, that's probably the first thing to lock down before choosing a method. Have you also landed on a format for your tracker yet or still testing approaches? It would be helpful to know what kind of items you're working with.