Question

1 Approved Answer

Posted on Jul 12, 2024

Tab1 Tab 3 10 11 12 13 14 15 16 17 18 1,047.01 0,00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0,00 0,00 0,00

Tab1 Tab 3 10 11 12 13 14 15 16 17 18 1,047.01 0,00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0,00 0,00 0,00 57.44 57.44 1,057.44 D.DO 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 45.41 45.41 45.41 15.41 45.41 15.41 45.41 45.41 45.41 1,045.41 0.00 10.00 0.00 43.34 43.34 43.34 43.34 43.34 43.34 43.34 43.34 43.34 1,043.34 0.00 0.00 0.00 0.00 44.22 44.22 44.22 14.22 14.12 14.22 44 22 14 22 14 22 14 22 14.22 4.22 14.22 14.22 54.91 54.91 54.91 $4.91 54.91 54.91 1.054.91 0.00 0.00 0.00 0.00 0.00 0.00 54.46 54.46 54.46 54.46 54.46 54 46 $4 46 54 46 54 46 54.46 14.46 54.46 54.46 54.46 56.75 56.75 56.75 56.75 56.75 $6.75 56.75 56 75 56.75 16.75 56.75 1,056.75 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 00 0.00 0.00 0.00 0.00 0,00 0.00 0.00 0.00 0.00 0.00 1.00 0.00 0.00 100 0.00 50.94 10.94 50,94 50.94 1,050.94 0.00 0.00 0.00 0.00 0.OD 0.00 0.00 50.36 50.36 50.36 1,050.36 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 46.91 46.91 16,91 46.91 46.91 16.91 46.91 16.91 469 1691 46.91 16.91 46.91 1,046.9 0.00 42.29 1,042.29 D.DO D.DO D.CO 0.00 0.00 0.00 0.00 0,00 0.00 0.OD 13.58 13.58 43.58 43 58 13.58 1,043.5 0.00 0.00 0.00 1,00 43.58 43.58 43.58 13.58 53.47 53.47 53.47 53.47 53.47 $3.47 53.47 1.053.47 0.00 0.00 0.00 0.00 0.00 0.00 48.3M 48.18 48.38 48 38 48 78 48.38 18.38 55.07 55.07 55.07 55.07 55.07 55.07 $5.07 $5-07 $5.07 55.07 55.07 55.07 55.07 1,055.07 51.14 $1.14 51.14 51.14 51.14 $1.14 51.14 51.14 $1.14 $1.14 51.14 1.14 1,05 0,00 0,00 D.DO 0.00 0,00 0.00 0,00 0,00 0,00 0.00 0.00 0.00 O.00 0.00 0.00 0.00 0.00 10.09 40.03 40.03 10.03 40.03 40.03 40.03 40703 1,040.0 0.00 0.00 0.00 0.00 49.15 49.15 19.15 19.15 19.15 19.15 19.15 19.15 19.15 49.15 19.15 9.15 19.15 19,15 1,045 41.39 41.39 41.39 1.39 41.39 139 11.39 1,041.39 0.00 0.00 0.00 0.00 45.71 15.71 1.045.71 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0,00 0.00 0.00 0,00 0,00 D.DO D.00 0.00 0.00 0.00 0.00 0.00 0,00 0,00 0,00 54.78 54.78 54.78 54.78 54.78 $4.78 54.78 $4.78 54.78 54.78 54.78 4.78 54.78 14.78 0.00 0.00 0.00 0.00 10 00 0.00 0.00 0.00 0.00 0,00 0,00 1.26 1.26 51.26 51.26 51.26 $1.26 51.26 $1.26 01.26 1,051.2 0,00 0,00 0.00 0.00 D.00 D.DO 0.00 0.00 0200 0.00 0.00 0.00 0.OD 1,00 34.54 54.54 $4.54 54.54 $4.54 54.54 -454 34.54 54.34 1.054.54 0.00 0.00 0.00 46. 36 46,36 46,36 16.26 16.16 16 36 16 36 16 36 16 36 16.36 1,046.36 0.00 0,00 0.00 1,059.18 0.00 0.00 D.DO 0.00 0200 0.0 0.00 0.OD 0.00 0,00 56.95 56.95 56.95 56.95 56.9S 56.95 56 95 $6.95 56 95 1.056.95 0.00 0.00 0.00 0.00 46,85 46,85 46,85 16.85 46,85 16,85 46.85 46 85 46.85 6.8 46.85 16.85 16,85 16.85 56.28 56.28 56.28 56.28 56.28 16.28 56.28 56 28 56.28 56.28 16. 28 56.28 56.28 58.40 18.40 58.40 1.058.40 0.00 0.00 0.00 0.00 1.00 56, 18 56.18 1.056.18 0.CO 0.00 0.00 0.00 0700 0.00 0.00 0,00 0,00 0,00 0.00 53.95 53.95 51.95 53.95 53.95 53.95 53.95 1.053.95 0.00 0.00 0.00 0.OD 0.00 40.83 40.83 10.83 40.83 40.83 40.83 40.83 10.83 10.83 10.83 40.83 10.83 10.83 16 22 46, 22 46 22 46 22 1.046 22 0.00 0.00 0.00 0.00 0,00 0,00 16. 22 55.00 $5.00 55.00 55.00 55.00 55.10 55.00 $5-00 1.055.00 0.00 0.00 0.OD 0.00 0.00 45.01 45.01 45.01 45.01 15.01 45.01 45.01 45.01 5.01 45.01 45.01 45.01 45.01 45.01 43.56 43,56 43.56 13,56 1.043.56 0.00 0.00 0.06 0,00 0,00 10,00 0.00 42.77 42.77 42.77 12.77 42.17 12.77 42.77 12 77 42.77 12.77 12.77 2.77 12.77 12.77 49.69 19.69 49.69 19.69 19269 10 69 1.049.69 0.00 10.00 0.00 0.00 46.36 46.36 1.046.36 0.00 0.00 0.00 0.00 0.0 0.00 0,00 0.00 0,00 0.00 0.00 D.DO D.DO D.CO 1.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 51.41 $1.41 51.41 51.41 $1.41 $1.41 51.41 $1.41 $1.41 $1.41 $1.41 $1.41 51.41 51.41 40,06 40,06 40,06 10.06 40.06 40 06 40-06 40.06 40.06 40,06 10.06 1,040.06 0.00 0.78 0.75 0.71 D.68 D.64 0.61 0.58 0.56 0.53 0.51 0.48 0.46 0.44 0.42Tab1 Tab 3 11 12 13 14 16 17 18 19 20 21 22 23 24 0.00 0.00 0,00 0,00 0.00 0,00 0,00 9,00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 D.DO D.DO D.00 0.00 0.00 45.41 45.41 45.41 1,045.41 0.00 1.00 0.00 0.00 0,00 0.00 0.00 0.00 0.00 0-00 43.34 43 34 43.34 1.043.34 10.00 0,00 0.00 0,DO 0.00 D.DO 0.00 0.00 44 22 44 22 44.22 14.22 44.22 4.22 14.22 44.22 14.22 14.22 14.22 1,044.22 0-00 .054.91 0.00 0.00 0.00 0,00 0.00 0.00 0.00 0.00 0200 0200 54 46 $4.46 34.46 14.46 54.46 54.46 54.46 54.46 54.46 54.46 54.46 54.46 1,054 4 0.00 56.75 16.75 36 75 6.75 56.75 1,D56.75 0.00 0.00 D.DO D.DO D.00 0.00 0.00 0200 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1,00 0,00 0.00 0,00 0.00 0 00 10O 0.00 .0D 1.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.DO 0.00 0.00 0.00 0.00 16.91 4691 16.91 16.91 1691 6.91 1.046.91 9.00 0.00 0.00 0.00 0.00 0.00 0,00 1.00 0.00 0,00 D.DO 2.00 0.00 13.58 43.58 43.58 1,043.58 1.00 1.00 0.00 D.D0 3.00 0.00 0.00 53.47 1.053.47 0.00 0.0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0200 48 38 18.38 18.38 18,38 18,38 8,3H 1.048.38 D,DO D.CO 12.00 0-00 $5.07 $5 07 $5.07 $5.07 55.07 55.07 $5.07 1,055.07 D.DI 0.00 0.00 0.00 $1.14 $1.14 $1.14 $1.14 51.14 51.14 1.14 51.14 1.051.14 0.00 D.00 D.00 0.00 0.00 0.00 0.00 0.00 0.00 D.DI 5,00 100 0.00 0.00 0.00 0.00 1.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 10.03 1.040.03 0.00 0.00 0.00 0.00 900 0.00 1.00 49.15 49.15 49.15 19.15 19.15 9,15 19.15 49.15 1,049.15 0.00 0.00 0-00 11.39 41 39 41.39 1,041.39 0.00 1.00 0.00 0.00 0.00 0.00 0.00 0.00 0 00 0.00 0.00 0.00 0.00 TOOTH 0.00 0.00 0.00 0.00 0.DO 0.00 0200 0.00 0.00 0.00 0,00 0,00 0,00 0,00 D.DO 0.00 100 -4.78 54.78 54.78 4.78 14.78 54.78 14.TH 54.78 1.054.78 0.00 0.00 0.00 0.00 0.00 100 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 00 $1.26 $1.26 1.26 1,051.26 0.00 1.00 0,00 100 0.DO 5,00 0.00 0.00 0.00 0.00 0.00 1.00 0.00 7.00 0.00 0.00 0.00 (.00 14.54 4.54 54.54 4.54 1,054.34 0.00 0.00 0.00 9.00 0.00 0.00 0 00 46.36 16.36 46.36 46.36 ,046.36 10,00 0.00 0,00 0,D0 9.00 0.00 0-00 0200 0.00 0.00 0.00 0,00 0.00 0.00 0.00 36.95 56.95 36.95 1,056.9 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0200 46.85 16 85 46.85 46.85 46,85 16,85 46,85 46,85 46,85 46.85 16,85 46.85 16.85 46 85 1,046 6 28 16.25 56.28 16.28 56.28 56.28 56.28 16.28 56.28 56 28 0.00 0.00 0.00 0.00 0.00 1,00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 00 0.00 0.00 0.00 0.00 0,00 1,00 0,90 0,DO 0.00 0.00 53.95 1.053.95 0.00 0.00 0.00 0.0D 0.00 0.00 D.DO 0.00 0.00 1200 40-83 40.83 10.83 .83 10.83 10.83 1.040.83 46 23 1.046 22 0.00 0.00 0,00 0.OD 0 01 0,00 0,00 0.DO 0.00 0.00 0 00 55.00 55-00 1,055.00 0.00 0.00 0.OD 0.00 0.00 D.DO D.DO D.CO 0.00 0.00 0.00 45-01 4501 45.01 15.01 15.01 15.01 45.01 13.01 45.01 15.01 15.01 15.01 45.01 45.01 1,045. 0.00 0.00 0.00 0,00 0,00 0,00 0,0 0,00 0,00 D.DO 0.00 0 00 0 00 12.77 12.77 42.77 12.77 12.77 2.77 12.77 12.77 12.77 12.77 12.17 12.77 12.77 42.77 1,042 19.69 49.69 1.049.69 0.00 0.00 0.00 0,00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 00 0.00 0.00 0.00 0.00 1.00 0,00 0.00 0,00 D.00 0.00 0 00 0-00 0.00 0.00 0.00 0.00 0.0D 0.00 0.00 D.D0 D.DO D.00 0.00 0.00 0.00 $1.41 $1.41 $1.41 $1.41 $1.41 51.41 51.41 1.41 $1.41 1.41 51.41 1,051.41 0 00 40-06 40-06 40 06 40.06 40,06 40,06 1,040,06 0,00 0,00 0.DO 0.00 0 00 0.58 0.56 0.53 0.51 0.48 0,46 0,44 0,42 D,40 D.38 0.36 0.34 0.33 0.31Problem 2 (10 points) A money manager for a university endowment has received a $10 million donation to invest. The intent is to establish a cash flow stream for the next 25 years. The goal is to have an annual payout that begins at $50,000 and grows by 3% per year. The manager has identified a list of 50 different bonds to invest in. The manager's goal is to determine which bonds to purchase now so as to maximize the total discounted return while guaranteeing the bond's pay out the required annual funds. The manger has specified a limit of 2,500 units of any individual bond. The data on tab 3 lists the 50 bonds available for purchase annual payout for each of the next 25 years. The data includes the payout each year based on the bond's coupon and maturity date. The investment column contains a place to identify the number of bonds purchased of each type. First, complete the model. Calculate the cash flow in each year as the (sum) product of the Investment column and the appropriate bond cash flow column. Calculate the cash flow required based on the Initial Cash Required and the Annual Increase fields. Calculate the total Disc Cash Flow as the product of the Cash Flow and Discount Factor rows. Specify the Max Per Bond setting as the maximum number of units that can be invested in a single bond. Now specify the optimization model using Analytic Solver. Define the Investment column as the decision variables. Define the Disc Cash Flow as the Objective. (max-normal) Establish the constraint that the Cash Flow must exceed the Cash Required field. . Establish a constraint that limits the Investment to be less than the Available Funds. Constrain the Investments to be non-negative integers and less than the Max per Bond setting. We wish to solve the model using the Simplex method to guaranteed optimality. Now solve the model. Create a graph of the Cash Flow per year and paste it into your template. . What is the total Disc Cash Flow? How many bonds were invested in? Filter your data to show the bonds that were selected, including the number purchased and the Bond characteristics. Paste this data into your template.Tab1 Tab 3 Bond Cash Flows Investment Bond Termi Coupon Yield Rating Price 5 4.70% 5.00% AAA $987.01 47.01 47.01 47.01 47.01 1.047 2 7 5.74% 5.46% AAA $1,016.25 57.44 57.44 57.44 57.44 14 4.54% 5.80% AAA $881.71 45.41 45.41 45.41 45.41 00 14 4.33% 5.37% AAA $899.45 43.34 43,34 43,34 43.34 27 4.42% 5.59% AAA $854.57 44.22 44.22 44.22 14.22 6 5.49% 5.36% AAA $1,010.34 54.91 54.91 54.91 54.91 00 23 5.45% 4.52% AAA $1.130.47 54.46 54.40 $4.46 54.46 0.0 16 5.68% 4.61%% AAA $1,118,71 46.75 56.75 56.75 16.75 0.0 9 4.08% 4.24% AAA $997.08 40.81 1.040.81 0.00 0.00 0,00 0.0 10 4.87% 4.63% AAA $1,002.28 1,048.68 0.00 0.00 0.0 11 5.09% 5.63% AAA $963.01 50.94 50.94 50.94 50.94 12 5.04% 5.30% AAA $983.30 50.36 50.36 50.36 50.36 0.0 13 18 4.69% 5.46% AAA 8912.91 46.91 46.91 46.91 16.91 14 AAA $915,47 42.29 42.29 42.29 42.29 0.0 4.23% 5.95% 0.0 15 14 4.36% 4.97% AAA $939.54 43.58 43.58 43.58 43.58 0.0 16 12 5.35% 5.87% AAA $956.10 53.47 53.47 53.47 53.47 17 4.84% 5.83% AAA $891.29 48.38 48.38 48.38 48.38 0.0 18 5.51% 4.85% AAA $1,077.56 55.07 $5.07 55.07 55.07 0.0 19 5.11%% 5.96% $905.54 51.14 51.14 $1.14 11.14 AAA 0.0 20 5.78% 5.75% AAA $1,000.61 57.77 57.77 1,057.77 0,00 0.0 21 5.65% 4.28% AAA $1,025.80 56.53 1,056.53 0.0D 0.00 0.0 22 4.00% 5.93% AAA $828.79 40.03 40.03 40.03 40,03 0.0 23 4.91% 5.22% AAA 8963.21 49.15 49,15 49.15 49,15 24 4.14% 5.36% AAA $882.17 41.39 11.30 41.39 41.39 25 45.71 4.57% 5.26% AAA $960.67 45.71 45.71 45.71 0.0 26 4.04% 4.89% AAA $969.61 40.37 40.37 40.37 1,040.37 0.0 27 23 5.48% 4.42% AAA $1,150.81 54.78 54.78 54.78 54.78 0.0 28 3 4.11% 4.42% AAA $991.57 41.13 41.13 1,041.13 0.00 0.0 29 14 5.13% 5.080% AAA $1,004.16 51.26 51,26 51.26 1.26 0.0 30 4.95% 4.34% AAA $1,016.64 19.48 19.48 ,049.48 0.00 0.0 31 5.45% 5.98% $949.14 $4 54 54.54 AAA 14.54 0.0 32 4.64% 5.59% AAA $904.81 46.36 16.36 46,36 16,36 33 5.924 4.97%% AAA $1,041.16 59.18 19.18 0.0 59.18 59.18 1,059 0.0 34 5.70% 5.85% AAA $985.41 56.95 0.0 35 4.68% 5.93% AAA $840.02 46,85 46,85 46,85 46,85 0.0 36 5.63% 5.03% AAA $790.98 56.28 56.28 56.28 56.28 0.0 37 5.84% 5.98% AAA $991.38 58.40 58.40 58.40 58.40 0.0 38 5.62% 5.36% AAA $1.014.91 56. 18 56,18 56, 18 56.18 0.0 39 5.39% 4.93% AAA $1,041.55 53.95 53.95 53.95 53.95 0.0 40 4.08% 5.41% AAA $827.29 40.83 40 83 40.83 40,83 0.0 41 4.62% 5.57% 16.22 AAA 46 22 46. 22 46.22 0.0 42 5.50% 5.83% AAA $970.11 55.00 $5.00 $5.00 65.00 0.0 4.50% 4.02% AAA 1,074.35 45.01 45.01 45.01 45.01 0.0 44 4.36% 4.56% AAA $985.17 43,56 43,56 43.56 43,56 0.0 45 25 4.28% 5.39% AAA $849.24 42.77 42.77 42.77 42.77 0.0 46 13 .97% 4.83% AAA $1,012.95 49 69 49.69 49.69 49.69 0.0 47 4.64% 5.41% AAA $955.92 46.36 46.36 46,36 46.36 48 4.86% 5.00% AAA $995.09 48.60 48.60 1,D48.60 49 22 5.14% 5.17% AAA $996.25 $1.41 $1.41 51.41 51.41 4.01% 4.41% AAA $952.41 40.06 40,06 40,06 40,06 Avail Funds 10,000,000 Cash Flow 50.000 Investment Cash Required Discount Factor 0.86 0.82 Max per Bond 0.95 0.91 Discount Rate Dise Cash Flow Initial Cash Required 50.000 Annual Increase 3% Bonds Invested in 0 Max Bonds 0.0