Reference no: EM133327493

Question: Prepare these financial statements using Excel spreadsheets. In a single Excel file, create 1) a data worksheet, 2) one worksheet for the Cash Flow Statement and 3) another worksheet for the 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.

Client Data-John and Laura Rockefeller

You recently met with your new clients, Laura and John Rockefeller. It was your first formal meeting; however, they requested that you combine the initial meeting with a data gathering session. The Rockefellers 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 daughter. 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 John is 30 years old, Laura is 29 years old and their daughter, Bessie, is 6 years old. John earns $135,000 per year as a manager at an oil exploration company, while Laura is an administrative assistant to the President at a local college and earns $72,500 annually.

They each contribute to their pre-tax retirement accounts. John's is a 401(k) plan with a dollar-for-dollar match on the first 6% of income contributed. Laura's plan is a 403(b) with a $0.50 on the $1.00 match on the first 9% contributed. They are both currently contributing 5%

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 5.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 16.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.25%. John, Laura and Bessie are all covered under Laura's health insurance plan from work because her plan is superior to John's employer plan. Her monthly payroll deduction for health insurance is $472.

By way of assets, the Rockefellers have a joint checking account with a balance of $6,585, their respective 401(k) (John) and 403(b) (Laura) balances are $82,335 and $41,110. Neither owns stocks outside of their retirement accounts. Laura has a "rainy day" savings account with $6,230 in it, while John has a money market account with a balance of $9,008. Six months ago, Laura's favorite aunt passed away and Laura received a life insurance settlement of $100,000. Laura put this money in a money market mutual fund as she and John would like your help in deciding how to best use it to meet their goals. The current balance is $100,125. John has a baseball card collection inherited from his father valued at $8,000. Laura owns a toy train collection she inherited from her father which cost a total of $13,250 and was recently appraised for property insurance purposes at $4,950, but which she believes would only fetch $4,000 if she had to sell it today. John also owns a life insurance policy on his life with a face value of $200,000, a current cash value of $8,100, and an annual premium of $2,925. He also has term-life insurance with a face value of 2.0 times his salary that his employer pays for.

John and Laura own their home in Akron, Ohio, currently assessed for property taxes at $312,000, and aggregate property taxes run 2.25% of taxable value annually. Their original mortgage, taken out five years ago, was for $232,000 financed for 30 years at 3.75%. Their monthly mortgage payment for principle and interest is $__$4,246.51__, and after making 59 monthly payments their current mortgage balance is $___ $4,233.28___. The annual cost of their homeowner's insurance is $2,020. In their home, they estimated that their home furnishings are currently worth $80,000, including a new living room set that cost $4,450 they purchased with funds from John's money market account in 2015.

Laura drives a 2015 BMW with a blue book value of $19,000 (fully paid for), while John just bought a 2019 BMW M5 SUV last Fall (September 2019) that cost him $46,500. He was miserable when he checked his new SUV's blue book value and saw that the value had depreciated to $39,900 in the short time he has had the vehicle. His original loan was for loan balance of $38,000, financed for 5 years at 3.75%. His monthly payment is $__670.63__ and the loan balance is $___34,746.12__. John also owns a 2009 BMW motorcycle, which he thinks is worth $7,000. He bought it about two years ago for $9,900, financed it for four years at 5.99%. He still owes $___2,830.03___ on the bike after 24 payments, and his payments are $_119.59_____ month. John's combined vehicle insurance for both his car and motorcycle average $275/month, while Laura's auto coverage is $205 monthly.

The Rockefellers typically spend $1,150 a month for food (65% for groceries/35% eating out), $750 a month for childcare, $600 each month on entertainment, and $5,000 each year for clothing. Auto maintenance typically costs them about $2,500 a year, and they average $340/month in gas. Total utilities (electricity, gas, water, and cable) average $680 per month. Out-of-pocket medical expenses are $1,500 annually. They spend about $2,500 on gifts annually, and $3,600 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 $21,000, and they regularly pay $1,200 each month in credit card bills. All new credit card purchases are now paid off upon billing. Twelve months ago, they started a college fund for Bessie, and contribute $400 each month in the Vanguard S&P 500 Index ETF; the account balance is now $5,575. John has an outstanding student loan with 4.55% interest rate that will be paid off Dec 31, 2020. Monthly payments are $257.