please see updated post. I need help with Merger valuation with change in capital structure, in excel. I have done part of the assignment. upadated photos attached.

x Excel template File Review View Help Tell me what you want to do - 10-A Home Insert Formulas Data X Cut Arial Copy Paste Format Painter I U A 23 Wrap Text Percent D BO-A E 3 Morge & Conter $- Undo Clipboard Font Alignment B31 B D 1 Merger Valuation with Change in Capital Structure 2 3 Current target capital structure: 4 Debt Equity Number of common shares outstanding Current debt amount 8 9 Debt interest rate 10 Risk-free rate 11 Market risk premium 12 Tax rate 30.00% 70.00% 1,000,000 $11,870,000 7 13 Beta 7.30% 4.00% 4.00% 40.00% 1.30 $1,600,000 4.00% $2,300,000 $2,900,000 $3,300,000 $3,820.000 $34.300,000 8.00% 45.00% 56.00% Formulas 9.20% -810+(B13"B11) 14 Interest payments, Years 1 - 3 15 Growth rate 16 Free cash flow, Year 1 17 Free cash flow. Year 2 18 Free cash flow. Year 3 19 Free cash flow, Year 4 20 21 Level of debt. Year 3 22 New interest rate at higher debt level 23 New target capital structure: 24 Debt 25 Equity 26 27 Calculate target firm's levered cost of equly 28 29 30 Calculate target firm's unlovered cost of equity 31 32 33 Calculate target firm's unlevered value 34 Unlevered hormon value of FCF 35 Unlevered value of operations 36 37 Calculate value of interest tax shields: 3B Tax shield. Year 1 39 Tax Shield Year 2 40 Tax shiold, Year 3 Tax shield, Year 4 42 Taxshiold, Horizon value 43 44 Value of tax shields ANA HNA $640.000 B14-B12 $640,000 -814'B12 $640,000 =B14'B12 SO -B15'312 SO =(B361.041826-816) $0 -B331+B26)+B34/(1+B2672B36(1+B26Y3+(B36+B37)(1+B2674 46 Calculate target firm's per share value to acquiring firm: Sheet1 0 Copy Paste BI UB Format Painter ab A. Merge & Center st Undo Clipboard Font Alignment B31 D B 70.00% 1,000,000 $11,870,000 A 5 Equity 6 Number of common shares outstanding 7 Current debt amount 8 9 Debt interest rate 10 Risk-free rate 11 Market risk premium 12 Tax rate 13 Beta 7.30% 4.00% 4.00% 40.00% 1.30 $1,600,000 4.00% $2,300,000 $2,900,000 $3,300,000 $3,820,000 $34,300,000 8.00% 45.00% 55.00% Formulas 9.20% =B10+(B13-B11) #N/A 14 Interest payments, Years 1.3 15 Growth rate 16 Free cash flow, Year 1 17 Free cash flow. Year 2 18 Free cash flow. Year 3 19 Free cash flow. Year 4 20 21 Level of debt. Year 3 22 New interest rate at higher debt level 23 New target capital structure: 24 Debt 25 Equity 26 27 Calculate target firm's lovered cost of equity 28 TL 29 30 Calculate target firm's unlevered cost of equity 31 32 33 Calculato target firm's unlevered value: 34 Unlevered horizon value of FCF 35 Unlevered value of operations 36 37 Calculate value of interest tax shields: 38 Tax shield, Year 1 39 Tax shield, Year 2 40 Tax shield, Year 3 41 Tax shield, Year 4 42 Tax shield, Horizon value 43 44 Value of tax shields 45 46 Calculate target firm's per share value to acquiring firm: 47 Value of operations 48 Target firm's equity value to acquiring firm 49 Per share value to acquiring firm 50 #N/A #N/A $640,000 =B14'B12 $640,000 -B14'B12 $640,000 -B14"B12 $0 =B15'812 $0 =(B36*1.047(B26-B16) $0 =B33/(1+B26)+B34/(1+B26Y2+B35/(1+B26y^3+(B36+837/(1+B2674 #N/A ANA #N/A Sheet1 x Excel template File Review View Help Tell me what you want to do - 10-A Home Insert Formulas Data X Cut Arial Copy Paste Format Painter I U A 23 Wrap Text Percent D BO-A E 3 Morge & Conter $- Undo Clipboard Font Alignment B31 B D 1 Merger Valuation with Change in Capital Structure 2 3 Current target capital structure: 4 Debt Equity Number of common shares outstanding Current debt amount 8 9 Debt interest rate 10 Risk-free rate 11 Market risk premium 12 Tax rate 30.00% 70.00% 1,000,000 $11,870,000 7 13 Beta 7.30% 4.00% 4.00% 40.00% 1.30 $1,600,000 4.00% $2,300,000 $2,900,000 $3,300,000 $3,820.000 $34.300,000 8.00% 45.00% 56.00% Formulas 9.20% -810+(B13"B11) 14 Interest payments, Years 1 - 3 15 Growth rate 16 Free cash flow, Year 1 17 Free cash flow. Year 2 18 Free cash flow. Year 3 19 Free cash flow, Year 4 20 21 Level of debt. Year 3 22 New interest rate at higher debt level 23 New target capital structure: 24 Debt 25 Equity 26 27 Calculate target firm's levered cost of equly 28 29 30 Calculate target firm's unlovered cost of equity 31 32 33 Calculate target firm's unlevered value 34 Unlevered hormon value of FCF 35 Unlevered value of operations 36 37 Calculate value of interest tax shields: 3B Tax shield. Year 1 39 Tax Shield Year 2 40 Tax shiold, Year 3 Tax shield, Year 4 42 Taxshiold, Horizon value 43 44 Value of tax shields ANA HNA $640.000 B14-B12 $640,000 -814'B12 $640,000 =B14'B12 SO -B15'312 SO =(B361.041826-816) $0 -B331+B26)+B34/(1+B2672B36(1+B26Y3+(B36+B37)(1+B2674 46 Calculate target firm's per share value to acquiring firm: Sheet1 0 Copy Paste BI UB Format Painter ab A. Merge & Center st Undo Clipboard Font Alignment B31 D B 70.00% 1,000,000 $11,870,000 A 5 Equity 6 Number of common shares outstanding 7 Current debt amount 8 9 Debt interest rate 10 Risk-free rate 11 Market risk premium 12 Tax rate 13 Beta 7.30% 4.00% 4.00% 40.00% 1.30 $1,600,000 4.00% $2,300,000 $2,900,000 $3,300,000 $3,820,000 $34,300,000 8.00% 45.00% 55.00% Formulas 9.20% =B10+(B13-B11) #N/A 14 Interest payments, Years 1.3 15 Growth rate 16 Free cash flow, Year 1 17 Free cash flow. Year 2 18 Free cash flow. Year 3 19 Free cash flow. Year 4 20 21 Level of debt. Year 3 22 New interest rate at higher debt level 23 New target capital structure: 24 Debt 25 Equity 26 27 Calculate target firm's lovered cost of equity 28 TL 29 30 Calculate target firm's unlevered cost of equity 31 32 33 Calculato target firm's unlevered value: 34 Unlevered horizon value of FCF 35 Unlevered value of operations 36 37 Calculate value of interest tax shields: 38 Tax shield, Year 1 39 Tax shield, Year 2 40 Tax shield, Year 3 41 Tax shield, Year 4 42 Tax shield, Horizon value 43 44 Value of tax shields 45 46 Calculate target firm's per share value to acquiring firm: 47 Value of operations 48 Target firm's equity value to acquiring firm 49 Per share value to acquiring firm 50 #N/A #N/A $640,000 =B14'B12 $640,000 -B14'B12 $640,000 -B14"B12 $0 =B15'812 $0 =(B36*1.047(B26-B16) $0 =B33/(1+B26)+B34/(1+B26Y2+B35/(1+B26y^3+(B36+837/(1+B2674 #N/A ANA #N/A Sheet1