A colleague is working on valuing a potential merger between two firms and has sent you their (very simple) model in an Excel file, which is attached to this post. However, they accidentally deleted some of the cells before sending the file and now cannot recover the original. Luckily, all the cells that were deleted are colored in green. Use your knowledge of mergers and Excel to complete the spreadsheet, which uses a technique called Adjusted Present Value (APV) to value the operating cash flows and the interest tax shields for the acquirer, the target, and the resulting merged firm. All the numbers you need to assume are included and styled as Input cells.

\begin{tabular}{|c|c|c|c|c|} \hline & Acquirer & Target & Adj & Merged \\ \hline Assets & & & & \\ \hline Current Assets & 95 & 40 & & 135 \\ \hline PPE & 800 & 300 & & 1100 \\ \hline Goodwill & 0 & 0 & & 0 \\ \hline TA & 895 & 340 & 0 & 1235 \\ \hline Liabilities \& Equity & & & & \\ \hline Current Liabilities & 80 & 30 & & 110 \\ \hline Bonds & 450 & 100 & & 550 \\ \hline Equity & 365 & 210 & & 575 \\ \hline TL+TE & 895 & 340 & & 1235 \\ \hline & & & & \\ \hline Revenue & $200.00 & $90.00 & & $290.00 \\ \hline Operating Costs & $145.00 & $75.00 & & $220.00 \\ \hline% of Revenue & 72.50% & 83.33% & & 75.86% \\ \hline Interest & $22.50 & $6.00 & & $27.50 \\ \hline Interest rate & 5.00% & 6.00% & & 5.00% \\ \hline EBT & $32.50 & $9.00 & & $42.50 \\ \hline Taxes & $7.48 & $1.62 & & $8.71 \\ \hline Tax rate & 23.00% & 18.00% & & 20.50% \\ \hline NI & $25.03 & $7.38 & & $33.79 \\ \hline & & & & \\ \hline Market to Book ratio & 4.00 & 3.50 & & 0.00 \\ \hline Market value & & & & \\ \hline & & & & \\ \hline Purchase premium & & & & 15% \\ \hline Purchase price & & & & 0 \\ \hline New equity issue & & & & -300 \\ \hline New debt issue & & & & 300 \\ \hline Marketing Improvement & & & & 30 \\ \hline Operating cost savings & & & & 10 \\ \hline \end{tabular} \begin{tabular}{|l|l|r|r|r|} \hline 1 & \multicolumn{1}{c}{ A } & \multicolumn{1}{c}{ B } & \multicolumn{1}{c|}{ D } \\ \hline 1 & Acquirer & Target & Merged \\ \hline 2 & Market return & 10% & 10% & 10% \\ \hline 3 & Risk-free rate & 3% & 3% & 3% \\ \hline 4 & Beta & 0.90 & 1.20 & 1.00 \\ \hline 5 & Cost of equity (levered) & & & \\ \hline 6 & Cost of debt & & & \\ \hline 7 & Weight on equity & & & \\ \hline 8 & Weight on debt & & & \\ \hline 9 & Unlevered cost of equity & & & \\ \hline 10 & & & & \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline & Firm & Acquirer & Target & Merged & \multicolumn{2}{|c|}{ Growth rate } & 1.50% & \multicolumn{3}{|c|}{ Deal Value } & & & \\ \hline 2 & NI & $25.03 & $7.38 & $33.79 & & & & & & & & & \\ \hline & FCF & $47.53 & $13.38 & $61.29 & & & & & & & & & \\ \hline 4 & Discount rate & 0.00% & 0.00% & 0.00% & & & & & & & & & \\ \hline 5 & & & & & & & & & & & & & \\ \hline 6 & & \multicolumn{3}{|c|}{ FCF } & \multicolumn{3}{|c|}{ PV of FCF } & \multicolumn{3}{|c|}{ Interest Tax Shield } & \multicolumn{3}{|c|}{ PV of Tax Shield } \\ \hline 7 & Year & Acquirer & Target & Merged & Acquirer & Target & Merged & Acquirer & Target & Merged & Acquirer & Target & Merged \\ \hline 8 & 0 & $47.53 & $13.38 & $61.29 & & & & $5.18 & $1.08 & $5.64 & & & \\ \hline 9 & 1 & $48.24 & $13.58 & $62.21 & $48.24 & $13.58 & $62.21 & $5.25 & $1.10 & $5.72 & $5.25 & $1.10 & $5.72 \\ \hline 0 & 2 & $48.96 & $13.78 & $63.14 & $48.96 & $13.78 & $63.14 & $5.33 & $1.11 & $5.81 & $5.33 & $1.11 & $5.81 \\ \hline 1 & 3 & $49.70 & $13.99 & $64.09 & $49.70 & $13.99 & $64.09 & $5.41 & $1.13 & $5.90 & $5.41 & $1.13 & $5.90 \\ \hline 2 & 4 & $50.44 & $14.20 & $65.05 & $50.44 & $14.20 & $65.05 & $5.49 & $1.15 & $5.98 & $5.49 & $1.15 & $5.98 \\ \hline 3 & 5 & $51.20 & $14.41 & $66.02 & $51.20 & $14.41 & $66.02 & $5.57 & $1.16 & $6.07 & $5.57 & $1.16 & $6.07 \\ \hline & Continuing Value & & & & & & & & & & & & \\ \hline & & & & Total & & & & & & Total & & & \\ \hline & & & & & & & & & & & & & \\ \hline 17 & & & & & & & & & & Firm Value & & & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|} \hline & Acquirer & Target & Adj & Merged \\ \hline Assets & & & & \\ \hline Current Assets & 95 & 40 & & 135 \\ \hline PPE & 800 & 300 & & 1100 \\ \hline Goodwill & 0 & 0 & & 0 \\ \hline TA & 895 & 340 & 0 & 1235 \\ \hline Liabilities \& Equity & & & & \\ \hline Current Liabilities & 80 & 30 & & 110 \\ \hline Bonds & 450 & 100 & & 550 \\ \hline Equity & 365 & 210 & & 575 \\ \hline TL+TE & 895 & 340 & & 1235 \\ \hline & & & & \\ \hline Revenue & $200.00 & $90.00 & & $290.00 \\ \hline Operating Costs & $145.00 & $75.00 & & $220.00 \\ \hline% of Revenue & 72.50% & 83.33% & & 75.86% \\ \hline Interest & $22.50 & $6.00 & & $27.50 \\ \hline Interest rate & 5.00% & 6.00% & & 5.00% \\ \hline EBT & $32.50 & $9.00 & & $42.50 \\ \hline Taxes & $7.48 & $1.62 & & $8.71 \\ \hline Tax rate & 23.00% & 18.00% & & 20.50% \\ \hline NI & $25.03 & $7.38 & & $33.79 \\ \hline & & & & \\ \hline Market to Book ratio & 4.00 & 3.50 & & 0.00 \\ \hline Market value & & & & \\ \hline & & & & \\ \hline Purchase premium & & & & 15% \\ \hline Purchase price & & & & 0 \\ \hline New equity issue & & & & -300 \\ \hline New debt issue & & & & 300 \\ \hline Marketing Improvement & & & & 30 \\ \hline Operating cost savings & & & & 10 \\ \hline \end{tabular} \begin{tabular}{|l|l|r|r|r|} \hline 1 & \multicolumn{1}{c}{ A } & \multicolumn{1}{c}{ B } & \multicolumn{1}{c|}{ D } \\ \hline 1 & Acquirer & Target & Merged \\ \hline 2 & Market return & 10% & 10% & 10% \\ \hline 3 & Risk-free rate & 3% & 3% & 3% \\ \hline 4 & Beta & 0.90 & 1.20 & 1.00 \\ \hline 5 & Cost of equity (levered) & & & \\ \hline 6 & Cost of debt & & & \\ \hline 7 & Weight on equity & & & \\ \hline 8 & Weight on debt & & & \\ \hline 9 & Unlevered cost of equity & & & \\ \hline 10 & & & & \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline & Firm & Acquirer & Target & Merged & \multicolumn{2}{|c|}{ Growth rate } & 1.50% & \multicolumn{3}{|c|}{ Deal Value } & & & \\ \hline 2 & NI & $25.03 & $7.38 & $33.79 & & & & & & & & & \\ \hline & FCF & $47.53 & $13.38 & $61.29 & & & & & & & & & \\ \hline 4 & Discount rate & 0.00% & 0.00% & 0.00% & & & & & & & & & \\ \hline 5 & & & & & & & & & & & & & \\ \hline 6 & & \multicolumn{3}{|c|}{ FCF } & \multicolumn{3}{|c|}{ PV of FCF } & \multicolumn{3}{|c|}{ Interest Tax Shield } & \multicolumn{3}{|c|}{ PV of Tax Shield } \\ \hline 7 & Year & Acquirer & Target & Merged & Acquirer & Target & Merged & Acquirer & Target & Merged & Acquirer & Target & Merged \\ \hline 8 & 0 & $47.53 & $13.38 & $61.29 & & & & $5.18 & $1.08 & $5.64 & & & \\ \hline 9 & 1 & $48.24 & $13.58 & $62.21 & $48.24 & $13.58 & $62.21 & $5.25 & $1.10 & $5.72 & $5.25 & $1.10 & $5.72 \\ \hline 0 & 2 & $48.96 & $13.78 & $63.14 & $48.96 & $13.78 & $63.14 & $5.33 & $1.11 & $5.81 & $5.33 & $1.11 & $5.81 \\ \hline 1 & 3 & $49.70 & $13.99 & $64.09 & $49.70 & $13.99 & $64.09 & $5.41 & $1.13 & $5.90 & $5.41 & $1.13 & $5.90 \\ \hline 2 & 4 & $50.44 & $14.20 & $65.05 & $50.44 & $14.20 & $65.05 & $5.49 & $1.15 & $5.98 & $5.49 & $1.15 & $5.98 \\ \hline 3 & 5 & $51.20 & $14.41 & $66.02 & $51.20 & $14.41 & $66.02 & $5.57 & $1.16 & $6.07 & $5.57 & $1.16 & $6.07 \\ \hline & Continuing Value & & & & & & & & & & & & \\ \hline & & & & Total & & & & & & Total & & & \\ \hline & & & & & & & & & & & & & \\ \hline 17 & & & & & & & & & & Firm Value & & & \\ \hline \end{tabular}