Sheet1Q2) David's WinningsWinter 2023DATACocoaChorneyKitsoLibbyPebblesSydneyCourt:RunningDrillingPipelineDistributionManufacturingExplorationWarehouseBasketballLandRate of...

there in the files.


Sheet1 Q2) David's WinningsWinter 2023 DATACocoaChorneyKitsoLibbyPebblesSydneyCourt:Running DrillingPipelineDistributionManufacturingExplorationWarehouseBasketballLand Rate of Return3.02%3.54%4.30%3.16%3.58%3.75%5.24%5.08% MaturityLongLongShortLongMediumMedium Risk RatingGoodFairExcellentGoodFairGood $116,000$105,000Investment DECISION$ to investPurchase Business? (0 = no / 1 = yes) Investment Decision:$100$200$300$400$500$60011 Annual Return$3.02TOTAL RETURN CONSTRAINTSTotalAvailable Total investment money Sports Investment: only 1 The optimal solution is to invest 2. David’s Winnings [40 marks] David bought a lottery ticket with his colleagues at his new job and just won a share of the Lotto Max Millions and is deciding how to invest part of his share ($450,000). He is looking at 6 different corporate bonds as well as 2 sports investments. He has some stipulations and concerns about the risks associated with each investment. You have been asked to determine the best investment decision to maximize his annual return on the investment within the constraints given. See the Excel template for the rate of return (%), maturity and risk rating assigned to each of the investment possibilities available for him. * Maturity is the length of time over which the bonds will be payable. ** Rating indicates independent underwriter’s assessment of the quality or risk associated with each issue. David has some stipulations about how he wants to invest the money: - He wants to invest the entire amount - He wants to invest in part ownership in either one of the sports investments but not both. - No more than $90,000 should be invested in any one type of corporate bond investment (excludes the sports investment). - At least $45,000 should be in Chornay Pipeline (although it is a bond with a long maturity and fair risk rating, he is expecting a turnaround for Canadian pipelines). - He wants to invest at least $180,000 in bonds that mature in the long term. - He wants at least $160,000 invested in bonds associated with OSCM (Operations and Supply Chain Management) functions (such as warehousing, manufacturing or distribution). - No more than 25% of the total money invested in the corporate bonds (excluding the sports investment) should be invested in bonds that are rated Fair. Create an Excel Solver spreadsheet that models David’s situation. Use the Solver to find the best portfolio that satisfies David’s constraints. What is the "best" portfolio ($) to invest in each of the corporate bonds and one sports investment and the total expected return? State the names and amounts for each investment. - Ensure that you have named each constraint with a unique name - The template provided to help formulate your problem. Note that not every cell in every row needs to be filled in. Do not insert or delete any rows. - You will need to submit your Excel spreadsheet to D2L. The formulation for this question is the only thing marked on the Excel spreadsheet.