Question

You have been appointed as a human resource support staff intern at ABC company. Your first...

You have been appointed as a human resource support staff intern at ABC company. Your first project is to develop a retirement plan that the company can use to support the company’s staff on their retirement. A sample employee profile and the requirements for their retirement plan case scenario have been given to you to develop the spreadsheet model.

A. Sample Profile Tim is 37 years old and would like to establish a retirement plan. Below is his current profile:

Age: 37

Salary: $ 145,000

Expected annual percentage increase in salary: 2%

Percentage of annual salary contributed to retirement: 6%

Current total retirement savings: $ 259,000

Annual rate of return on retirement savings: 4%

Expected age of retirement: 65

Expected annual expenses after retirement (currently): $90,000

Rate of return on retirement savings after retirement: 3%

Income tax rate postretirement: 15%

Assume that Tim’s employer contributes 6% of his salary to his retirement fund. Tim can make an additional annual contribution to his retirement fund before taxes (tax free) up to a contribution of $16,000. Assume that he contributes $6,000 per year. Also, assume an inflation rate of 2%

B. Tasks

o You are to develop a spreadsheet model that could be used to assist Tim with retirement planning

o From your analysis, produce a managerial report that summarizes your findings.

Your report must include the following:

a) Outline the factors that will have the greatest impact on Tim’s retirement

b) Accumulated savings at the onset of retirement as well as the age at which funds will be depleted (given assumptions on the input parameters).

c) A data table to demonstrate the sensitivity of the age at which funds will be depleted to the retirement age and additional pre-tax contributions.

d) You may add any other factors that you think might be important contributions to Tim’s retirement

Homework Answers

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Assume that you are 30 years old​ today, and that you are planning on retirement at...
Assume that you are 30 years old​ today, and that you are planning on retirement at age 65. Your current salary is​ $45,000 and you expect your salary to remain constant as long as you work. To save for your​ retirement, you plan on making annual contributions to a retirement account. Your first contribution will be made on your 31st birthday and will be​ 8% of this​ year's salary. ​ Likewise, you expect to deposit​ 8% of your salary each...
Assume that you are 30 years old today, and that you are planning on retirement at...
Assume that you are 30 years old today, and that you are planning on retirement at age 65. Your current salary is $40,000 and you expect your salary to increase at a rate of 4% per year as long as you work. To save for your retirement, you plan on making annual contributions to a retirement account. Your first contribution will be made on your 31st birthday and will be 9% of this year's salary. Likewise, you expect to deposit...
Assume that you are 24 years old today, and that you are planning on retiring at...
Assume that you are 24 years old today, and that you are planning on retiring at age 65. Your salary is expected to increase at a rate of 5% per year as long as you work. To save for your retirement, you plan on making annual contributions to a retirement account. Your first contribution will be made on your 25th birthday and will be 10% of your salary of $50,000 at that time. You plan to put aside 10% of...
On the first day of your new job, your employer presents you with the following two...
On the first day of your new job, your employer presents you with the following two plans regarding retirement contributions: • Plan A: The company will make an annual contribution of $20,000 into your retirement account for the next 30 years. The first payment starts 1 year from now. • Plan B: If you stay with the company for 3 years, then the company makes annual contributions starting from $20,000 and grow at 5% per year. The first payment starts...
You are starting your first professional job after graduation and evaluating three alternatives for your retirement...
You are starting your first professional job after graduation and evaluating three alternatives for your retirement savings in conjunction with your college loan repayment and car purchase plans. Your company 401K retirement plan will match your retirement savings contribution up to $6000/year (about 8% of your annual salary). Your 401K retirement investment fund has a long-term annual return of about 7%. Alterative-1: You can repay your college loan at $6000/yr in 5 years, but your budget with a new-car purchase...
You have recently been appointed as a pension fund specialist for your company and you have...
You have recently been appointed as a pension fund specialist for your company and you have been tasked to perform valuation on some of the pension fund portfolios. Your company wants to know the minimum annual return required on the pension fund in order to make all required payments over the next five years and not diminish the current asset base. The fund currently has assets of R500 million. Required: 3.1. Determine the rate of return if outflows are expected...
You have recently been appointed as a pension fund specialist for your company and you have...
You have recently been appointed as a pension fund specialist for your company and you have been tasked to perform valuation on some of the pension fund portfolios. Your company wants to know the minimum annual return required on the pension fund in order to make all required payments over the next five years and not diminish the current asset base. The fund currently has assets of R500 million. Required: 3.1 Determine the rate of return if outflows are expected...
You recently graduated from university, and your job search led you to Coles Group Limited. Since...
You recently graduated from university, and your job search led you to Coles Group Limited. Since you thought the company’s business was very promising, you accepted their job offer. As you are finishing your employment paperwork, Michel, who works in the Finance Department, stops by to inform you about the company’s new superannuation plan. Australian companies offer membership of a superannuation fund to their employees, where their Superannuation Guarantee contributions are saved. Superannuation funds have concessional tax arrangements, which saves...
For this assignment, you will read the scenario and then use the provided Excel and Word...
For this assignment, you will read the scenario and then use the provided Excel and Word document templates to complete your assignment before uploading them to the assignment submission area. Scenario Larry and Beth are both married, working adults. They both plan for retirement and consider the $6,000 annual contribution a must. First, consider Beth's savings. She began working at age 20 and began making an annual contribution to her IRA of $6,000 each year until age 32 (12 contributions)....
1. If you have been in an employer-sponsored pension plan for three years and you leave...
1. If you have been in an employer-sponsored pension plan for three years and you leave your employer well before retirement, Select one: a. you are not entitled to the employer's contribution to your plan. b. you can transfer the funds to a RRIF. c. you can transfer the plan to your new employer if the RPP is similar. d. you can withdraw the funds tax-free within 30 days of departure. 2. Morley is retiring on his 66th birthday. How...