: Please find the attached for the questions. 1a) John wanted to know what their expenses would be the year they retired (future value), given 12 years to retirement, $123,000 in household expenses in today's dollar terms, and 3% inflation. 1b) What would their expenses be after 30 years in retirement (future value)? 2) What would be the value of the McMurtrey's retirement portfolio in 12 years, given the current $420,000 value, $25,000 in annual savings (no growth in contributions), and a 7% average return on their investments? Retirement distribution research indicates that a portfolio can distribute 5% of the portfolio per year with payments growing with inflation, and have an 80% chance of not being depleted.For example, a retirement portfolio of $1 million at retirement could support a first year distribution of $50,000. 3a) What first year distribution amount is suggested, then, in the first year of retirement, given the result of question 2 above? 3b) What is the distribution amount determined in 3A relative to their expected spending needs in the first year of retirement (from Question 1a)? Express in percentage terms. 3c) Will the McMurtreys have enough saved to enjoy a successful retirement, given their current plan? 4) The McMurtreys have life insurance that will pay a total of $100,000 upon their death, for the benefit of their children. Assuming they die in 42 years (at age 96), what is the equivalent value of this sum in today's terms assuming 3% inflation? 5) The couple also plans to live in their home until their death at age 96, and was planning on leaving the home to both children, jointly. What amount would each child inherit if the house appreciated by 3.5% per year for 42 years? 6) What advice or suggestions do you have for the McMurtrey's in regard to achieving a successful retirement?This can be answered in general terms, without using specific numbers; however, you're welcome to be specific based on working with the spreadsheet model (next).Please specify 5 possible suggestions. 7) Given the "base case" spreadsheet (see adjacent worksheet) established for the McMurtreys, create a scenario that ends the planning period with a positive value. That is, make "reasonable" changes to the inputs (in RED) that will make the ending balance in year 2059 greater than zero. To the right of the model, indicate the changes you made and explanation for each. When completed, save the worksheet. 8) Once 7 above is completed, in the adjacent Forecast model worksheet, create a line graph of the annual portfolio balances (column H) by age (column A). Include an appropriate title, and label both axes. Place the chart adjacent to the completed model in that worksheet in the indicated area. Download Attachments: Minicase 2 Spr 17 McMurtrey Personal Finance.xls