Ross, Westerfield, Jaffe, and Jordan's Spreadsheet Master

Corporate Finance, 11th edition

by Brad Jordan and Joe Smolira
Version 11.0

Chapter 16
In these spreadsheets, you will learn how to use the following Excel fun

Scroll bars
Pie charts

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 tab
then "Excel Options," "Add-Ins" and select
"Go." Check "Analysis ToolPak" and
"Solver Add-In," then click "OK."
 the following Excel functions:

eadsheets:

equire that
 Chapter 16 - Section 2
Financial Leverage and Firm Value: An Example

The capital structure that produces the highest firm value is the capital structure that is most beneficial to sharehold
illustrate the effects of financial leverage on earnings per share and return on equity using the Trans Am Corporation

Suppose we have the following current and proposed capital structures for the Trans Am Corporation:

Current Proposed
Assets $ 8,000 $ 8,000
Debt $ - $ 4,000
Equity $ 8,000 $ 4,000
Debt-equity ratio 0 1.0
Share price $ 20 $ 20
Shares outstanding 400 200
Interest rate 10% 10%

With these capital structures, the ROE and EPS for different scenarios will be:

Current Capital Structure: Debt = $0

Recession Expected Expansion
EBIT $ 400 $ 1,200 $ 2,000
Interest - - -
Net income $ 400 $ 1,200 $ 2,000
ROE 5.00% 15.00% 25.00%
EPS $ 1.00 $ 3.00 $ 5.00

Proposed Capital Structure: Debt = $4000

Recession Expected Expansion
EBIT $ 400 $ 1,200 $ 2,000
Interest 400 400 400
Net income $ - $ 800 $ 1,600
ROE 0.00% 20.00% 40.00%
EPS $ - $ 4.00 $ 8.00

If we want to examine the effects of leverage for different capital structures, we can simply change the debt-equity r
debt-equity ratio. If you click one of the arrows on the scroll bar, it changes the pro forma statements for the differen
below.

Financial Leverage: EPS and EBIT for Trans Am Corpo

$10
 Financial Leverage: EPS and EBIT for Trans Am Corpo
$10

$8

$6
Earnings per share

$4

$2

$-
$400 $1,200 $2,0

Earnings before interest and taxes (no taxes)

As you can see from changing the debt-equity ratio, the advantages of debt increase as debt increases, but the disad
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 scrol
a "+" symbol which allows you to adjust the scroll bar size. Once you create the scroll bar, put the mouse over the sc
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 w
value between the minimum and maximum value we will set later. We set the minimum value to 1 since a value of z
ratio. The maximum we chose is 100. You may wonder why we chose such a large number. The reason is the increme
increment that we want the counter to change by. We would like the incremental change to be a decimal so that we
Unfortunately, Excel will only calculate incremental changes that are whole numbers. To work around this issue, we d
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
output for the counter. When you click the scroll bar, this cell changes, which results in a change in the debt-equity r
this is that it is an output from the scroll bar, but the output is unique in that you can manually change it without cha
in this cell and it will not affect the future use of the scroll bar. Typically, if you overwrite an output cell, the new valu

So what is the breakeven EBIT? We could use Solver or Goal Seek to answer this question if we wanted, but instead w
us.

Breakeven EBIT: $ 800.00

Here is a question for you: How does the breakeven level of EBIT change as the debt-equity ratio changes? Click on t

Modigliani and Miller Proposition I

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 cap
the pie model, which we can graphically examine in Excel. Below, we show an exploded pie chart based on the capit

Pie Model of Capital Structure

$4,000 $4,000 Debt

Equity
 $4,000 $4,000 Debt
Equity

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 selecte
are numerous options if you right-click on the chart and select Format Chart Area from the menu. In this case, we se
color of the pie chart itself, we right clicked on each colored portion of the pie, selected Format Data Series, then Fil
beneficial to shareholders. But, what are the effects of leverage? We can
Trans Am Corporation as an example.

This is the counter for the

Counter: 10 scroll bar. We introduced
scroll bars in Chapter 8,
but we will have more to
say about them below.

ange the debt-equity ratio. In this case, we set up a scroll bar to change the
ements for the different states of the economy, as well as changes the graph

Trans Am Corporation
Trans Am Corporation

Current Capital Structure

Proposed Capital Structure

$2,000

ncreases, but the disadvantage to debt also increases as the debt-equity

hen selected the scroll bar we liked under Form Controls. This will bring up
the mouse over the scroll bar, right-click the mouse, and select Format
 value is the value we want the scroll bar to start on. We need to set this
to 1 since a value of zero would equal the company's current debt-equity
e reason is the incremental change. The Incremental change is the
e a decimal so that we could examine debt-equity ratios of, say 1.8 and 1.9.
around this issue, we divided the counter by 10 in the debt-equity ratio cell.
nally, we have a linked cell, in this case cell J13. The linked cell shows the
ge in the debt-equity ratio. Notice that the number is purple. The reason for
y change it without changing anything else. For example, you could type 45
utput cell, the new value you entered will be static.

e wanted, but instead we will create an equation to answer the question for

tio changes? Click on the scroll bar to find out.

ardless of the firm's capital structure. This argument has become known as
art based on the capital structure for the Trans Am Corporation.

Debt
Equity
 Debt
Equity

r this chart, we selected Exploded pie in 3-D. As with any other chart, there
enu. In this case, we selected a Shadow border for the chart. To change the
at Data Series, then Fill.
 Chapter 16 - Master it!

The TL Corporation currently has no debt outstanding. Josh Culberson, the CFO, is considering restructuring the
repurchase outstanding equity. The company's assets are worth $40 million, the stock price is $25 per share, an
expected state of the economy, EBIT is expected to be $3 million. If there is a recession, EBIT would fall to $1.8
$4.3 million. If the company issues debt, it will issue a combination of short-term debt and long-term debt. The
0.20. The short-term debt will have an interest rate of 3 percent and the long-term debt will have an interest ra

a. On the next worksheet, fill in the values in each table. For the debt-equity ratio, create a spinner that changes t
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?

To illustrate the new capital structure, you would like to create a pie chart. Another pie chart that is available is
d. the equity and total debt in the main pie chart and the short-term debt and long-term debt in the secondary pi
chart and select Format Data Series, Series Options, then Split Series By will permit you to display the series by
select which data series you want displayed in the primary pie chart and the secondary pie chart.
 ering restructuring the company by issuing debt and using the proceeds to
ice is $25 per share, and there are 1,600,000 shares outstanding. In the
EBIT would fall to $1.8 million and in an expansion EBIT would increase to
nd long-term debt. The ratio of short-term debt to long-term debt will be
will have an interest rate of 8 percent.

spinner that changes the debt-equity ratio. The resulting debt-equity ratio

hart that is available is the pie of pie chart, Using the pie of pie chart, graph
ebt in the secondary pie chart. Note, if you right-click on a data series in the
o display the series by a customized choice. In the customization, you can
ie chart.
 Master it! Solution

a. Current Proposed
Assets
Short-term debt
Long-term debt
Debt
Equity
Short-term debt/Long-term debt
Debt-equity ratio
Share price
Shares outstanding
Short-term debt interest rate
Long-term debt interest rate

Recession Expected Expansion

EBIT
Interest
Net income
ROE
EPS

Recession Expected Expansion

EBIT
Interest
Net income
ROE
EPS

b.
c. Breakeven EBIT

d.
Counter: