Question
In: Accounting
chapter 14 Create an Excel spreadsheet to organize your answers to the following problem, and submit...
chapter 14
Create an Excel spreadsheet to organize your answers to the
following problem, and submit your Excel file as an attachment by
clicking on the appropriate button on this page.
A company with EBIT of $6,000,000 is considering two financing
alternatives. The first alternative would have $25 million of bonds
at 8% interest and 1,000,000 common shares outstanding, whereas the
second would have $47.5 million of bonds at 8% interest and only
750,000 shares outstanding. The company is in the 35% tax
bracket.
Part 1: Construct the bottom half of the income statement
(including EPS) for each financing alternative when EBIT is at
$6,000,000.
Part 2: Construct the bottom half of the income statement
(including EPS) for each alternative if EBIT increases by
20%.
Part 3: Construct the bottom half of the income statement
(including EPS) for each alternative if EBIT decreases by
20%.
Part 4: What is the EBIT/EPS indifference point for this
firm?
Part 5: Comment on the results of your calculations above.
What principle does this exercise demonstrate?
Solutions
Expert Solution
| Part 1 | |||
| a | Statement of profit and loss | ||
| Amount ($) | |||
| Earnings before interest and tax | 6,000,000 | ||
| Finance costs | 2,000,000 | ||
| Earnings before taxes | 4,000,000 | ||
| Tax expenses | 1,400,000 | ||
| Earnings after tax | 2,600,000 | ||
| Earnings per share- | |||
| Shares outstanding | 1,000,000 | ||
| Basic EPS | 2.60 | ||
| b | Statement of profit and loss | ||
| Amount ($) | |||
| Earnings before interest and tax | 6,000,000 | ||
| Finance costs | 3,800,000 | ||
| Earnings before taxes | 2,200,000 | ||
| Tax expenses | 770,000 | ||
| Earnings after tax | 1,430,000 | ||
| Earnings per share- | |||
| Shares outstanding | 750,000 | ||
| Basic EPS | 1.91 | ||
| Part 2 | |||
| a | Statement of profit and loss | ||
| Amount ($) | |||
| Earnings before interest and tax | 7,200,000 | ||
| Finance costs | 2,000,000 | ||
| Earnings before taxes | 5,200,000 | ||
| Tax expenses | 1,820,000 | ||
| Earnings after tax | 3,380,000 | ||
| Earnings per share- | |||
| Shares outstanding | 1,000,000 | ||
| Basic EPS | 3.38 | ||
| b | Statement of profit and loss | ||
| Amount ($) | |||
| Earnings before interest and tax | 7,200,000 | ||
| Finance costs | 3,800,000 | ||
| Earnings before taxes | 3,400,000 | ||
| Tax expenses | 1,190,000 | ||
| Earnings after tax | 2,210,000 | ||
| Earnings per share- | |||
| Shares outstanding | 750,000 | ||
| Basic EPS | 2.95 | ||
| Part 3 | |||
| a | Statement of profit and loss | ||
| Amount ($) | |||
| Earnings before interest and tax | 4,800,000 | ||
| Finance costs | 2,000,000 | ||
| Earnings before taxes | 2,800,000 | ||
| Tax expenses | 980,000 | ||
| Earnings after tax | 1,820,000 | ||
| Earnings per share- | |||
| Shares outstanding | 1,000,000 | ||
| Basic EPS | 1.82 | ||
| b | Statement of profit and loss | ||
| Amount ($) | |||
| Earnings before interest and tax | 4,800,000 | ||
| Finance costs | 3,800,000 | ||
| Earnings before taxes | 1,000,000 | ||
| Tax expenses | 350,000 | ||
| Earnings after tax | 650,000 | ||
| Earnings per share- | |||
| Shares outstanding | 750,000 | ||
| Basic EPS | 0.87 | ||
| Part 4. | 4million EBIfT and 100,000 shares in part b is indif | ferent point for EPS. | |
| a | Statement of profit and loss | ||
| Amount ($) | |||
| Earnings before interest and tax | 4,000,000 | ||
| Finance costs | 2,000,000 | ||
| Earnings before taxes | 2,000,000 | ||
| Tax expenses | 700,000 | ||
| Earnings after tax | 1,300,000 | ||
| Earnings per share- | |||
| Shares outstanding | 1,000,000 | ||
| Basic EPS | 1.30 | ||
| b | Statement of profit and loss | ||
| Amount ($) | |||
| Earnings before interest and tax | 4,000,000 | ||
| Finance costs | 3,800,000 | ||
| Earnings before taxes | 200,000 | ||
 ekkarill92 answered 2 years agoRelated SolutionsCreate an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
