Formulate the following problem as a linear program, and solve using the Excel S
ID: 361884 • Letter: F
Question
Formulate the following problem as a linear program, and solve using the Excel Solver. (20 pts) Many Ann Arborites head to Gallup Park at some point during the summer months. A popular activity is getting out on the Huron River on the various watercraft that the park rents. However, the boats are aging, and Dayna, the recreation manager would like to take advantage of winter sales to replace the fleet. Her goal is to maximize the potential enjoyment of park’s visitors at any given time. Dayna has a budget of $20,000 dollars, and she would like to decide how many one-person kayaks, two-person kayaks, three-person canoes, and two-person paddleboats to purchase. While she doesn’t quite know what optimization is, she heard you learned something about it in IOE 202 and has asked for your help. The boats cost $300, $450, $800, and $500, respectively. While people have different preferences (or utility values) for the boats, she estimates that in general, individuals have utilities of 20, 16, 12, and 12 for riding on each of the boats, respectively. Boats that hold more than one person accrue utility for each passenger. Based on available rescue capacity, Dayna would like a maximum of 150 people to be on the river at any one time. The park only has space to store 10 paddleboats and would like to stay under 20 canoes so that the staff can transport them all at the same time. She would also like to have at least double the number of one-person kayaks as two-person kayaks.
Explanation / Answer
answer --Given problem can be formulated as below
Let the different variables are a1, a2, a3, b1, b2, b3, c1, c2 and c3.
Minimize Cost = 6a1 + 9a2 + 100a3 + 12b1 + 3b2 + 5b3 + 4c1 + 8c2 + 11c3
subject to
a1 + a2 + a3 <= 130
b1 + b2 + b3 <= 70
c1 + c2 + c3 <= 100
a1 + b1 + c1 <= 80
a2 + b2 + c2 <= 110
a3 + b3 + c3 <= 60
Solver Solution
Solver Solution
a1 a2 a3 b1 b2 b3 c1 c2 c3 Total Demand
Decision Variables 0 80 0 0 10 60 80 20 0
Rental 6 9 100 12 3 5 4 8 11 1530
Constraint 1 1 1 1 0 0 0 0 0 0 80 130
Constraint 2 0 0 0 1 1 1 0 0 0 70 70
Constraint 3 0 0 0 0 0 0 1 1 1 100 100
Constraint 4 1 0 0 1 0 0 1 0 0 80 80
Constraint 5 0 1 0 0 1 0 0 1 0 110 110
Constraint 6 0 0 1 0 0 1 0 0 1 60 60
Different variable values are
a1 0
a2 80
a3 0
b1 0
b2 10
b3 60
c1 80
c2 20
c3 0