Problems:
Linear Programming in Excel/Solver
Solver to set up and solve the problem below. Please make sure to embed the formulation to the Linear Programming problem within the Excel solution. In addition to the typical supply and demand constraints that you need to include, please make sure to also account for the constraints which will ensure that locations 3, 4, 5, and 6 each receive at least 5 cars.
The Rent-a-dent car rentalcompany allows its customers to pick up a rental car at onelocation and return it to any of its locations. Currently 2locations(1,2) have 16 and 18 surplus cars and fourlocations(3,4,5,6) each need 10 cars. The cost of getting thesurplus cars from locations 1 and 2 to the other locations aresummarized in the following table.
location3 location4 location5 location6
Location1 $54 $17 $23 $30
Location2 $24 $18 $19 $31
Because 34 surplus cars are available at locations 1 and 2 and 40 cars are needed at locations 3,4,5 and 6, some locations will not receive as many cars as they need. However, management wants tomake sure that all the surplus cars are sent where they are needed and that each location needing cars receives at least five.
a. Formulate an LP model for this problem
b. Create a spreadsheet model for this problem and solve it using Solver.
c. What is the optimal solution?