In the Modeling & Problem Solving module, the second problem dealt with a client who was planning for retirement. While meeting with him to discuss his needs further he asked for you to please build a model for him so he can see what his total retirement savings would likely be given the following:

· His current age is 46 (He happens to have been born on January 1^st).

· His current annual salary is $126,000 and he gets a 2% salary increase annually.

· He has decided that he wants to retire on his 65^th birthday.

· He started this year with retirement savings of $137,000

· His annual contributions to his savings are 5.95% of his annual salary

· His employer’s contributions to his savings are 7.95% of his annual salary

· The annual rate of return on his savings account is 7%.

Assumptions:

· His retirement contributions show up all at once on 12/31 each year.

· His returns all appear at once on 12/31 each year.

· Salary increases take effect as of 1/1 each year, always a nice birthday present!

Given the above, for this year we know (in rounded dollars):

· His contributions will be ($126,000 * 5.95%) = $7,497

· His employer’s contributions will be ($126,000 * 7.95%) = $10,017

· Together, the total contributions will be ($7,497 + $10,017) = $17,514

· His annual return on his savings will be ($137,000 * 7%) = $9,590

· His account therefore will grow by ($17,514 + $9,590) = $27,104

· At the end of this year his account will have ($137,000 + $27,104) = $164,104

In a this Excel file, do the following:

1. In a new sheet, create an influence diagram for this analysis.

2. In another new sheet, construct a model containing three modules: Parameters, Outputs, and Calculations. Name this sheet “Model”.

In the models where there have been only a few time periods in the analysis we have made one column per time period. When an analysis contains many years, like this one, I recommend flipping it so that the time periods go down the rows instead of across the columns.

Age	Salary	Contribution	Employer Cont.	Total Contribution	Annual Return	Annual Growth	Balance on 12/31
46	$126,000	$7,497	$10,017	$17,514	$9,590	$27,104	$164,104

When you're done, the Excel file (workbook) should contain three tabs (sheets): This one, the Influence Chart, and the Model.