FIN 6406 Excel HW 1 AP

You need the excel spreadsheet provided that has financials for a firm.

1. Determine the Cash Flow From Assets (FCF), Cash Flow to Creditors, and Cash Flow to Shareholders for the firm.
2. Assume you are sitting in a board meeting discussing the firms plans for sales growth. You are telling the board that you expect to grow sales by 7.5 %. After explaining your plan, a board member asks you for an estimate of how much funding you will need in 2020. What is your answer if you use the formula for External Funds Needed?
3. Now assume the board member asks whether or not you can achieve the growth holding your capital structure constant. What is your answer?
4. What is your answer if the board member asks if you can achieve the growth without issuing ANY new external financing?
5. Calculate the following ratios for the firm for 2019.

Current Quick Cash Debt

Equity Mult Cash Coverage Inventory Turn Days Sales in Inv

Rec Turn Days Sales Out Total Asset Turn Profit Margin

ROA ROE

6. Also calculate ROE using the expanded DuPont formula.

Using the same excel spreadsheet but with different tabs, answer the following questions. Where stated, make sure you solve the problem by hand/basic PV/FV techniques, i.e. discounting each cash flow, and also using TVM formulas from Excel. All work should be presented in three sheets/tabs (not files). Your tabs should be labeled. Questions 1-4 are in the second tab, questions 5-6 are in the third tab, and question 7 is in a fourth tab. In all cases, presentation matters!!! Make these professional. Also, all work should be set up in such a way that you could change one parameter and have the formula automatically update, i.e. you need an assumptions section. In other words, you would want to be able to do a basic type of what-if analysis. Hard-coding values is bad, formulas are good.

1. Find the FV of $5,000, 15 years in the future if the opportunity cost rate is 10% (use Excel formulas and basic PV/FV techniques).

2. Find the PV of $500,000 to be delivered in 20 years if the opportunity cost rate is 8.66% (use Excel formulas and basic PV/FV techniques).

3. Would you prefer the following (you can use just formulas for this problem):

1. An ordinary annuity where you receive $500 each year for 20 years if the opportunity cost is 6%
2. An annuity due where you receive $480 each year for 20 years if the opportunity cost rate is 5.75%

4. What is the net present value of the following stream of cash flows assuming the opportunity cost rate is 15%? Solve NPV using each cash flow and the NPV function in Excel (use Excel formulas and basic PV/FV techniques).

Year 0: -$10,000

Year 1: $4,000

Year 2: -$2,000

Year 3: $5,000

Year 4: $3,000

Year 5: $6,000

5. You are planning for retirement. Use the following information to answer the question.
   1. The expected inflation rate from now until retirement is expected to be 2.5%.
   2. Your current income/expenses are $100,000 and that is the same standard of living you will have in retirement. To estimate expenses, you can take the average of the first year in retirement and last year in retirement and assume that is the value for each year in retirement.
   3. You can expect to earn 5% on your money in retirement.
   4. You are 35 when you graduate and expect to work until you are 65.
   5. Your life expectancy is 87.
   6. You will leave your heirs $1,000,000 upon your death.

How much will you need at retirement if you assume that the amount you will spend in retirement is the average of the first and last years expenses in retirement?

7. Assume that you withdraw the expenses at the beginning of each year of retirement.

6. You are saving for retirement. Use the following information to answer the question.
   1. Using the salary in 5, assume your average salary increase is 3.75% per year.
   2. Assume that you save in a 401(k) type account the average of your first and last years salary at 15% each year (similar to the way you did expenses). To estimate contributions, you can take the average of the first year working contribution and the last working year contribution and assume that is the value for each years contribution during your career.
   3. You earn 9% throughout your working life.

Will you have enough in retirement (tell me how much you will have)?

How much in total can you leave your heirs to exactly meet your longevity goal?

7. Create an amortization table for the following mortgage.
   1. Mortgage amount is $300,000.
   2. The mortgage is a 30 year fixed at 4% (paid monthly).
   3. You pay an additional $100 per month until the mortgage is paid.

When will the mortgage be paid off?