FINANCE PROBLEMS 1-20

FINANCE PROBLEMS 1-20

Subject: Business    / Finance
Question
Submit a spreadsheet displaying how the answers were calculated. Each problem should be
answered on a clearly labeled separate worksheet of a spreadsheet. Answers should be
formatted in a manner that makes the answers clear and easy to read. Each of the following
Excel functions should be used to calculate at least one of the answers: FV, PV, PMT, RATE,
NPER, NPV, AVERAGE, STDEV, and IRR. Calculating the answers elsewhere and typing or
pasting them into the spreadsheet is unacceptable. The spreadsheet project submitted
should be a single-file readable in Microsoft Excel.
I will submit an Excel spreadsheet named & saved as I need it to be as well for the
problems to be calculated in. 1.)Cash flows: It is typical for Jane to plan, monitor, and assess her financial position
using cash flows over a given period, typically a month. Jane has a savings account and
her bank loans money at 6 % per year while it offers short-term investment rates of 5 %.
Jane’s cash flows during August were as follows:
Item
Cash Inflow
Cash Outflow
Clothes
$1,100
Interest received
$440
Dining out
$470
Groceries
$810
Salary
$4,400
Auto payment
$344
Utilities
$260
Mortgage
$1,340
Gas
$203
a.??Determine Jane’s total cash inflows and cash outflows.
b.??Determine the net cash flow for the month of August.
c.??If there is a shortage, what are a few options open to Jane?
d.??If there is a surplus, what would be a prudent strategy for her to follow? 2.)Income statement preparation: Adam and Arin Adams have collected their personal income
and expense information and have asked you to put together an income and expense statement
for the year ended December 31, 2015. The following information is received from the Adams
family Adam’s salary $45,200
Arin’s salary 29,800
Interest received 530
Dividends received 140
Auto insurance 580
Home insurance 760
Auto loan payment 3,320
Mortgage payment 14,300 Utilities $3,210
Groceries 2,150
Medical 1,450
Property taxes 1,657
Income tax, Social Security 12,900
Clothes and accesories 1,960
Gas and auto repair 2,090
Entertainment 1,980 a. Create a personal income and expense statement for the period ended December 31, 2015. It
should be similar to a corporate income statement.
b. Did the Adams family have a cash surplus or cash deficit?
c. If the result is a surplus, how can the Adams family use that surplus?
3.) Balance sheet preparation: Adam and Arin Adams have collected their personal asset and
liability information and have asked you to
put together a balance sheet as of December 31, 2015. The following information is received
from the Adams family:
Cash
$307
Checking $2957
Savings
$1291
IBM Stock $1998
Auto Loan $7944
Mortgage $100,612
Medical Bills Payable $249
Utilities Payable $150
Real Estate $149,519 Retirement funds, IRA
2014 Sebring
2010 Jeep
Money Market Funds
Jewelry & Artwork
Net Worth
Household furnishings
Credit card Balance
Personal Loan $1957
$15,055
$7948
$1,150
$3080
$75,551
$4291
$2094
$2944 a. Create a personal balance sheet as of December 31, 2015. It should be similar to a corporate
balance sheet.
b. What must the total assets of the Adams family be equal to by December 31, 2015?
c. What was their net working capital (NWC) at the end of the year?
(Hint:
NWC is the difference between total liquid assets and total current liabilities.) 4.) Liquidity ratio- Josh Smith has compiled some of his personal financial data in order to
determine his liquidity position. The data are as follows:
Account
Amount
Cash
$ 3,280
Marketable securities
910
Checking account
750
Credit card payable
1,150
Short-term notes payable
1,170
a. Calculate Josh’s liquidity ratio.
b. Several of Josh’s friends have told him that they have liquidity ratios of about 1.5
How would you analyze Josh’s liquidity relative to his friends?
5.) Preparation of cash budget-Personal finance problem: Sam and Suzy Sizeman need to
prepare a cash budget for the last quarter of
2016 in order to make sure they can cover their expenditures during the period. Sam and Suzy
have been preparing budgets for the past
several years and have been able to establish specific percentages for most of their cash outflows.
These percentages are based on their take-home pay (that is., monthly utilities normally run
5.4 % of monthly take-home pay). The information her can be used to create their fourth-quarter
budget for 2016.
Income
Monthly take-home pay $4,919
Expenses
Housing 29.6%
Utilities 5.4%
Food 10.1%
Transportation 7.1%
Medical/dental 0.5%
Clothing for October & November 3.3%
Clothing for December $443
Property taxes (November only) 11.1%
Appliances 1.4%
Personal Care 1.9%
Entertainment for October & November 6.2%
Entertainment for December $1,529
Savings 7.1%
Other 5.2%
Excess cash 4.4%
a. Prepare a quarterly cash budget for Sam and Suzy covering the months October through
December 2016.
b. Are there individual months that incur a deficit?
c. What is the cumulative cash surplus or deficit by the end of December 2016? 6.) Time value-Personal Finance Problem: You have $3,300
to invest today at 11% interest compounded annually.
a.Find how much you will have accumulated in the account at the end of (1) 3years, (2) 6
years, and (3) 9 years.
b.Use your findings in part a to calculate the amount of interest earned in (1) the first 3 years
(years 1 to 3), (2) the second 3years (years 4to 6), and (3) the third 3years (years 7to 9).
c.Compare and contrast your findings in part b. Explain how the amount of interest earned
changes in each succeeding 3 dash year period.
7.) Time value-Personal Finance Problem: You can deposit $10,000 into an account paying 14%
annual interest either today or exactly 5years from today. How much better off will you be at the
end of 35years if you decide to make the initial deposit today rather than 5
years from today? 8.) Time value-Personal Finance Problem: Jim Nance has been offered an investment that will
pay him $380 three years from today.
a.If his opportunity cost is 5% compounded annually, what value should he place on this
opportunity today???
b.What is the most he should pay to purchase this payment today?
c. If Jim can purchase this investment for less than the amount calculated in part (a),
what does that imply about the rate of return that he will earn on the investment?
9.) Cash flow investment decision-Personal Finance Problem: Tom Alexander has an
opportunity to purchase any of the investments shown in the following table,
Investment Price
Single cash inflow
Year of receipt
A
$15,700
$22,986
3
B
$471
$2,381
18
C
$2,983
$7,034
8
D
$785
$26,693
38
.
The purchase price, the amount of the single cash inflow, and its year of receipt are given for
each investment. Which purchase recommendations would you make, assuming that Tom can
earn 10% on his investments? 10.) Value of a retirement annuity-Personal Finance Problem: An insurance agent is trying to sell
you an immediate-retirement annuity, which for a single amount paid today will provide you
with $14, 900 at the end of each year for the next 15years. You currently earn 4% on low-risk
investments comparable to the retirement annuity. Ignoring taxes, what is the most you would
pay for this annuity? 11.) René Levin wishes to determine the future value at the end of 8 years of a $13,100 deposit made today into an account paying a nominal annual rate of 11%. a.Find the future value of René’s deposit, assuming that interest is compounded (1) annually, (2)
quarterly, (3) monthly, and (4) continuously.
b. Compare your findings in part a, and use them to demonstrate the relationship between
compounding frequency and future value.
c.What is the maximum future value obtainable given the
$13 comma 100 deposit, the 8-year time period, and the 11%
nominal annual rate? Use your findings in part a to explain. 12.) ?Tim Smith is shopping for a used car. He has found one priced at $ 4,400. The salesman
has told Tim that if he can come up with a down payment of $900, the dealer will finance the
balance of the price at an annual rate of 9% over 4 years (48months).?(Hint: Use four decimal
places for the monthly interest rate in all your calculations.)
a.??Assuming that Tim accepts the dealer’s offer, what will his monthly (end-of-month)
payment amount be?
b.??Use a financial calculator or spreadsheet to help you figure out what Tim’s monthly
payment would be if the dealer were willing to finance the balance of the car price at an annual
rate of 7%?
13.) Rishi Singh has $2,000 to invest. His investment counselor suggests an investment that pays
no stated interest but will return $2,500 at the end of 6years.??
a. What annual rate of return will Rishi earn with this investment?
b. Rishi is considering another investment, of equal risk, that earns an annual return of??
1.79% . Which investment should he make, and why? 14.) ??Mia Salto wishes to determine how long it will take to repay a loan with initial
proceeds of $9,000 where annual end-of-year installment payments of $2,006 are required.
a. If Mia can borrow at an annual interest rate of 14%, how long will it take for her to repay the
loan fully?
b. How long will it take if she can borrow at an annual rate of 11%?
c.How long will it take if she has to pay 17%
annual interest?
d.Reviewing your answers in parts a, b, and c, describe the general relationship between the
interest rate and the amount of time it will take Mia to repay the loan fully.
15.) ?Laura Drake wishes to estimate the value of an asset expected to provide cash inflows of
$ 3,000 per year at the end of years 1 through 4 and $17,458
at the end of year 5. Her research indicates that she must earn 8% on low-risk assets, 15%
on average-risk assets, and 22% on high-risk assets.
a.Determine what is the most Laura should pay for the asset if it is classified as (1) low-risk, (2)
average-risk, and (3) high-risk.
b.Suppose Laura is unable to assess the risk of the asset and wants to be certain she’s making a
good deal. On the basis of your findings in part a, what is the most she should pay? Why?
c. All else being the same, what effect does increasing risk have on the value of an asset? Explain
in light of your findings in part a.
16.) ?Lynn Parsons is considering investing in either of two outstanding bonds. The bonds both
have $1,000 par values and 8% coupon interest rates and pay annual interest. Bond A has exactly
6 years to maturity, and bond B has 16 years to maturity.??
a.Calculate the present value of bond A if the required rate of return is: (1) 5%, (2) 8%, and
(3)11%.
b.Calculate the present value of bond B if the required rate of return is: (1) 5%, (2) 8%, and
(3)11%.
c. From your findings in parts a and b, discuss the relationship between time to maturity and
changing required returns.
d.If Lynn wanted to minimize interest rate risk, which bond should she purchase? Why? 17.) ?Jamie Wong is considering building an investment portfolio containing two stocks, L and
M. Stock L will represent 60% of the dollar value of the portfolio, and stock M will account for
the other 40%. The expected returns over the next 6 years, 2015minus 2020, for each of these
stocks are shown in the following table:
Expected Return
Year Stock L Stock M 2015 15% 21% 2016 16% 19% 5017 16% 17% 2018 16% 15% 2019 16% 13% 2020 17% 11% a.Calculate the expected portfolio return, r Subscript p, for each of the 6 years.
b. Calculate the expected value of portfolio returns, r overbar Subscript p, over the 6-year period.
c.Calculate the standard deviation of expected portfolio returns,
sigma Subscript r Sub Subscript p, over the 6-year period.
d. How would you characterize the correlation of returns of the two stocks L and M?
e. Discuss any benefits of diversification achieved by Jamie through creation of the portfolio.
18.) ?Katherine Wilson is wondering how much risk she must undertake to generate an
acceptable return on her porfolio. The risk-free return currently is 7%. The return on the average
stock (market return) is 10%. Use the CAPM to calculate the beta coefficient associated with a
portfolio return of 13%. 19.) Billy and Mandy Jones have $21,000 to invest. On average, they do not make any
investment that will not return at least 7.7% per year. They have been approached with an
investment opportunity that requires $21,000 upfront and has a payout of $5,900 at the end of
each of the next 5 years. Using the internal rate of return (IRR) method and their requirements,
determine whether Billy and Mandy should undertake the investment. 20.) Rieger International is attempting to evaluate the feasibility of investing $91,000 in a piece
of equipment that has a 5-year life. The firm has estimated the cash inflows associated with the
proposal as shown in the following table:
Year Cash Inflows 1 $40,000 2 $25,000 3 $30,000 4 $25,000 5 $25,000 The firm has a 12% cost of capital.
a.?Calculate the payback period for the proposed investment.
b.?Calculate the net present value (NPV) for the proposed investment.
c.?Calculate the internal rate of return (IRR), rounded to the nearest whole percent, for the
proposed investment.
d.?Evaluate the acceptability of the proposed investment using NPV and IRR. What
recommendation would you make relative to implementation of the project?

https://applewriters.com/place-order/
Order Now