Formulate a linear programming model for the Case Problem: Provident Pensions, develop the spreadsheet model and solve it using Excel Solver. Show complete solution. Ensure that the formulas and Solver parameters are properly set.

Provident Financial Services Corporation (also known as PFS) manages a well-regarded pension fund that is used by a number of companies to provide pensions for their employees. Management takes pride in the rigorous professional standards used in operating the fund since the near collapse of the financial markets during the protracted recession that began in late 2007 PFS has redoubled its efforts to provide prudent management of the fund. It is now 2023. By its estimate, the total pension payments that will need to be made by the fund over the next 10 years are shown in the table below. PFS would like to make the smallest possible investment (including any deposit into the money market fund) on January 1,2024, to cover all its required pension payments through 2033. PFS top management has given you the assignment of modeling and analyzing this problem. Some spreadsheet modeling needs to be done to see how to do this. a. Visualize where you want to finish. What numbers are needed by PFS management? What are the decisions that need to be made? What should the objective be? b. Suppose that PFS were to invest PHP 1400 million in the money market fund and purchase 10,000 units each of bond 1 and bond 2 on January 1, 2024. Calculate by hand the payments received from bonds 1 and 2 on January 1 of 2025 and 2026 . Also calculate the resulting balance in the money market fund on January 1 of 2024,2025, and 2026 after receiving these payments, making the pension payments for the year, and depositing any excess into the money market All of these bonds will be available for purchase on January 1, 2024, 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 year's interest from the money market fund) are received. The entire face value of a bond also will be received on its maturity date and deposited into the money market fund. 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. c. Formulate the above problem mathematically as a linear programming model. Define your decision variables clearly; define the notation you use. Using your decision variables, formulate the objective function and constraints. d. Make a rough sketch of a spreadsheet model, with blocks laid out for the data cells, changing cells, output cells, and objective cell. e. Build a spreadsheet model for years 2024 through 2026 , and then thoroughly test the model. f. Expand the model to consider all years through 2033 , and then solve it using Excel Solver How much should PFS invest (including any deposit into the money market fund) on January 1, 2024, to cover all its required pension payments through 2033 ? 1. Money Market Funds are cash equivalents funds that are meant for short-term investing. They have solid credit quality and are highly liquid. They are considered equivalent to cash because they can be converted to actual cash quickly. 2. Determine PFS initial investment and the number of units of the bonds to purchase on January 1, 2024. The initial investment will be used to purchase the bonds and for the pension payment for 2024. Any excess amount will be invested in the money market fund. 3. The money market fund "matures" (or can be withdrawn) the following year as needed. 4. Consider all transactions are done on January 1 of the year. 5. Assume that cash inflows (interest payments from the bonds and money market fund, the bonds maturity values and the money market fundsare received/available at the start of the day. Cash ourflows (e. 8 . purchase of the bonds (year 2024 ) and the pension payments for the year) are made during the day. Any excess funds are invested in the money market fund towards the end of the day. 6. The interests eamed from the investments and the maturity values should be able to cover all its required pension payments through 2033 . Please refer to the tables above for details. 7. Minimum maintaining balance required for the money market fund lor cash balance) is 0 . 8. Objective is to minimize on the initial investment in January 1, 2024. 9. Solve as a regular linear programming problem. No need to solve as an integer program