70.2K

Verified Solution

Link Copied!

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.

Please explain your steps, so I can follow :)

\begin{tabular}{|c|c|c|c|c|} \hline & Acquirer & Target & Adj & Merged \\ \hline \multicolumn{5}{|l|}{ Assets } \\ \hline Current Assets & 95 & 40 & & 135 \\ \hline PPE & 800 & 300 & & 1100 \\ \hline Goodwill & 0 & 0 & & 0 \\ \hline TA & 895 & 340 & 0 & 1235 \\ \hline \multicolumn{5}{|l|}{ Liabilities \& Equity } \\ \hline Current Liabilities & 80 & 30 & & 110 \\ \hline Bonds & 450 & 100 & & 550 \\ \hline Equity & 365 & 210 & & 575 \\ \hlineTL+TE & 895 & 340 & & 1235 \\ \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 Market to Book ratio & 4.00 & 3.50 & & 0.00 \\ \hline \multicolumn{5}{|l|}{ Market value } \\ \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}

Answer & Explanation Solved by verified expert

See Answer