Allied Freight Input data Unit shipping costs From Warehouse 1 Warehouse2 To Customer 1 Customer 2 Customer 3 $15 $35 $25 $10 $30 $40 Shipping plan and constraints on demand and supply To Region 1 Region 2 Region 3 Total shipped From Warehouse 1 0 10 30 40 Warehouse 2 30 0 0 30 Total Received 30 10 30 <= <= <= Demand 30 30 30 0 0 0 Penalty for Unmet Demand Each Unit Unmet Demand Total Penalty Objective to minimize Total Cost Customer 1 Customer 2 Customer 3 $90 $80 $110 0 20 0 1600 3000 <= <= Capacity 40 30 Oneway analysis for Solver model in Sheet1 worksheet Total_Cost Input (cell $E$23) values along side, output cell(s) along top $30 $40 $50 $60 $70 $80 $90 $100 $110 $120 $130 2200 2400 2600 2800 3000 3000 3000 3000 3000 3000 3000 1 Sensitivity of Total_Cost to Input Data for chart Total_Cost When you select an output from the dropdown list in cell $K$4, the chart will adapt to that output. 2200 2400 2600 2800 3000 3000 3000 3000 3000 3000 3000 Sensitivity of Total_Cost to Input 3500 3000 2500 2000 1500 1000 500 0 $30 $40 $50 $60 $70 $80 Input ($E$23) $90 $100 $110 $120 $130 Oneway analysis for Solver model in Sheet1 worksheet Total_Cost Input (cell $H$15) values along side, output cell(s) along top 10 20 30 40 50 60 70 4300 3500 3000 2500 2000 1950 1950 1 Sensitivity of Total_Cost to Input Data for chart Total_Cost When you select an output from the dropdown list in cell $K$4, the chart will adapt to that output. 4300 3500 3000 2500 2000 1950 1950 Sensitivity of Total_Cost to Input 5000 4500 4000 3500 3000 2500 2000 1500 1000 500 0 10 20 30 40 Input ($H$15) 50 60 70 Allied Freight supplies goods to three customers, who each require 30 units. The company has two warehouses. In warehouse 1, 40 units are available, and in warehouse 2, 30 units are available. The costs of shipping one unit from each warehouse to each customer are shown below. There is a penalty for each unsatisfied customer unit of demand - with customer 1, a penalty cost of $90 is incurred; with customer 2, $80; and with customer 3, $110. To From Warehouse 1 Warehouse 2 Penalty cost Customer 1 $15 $10 $90 Customer 2 $35 $30 $80 Customer 3 $25 $40 $110 a. Use SolverTable to see what happens to the shipping cost, penalty cost and total cost as all the customer demand simultaneously change by the same percentage. Vary the percentages from -25% to +10% in steps of 5%