Tim is 37 years old and would like to establish a retirement plan. Develop a spreadsheet model that could be used to assist Tim with retirement planning. Your model should include the following input parameters:

• Tims current age = 37 years
• Tims current total retirement savings = $259,000
• Annual rate of return on retirement savings = 4%
• Tims current annual salary = $145,000
• Tims expected annual percentage increase in salary = 2%
• Tims percentage of annual salary contributed to retirement = 6%
• Tims expected age of retirement = 65
• Tims expected annual expenses after retirement (current dollars) = $90,000
• Rate of return on retirement savings after retirement = 3%
• Income tax rate postretirement = 15%

Assume that Tims employer contributes 6% of Tims 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%.

Provide Report

1. Your spreadsheet model should provide the accumulated savings at the onset of retirement as well as the age at which funds will be depleted (given assumptions on the input parameters)?
2. 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 contributions. Similarly, consider other factors you think might be important?
3. Develop a report for Tim outlining the factors that will have the greatest impact on his retirement?

															No tax and 3% return on retirement savings
Age	Salary (2%)	Employer Retirement Contribution (6%)	Tim's Contribution to Retirement	Tim's current total retirement savings	Savings at end of the year (4%)	Gross Savings at end of the year		Age	Current Salary	Expenses every year	Saving after expenses	Saving after tax deductions (15%)	Gross Savings at end of the year		Age	Current Salary	Expenses every year	Saving after expenses	Gross Savings at end of the year (3%)
36						$2,59,000		66	$17,94,694	$90,000	$17,04,694	$16,91,194	$17,41,930		66	$17,94,694	$90,000	$17,04,694	$17,55,835
37	$1,45,000	$8,700	$6,000	$2,59,000	$2,69,360	$2,84,060		67	$17,41,930	$90,000	$16,51,930	$16,38,430	$16,87,582		67	$17,55,835	$90,000	$16,65,835	$17,15,810
38	$1,47,900	$8,874	$6,120	$2,84,060	$2,95,422	$3,10,416		68	$16,87,582	$90,000	$15,97,582	$15,84,082	$16,31,605		68	$17,15,810	$90,000	$16,25,810	$16,74,584
39	$1,50,858	$9,051	$6,242	$3,10,416	$3,22,833	$3,38,127		69	$16,31,605	$90,000	$15,41,605	$15,28,105	$15,73,948		69	$16,74,584	$90,000	$15,84,584	$16,32,121
40	$1,53,875	$9,233	$6,367	$3,38,127	$3,51,652	$3,67,252		70	$15,73,948	$90,000	$14,83,948	$14,70,448	$15,14,561		70	$16,32,121	$90,000	$15,42,121	$15,88,385
41	$1,56,953	$9,417	$6,495	$3,67,252	$3,81,942	$3,97,854		71	$15,14,561	$90,000	$14,24,561	$14,11,061	$14,53,393		71	$15,88,385	$90,000	$14,98,385	$15,43,337
42	$1,60,092	$9,606	$6,624	$3,97,854	$4,13,768	$4,29,998		72	$14,53,393	$90,000	$13,63,393	$13,49,893	$13,90,390		72	$15,43,337	$90,000	$14,53,337	$14,96,937
43	$1,63,294	$9,798	$6,757	$4,29,998	$4,47,198	$4,63,752		73	$13,90,390	$90,000	$13,00,390	$12,86,890	$13,25,497		73	$14,96,937	$90,000	$14,06,937	$14,49,145
44	$1,66,559	$9,994	$6,892	$4,63,752	$4,82,302	$4,99,188		74	$13,25,497	$90,000	$12,35,497	$12,21,997	$12,58,657		74	$14,49,145	$90,000	$13,59,145	$13,99,919
45	$1,69,891	$10,193	$7,030	$4,99,188	$5,19,156	$5,36,379		75	$12,58,657	$90,000	$11,68,657	$11,55,157	$11,89,811		75	$13,99,919	$90,000	$13,09,919	$13,49,217
46	$1,73,288	$10,397	$7,171	$5,36,379	$5,57,834	$5,75,402		76	$11,89,811	$90,000	$10,99,811	$10,86,311	$11,18,901		76	$13,49,217	$90,000	$12,59,217	$12,96,993
47	$1,76,754	$10,605	$7,314	$5,75,402	$5,98,418	$6,16,337		77	$11,18,901	$90,000	$10,28,901	$10,15,401	$10,45,863		77	$12,96,993	$90,000	$12,06,993	$12,43,203
48	$1,80,289	$10,817	$7,460	$6,16,337	$6,40,991	$6,59,268		78	$10,45,863	$90,000	$9,55,863	$9,42,363	$9,70,634		78	$12,43,203	$90,000	$11,53,203	$11,87,799
49	$1,83,895	$11,034	$7,609	$6,59,268	$6,85,639	$7,04,282		79	$9,70,634	$90,000	$8,80,634	$8,67,134	$8,93,148		79	$11,87,799	$90,000	$10,97,799	$11,30,733
50	$1,87,573	$11,254	$7,762	$7,04,282	$7,32,453	$7,51,469		80	$8,93,148	$90,000	$8,03,148	$7,89,648	$8,13,337		80	$11,30,733	$90,000	$10,40,733	$10,71,955
51	$1,91,324	$11,479	$7,917	$7,51,469	$7,81,528	$8,00,925		81	$8,13,337	$90,000	$7,23,337	$7,09,837	$7,31,132		81	$10,71,955	$90,000	$9,81,955	$10,11,414
52	$1,95,151	$11,709	$8,075	$8,00,925	$8,32,962	$8,52,746		82	$7,31,132	$90,000	$6,41,132	$6,27,632	$6,46,461		82	$10,11,414	$90,000	$9,21,414	$9,49,056
53	$1,99,054	$11,943	$8,237	$8,52,746	$8,86,856	$9,07,036		83	$6,46,461	$90,000	$5,56,461	$5,42,961	$5,59,250		83	$9,49,056	$90,000	$8,59,056	$8,84,828
54	$2,03,035	$12,182	$8,401	$9,07,036	$9,43,317	$9,63,901		84	$5,59,250	$90,000	$4,69,250	$4,55,750	$4,69,422		84	$8,84,828	$90,000	$7,94,828	$8,18,673
55	$2,07,096	$12,426	$8,569	$9,63,901	$10,02,457	$10,23,452		85	$4,69,422	$90,000	$3,79,422	$3,65,922	$3,76,900		85	$8,18,673	$90,000	$7,28,673	$7,50,533
56	$2,11,238	$12,674	$8,741	$10,23,452	$10,64,390	$10,85,805		86	$3,76,900	$90,000	$2,86,900	$2,73,400	$2,81,602		86	$7,50,533	$90,000	$6,60,533	$6,80,349
57	$2,15,462	$12,928	$8,916	$10,85,805	$11,29,237	$11,51,081		87	$2,81,602	$90,000	$1,91,602	$1,78,102	$1,83,445		87	$6,80,349	$90,000	$5,90,349	$6,08,059
58	$2,19,772	$13,186	$9,094	$11,51,081	$11,97,124	$12,19,404		88	$1,83,445	$90,000	$93,445	$79,945	$82,344		88	$6,08,059	$90,000	$5,18,059	$5,33,601
59	$2,24,167	$13,450	$9,276	$12,19,404	$12,68,180	$12,90,906									89	$5,33,601	$90,000	$4,43,601	$4,56,909
60	$2,28,650	$13,719	$9,461	$12,90,906	$13,42,543	$13,65,723									90	$4,56,909	$90,000	$3,66,909	$3,77,916
61	$2,33,223	$13,993	$9,651	$13,65,723	$14,20,352	$14,43,996									91	$3,77,916	$90,000	$2,87,916	$2,96,554
62	$2,37,888	$14,273	$9,844	$14,43,996	$15,01,756	$15,25,873									92	$2,96,554	$90,000	$2,06,554	$2,12,750
63	$2,42,646	$14,559	$10,041	$15,25,873	$15,86,908	$16,11,507									93	$2,12,750	$90,000	$1,22,750	$1,26,433
64	$2,47,499	$14,850	$10,241	$16,11,507	$16,75,967	$17,01,058									94	$1,26,433	$90,000	$36,433	$37,526
65	$2,52,449	$15,147	$10,446	$17,01,058	$17,69,101	$17,94,694

Provide the full report of above given case problem