Using Excel,

1. Charles McKeown is an acquisitions editor for a college textbook publisher. The file that accompanies this book contains a list of 151 textbooks that Charles has an opportunity to acquire from another publisher. For each title, the file lists the price (acquisition cost) and net present value of expected future sales. Assume that Charles may select up to 20 tiles from this list and spend $12 million on these acquisitions.
   • Create an optimization model in a spreadsheet to solver Charles problem.
   • Which titles should Charles acquire, how much of the budget would be used, and what is the expected NPV of these titles?

A B c D E F G H Select? Yes=1, No=0 0 # Selected $ Spent Allowed 20 $12,000 0 0 0 NPV 0 0 0 0 0 0 0 0 0 0 0 0 1 2 Book Acquisitions 3 4 5 Title Code Price (000s) NPV (000s) 6 AB6 $ 976 $ 1,219 7 AC3 $ 1,026 $ 1,054 8 AH9 $ 892 $ 2,293 9 BD6 $ 2,594 $ 3,260 10 BR8 $ 1,113 $ 1,122 11 BX4 $ 3,219 $ 3,484 12 CE4 $ 615 $ 677 13 CV2 $ 2,412 $ 2,686 14 CZ2 $ 604 $ 688 15 DH7 $ 871 $ 1,076 16 DO5 $ 849 $ 1,014 17 DW7 $ 1,723 $ 1,870 18 DX3 $ 728 $ 824 19 DY6 $ 319 $ 451 20 DZ4 $ 239 $ 87 21 EH1 $ 2,129 $ 2,407 22 $ 954 $ 1,201 23 EM5 $ 2,283 $ 2,505 24 EPS $ 992 $ 1,100 25 EU9 $ 1,116 $ 1,137 26 EVO $ $ 1,621 $ 1,896 27 EZ2 $ 167 $ 449 28 FB1 $ 758 $ 1,353 29 FD8 $ 2,422 $ 4,054 30 FL7 $ $ 3,530 $ 3,894 31 FR7 $ 991 $ 1,073 32 GB2 $ 2,123 $ 2,140 33 GC8 $ 197 $ 201 34 GD7 $ 1,999 $ 2,523 35 GW7 $ 699 $ 809 36 GY4 $ 596 $ 612 37 HE4 $ 195 $ 317 38 HG6 $ $ 1,009 $ 1,246 39 HK3 $ 771 $ 894 40 HN6 $ 227 $ 187 41 HO2 $ 1,464 $ 1,531 42 HV1 $ 894 $ 1,204 43 IA6 $ 1,023 $ 1,095 44 1J4 $ 2,677 $ 3,455 45 IP7 $ $ 3,980 $ 5,258 46 IS7 $ 756 $ 874 47 T8 $ 826 $ 1,090 48 IY8 $ $ 1,663 $ 1,743 49 JB9 $ 1,830 $ 2,140 50 JD3 $ 2,840 $ 2,942 51 JK6 $ 884 $ 921 52 JN1 $ 1,934 $ 3,469 53 $ 1 260 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 IDS 1 26 1 n A B c D E F G H Select? Yes=1, No=0 0 # Selected $ Spent Allowed 20 $12,000 0 0 0 NPV 0 0 0 0 0 0 0 0 0 0 0 0 1 2 Book Acquisitions 3 4 5 Title Code Price (000s) NPV (000s) 6 AB6 $ 976 $ 1,219 7 AC3 $ 1,026 $ 1,054 8 AH9 $ 892 $ 2,293 9 BD6 $ 2,594 $ 3,260 10 BR8 $ 1,113 $ 1,122 11 BX4 $ 3,219 $ 3,484 12 CE4 $ 615 $ 677 13 CV2 $ 2,412 $ 2,686 14 CZ2 $ 604 $ 688 15 DH7 $ 871 $ 1,076 16 DO5 $ 849 $ 1,014 17 DW7 $ 1,723 $ 1,870 18 DX3 $ 728 $ 824 19 DY6 $ 319 $ 451 20 DZ4 $ 239 $ 87 21 EH1 $ 2,129 $ 2,407 22 $ 954 $ 1,201 23 EM5 $ 2,283 $ 2,505 24 EPS $ 992 $ 1,100 25 EU9 $ 1,116 $ 1,137 26 EVO $ $ 1,621 $ 1,896 27 EZ2 $ 167 $ 449 28 FB1 $ 758 $ 1,353 29 FD8 $ 2,422 $ 4,054 30 FL7 $ $ 3,530 $ 3,894 31 FR7 $ 991 $ 1,073 32 GB2 $ 2,123 $ 2,140 33 GC8 $ 197 $ 201 34 GD7 $ 1,999 $ 2,523 35 GW7 $ 699 $ 809 36 GY4 $ 596 $ 612 37 HE4 $ 195 $ 317 38 HG6 $ $ 1,009 $ 1,246 39 HK3 $ 771 $ 894 40 HN6 $ 227 $ 187 41 HO2 $ 1,464 $ 1,531 42 HV1 $ 894 $ 1,204 43 IA6 $ 1,023 $ 1,095 44 1J4 $ 2,677 $ 3,455 45 IP7 $ $ 3,980 $ 5,258 46 IS7 $ 756 $ 874 47 T8 $ 826 $ 1,090 48 IY8 $ $ 1,663 $ 1,743 49 JB9 $ 1,830 $ 2,140 50 JD3 $ 2,840 $ 2,942 51 JK6 $ 884 $ 921 52 JN1 $ 1,934 $ 3,469 53 $ 1 260 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 IDS 1 26 1 n