I need assistance with the attached formulation. Please just type cell reference in a separate solution document. Thanks kindly.

Problem 28-9 Your company has earnings per share of $4. It has 1 million shares outstanding, each of whi price of $40. You are thinking of buying TargetCo, which has earnings per share of $2, 1 mi shares outstanding, and a price per share of $25. You will pay for TargetCo by issuing new s There are no expected synergies from the transaction. a. If you pay no premium to buy TargetCo, what will your earnings per share be after th b. If you pay a 20% premium to buy TargetCo, what will your earnings per share be afte merger? c. What explains the change in earnings per share in part (a)? Are your shareholders any worse off? d. What will your price-earnings ratio be after the merger (if you pay no premium)? How this compare to your P/E ratio before the merger? How does this compare to TargetCo merger P/E ratio? Your Company: Earnings per share Shares outstanding Price per share $4.00 1,000,000 $40.00 Target: Earnings per share Shares outstanding Price per share $2.00 1,000,000 $25.00 Total consolidated earnings a. If you pay no premium to buy TargetCo, what will your earnings per share be after th Value of target company Shares of acquirer issued Total shares outstanding New EPS b. If you pay a 20% premium to buy TargetCo, what will your earnings per share be afte merger? Premium 20% Purchase price Value of target company Shares of acquirer issued Total shares outstanding New EPS c. What explains the change in earnings per share in part (a)? Are your shareholders any worse off? Focusing on EPS alone cannot tell you whether they're better or worse off. d. What will your price-earnings ratio be after the merger (if you pay no premium)? How this compare to your P/E ratio before the merger? How does this compare to TargetCo merger P/E ratio? Acquirer's P/E ratio before Acquirer's P/E ratio after Target's P/E ratio before Requirements To calculate the acquirer's new EPS, you need to calculate the total consolidated earnings an 1. of shares outstanding after the merger. Use cell references in all of the following requirem In cell D20, calculate the total consolidated earnings by adding both companies' earnings. (1 2. In cell D24, calculate the total value of the target company. (1 point.) 3. In cell D25, calculate the shares that the acquirer needs to issue. (1 point.) 4. In cell D26, calculate the total number of shares outstanding after the merger. (1 point.) In cell D27, by using cell references, calculate the new EPS. by dividing the total consolidat 5. total number of shares outstanding after the merger. (1 point.) 6. In cell D33, calculate the new price per share of the target company if a premium is paid. (1 7. In cell D34, calculate the new total value of the target company. (1 point.) 8. In cell D35, calculate the shares that the acquirer needs to issue. (1 point.) 9. In cell D36, calculate the total number of shares outstanding after the merger. (1 point.) In cell D37, by using cell references, calculate the new EPS by dividing the total consolidate 10. total number of shares outstanding after the merger. (1 point.) 11. In cell D45, by using cell references, calculate the acquirer's P/E ratio before the merger. (1 12. In cell D46, calculate the acquirer's P/E ratio after the merger. (1 point.) 13. In cell D47, calculate the target company's P/E ratio before the merger. (1 point.) outstanding, each of which has a ngs per share of $2, 1 million argetCo by issuing new shares. ings per share be after the merger? earnings per share be after the Are your shareholders any better or ou pay no premium)? How does this compare to TargetCo's pre- ings per share be after the merger? earnings per share be after the Are your shareholders any better or etter or worse off. ou pay no premium)? How does this compare to TargetCo's pre- consolidated earnings and the total number the following requirements. h companies' earnings. (1 point.) he merger. (1 point.) iding the total consolidated earnings by the y if a premium is paid. (1 point.) he merger. (1 point.) ding the total consolidated earnings by the tio before the merger. (1 point.) ger. (1 point.)