Determinants of Capital Structure Spreadsheet/ Discounted Cash Flow Analysis Spreadsheet

Higgins, Analysis for Financial Management 1

Analysis for Financial Management

Robert C. Higgins

Spreadsheet Tutorial

to accompany Chapter 6

Determinants of Capital Structure

Introduction: This spreadsheet tutorial will give you practice with the regression tool in Excel that

allow you to evaluate what factors have the greatest impact on a company’s capital structure.

Follow the instructions on this handout using the accompanying Excel spreadsheet. Use your

spreadsheet to answer the questions in each of the problems below. The instructions are based on

Excel 2013 for a PC, so you may need to make some adjustments if you are using a different

spreadsheet, such as Calc or Excel for a Mac.

Quick Review of Regression: 1 In regression analysis, we ask the question of how some variable X

(the explanatory variable) affects some other variable Y (the dependent variable). This can be seen

graphically, as in the chart pictured below.

This chart shows the relationship between a college student’s GPA and their starting salary after

college (don’t worry, it’s just hypothetical data). Each dot represents one student, showing the

combination of their GPA and their starting salary. It’s clear that the data show a positive relation

between GPA and salary; generally speaking, the better your performance in college, the higher

your salary after college. Regression analysis just allows us to quantify this relationship. It allows

us to identify the “line of best fit” (shown in the chart) that best represents the relationship between

GPA (the explanatory variable, X) and starting salary (the dependent variable, Y).

1 If you have never studied regression analysis before, you may need to do some additional preparation in order to best

understand this tutorial.

Higgins, Analysis for Financial Management 2

Excel has a regression tool that allows you to do this type of analysis very quickly. To use it you

must have installed the Analysis Toolpak add-in (go to the Add-ins menu under Excel Options).

When you go to Data | Data Analysis | Regression and use the regression dialog box (explained

further below), you get some output that looks like this:

That’s a lot of numbers, so I’ve highlighted the ones you need to worry about the most. So here’s

how we interpret our output: First, look at the coefficient on GPA (the explanatory variable), which

is 18,772. This means that, according to the data, if a student has a one-point higher GPA (such as

moving from 2.8 to 3.8), she will have a $18,772 higher starting salary, on average.

Next, look at the t-stat on GPA, which is 6.4. This tells us whether the relationship we observe is

statistically significant (the larger the t-stat, the more significant). To keep things simple, you can

follow a rule of thumb that if the t-stat is greater than 2 (or less than -2), the relationship is

statistically significant, meaning it’s unlikely it occurred just by chance.

Finally look at the R-square of the regression. This tells us how well the explanatory variables do at

explaining the variation in the dependent variable. The R-square of 0.69 means that variation in

GPA explains about 69% of the variation in starting salary. 2

That’s the quick review. Now on to the problem at hand.

2 A final word of warning: This result does not mean that getting a higher GPA causes a student to get a higher starting

salary. It just means that the two variables are correlated. Suppose, for example, that taller people get higher salaries

(that’s actually true, by the way) and also get higher GPAs (I don’t think that’s actually true). If that were the case, then

the correlation could just result from student’s heights. That is, even if a short student were to study extra hard to raise

their GPA, it won’t cause them to get a higher salary, because they are already doomed by their shortness.

SUMMARY OUTPUT

Regression Statistics

Multiple R 0.827812463

R Square 0.685273475

Adjusted R Square 0.668708921

Standard Error 6292.659712

Observations 21

ANOVA

df SS MS F Significance F

Regression 1 1638146241 1.64E+09 41.36987 3.63259E-06

Residual 19 752353758.8 39597566

Total 20 2390500000

Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%

Intercept 5075.393508 10187.06445 0.498219 0.624047 -16246.3774 26397.164 -16246.3774 26397.16445

GPA 18772.08779 2918.572519 6.431942 3.63E-06 12663.44531 24880.73 12663.44531 24880.73028

Higgins, Analysis for Financial Management 3

Instructions:

In this problem we will ask the question, “What factors are significant determinants of a company’s

capital structure?” The worksheet lists all 500 U.S. corporations from the S&P 500. The debt ratio

of each is shown, which will be the dependent variable in this problem. To the right are four

explanatory variables that may or may not show a significant impact on capital structure.

Step 1: Size and Capital Structure

First test whether firm size affects capital structure. Choose Data | Data Analysis | Regression. 3

The dependent (Y) variable is the Debt Ratio, so highlight B4:B504 for that range. The explanatory

(X) variable is Size, so highlight D4:D504 for that range. Check the Labels box to indicate that the

first cell in each column is a label, then click on OK.

Q1: Is firm size a significant predictor of capital structure? __________

Q2: Based on the sign of the coefficient on Size, interpret the meaning of the relationship between

size and capital structure. ____________________________________________

Step 2: Growth and Capital Structure

Repeat the analysis with Growth as the explanatory variable. (Before doing another regression, I

recommend deleting the tab with the old regression results. Otherwise, you will soon have an

unwieldy number of tabs in your workbook.)

Q3: Is the growth rate a significant predictor of capital structure? __________

Q4: Based on the sign of the coefficient on Growth, interpret the meaning of the relationship

between Growth and capital structure. ________________________________

Step 3: Profitability and Capital Structure

Repeat the analysis with Profitability as the explanatory variable.

Q5: Is Profitability a significant predictor of capital structure? __________

Q6: How do you interpret the coefficient on Profitability (consider the discussion of Market

Signaling in Chapter 6)? ____________________________

Step 4: Asset Tangibility and Capital Structure

Repeat the analysis with Asset Tangibility as the explanatory variable.

Q7: Is Asset Tangibility a significant predictor of capital structure? __________

Q8: How do you interpret the coefficient on Asset Tangibility (consider the discussion of Distress

Costs in Chapter 6)? ________________________

Step 5: All Variables

Repeat the analysis using all four explanatory variables. (Highlight all four columns simultaneously

for the X variable.)

Q9: Which variables are now significant predictors of capital structure? __________

3 In other versions of Excel, it is Tools | Data Analysis | Regression. Remember that you must have the Analysis

ToolPak add-in installed in order to run regressions.