Can I get the solution for the homework by excel (with formulas)?

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 firm's plans for sales

growth.You are telling the board that you expect to grow sales by 15 %.

After explaining your plan, a board member asks you for an estimate of how much

funding you will need in 2021.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 2020.

CurrentQuickCash Debt Equity Mult

Cash CoverageInventory Turn Days Sales in Inv Rec Turn

Days Sales OutTotal Asset TurnProfit Margin ROAROE

6.Also calculate ROE using the expanded DuPont formula.

Balance

Sheet

Assets:20202019Liab+Eq:20202019

Cash$163$154Acc Payable$266$100

Acc. Receiv.$550$455Accruals$150$150

Inventory$569$450Total Current$416$250

Total Current $1,282$1,059Long-Term Debt$296$350

Net Fixed Assets $1,739$1,400Total Liab $712$600

Total Assets$3,021$2,459Common at par$55$45

Capital Surplus$585$494

Retained Earnings$1,729$1,370

Treasury Stock$(60)$(50)

Total Equity$2,309$1,859

Total Liab & Eq$3,021$2,459

Income Statement

Sales$1,753

Cost of Good Sold900

Depreciation90

Operating Income 763

Interest Expense82

Pre-tax Income681

Taxes212

Net Income469

Dividends110

Addition to RE's 359

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 use a basic type of

"what-if" analysis.Hard-coding values is bad, formulas are

good.

1.Find the FV of $15,000, 5 years in the future if the opportunity cost rate is 7.37%

(use Excel formulas and basic PV/FV techniques).

2.Find the PV of $25,000 to be delivered in 20 years if the opportunity cost rate is

5.39% (use Excel formulas and basic PV/FV techniques).

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

a.An ordinary annuity where you receive $500 each year for 20 years if the

opportunity cost is 6%

b.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.35%? 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: -$1,500

Year 3: $5,000

Year 4: $2,800

Year 5: $6,000

5.You are planning for retirement.Use the following information to answer the

question.

a.The expected inflation rate from now until retirement is expected to be 2.5%.

b.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.

c.You can expect to earn 5% on your money in retirement.

d.You are 35 when you graduate and expect to work until you are 65.

e.Your life expectancy is 87.

f.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 year's expenses in retirement?

g.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.

a.Using the salary in 5, assume your average salary increase is 3.75% per year.

b.Assume that you save in a 401(k), write account the average of your first and last

year's 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 year's contribution during your career.

c.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.Make an amortization table for the following mortgage.

a.Mortgage amount is $300,000.

b.The mortgage is a 30 year fixed at 6% (paid monthly).

c.You pay an additional $300 per month until the mortgage is paid.

When will the mortgage be paid off?