saint MGT325 week 5 Spreadsheet project
Report this Question as Inappropriate
Question
Please see Attachment for first portion…
1. 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 1400
Interest received 440
Dinning out 350
Groceries 810
Salary 4600
Auto payment 339
Utilities 280
Mortgage 1340
Gas 218
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. Adam and Arin Adams have collected their personal income and expense information and have asked you to put together an income expense statement for the year ended December 31, 2015. The following information from the Adams Family.
Adam’s Salary 45100 Utilities 3170
Arin Salary 29800 Groceries 2230
Interest received 540 Medical 1530
Dividends received 120 Property taxes 1657
Auto Insurance 610 Income tax , Social Security 12600
Home Insurance 730 Clothing and accessories 2040
Auto loan payment 3340 Gas and repair 2080
Mortgage payment 13900 Entertainment 2040
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 cash surplus or cash deficit?
c. If the result is a surplus, how can the Adams family use the surplus?
3. 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 297 Retirement funds, IRA 1940
Checking 2917 2014 Sebring 14943
Savings 1137 2010 Jeep 8078
IBM stock 1970 Money Market funds 1298
Auto Loan 8063 Jewelry and artwork 3086
Mortgage 99290 Net worth 77337
Medical bills payable 258 Household furnishings 4124
Utility bills payable 142 Credit card balance 1988
Real estate 150363 Personal loan 3075
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 December 31, 2015
c. What was their net working capital (NWC) at the end of the year? (Hint: NWC is the difference between liquid assets and total current liabilities)
4. 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 3270
Marketable securities 960
Checking account 780
Credit card payables 1140
Short term notes payable 910
a. Calculate Josh’s liquidity ratio
b. Several of Josh’s friends have told him they have liquidity ratios of about 1.8. How would you analyze Josh’s liquidity relative to his friends?
5. 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 normally run 4.6% of monthly take home pay). The information here can be used to create their fourth quarter budget for 2016.
Income
Monthly take home pay 4911
Expenses
Housing 29.8%
Utilities 4.6%
Food 9.9%
Transportation 6.6%
Medical/dental 0.5%
Clothing for October & November 3.2%
Clothing for December $439
Property taxes (November only) 11.9%
Appliances 1.4%
Personal care 1.9%
Entertainment for October & November 6.1%
Entertainment for December $1462
Savings 7.9%
Other 4.8%
Excess in cash 4.5%
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. You have $3100 to invest today at 7% interest compounded annually
a. Find how much you will accumulated in the account at the end of (1) 2 years, (2) 4 years, and (3) 6 years.
b. Use your findings in part A to calculate the amount of interest earned in (1) the first 2 years (years 1 to 2), (2) the second 2 years (years 3 to 1), and (3) the third 2 years (years 5 to 6)
c. Compare and contrast your findings in part B. Explain how the amount of interest earned in each succeeding 2-year period.
7. You can deposit $10,000 into an account paying 5% annual interest either today or exactly 5 years from today. How much better off will you be at the end of 25years if you decided to make the initial deposit today rather than 5 years from today?
8. Jim Nance has been offered an investment that will pay him 670 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 par (a), what does that imply about the rate of return that he will earn on the investment?
9. Tom Alexander has an opportunity to purchase any of the investments shown in the following table. 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 earn 10% on his investments?
Investment Price Single cash inflow Year of receipt
A 10300 15080 3
B 309 1562 18
C 1957 4615 8
D 515 17512 38
10. An insurance agent is trying to sell you an immediate-retirement annuity, which for a single amount paid today, will provide you with 12600 at the end of each year for the next 20 years. You currently earn 6% on low risk investments comparable to the retirement annuity. Ignoring taxes, what is the most you would pay for
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.
