73-220 Excel Assignment 3 Summer, 2016 This assignment is due on Friday, July 22nd at 11:59pm. The file (S16220ExcelAssign3) containing the data for this assignment can be found on Blackboard and your solution (by completing the worksheet containing the data and using Solver) is to be submitted through Blackboard. When submitting your completed file, add your last name to the name of your file (e.g., S16220ExcelAssign3Miller) The problem for this assignment is a transportation problem and is to be solved using the approach used to solve the transportation problem during the July 14th class. The scenario for this problem is as follows: MnM, a manufacturer of ebikes, has its engines manufactured by four different companies (in four different foreign countries). These companies then ship these engines to MnM's three different assembly plants to be installed in its ebikes. MnM must not only pay the manufacturers for the production of these engines, it must also pay the shipping costs. The total costs (production and shipping) that MnM must pay for producing and shipping each engine from each company to each assembly plant, along with the monthly production capacities at each plant, and, the monthly engine requirements at each of MnM's assembly plants are given in the following table (and duplicated on the accompanying S16220ExcelAssign3 file) . Company W X Y Z Required Assembly Plants A B C Capacities $42 $57 $48 2000 $48 $56 $52 1500 $51 $58 $55 2300 $46 $49 $48 700 1600 2100 1300 Using Solver determine how many units should be produced at each company and shipped to each assembly plant so that total costs are minimized subject to the restrictions that: each assembly plant will get all the engines that it requires each company cannot not produce more than its capacity allows them to produce Company Y must produce and ship its capacity of 2300 Company Y cannot ship any engines to Assembly Plant C If you solved this problem correctly, your total cost should be $249,200. Excel Assignment 3 Company W X Y Z Required A $42 $48 $51 $46 1600 cost/engine: Assembly Plant B $57 $56 $58 $49 2100 C $48 $52 $55 $48 1300 Capacity 2000 1500 2300 700