Question 1 (30 Points) Recent market performance has forced many to review their retirement strategy. Your professor would like your help in building a retirement model. Please develop a spreadsheet model that analyzes both pre and post-retirement. Your model should include the following inputs: Age=42 Current retirement Savings=$250,000 Rate of return on retirement savings (pre retirement)=7.27% Current annual salary=$140,000 Annual salary increase=2% Percent of salary contributed to retirement savings=5% Percent school contribution to retirement savings=9.4% (of salary) Expected age of retirement=67 Expected annual expenses =$90,000 (2022 valuation) Income tax rate after retirement=15% Inflation rate 2% Given these inputs, develop a spreadsheet model in two parts. First, model the pre-retirement savings (to be robust, build out over a period of 75 years). Second, model the post-retirement retirement savings left (again, build out over a period of 75 years from start of retirement). Please build the model on the worksheet Question 1 Model. Be sure to build the model so that a sensitivity analysis can be run on all model inputs. First, you should develop the pre-retirement savings model. Given the current age of 42, develop a model that calculates the retirement fund over the next 75 years. This should include the current fund balance and the year over year growth due to contributions and expected returns. 1 (3 Points): At age 67, what is the annual salary? Annual salary at 67 2 (3 Points): At age 67, what are the total annual contributions (both personal and university contributions)? Annual contributions at 67 3 (3 Points): At age 67, what is the value of the fund at the end of the year? EOY retirement fund value at 67