Essentials 9e Chapter 13 Excel Master student
Chapter 13
Ross, Westerfield, and Jordan’s Excel Master
Essentials of Corporate Finance, 9th edition
by Brad Jordan and Joe Smolira
Version 9.0
Chapter 13
In these spreadsheets, you will learn how to use the following Excel functions:
The following conventions are used in these spreadsheets:
1) Given data in blue
2) Calculations in red
NOTE: Some functions used in these spreadsheets may require that
the “Analysis ToolPak” or “Solver Add-In” be installed in Excel.
To install these, click on the File button
then “Options,” “Add-Ins” and select
“Go.” Check “Analysis ToolPak” and
“Solver Add-In,” then click “OK.”
Scroll bars
/xl/drawings/drawing1.xml#’Section%2013.2′!A65
Pie charts
/xl/drawings/drawing1.xml#’Section%2013.3′!A26
Section 13.2
Chapter 13 – Section 2
The Effect of Financial Leverage
The capital structure that produces the highest firm value is the capital structure that is most beneficial to shareholders. But, what are the effects of leverage? We can illustrate the effects of financial leverage on earnings per share and return on equity using the Trans Am Corporation as an example.
Suppose we have the following current and proposed capital structures for the Trans Am Corporation:
Current Proposed
Assets $ 8,000,000 $ 8,000,000
Debt $ – $ 4,000,000
Equity $ 8,000,000 $ 4,000,000
Debt-equity ratio 0 1.0 Counter: 10
Joe Smolira: This is the counter for the scroll bar. We introduced scroll bars in Chapter 6.
Share price $ 20 $ 20
Shares outstanding 400,000 200,000
Interest rate 10% 10%
With these capital structures, the ROE and EPS for different scenarios will be:
Current Capital Structure: Debt = $0 million
Recession Expected Expansion
EBIT $ 500,000 $ 1,000,000 $ 1,500,000
Interest – – –
Net income $ 500,000 $ 1,000,000 $ 1,500,000
ROE 6.25% 12.50% 18.75%
EPS $ 1.25 $ 2.50 $ 3.75
Proposed Capital Structure: Debt = $4 million
Recession Expected Expansion
EBIT $ 500,000 $ 1,000,000 $ 1,500,000
Interest 400,000 400,000 400,000
Net income $ 100,000 $ 600,000 $ 1,100,000
ROE 2.50% 15.00% 27.50%
EPS $ 0.50 $ 3.00 $ 5.50
If we want to examine the effects of leverage for different capital structures, we can simply change the debt-equity ratio. In this case, we set up a scroll bar to change the debt-equity ratio. If you click one of the arrows on the scroll bar, it changes the pro forma statements for the different states of the economy, as well as changes the graph below.
As you can see from changing the debt-equity ratio, the advantages of debt increase as debt increases, but the disadvantage to debt also increases as the debt-equity ratio increases.
RWJ Excel Tip
To begin, we need to set up the scroll bar. We went to the Developer tab, then clicked Insert, then selected the scroll bar we liked under Form Controls. This will bring up a “+” symbol which allows you to adjust the scroll bar size. Once you create the scroll bar, put the mouse over the scroll bar, right-click the mouse, and select Format Control from the menu. This brings up a box that looks like this:
Format control allows us to set the parameters of how the scroll bar will operate. The current value is the value we want the scroll bar to start. We need to set this value between the minimum and maximum value we will set later. We set the minimum value to 1 since a value of zero would equal the company’s current debt-equity ratio. The maximum we chose is 100. You may wonder why we chose such a large number. The reason is the Incremental change. The Incremental change is the increment that we want the counter to change by. We would like the change in the debt-equity ratio to be a decimal so that we could examine debt-equity ratios of, say 1.8 and 1.9. Unfortunately, Excel will only calculate incremental changes that are whole numbers. To work around this issue, we divided the counter by 10 in the debt-equity ratio cell. This will allow us to examine different debt-equity ratios from 0.1 to 10.0 at 0.1 increments. Finally, we have a linked cell, in this case cell J13. The linked cell shows the output for the counter. When you click the scroll bar, this cell changes, which results in a change in the debt-equity ratio. Notice that the number is purple. The reason for this is that it is an output from the scroll bar, but the output is unique in that you can manually change it without changing anything else. For example, you could type 45 in this cell and it will not affect the future use of the scroll bar. Typically, if you overwrite an output cell, the new value you entered will be static.
So what is the breakeven EBIT? We could use Solver or Goal Seek to answer this question if we wanted, but instead we will create an equation to answer the question for us.
Breakeven EBIT: $ 800,000.00
Here is a question for you: How does the breakeven level of EBIT change as the debt-equity ratio changes? Click on the scroll bar to find out.
Financial Leverage: EPS and EBIT for Trans Am Corporation
Current Capital Structure 500000 1000000 1500000 1.25 2.5 3.75 Proposed Capital Structure 500000 1000000 1500000 0.5 3 5.5
Earnings before interest and taxes (no taxes)
Earnings per share
Financial Leverage: EPS and EBIT for Trans Am Corporation
Current Capital Structure 500000 1000000 1500000 1.25 2.5 3.75 Proposed Capital Structure 500000 1000000 1500000 0.5 3 5.5
Earnings before interest and taxes (no taxes)
Earnings per share
Financial Leverage: EPS and EBIT for Trans Am Corporation
Current Capital Structure 500000 1000000 1500000 1.25 2.5 3.75 Proposed Capital Structure 500000 1000000 1500000 0.5 3 5.5
Earnings before interest and taxes (no taxes)
Earnings per share
Section 13.3
Chapter 13 – Section 3
Capital Structure and the Cost of Equity Capital
M&M Proposition I states that the total value of a firm’s debt and equity will be the same regardless of the firm’s capital structure. This argument has become known as the pie model, which we can graphically examine in Excel. Below, we show an exploded pie chart based on the capital structure for the Trans Am Corporation on the previous worksheet.
RWJ Excel Tip
To create a pie chart, select the data and go to Insert and select Pie from the chart options. For this chart, we selected Exploded pie in 3-D. As with any other chart, there are numerous options if you right-click on the chart and select Format Plot Area from the menu. In this case, we selected a Shadow border for the chart. To change the color of the pie chart itself, we right clicked on each colored portion of the pie, selected Format Data Series, then Fill.
Pie Model of Proposed Capital Structure
Debt Equity 4000000 4000000
Master it!
Chapter 13 – Master it!
The TL Corporation currently has no debt outstanding. Josh Culberson, the CFO, is considering restructuring the company by issuing debt and using the proceeds to repurchase outstanding equity. The company’s assets are worth $40 million, the stock price is $25 per share, and there are 1,600,000 shares outstanding. In the expected state of the economy, EBIT is expected to be $3 million. If there is a recession, EBIT would fall to $1.8 million and in an expansion EBIT would increase to $4.3 million. If the company issues debt, it will issue a combination of short-term debt and long-term debt. The ratio of short-term debt to long-term debt will be 0.20. The short-term debt will have an interest rate of 3 percent and the long-term debt will have an interest rate of 8 percent.
a. On the next worksheet, fill in the values in each table. For the debt-equity ratio, create a spinner that changes the debt-equity ratio. The resulting debt-equity ratio should range from 0 to 10 at increments of 0.1.
b. Graph the EBIT and EPS for the TL Corporation on the same graph using a scatter plot.
c. What is the breakeven EBIT between the current capital structure and the new capital structure?
d. To illustrate the new capital structure, you would like to create a pie chart. Another pie chart that is available is the pie in pie chart, Using the pie in pie chart, graph the equity and total debt in the main pie chart and the short-term debt and long-term debt in the secondary pie chart. Note, if you right-click on a data series in the chart and select Format Data Series, the Series Options will permit you to display the series by a customized choice. In the customization, you can select which data series you want displayed in the primary pie chart and the secondary pie chart.
Solution
Master it! Solution
a. Current Proposed
Assets $ 40,000,000
Short-term debt $ –
Long-term debt $ –
Debt
Equity
Short-term debt/Long-term debt – 0.20
Debt-equity ratio 0 Counter:
Share price $ 25
Shares outstanding 1,600,000
Short-term debt interest rate 3%
Long-term debt interest rate 8%
Recession Expected Expansion
EBIT $ 1,800,000 $ 3,000,000 $ 4,300,000
Interest
Net income
ROE
EPS
Recession Expected Expansion
EBIT
Interest
Net income
ROE
EPS
b.
c. Breakeven EBIT
d.
The post Essentials 9e Chapter 13 Excel Master student appeared first on graduatepaperhelp.
"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"
We are a leading academic writing company, which has been providing exclusive writing services for years.