CONTACT ME FOR A HIGH QUALITY CUSTOMIZED PAPER
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 for a PC, so you may need to make some adjustments if you are using a different spreadsheet, such as Google Sheets or Excel for a Mac.
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 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).
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 in this tutorial. 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, so 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.[1]
That’s the quick review. Now on to the problem at hand.
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.[2] 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? __________
[1] 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 students’ heights. That is, even if a short student were to study extra hard to raise their GPA, it wouldn’t cause them to get a higher salary, because they are already doomed by their shortness.
[2] 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.