MBA 620_P5_Miilestone_Princess_Calculation_date

xlsx

School

University of Maryland Global Campus (UMGC) *

*We aren’t endorsed by this school

Course

620

Subject

Finance

Date

Jan 9, 2024

Type

xlsx

Pages

Uploaded by CaptainStrawCrow4913

INSTRUCTIONS Complete the Cost of Capital tab Complete the Payback tab Complete the Budget Projections tab o Find the cost of Equity using the Capital Asset Pricing Model o Find the Weighted Average Cost of Capital (WACC) o Complete the After-tax Cash Flow re-evaluation table o Complete the DCF Payback timeline o Complete the questions on the tab o Revenue increases 4% annually o Expense increases 2¾% annually

l (CAPM)

Instructions: 1Find the cost of Equity using the Capital Asset Pricing Model (CAPM) 2Find the Weighted Average Cost of Equity (WACC) 1 2.200% 1.12 7.05% = CAPM 7.63% -------------------------------------- R F R M CAPM = Risk Free Rate + Beta x Market Premium 𝐶𝐴𝑃𝑀= 𝑅_𝐹 + ( 𝛽 × (𝑅 ̅_𝑀 − 𝑅_(𝐹 ))) CAPM Information from Largo Global Cost of Equity R F Risk-free rate of return = 2.20 percent 1 𝛽 Beta = 1.12. R M Expected Return of the Market = 7.05 percent 2 R P Market premium = R M - R F ____ 1 current U.S. 10-yr Treasury Yield. Source: U.S.Treasury.gov. Mar, 2022 2 The S&P 500 long-term average when holding the S&P 500 index. Source: https://ycharts.com/indicators/sp_500_1_year_return Jan, 2022

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

2 E $ 6,373,341,000 D $ 761,000,000 Total Capital (V) $ 7,134,341,000 $141,000,000 34.00% Last Fiscal Year End Interest Expense Tax Rate (T C ) WACC Information from Largo Global a. As of today, Largo Global market capitalization (E) is $6,373,341,000. 1 b. Largo Global's Market value of debt is $761,000,000. c. Cost of Equity = CAPM from question 1 d. Cost of Debt = Last Fiscal Year End Interest Expense 2 / Market Value of Debt (D). e. Use the tax rates given in Project 4 Tab 3. _________ 1 Market value of equity (E), also known as market cap, is calculated using the following equation: Market Cap = Share Price x Shares Outstanding from Project 1 2 From Project 1. Note that the Cost of Debt formula expressed at here is different from the cost of debt formula introduced in most textbooks. Most textbooks only consider the long-term debt (i.e., bond) as the debt and use the bond valuation formula when calculating the cost of debt and WACC.

1. Find the weight of equity = E / (E + D). 89.33% 2. Find the weight of debt = D / (E + D). 10.67% Re 3. Find the cost of equity using CAPM. 7.63% Rd 4. Find the cost of debt. 18.53% WACC 5. Find the weighted average cost of capital. 8.12% 𝑬/𝑽 𝑫/𝑽

