please answer

Overview This assignment covers three topics - modeling, spreadsheet engineering, and scenarios. Modeling Begin to translate the given scenario into a model: 1. Create an influence chart. 2. List the key (you must determine which values are important) inputs that can be extracted from the information given. 3. State any assumptions that you needed to make. These steps will make designing your spreadsheet model far easier. It is highly recommended that you approach these design steps early. These are not explicitly graded, do not submit them; however, they are a key building block of your DSS. . . . . Evaluation Your model will be evaluated by peers, who will try to use it to evaluate realistic scenarios as though they are the client whose retirement is being evaluated. Criteria Evaluating groups will use the model that you create, and the sheet will be graded on the following criteria. A full description is in the Moodle assessment sheet (it is recommended that you read that prior to starting). Clarity and usability: Is the spreadsheet model well designed, clear, and easy to use? Accuracy: Do the calculated outputs of the sheet make sense? Scenarios: Does the sheet allow for the convenient comparison of different scenarios. Ambition: Subjective judgement on how comprehensive and complete the model is. Did the model attempt to capture the 'entire picture'. Scenarios When evaluating, try at least 4 alternate scenarios: 1. Increase post-retirement spending by 7.5%, increase retirement savings rate by 10%. 2. Decrease post-retirement spending by 10%, decrease investment income rate by one third. 3. Choose two other scenarios. You can change savings, investments, spending, etc... Choose two other reasonable sets of inputs, and run them as scenarios. Any inputs are fair game. A well designed model should deal with any combination of reasonable set of inputs. Deliverables The deliverables for this assignment are: A spreadsheet containing your DSS. Your team's evaluations of the other assigned sheets. . . Retirement Planning Background Information Greg Johnson needs financial advice, and he has come to your team for help. Greg would like to retire in 10, 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 $91,800 (per annum). Every year, Greg receives a cost-of-living adjustment (COLA) of 3%. In addition, he is eligible for year-end bonuses that typically range from 5% to 15% of his annual salary, dependent upon the performance of his regional office. Last year was rough due to COVID and Greg only earned a bonus of $6,000.00. Greg doesn't cheat on his taxes, and he lives in Alberta, so he is subject to whatever tax rate applies to his income in a given year. Every year Greg invests $3,000 into an RRSP through automatic withdrawals. 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 over the last 10 years has been approximately 6% 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. He 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. 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. Greg's plan for expenditures in retirement isn't written in stone, but he wishes to both live and travel reasonably comfortably, but not necessarily in luxury- unless he has the excess money to live it up. They are both in good health and believe they will live for a nice long retirement. At retirement, he wonders how long can he expect his money to last? . . Greg has also tallied up his expenses over the past year. This isn't a comprehensive list of every dollar he spends, but he's described it as representative of his current monthly expenses. He's only focused on the major items here, for example, he hasn't really tracked things like entertainment very closely. Food & Household items: $550.00 Utilities: $200.00 (average) Telecom/internet/streaming TV: $250.00 Entertainment: $250.00 Mortgage: $1200.00 Insurance (auto, home, & life): $485.00 Maintenance & Repairs: $350.00 (home & autos) Fuel: $200.00 (2 autos) . . . . Spreadsheet Engineering Create a decision support system spreadsheet to help calculate the length of time 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 examples from class. Your DSS should be realistic in its scope - planning for retirement is a very common activity and your DSS system should consider the important factors - both those listed explicitly in the background data, and those implied by the situation. Note: There is ambiguity here, that is part of the assignment, and a substantial skill in designing these types of decision support systems. 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. Overview This assignment covers three topics - modeling, spreadsheet engineering, and scenarios. Modeling Begin to translate the given scenario into a model: 1. Create an influence chart. 2. List the key (you must determine which values are important) inputs that can be extracted from the information given. 3. State any assumptions that you needed to make. These steps will make designing your spreadsheet model far easier. It is highly recommended that you approach these design steps early. These are not explicitly graded, do not submit them; however, they are a key building block of your DSS. . . . . Evaluation Your model will be evaluated by peers, who will try to use it to evaluate realistic scenarios as though they are the client whose retirement is being evaluated. Criteria Evaluating groups will use the model that you create, and the sheet will be graded on the following criteria. A full description is in the Moodle assessment sheet (it is recommended that you read that prior to starting). Clarity and usability: Is the spreadsheet model well designed, clear, and easy to use? Accuracy: Do the calculated outputs of the sheet make sense? Scenarios: Does the sheet allow for the convenient comparison of different scenarios. Ambition: Subjective judgement on how comprehensive and complete the model is. Did the model attempt to capture the 'entire picture'. Scenarios When evaluating, try at least 4 alternate scenarios: 1. Increase post-retirement spending by 7.5%, increase retirement savings rate by 10%. 2. Decrease post-retirement spending by 10%, decrease investment income rate by one third. 3. Choose two other scenarios. You can change savings, investments, spending, etc... Choose two other reasonable sets of inputs, and run them as scenarios. Any inputs are fair game. A well designed model should deal with any combination of reasonable set of inputs. Deliverables The deliverables for this assignment are: A spreadsheet containing your DSS. Your team's evaluations of the other assigned sheets. . . Retirement Planning Background Information Greg Johnson needs financial advice, and he has come to your team for help. Greg would like to retire in 10, 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 $91,800 (per annum). Every year, Greg receives a cost-of-living adjustment (COLA) of 3%. In addition, he is eligible for year-end bonuses that typically range from 5% to 15% of his annual salary, dependent upon the performance of his regional office. Last year was rough due to COVID and Greg only earned a bonus of $6,000.00. Greg doesn't cheat on his taxes, and he lives in Alberta, so he is subject to whatever tax rate applies to his income in a given year. Every year Greg invests $3,000 into an RRSP through automatic withdrawals. 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 over the last 10 years has been approximately 6% 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. He 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. 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. Greg's plan for expenditures in retirement isn't written in stone, but he wishes to both live and travel reasonably comfortably, but not necessarily in luxury- unless he has the excess money to live it up. They are both in good health and believe they will live for a nice long retirement. At retirement, he wonders how long can he expect his money to last? . . Greg has also tallied up his expenses over the past year. This isn't a comprehensive list of every dollar he spends, but he's described it as representative of his current monthly expenses. He's only focused on the major items here, for example, he hasn't really tracked things like entertainment very closely. Food & Household items: $550.00 Utilities: $200.00 (average) Telecom/internet/streaming TV: $250.00 Entertainment: $250.00 Mortgage: $1200.00 Insurance (auto, home, & life): $485.00 Maintenance & Repairs: $350.00 (home & autos) Fuel: $200.00 (2 autos) . . . . Spreadsheet Engineering Create a decision support system spreadsheet to help calculate the length of time 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 examples from class. Your DSS should be realistic in its scope - planning for retirement is a very common activity and your DSS system should consider the important factors - both those listed explicitly in the background data, and those implied by the situation. Note: There is ambiguity here, that is part of the assignment, and a substantial skill in designing these types of decision support systems. 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