Assignment Task Sarah is currently 30 years old. She had been looking for a job since she moved to Toronto, Ontario. Her persistence and high qualifications were the main reasons for the recent offer she received from a well-known retail company as a business analyst. Sarah would like to establis

Comments Off on Assignment Task Sarah is currently 30 years old. She had been looking for a job since she moved to Toronto, Ontario. Her persistence and high qualifications were the main reasons for the recent offer she received from a well-known retail company as a business analyst. Sarah would like to establis

Assignment Task
Sarah is currently 30 years old. She had been looking for a job since she moved to Toronto, Ontario. Her persistence and high qualifications were the main reasons for the recent offer she received from a well-known retail company as a business analyst. Sarah would like to establish a sound retirement plan to make sure that she is financially stable in 35 years. Therefore, she has decided to invest a fixed portion of her annual salary into her retirement fund (tax free) at the end of every year for the next 35 years (i.e., she plans to retire when she is 64). According to her contract, her employer is also going to contribute to her (tax free) retirement fund. Below you can find some information about Sarah’s retirement fund:

Sarah’s current (i.e., year 1) annual salary: $120,000
Sarah’s expected annual percentage increase in salary: %2.5
Sarah’s percentage of annual salary contributed to retirement: %7.5
The employer’s contribution to Sarah’s retirement fund: 5% of her annual salary
Rate of return on Retirement Funds: 2.1%
Assume that Sarah is willing to make an additional $5000 annual contribution to her retirement fund (tax free). In addition, assume that the return on Retirement Funds is calculated and deposited at the beginning of each year based on the account balance.

Please note that all dollar values in this assignment are current value. In other words, you do not need to convert earnings and expenditures to their present value.

Develop a spreadsheet that could be used to assist Sarah with her retirement planning.

1. Create a two-way data table that shows the balance of Sarah’s retirement fund at the end of 35 years for various levels of annual contribution made by herself. Vary the percentage of her annual salary that she contributes to her retirement fund from 5.5% to 10% in increments of 0.5% and vary her fixed additional contribution from $3000 to $8000 in increments of $1000.

2. Assuming all parameters but “Sarah’s percentage of annual salary contributed to retirement” to remain the same, what value for this variable will bring about a total balance of $1,500,000 in Sarah’s retirement fund at the end of year 35?

3. Sarah is not sure if she can afford to retire at the age of 64. To help her gain a better understanding of the situation, develop a spreadsheet about her post-retirement budget and spending. Below is some information that you will need to build the spreadsheet. 2

Sarah’s expected expense in the first year after retirement (excluding income tax): $80,000 • Income tax rate (post-retirement): 15%
Annual inflation rate: 2.5%
Rate of return on Retirement Funds: 2.1%
Hint1: Sarah expects to spend 80,000 during her first year of retirement. Starting second year, her expenditure will increase every year by the inflation rate (i.e., 2.5%).

Hint2: Sarah’s withdrawal (i.e., income) from her retirement fund will equal her expense plus income tax. In other words, Sarah must pay tax on the amount that she withdraws from her account.

Hint3: Do not forget that this account is tax-free (i.e., no tax will be deducted from the return on retirement funds or the balance of the account).

Like pre-retirement, assume that the return on Retirement Funds is calculated and deposited at the beginning of each year based on the account balance.

Build a spreadsheet that calculates Sarah’s retirement fund balance for 15 years after her retirement. Will Sarah’s retirement fund be depleted 15 years after her retirement?

Create a one-way data table that shows the balance of Sarah’s Retirement Funds after 15 years of her retirement. Vary “Sarah’s expected expense in the first year after retirement (excluding income tax” from $55,000 to $90,000 in increments of $5000.

4. Use scenario manager to calculate the balance of Sarah’s Retirement Funds after 15 years of her retirement in the following scenarios: Base Case Best Case Worst Case Sarah’s expected expense in the first year after retirement (excluding income tax) $75,000 2.5% $65,000 1.5% $90,000 3.5% Annual inflation rate Income tax rate (post-retirement) 15%

5. Write a short report for Sarah and explain to her your findings, outline the parameters that influence her retirement plan the most, and provide suggestions

Get 30% off your orders today

X
WeCreativez WhatsApp Support
Our customer support team is here to answer your questions. Ask us anything!