Payback Table View After-Tax Cash Flow Re-evlauation and Payback Timelines Instructions Technologically advanced distribution equipment proposal re-evaluation The CFO has asked you to re-evaluate the cash flow projections associated with the equipment purchase proposal due to the proposed loan agreemen recommend whether the purchase should go forward. Table 1 shows the data and Table 2 shows projections of the cash inflows and outflows that wo occur during the first eight years using the new equipment. Keep the following in mind: Row 34 has a suggested Excel function to use. Complete all the blank cells within the tables. I. In the Data Table: A. Use the WACC calulated on the Cost of Capital tab B. Calulate the loan amount with a 10% down payment II. In the After-tax Cash Flow: C. Complete the Depreciation Expense from Project 4 (straight line, $0 Salvage) D. Complete the interest expense using the loan interest rate. E. Complete the After-tax Cash Flow Table including the interest expense F. Compute the PV, NPV 1 , IRR, and adjusted NPV 2 III. In the Payback Timeline View: G. Complete the discounted cash flow Payback Timeline View of Discounted Cash Flows i) complete the timeline amounts based on the DCF (DCF is the same as PV) ii) complete the timeline amountss for the Cummulative DCF iii) calulate the payback period in years and months IV. Answer the following questions: 1. What is the total depreciation for tax purposes? 2. What is the total PV of the Cash Flows using the WACC rate? 3. What is the NPV using the WACC rate? 4. What is the NPV using the alternative rate? 5. What is the IRR? 6. What is the payback period using the DCF? 7. Should the project be accepted? Why?

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Table 1 - Data Cost of new equipment (at year 0) 191.10 million Corporate income tax rate - Federal 26.0% Corporate income tax rate - State of Maryland 8.0% Discount rate for the project using WACC 8.12% Loan Amount 171.99 million Loan Interest rate (Prime + 2) 5.25% Table 2 - After-tax Cash Flow Table (all figures in $ millions) Year PV Excel function to use : SLN IPMT PV 0 ($191.10) 1 $850.0 $840.0 $23.89 $9.03 ($22.92) ($5.96) ($1.83) $8.76 $8.10 2 $900.0 $810.0 $23.89 $8.09 $58.02 $15.09 $4.64 $62.18 $53.19 3 $990.0 $870.0 $23.89 $7.11 $89.01 $23.14 $7.12 $82.63 $65.38 4 $1,005.0 $900.0 $23.89 $6.07 $75.04 $19.51 $6.00 $73.42 $53.72 5 $1,200.0 $1,100.0 $23.89 $4.98 $71.14 $18.50 $5.69 $70.84 $47.94 6 $1,300.0 $1,150.0 $23.89 $3.83 $122.29 $31.79 $9.78 $104.60 $65.48 7 $1,350.0 $1,300.0 $23.89 $2.61 $23.50 $6.11 $1.88 $39.40 $22.81 8 $1,320.0 $1,300.0 $23.89 $1.34 ($5.23) ($1.36) ($0.42) $20.44 $10.94 PV $327.57 NPV 0 1 2 3 4 5 6 7 Projected Cash Inflows from Operations Projected Cash Outflows from Operations Depreciation Expense Interest Expense Projected Taxable Income Projected Federal Income Taxes Projected State Income Taxes Projected After-tax Cash Flows NPV 1 - calculated NPV including interest expense NPV 2 - calculated NPV at the lower discount rate of 5.02% Payback Timeline View Example of Actual Cash Flows

| | | | | | | | Cash Flow ($191.10) $8.76 $62.18 $82.63 $73.42 $70.84 $104.60 $39.40 ($191.10) ($182.34) ($120.16) ($37.53) $35.89 $106.73 $211.33 $250.73 Payback Period 3 years 6 0 1 2 3 4 5 6 7 | | | | | | | | ($191.10) $8.10 $53.19 $65.38 $53.72 $47.94 $65.48 $22.81 Cummulative DCF ($191.10) ($183.00) ($129.80) ($64.43) ($10.70) $37.24 $102.72 $125.53 Payback Period 4 years 3 ANSWER THESE QUESTIONS: 1. What is the total depreciation for tax purposes? $ 191.10 2. What is the total PV of the Cash Flows using the WACC rate? $327.57 3. What is the NPV using the WACC rate? $136.47 4. What is the NPV using the alternative rate? $180.50 Cummulative Cash Flow Discounted Cash Flow (DCF)

5. What is the IRR? 23.35% 6. What is the payback period using the DCF? 4 yrs 3 m 7. Should the project be accepted? Why? Yes, as it has a positive NPV, favorable IRR, and a reasonable paybac This indicates that the project is expected to generate higher returns provide value to the company.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

nt, and ould

IRR NPV IRR NPV ($191.10) $8.34 $56.38 $71.34 $60.35 $55.45 $77.96 $27.96 $13.81 $136.47 $180.50 IRR 23.35% 8 NPV 1 NPV 2

| $20.44 $271.17 $271.17 months 8 PV | ($191.10) $10.94 $327.57 $136.47 $136.47 months

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

ck period. s than the cost of capital and will

INSTRUCTIONS: 1). Complete the budget projections for years 2021-2024 using the following information Revenue increases 4% annually Expense increases 2¾% annually 2). Answer the question below the forecast. 1). Largo Global Income Statement of December 31, 2020 (millions) ACTUAL BUDGET FORECAS 2020 2021 2022 2023 Sales (net sales) $2,013 $2,094 $2,177 $2,264 Cost of goods sold 1400 1439 1478 1519 Gross profit 613 655 699 746 125 128 132 136 488 527 567 610 Depreciation and amortization 174 174 174 198 314 353 393 412 Interest expense 141 141 141 148 Earnings before taxes (EBT) 173 212 252 264 Taxes (34%) 59 72 86 90 Net earnings (loss)/Net Income $ 114 140 166 174 2). Based on the changes suggested throughout the 5 projects, is Largo Global in a better fina For Depreciation and Interest expenses assume the Acutal 2020 figure as the base for t and and forecast then add the amount calculated in the Payback tab for both budget an Selling, general, and administrative expenses Earnings before Interest, taxes, depreciation, and amortization (EBITDA) Earning before interest and taxes (EBIT) Operating income (loss)