Erin Oreilly was recently employed by the human resources

Erin Oreilly was recently employed by the human resources Customer Question Erin O’reilly was recently employed by the human resources department of a moderate sized engineering firm . Management is considering the adoption of a defined benefit pension plan in which the firm will pay 75% of an individual’s last annual salary if the employee has work for the firm 25 years. The amount of the pension is to be reduced by 3% for every year less than 25 so that an individual who has been employed for 15 years will receive a pension of 45% of the last year salary ( 75%-(10 x 3%).pension payment will start at age 65 provided the individual has retired. There is no provision for early retirement. Continuing to work after 65 may increase the individual’s pension if the person has work for less than 25 years or if the salary were to increase.One of the first tasks given O’Reilly is to estimate the amount that the fir m must set aside today to fund pension. While management plans to hire actuaries to make the final determination. The managers believe the exercise may highlight some problem that they will want to be able to discuss with the actuaries. O’Reilly was instructed to select 2 representative employees and estimate their annual pensions and the annual contributions necessary to fund the pensions.O’Reilly decided to select Arnold Berg and Vanessa Barber. Berg is 58 years old has been with the firm for 27 years and is earning $34000 .Barber is 47 has been with the firm for 3 years and earns $42000 annually.O’Reilly believes that Berg will be with the firm until he retires .he is a competent worker whose salary will not increase by more than 4% annually and it is anticipated he will retire at 65. Barber is a more valuable employee, and O’Reilly expects Barber’s salary to rise at least 7% annually in order to retain her until retirement at 65To determine the amount that must be invested annually to fund each pension, O’Reilly needs ( in addition to an estimate of the amount of the pension) an estimate of how long the pension will be distributed (ie , life expectancy) and how much the invested funds will earn. Since the firm must pay an interest rate of 8% to borrow money, she decides that the invested funds should be able to earn at least that amountWhile O’Reilly believes she is able to perform the assignment she has come to you for assistance to help answer the following questions:1) If each individual retires at age 65 how much will his or her estimated pension be?2) Life expectancy for both employees is 15 years at age 65. If the firm buys an annuity from an insurance company to fund each pension and the insurance company asserts it is able to earn 9% on the funds invested in the annuity what is the cost or the amount required to purchase the annuity contract.3) if the firm can earn 8% on the money it must invest annually to fund the pension ,how much will the firm have to invest annually to have the funds necessary to purchase the annuities?4) what would be the impact of each of the following on the amount that the firm must invest annually to fund the pension?a) life expectancy is increased to 20 yearsb) the rate of interest on the annuity contract with the insurance company is reduced to 7%c) barber retires at age 62 instead of 65 it need to be done on excel sheet