need revision on finance questions-excel experts only

Description

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

Don't use plagiarized sources. Get Your Custom Assignment on
need revision on finance questions-excel experts only
From as Little as $13/Page

Unformatted Attachment Preview

INSTRUCTIONS
Complete the Cost of Capital tab
o Find the cost of Equity using the Capital Asset Pricing Model (CAPM)
o Find the Weighted Average Cost of Capital (WACC)
Complete the Payback tab
o Complete the After-tax Cash Flow re-evaluation table
o Complete the DCF Payback timeline
o Complete the questions on the tab
Complete the Budget Projections tab
o Revenue increases 4% annually
o Expense increases 2¾% annually
Model (CAPM)
Instructions:
1 Find the cost of Equity using the Capital Asset Pricing Model (CAPM)
2 Find the Weighted Average Cost of Equity (WACC)
1
CAPM Information from Largo Global Cost of Equity
RF
Risk-free rate of return = 2.20 percent1

Beta = 1.12.
RM
Expected Return of the Market = 7.05 percent2
RP
Market premium = RM – RF
____
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
CAPM = Risk Free Rate + Beta x Market Premium
=
RF
2.200%
————————————–

1.12
RM

+
7.05%
( × ( ത − ))
= CAPM
7.63%
2 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 Expense2 / 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.
E
D
Total Capital (V)
Last Fiscal Year End
Interest Expense
Tax Rate (TC)
$
$
$
6,373,341,000
539,500,000 Notes payable avg:
6,912,841,000
$141,000,000
34.00%

1. Find the weight of equity = E / (E + D).
92.20%

