Can you thoroughly explain how to do each step?

Exercise 2: Retirement account You have joined one of the top business schools in the nation, so your older relative, Mr. Clark, wants some help to plan his retirement and to figure out how much he will have saved when he is 65. He is currently 40 years old, making $175,000 per year. He has $225,000 in his retirement account (401(k) plan) and has invested another $85,000 on his own in a personal account. He expects that his earnings will increase at least 3% each year. Conventional wisdom dictates that in the long run, the stock market returns an average of 8% per year. Therefore, he expects that his 401(k) and his personal stock account will have a net annual gain of 8% (Note: Stock market returns need to be calculated on all the contributions for both the 401(k) account and the personal account). Mr. Clark contributes 6% of his salary to his 401(k) and his employer contributes another 5% of his salary. On his personal account, Mr. Clark is confident that he can add $10,000 to his personal account this year and increase his annual contribution by 4% each year after. He wants to know the total balance (401(k) and personal stock account) at the end of each year from now until he is 65 years old. KEY ASSUMPTION: For simplicity, assume that the annual contributions to both the 401(k) account and the personal stock account are made at the beginning of the year. This way, the contributions will accrue returns for the entire year. (This isn't true in the "real world, but making this assumption significantly simplifies this model.) Step 1 Go to worksheet "Sensitivity analysis and complete the calculations in the Excel spreadsheet to calculate how Mr. Clark's total retirement and individual account will grow each year. You MUST use absolute cell references for the growth rates, market returns, etc. If you do not, the second step will not work correctly. Step 2 Mr. Clark wants to know how his retirement account will look if his salary increases on average 2%, 3%, 4%, 5%, 6% or 7% each year until he is 65 and if the market returns 4%, 6%, 8%, 10%, 12% and an outrageous 15%. That is, he wants an output of his final retirement like this: (The first row represents % increase in his earnings, and the first column represents the various market return scenarios.). The cell in gray (denoted "First Cell below) represents the model that you will build in Step 1 - earnings grow at 3% and the market returns 8%. Increase in earnings First Cell 3% 4% 5% 6% 7% 8 4% 6% Average Market Return 8% 10% 12% 15% 1 X fx F G H I J K L M Retirement Savings Assumptions $175,000 6% 5 6 7 Current Earnings Earnings Increase Stock MKT return 3% 8% Mr. Clark's Contribution To 401(k) Employer Contribution to 401(k) Additional savings Increase in add. Savings 5% $10,000 Previous Year's Balance Mr. Clark's Contribution 401(k) Plan Employer Contribution Stock Market Earnings 2018 Plan Total Previous Year Balance Presiden er Personal Account Additional Stock Market Savings Earnings deltier Salary 401K Plan Total Acct Total Yearly total Mr. Clark's Age 40 Sensitivity Analysis 2% Increase in earnings 3% 4% 5% 6% 7% 8% Average Market Return 6% 8% 10% 12% 15% Exercise 2: Retirement account You have joined one of the top business schools in the nation, so your older relative, Mr. Clark, wants some help to plan his retirement and to figure out how much he will have saved when he is 65. He is currently 40 years old, making $175,000 per year. He has $225,000 in his retirement account (401(k) plan) and has invested another $85,000 on his own in a personal account. He expects that his earnings will increase at least 3% each year. Conventional wisdom dictates that in the long run, the stock market returns an average of 8% per year. Therefore, he expects that his 401(k) and his personal stock account will have a net annual gain of 8% (Note: Stock market returns need to be calculated on all the contributions for both the 401(k) account and the personal account). Mr. Clark contributes 6% of his salary to his 401(k) and his employer contributes another 5% of his salary. On his personal account, Mr. Clark is confident that he can add $10,000 to his personal account this year and increase his annual contribution by 4% each year after. He wants to know the total balance (401(k) and personal stock account) at the end of each year from now until he is 65 years old. KEY ASSUMPTION: For simplicity, assume that the annual contributions to both the 401(k) account and the personal stock account are made at the beginning of the year. This way, the contributions will accrue returns for the entire year. (This isn't true in the "real world, but making this assumption significantly simplifies this model.) Step 1 Go to worksheet "Sensitivity analysis and complete the calculations in the Excel spreadsheet to calculate how Mr. Clark's total retirement and individual account will grow each year. You MUST use absolute cell references for the growth rates, market returns, etc. If you do not, the second step will not work correctly. Step 2 Mr. Clark wants to know how his retirement account will look if his salary increases on average 2%, 3%, 4%, 5%, 6% or 7% each year until he is 65 and if the market returns 4%, 6%, 8%, 10%, 12% and an outrageous 15%. That is, he wants an output of his final retirement like this: (The first row represents % increase in his earnings, and the first column represents the various market return scenarios.). The cell in gray (denoted "First Cell below) represents the model that you will build in Step 1 - earnings grow at 3% and the market returns 8%. Increase in earnings First Cell 3% 4% 5% 6% 7% 8 4% 6% Average Market Return 8% 10% 12% 15% 1 X fx F G H I J K L M Retirement Savings Assumptions $175,000 6% 5 6 7 Current Earnings Earnings Increase Stock MKT return 3% 8% Mr. Clark's Contribution To 401(k) Employer Contribution to 401(k) Additional savings Increase in add. Savings 5% $10,000 Previous Year's Balance Mr. Clark's Contribution 401(k) Plan Employer Contribution Stock Market Earnings 2018 Plan Total Previous Year Balance Presiden er Personal Account Additional Stock Market Savings Earnings deltier Salary 401K Plan Total Acct Total Yearly total Mr. Clark's Age 40 Sensitivity Analysis 2% Increase in earnings 3% 4% 5% 6% 7% 8% Average Market Return 6% 8% 10% 12% 15%