Start with the partial model in the file Ch22 P07 Build a Model.xlsx on the textbook sWeb site. Wansley Portal Inc., a large Internet service provider, is evaluating the possibleacquisition of Alabama Connections Company (ACC), a regional Internet service provi-der. Wansley s analysts project the following post-merger data for ACC (in thousandsof dollars): 2017 2018 2019 2020 2021 Net sales $500 $600 $700 $760 $806Selling and administrative expense 60 70 80 90 96Interest 30 40 45 60 74If the acquisition is made, it will occur on January 1, 2017. All cash flows shown in theincome statements are assumed to occur at the end of the year. ACC currently has acapital structure of 30% debt, which costs 9%, but Wansley would increase that over timeto 40%, costing 10%, if the acquisition were made. ACC, if independent, would pay taxesat 30%, but its income would be taxed at 35% if it were consolidated. ACC s currentmarket-determined beta is 1.4. The cost of goods sold, which includes depreciation, isexpected to be 65% of sales, but it could vary somewhat. Required gross investment in operating capital is approximately equal to the depreciation charged, so there will be noinvestment in net operating capital. The risk-free rate is 7%, and the market risk premiumis 6.5%. Wansley currently has $400,000 in debt outstanding. Use the compressed APVmodel to answer the following questions

.a. What is the unlevered cost of equity?

b. What are the horizon value of the tax shields and the horizon value of the unleveredoperations? What are the value of ACC s operations and the value

Here is the excel model provided:

a. What is the unlevered cost of equity?
The unlevered cost of equity should be used to discount the FCFs, tax shields and horizon value.
Step 1: Find the levered cost of equity at old capital structure.
rL=
Step 2: Find the unlevered cost of equity.
rU=
b. What is the horizon value of the tax shields and the unlevered operations? What is the value of ACCs operations and the value of ACCs equity to Wansleys shareholders?
Before we can proceed with this problem, we must generate pro forma income statements for ACC's operations after the proposed merger so we can calculate free cash flow and interest tax shields.
			2017	2018	2019	2020	2021
Sales
Cost of Goods Sold (incl. depreciation)
Gross Profit
Selling/admin. costs
EBIT
Interest
EBT
Taxes
Net Income
EBIT
NOPAT
Investment in net operating capital
FCF
* In this scenario, we state that investment in net operating capital is zero. This arises from the fact that the only needed investments are those needed to replace worn out capital, and that they equal depreciation.
We must determine the tax shields.
From this point, we can derive horizon value from the basic DCF framework.
The tax shield is the interest multiplied by the post-merger tax rate.
			2017	2018	2019	2020	2021
Interest			0.0	0.0	0.0	0.0	0.0
Tax shield
HVTS 2020	=	TS2021	*	(1+g)	/	(rsU	-	g)
HVTS 2020	=		*		/		-
HVTS 2020	=		/
HVTS 2020	=
To calculate the value of the tax shields add the horizon value of the tax shields to the 2021 tax shield
to get the total tax shield cash flow in 2021. In the other years the total TS cash flow is just the annual TS
Then find the NPV of this stream of tax shields at the unlevered cost of equity.
			2017	2018	2019	2020	2021
Total TS Cash Flows
NPV of TS Cash Flows				This is the value of all of the tax shields.
To calculate the unlevered value of operations you need the unlevered horizon value and the
the annual free cash flows.
To calculate the unlevered horizon value, we just need the free cash flow for 2021
HVUL 2021	=	FCF2021	*	(1+g)	/	(rsU	-	g)
HVUL 2021	=		*		/		-
HVUL 2021	=		/
HVUL 2021	=
To calculate the unlevered value of operations, add the unlevered horizon value to the free cash flow
in 2021 to get the total unlevered cash flow in 2021. In the other years the unlevered cash flow is
just the annual free cash flow. The unlevered value of operations is the NPV of the unlevered
cash flows at the unlevered cost of equity.
Year			2017	2018	2019	2020	2021
Total unlevered CFs
NPV of unlevered CFs				This is the unlevered value of operations
The value of operations is the value of the interest tax shields plus the unlevered value of operations
		VTS	+	Vunlevered
Vops	=		+
Vops	=
To find the value of ACC to Wansley's shareholders take the value of operations, add in any non-operating assets (there are non for ACC) and subtract off the debt.
Vops	=
Debt	=
Equity	=