A financial planner wants to design a portfolio of investments for a client. The client has $300,000 to invest and the planner has identified four investment options for the money. The following requirements have been placed on the planner. No more than 25% of the money in any one investment, at least one third should be invested in long-term bonds which mature in seven or more years, and no more than 25% of the total money should be invested in Cor D since they are riskier investments. The planner has developed the spreadsheet model (see Table 'Spreadsheet model') based on the data in the table below and the requirements of the client. The objective is to maximize the total return of the portfolio. Investment Return Years to Maturity Rating A 6.45% 9 1-Excellent B 7.10% 8 2-Very Good 8.20% 5 4-Fair D 9% 8 3-Good Refer to the Table 'Spreadsheet model below. a) What formula should be entered in cell B7 in the accompanying Excel spreadsheet to compute total dollars invested? b) What formula should be entered in cell D7 in the accompanying Excel spreadsheet to compute the total return? Use SUM and SUMPRODUCT functions where necessary. Do NOT leave any space when writing the formula in the appropriate boxes below. Spreadsheet model B 1 Amount 2 Bond Invested $0 4B $0 5C $0 6D $0 7 Total invested: C D Maximum 25.0% Return $75,000 6.45% $75,000 7.10% $75,000 8.20% $75,000 9% E F G H Years to 7+years? Good or worse? Maturity (1-yes,0-no) Rating (1-yes,0-no) 9 1-Excellent 0 8 2-Very Good 5 0 4-Fair 8 1 3-Good 1 $ Total: $0 Total: $0 1 1 1 $ Total: 8 Total available: $300,000 Required: $100,000 Allowed: $75,000