Can you solve the equations within this worksheet? All information is in the worksheet

 

 

Part 1 Calculating WACC:
First calculate the expected cost of equity determined using the CAPM:
CAPM = Risk Free Rate + Equity Beta * Market Risk Premium
And we need to remember that market risk premium = Return on Market - Risk free rate
So
CAPM = Rrf + (beta*(retrurn on market - Rrf)
Next calculate the WACC of the firm:
WACC = (Weight Debt * Cost of Debt) + (Weight Equity * Cost of Equity )
and remember to calculate the cost of debt as Cost od debt*(1-Tax rate) Part 2 Use the CAPM for calculation of the cost of equity.
2. Calculate the cash flows of the project given the following assumptions: · Initial investment outlay of $60 million, comprised of $50 million for machinery with $10 million for net working capital (m
· Project and equipment life is five years
· Revenues are expected to increase to $50 million annually in each of the future years
· Gross margin percentage is 60% (not including depreciation)
· Depreciation is computed at the straight line rate for tax purposes
· Selling, general, and administrative expenses are 5% of sales
· Tax rate is 30%, a reduced rate that reflects a tax credit due to the repurpose of the building Compute net present value and internal rate of return of the project. You may use Excel to comple Year
Revenues
Gross Margin
Sales & Admin
Depreciation 0 1 2 3 NWC Increase
NWC Recovery
Capital Expenditures
FCF
Tax
Discount Rate 30%
Wacc as found in part A NPV Use NPV Formula = CFo + (NPV(WACC,FCF1,FCF2,FCF3,FCF4,FCF5) IRR Use IRR formula =IRR(Cfo:Cf5) n for net working capital (metal and gemstone inventory) y use Excel to complete this project. The text has a number of resources that provide examples of spreadsheet soluti 4 5 Comments
50 m per year
60% of revenues
5% of revenues
50 million over 5 years 10 million out year 0
10 million recovered end of project
50 million
FCF=((Gross Margin-Sales&Admin)*(1-tax rate))+(Depreciation*tax rate)-NWC Increase - C or =C14+NPV(C16,D14:H14)
or = IRR(C14:H14) ples of spreadsheet solutions for this purpose. Model your responses according to these examples. tion*tax rate)-NWC Increase - Capex + NWC recovery at end of project

Attachments:

• 1492841561-Short application 1 - Excel Outline _1_ _1_ _7__1_1 1.xlsx