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: 1. The cost of debt (ro) 2. The corporate tax rate (TE) 3. The risk free rate (TR) 4. The market risk (B) 5. The market rate of return (r) 6. Current Stock Price (P) 7. Dividend Growth Rate (g) 8. 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: 1. 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. 2. Tax rate (T) 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 Home Insert Draw Page Layout Formulas Data Review View Help Acrobat Times New Roman 12 - AA Wap text General Copy Format Poster -A- Merge Center $ - % - en Delete Conditional Format Fomaning toile Styles Cligib Fort Alg Number 01 f D 1 G B C Calculating Weighted Average Cost of Capital (WACC) H Step 1: Click here to calculate the before-txt and were cost of dett Interest expense Average debut TO Sum of income tax expense Sun of income before tax (1-T) 5233.0 $1.070.5 21 $1,4810 $8.752.01 16.990 3 2018 2017 2016 4 Short-term det 5995.0 5996.0 $45.0 5 Long-term debe 5754.0 $580.0 5520.0 6. laterest expense S233.0 5248.0 $295.0 7 Income before taxes $2.915.0 $2.841.0 $2.996.0 8 Income tax expense 5503.0 5502.0 $476.0 9 Dividend (most recent $10 10 Current stock price. P $20.00 11 Average shares outstanding 10.00 12 Growth rate, 8.09 13 Risk-free rate, 0.2594 141 Company risk. 1.60 15 Market rame 7.00% 16 Book Value Market Value 17 Weight of debt, we 0.096 0.096 18 Weight of equity, we 0.09 0.096 19 20 Click here to carry over the weights of debt and equity before proceeding 21 Step 2: Click here to alexlate the cost of city (CAPM) nu) Step 3: Click here to calculate the cout of equity dividend growth) (DP) 13.00 Step 4: Click here to calculate WACC ning book and market weight Click here to clear the WACC calculations WACC-CAPM WACC Dividends Book Valur Market Value 0.00% 0.00% 0.00% 0.0044 24 25 26 27 28 Calculating WACC Scenarios Sheet Sheet R! Type here to search 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. 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: 1. The cost of debt (ro) 2. The corporate tax rate (TC) 3. The risk free rate (rap) 4. The market risk (B) 5. The market rate of return (rm) 6. Current Stock Price (P) 7. Dividend Growth Rate (g) 8. 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: 1. 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. 2. Tax rate (T) 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. 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: 1. The cost of debt (ro) 2. The corporate tax rate (TE) 3. The risk free rate (TR) 4. The market risk (B) 5. The market rate of return (r) 6. Current Stock Price (P) 7. Dividend Growth Rate (g) 8. 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: 1. 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. 2. Tax rate (T) 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 Home Insert Draw Page Layout Formulas Data Review View Help Acrobat Times New Roman 12 - AA Wap text General Copy Format Poster -A- Merge Center $ - % - en Delete Conditional Format Fomaning toile Styles Cligib Fort Alg Number 01 f D 1 G B C Calculating Weighted Average Cost of Capital (WACC) H Step 1: Click here to calculate the before-txt and were cost of dett Interest expense Average debut TO Sum of income tax expense Sun of income before tax (1-T) 5233.0 $1.070.5 21 $1,4810 $8.752.01 16.990 3 2018 2017 2016 4 Short-term det 5995.0 5996.0 $45.0 5 Long-term debe 5754.0 $580.0 5520.0 6. laterest expense S233.0 5248.0 $295.0 7 Income before taxes $2.915.0 $2.841.0 $2.996.0 8 Income tax expense 5503.0 5502.0 $476.0 9 Dividend (most recent $10 10 Current stock price. P $20.00 11 Average shares outstanding 10.00 12 Growth rate, 8.09 13 Risk-free rate, 0.2594 141 Company risk. 1.60 15 Market rame 7.00% 16 Book Value Market Value 17 Weight of debt, we 0.096 0.096 18 Weight of equity, we 0.09 0.096 19 20 Click here to carry over the weights of debt and equity before proceeding 21 Step 2: Click here to alexlate the cost of city (CAPM) nu) Step 3: Click here to calculate the cout of equity dividend growth) (DP) 13.00 Step 4: Click here to calculate WACC ning book and market weight Click here to clear the WACC calculations WACC-CAPM WACC Dividends Book Valur Market Value 0.00% 0.00% 0.00% 0.0044 24 25 26 27 28 Calculating WACC Scenarios Sheet Sheet R! Type here to search 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. 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: 1. The cost of debt (ro) 2. The corporate tax rate (TC) 3. The risk free rate (rap) 4. The market risk (B) 5. The market rate of return (rm) 6. Current Stock Price (P) 7. Dividend Growth Rate (g) 8. 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: 1. 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. 2. Tax rate (T) 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