Question

Knapsack problem.

From the following list of potential projects, use Excel solver to find the optimal allocation of your budget of $1,000,000 to maximize profit. Select the projects that you will select and attach your answer report.

Project ID	Cost	Expected profit	Check selected
1	$400,000	$1,000,000
2	$50,000	$400,000
3	$300,000	$800,000
4	$150,000	$300,000
5	$400,000	$600,000
6	$600,000	$2,000,000
7	$200,000	$300,000
8	$250,000	$700,000
9	$100,000	$300,000
10	$100,000	$300,000
11	$250,000	$100,000
12	$350,000	$700,000
13	$400,000	$600,000
14	$500,000	$1,000,000
15	$50,000	$200,000
16	$200,000	$400,000
17	$350,000	$800,000
18	$50,000	$100,000
19	$150,000	$400,000
20	$300,000	$700,000

Answer 1

Project IDC Cost Expected profit $400,000$1,000,000 $400,000 $800,000 $300,000 $600,000 600,000 $2,000,000 $300,000 $700,000 $300,000 $300,000 0,000$100,000 $700.000 $600,000 $500,000$1,000,000 $200,000 $400,000 $800,000 $100,000 $400,000 $700,000 $50,000 $300,000 $150,000 $400,000 2 4 4 $200,000 $250,000 $100,000 $100,000 WN),(ХК) $350,000 $400,000 7 10) 10 12 11 13 12 14 13 15 14 15 17 16 $50,000 $200,000 1e 18 20 19 21 20 18 17$350,000 $50,000 $150,000 $300,000

22 Decision Variables 23 Whether to select a project or not. ariables are binary. 1 means selected. O means not selected 24 25 Project ID Selected/Not 6 32 34 9 35 10 36 11 37 12 38 13 39 14 15 16 17 18 19 20 43 45

Objective function Maximize total profit 46 47 48 SUMPRODUCT(B26:B45,C2:C21) 49 Constraints 心: 1000000 51 52

Objective function Maximize total profit 46 47 458 13 50 51 52 53 7 2 C) Constraints 0

Solver Parameters Set Objective To: O Max OMin alue Of By Changing aiable Cells: SAS48 SBS26:SBS45 Subject to the Constraints: SAS50 -SCS50 SBS26:SBS45binary Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solng GRGNonlinear Method: Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Decision Variables Whether to select a project or not. Variables are binary. 1 means selected. 0 means not selected 25 Project ID selected/Not 26 28 29 32 35 10 12 13 14 15 16 17 18 19 20 Objective function 37 39 41 42 43 45 47Maximize total profit 48 2800000