answer using excel functions when applicable

4) (10 points) Assume for this question that NCC plans to finance $30 million of the acquisition by issuing 20-year maturity, $1,000-par-value convertible bonds. The bonds would have an annual coupon rate of 5% and be convertible to common stock at $62.50. (Show all work/calculations.) A. How many bonds would NCC need to issue? B. What would the net proceeds of the issue be if underwriting/flotation costs are 2% ? C. What would the straight value of each bond be in the market after issue if similar risk bonds are yielding 4.5% ? D. What is the conversion ratio of each bond? E. Based on NCC's pre-merger stock price, what is the initial conversion (or stock) value of each bond? F. What would the conversion (or stock) value be if NCC's stock eventually rose to $68, its CFO's ambitious forecasted stock price? G. At what point would cautious investors in the bonds generally be interested in converting to stock (ignore transaction costs)? H. What are two advantages of convertible bonds (over straight bonds) to the issuing company? pertinent financial data is included in the tables below. The applicable corporate tax rate is 21%. \begin{tabular}{|l|c|l|r|} \hline \multicolumn{4}{|c|}{ Table 3: Portable Coffee Express Balance Sheet (December 31, 2020) } \\ \hline Assets & \multicolumn{2}{|l|}{ Liabilities \& Equity } \\ \hline Cash & $3,000,000 & Current liabilities & $3,600,000 \\ \hline Accounts Receivable & 2,000,000 & Mortgages payable & 5,900,000 \\ \hline Inventory & 7,000,000 & Common stock & 14,800,000 \\ \hline Land & 7,250,000 & Retained Earnings & 9,700,000 \\ \hline Fixed assets (net) & 14,750,000 & Total Liabilities \& Equity & $34,000,000 \\ \hline Total Assets & $34,000,000 & & \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline \multicolumn{3}{|l|}{ Table 4: NCC and Portable additional data (December 31, 2020) } \\ \hline Item & NCC & Portable \\ \hline \begin{tabular}{l} Earnings available for \\ common stock \end{tabular} & $29,000,000 & $12,580,000 \\ \hline \begin{tabular}{l} Number of shares of \\ Common stock \end{tabular} & 7,000,000 & 3,400,000 \\ \hline \begin{tabular}{l} Market price per share \\ ( Estimated by NCC) \end{tabular} & $43.30 & $19 \\ \hline \end{tabular} \begin{tabular}{|r|r|r|} \hline 4 & \multicolumn{1}{|c|}{ A } & C \\ \hline 1 & 30,000,000 & Bond Issue \\ \hline 2 & 1,000 par \\ \hline 3 & 20 term \\ \hline 4 & 5% coupon rate \\ \hline 6 & 4.50% rate of similar risk bonds \\ \hline 7 & 2% flotation costs \\ \hline 8 & 62.50 common stock conversion price \\ \hline 9 & 43.30 & NCC pre-merger stock price \\ \hline 10 & 68 NCC aspirational stock price \\ \hline 11 & \\ \hline 12 & \\ \hline 13 & Part A \\ \hline 14 & \\ \hline 15 & Part B \\ \hline 16 & \\ \hline 17 & Part C \\ \hline 18 & \\ \hline 19 & Part D \\ \hline 20 & \\ \hline 21 & Part E \\ \hline 22 & \\ \hline 23 & Part F \\ \hline 24 & \\ \hline 28 & Part G \\ \hline 29 & \\ \hline \end{tabular}