Q=Prepare these financial statements using Excel spreadsheets. In a single Excel file, a data worksheet, ( Cash Flow Statement and Balance Sheet). Set up your data, assumptions, and calculations in Excel so that if you have to change a number in the data/assumptions, everything in your financial statements will automatically recalculate

You recently met with your new clients, Jane and James Buffett. It was your first formal meeting; however, they requested that you combine the initial meeting with a data gathering session. The Buffetts were referred to you by a long-time client, and they are interested in a fairly comprehensive review of their finances to include a financial checkup; a review of their cash management including saving, credit management, and housing; and college education planning for their children. They would like you to start the engagement with an analysis of their current financial situation so that they can move forward to accomplish their goals. You learned that James is 34 years old, Jane is 36 years old and their children are Savannah (age 10), Sarah (age 8), and Cameron (age 2). James earns $102,000 per year as a sales manager at a petroleum products company, while Jane is VP of human resources at a local college and earns $121,500 annually. They each contribute to their pre-tax retirement accounts. Jamess is a 401(k) plan with a $0.75- per-dollar match on the first 6% of income contributed. Janes plan is a 403(b) with a $0.50 on the $1.00 match on the first 9% contributed. They are both currently contributing 6% of their salaries to their retirement plan. Both are fully vested in these plans, and they have had good success with the returns from their mutual funds in their tax-deferred accounts with an average annual rate of return of about 7.5%. In addition to retirement contributions, other payroll deductions include Social Security (FICA) plus federal, state, and local income tax. Their federal income tax withholding is 18.5% of their gross salaries, and this has historically been very close to equaling their tax liability. State taxes are 4.1% and local taxes are 2.5%. James, Jane and the kids are all covered under Janes health insurance plan from work because her plan is superior to Jamess employer plan. Her monthly payroll deduction for health insurance is $624. By way of assets, the Buffetts have a joint checking account with a balance of $5,885, their respective 401(k) (James) and 403(b) (Jane) balances are $99,335 and $141,110. Neither owns stocks outside of their retirement accounts. Jane has a rainy day savings account with $8,180 in it, while James has a money market account with a balance of $8,008. Six months ago, Janes 2 grandmother passed away and Jane received a life insurance settlement of $75,000. Jane put this money in a money market mutual fund as she and James would like your help in deciding how to best use it to meet their goals. The current balance is $75,100. James has a stamp collection inherited from his father valued at $12,000. Jane owns a gun collection she inherited from her father which cost a total of $15,00 and was recently appraised for property insurance purposes at $6,750, but which she believes would only fetch $5,500 if she had to sell it today. James also owns a life insurance policy on his life with a face value of $250,000, a current cash value of $18,100, and an annual premium of $3,720. They both have term life insurance policies that are paid for by their employers, with James at 1.5 times salary and Jane at 2.0 times salary. James and Jane own their home in Akron, Ohio, currently assessed for property taxes at $335,000, and aggregate property taxes run 2.0% of taxable value annually. Their original mortgage, taken out five years ago, was for $240,000 financed for 30 years at 3.25%. Their monthly mortgage payment for principal and interest is $____, and after making 60 monthly payments their current mortgage balance is $______. The annual cost of their homeowners insurance is $1,890. In their home, they estimated that their home furnishings are currently worth $90,000, including a new living room set that cost $4,200 they purchased with funds from Jamess money market account in 2018. James drives a 2016 Ram pickup truck with a blue book value of $19,000 (fully paid for), while Jane just bought a 2021 BMW M5 SUV last Fall (October 2020) that cost her $47,200. She was miserable when she checked her new SUVs blue book value and saw that the value had depreciated to $41,100 in the short time she has had the vehicle. Her original loan was for a loan balance of $36,000, financed for 5 years at 2.625%. Her monthly payment is $____ and the loan balance (after 4 months) is $______. James also owns a 2013 Tracker boat, which he thinks is worth $13,000. He bought it about two years ago for $16,500 with $4,000 in cash and a $12,500 loan that he financed for four years at 5.49%. He still owes $______ on the boat after 24 payments, and his payments are $______ month. Jamess truck insurance costs $85 per month, Janes auto coverage is $115 per month, and the boat insurance costs $32 per month. The Buffetts typically spend $1,450 per month for food (80% for groceries/20% dining out), $900 per month for childcare, $750 each month on entertainment, and $3,000 each year for clothing. Auto maintenance typically costs them about $3,000 per year, and they average $410 per month in gas. Total utilities (electricity, gas, water, and cable) average $620 per month. Out-of-pocket medical expenses are $2,500 annually. They spend about $2,500 on family gifts annually, and $4,800 each year on charitable contributions. At the moment, they have a couple credit cards with balances they are trying to get paid off. Their aggregate monthly credit card balance is $22,500, and they regularly pay $1,000 each month in credit card bills toward paying off these balances. All new credit card purchases are now paid off upon billing. Twelve months ago, they started a college fund for each child, and contribute $300 to each account monthly in the Vanguard S&P 500 Index ETF; each account balance is now $3,775. James has an outstanding student loan with 4.75% interest rate that will be paid off May 31, 2021. The current balance is $765 and the monthly payments are $257.