It needs to be done on excel
FINA 4305Final Client Case April, 2021 Demographic Information Gary M. Happy, born 6/15/1979, married on May 2, 2006 to Betty Z. Sad, born July 5, 1979. The couple has two children (One, born 2/15/2012; Two, born 2/5/2014. The children go to private school and are in summer camps each year. Clients have no wills drafted. They have no prenuptial agreement. Gary earns $92,000 per year gross plus 10% in an annual bonus, has a 401(k) plan and contributes 8% of his income. The company matches 50% up to 6%. His expected retirement age is 70 and life expectancy is 95. Betty earns $45,000 per year as an office administrator. She has a retirement plan and contributes 8% each year. The company matches 50% up to 10%. Her expected retirement age is 70 and life expectancy is 95. Gary and Betty are in the 12% income tax bracket. Other Assumptions: Investment earnings 6% (stock mutual fund, 401k investments) IRA returns will be 3% per year, inflation will be 2.5% per year, social security inflation is one half the regular inflation rate we are using, college inflation will be 6% per year. Assets and liabilities: Client estimates the current value of the house is $275,000. It was purchased on 9/29/2007 for $305,000 with a 30-year, 6.5% mortgage of $205,000 and $50,000 down payment. The homeowner’s association month fee is $280.00. Gary has one credit card carrying $12,800 balance at 17% and he pays $250 a month. Betty has one 10% student loan of $37,500. She made her first payment on June 15, 2012 and will pay for 25 years. They have two cars and only one auto loan – 1) a 2020 Ford Edge SUV, excellent condition, 9,756 miles, blue, purchased for $22,000, and 2) a 2015 BMW 325i, excellent condition, 88,435 miles, black, purchased for $56,500 and financed $45,000 at 1.75% for seven years starting on 9/1/15. The annual cost for insurance is $2,750 (1,250 Edge, $1,500 BMW). The deductible is $500 and the coverage is 100/300/50 for each car. There is a $48,000 balance in Gary’s 401k plan and $15,000 in Betty’s. Their joint savings account has a balance of $25k. Gary has $25k in an IRA and Betty has $27k in her IRA. They own a large cap stock mutual fund worth $6,000, jewelry worth $12,000, and household appliances worth $11,000. Expenses Food $985, dining out $325, gasoline for cars $275, school and summer camp $798, $8,400 a year on vacations, utilities $420, cell phones $120, cable and internet $160, life insurance $125 (a $375k policy on Gary which pays $75, and a $250k policy on Betty which pays $50), homeowner’s insurance $85, personal care $290, medical expenses $25, savings $350 in addition to 401k contributions, donations $250, other stuff $375, entertainment 375. Client Goals: 1. Evaluate monthly and year expenses, they do not feel like there is enough income. 2. Provide for college for each of their children. The estimate costs will be about $28,000 per year for four years at a four-year degree at a state university (being from out of state). 3. Retire with similar cost of living at age 70. 4. Provide for each other at the death of the spouse. 5. Reduce cost of debt. Financial Plan You are to prepare a financial plan for this client that includes, but is not limited to, the following client reports 1. Net worth statement (you will need to create amortization tables) 2. Cash Flow statement 3. Capital needs analysis for each spouse 4. Capital needs analysis for retirement capital needs to retire at age 70 (both) 5. Capital needs analysis for college costs. The children will attend state colleges costing $28k per year now and the costs increase by 6% per year 6. Estate distribution report 7. Provide at least three (3) financial planning recommendations for the family in each of the sections of the financial plan and at least two (2) positive observations in each area 8. Calculate the following ratios: a. Debt Ratio b. Current Ratio c. Liquidity ratio d. Debt payments ratio e. Savings ratio You will turn in two documents – one the spreadsheet with all the calculations and the other a word document with tables (summaries) copied from the spreadsheet. The word document is what you are presenting to the client – their financial situation, the goals they have indicated, ability to meet their goals, modifications to current spending or investing habits, recommendations that provide the balance between what they can achieve financially and what the need to modify. The beginning of this document will include a summary of the key items that the Board of Certified Financial Planners requires in a financial planning engagement service agreement with clients.