Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

I need help with these problems. The details are included in the attached files. Thank you Submit a spreadsheet displaying how the answers were calculated.

I need help with these problems. The details are included in the attached files. Thank you

image text in transcribed 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?

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Entrepreneurial Finance

Authors: J. Chris Leach, Ronald W. Melicher

2nd Edition

0324289235, 9780324289237

More Books

Students also viewed these Finance questions

Question

Behaviour: What am I doing?

Answered: 1 week ago