C L M N 1 A B D E F G H J K 1 2 Stratford National Bank 3 Amount Short Term? High Risk? Tax Free? 4 Bond Invested % Return Return Maturity no) Rating (1-yes, 0 -no) Tax Free no) 5 A 9.5% $0 Long 0 High 1 Yes 1 6 B 8.0% $0 Short 1 Low 0 Yes 1 7 C 9.0% $0 Long 0 Low 0 No 0 8 D 9.0% $0 Long 0 High 1 Yes 1 9 E 9.0% $0 Short 1 High 1 No 0 10 Total Invested $0 Total: Total: Total: 11 Total Available $200,000 Required $100,000 Required $100,000 Required $60,000 12 Total Return $0 at least at most at least 13 Tax Free Return $0 14 40% of Total Return $0 15 16 A banking officer at Stratford National Bank needs to determine how to invest $200,000 in the following collection of bonds to maximize 17 annual return. The officer wants to invest at least 50% of the money in short term bonds and no more than 50% in high risk bonds. At 18 least 30% of the funds should go in tax-free investements, and at least 40% of the total return should be tax free. 19 Create a linear programming model to represet the problem using Excel Solver. The decision variables are the amounts of money that 20 should be invested in each bond. 21 The Objective Function is to maximize the total annual return in cell D12. 22 To effectively solve this problem you will have to use the =SUMIF function in cells F10, H10, J10, and B13. 23 Bond Annual Maturity Risk Tax-Free 24 Return 9.5% Long High 25 Yes 8.0% Short Low Yes 26 9.0% Long Low No 27 9.0% Long High Yes 28 E 9.0% Short High No 29 A B D