Public Administration
FIN 310: Principles of Financial Management
PROJECT (10 pts)
(Due:12/6/2012)
Objectives: The objective of this project is to engage you in some "real life" exercises. It allows you to learn how some of the key concepts discussed in class are derived by using simple examples. In addition, you can learn how to collect information and data from popular finance sources. It is also expected to sharpen your Excel skill.
Group project: This is small group assignment. You may work in groups of up to 3 people (acceptable to work individually). It is each student’s responsibility to ensure his/her name is on one homework assignment and turned in before the deadline. All members of a group will receive the same grade. While complaints of unproductive group members might be valid, they will not be considered in grade assignment – work it out before you turn it in!
Select your company: Each group will work on a unique company. I use the Blackboard wiki tool to process the selection. Go to the course web site of the Blackboard. Click the “Tools” tab. Find and click “Wikis”. You will see the wiki named “project company list”. Click it and follow the instruction to select your company. Note that you can only sign to a company that has not been picked by other groups – first come first choose.
Submission:
The project is due Thursday, December 6th, 2012. Each group should upload to the Blackboarda word or pdf file of your report AND an excel file which should contain the data you use and show how you estimate the WACC. Detailed requirements are specified in the “Assignment” part below. A 10% reduction from full credit perday the assignment is late after the due date.
Instructions
In this case, you will find the weighted-average cost of capital (WACC) for your firm. While doing this, you will explore the capital structure for your firm, as well as find the cost of each component of the firm’s financing. If you are having trouble with a specific question, let me know and we can work on it. Also, keep in mind that we are making estimates here, so there is some margin of error in any of the calculations even if the CFO of the company were performing the calculation. It is not realistically possible to get this perfect. Make an honest effort and do the best you can.
Equity
You will estimate the cost of equity considering the estimates you find using historical returns and the Capital Asset Pricing Model (CAPM). Detailed instructions on how to apply these models to determine costs of capital follow in the appendix of the instruction.
Debt
We will use the book value of debt as proxy for market value of debt. The cost debt is estimated by adding default premium to risk free rate. Details about how to carry this out are in the appendix.
Preferred Stock
For simplicity, we ignore preferred stock. Assume the firm only has debt and common equity in its capital structure.
Finding WACC
Using the information you gather throughout the case, you will estimate the WACC for your firm. This is an important calculation that is performed at every large firm, and the cost of capital for the firm guides decisions as we have seen throughout the capital budgeting portion of the course. By learning how to find the WACC for real companies, you will gain a greater understanding of the underlying issues in financing, and you will likely find that estimating the cost of capital is as much an art as it is a science.
The Assignment
Write a one-page executive summary giving your results and a summary of the major components of your WACC calculation. The audience for an executive summary is the CEO or CFO, so it should be clear and concisely written (these guys are very busy), but give enough information to allow top-level management to make informed decisions. Keep in mind that appearance counts for a lot when it comes to business communication. People are more likely to believe the analysis in a document that looks professional. Also, when writing a business document, always keep your audience in mind. This report is going to top management, some of whom may have come up through the operations or marketing departments. Intricate knowledge of finance is not a given for these types of managers, so the challenge is to keep your report understandable for an audience that is reasonably intelligent, but not particularly knowledgeable about finance.
Submit the word or pdf file of your one-page executive summary of the WACC estimation.
You should have a supporting report (appendix to executive summary) that should include a write-up and your calculations. The write-up should be a narrative guiding others through the calculations, including justifications for various choices you are prompted to make, and should reference the calculations you are making. The calculations will likely be mainly in the form of Excel spreadsheets. The appendix does not need to be quite as professionally done as the executive summary, but should still be readable and understandable to someone else within the finance department of your organization. After all, you don’t want to hand a piece of junk report to a manager or coworker with your name on it. Generally, this means that your write-up should be understandable, and your calculations should be labeled and reasonably formatted. Since your audience is someone in the finance department, you can assume that your reader has a fairly developed understanding of finance.
To help your organize your calculation and for the purpose of standardization, I created an Excel file Product_appendix.xls. Do the required data collection and estimation as described below.Submit this Excel file. Your executive summary is based on the results reported in the Excel file. I should see the cell function of your calculation in the Excel function bar wherever applicable (otherwise points will be deducted!).
Note (Important, please read!):
-The Excel file Project_appendix contains four worksheets. Be sure you complete all of them.
-Basically I should be able to replicate your estimations using your data, otherwise points will be deducted.
-You can highlight cells, format cells, and/or adjust the width of the column wherever necessary.
Appendix
A. Equity
Report the results of Equity in the “Equity” worksheet of Project_appendix.xls;report the CAPMbeta regression in the “CAPM Reg” worksheet.
There are two necessary components for you to find: market value of equity and cost of equity.
I. Market value of equity (should be relatively easy to find)
MVequity = Price per share Number of shares outstanding
Number of shares outstanding: can often be found at a financial website like finance.yahoo.com (Profile). Alternatively, you can look on an annual report at the Owner’s Equity portion of the balance sheet.
Market price: is easy to find at any number of sources. Please note the date when you find the price.
II. Cost of equity
We will estimate the cost of equity using two methods. You will need historical stock prices or historical index values. These are readily available at finance.yahoo.com. Make sure to use the “Adjusted Close” column of data, which adjust for stock splits and dividends.
Method 1: Historical Returns. Report historical average annual returns for the past three years, five years, and ten years (refer to the class note about the trade-off when finding the right length of time). To figure these, follow these instructions:
1)Find the annual return for each of the past ten years. To do this, use the adjusted close prices for your stock and use the following formula to figure returns:
So, suppose we are doing this analysis on October 1st, 2012. To find the return for the past year, you would take
To figure the year before that,
And so on. Repeat this for each of the past 10 years.
Note: use as many returns as possible if your company has less than 10 (or 5) years of return data.
2)To find the three-year, five-year, and ten-year average annual returns, just take the average of the annual returns from that period.
Make a determination about which is the best horizon to measure the cost of equity for your firm. Justify your choice (include a note in the write-up).
Method 2: CAPM.Report data and regression in “CAPM Reg” worksheet and summarize results in “Equity” worksheet.
You need several parameters to apply CAPM: risk-free rate, market risk premium, and beta of your stock. For this project, we will use rf = 3% and MRP = 5%.
Next, estimate beta. As we discussed, beta can be estimated as the coefficient of a regression of stock returns on market returns. For this case, we use weekly returnsover the past three years.Use the following instructions to estimate beta. Then use CAPM to get a cost of equity.
Estimating Beta by Regression
Begin by downloading historical prices for your stock and the S&P 500 into Excel. You can enter your range of dates, return interval, and have the information downloaded into excel. Make sure that you request the same set of dates for both the index and your security. You will need the column of data in the Yahoo output labeled as the “adjusted close”. Once you download the historical prices into Excel, compute the returns for your stock and the market.
To calculate beta, regress the stock’s returns on the market returns. Save your regression results.Beta will be the numbers labeled the coefficient on the X variable.
Note on regression betas: if you get a beta from your regression that is very close to zero (e.g. 0.002106 or -0.0001683), it is likely that you made an error. We know from the fact that stock return autocorrelations are very low that you will get a low beta if you mismatch the periods of your stock returns and the market returns (e.g. you match October 2005 your company returns with November 2005 S&P returns). Remember that the average beta across all stocks is equal to 1, so if you get something that is far away from 1 in your beta calculation, you may have issues.
Method 3: Constant Growth Dividend Discount Model.Report in “Equity” worksheet.
According to constant DDM, re = D1/P + g. To estimate re, you need to find (estimate): D1 (next dividend), P (current price), and g (growth rate)
D1: You can find past dividends (D0) on finance.yahoo.com in the same way you find prices… just click dividends instead of daily, weekly, or monthly stock prices. Firms typically pay quarterly dividends. It is often easier to annualize these (by multiplying by four) for our analysis. You will need estimates of future dividends (D1) to find the cost of equity using DDM.
Price: Use the same price when calculating the market value of equity.
Growth rate: If historical dividend growth rates are fairly constant, and this can be reasonably expected to continue, a constant dividend growth rate formula may be used. Estimate the constant growth rate as g = retention ratio ROE = (1 – payout ratio) ROE; both payout ratio and ROE can be found on finance.yahoo.com(key statistics).
You now have several estimates of the cost of equity. Considering each of your estimates, come up with a composite estimate of cost of equity, and justify your estimate.
B. Debt
Report the results of Debt in the “Debt” worksheet of Project_appendix.xls.
I. Cost of debt
1) Find the credit rating of your firm.
What is your firm’s credit rating? If your firm is rated, you should be able to find the rating at You may try other sources to find the rating (e.g., google).
If your firm is not rated, you may try to find similar companies based on industry and overall risk, and use their credit rating as a proxy.
2) Find the default spread assuming the average maturity of the firm’s debt is 7 years. Use the Default spread table in the lecture slide of Lec 11.
3) The cost of debt is estimated as rf + default spread. Again, set rf = 3%.
II. Market value of debt
We use the book value of debt as the estimate of the market value.
Note on using the book value of debt: Typically, we are going to use the non-current portion of balance-sheet liabilities as our book value of debt number for the calculation. So D is calculated as total liabilities minus current liabilities if CA>CL or as total liabilities minus total current assets if CA<CL.
III. Tax rate
Marginal tax rate should be used to calculate the after-tax cost of debt.[1] Again, for simplicity, we use a proxy for the margin tax rate -- the effective tax rate, computed by dividing the total income tax expense by earnings before interest and taxes (EBIT). Total income tax expense and EBIT can be found from the firm’s “income statement”, which can be got from finance.yahoo.com. Use the most recent year’s data.
Finding WACC
Report the results in the “WACC” worksheet of Project_appendix.xls.
This is a wrap-up worksheet. Based on all of your estimates, find the WACC for your firm.
1
[1] Almost all firms provide a table in the notes to the financial statements (10-K or annual report) listing the marginal federal, state, and local tax rates.