Question

Can anyone please explain step by step how to solve this by excel solver cause the solver won't accept the binary word

A group of college students is planning a camping trip during the upcoming break. The group must hike several miles through the woods to get to the campsite, and anything that is needed on this trip must be packed in a knapsack and carried to the campsite. On particular student, Tina Shawl, has identified eight items that she would like to take on the trip, but the combined weight is too great to take all of them. She has decided to rate the utility of each item on a scale of 1 to 100, with 100 being the most beneficial. The item weights in pounds and their utility values are given below.

Item    1    2 3 4    5    6 7 8

Weight 8    1 7    6 3 12 5 14

Utility 80    20 50    55    50 75 30    70

Recognizing that the hike to the campsite is a long one, a limit of 35 pounds has been set as the maximum total weight of the items to be carried.

a) Formulate this as a 0-1 programming problem to maximize the total utility of the items carried.Solve this knapsack problem using a computer.

b) Suppose item number 3 is an extra battery pack, which may be used with several of the other items.Tina has decided that she will only take item number 5, a CD player, if she also takes item number 3.On the other hand, if she takes item number 3, she may or may not take item number 5.Modify this problem to reflect this and solve the new problem.

Answer 1

(a) Items Carried = 1,2,4,5,6,7 ; Total Utility = 310; Total Weight = 35 lb

ITEMS CARRIED
ITEM	UTILITY
1	80
2	20
4	55
5	50
6	75
7	30
TOAL	310

(b) Items Carried = 1,2,3,4,5,7 Total Utility = 285 Total Weight = 30 lb

ITEMS CARD
ITEMS	UTILITY
1	80
2	20
3	50
4	55
5	50
7	30
TOTAL	285

Excel solver screenshots of both parts of the problem are given below