PantherTec sponsors a defined benefit pension plan for its employees. The companys pension formula pays 2% per year of service time of the employees final years salary, with a maximum benefit of 60% of final salary. To illustrate, an employee earning $200,000 and retiring after 20 years would receive an $80,000 annual benefit (2% x 20 years x $200,000). Accompanying this project is an Excel spreadsheet with two tabs. The first, Employee Information, lists the 200 employees of PantherTec, along with information which may or may not be needed to compute pension obligations. The second tab, Pension Plan Asset Performance, outlines how PantherTecs plan assets have performed over the last several periods, expected returns on plan assets over that period, and information on previous amortizations. There are no prior service costs or other accumulated losses on the projected benefit obligation. Required: Write a project summary addressing each of the following requirements, citing accompanying tables and figures as necessary. Your final deliverable should include a typed report with responses to each of the following points as well as an Excel file with supporting analysis. Responses should be succinct, but complete.

Analyses:

1. Compute the projected benefit obligation (PBO) as of 12/31/2014 using the Employee Information tab assuming the companys actuary recommends a 6% discount rate. For simplicity, assume that all retirements occur on December 31 of the retirement year, and partial years worked provide no benefit. In other words, if a 40 yearold employee begins working on 7/1/2014 and is expected to retire at 65, then assume that his retirement date is 12/31/2039 and that he will accrue 25 years of service (2015 through 2039). Computations should be performed in Excel.

2. Compute Pension Expense for 2014, separately identifying each component. Assume that no actuarial assumptions changed between 2013 and 2014 (i.e., same projected ending salaries, benefit periods, etc.).

3. Describe the funding status for the plan. Is the plan over or underfunded?

4. PantherTec is considering a pension plan amendment that would alter its plan to pay an annual benefit of 2.75% instead of 2%. In addition, the companys actuary recently suggested that the company may need to reduce its discount rate from 6% to 5% in 2015 in light of historically low interest rates. Treating each potential change independently, compute the effect of the changes on the companys PBO as of 12/31/2014 and comment on how each would be recorded, should the changes be enacted.

Critical Thinking:

5. Compare pension and salary information male vs. female employees. Comment on possible reasons for those differences. Can you infer anything about expectations for male vs. female employees from the data provided? It may be useful to use a Pivot Table for your analysis.

6. Assume PantherTec is considering investing in an employee health and wellbeing program. This program will involve an onsite fitness center and complementary dietary and fitness consulting services. Discuss how this program could affect pension costs. Can you assess whether these concerns appear to be valid (hint: consider using CORREL())? Keeping in mind that managers of companies have both social responsibilities to employees and fiduciary responsibilities to shareholders, what considerations should the manager take into account when making this decision?

7. Comment on the trend in plan asset returns. Have managers been accurate in their expected rates of return? When pension plan assets consistently deviate from expected returns, managers may either adjust the riskiness of the plans investment portfolio, or adjust the expected rate of return. Discuss the ethical dilemma managers face when confronted with those two choices.

I can send you the excel spreadsheet, and I need this done by thursday!!