CASE PROBLEM: RETIREMENT PLAN Tim is 32 ycors old and would like to establish a retirement plan. Develop a spreadsheet mmodel hat could be used to assist Tim with retirement planning. Your model should include the following input parameters: Tim's current age = 37 years Tim's current total retirement savings Annual rate of return on $259, 000 retirement savings 4% Tim's current annual salary Tim's expected annual percentage increase in salary 2% Tim's percentage of annual salary contributed to retirement 6 % Tim's expected age of retirement 65 Tim's expected annual expenses after retirement (current dollars) $90,000 Rate of return on retirement savings after retirement Income tax rate postretirement = 15 % $145,000 3% Assume that Tim's employer contributes 6% of Tim's 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%. Managerial Report Your spreadsheet model should provide the accumulated savings at the onset of retire- well as the age at which funds will be depleted (given assumptions on the input ment as parameters). As a feature of your spreadsheet model, build a data table to demonstrate the sensitivity of the age at which funds will be depleted to the retirement age and additional pre-tax con- tributions. Similarly, consider other factors you think might be important. Develop a report for Tim outlining the factors that will have the greatest impact retirement. on his CASE PROBLEM: RETIREMENT PLAN Tim is 32 ycors old and would like to establish a retirement plan. Develop a spreadsheet mmodel hat could be used to assist Tim with retirement planning. Your model should include the following input parameters: Tim's current age = 37 years Tim's current total retirement savings Annual rate of return on $259, 000 retirement savings 4% Tim's current annual salary Tim's expected annual percentage increase in salary 2% Tim's percentage of annual salary contributed to retirement 6 % Tim's expected age of retirement 65 Tim's expected annual expenses after retirement (current dollars) $90,000 Rate of return on retirement savings after retirement Income tax rate postretirement = 15 % $145,000 3% Assume that Tim's employer contributes 6% of Tim's 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%. Managerial Report Your spreadsheet model should provide the accumulated savings at the onset of retire- well as the age at which funds will be depleted (given assumptions on the input ment as parameters). As a feature of your spreadsheet model, build a data table to demonstrate the sensitivity of the age at which funds will be depleted to the retirement age and additional pre-tax con- tributions. Similarly, consider other factors you think might be important. Develop a report for Tim outlining the factors that will have the greatest impact retirement. on his