2. Find the weight of debt = D / (E + D).
7.80%
Re
3. Find the cost of equity using CAPM.
7.63%
Rd
4. Find the cost of debt.
26.14%
5. Find the weighted average cost of capital.
7.21%
WACC
$
122 Long-term debt avg: $
418
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 agreement,
whether the purchase should go forward. Table 1 shows the data and Table 2 shows projections of the cash inflows and outflows that would occur during the first eig
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, NPV1, IRR, and adjusted NPV2
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?
Payback Table View
Table 1 – Data
191.10 million
26.0%
8.0%
7.21%
171.99 million
5.25%
Cost of new equipment (at year 0)
Corporate income tax rate – Federal
Corporate income tax rate – State of Maryland
Discount rate for the project using WACC
Loan Amount
Loan Interest rate (Prime + 2)
10% down payment
19.11
Table 2 – After-tax Cash Flow Table
(all figures in $ millions)
Year
Projected Cash Projected Cash
Depreciation
Inflows from Outflows from
Expense
Operations
Operations
Excel function to use :
0
1
2
3
4
5
6
7
8
$850.0
$900.0
$990.0
$1,005.0
$1,200.0
$1,300.0
$1,350.0
$1,320.0
$840.0
$810.0
$870.0
$900.0
$1,100.0
$1,150.0
$1,300.0
$1,300.0
SLN
Interest
Expense
Projected
Taxable Income
Projected Projected
State
After-tax
Projected Federal Income
Cash
Income Taxes
Taxes
Flows
IPMT
$23.89
$23.89
$23.89
$23.89
$23.89
$23.89
$23.89
$23.89
$9.03
$8.09
$7.11
$6.07
$4.98
$3.83
$2.61
$1.34
($22.92)
$58.02
$89.01
$75.04
$71.14
$122.29
$23.50
($5.23)
($5.96)
$15.09
$23.14
$19.51
$18.50
$31.79
$6.11
($1.36)
($1.83)
$4.64
$7.12
$6.00
$5.69
$9.78
$1.88
($0.42)
NPV1 – calculated NPV including interest expense
NPV2 – calculated NPV at the lower discount rate of 5.02%
($191.10)
$8.76
$62.18
$82.63
$73.42
$70.84
$104.60
$39.40
$20.44
PV
NPV
Payback Timeline View Example of Actual Cash Flows
0
1
2
3
4
5
6
|
|
|
|
|
|
|
Cash Flow
($191.10)
$8.76
$62.18
$82.63
$73.42
$70.84
$104.60
Cummulative Cash
Flow
($191.10)
($182.34)
($120.16)
($37.53)
$35.89
$106.73
$211.33
Payback Period
3 years
0
1
2
3
4
5
6
|
|
|
|
|
|
|
Discounted Cash
Flow (DCF)
($191.10)
$53.47
$47.92
$42.08
$35.93
$29.46
$22.65
Cummulative DCF
($191.10)
($137.63)
($89.70)
($47.62)
($11.69)
Payback Period
$17.78
$33.26
5.60 years
ANSWER THESE QUESTIONS:
1. What is the total depreciation for tax purposes?
$
191.12
2. What is the total PV of the Cash Flows using the WACC rate?
$339.65
3. What is the NPV using the WACC rate?
$148.55
4. What is the NPV using the alternative rate?
$180.50
5. What is the IRR?
23.35%
6. What is the payback period using the DCF?
5 years 4 months
7. Should the project be accepted? Why?
The project should be approved based on the capital budgeting findings because o
When accounting for the entire cost of the new equipment, the project has a p
$133.08 million. Almost three times the WACC of 8.38% is represented by the IR
positive and will surpass the cost of capital. The project’s payback period, which w
drawback. Despite this, the project will be lengthy but p
an agreement, and recommend
uld occur during the first eight years
PV
NPV1
IRR
NPV2
PV
NPV
IRR
NPV
($191.10)
$8.34
$59.21
$78.68
$69.91
$67.45
$99.60
$37.51
$19.46
$53.47
$47.92
$42.08
$35.93
$29.46
$22.65
$15.48
$7.94
$339.65
$148.55
IRR
7
8
|
|
$180.50 Discount Rate
23.35%
5.02%
$39.40
$20.44
$250.73
$271.17
$271.17
6 months
7
8
PV
|
|
($191.10)
$15.48
$7.94
$254.95
$63.85
$48.75
$56.69
5 months
udgeting findings because of the numerous favorable outcomes.
uipment, the project has a positive net present value (NPV) of
38% is represented by the IRR of 23.35%; ideally, the IRR will be
‘s payback period, which will take more than five years, is its only
project will be lengthy but profitable.
INSTRUCTIONS:
1). Complete the budget projections for years 2023-2026 using the following information
Revenue increases 4% annually
Expense increases 2¾% annually
For Depreciation and Interest expenses assume the Acutal 2022 figure as the base for the budget and
and forecast then add the amount calculated in the Payback tab for both budget and forecast.
2). Answer the question below the forecast.
1).
Largo Global Income Statement of December 31, 2022 (millions)
Sales (net sales)
Cost of goods sold
Gross profit
Selling, general, and administrative
expenses
Earnings before Interest, taxes,
depreciation, and amortization
(EBITDA)
Depreciation and amortization
Earning before interest and taxes
(EBIT) Operating income (loss)
Interest expense
Earnings before taxes (EBT)
Taxes (34%)
Net earnings (loss)/Net Income
ACTUAL BUDGET
FORECAST
2025
2022
2023
2024
$2,013 $2,093.52 $2,177.26 $2,264.35
1400 $1,438.50 $1,478.06 $1,518.71
613
655
699
746
$
125
$128.44
$131.97
$135.60
488
527
567
610
174
$202.67
$232.13
$262.40
314
324
335
348
141
173
59
114
$153.91
170
$57.80
112
$166.23
170
$57.76
112
$177.91
170
$57.83
112
2). Based on the changes suggested throughout the 5 projects, is Largo Global in a better financial position?
LGI was able to evaluate their business processes and create an improvement plan that has improved their financ
beginning. LGI was dealing with decreasing net income year over year, rising total liabilities, and much lower ne
comparison to industry benchmarks. Since property, plant, and equipment made up 97.13% of LGI’s assets and inc
the increase in total liabilities, it made sense for LGI to sell some of its plant assets and acquire new equipment to
based on forecasts, the capital budgeting analysis provides numerous opportunities for the project to be profitable. LG
it increases in 2024, but by then, the project will be almost finished.
base for the budget and
et and forecast.
illions)
FORECAST
2026
$2,354.93
$1,560.47
794
$139.33
655
$293.51
362
$188.87
173
$57.79
115
etter financial position?
at has improved their financial situation compared to the
abilities, and much lower net income than competitors in
3% of LGI’s assets and increased by 7.99% in tandem with
acquire new equipment to raise productivity. Regretfully,
project to be profitable. LGI’s net income will decrease until
be almost finished.

Purchase answer to see full
attachment