WACC

WACCProjectExcelSheetBSA554MBA763Template.xlsx

Home >Business & Finance homework help >Accounting homework help >WACC

Step 1

BSA 554 WACC Project Check-list:
STATE YOUR NAME:
STATE THE COMPANY NAME:
1. Determine the firm’s rd from the 10-K report in the Note to the Long-term Debt, where rd = ∑(wdi*rdi), where wdi = the weight of debt for each bond and rdi is the coupon rate for each bond. Please regard equation 6.6 on page 247 for a review of the methodology.
Provide the NOTE # and page # in the 10-K report where the Note to Long-term Debt is located.
Note that you will have to determine weights of each bond issue relative to the grand total amount of bonds issued before you begin the rd calculation.
Show all calculations for the Rd construction.
$ per Bond Issue	Wdi	*	Rdi	=
Reference:
What you have learned from this section:

Step 2

2. Construct the capital structure of the firm to find the wd and ws components of the WACC. Note that the dollar amount of long-term debt and the dollar amount of total equity should be utilized. Preferred equity dollar amount should be included in the calculation if the firm still has preferred equity outstanding.
Provide a link to the balance sheet utilized:
Remember that the wd + ws has to equal 1.00, which is 100% of the grand total dollar amount of the long-term debt and the total equity amount (assuming that the firm does not have preferred equity).
Note that the long-term debt amount is not equal to Total Liabilities.
wd	ws
Reference:
What you have learned from this section:

Step 3

3. Find the firm’s beta.

Provide a link to the web-page where this was derived. You may also provide the page number in the 10-K where the beta is stated.

You should find the beta from the company's 10K report. You may also find the beta via Yahoo Finance and/or Google Finance.

How is this beta compared to the firm's nearest competitor?

Reference:

What you have learned from this section:

Step 4

4. Determine the firm’s tax rate from the 10-K information. Please make sure to include the page number and section/Note # where you determined the firm’s tax rate in the 10-K report from the previous fiscal year.

Reference:

What you have learned from this section:

Step 5

5. Find either the 1-year Treasury bill rate or the 10-year Treasury note rate at the close of the firm’s fiscal year. If the risk-free rate is provided by the firm in the 10-K, please utilize this percentage.

You should align the Treasury rates to the firm's close of the fiscal year analyzed.

https://www.federalreserve.gov/releases/h15/

https://www.federalreserve.gov/datadownload/Choose.aspx?rel=H15

You must state the exact date and Treasury rate utilized and provided.

Reference:

What you have learned from this section:

Step 6

6. Find an appropriate rm percentage for the previous fiscal year.

You must state the link to where you found the information for the annual return for the year analyzed.

You may utilize either the S&P 500 US Equity Index, the NYSE (100), or the NASDAQ (100) returns for the fiscal year analyzed.

Please show all of your work on how this percentage was determined. Graphs and/or calculations are encouraged.

Reference:

What you have learned from this section:

Step 7

7. Construct the current CAPM rs for the firm. Please review page 258, equation 6.14.

Calculations must be provided.

Reference:

What you have learned from this section:

Step 8

8. Please construct a rs utilizing a Discounted Dividend Model.

Utilize equation 9.10 on pate 373 to calculate rs.

Utilize equation 9.11 on pate 373 to calculate g, the growth rate.

Please state how the Do, the growth rate, and the price per share was determined by including the associated calculations and web-links on this tab.

Please align the price-per-share with the date of the close of the fiscal year.

Reference:

What you have learned from this section:

Step 9

9. Construct the current WACC for the firm utilizing the CAPM Rs and then the DDM Rs. Please review page 375, equation 9.2. Note that you do not have to utilize the short-term debt portion in the equation.

A minimum of two WACCs should be constructed.

Calculations must be provided utilizing the previous information determined in the previous tabs.

Bring the percentage out to the fourth decimal (the nearest basis point).

Reference:

What you have learned from this section:

Step 10

10. Calculate the beta unlevered. Please review page 611, equation 15-10.

Please provide the equation and the previous tabs' components.

You will be utilizing the firm's beta, capital structure, and tax rate for the fiscal year analyzed.

Reference:

What you have learned from this section:

Step 11

11. Calculate the new beta levered at plus or minus 5% or 10% intervals of wd in the firm’s capital structure. Please review page 611, equation 15.9a.
Wd should start at 0% and end at 100%.
Note that the rd should increase or decrease by the percentage change of the wd.
Note: Please insert a column showing your current capital structure.
Wd	0.00	0.05	0.10	0.15	0.20	0.25	0.30	0.35	0.40	0.45	0.50	0.55	0.60	0.65	0.70	0.75	0.80	0.85	0.90	0.95	1.00
We	1.00	0.95	0.90	0.85	0.80	0.75	0.70	0.65	0.60	0.55	0.50	0.45	0.40	0.35	0.30	0.25	0.20	0.15	0.10	0.05	0.00
Reference:
What you have learned from this section:

