Question

In: Accounting

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

please show the formula as well

Expert Solution



	Current Total retirement Savings	$259,000
	Number of years to retirement	28	(65-37)
	Annual Rate of return=4%=0.04
	Future value of current saving at retirement	$ 776,664	(259000*(1.04^28)
	Future Value at retirement=(Contribution)*(1+i)^(28-N)
	i=Rate of return=0.04, N=Year of Contribution)
	Salary in year 2=1.02*145000	147900
	Salary in year (N+1)=1.02* (Salary in Year(N))
	N	A	B=0.12*A	C	D=B+C	E=D*(1.04^(28-N)
	Year	Salary	Contribution	Additional	Total	Future Value
			of Tim+Employer	Contribution	Contribution	at Retirement
Age 37	0				$259,000	$ 776,664
	1	$145,000	$17,400	$6,000	$23,400	$ 67,471
	2	$147,900	$17,748	$6,000	$23,748	$ 65,841
	3	$150,858	$18,103	$6,000	$24,103	$ 64,255
	4	$153,875	$18,465	$6,000	$24,465	$ 62,711
	5	$156,953	$18,834	$6,000	$24,834	$ 61,210
	6	$160,092	$19,211	$6,000	$25,211	$ 59,748
	7	$163,294	$19,595	$6,000	$25,595	$ 58,326
	8	$166,559	$19,987	$6,000	$25,987	$ 56,941
	9	$169,891	$20,387	$6,000	$26,387	$ 55,593
	10	$173,288	$20,795	$6,000	$26,795	$ 54,281
	11	$176,754	$21,211	$6,000	$27,211	$ 53,003
	12	$180,289	$21,635	$6,000	$27,635	$ 51,759
	13	$183,895	$22,067	$6,000	$28,067	$ 50,548
	14	$187,573	$22,509	$6,000	$28,509	$ 49,368
	15	$191,324	$22,959	$6,000	$28,959	$ 48,219
	16	$195,151	$23,418	$6,000	$29,418	$ 47,099
	17	$199,054	$23,886	$6,000	$29,886	$ 46,009
	18	$203,035	$24,364	$6,000	$30,364	$ 44,946
	19	$207,096	$24,851	$6,000	$30,851	$ 43,911
	20	$211,238	$25,349	$6,000	$31,349	$ 42,903
	21	$215,462	$25,855	$6,000	$31,855	$ 41,920
	22	$219,772	$26,373	$6,000	$32,373	$ 40,962
	23	$224,167	$26,900	$6,000	$32,900	$ 40,028
	24	$228,650	$27,438	$6,000	$33,438	$ 39,118
	25	$233,223	$27,987	$6,000	$33,987	$ 38,231
	26	$237,888	$28,547	$6,000	$34,547	$ 37,366
	27	$242,646	$29,117	$6,000	$35,117	$ 36,522
Age 65	28	$247,499	$29,700	$6,000	$35,700	$ 35,700
					SUM	$ 2,170,650
	Total Amount accumulated at retirement	$ 2,170,650
	Expected annual expense at current price	$90,000
	Rate of inflation=2%	0.02
	Expected annual expense at future price	$ 156,692	(90000*(1.02^28)
	Income tax after retirement=15%=0.15
	Expected annual before tax requirement	$ 184,344	(156692/(1-0.15)
	Rate of return after retirement	3%
	Inflation agjusted rate of return=3%-2%=1%	0.01
	Number of years Tim can get the required return after retirement	12.59051396	(Using NPER function With Rate=0.01,Pmt=184344, PV=-2170650

	Tim can expect to get the required amount for 12.6 years after retirement(till the age of 77.6 years

a)	Factors that will have greatest impact:
	Return rate before and after retirement
	Inflation
	Taxes after retirement
b)	Accumulated savings at retirement	$ 2,170,650

c)	Years to deplete the retirement fund	12.6

d)	Since, the depletion will occur after 12.6 years,
	Tim will need additional savings
	The number of years to last should be 25 years

ekkarill92 answered 1 year ago

You have been appointed as the Human Resource Manager of Milynn Deeds Insurance Company. For you...

You have been appointed as the Human Resource Manager of Milynn Deeds Insurance Company. For you to properly define the duties, responsibilities and accountabilities of a job, it is crucial to conduct a job analysis. Discuss the uses of job analysis information.

Assuming you have been appointed as the Human Resource Manager in a firm and you have...

Assuming you have been appointed as the Human Resource Manager in a firm and you have been asked to draw an occupational health policy/programme in the wake of the COVID-19 pandemic, discuss four (4)elements you would incorporate into designing the policy.

You have been promoted to work as human resource manager for the ABC OVERSEAS COMPANY, you...

You have been promoted to work as human resource manager for the ABC OVERSEAS COMPANY, you new job will be here in CANADA branch. 1.How will you achieve employment equity? 2.What are the variables of Canadian culture that you will realize at your new workplace? 3.Will this Organizational Culture impact positively on your new work environment? Why? 4.What can you do as a manager to improve employee engagement?with examples of your own? How can we protect employment at Canadian workplace?...

Assignment 3: Human Resource Project Plan. You have recently been appointed head of human resources and...

Assignment 3: Human Resource Project Plan. You have recently been appointed head of human resources and are now in charge of managing a small team. Your next project is to revamp the compensation and benefits package employees are receiving at your company. Design a plan for the human resource project without the use of MS Project or similar software. Note: You will have to make assumptions or create fictitious data for this assignment. Be clear about these assumptions and data...

You have just been hired as a manager of ABC Company. Your first week on the...

You have just been hired as a manager of ABC Company. Your first week on the job you are asked by upper management to review payroll. You notice the company is not paying any taxes for its employee. You ask the payroll director why there are no taxes paid and he states that all of the workers are considered independent contractors. What would you do in this case?

You have just been hired as the new human resource manager for Delta Inc. On your...

You have just been hired as the new human resource manager for Delta Inc. On your first day at work the CEO wants to meet with you to discuss a proposed evaluation of compensation practices at Delta. Unfortunately, the CEO is very busy and wants you to - in a short meeting - thoroughly describe the most important generic aspects of a compensation system that should be considered when evaluating Delta’s compensation practices. The CEO tells you to plan on...

You have just been appointed as the County Commissioner of Hazard County. Your first day on...

You have just been appointed as the County Commissioner of Hazard County. Your first day on the job you have the following conversations: Ludwig mentions that Frank, the local rancher, is inflating land prices by buying too much land. This is an example of _______. (a negative externality a pecuniary externality a positive externality not an externality) George mentions that Ms. Daisy has planted some lovely new roses in front of her store. This is an example of ______. (a...

Assume that you have been appointed as Marketing Manager for a company in your region. In...

Assume that you have been appointed as Marketing Manager for a company in your region. In the past, many of their new products were unsuccessful. The senior management team now wants to focus on proper idea generation for their new products and also wants to select profitable market segments correctly. i) How would you select profitable market segment(s) for this company? Justify your answer. ii) Discuss your strategies on how to generate proper ideas for this company’s new products.

As the human resource director, you have been assigned to prepare a letter to be used...

As the human resource director, you have been assigned to prepare a letter to be used to layoff a number of employees. The decision is based on the loss of a major contract. The top management believes the state of the economy overseas is the major reason for the cancellation. You have been told that if a new contract is signed the laid off employees will be rehired. Prepare this into a Negative Letter.

Imagine that you have been appointed Director of Clinical Decision Support at a healthcare delivery system....

Imagine that you have been appointed Director of Clinical Decision Support at a healthcare delivery system. This healthcare system consists of several large hospitals and multiple outpatient clinics and uses the same EHR system across the enterprise. There has been limited CDS activity at the institution prior to your arrival. Now, with the increasing need to provide increased care value, the appropriate use of CDS is an institutional priority. The current CDS available at your institution consists primarily of off-the-shelf...