Prepare these financial statements using excel

Assignment Help Financial Accounting
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.

Reference no: EM133327493

Questions Cloud

What documents must a corporation file when incorporating : What documents must a corporation file when incorporating? Who are these documents filed with? Describe the benefits of being a corporation
What is the percentage deviation from the law of one price : Who is right? Can you identify the prices of three comparable consumer goods in at least three different countries? Are their prices the same in U.S. dollars
Determine for december 31, 2020 the amount of earnings : Use this information to determine for December 31, 2020 the amount of Earnings per Share (rounded to the nearest cent)
How do volcanic eruptions in?uence global climate : How do volcanic eruptions in?uence global climate ? Can you cite any examples? What types of phenomena commonly precede volcanic eruptions and can be used
Prepare these financial statements using excel : 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
What do you need to caution chris about : At this rate, if I keep investing the same amount every year until I retire, I won't have any financial worries." What do you need to caution Chris about
How much will you deposit from january 1 : Your bank capitalizes interest at an annual rate of 8%. How much will you deposit from January 1, 2014 to 2017 to accumulate the 1,000.00 in January 2017
Find the principal to the nearest dollar : reinforce the fence around the entire yard. The simple interest note is for 140 days at 11.5%. Find the principal to the nearest dollar
Determine how many of each ticket should be sold to : At most, the plane has a capacity of 150 travelers. Explain how you can help this company to determine how many of each ticket should be sold to

Reviews

Write a Review

Financial Accounting Questions & Answers

  Financial statement analysis and preparation

Financial Statement Analysis and Preparation

  Shareholder of a company

Describe the ways that a person can become a shareholder of a company. Why Wal-Mart would split its stock?

  Financial and accounting principles

An understanding of financial and accounting principles can be a valuable tool for managers. While not all managers will find themselves calculating financial ratios or preparing annual financial data.

  Prepare a statement of cash flow using the direct method

Prepare a Statement of Cash Flow using the Direct Method and Prepare the Operations section of the Statement of Cash Flow using the Indirect Method.

  Financial accounting assignment

This assignment has one case study and two question apart from case study. Questions related to document Liquidation question and Company financial statements question - Torquay Limited

  Prepare general journal entries for goela

Prepare general journal entries for Goela Ltd

  Principles of financial accounting

Prepare the journal entry to record the acquisition of the assets.

  Prepare general journal entries to record the transactions

Prepare general journal entries to record the transactions, assuming use of the periodic inventory system

  Global reporting initiative

Compare the view espoused by the economist Milton Friedman about the social responsibilities of business with the views express by Stigler.

  Explain the iasb conceptual frameworks

Explain the IASB Conceptual Framework's perspective of users and their decisions.

  Determine the company''s financial statements

T he focus of the report is to determine the extent to which you are comfortable relying on the financial statements as presented by management .

  Computation of free cash flow

Computation of Free Cash Flow

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd