Solve the following problem.

Need entire solution including a complete formulation of problem (includes constraints, objective function, and decision variables).

Additionally need excel solver solution with all details regarding each cell.

Prudent Provisions for Pensions Among its many financial products, the Prudent Financial Ser- each year, starting one year after purchase and continuing until vices Corporation (normally referred to as PFS) manages a well- (and including) the maturity date. Thus, these interest payments regarded pension fund that is used by a number of companies to on January 1 of each year are in time to be used toward the penprovide pensions for their employees. PFS's management takes sion payments for that year. Any excess interest payments will pride in the rigorous professional standards used in operating be deposited into the money market fund. To be conservative the fund. Since the near-collapse of the financial markets during in its financial planning, PFS assumes that all the pension paythe protracted Great Recession that began in late 2007, PFS has ments for the year occur at the beginning of the year immediately redoubled its efforts to provide prudent management of the fund. after these interest payments (including a year's interest from the It is now December 2017. The total pension payments that money market fund) are received. The entire face value of a bond will need to be made by the fund over the next 10 years are also will be received on its maturity date. Since the current price shown in the following table. 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. 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 By using interest as well, PFS currently has enough liquid received from bonds 1 and 2 on January 1 of 2019 and 2020. assets to meet all these pension payments. Therefore, to safeguard Also calculate the resulting balance in the money market the pension fund, PFS would like to make a number of investments fund on January 1 of 2018, 2019, and 2020 after receiving whose payouts would match the pension payments over the next these payments, making the pension payments for the year, 10 years. The only investments that PFS trusts for the pension fund and depositing any excess into the money market fund. are a money market fund and bonds. The money market fund pays c. Make a rough sketch of a spreadsheet model, with blocks laid out an annual interest rate of 2 percent. The characteristics of each unit for the data cells, changing cells, output cells, and objective cell. of the four bonds under consideration are shown in the table below. d. Build a spreadsheet model for years 2018 through 2020, and then thoroughly test the model. e. Expand the model to consider all years through 2027, and then solve it. Additional Case Additional cases for this chapter also are available at the University of Western Ontario Ivey School of Business website, All of these bonds will be available for purchase on January 1, cases.ivey.uwo.ca/cases, in the segment of the CaseMate area 2018, in as many units as desired. The coupon rate is the percent- designated for this book. age of the face value that will be paid in interest on January 1 of