EXCEL ASSIGNMENTS SUPPLY CHAIN MANAGEMENT

Transportation Problem

The Rapid trucking company works with Steel, a shipper. The trucking contract requires Rapid to provide or decline trucks requested by Steel with a payment per shipment of $120 per truck provided. To provide trucks to Steel, Rapid has to plan in advance and reserve trucks the previous day at a cost per truck of $60.

If Steel cannot get demand satisfied by Rapid, it will cost $200 to get the shipment completed by a spot carrier.

The demand for trucks at Steel each day can be described by the following distribution:

Trucks Required	Probability
1	0.2
2	0.3
3	0.3
4	0.1
5	0.1

a. Given this contract, what is the profit-maximizing number of trucks Rapid should reserve, and what is the associated expected profit for Rapid?

b. Given Rapid’s decision in 2a, what is Steel’s expected cost?

c. A consultant offers the following contract: Rapid would reserve five trucks each day. For all trucks that Steel uses, Steel would pay Rapid at the rate of $120 per truck. It would cost Rapid $60 per truck to reserve trucks the previous day. If trucks are not used by Steel, Steel would guarantee to pay for up to two trucks that are not used at the rate of $60 per day.

(Thus, for example if Steel only uses two trucks, Steel would pay Rapid $120 per truck for two trucks. In addition, since Rapid would have three trucks unused, Steel would pay for two of those three trucks at the rate of $60 per truck. On the other hand, if Steel used four trucks, Steel would pay Rapid $120 per truck for four trucks. In addition, since Rapid would have one truck unused, Steel would pay for one truck at the rate of $60 per truck.) What is the impact of this contract on Rapid’s expected profit?

What is the impact of this contract on Steel’s expected costs?

Is this contract Pareto improving over the original system? Explain why or why not.

Warehousing Problem

The Prompt Corporation purchases product from two suppliers, A and B. Supplier A is located in Austin, TX, and supplier B in Carthage, TN. Supplier A supplies component a and supplier B supplies component b. These components are used to make gizmos of two types, I and II. Gizmo I is made in Helena, MT, and Gizmo II is made in Duluth, MN. Each manufacturing plant makes 50 gizmos a day. Each gizmo requires 1 unit each of components a and b.

Currently loads are shipped full truckload from suppliers directly to plants. Trucks used have a capacity of 500 units and cost $2,500 per truckload shipped. Holding cost per com- ponent per day is $0.50. Transit time per route is 4 days.

a. Provide the total supply chain costs for this system.

b. Prompt has realized that it can obtain larger trucks with a capacity of 1,000 units for a cost of $3,500 per route and the same transit time of 4 days per route. If Prompt has these trucks, should all shipments be full truckload or less-than-full truckload? What will be the associated total supply chain costs to use these trucks with the appropriate level of loads on these trucks? Are the costs lower than (3a)? Explain why or why not.