Use the Company NextEra Energy (stock Symbol NEE) to answer the following questions.

Estimate Capital Structure (25 points)

- Estimate the firms weights of debt, preferred stock, and common stock using the firms balance sheet (book value).

- Estimate the firms weights of debt, preferred stock, and common stock using the market value of each capital component.

Compute Weighted Average Cost of Capital (WACC)

- Estimate the firms before-tax and after-tax component cost of debt; (Note: If the information about the current corporate tax rate is not available, you need to estimate the tax rate based on the historical tax payments).

- Estimate the firms component cost of preferred stock;

- Use three approaches (CAPM, DCF, bond-yield-plus-risk-premium) to estimate the component cost of common equity for the firm.

- Calculate the firms weighted average cost of capital (WACC) using the market-based capital weights.

Cash Flow Estimation

- We assume that the company of your choice is considering a new project. The project has 11 years life. This project requires initial investment of $680 million to purchase equipment, and $25 million for shipping & installation fee. The fixed assets fall in the 10-year MACRS class. The number of units of the new product expected to be sold in the first year is 2,560,000 and the expected annual growth rate is 5.5%. The sales price is $255 per unit and the variable cost is $200 per unit in the first year, but they should be adjusted accordingly based on the estimated annualized inflation rate of 1.8%. The required net operating working capital (NOWC) is 12% of sales. Use the corporate tax rate obtained in Step (4) for the project. The project is assumed to have the same risk as the corporation, so you should use the WACC you obtained from prior steps as the discount rate. Note: you may revise the partial model in the file Ch11 P18 Build a Model.xls on the website of the textbook (also posted in this final project learning module in Blackboard) for capital budgeting analysis, but you are NOT required to strictly follow the partial model. Actually, you are encouraged to build a better model by yourself.

- Compute the depreciation basis and annual depreciation of the new project. (Please refer to Table 11A-2 MACRS Depreciation Percentages on P.496 of the textbook)

- Estimate annual cash flows for the 11 years.

- Draw a time line of the cash flows.

Capital Budgeting Analysis

- Using the WACC obtained from in Step (4) as the discount rate for this project, apply capital budgeting analysis techniques (NPV, IRR, MIRR, PI, Payback, Discounted Payback) to analyze the new project.

- Perform a sensitivity analysis for the effects of key variables (e.g., sales growth rate, cost of capital, unit costs, sales price) on the estimated NPV or IRR in order to demonstrate the sensitivity of the model. The Scenario analysis of several variables simultaneously is encouraged (but not required). A document Sensitivity Analysis in Excel is provided in this learning module. It introduces the Data Table method that you can use for performing sensitivity analysis in Excel.

- Discuss whether the project should be taken and summarize your report.

