Roger and Wanda Person are average folks who want to live ?the good life.? They meet at CMC during college and later decide to get married, raise a family, buy a house, and save for their son?s college and their retirement. What will it take for them to manage to pay their expenses, plan ahead for unexpected events, and save enough money to enjoy their retirement years? This case study helps you think about financial planning over a long period of time.

continues in the attachment

Roger & Wanda Anybody: BASE CASE FINANCIAL PROJECTION Life Events and Financial Conditions SALARY & OTHER INCOME REDUCTIONS FROM INCOME Year Age Events Roger Wanda Wedding Mortgage Turner Other Year's Savings Savings Balance and Calculations Beginning of Year Retirement Savings TOTAL Bank Investments Bank Interest Invest Earnings Year Savings 1% 6% (Col. J) End of Year Retirement Savings TOTAL Bank Investments Year 2021 Begin Principal 175000.00 Interest Amortization 619.79 -619.79 End Principal 174380.21 4.25% APR Monthly payment 0.00 per year 0.00 Year 1 payments FIN 325 Case Study #1 Life Cycle (Time Value of Money) Analysis: The Persons Due Date: Friday, March 18, 2016 Roger and Wanda Person are average folks who want to live \"the good life.\" They meet at CMC during college and later decide to get married, raise a family, buy a house, and save for their son's college and their retirement. What will it take for them to manage to pay their expenses, plan ahead for unexpected events, and save enough money to enjoy their retirement years? This case study helps you think about financial planning over a long period of time. For this case, assume today is January 1, 2025. The Persons have provided you the following history (and assumptions they have made about their future life.) Assume all dollars are/were received or paid at year end and IGNORE taxes and inflation! Roger tells you this history: 2015 2017 2021 2022 2024 At age 22 we met and graduated from CMC with BSBA degrees. We each started work and earned $39,000 salary. We each paid our own expenses and started out saving 10% of our gross incomes each year. (Ignore expenses for now.) Wanda got a great job performance review. She received a $6,000 salary raise Jan. 1. On Jan. 1, we kept $25,000 in our bank savings and opened up an investment account with the rest. Then, we got married. We still put aside 10% for savings into the investment account at that point going forward, and got $35,000 cash as gifts. We bought a $225,000 house and made a $50,000 down payment out of our bank savings and wedding cash and signed a $175,000 30-year mortgage at 4.25% and started paying monthly in January. But those payments cut $250 out of our planned investment savings every month for the life of the mortgage. Wanda got another $5,000 salary increase! Our son, Turner was born. Wanda decided to take a year off so she did not receive any salary: No Savings! Expenses for Turner of $4,000 per year also reduced our investment savings plan by that amount, until he goes to college. But Wanda returned to work 1/1/25. Roger then told you the following assumptions he has about their future finances: 2027 2029 2035 2042 2046 2048 2049 2059 Wanda continues to get a $5,000 salary increase this year and every 5 years until 2042. Roger finally lands a much better job, increasing his annual salary to $75,000. But: Roger's has some health problems which leaves him unable to work for a while. This reduces his income by $30,000 for the year (still save 10%). Turner is off to college. It costs us $10,000 a year to send him to CMC for four years, from investment savings. Free at last! To celebrate our 25th wedding anniversary and Turner being on his own, we take a cruise around the world (cost: $70,000 from investment savings) and each take three months off from work without pay. Rough year! Wanda quits her job to work for a charity. Her salary is reduced to $35,000. Roger loses his job too, and ends up self-employed earning $40,000 per year. After paying the mortgage for December we sell the house for $300,000. We pay off the remaining mortgage principal and buy a smaller townhouse for $200,000 cash. We both retire on December 31. Our Social Security pays some bills and health care is paid for by our company retirement plan. But for all the rest (entertainment, travel, etc.), we expect to draw an extra $80,000 per year from savings from then on. Roger asks you to prepare a financial plan to help him and Wanda understand their finances as they plan ahead for retirement. Will they have enough saved? How long will it last?? ASSIGNMENT Using the MS Excel template provided, and your understanding of time value of money calculations, analyze all of the cash flows listed on the prior page. Answer the following questions IN DETAIL, REFERENCING YOUR WORK. When you are done, write a multi-page report to Wanda and Roger as if your team is a personal financial planning group. Use the spreadsheet template to capture the Persons financial information. How much money have they saved as of today (1/1/2025)? Check your calculations carefully. Then, analyze the remaining assumptions Roger provided. Your goal is to calculate the Persons income and savings for the remainder of their lives, from 2025 to retirement and beyond. SAVINGS: Assume the Persons will place their initial savings from 2015, into a bank account which pays 1% interest on the year-end balance starting in 2016. But, remember beginning in January 1, 2021, the Persons want to invest their savings in a mutual fund investment (we will learn about this later in the course). Assume their investment pays a 6% annual return each year, calculated on the year-end balance the prior year. Don't forget! If the Persons must draw down their investment savings due to the events Roger expects, you must build this into your calculations! QUESTIONS What do you think of the Persons' life savings plan? Do you think they will be able to enjoy a good retirement? At what age will they run out of money? Remember, as of now the average American's life expectancy is about 78 years. Run some other sets of calculations to show the Persons what happens if: o Their annual mutual fund returns are 1% higher and lower (that is, 7% or 5%) o Starting in 2025 they are only able to save 7% of their income (before mortgage and other expenses) instead of the 10% they have been saving. Explain to the Persons what your findings are. Discuss how savings are impacted by decisions made early in a person's life. What recommendations would you make to them to ensure their savings go far enough? o Assume Roger could get an advanced degree from CU, then get a high paying job beginning in 2025 which he could retain until retirement. How much salary would he have to earn each year starting in 2025, so that the Persons would be \"millionaires\" ($1,000,000 or more of savings) when they retire at age 67? Do you think this is realistic? Add an appendix to your report. In the appendix, discuss what YOU as real persons learned from this exercise which you can apply in your lives. Get creative. Graphs? Tables? Remember, you are reporting to a CLIENT. You need to explain finances: they are not experts!! You also need to tell them the key numbers in all of this work - they are paying you an advisory fee to tell them the key things they need to know. RECOMMENDED WORK PLAN: Appoint one team member to create the calculations in Excel for your case study. Appoint another team member to check all of the calculations. Do the base case and agree on it FIRST. Make copies of the main spreadsheet before you calculate all sensitivities. Appoint another team member to run all of the sensitivities and collect data on the results. Work together as a team to answer the questions. Appoint someone to create your write-up. Attach ALL of your Excel analysis to your group's presentation. Use this checklist to make sure everything is covered. GROUP CASE #1 CHECKLIST Done 2015 Wanda and Roger initial salary $39,000 2015 Wanda and Roger annual savings 10% 2016 Formulas for interest earned and new total savings at end of 2016 2017 Wanda salary change (and change to savings) 2021 Formulas for investment savings and keeping only $25000 in the bank 2021 Wedding gift 2021 Down payment on house 2021 Calculate mortgage pay down schedule (see separate tab in Excel worksheet) 2021 Mortgage impact on annual amount saved 2022 Wanda salary increase; then every 5 years until 2042 2024 Turner expenses impact on savings Beginning of 2025: How much total saved at this point 2029 Roger salary increase 2035 Roger health problems 2042-2045 Savings reduction to pay for Turner's college years 2046 Cost of cruise 2046 Roger and Wanda salary reduction for the year 2048 Roger and Wanda salary change until they retire 2049 Sale of house, pay down of remaining mortgage principal, pay for new house 2060 No more income; begin drawing savings each year from here on Calculate, and determine what year the savings runs out, in the initial case study Recalculate savings if investment return is 7% per year and when savings runs out - use separate spreadsheet Recalculate savings if investment return is 5% per year and when savings runs out - use separate spreadsheet Recalculate savings if Persons can only save 7% of salaries each year (BEFORE any other expenses) Recalculate Roger's salary at 2025 and forward, to ensure there is $1,000,000 in total, saved at 2060 (retirement date) Develop write-up answering all questions, one section for each set of questions Create attachments to help Persons understand their report Complete appendix describing what each group member learned from doing the case REQUIRED: Complete and submit a Case 1 Peer Review report (each team member)