SOLVE IN EXCEL USING SOLVER SHOW ALL FORMULAS USED AND THE SOLVER:

P06_60.xlsx

60. Ford has four automobile plants. Each is capable of producing the Focus, Mustang, or Taurus, but it can produce only one of these cars. The fixed cost of operating each plant for a year and the variable cost of producing a car of each type at each plant are given in the file P06_60.xlsx. Ford faces the following restrictions: Each plant can produce only one type of car. The total production of each type of car must be at a single plant. For example, if any Mustangs are made at plant 1, then all Mustangs must be made there. Each year, Ford must produce five million of each type of car. a. Determine how to minimize the annual cost of producing these cars. A B C D E 1 Ford auto data 2 3 Fixed cost 4 (in $billions) 5 Plant 1 $50 6 Plant 2 $40 7 Plant 3 $20 8 Plant 4 $10 Variable costs (in $1000s) Focus Mustang Taurus $11.5 $21.3 $23.3 $11.7 $20.9 $22.9 $12.4 $21.4 $23.4 $12.7 $22.5 $24.5