Create an Excel spreadsheet to organize your answers to the
following problem, and submit your Excel file as an attachment by
clicking on the appropriate button on this page.
A firm that is in the 35% tax bracket forecasts that it can
retain $4 million of new earnings plans to raise new capital in the
following proportions:
60% from 30-year bonds with a flotation cost of 4% of face
value. Their current bonds are selling at a price of 91...
create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
create an Excel spreadsheet to organize your answers to the
following problem, and submit your Excel file as an attachment by
clicking on the appropriate button on this page.
A company is evaluating the purchase of a machine to improve
product quality and output levels. The new machine would cost $1.6
million and would be depreciated for tax purposes using the
straight-line method over an estimated six-year life to its
expected salvage value of $100,000. The new machine would require...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...Create an Excel spreadsheet to organize your answers to
the following problem, and submit your Excel file as an attachment
by clicking on the appropriate button on this page.
A company with EBIT of $6,000,000 is considering two financing
alternatives. The first alternative would have interest expense of
$2,000,000 and 1,000,000 common shares outstanding, whereas the
second would have interest expense of $3,800,000 but only 750,000
shares outstanding. The company is in the 35% tax bracket.
Part 1: Construct the...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...Create an Excel spreadsheet to organize your answers to
the following problem, and submit your Excel file as an attachment
by clicking on the appropriate button on this page.
A firm had the following abbreviated income statement for 2020
and abbreviated balance sheets at the end of 2020 and 2021.
2020
Sales
$600,000
Less Cost of goods sold
320,000
Gross Profit
280,000
Less Operating expenses
190,000
Less Depreciation
30,000
Operating Income (EBIT)
60,000
Less Interest expense
20,000
Earnings Before Taxes...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...Create an Excel spreadsheet to organize your answers to
the following problem, and submit your Excel file as an attachment
by clicking on the appropriate button on this page.
A company with EBIT of $6,000,000 is considering two financing
alternatives. The first alternative would have $25 million of bonds
at 8% interest and 1,000,000 common shares outstanding, whereas the
second would have $47.5 million of bonds at 8% interest and only
750,000 shares outstanding. The company is in the 35%...
Create an Excel spreadsheet to organize your answers to the following problem. A company with EBIT...Create an Excel spreadsheet to organize your answers to
the following problem.
A company with EBIT of $6,000,000 is considering two financing
alternatives. The first alternative would have $25 million of bonds
at 8% interest and 1,000,000 common shares outstanding, whereas the
second would have $47.5 million of bonds at 8% interest and only
750,000 shares outstanding. The company is in the 35% tax
bracket.
Part 1: Construct the bottom half of the income statement
(including EPS) for each financing...
Using your Excel TVM Calculators answer the following questions, be sure to submit your spreadsheet with...Using your Excel TVM Calculators answer the following
questions, be sure to submit your spreadsheet with your answers
highlighted.
At what annual interest rate must $137,000 be invested so that
it will grow to be $475,000 in 10 years?
If you wish to accumulate $175,000 in 13 years, how much must
you deposit today in an account that pays an annual interest rate
of 15%?
How many years will it take for $125,000 to grow to be $500,000
if it...
You need to create an excel spreadsheet that answers the following questions. Where stated, make sure...You need to create an excel spreadsheet that answers the
following questions. Where stated, make sure you solve the problem
“by hand”, i.e. discounting each cash flow, and also using TVM
formulas from Excel. All work should be presented in three
sheets/tabs (not files). Your sheets should be labeled. Questions
1-4 are in the first tab, questions 5-6 are in the second tab, and
question 7 is in a third tab. In all cases, presentation matters!!!
Make these professional. Also,...
a. Use the multiple regression spreadsheet provided in the Excel spreadsheet to do the following: create...a. Use the multiple regression spreadsheet provided in the Excel
spreadsheet to do the following: create a new data set that is a
subset of this larger provided data set. The new data set should
include the dependent variable (VALUE) and the following
independent variables (AGE, LOTSIZE, RMS, MOD KITCH, MOD BATH,
AIRCON and FIREPL).
b. For this new data set, use the Correlation option of Data
Analysis in Excel and produce the correlation matrix associated
with the dependent variable...
For this assignment, please submit the answers to the following questions, as well as an Excel...For this assignment, please submit the answers to the following
questions, as well as an Excel spreadsheet which documents the work
you did.
Do poets die young? According to William Butler Yeats, “She is
the Gaelic muse, for she gives inspiration to those she persecutes.
The Gaelic poets die young, for she is restless, and will not let
them remain long on earth.” One study designed to investigate this
issue examined the age at death for writers from different cultures...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
ADVERTISEMENT
|