Answer Happy

solve using excel and showing formulas
f4 transportation data.xlsx 4. (15 pts.) Zen Power is an energy supplier with three electric power plants that provide energy to four cities (f4 transportation data). Each power plant has a limit on the amount of energy it can supply. The energy supply of each plant, the energy demand of each city, and the transportation cost of supplying energy from each plant each city is given in the Excel data file. Formulate a transportation optimization problem and answer the following questions. Clearly show your Excel analysis work and state your solutions in the same Excel file in the appropriate highlighted cells: (Hint: This is a transportation problem. The transportation cost in this problem is not the cost of physically moving materials but rather the infrastructure cost of sending electricity to different locations) . (7.5 pts.) Appropriate setup of the optimization problem in Excel using Solver (including decision variables, objective function and constraints). (1.5 pts.) What is the total cost with the optimal transportation plan? ● (2 pts.) How many million kwh of energy is moved from Plant 1 to City 1,2,3 and 4 respectively? . (2 pts.) How many million kwh of energy is moved from Plant 2 to City 1,2,3 and 4 respectively? (2 pts.) How many million kwh of energy is moved from Plant 3 to City 1,2,3 and 4 respectively? Attach File Browse Local Files QUESTION 30 Browse Content Collection 15 points
From plant 1 plant 2 plant 3 Demand (million kwh) Transportation Cost/million kwh city 1 8 9 14 45 city 2 6 9 20 To city 3 10 16 30 city 4 9 7 5 30 What is the total cost with the optimal transportation plan? How many million kwh of energy is moved from Plant 1 to City 1,2,3 and 4 respectively? How many million kwh of energy is moved from Plant 2 to City 1,2,3 and 4 respectively? How many million kwh of energy is moved from Plant 3 to City 1,2,3 and 4 respectively? Supply (million kwh) 35 50 40