Number 5

WorkbookPJ5.xlsx

Home >Applied Sciences homework help >Number 5

Instructions

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 5% annually
	o Expense increases 2% annually

Cost of Capital

Instructions:
	1	Find the cost of Equity using the Capital Asset Pricing Model (CAPM)
	2	Find the Weighted Average Cost of Equity (WACC)
1
	RF	1.725%	ꞵ	1.12	RM	6.97%	= CAPM		7.60%
--------------------------------------
2
								E		$ 6,373,341,000
								D		$ 539,500,000
								Total Capital (V)		$ 6,912,841,000
								Last Fiscal Year End Interest Expense		$141,000,000
								Tax Rate (TC)		34.000%
		1. Find the weight of equity = E / (E + D).							92.196%
		2. Find the weight of debt = D / (E + D).							7.804%
	Re	3. Find the cost of equity using CAPM.							7.60%
	Rd	4. Find the cost of debt.							26.135%
	WACC	5. Find the weighted average cost of capital.							8.35%

WACC Information from Largo Global a. As of today, Largo Global market capitalization (E) is $6,373,341,000.1 b. Largo Global's book value of debt is $539,500,000.2 c. Cost of Equity = CAPM from question 1 d. Cost of Debt = Last Fiscal Year End Interest Expense3 / Book 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 Book value of debt (D) is calculated as follows: Book Value of Debt = Last 2-Year Avg of Notes Payable + Last 2 Year Avg of Long-Term Debt from Project 1. 3 From Project 1

Payback

Payback Table View
			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.35%
Loan Amount				171.99	million
Loan Interest rate (Prime + 2)				5.25%
			Table 2 - After-tax Cash Flow Table
	(all figures in $ millions)
Year	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	PV	NPV1	IRR	NPV2
Excel function to use :			SLN	IPMT					PV	NPV	IRR	NPV
0								($191.10)				($191.10)
1	$850.0	$840.0	$23.89	$0.00	($13.89)	($3.61)	($1.11)	$14.72	$13.59			$14.03
2	$900.0	$810.0	$23.89	$0.00	$66.11	$17.19	$5.29	$67.52	$57.51			$61.30
3	$990.0	$870.0	$23.89	$0.00	$96.11	$24.99	$7.69	$87.32	$68.64			$75.54
4	$1,005.0	$900.0	$23.89	$0.00	$81.11	$21.09	$6.49	$77.42	$56.17			$63.82
5	$1,200.0	$1,100.0	$23.89	$0.00	$76.11	$19.79	$6.09	$74.12	$49.63			$58.21
6	$1,300.0	$1,150.0	$23.89	$0.00	$126.11	$32.79	$10.09	$107.12	$66.20			$80.16
7	$1,350.0	$1,300.0	$23.89	$0.00	$26.11	$6.79	$2.09	$41.12	$23.45			$29.32
8	$1,320.0	$1,300.0	$23.89	$0.00	($3.89)	($1.01)	($0.31)	$21.32	$11.22			$14.49
								PV	$346.42
NPV1 - calculated NPV including interest expense								NPV		$155.32		$205.77
NPV2 - calculated NPV from Project 4 at the lower discount rate of 4.95%								IRR			19.91%
	Payback Timeline View Example of Actual Cash Flows
		0	1	2	3	4	5	6	7	8
		\|	\|	\|	\|	\|	\|	\|	\|	\|
	Cash Flow	($191.10)	$14.72	$67.52	$87.32	$77.42	$74.12	$107.12	$41.12	$21.32	$299.57
	Cummulative Cash Flow
		($191.10)	($176.38)	($108.86)	($21.54)	$55.89	$130.01	$237.13	$278.25	$299.57
	Payback Period						3	years	3	months
		0	1	2	3	4	5	6	7	8	PV
		\|	\|	\|	\|	\|	\|	\|	\|	\|	($191.10)
	Discounted Cash Flow (DCF)	($191.10)	$13.59	$57.51	$68.64	$56.17	$49.63	$66.20	$23.45	$11.22	$346.42
											$155.32
	Cummulative DCF	($191.10)	($177.51)	($120.00)	($51.36)	$4.81	$54.44	$120.64	$144.09	$155.32
	Payback Period						3	years	11	months
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?				$346.42
	3. What is the NPV using the WACC rate?				$155.32
	4. What is the NPV using the alternative rate?				$205.77
	5. What is the IRR?				19.91%
	6. What is the payback period using the DCF?				3 years 11 months
	7. Should the project be accepted? Why?				The prpject should be accepted as it has a posiive NPV and the pay back period is very short.

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, and recommend 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 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 WACC from Cost of Capital tab 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?

Budget Projections

INSTRUCTIONS:
1). Complete the budget projections for years 2021-2024 using the following information
	Revenue increases 5% annually
	Expense increases 2½% annually
	For Depreciation and Interest expenses assume the Acutal 2020 figure as the base for the budget and forecast.
2). Answer the question below the forecast.
1).	Largo Global Income Statement of December 31, 2020 (millions)
		ACTUAL	BUDGET	FORECAST
		2020	2021	2022	2023	2024
	Sales (net sales)	$2,013	$2,114	$2,219	$2,330	$2,447
	Cost of goods sold	1400	$1,435	$1,471	$1,508	$1,545
	Gross profit	613	679	748	823	901
	Selling, general, and administrative expenses	125	128	131	135	138
	Earnings before Interest, taxes, depreciation, and amortization (EBITDA)	488	551	617	688	763
	Depreciation and amortization	174	174	174	174	174
	Earning before interest and taxes (EBIT) Operating income (loss)	314	377	443	514	589
	Interest expense	141	141	141	141	141
	Earnings before taxes (EBT)	173	236	302	373	448
	Taxes (34%)	59	80	103	127	152
	Net earnings (loss)/Net Income	$ 114	155	199	246	296
2). Based on the changes suggested throughout the 5 projects, is Largo Global in a better financial position?
Yes, LGI is in a better position as there is an allowance for growth and development. Moreover, the organization is expected to increase its revenues across the period.