Sample Excel Sheet for Freight Costing & Inventory Cost Reduction

Some companies become so concerned with their inventory holding costs that they end up purchasing just enough to fulfill their existing orders, and nothing more. While this may seem like a good way to avoid holding too much inventory, in the end it does nothing more than impact a company’s ability to reduce its inventory costs. In essence, these companies aren’t using their volumes to maximize their purchasing power. Therefore, I’ve decided to include a sample excel sheet for freight costing & inventory cost reduction. The excel sheet shows how companies can better measure their inventory holding costs, versus their part savings, when purchasing higher volumes.

The excel sheet starts by showing the costs of a company buying a widget and incurring a freight cost to get that part into their warehouse. The company’s approach is to purchase minimal volume of parts in order to reduce its inventory holding costs. If fewer parts remain at the end of the month, then the company believes they have successfully reduced these costs. However, this ignores the price reduction the company could accrue from purchasing larger volumes and the lower freight costs by shipping larger amounts. 

Typically, inventory holding costs are approximately 3%. These costs are based on a number of different inventory cost drivers. In order for the company to derive a benefit from purchasing more, and holding parts longer, the savings must be higher than the monthly holding costs. In this case, there is one rule to apply: If the savings accrued in price & freight reduction is greater than your inventory holding costs of 3%, then you’ll come out on top. In our example, we’ll review what happens if the company were to purchase enough for three months of inventory. So, how does this excel sheet work? 

Sample Excel Sheet for Freight Costing & Inventory Cost Reduction  

PART 1: For part 1, I’ve stuck with 3% as the company’s holding costs. Next, I’ve assumed that if the company purchases a higher volume, they would reduce their per-unit freight costs by 5%. Finally, I’ve stuck with an example where the company has successfully negotiated a 10% price reduction on parts with its vendor. You can input different values for all of these variables. For instance, if your company runs an extremely efficient inventory, then your monthly carrying charges might be lower than this 3%. Also, if your company’s purchasing department is strong in negotiation, it might be able to reduce its per-unit freight costs, and its pricing, by an even higher percentage. All of these values are found in the “yellow” shaded boxes below in the excel sheet.

PART 2: For Part 2, I’ve used a fictitious transaction. In this case, the company’s initial purchase price is $52.00 (for a given widget). Their freight costs per-unit is $5.00 (the price to get parts into their warehouse). Both the company’s price and freight cost are in the “Blue” highlighted sections near the title “original conditions”. Again, you can input any value you want in these Blue sections below. The company’s sub-total is $57.00, which is the price of $52.00 plus the per-unit freight cost of $5.00. However, once the company adds its 3% inventory holding costs, their total cost of ownership at the end of the month is $58.71. Part 2 is shown below.Holding Inventory Longer Versus Savings on Price & Freight 

PART 3: Part 3 analyzes the benefit of the 10% price reduction and the 5% reduction in freight costs once the company purchases the higher volume. It also assumes that some, if not all, of the inventory will be held for a total of three months. The company’s concern is whether the savings (price & freight) will exceed the company's inventory holding costs of 3% for the next 3 months. This means that each subsequent month is charged a 3% holding cost. Part 3 is shown below.

What does part 3 show? 

* Company uses its purchasing power to reduce its pricing by 10% and its per-unit freight cost of 5%.

* Their new price is $46.80 per-unit ($52.00 * 10% = $5.20 savings) and their new freight cost is $4.75 per-unit ($5.00 * 5% = $0.25 savings). 

* Each month the company accrues a monthly holding charge of 3%. 

• Month 1, the company incurs 3% holding costs and its month 1 ownership cost = $53.10 ($51.55 x 3% = $1.55)
• Month 2, the company incurs 3% holding costs and its month 2 ownership cost = $54.69 ($53.10 x 3% = $1.59)
• Month 3, the company incurs 3% holding costs and its month 3 ownership cost = $56.33 ($54.69 x 3% = $1.64) 

* Even with holding inventory longer, the company still has a net savings benefit of $2.38/Unit thanks to the 10% price reduction and 5% freight cost reduction. This is calculated by taking the original price of $58.71 (from step 1 above) and deducting Month 3’s cost of $56.33 (from step 3). $58.71 - $56.33 = $2.38 savings. All three parts are put together below. 

Granted, when a company holds parts longer, it does risk encountering issues with obsolete, outdated and damaged inventory. These play a vital role in increasing a company’s inventory cost of ownership and in increasing a company’s holding costs. If companies can improve their sales forecast accuracy, then obsolete and outdated parts are less likely to have an impact. In addition, if a company can guard against damaged inventory by having clearly defined handling procedures, and mitigating the amount of times they move inventory internally, then these costs can also be avoided. However, the purpose of this sample excel sheet for freight costing & inventory cost reduction is to properly answer that question: "If we purchase more, will the savings more than make up for the holding costs?" Use the following excel sheet to help answer that question:Download Freightcosting

This post is part of a larger approach to reducing freight costs. The following article provides a number of different approaches to reducing freight costs. Essential Ways to Reduce Freight Costs on Incoming & Outgoing Shipments