Assume today's date is 1/1/2018

Subject:AccountingPrice:10.01 Bought7

Share With

Assume today's date is 1/1/2018. The Report worksheet contains a projected income statement, balance sheet and cash flow statement for Cloud Peak Energy (ticker symbol: CLD).

Your answers will go in the blue shaded cells on the DCF worksheet.

This assignment is worth a total of 33 points. Points possible are shown to the right of the cell(s) to which they pertain.

Discounted Cash Flow Settings

1. In cell C4, create a dropdown menu with the choices "Perpetuity" and "Multiple" for terminal value calculation method. Leave it set at Multiple when you turn your spreadsheet in.
2. In cell B5, use a formula that will cause the cell to display the words "Terminal growth rate" if cell C4 reads "Perpetuity" and reads "Terminal EBITDA multiple" otherwise.
3. In cell C5, use a formula that will cause the cell to display "1.5%" if cell C4 reads "Perpetuity" and displays 4.0 otherwise. Use conditional formatting so that the 1.5% is displayed as a percentage and the 4.0 is not.

Discounted Cash Flow Model

1. Complete the blue cells in the range of D10:H16 to calculate the free cash flows. Notes: Include "Other income, nontaxable" with EBIT. Have the items displayed as positive or negative such that you can calculate FCF using the SUM function. There is no amortization expense.
2. Complete the blue cells in the range K10:K16, normalizing the appropriate accounts for use in a growing perpetuity terminal value calculation.
3. In cell H18, use a formula that will automatically adjust to calculate the correct terminal value whether "Perpetuity" or "Multiple" is chosen in cell C4.
4. Calculate the present value factors in cells D21:H21.
5. Calculate the present values of the free cash flows and terminal value, as appropriate, in cells D23:H23 using Method 1 for calculating present value as illustrated in the online course material.
6. In cell C25, calculate the firm's enterprise value.
7. In cell C26, calculate the firm's net debt.
8. In cell C27, calcualte the firm's equity value.

Sensitivity Analysis

1. In cell D30, use a formula that will cause the cell to display the words "Terminal Growth Rate" if cell C4 reads "Perpetuity" and reads "Terminal EBITDA Multiple" otherwise. Merge and center this heading across cells D30:H30.
2. In cells C31:H36 create a data table that will calculate the enterprise value for various values of WACC (provided for you), terminal growth rate and terminal EBITDA multiple. The values for terminal growth rate and terminal EBITDA multiple will be displayed in cells D31:H31. Use a formula that will cause the growth rate to be displayed if cell C4 reads "Perpetuity" and the EBITDA multiple to be displayed otherwise. Use conditional formatting so that growth rates are displayed as percentages and multiples are not. Use the following values for terminal growth rates and terminal EBITDA multiples in columns D through H, respectively:
   • Terminal growth rates: 0.5%, 1.0%, 1.5%, 2.0%, 2.5%
   • Terminal EBITDA multiples:  2.0, 3.0, 4.0, 5.0, 6.0