WACC ASSIGNMENT Due 4/20/2021 at 5pm
The WACC spreadsheet provides a tool for understanding the calculation of WACC and the affect in changes of the components of WACC. The spreadsheet automates the WACC calculation so the emphasis on this assignment is on understanding the implications the various inputs to the WACC calculation. Clicking the various buttons on the Financial Statements worksheet and the Calculate WACC worksheet calculate the WACC for the company. Since some information carries over between worksheets you should start with the Financial Statements worksheet. The Scenarios worksheet has spinner buttons that automate the process of changing various factors. The Scenarios sheet allows you to vary those factors and then recalculate WACC. Since working with somebody else’s spreadsheet takes some getting used to, I suggest that you try to use this spreadsheet and then contact me via email or office hours with any questions.
Assume you are a financial manager in the company. The CFO believes that understanding its WACC is critical for the company to make good financial decisions. The CFO believes that if the company’s WACC increases above 10% it will create very real hardship. The CFO has asked you to calculate the companies WACC and what effects it. You should use the spreadsheet provided.
Since WACC can be calculated with either the SML or the dividend growth model, please use both WACC SML and WACC DIVIDEND GROWTH. The spreadsheet calculates WACC and allows you to perform sensitivity analysis on the key factors affecting WACC.
The following factors affect WACC:
- The cost of debt (rD)
- The corporate tax rate (TC)
- The risk free rate (rRF)
- The market risk (β)
- The market rate of return (rM)
- Current Stock Price (P)
- Dividend Growth Rate (g)
- The current dividend
Using the “Sensitivity” worksheet to adjust the above factors and comment the impact.
Which two factors do you think have the largest effect on WACC SML? WACC DIVIDEND GROWTH?
Extra credit: Assess and comment on the company’s leverage and how any changes there might affect WACC.
Please note the following:
- In somewhat of a departure from the lecture, the WACC Spreadsheet uses interest expense/debt outstanding to determine cost of debt. This is an acceptable way to determine cost of debt if
a) information regarding market value of debt is not available. Book value of debt is not perfect, but it is the best available in this case. The spreadsheet uses the market value of the equity.
b) reported interest expense is not net of significant interest income.
- Tax rate (TC) is calculated using 2018 Earnings Before Taxes and Income Tax Expense. This is correct if there was nothing unusual regarding taxes in 2018. This determines what the effective tax rate was as opposed to the statutory tax rate. Often the effective tax rate is a more realistic representation of the actual taxes paid by a company.