Avery Appliances is an appliance manufacturer that hopes to hire a third-party logistics provider to help with distribution of its products. Avery has three plants and serves three markets. The attached Excel file provides information on the capacity available at each plant, the demand in each market, and the cost to ship from each plant to each market for two different logistics providers. Avery Appliance student.xlsx Avery Appliances is considering 3 options for its logistics provider strategy: a) ALPHA Logistics has submitted a bid which details the costs to ship a unit from each plant to each market given in the table in the "ALPHA" worksheet. The table also includes the total monthly production volume at each plant and the total demand in each market. Use Excel Solver to determine the optimal total transportation cost in dollars if Avery Appliances hires ALPHA Logistics. b) Avery Appliances has also solicited a bid from OMEGA Logistics. OMEGA provides Avery with the alternate transportation cost plan given in the "OMEGA" worksheet ( X cells indicate that OMEGA Logistics will not ship between that plant and that market). Use Excel Solver to determine the optimal total transportation cost in dollars if Avery Appliances hires OMEGA Logistics. (Hint: how do you require no shipping along certain routes in Solver? If your solution allows shipping along routes with an " X " it is incorrect.) c) Now consider that Avery Appliances has the flexibility to hire both third party logistics providers. Use Excel Solver to modify your original model to determine the optimal total transportation cost in dollars if both providers are used. (Hint: you can solve this part with only 9 decision variables. Suppose you want to ship any units along the route from Plant 1 to Market A, which provider would you use for that route? What would be the appropriate cost to put in B4? How can you extend this logic for every cell in B4:D6?) Suppose that ALPHA Logistics charges a fixed management fee of $4,000 and OMEGA logistics charges a fixed management fee of $6,000. If both providers are hired the total fee is $10,000. To minimize the total cost, fixed management fee + transportation cost, should Avery Appliances hire only ALPHA, only OMEGA, or both logistics providers? Enter the optimal total cost (transportation cost + fixed management fee) for the best of the three options