Step 12

12. Determine the new rs CAPMs at each differing capital structure utilizing the new levered betas.
Insert a column showing your company's current capital structure.
Each CAPM needs to be calculated in the cell's formula. Please show your work in order to earn credit.
Note: Please insert a column showing your current capital structure.
Wd	0.00	0.05	0.10	0.15	0.20	0.25	0.30	0.35	0.40	0.45	0.50	0.55	0.60	0.65	0.70	0.75	0.80	0.85	0.90	0.95	1.00
We	1.00	0.95	0.90	0.85	0.80	0.75	0.70	0.65	0.60	0.55	0.50	0.45	0.40	0.35	0.30	0.25	0.20	0.15	0.10	0.05	0.00
CAPM = rs
Reference:
What you have learned from this section:

Step 13

13. Make sure to adjust the rd by plus or minus the percentage of wd’s change, so that a leverage increase of 5% should result in a new rd of (rd1.05) and a leverage decrease of 5% should result in a new rd of (rd0.95). Please refer to page 610 figure 15-5.
Please review the week-10 announcement that includes information on problems 15-9, 15-10, and 15-11 on pages 622 and 623.
Note: A decrease of wd by 5% should apply to Rd as (Rd0.95), and an increase of wd by 5% should apply to Rd as (Rd1.05), to determine the new Rd at the new Wd percentage.
Note: Please insert a column showing your current capital structure.
Wd	0.00	0.05	0.10	0.15	0.20	0.25	0.30	0.35	0.40	0.45	0.50	0.55	0.60	0.65	0.70	0.75	0.80	0.85	0.90	0.95	1.00
We	1.00	0.95	0.90	0.85	0.80	0.75	0.70	0.65	0.60	0.55	0.50	0.45	0.40	0.35	0.30	0.25	0.20	0.15	0.10	0.05	0.00
Rd
Reference:
What you have learned from this section:

Step 14

14. Replicate rows 1 to 6 in figure 15-5 on page 610 to determine the capital structure where WACC is minimized.
NOTE: Remember to provide all calculations of WACC.
Note: Please insert a column showing your current capital structure.
Wd	0.00	0.05	0.10	0.15	0.20	0.25	0.30	0.35	0.40	0.45	0.50	0.55	0.60	0.65	0.70	0.75	0.80	0.85	0.90	0.95	1.00
We	1.00	0.95	0.90	0.85	0.80	0.75	0.70	0.65	0.60	0.55	0.50	0.45	0.40	0.35	0.30	0.25	0.20	0.15	0.10	0.05	0.00
WACC
Reference:
What you have learned from this section:

Step 15

15. Graph the WACCs at the different capital structures of the firm as stated in the instructions. Note that the WACC graph should be in a "U" shape.

Refer to page 612's Figure 15-7 for the construction of the WACC graph. Note that the percentages are on the y-axis and the wd percentages are on the x-axis.

Reference:

What you have learned from this section:

Step 16

16. Calculate the FCF for 2017 (or 2018) utilizing Figure 2-5 on page 65. Please show the calculations for steps 1 to 5.

Reference:

What you have learned from this section:

Step 17

17. Using a zero-growth valuation model of V^0 = FCF/WACC, determine the V^0 at the current capital structure utilizing both the CAPM WACC. Please show your calculations from the previous tabs.

Reference:

What you have learned from this section:

Step 18

18. Using a zero-growth valuation model of V^0 = FCF/WACC, determine the V^0 at the optimal capital structure.

Reference:

What you have learned from this section:

Step 19

19. Determine the estimated stock price per share utilizing Figure 7-6 on page 309.

Valuation +

Cash and Cash Equivalents =

Total Estimated Value -

Long-term Debt -

Preferred Equity =

Estimated Value of Equity /

Number of Shares of Stock Outstanding =

Estimated Stock Price Per Share

Compare to the current stock price per share.

Reference:

What you have learned from this section:

Step 20

20. Review that stock price of the firm at the close of fiscal 2017 (or 2018). Compare the market price to your calculated intrinsic price, and make a written recommendation on the stock. Support your written recommendation with a stock chart and analysts’ information.

The project is due by Sunday, January 27th. No late work will be accepted.

Reference:

What you have learned from this section: