Thank you for the help!

Utilizing the financial planning process, you evaluate two new clients who are in need of a financial planner. James and Tracy Bond want to meet with you to discuss their finances and their goals for themselves as well as their family. In your first meeting, you start the financial planning process with the first step by understanding their personal and financial circumstances.

Tracy is 64 years old, and this is her first marriage. James and Tracy recently celebrated their 16th wedding anniversary. Two years after they were married James and Tracy adopted two daughters who are now in their freshman and junior years of high school. Jackie (14) and Katie (16).

James is 66 years old and this is his second marriage. His first wife was killed in an accident 20 years ago, and he has two daughters from that marriage. Mary (age 25) lives with her long[1]time boyfriend, Anderson, and Jill (age 29) is married to Larry. Jill and Larry have a daughter, Marilyn (age 5) and a son, Joel (age 3).

James and Tracy live in CO where James works as a History professor. He really enjoys his work and is not certain when he wants to retireperhaps 5 to 10 years from now. After juggling work and the children for many years, Tracy retired 3 years ago from her position at a real estate firm. She is very happy to have more time with their daughters and with their grandchildren.

James has a base salary of $135,000 per year from his position as a tenured professor. In addition, he has held a chaired professorship for the last decade that increases his annual gross income by $45,000, and he plans on holding that professorship until he is age 70. James is covered by a 403(b) retirement plan that has an annual required employee contribution of 6% of salary. The State also contributes 7% of Jamess salary per year to the 403(b). The contributions are tax-deferred for federal income tax purposes, but the employee contributions are taxable for Social Security (FICA) tax purposes. He currently has a balance of $1,252,331 in this 403(b) account, which is invested in a diversified portfolio.

In addition to payroll withholding for his required retirement plan, James has the following deductions from his paycheck each month:

Federal income tax$1,950 FICA: Social Security6.2% up to $137,700 ($141,900 in 2021); Medicare1.45% on all earnings Health insurance coverage (monthly) for Tracy, Jackie, Katie, and himself$715 Disability insurance coverage (monthly) $225 Term life insurance coverage (monthly) $132

When Tracy retired, she rolled her company 401(k) plan into an IRA that has a current balance of $2,314,626. She began taking Social Security benefits when she turned 64 in January. Her monthly benefit from Social Security is $2,200, and her annual federal income tax on benefits is estimated to be $1,320.

The following are the balances in the other accounts held by James and Tracy:

Joint taxable investment account--$67,000 Joint emergency fund (money market account)--$44,222 Tracys money market account (an inheritance from Tracys mother)--$150,000 Joint checking account--$20,000

Three months ago, Tracys uncle passed away and Tracy received a life insurance payment of $200,000. That windfall (plus the cash flow from Tracys Social Security benefits) are what prompted James and Tracy to call you for financial guidance. Tracy wants to use the $200,000 life insurance benefit to pay for Jackie and Katies college education, but both she and James feel they need help deciding how to pay for their daughters education while continuing to pursue their other financial goals. Tracy has parked this money in a separate money market account until the college funding decision has been made.

James has an art collection inherited from his father valued at $12,000. Tracy owns china plates that cost $12,000, appraised for property insurance purposes at a replacement value of $14,750, and could be sold today for $10,000. The only life insurance the family has is a term life policy with a face value of $1,000,000 through the college. Premiums for this policy are paid through payroll withholding (mentioned previously).

They own their home, currently assessed for property taxes at $575, 000, and aggregate property taxes run 1.83% of taxable value annually. Their original mortgage, taken out 10 years ago, was for $440,000 financed for 30 years at 3.25%. Their monthly mortgage payment for principal and interest is $_____, and their current mortgage balance after making 120 payments on time is $______. The annual cost of their homeowners insurance is $3,500. They estimate that their home furnishings and other personal belongings have a current market value of ~$125,000.

James drives a 2016 Chevy Silverado pickup truck with a blue book value of $32,000 (fully paid for), while Tracy just bought a new 2016 Maserati Ghibli last month that cost $41,500. She was miserable when she at the urging of her husband checked her new cars blue book value and saw that her cars value had depreciated by $5,000. Her auto loan is for $35,500, financed for 4 years at 2.69% through her credit union. Her monthly payment is $______, and they will make her first payment on January 1. James also owns a 2017 500CC Honda Rebel motorcycle, which he thinks is worth $5,500. He bought it two years ago for $7,690, financed it for four years at 2.8%. He still owes $3,952 on the bike, and his payments are $169.50/month. Vehicle insurance for both cars is $2,200 annually, and motorcycle coverage costs $850 annually. In addition they have a $2 million umbrella liability policy that costs $525 annually. They anticipate their auto insurance bill will skyrocket once Katie gets her drivers license.

Using Excel spreadsheets:

1a)Create a data worksheet

1b) Create a worksheet for the Cash Flow Statement

1c) Create a worksheet for the Balance Sheet

1d) Create a worksheet for ratio calculations

1e) a worksheet for additional calculations required for the case