Note: Please show how to solve in Excel and provide formulas.

You have been appointed as a human resource support staff intem at SunShine company. Your first project is to develop a retirement plan that the company can use to support the its staff in their retirement planning. 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 John is 34 years old and would like to establish a retirement plan. Below is his current profile: 34 Age: Salary: Expected annual percentage increase in salary: Percentage of annual salary contributed to retirement: Current total retirement savings: Annual rate of return on retirement savings: Expected age of retirement: Expected annual expenses after retirement (currently): Expected Rate of return on retirement savings after retirement: Income tax rate post-retirement: $ 85,000 3.4% 5.0% $ 129,000 4.125% 65 $52,000 3% 12% At the moment, John's employer contributes 5.8% of his salary to his retirement fund. We can assume that this 5.8% contribution from John's employer will be the same as long as John is working with the company. On top of the 5% of annual salary contributed to his retirement, John also made an additional contribution of $4,000 to his retirement fund before taxes (tax free). Note: John is allowed to make an additional annual contribution to his retirement fund before taxes (tax free) up to $12,000. Also, assume an inflation rate of 2%. o Your report must address the following: a) Outline the factors that will have the greatest impact on John'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 make important contributions to John's retirement. e) The report must be written in a narrative style. Do not use bullet points to answer the listed questions (points will be deducted if you do so). You have been appointed as a human resource support staff intem at SunShine company. Your first project is to develop a retirement plan that the company can use to support the its staff in their retirement planning. 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 John is 34 years old and would like to establish a retirement plan. Below is his current profile: 34 Age: Salary: Expected annual percentage increase in salary: Percentage of annual salary contributed to retirement: Current total retirement savings: Annual rate of return on retirement savings: Expected age of retirement: Expected annual expenses after retirement (currently): Expected Rate of return on retirement savings after retirement: Income tax rate post-retirement: $ 85,000 3.4% 5.0% $ 129,000 4.125% 65 $52,000 3% 12% At the moment, John's employer contributes 5.8% of his salary to his retirement fund. We can assume that this 5.8% contribution from John's employer will be the same as long as John is working with the company. On top of the 5% of annual salary contributed to his retirement, John also made an additional contribution of $4,000 to his retirement fund before taxes (tax free). Note: John is allowed to make an additional annual contribution to his retirement fund before taxes (tax free) up to $12,000. Also, assume an inflation rate of 2%. o Your report must address the following: a) Outline the factors that will have the greatest impact on John'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 make important contributions to John's retirement. e) The report must be written in a narrative style. Do not use bullet points to answer the listed questions (points will be deducted if you do so)