How much will he have to live on when he retires?

Is his current rate of savings adequate to meet his goal of retiring at 65 years of age AND last

him and Madelyn until they reach their early to mid-90's?

If not, how much should he be setting aside each year?

How long after retirement will he be able to live comfortably? What are the risks he faces, and

how should his retirement planning take these risks into account?

Modeling Begin to translate the given scenario [separate download] into a model: 1. Ust the key inpuls (you must determine which yalua are important] that can be extracted from the provided informatio n 2. Create an inuence chart 3. State any assumptions that you needed to make Spreadsheet Engineering Create a decision support system spreadsheet to help calculate the length that the money will last in retirement. While constructing the spreadsheet ensure that you follow the guidelines isolate inputs and calculations, organize data clearly, and make the results clear. Your sheet can be structured as you please as long as it is clear and effective the data and calculations may dictate that you construct your calculations following a different format than the example. Note: There is ambiguity here, that is part of the assignment. Determine, as accurately as you can. the most likely scenario (i.e. the base case} for how long the money will last. You will have incomplete information and you will have superfluous information; sorting through the information. evaluating importance, and filling in gaps is part of the challenge. Scenarios Use the scenario manager to create two additional scenarios that can be compared to the baseline that you've already created: 1. Increase postretirement spending by 7.5%. increase retirement savings rate by 10%. 2. Decrease postretirement spending by 10%, decrease earnings by 5%. Greg Johnson needs financial advice and he has come to your team for help. Greg would like to retire in 10 years or sooner, if financially possible. However, he is fearful that he may not meet his retirement goals. He has come to you for an analysis of his current financial position and whether he is on track to retire at age 65 or not, and if not, what options would your team recommend helping him ensure he meets his goals. Greg is 55 years of age, married with one child. He has been working as a service technician for Xerox for the last 30 years. His wife, Madelyn is 53 and retired at 50. She only worked part time since she married Greg 35 years ago and therefore did not earn a pension. Greg's son, James, is 30 years old and has a family of his own. Greg's 2020 salary from Xerox is $64,550 (per annum). Every year, Greg receives a cost-of-living adjustment (COLA) of 2.8%. In addition, he is eligible for year-end bonuses that range from 5% to 15% of his annual salary, dependent upon the performance of his regional office. Last year, Greg earned a bonus of 10% or approximately $6,280.00. Every year Greg invests $3,000 into an RRSP. He and Madelyn also try to put in most of their net income (gross income less all expenses and taxes deducted) equally between TFSA accounts and RRSPS. Altogether, TFSAS, RRSPs and his LIRA amount to $420,000. These account balances are invested in long- term conservative investment ETFs that are a combination of equity and fixed income securities (40/60 split, respectively). Any new contributions are also allocated in these same proportions. The cumulative average appreciation on these investments is 5% per year. In addition, he earns approximately $3500 per year in dividend and interest income. Also, part of his net worth is a $50,000 rainy-day fund (invested in a short-term money market mutual fund with RBC). In addition to his retirement assets, Greg's net worth consists of his home (purchase price $275,000 in 2002) in which he put $55,000 down and took out a 25-year mortgage in the amount of $220,000. Today, Greg's current mortgage balance is $88,000 and he estimates that the market value of their home is $350,000. This estimated market value less his current mortgage balance represents the equity in their home. His monthly mortgage payments are $1,200.00 and he and Madelyn have no other outstanding debts. They pay off their credit card charges every month and have a secured personal line- of-credit for $25,000 that they have never had to use. He has a term life insurance policy with a value of $500,000 and Madelyn as the beneficiary. His monthly premium for this policy is $135.00. This policy terminates when Greg reaches age 70. Even though it carries no cash value, should Greg die while the policy is in effect, the death benefit is paid to his beneficiary, Madelyn. Greg's goal is to work until he is 65 at the very latest - sooner, if financially viable. He wants to ensure that his assets will not only adequately provide for he and Madelyn in their retirement, but also allow them to travel the world. They are both in good health and believe they will live at least into their early 90's. This too is a concern of Greg's. At retirement, he wonders if his assets will last until he and Madelyn reach their early 90's or will his funds run out before then? Some of Greg's questions for your team are the following:' How much will he have to live on when he retires? ' Is his current rate of savings adequate to meet his goal of retiring at 65 years of age AND last him and Madelyn until they reach their early to mid90's? ' If not, how much should he be setting aside each year? ' How long after retirement will he be able to live comfortably? What are the risks he faces, and how should his retirement planning take these risks into account? Other pieces of information that Greg has provided you for your analysis include the following: Monthly expenses: ' Food & Household items: $550.00 ' Utilities: $200.00 (average) ' Telecom/internet/streamingTV:$250.00 ' Entertainment: $250.00 ' Travel/Vacations: 5 400.00 (averaged monthly) ' Mortgage: $1200.00 ' Insurance (auto, home, & life): $485.00 ' Maintenance & Repairs: $350.00 (home & autos) ' Fuel: $200.00 (2 autos) ' Miscellaneous:$275.00 Total estimated monthly expenses: $4160.00 Estimated tax rate applied: 19%