Question

You are trying to decide on the best mode of transportation for your company. You want to balance the cost of faster transportation with holding more inventory. This is because the amount of inventory you must hold is a function of lead time.

It costs $15 per unit per year to hold a product in inventory. The forecasted demand for this product is 10,000 units per year. The total amount of inventory is made up of two types of inventory: safety stock and in-transit. Calculations for determining the average amount of inventory for each type are given below.  

Safety Stock Cost = 2 x Average Demand during Lead Time * Holding Cost per Unit per Year

In-Transit Inventory Cost = Total Annual Demand x (Lead Time / 365) * Holding Cost per Unit per Year

Total Cost = Safety Stock Cost + In-Transit Inventory Cost + Transportation Cost

The following is data related to your transportation options.

Transportation Mode	Lead Time (Days)	Cost/Unit
Air	2	$6
Truck	7	$3.50
Rail	21	$2.75

1. Build a spreadsheet model that calculates the total cost of transportation for Air, Truck, and Rail transportation. Based on your total cost calculations, what mode of transportation would you recommend? What is the total cost of your recommendation?

2. How much would the cost per unit of the other two modes have to change in order to make you change your recommendation?

3. What would the lead time of Rail have to change to in order to make it the best option?
4. Use a one-way data table to show how the total cost of Air transportation changes as demand varies between 2,000 to 20,000 units. For what demand levels is it better to recommend Air transportation over your recommendation to Problem #1?

5. Create a two-way data table showing the total costs at varying levels of transportation cost per unit and lead time for Truck transportation. What does this tell you

6. How does changing the holding cost affect your recommendation to Problem #1?

7. You run a regression and find that your demand actually appears to be related to lead time according to the following equation:

Demand = 10,700 – 100*Lead Time

Assume that you sell this item for $45. Calculate Total Profit for each of the modes of transportation. Does this affect your recommendation to Problem #1? How sensitive is this decision?

SHOW EXCEL WORK

Answer 1

So,

based on the minimum total cost, 'Truck' seems to be the best option with a total cost = $43,630.14

(b)

The difference in total cost between 'Truck' and 'Air = 62,465.75 - 43,630.14 = 18835.61

So, the reduction in the cost of units for 'Air should be = 18835.61 / 10000 = $1.89

Similarly, the reduction in the cost per unit for 'Rail' in order to compete with 'Truck' should be = (53,390.41 - 43,630.14) / 10000 = $0.98

(c)

Use the goal-seek function as follows:

We will get the lead time of 'Rail' = 13 days when the difference becomes zero.

(d)Result (data-table)

In none of the demand range, the Air mode can be the most economical option. The Truck is always the least cost option as it seems.