Homework Help is Here – Start Your Trial Now!
Business
Finance
Please help fill out the attached excel example to answer instructions of problem 13-13! Instead of just whoing the correct answers, can you please include the formula you used to get the answers so I can learn how to do the work! Thank you!

Please help fill out the attached excel example to answer instructions of problem 13-13! Instead of just whoing the correct answers, can you please include the formula you used to get the answers so I can learn how to do the work! Thank you!

Essentials Of Investments
Essentials Of Investments
11th Edition
ISBN:9781260013924
Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Chapter1: Investments: Background And Issues
Section: Chapter Questions
Problem 1PS
See similar textbooks
icon
Related questions
Question

Please help fill out the attached excel example to answer instructions of problem 13-13! Instead of just whoing the correct answers, can you please include the formula you used to get the answers so I can learn how to do the work! Thank you! 

FINANCING ALTERNATIVES The Severn Company plans to raise a net amount of $270 mil- lion to finance new equipment in early 2020. Two alternatives are being considered: Common stock may be sold to net $60 per share, or bonds yielding 9% may be issued. The balance sheet and 13-13 income statement of the Severn Company prior to financing are as follows: The Severn Company: Balance Sheet as of December 31, 2019 (millions of dollars) Current assets $ 900.00 Notes payable $ 255.00 Net fixed assets 450.00 Long-term debt (7%) 697.50 Common stock, $1 par 60.00 Retained earnings 337.50 Total assets $1,350.00 Total liabilities and equity $1,350.00 The Severn Company: Income Statement for Year Ended December 31, 2019 (millions of dollars) Sales $2,475.00 Operating costs 2,103.75 Earnings before interest and taxes (15%) $ 371.25 Interest on short-term debt 10.50 Interest on long-term debt 48.83 Earnings before taxes $ 311.92 Federal-plus-state taxes (25%) 77.98 Net income $233.94 The probability distribution for annual sales is as follows: Probability Annual Sales (millions of dollars) 0.30 $2,250 0.40 2,700 0.30 3,150 Assuming that EBIT equals 15% of sales, calculate earnings per share (EPS) under the debt financ- ing and the stock financing alternatives at each possible sales level. Then calculate expected EPS and Opps under both debt and stock financing alternatives. Also calculate the debt-to-capital ratio and the times-interest-earned (TIE) ratio at the expected sales level under each alternative. The old debt will remain outstanding. Which financing method do you recommend? (Hint: Notes payable should be included in both the numerator and the denominator of the debt-to-capital ratio.)
Transcribed Image Text:FINANCING ALTERNATIVES The Severn Company plans to raise a net amount of $270 mil- lion to finance new equipment in early 2020. Two alternatives are being considered: Common stock may be sold to net $60 per share, or bonds yielding 9% may be issued. The balance sheet and 13-13 income statement of the Severn Company prior to financing are as follows: The Severn Company: Balance Sheet as of December 31, 2019 (millions of dollars) Current assets $ 900.00 Notes payable $ 255.00 Net fixed assets 450.00 Long-term debt (7%) 697.50 Common stock, $1 par 60.00 Retained earnings 337.50 Total assets $1,350.00 Total liabilities and equity $1,350.00 The Severn Company: Income Statement for Year Ended December 31, 2019 (millions of dollars) Sales $2,475.00 Operating costs 2,103.75 Earnings before interest and taxes (15%) $ 371.25 Interest on short-term debt 10.50 Interest on long-term debt 48.83 Earnings before taxes $ 311.92 Federal-plus-state taxes (25%) 77.98 Net income $233.94 The probability distribution for annual sales is as follows: Probability Annual Sales (millions of dollars) 0.30 $2,250 0.40 2,700 0.30 3,150 Assuming that EBIT equals 15% of sales, calculate earnings per share (EPS) under the debt financ- ing and the stock financing alternatives at each possible sales level. Then calculate expected EPS and Opps under both debt and stock financing alternatives. Also calculate the debt-to-capital ratio and the times-interest-earned (TIE) ratio at the expected sales level under each alternative. The old debt will remain outstanding. Which financing method do you recommend? (Hint: Notes payable should be included in both the numerator and the denominator of the debt-to-capital ratio.)
Net amount to be raised, millions USD 270 Common stock to be sold to net, per share 60.00 Par value of shares 1.00 60.00 64.50 Number of shares outstanding in millions when using debt Number of shares outstanding, with new issue Proposed bond yield EBIT as percentage of sales 0.0900 0.1500 Тахes 0.2500 Interest on long-term debt 0.0700 Interest on short-term debt 0.0412 Exponent "2" 2 Cell for "1" 1 Cell for "-1" -1 Probability distribution for annual sales Annual sales in Probability Use of Debt Use of Equity millions USD 0. 2,250 Probability 0.4 2,700 Sales 0.3 3,150 EBIT Interest EBT Balance Sheet as of December 31, 2019 Taxes (millions of dollars US) Net income Current assets 900.00 EPS Net fixed assets 450.00 TIE Total assets 1,350.00 Notes payable Long-term debt Common stock, $1.00 par Retained earnings Total liabilities and equity 255.00 Expected EPS 697.50 STDEV 60.00 CV 337.50 1,350.00 Еxpected TIE STDEV CV Income Statement Year Ended December 31, 2019 Total debt (millions of dollars US) Total capital Sales 2,475.00 Debt-to-capital Operating costs Earnings before interest and taxes 2,103.75 371.25 Interest on short-term debt 10.50 Interest on long-term debt 48 83 Earnings before taxes Federal-plus-state taxes 311.93 77.98 233.94 Net income Previous debt-to-capital Total debt Total capital Debt-to-capital
Transcribed Image Text:Net amount to be raised, millions USD 270 Common stock to be sold to net, per share 60.00 Par value of shares 1.00 60.00 64.50 Number of shares outstanding in millions when using debt Number of shares outstanding, with new issue Proposed bond yield EBIT as percentage of sales 0.0900 0.1500 Тахes 0.2500 Interest on long-term debt 0.0700 Interest on short-term debt 0.0412 Exponent "2" 2 Cell for "1" 1 Cell for "-1" -1 Probability distribution for annual sales Annual sales in Probability Use of Debt Use of Equity millions USD 0. 2,250 Probability 0.4 2,700 Sales 0.3 3,150 EBIT Interest EBT Balance Sheet as of December 31, 2019 Taxes (millions of dollars US) Net income Current assets 900.00 EPS Net fixed assets 450.00 TIE Total assets 1,350.00 Notes payable Long-term debt Common stock, $1.00 par Retained earnings Total liabilities and equity 255.00 Expected EPS 697.50 STDEV 60.00 CV 337.50 1,350.00 Еxpected TIE STDEV CV Income Statement Year Ended December 31, 2019 Total debt (millions of dollars US) Total capital Sales 2,475.00 Debt-to-capital Operating costs Earnings before interest and taxes 2,103.75 371.25 Interest on short-term debt 10.50 Interest on long-term debt 48 83 Earnings before taxes Federal-plus-state taxes 311.93 77.98 233.94 Net income Previous debt-to-capital Total debt Total capital Debt-to-capital
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps with 4 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
Techniques of Time Value Of Money
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, finance and related others by exploring similar questions and additional content below.
Similar questions
Recommended textbooks for you
Essentials Of Investments
Essentials Of Investments
Finance
ISBN:
9781260013924
Author:
Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:
Mcgraw-hill Education,
FUNDAMENTALS OF CORPORATE FINANCE
FUNDAMENTALS OF CORPORATE FINANCE
Finance
ISBN:
9781260013962
Author:
BREALEY
Publisher:
RENT MCG
Financial Management: Theory & Practice
Financial Management: Theory & Practice
Finance
ISBN:
9781337909730
Author:
Brigham
Publisher:
Cengage
Foundations Of Finance
Foundations Of Finance
Finance
ISBN:
9780134897264
Author:
KEOWN, Arthur J., Martin, John D., PETTY, J. William
Publisher:
Pearson,
Fundamentals of Financial Management (MindTap Cou…
Fundamentals of Financial Management (MindTap Cou…
Finance
ISBN:
9781337395250
Author:
Eugene F. Brigham, Joel F. Houston
Publisher:
Cengage Learning
Corporate Finance (The Mcgraw-hill/Irwin Series i…
Corporate Finance (The Mcgraw-hill/Irwin Series i…
Finance
ISBN:
9780077861759
Author:
Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan Professor
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS