Ross_Essentials_10e_Chapter_13_Excel_Master_student_version

xlsx

School

John Brown Univeristy *

*We aren’t endorsed by this school

Course

2113

Subject

Finance

Date

Nov 24, 2024

Type

xlsx

Pages

Uploaded by DeanExploration7479

by Brad Jordan and Joe Smolira Version 10.0 Chapter 13 In these spreadsheets, you will learn how to use the follow 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." Ross, Westerfield, and Jordan's Excel Master Essentials of Corporate Finance , 10th edition Scroll bars Pie charts

wing Excel functions: :

Chapter 13 - Section 2 The Effect of Financial Leverage Suppose we have the following current and proposed capital structures for the Trans Am C 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 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 The capital structure that produces the highest firm value is the capital structure that is m illustrate the effects of financial leverage on earnings per share and return on equity using If we want to examine the effects of leverage for different capital structures, we can chang equity ratio. If you click one of the arrows on the scroll bar, it changes the pro forma state Financial Leverage: EPS and EBIT f

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

RWJ Excel Tip As you can see from changing the debt-equity ratio, the advantages of debt increase as de increases. To begin, we need to set up the scroll bar. We went to the Developer tab, then clicked Inse "+" symbol which allows you to adjust the scroll bar size. Once you create the scroll bar, pu from the menu. This brings up a box that looks like this: $500,000 $1,000,000 $0 $3 $6 $9 $12 $15 $18 $21 $24 Earnings before interest and taxes (no taxes) Earnings per share

Break-even EBIT: $ 800,000.00 Here is a question for you: How does the break-even level of EBIT change as the debt-equi Format control allows us to set the parameters of how the scroll bar will operate. The curr value between the minimum and maximum value we will set later. We set the minimum v ratio. The maximum we chose is 100. You may wonder why we chose such a large number that we want the counter to change by. We would like the change in the debt-equity ratio Unfortunately, Excel will only calculate incremental changes that are whole numbers. To w This will allow us to examine different debt-equity ratios from 0.1 to 10.0 at 0.1 increment output for the counter. When you click the scroll bar, this cell changes, which results in a c this is that it is an output from the scroll bar, but the output is unique in that you can man this cell and it will not affect the future use of the scroll bar. Typically, if you overwrite an o So what is the break-even EBIT? We could use Solver or Goal Seek to answer this question us.

Corporation: Counter: 10 most beneficial to shareholders. But, what are the effects of leverage? We can g the Trans Am Corporation as an example. ge the debt-equity ratio. In this case, we set up a scroll bar to change the debt- ements for the different states of the economy and changes the graph below. for Trans Am Corporation This is the counter for the scroll bar. We introduced scroll bars in Chapter 6.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

ebt increases, but the disadvantage to debt also increases as the debt-equity ratio ert, then selected the scroll bar we liked under Form Controls. This will bring up a ut the mouse over the scroll bar, right-click the mouse, and select Format Control $1,500,000 Current Capital Structure Proposed Capital Structure )

ity ratio changes? Click on the scroll bar to find out. rent value is the value at which we want the scroll bar to start. We need to set this value to 1 since a value of zero would equal the company's current debt-equity r. The reason is the incremental change. The Incremental change is the increment o to be a decimal so that we could examine debt-equity ratios of, say 1.8 and 1.9. work around this issue, we divided the counter by 10 in the debt-equity ratio cell. ts. Finally, we have a linked cell, in this case cell J13. The linked cell shows the change in the debt-equity ratio. Notice that the number is purple. The reason for nually change it without changing anything else. For example, you could type 45 in output cell, the new value you entered will be static. n if we wanted, but instead we will create an equation to answer the question for

Chapter 13 - Section 3 Capital Structure and the Cost of Equity Capital RWJ Excel Tip M&M Proposition I states that the total value of a firm's debt and equity will be the same pie model, which we can graphically examine in Excel. Below, we show an exploded pie ch worksheet. To create a pie chart, select the data and go to "Insert" and select "Pie" from the chart opti are numerous options if you right-click on the chart and select Format Plot Area from the color of the pie chart itself, we right clicked on each colored portion of the pie, selected "F $4,000,000 $4,000,000 Pie Model of Proposed Capital Struct

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

regardless of the firm's capital structure. This argument has become known as the hart based on the capital structure for the Trans Am Corporation on the previous tions. For this chart, we selected Exploded pie in 3-D. As with any other chart, there menu. In this case, we selected a "Shadow" border for the chart. To change the Format Data Series," then "Fill." ture Debt Equity

Chapter 13 - Master It! a. b. Graph the EBIT and EPS for the TL Corporation on the same graph using a scatter plo c. What is the break-even EBIT between the current capital structure and the new capi d. The TL Corporation currently has no debt outstanding. Josh Culberson, the CFO, is co repurchase outstanding equity. The company's assets are worth $40 million, the stoc expected state of the economy, EBIT is expected to be $3 million. If there is a recessi million. If the company issues debt, it will issue a combination of short-term debt an short-term debt will have an interest rate of 3 percent and the long-term debt will ha On the next worksheet, fill in the values in each table. For the debt-equity ratio, crea should range from 0 to 10 at increments of 0.1. To illustrate the new capital structure, you would like to create a pie chart. One type the equity and total debt in the main pie chart and the short-term debt and long-ter chart and select Format Data Series, the Series Options will permit you to display the series you want displayed in the primary pie chart and the secondary pie chart.

ot. ital structure? onsidering restructuring the company by issuing debt and using the proceeds to ck price is $25 per share, and there are 1,600,000 shares outstanding. In the ion, EBIT would fall to $1.8 million and in an expansion EBIT would increase to $4.3 nd long-term debt. The ratio of short-term debt to long-term debt will be 0.20. The ave an interest rate of 8 percent. ate a spinner that changes the debt-equity ratio. The resulting debt-equity ratio of pie chart that is available is the pie-in-pie chart. Using the pie-in-pie chart, graph rm debt in the secondary pie chart. Note, if you right-click on a data series in the e series by a customized choice. In the customization, you can select which data

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Master It! Solution a. Current Proposed Assets $ 40,000,000 $ 40,000,000 Short-term debt $ - $ 3,492,063.49 Long-term debt $ - $ 17,460,317 Debt $ - $ 20,952,381 Equity $ 40,000,000 $ 19,047,619 Short-term debt/Long-term debt - 0.20 Debt-equity ratio 0 1.1 Share price $ 25 $ 25 Shares outstanding 1,600,000 761,905 Short-term debt interest rate 3% 3% Long-term debt interest rate 8% 8% Current Capital Structure: Debt =$0 million Recession Expected Expansion EBIT $ 1,800,000 $ 3,000,000 $ 4,300,000 Interest - - - Net income $ 1,800,000 $ 3,000,000 $ 4,300,000 ROE 4.50% 7.50% 10.75% EPS $ 1.13 $ 1.88 $ 2.69 Proposed Capital Structure: Debt = $20.952 million Recession Expected Expansion EBIT $ 1,800,000 $ 3,000,000 $ 4,300,000 Interest 1,501,587 1,501,587 1,501,587 Net income $ 298,413 $ 1,498,413 $ 2,798,413 ROE 0.75% 3.75% 7.00% EPS $ 0.39 $ 1.97 $ 3.67 b.

c. Breakeven EBIT $ 2,866,667 d. $19,047,619 Pie chart for TL Coproration Proposed Capital Structure 1 2 3 Total debt Short-Term debt Long-Term debt

Counter: 11 $1,500,000 $2,000,000 $2,500,000 $3,000,000 $3,500,000 $4,000,000 $4,500,000 $- $0.50 $1.00 $1.50 $2.00 $2.50 $3.00 $3.50 $4.00 Financial Leverage:EPS and EBIT for TL corp. Current Capital Structure Proposed Capital Structure Ebit EPS

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version