APEC 1201 : Exercise 7 Economics is the study of how to make choices that maximize an objective, while subject to constraints. This assignment is an example of an economic choice that does not use money. In the sheet "Solver Set Up" you are running a series of blood donation clinics. You have four cities that have provided you with a donated supply of blood. You need to choose where to send them. For this problem, you will need to switch between the Instructions and Solver Set Up Sheets Problem 1 Go to "Solver Set Up". Open Solver. Set your objective to maximize the Total Lives Saved. 1.5 Points Problem 2 The number of units supplied by each city is fixed. Tell Solver to find the answer by changing the cells of where the donations will be allocated. 1.5 Points Problem 3 Mathematically, you are constrained by the fact that you cannot allocate more blood in total to cities than has been supplied in total. Set up Solver with this constraint. Note: We don't want to rule out the possibility that all blood will be allocated to one city, so don't add extra constraints about where these units may end up. 1.5 Points Problem 4 In the additional Solver settings menu, set the solving method to "GRG Non Linear". Also, set the checkbox to "Make Unconstrained Variables Non-Negative". This will limit the units to only positive numbers, since you cannot allocate a negative unit. 0.5 Points Note The number of blood units that expire or get lost in transit are in the column "Units Lost in Transit". This is just included for reference. You do not need to do anything with this. Similarly, the "Need Index" is used to help calculate lives saved, but it is already included in the formula and you do not need to do anything with this. Problem 5 The initial allocations are blank. Keep the initial allocations blank as the first seed value set. Run Solver and use the Answer Report feature to show your answer. Rename the report sheet "Allocation Report 1." You should expect an unusual answer. In the next problem, you will "fix" this. Note: Some computers do not update the screen right away. Generate the Answer Report, and you might see something change when you switch back to the original sheet. Also, for some users, their answer report will not display their initial choice variables individually, so we cannot evaluate it. If your version is doing this, please take a screen shot of your initial conditions and run it again so we can evaluate your work. If your report displays your initial values, you do not need to do anything else. 1.5 Points Problem 6 Use the same specifications for the objective, constraints, and choice varaibles. Change the initial allocations to a reasonable seed value. Use the Answer Report feature to show your answer. Rename the report sheet "Allocation Report 2". Notel: Your final answer might be slightly off by 1%, due to rounding errors, that is ok. We assume solver helps find approximate answers, not exact ones. Note2: Again, some computers do not update the screen right away. Generate the Answer Report, and you might see something change when you switch back to the original sheet. Note3: You may need to try a few different seed values to find the solution, but as a hint, you should able to save at least 100,000 lives but fewer than 200,000 (don't make a constraint of this, it is just a hint to check your answer). 1.5 Points Final Note How long did it take you to complete this assignment? Blood Donation Allocation Problem Donations Donations Units Lost in City Need Index Supplied Allocated Transit Lives Saved A-burgh 1.65 415,000 B-port 1.68 81,000 C-polis 1.42 138,500 D-view 1.03 185,500 E-Port 1.07 61,500 F-sville 1.05 3,500 G-town 1.20 115,000 Total 9.10 1,000,000 D Instructions Solver Set Up +