Chapter: Problem: 11 18 Cash Flow Estimation and Risk Analysis Webmasters.com has developed a powerful new server that would be used for corporations Internet activities. It would cost $10 million at Year to buy the equipment necessary to manufacture the server. The project would require net working capital at the beginning of each year in an amount equal to 10% of the year's projected sales; for example, NWC - 10% Sales: The firm believes it could sell 1,000 units per year. The servers would sell for $24,000 per unit, and Webmasters believes that variable costs would amount to $18,000 per unit. After Year 1, the sales price and variable costs will increase at the inflation rate of 3%. The company's nonvariable costs would be $1 million at Year 1 and also would increase at the 3% inflation rate. The server project would have a life of 4 years. If the project is undertaken, it must be continued for the entire 4 years. The equipment would be depreciated over a 5-year period, using MACRS rates. The estimated market value of the equipment at the end of the project's 4-year life is $500,000 Webmasters' federal-plus-state tax rate is 25%. Its cost of capital is 10% for average-risk projects, defined as projects with a coefficient of variation of NPV between 0.8 and 1.2. Low-risk projects are evaluated with a WACC of 8%, and high- risk projects at 13%. Also, the project's returns are expected to be highly correlated with returns on the firm's other assets. a. Develop a spreadsheet model, and use it to find the project's NPV, IRR, and payback. Input Data in thousands of dollars) Scenario namo Base Case Note: the items in red will be used in a scenario analysis. Probability of scenario 50% Equipment cost $10,000 Net operating working capital Sales 10% Key Results: First year sales in units) 1.000 NPV - $0 Sales price per unit $24.00 IRR - 0.0% Variable cost per unit excl. depr.) $18.00 Payback 0.00 Nonvariable costs excl. depr.) $1.000 Inflation in prices and costs 3.0% Estimated salvage value at year 4 $500 Depreciation Years Year 1 Year 2 Year 3 Year 4 Depreciation rates 20.00% 32.00% 19.20% 11.52% Tax rate 25% WACC for average-risk projects 10% 0 1 2 3 4 Intermediate Calculations Units sold Sales price per unit (excl. depr.) Variable costs per unit (excl. depr.) Nonvariable costs (excl. depr.) Sales revenue Required level of net operating working capital Basis for depreciation Annual equipment depr. rate Annual depreciation expense Ending Bk Val: Cost - Accur Dep'rn Salvage value Profit (or loss) on salvage Tax on profit or loss) Net cash flow due to salvage 20.00% 32.00% 19.20% 11.52% Years 2 0 Cash Flow Forecast Sales revenue Variable costs Nonvariable operating costs Depreciation equipment) Oper.income before taxes (EBIT) Taxes on operating income 40%) Net operating profit after taxes Add back depreciation Equipment purchases Cash flow due to change in NOWC Net cash flow due to salvage Net Cash Flow Timeline of cash flows) Key Results: Appraisal of the Proposed Project Net Present Value (at 10%) IRR - MIRR Payback Discounted Payback Data for Payback Years Years 2 O 3 4 Net cash flow Cumulative CF Part of year required for payback Data for Discounted Payback Years 1 Years 2 0 90 90 90 Net cash flow Discounted cash flow Cumulative CF Part of year required for discounted payback b. Now conduct a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per unit, and number of units sold. Set these variables' values at 10% and 20% above and below their base-case values. Include a graph in your analysis. 1st YEAR UNIT SALES NPV 1,000 90 % Deviation from Base Case -20% -10% 0% 10% 20% Note about data tables. The data in the column input should NOT be input using a cell reference to the column input cell. For example, the base case 1st Year Unit Sales in Cell B100 should be the number 1,000 and NOT have the formula D31 in that cell. This is because you'll use D31 as the column input cell in the data table and if Excel tries to iteratively replace Cell D31 with the formula =D31 rather than a series of numbers, Excel will calculate the wrong answer. Unfortunately, Excel won't tell you that there is a problem, so you'll just get the wrong values for the data table! % Deviation SALES PRICE NPV $24.001 $0 from VARIABLE COST Basel NPV $18.00 $0 % Deviation from Base Case -20% -10% 0% 10% 20% Base Case -20% -10% 0% 10% 20% Deviation NPV at Different Deviations from Base from Sales Variable Base Case Units Sold Price Cost/Unit -20% $0 $0 $0 -10% $0 $0 $0 0% $0 $0 $0 10% $0 $0 $0 $0 20% Range $0 $0 c. Now conduct a scenario analysis. Assume that there is a 25% probability that best-case conditions, with each of the variables discussed in Part b being 20% better than its base-case value, will occur. There is a 25% probability of worst- case conditions, with the variables 20% worse than base, and a 50% probability of base-case conditions. (Hint: Use Scenario Manager. Go to the Data menu, choose What-lf-Analyis, the choose Scenario Manager. After you create the Scenario's, you can pick a scenario and type in the resulting NPV (but be sure to return the Scenario to the base-case afterward). Or you can create a Scenario Summary and use a cell reference to the Scenario Summary worksheet to show the NPV for each scenario.) Variable Costs per Unit Scenario Sales Price per Unit Probability Unit Sales NPV Best Case Base Case Worst Case 25% 50% 25% 1,200 1,000 800 $28.80 $24.00 $19.20 $14.40 $18.00 $17.28 Expected NPV = Standard Deviation = Coefficient of Variation = Std Dev / Expected NPV = to d. If the project appears to be more or less risky than an average project, find its risk-adjusted NPV, IRR, and payback. CV range of firm's average-risk project: 0.8 1.2 Low-risk WACC = 8% WACC 10% High-risk WACC 13% Risk-adjusted WACC = Risk adjusted NPV IRR = Payback = e. On the basis of information in the problem, would you recommend that the project be accepted? structures are depreciated over 39 years. In both cases, straight-line depreciation must be used. The depreciation allowance for the first year is based, pro rata, on the month the asset was placed in service, with the remainder of The yearly recovery allowance, or depreciation expense, is determined by multiplying As we explained previously, the half-year convention causes an asset's depreciable life to each asset's depreciable basis by the applicable recovery percentage shown in Table 11A-2. Part 4 Projects and Their Valuation TABLE 11A-2 MACRS Depreciation Percentages Class of Investment 7-Year 10-Year 3-Year 5-Year Ownership Year 14.29% 10.00% 20.00% 33.33% 1 24.49 18.00 32.00 2 44.45 17.49 14.40 19.20 3 14.81 11.52 12.49 11.52 4 7.41 5 11.52 8.93 9.22 6 5.76 8.92 7.37 7 8.93 6.55 8 4.46 6.55 9 6.56 10 6.55 11 3.28 100% 100% Note: 100% 100% Residential rental property (apartments) is depreciated over a 27.5-year life, whereas commercial and industrial set placed in service in February would receive 10.5 months of depreciation in the first year. xon ite be 1 year greater than the asset's property class. For example, suppose a property has a $100,000 basis. If it has a tion, the depreciation expenses in the first two years are $100,000(0.3333) $100,000(0.4445) = $44,450. What is the impact of the 2017 TCH 3-year classifica $33,330 and acquired in 2018 th Chapter: Problem: 11 18 Cash Flow Estimation and Risk Analysis Webmasters.com has developed a powerful new server that would be used for corporations Internet activities. It would cost $10 million at Year to buy the equipment necessary to manufacture the server. The project would require net working capital at the beginning of each year in an amount equal to 10% of the year's projected sales; for example, NWC - 10% Sales: The firm believes it could sell 1,000 units per year. The servers would sell for $24,000 per unit, and Webmasters believes that variable costs would amount to $18,000 per unit. After Year 1, the sales price and variable costs will increase at the inflation rate of 3%. The company's nonvariable costs would be $1 million at Year 1 and also would increase at the 3% inflation rate. The server project would have a life of 4 years. If the project is undertaken, it must be continued for the entire 4 years. The equipment would be depreciated over a 5-year period, using MACRS rates. The estimated market value of the equipment at the end of the project's 4-year life is $500,000 Webmasters' federal-plus-state tax rate is 25%. Its cost of capital is 10% for average-risk projects, defined as projects with a coefficient of variation of NPV between 0.8 and 1.2. Low-risk projects are evaluated with a WACC of 8%, and high- risk projects at 13%. Also, the project's returns are expected to be highly correlated with returns on the firm's other assets. a. Develop a spreadsheet model, and use it to find the project's NPV, IRR, and payback. Input Data in thousands of dollars) Scenario namo Base Case Note: the items in red will be used in a scenario analysis. Probability of scenario 50% Equipment cost $10,000 Net operating working capital Sales 10% Key Results: First year sales in units) 1.000 NPV - $0 Sales price per unit $24.00 IRR - 0.0% Variable cost per unit excl. depr.) $18.00 Payback 0.00 Nonvariable costs excl. depr.) $1.000 Inflation in prices and costs 3.0% Estimated salvage value at year 4 $500 Depreciation Years Year 1 Year 2 Year 3 Year 4 Depreciation rates 20.00% 32.00% 19.20% 11.52% Tax rate 25% WACC for average-risk projects 10% 0 1 2 3 4 Intermediate Calculations Units sold Sales price per unit (excl. depr.) Variable costs per unit (excl. depr.) Nonvariable costs (excl. depr.) Sales revenue Required level of net operating working capital Basis for depreciation Annual equipment depr. rate Annual depreciation expense Ending Bk Val: Cost - Accur Dep'rn Salvage value Profit (or loss) on salvage Tax on profit or loss) Net cash flow due to salvage 20.00% 32.00% 19.20% 11.52% Years 2 0 Cash Flow Forecast Sales revenue Variable costs Nonvariable operating costs Depreciation equipment) Oper.income before taxes (EBIT) Taxes on operating income 40%) Net operating profit after taxes Add back depreciation Equipment purchases Cash flow due to change in NOWC Net cash flow due to salvage Net Cash Flow Timeline of cash flows) Key Results: Appraisal of the Proposed Project Net Present Value (at 10%) IRR - MIRR Payback Discounted Payback Data for Payback Years Years 2 O 3 4 Net cash flow Cumulative CF Part of year required for payback Data for Discounted Payback Years 1 Years 2 0 90 90 90 Net cash flow Discounted cash flow Cumulative CF Part of year required for discounted payback b. Now conduct a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per unit, and number of units sold. Set these variables' values at 10% and 20% above and below their base-case values. Include a graph in your analysis. 1st YEAR UNIT SALES NPV 1,000 90 % Deviation from Base Case -20% -10% 0% 10% 20% Note about data tables. The data in the column input should NOT be input using a cell reference to the column input cell. For example, the base case 1st Year Unit Sales in Cell B100 should be the number 1,000 and NOT have the formula D31 in that cell. This is because you'll use D31 as the column input cell in the data table and if Excel tries to iteratively replace Cell D31 with the formula =D31 rather than a series of numbers, Excel will calculate the wrong answer. Unfortunately, Excel won't tell you that there is a problem, so you'll just get the wrong values for the data table! % Deviation SALES PRICE NPV $24.001 $0 from VARIABLE COST Basel NPV $18.00 $0 % Deviation from Base Case -20% -10% 0% 10% 20% Base Case -20% -10% 0% 10% 20% Deviation NPV at Different Deviations from Base from Sales Variable Base Case Units Sold Price Cost/Unit -20% $0 $0 $0 -10% $0 $0 $0 0% $0 $0 $0 10% $0 $0 $0 $0 20% Range $0 $0 c. Now conduct a scenario analysis. Assume that there is a 25% probability that best-case conditions, with each of the variables discussed in Part b being 20% better than its base-case value, will occur. There is a 25% probability of worst- case conditions, with the variables 20% worse than base, and a 50% probability of base-case conditions. (Hint: Use Scenario Manager. Go to the Data menu, choose What-lf-Analyis, the choose Scenario Manager. After you create the Scenario's, you can pick a scenario and type in the resulting NPV (but be sure to return the Scenario to the base-case afterward). Or you can create a Scenario Summary and use a cell reference to the Scenario Summary worksheet to show the NPV for each scenario.) Variable Costs per Unit Scenario Sales Price per Unit Probability Unit Sales NPV Best Case Base Case Worst Case 25% 50% 25% 1,200 1,000 800 $28.80 $24.00 $19.20 $14.40 $18.00 $17.28 Expected NPV = Standard Deviation = Coefficient of Variation = Std Dev / Expected NPV = to d. If the project appears to be more or less risky than an average project, find its risk-adjusted NPV, IRR, and payback. CV range of firm's average-risk project: 0.8 1.2 Low-risk WACC = 8% WACC 10% High-risk WACC 13% Risk-adjusted WACC = Risk adjusted NPV IRR = Payback = e. On the basis of information in the problem, would you recommend that the project be accepted? structures are depreciated over 39 years. In both cases, straight-line depreciation must be used. The depreciation allowance for the first year is based, pro rata, on the month the asset was placed in service, with the remainder of The yearly recovery allowance, or depreciation expense, is determined by multiplying As we explained previously, the half-year convention causes an asset's depreciable life to each asset's depreciable basis by the applicable recovery percentage shown in Table 11A-2. Part 4 Projects and Their Valuation TABLE 11A-2 MACRS Depreciation Percentages Class of Investment 7-Year 10-Year 3-Year 5-Year Ownership Year 14.29% 10.00% 20.00% 33.33% 1 24.49 18.00 32.00 2 44.45 17.49 14.40 19.20 3 14.81 11.52 12.49 11.52 4 7.41 5 11.52 8.93 9.22 6 5.76 8.92 7.37 7 8.93 6.55 8 4.46 6.55 9 6.56 10 6.55 11 3.28 100% 100% Note: 100% 100% Residential rental property (apartments) is depreciated over a 27.5-year life, whereas commercial and industrial set placed in service in February would receive 10.5 months of depreciation in the first year. xon ite be 1 year greater than the asset's property class. For example, suppose a property has a $100,000 basis. If it has a tion, the depreciation expenses in the first two years are $100,000(0.3333) $100,000(0.4445) = $44,450. What is the impact of the 2017 TCH 3-year classifica $33,330 and acquired in 2018 th