Current Liabilities Current Assets and Monetary Assets Balance Excel Sheet Hey !! Is it okay if you can help me with this homework? Everything is on the wo

Current Liabilities Current Assets and Monetary Assets Balance Excel Sheet Hey !! Is it okay if you can help me with this homework? Everything is on the word documentThank You!!!! Financial statements and ratio analysis: questions 1-3 (60 points)
Question 1 (15 points)
Data
John and Mary, both 35 years old, own a house worth $150,000 and have a yearly income of
$50,000, monetary assets of $5,000, two cars worth $20,000, and furniture worth $10,000. The
house has a $100,000 mortgage, they have college loans of $10,000 outstanding, and the cars
have outstanding loans of $10,000 each. Bills totaling $1,150 for this month have not been paid
($1,000 is to pay off their credit card that they use for bills). They are requesting your help.
Calculations
– Using the data above, create a balance sheet to calculate Steve and Mary Jo’s net worth. How
are they doing?
Question 2 (15 points)
Data
John and Mary, who make $50,000 per year, calculated their average tax rate at 15 percent. They
contribute 12 percent of their income to charity and pay themselves 10 percent of their income.
They have a fixed 6%, $100,000 mortgage with 25 years to maturity, a 3 years $20,000 auto-loan
at a fixed 7% rate, and a $10,000, 10 years to maturity and 3% fixed rate college loan. In addition,
utilities and property taxes were $2,270 per year, food was $6,000, insurance was $1,500, and
other expenses were $5,430.
Calculations
– First, find compute their monthly expenditures on their mortgage, auto-loan and college loan.
– Then compute their annual income statement (use Figure 2.6, below). How are they doing?
Question 3 (30 points)
John and Mary would like you to help them understand where they are financially. You have John
and Mary’s balance sheet and income statements, which were prepared earlier. They ask for help
to calculate each of the key liquidity, debt, and savings ratios (see Chapter 2). Using the data and
calculations, comment on how well they are doing. You need to carefully explain and design your
analysis. What can and should they be doing to improve?
The time value of money – applications: questions 4 and 5 (40 points)
Question 4 (20 points): Retirement problem
You plan to retire 33 years from now and you expect to live for 27 years after retiring. You want
to have enough money upon reaching retirement age to withdraw $180,000 from the account at
the beginning of each year you expect to live, and yet still have $2,500,000 left in the account at
the time of your expected death (60 years from now). The company that just hired you, gave you
a $5,000 sign on bonus. You plan to accumulate the retirement fund by making equal annual
deposits (at the end of each year) for the next 33. For the first 10 years, you will deposit $2,000
annually in an account earning 12%. What equal annual deposits must you make each at the end
of each year, after year 10 (for 23 years), to reach your retirement goal? You only expect to earn
6% per year on your investment after you retire since you will choose to place the money in less
risky investments. Draw the timeline.
Question 5 (20 points): Tuition problem
A father is planning a savings program to put his daughter through college. His daughter is now 8
years old; today is her birthday. She plans to enroll at the university in 10 years, and she will enroll
on her 18th birthday. It should take her 4 years to complete her education. Currently, the cost for
college per year (for everything – food, clothing, tuition, books, transportation, and so forth) is
$40,000, but a 5 percent annual inflation rate in these costs is forecasted (inflation hint: if a price
is $100 today, with 5% inflation, in one year the price will be 100*(1+0.05), in 2 years
100*(1+0.05)^2, and so on). Assume that all college expenses (tuition, books, etc.) need to be
paid at the beginning of the year. The daughter recently received $15,000 from her grandfather’s
estate; this money will be invested in an investment account paying 8 percent (annual) interest
compounded annually. This money will be used to help meet the costs of the daughter’s
education. The money will be deposited in the investment account on her 8th birthday. The rest
of the cost of college will be met by money the father will be depositing in the same investment
account. He will make equal, annual deposits to the account in each year from his daughter’s 9th
birthday up to and including the day his daughter starts college. (Ignore taxes throughout the
problem). Draw the timeline.
Hint: Since the cost of tuition is not constant you cannot use the annuity formula. You need to
find the PV value of each of the payments on her 18th birthday and add them together to know
how much money the father needs.

Purchase answer to see full
attachment