Question : this is a question and answer I just need it on Excel

No. 1 – Acquisition Analysis

Brau Auto, a national autoparts chain, is considering purchasing a smaller chain, South Georgia Parts (SGP). Brau's analysts project that the merger will result in the following incremental free cash flows, tax shields, and horizon values:

Years 1 2 3 4

Free cash flow $1 $3 $3 $7

Unlevered horizon value 75

Tax shield 1 1 2 3

Horizon value of tax shield 32

Assume that all cash flows occur at the end of the year. SGP is currently financed with 30% debt at a rate of 10%. The acquisition would be made immediately, and if it is undertaken, SGP would retain its current $15 million of debt and issue enough new debt to continue at the 30% target level.

The interest rate would remain the same. SGP's pre-merger beta is 2.0, and its post-merger tax rate would be 34%. The risk-free rate is 8% and the market risk premium is 4%. What is the value of SGP to Brau?

Answer:

(21.11) Value of an acquisition CU Answer: b MEDIUM

rsL = rRF + b(RPM) = 8% + 2.0(4%) = 16%.

rsU = wdrd + wsrs = 0.30(10%) + 0.70(16%) = 14.2%.

Since all of the cash flows are to be discounted at the same rate, we don’t need to separately calculate the

values of the tax shield and unlevered value of operations. We can simply add the tax shields and free cash

flows together each year to input in the financial calculator:

Time line: (In millions)

0 r = 14.2% 1 2 3 4 Years

| | | | |

PV = ? 2 4 5 10

HV = 107

FCF4 = 117

Financial calculator solution: (In millions)

Inputs: CF0 = 0; CF1 = 2; CF2 = 4; CF3 = 5; CF4 = 117; I/YR = 14.2

Output: NPV = $76.96 million = Value of operations.

Value of equity = Value of operations – Value of debt = $76.96 – 15 = $61.96 million.

Some students will calculate separately the value of the tax shield and the unlevered value of operations

and add them together. In that case, the separate calculations are:

Unlevered value of operations:

Inputs: CF0 = 0; CF1 = 1; CF2 = 3; CF3 = 3; CF4 = 75 + 7 = 82; I/YR = 14.2

Output: NPV = $53.40 million = Unlevered value of operations.

Value of tax shields:

Inputs: CF0 = 0; CF1 = 1; CF2 = 1; CF3 = 2; CF4 = 3 + 32 = 35; I/YR = 14.2

Output: NPV = $23.56 million = Value of tax shields.

Value of operations = Value of tax shields + Unlevered value of operations = $53.40 + $23.56 = $76.96 million.

No. 1 – Acquisition Analysis

Brau Auto, a national autoparts chain, is considering purchasing a smaller chain, South Georgia Parts (SGP). Brau's analysts project that the merger will result in the following incremental free cash flows, tax shields, and horizon values:

Years 1 2 3 4

Free cash flow $1 $3 $3 $7

Unlevered horizon value 75

Tax shield 1 1 2 3

Horizon value of tax shield 32

Assume that all cash flows occur at the end of the year. SGP is currently financed with 30% debt at a rate of 10%. The acquisition would be made immediately, and if it is undertaken, SGP would retain its current $15 million of debt and issue enough new debt to continue at the 30% target level.

The interest rate would remain the same. SGP's pre-merger beta is 2.0, and its post-merger tax rate would be 34%. The risk-free rate is 8% and the market risk premium is 4%. What is the value of SGP to Brau?

Answer:

(21.11) Value of an acquisition CU Answer: b MEDIUM

rsL = rRF + b(RPM) = 8% + 2.0(4%) = 16%.

rsU = wdrd + wsrs = 0.30(10%) + 0.70(16%) = 14.2%.

Since all of the cash flows are to be discounted at the same rate, we don’t need to separately calculate the

values of the tax shield and unlevered value of operations. We can simply add the tax shields and free cash

flows together each year to input in the financial calculator:

Time line: (In millions)

0 r = 14.2% 1 2 3 4 Years

| | | | |

PV = ? 2 4 5 10

HV = 107

FCF4 = 117

Financial calculator solution: (In millions)

Inputs: CF0 = 0; CF1 = 2; CF2 = 4; CF3 = 5; CF4 = 117; I/YR = 14.2

Output: NPV = $76.96 million = Value of operations.

Value of equity = Value of operations – Value of debt = $76.96 – 15 = $61.96 million.

Some students will calculate separately the value of the tax shield and the unlevered value of operations

and add them together. In that case, the separate calculations are:

Unlevered value of operations:

Inputs: CF0 = 0; CF1 = 1; CF2 = 3; CF3 = 3; CF4 = 75 + 7 = 82; I/YR = 14.2

Output: NPV = $53.40 million = Unlevered value of operations.

Value of tax shields:

Inputs: CF0 = 0; CF1 = 1; CF2 = 1; CF3 = 2; CF4 = 3 + 32 = 35; I/YR = 14.2

Output: NPV = $23.56 million = Value of tax shields.

Value of operations = Value of tax shields + Unlevered value of operations = $53.40 + $23.56 = $76.96 million.

#### Top Answer

Dear Student, I assessed your homework question, but unfortunately was not able to complete... View the full answer