You have been appointed as a human resource support staff intern 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: age: 34 salary: $ 85,000 expected annual percentage increase in salary: 3.4% percentage of annual salary contributed to retirement: 5.0% current total retirement savings: $ 129,000 annual rate of return on retirement savings: 4.125% expected age of retirement: 65 expected annual expenses after retirement (currently): $52,000 expected rate of return on retirement savings after retirement: 3% income tax rate post-retirement: 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%. isds 361b dr. newby page 2 b. tasks o you are to develop a spreadsheet model that could be used to assist john with his retirement planning (10 points). o your model must follow the guidelines given in chapter 10 spreadsheet models for good spreadsheet modeling.

a) separate the parameters from the model: this enables the user to update the model parameters without the risk of mistakenly creating an error in a formula.
b) document the model and use proper formatting and color as needed: a good spreadsheet model is well documented. clear labels and proper formatting and alignment facilitate navigation and understanding. use an influence diagram if necessary.
c) use simple formulas: clear, simple formulas can reduce errors and make maintaining the spreadsheet easier. long and complex calculations should be divided into several cells
d) the results should provide information to assist you in writing your report addressing the questions required above (in the tasks section). o using the submission section on titanium, submit a copy of your excel model.

from your analysis, you are to produce a managerial report (about 800 words) that summarizes your evaluation of the analysis results (10 points). 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). o submit your report (in microsoft word) via titanium using the assignment submission section.