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 0 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 thenext 25 years. The data includes the payout each year based on the bond's coupon andmaturity date. The investment column contains a place to identify the number of bondspurchased of each type.

First, complete the model. Calculate the cash flow in each year as the (sum) product of the Investmentcolumn and the appropriate bond cash flow column. Calculate the cash flow required based on the Initial Cash Required and theAnnual Increase fields. Calculate the Disc Cash Flow as the product of the Cash Flow and discount Factorcolumn. Now specify the optimization model. Define the Investment column as the decision variables. Define the Disc Cash Flow as the Objective. (max) 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 perBond setting. We wish to solve the model using the standard LP /Quadratic engine toguaranteed optimality (0% tolerance). Now solve the model. Curate a graph of the Cash Flow per year. 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 numberpurchased and the Bond characteristics.

Please copy and paste this link below to help with the reference!

https://docs.google.com/spreadsheets/d/1XHJ1U3B6Y3nzE9jvGpZx61oHaq75_WnTmHnTMAQBo0w/edit?usp=sharing