You are the financial manager in charge of the company pension fund at Cowboys Incorporated. You know that the fund must be sufficient to make the payments listed in the table below. Each payment must be made on the first day of each year, and today is currently the first day of Year 1. You are going to finance these payments by purchasing bonds, which operate differently from other investment tools: rather than allowing for any amount to be invested in the tool and then paying off only at one point it time, bonds can be purchased only in whole/integer values at a given price and then pay small amounts (coupons) over the course of several years and then pay back a larger amount at the end of the bond’s life. The prices and coupons for the bonds currently available are as follows: Bond X costs $980 in Year 1 and yields a $60 coupon in Years 2-5 and a $1,060 payment on maturity in Year 6; Bond Y costs $970 in Year 1 and yields a $65 coupon in Years 2-11 and a $1,065 payment on maturity in Year 12; and Bond Z costs $1,050 in Year 1 and yields a $75 coupon in Years 2-14 and a $1,075 payment on maturity in Year 15. For the sake of simplicity, assume the bond yields arrive on the first day of the year and can be used to make the pension fund payments. Additionally, you know that excess cash on hand will be placed in a savings account which earns an annual rate of 4%. The required payments from the bonds and savings account for each of the next 15 years are shown below.

Year	Payment	Year	Payment	Year	Payment
1	$11,000	6	$18,000	11	$25,000
2	$12,000	7	$20,000	12	$30,000
3	$14,000	8	$21,000	13	$31,000
4	$15,000	9	$22,000	14	$31,000
5	$16,000	10	$24,000	15	$31,000

Construct and solve an Optimization model for this problem in Excel.

a. What is the minimum amount of total money needed in year 0 (able to be invested in the 3 bonds and savings account)?

b. How much money is being invested in each of the three Bonds (X, Y, and Z)?