Jen and Luke sample are married. Jen (age 59) works as a college professor and makes $120,000 a year. She also has a lucrative consulting business that brings in roughly $88,000 a year and is worth $363,055. Luke (age 61) works as a software engineer and makes $125,000 a year. Currently, they have two kids, Jimmy (age 21) and Jessica (age 18). Jimmy is in his Junior year of college, which costs about $25,000 a year with his scholarships, paid for by Jen and Luke. Jessica just started her freshman year. Due to a difference in her scholarships, Luke and Jen pay about $50,000 a year for her education. In October of this year, Luke and Jen will have completed the process of adopting a new baby. They also have a poodle named Darwin who costs about $700 a year to feed and entertain. Luke and Jen own two houses. Their primary residence is valued around $777,975. They just refinanced for $200,000 mortgage at a 5% interest rate to be paid off over the next 30 years. They also have a vacation home in Maine, which is fully paid off and is worth about $363,055. Each year they spend around $4,800 to maintain their two homes. They also spend around $12,000 in property taxes. They spend around $6,000 extra a year to travel to and from their vacation home in Maine. With all of that driving they have an annual transportation expense of around $7,200. The cost of upkeep for both houses is around $2,500 a year between lawn maintenance and their high-tech security system. While relaxing in both homes they enjoy watching their premium cable package which costs about $3,600 a year, but also includes phone. All other utilities cost $8,200 for them. When they’re not at their vacation home, they enjoy going to the local country club which costs them $9,000 a year in club dues. They have provided you with their multiple investment and retirement account statements, which has information about their asset holdings. Also, take note that Jen informed you during your meeting that the annuity accounts are in her name and not Luke’s. Each year they continue to max out Luke’s 401(k) account which has a 3% match. Each month they also save $1,000, which they put into their cash accounts. They have a life insurance policy with a cash value of $46,679. They also pay $4,000 a year in insurance premiums which they have through Luke’s work. In addition to their premiums, they pay $1,200 out of pocket, and an additional $15,000 for other medical/health insurance. Luke and Jen have several credit cards. Their Visa has an outstanding balance of $7,000 at a 7% interest rate, and they choose to pay $93 towards that balance each month. Their Discover has a balance of $19,000 at a 5% interest rate for which they pay $235 a month. Lastly, they have an AMEX card with a balance of $11,000 at a 23% interest rate, they pay $249 a month. Below are their remaining annual expenses: • • • • • • Groceries = $14,000 Clothing = $5,000 Personal Grooming = $3,000 Personal Gifts = $1,500 Charitable Gifts = $2,400 Taxes = $105,702 Use the documents and information provided to create a balance sheet and cash flow statement for Luke and Jen. No excel just attach screenshot to the word Q1: College Funding Analysis (10 points) Is it practical to suggest to a Luke and Jen that your computer model will precisely compute the savings needed to fund their estimated education goal? Provide an opinion to support your answer. How does this affect the recommendation you will make to them about their annual savings need? One page at least Q2: College Funding Analysis (20 points) Assume that Luke and Jen save $9,000 per year for Diane’s college costs. They contribute $4,500 to a taxable brokerage account and $4,500 per year to a 529 college savings plan. Assume they spend 100% of the funds over Diane’s 4 years in college, distributing an equal amount from each account per year. Which of the vehicles provided the greatest contribution toward her expenses, after taxes? Show your analysis. Put tax effect of both and determine which one is better either contribute $4,500 to a taxable brokerage account or $4,500 per year to a 529 college savings plan 1.5-2 pages Q3: Bring It All Together (30 points) Refer to financial plan document 2scope Using your final spreadsheet for Luke and Jen and the format discussed in class, prepare a financial plan document to provide recommendations for repayment of their credit cards and establish a college funding plan for Diane. (use the template sample in attach to write the answer also the excel file ) Use the template for number of pages Luke and Jen Sample FINANCIAL PLAN December 7, 2018 PREPARED BY: Your Name Table of Contents Table of Contents................................................................................................................................ 2 Goals, Recommendations, Action Items ............................................................................................. 3 Goals and Objectives ............................................................................................................... 4 Summary of Analysis and Recommendations .......................................................................... 5 Implementation Schedule and Action Items ............................................................................. 6 Personal Financial Statements ........................................................................................................... 7 Debt Repayment Plan ......................................................................................................................... 8 Education ............................................................................................................................................ 9 F9 to edit Goals, Recommendations, Action Items Goals objective 2 goals repayment credit and education fund for D (2 years old) Summary analysis for each goals and recommenditation (open) Action : What the action must be done When should be Who is invoved in it Goals and Objectives Prepared for Luke and Jen Sample The following table lists all of the custom objectives you have identified, sorted in order of decreasing priority. Summary of Analysis and Recommendations Prepared for Luke and Jen Sample Analysis very semple and clear to clints for each goals Refeer the spredsheet How we come up to the conculosion Remember Daina 2 years old now We assume he will graduate in …. All assumption must state here Implementation Schedule and Action Items Prepared for Luke and Jen Sample What when who Personal Financial Statements Cash flow ( period of time) Statement ( Date) Debt Repayment Plan How ? What do you like them to do? Education
Purchase answer to see full attachment