I just need the Case Recommendation for this

Case 4-1: Prudent Provisions for Pensions

Among its many financial products, the Prudent Financial Services Corporation (normally referred to as PFS) manages a well-regarded pension fund that is used by a number of companies to provide pensions for their employees. PFSs management takes pride in the rigorous professional standards used in operating the fund. Since the near-collapse of the financial markets during the protracted Great Recession that began in late 2007, PFS has redoubled its efforts to provide prudent management of the fund.

It is now December 2017. The total pension payments that will need to be made by the fund over the next 10 years are shown in the following table.

By using interest as well, PFS currently has enough liquid assets to meet all these pension payments. Therefore, to safeguard the pension fund, PFS would like to make a number of investments whose payouts would match the pension payments over the next 10 years. The only investments that PFS trusts for the pension fund are a money market fund and bonds. The money market fund pays an annual interest rate of 2 percent. The characteristics of each unit of the four bonds under consideration are shown in the table below.

All of these bonds will be available for purchase on January 1, 2018, in as many units as desired. The coupon rate is the percentage of the face value that will be paid in interest on January 1 of each year, starting one year after purchase and continuing until (and including) the maturity date. Thus, these interest payments on January 1 of each year are in time to be used toward the pension payments for that year. Any excess interest payments will be deposited into the money market fund. To be conservative in its financial planning, PFS assumes that all the pension payments for the year occur at the beginning of the year immediately after these interest payments (including a years interest from the money market fund) are received. The entire face value of a bond also will be received on its maturity date. Since the current price of each bond is less than its face value, the actual yield of the bond exceeds its coupon rate. Bond 3 is a zero-coupon bond, so it pays no interest but instead pays a face value on the maturity date that greatly exceeds the purchase price.

PFS would like to make the smallest possible investment (including any deposit into the money market fund) on January 1, 2018, to cover all its required pension payments through 2027. Some spreadsheet modeling needs to be done to see how to do this.

Year Pension Payments ($ millions) 2018 8 2019 12 2020 13 2021 14 2022 16 2023 17 2024 20 2025 21 2026 22 2027 24 Bond 1 Bond 2 Current Price Coupon Rate Maturity Date Face Value $ 980 4% Jan. 1, 2019 $ 1,000 920 2 Jan. 1, 2021 1,000 Bond 3 750 Jan. 1, 2023 1,000 Om Bond 4 800 Jan. 1, 2026 1,000 a. Visualize where you want to finish. What are the decisions that need to be made? What should the objective be? What numbers are needed by PFS management? b. Suppose that PFS were to invest $30 million in the money market fund and purchase 10,000 units each of bond 1 and bond 2 on January 1, 2018. Calculate by hand the payments received from bonds 1 and 2 on January 1 of 2019 and 2020. Also calculate the resulting balance in the money market fund on January 1 of 2018, 2019, and 2020 after receiving these payments, making the pension payments for the year, and depositing any excess into the money market fund. c. Make a rough sketch of a spreadsheet model, with blocks laid out for the data cells, changing cells, output cells, and objective cell. d. Build a spreadsheet model for years 2018 through 2020, and then thoroughly test the model. c. Expand the model to consider all years through 2027, and then solve it