Global supply chain management,

please help with the excel question below

Thank you

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: Probability Trucks Required 1 2 3 4 5 0.2 0.3 0.3 0.1 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? COORDINATING FREIGHT DATA Manufacture (Steel) Transporter (Rapid) Trucks Required 1 2 Truck cost: $ 120.00 Truck Reserve Cost: $ 60.00 Probability Cumulative Probability 0.2 0.2 0.3 0.5 0.3 0.8 0.1 0.9 0.1 1 3 Spot Truck Cost/ truck $ 200.00 Penalty Cost: $ 4 5 ANALYSIS Question a How many trucks should Rapid have? Question b. Given Quick's decision. what is Steel's total cost? Q= Demand Probability Spot Truck Cost Cost Smart's Order Revenue Cost to Reserve Profit Probability 1 2 3 4 5 Truck Cost 0.2 0.3 0.3 0.1 0.1 1 2 3 3 4 5 0.2 0.3 0.3 0.1 0.1 Expected profit Expected Costa C_underage Coverage Critical Ratio: --> Check against cumulative probability Have 3 trucks