Question 1: 15 points The Wakefield plant orders cobalt and nickel ingots to meet weekly demand for castings. The supplier will send up to 1 truck per day. Each truck can ship a maximum of 25 tons of metal on the truck at a fixed cost of $60,000 per truck plus the material cost. The supplier also requires cobalt to be ordered in ton increments, but nickel can be ordered in partial tons. The supply chain team wants to determine the optimum inventory and ordering strategy based on the following demand and projected metal costs. Assume there are 2,000 lbs. in a ton. Day 3 Day 1 9.05 18.20 Day 2 7.55 16.40 5.15 13.70 Day 4 8.00 17.60 Day 5 2.15 3.10 Day 6 6.75 12.00 Demand (tons) Cobalt Nickel Cost (per lb.) Cobalt Nickel $13.62 $6.07 $13.61 $6.27 $13.78 $5.95 $13.58 $5.87 $13.82 $6.46 $13.80 $6.03 They are starting day one with an inventory of 10.5 tons of cobalt and 26.0 tons of nickel on hand. The warehouse has a max capacity of 50 tons. The carrying cost for each day is the average total tons in inventory * $8,000. Create an Excel model to determine the optimum ordering and inventory strategy to minimize overall cost. Question 1: 15 points The Wakefield plant orders cobalt and nickel ingots to meet weekly demand for castings. The supplier will send up to 1 truck per day. Each truck can ship a maximum of 25 tons of metal on the truck at a fixed cost of $60,000 per truck plus the material cost. The supplier also requires cobalt to be ordered in ton increments, but nickel can be ordered in partial tons. The supply chain team wants to determine the optimum inventory and ordering strategy based on the following demand and projected metal costs. Assume there are 2,000 lbs. in a ton. Day 3 Day 1 9.05 18.20 Day 2 7.55 16.40 5.15 13.70 Day 4 8.00 17.60 Day 5 2.15 3.10 Day 6 6.75 12.00 Demand (tons) Cobalt Nickel Cost (per lb.) Cobalt Nickel $13.62 $6.07 $13.61 $6.27 $13.78 $5.95 $13.58 $5.87 $13.82 $6.46 $13.80 $6.03 They are starting day one with an inventory of 10.5 tons of cobalt and 26.0 tons of nickel on hand. The warehouse has a max capacity of 50 tons. The carrying cost for each day is the average total tons in inventory * $8,000. Create an Excel model to determine the optimum ordering and inventory strategy to minimize overall cost