Global supply chain management,

please help with the excel question below

Thank you

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. Gizmol (50 units /day) Component a 500 units Demand (units/day) Austin Helena Component a Component b 50 50 Shipping Quantity (unit/shipment) 500 500 500 units 500 units Gizmo II (50 units /day) Demand (units/day) Duluth Component b Component a Component b Shipping Quantity (unit/shipment) 50 500 50 500 Carthage 500 units Total Supply Chain Cost: /day Holding cost (h) $ 0.50 per component per day Cycle Stock Costs (hQ/2) In-Transit Inventory Costs (hLD) 500 components 2,500.00 each time Helena Duluth Helena Truck capacity (C) Truck shipment cost (K) Delivery Lead Time (L) Duluth $ 4 days Component a Component b Component a Component b Total Cycle Stock Costs Total Cycle Stock Costs Transport Cost (KD/Q) Helena Duluth Component a Component b Total Transport Costs