DCF Valuation (see sample model below) a. Use the below forecasted income statement and balance sheet to populate the DCF model with Sales, NOPAT and NOA for Forecast Y1 through Forecast Y5. b. Calculate the Increase in NOA for the forecast period. c. Calculate FCFF for the forecast period. d. Estimate a terminal growth rate for the company. e. Using your estimated terminal growth rate, calculate FCFF in the terminal year (i.e., apply terminal growth rate to the FCFF calculated in year five of your forecast). f. Using your calculated WACC below, apply a growing perpetuity formula to calculate the estimated terminal value. g. Using your calculated WACC below, discount the forecast horizon FCFF to obtain PV of forecast period FCFF. h. Using your calculated WACC below, discount the estimated terminal value to obtain the PV of terminal value. i. Refer to the 10-K balance sheet below and subtract the NNO, preferred stock (if applicable), and noncontrolling interest (if applicable) from the total firm value to obtain the equity value of the company. j. Divide the equity value of the firm by the outstanding shares to obtain your estimate of per share value.
WACC: 6.14%
Forecasted Income Statement:
Forecasted Balance Sheet:
CONSOLIDATED STATEMENTS OF INCOME - USD (\$) s 12 Months Ended Dec. 31, 2020 Dec. 31, 2021 Dec. 31, 202212/31/202312/31/2024 12/31/2025 12/31/2026 12/31/2027 Net Operating Revenues $33,014 $38,655 $43,004 Cost of goods sold Gross Profit Selling, general and administrative expenses Other operating charges Operating Income Interest income Interest expense Equity income (loss) - net Other income (loss) - net Income Before Income Taxes Income taxes Consolidated Net Income Net Income (Loss) Attributable to Noncontrolling In \begin{tabular}{|r|r|r|r|r|r|r|r|} \hline 13,433 & 15,357 & 18,000 & 18,404 & 19,324 & 20,097 & 20,901 & 21,528 \\ \hline 19,581 & 23,298 & 25,004 & 26,750 & 28,088 & 29,211 & 30,380 & 31,291 \\ \hline 9,731 & 12,144 & 12,880 & 13,673 & 14,357 & 14,931 & 15,528 & 15,994 \\ \hline 853 & 846 & 1,215 & 1,144 & 1,201 & 1,249 & 1,299 & 1,338 \\ \hline 8,997 & 10,308 & 10,909 & 11,934 & 12,530 & 13,032 & 13,553 & 13,959 \\ \hline 370 & 276 & 449 & 449 & 449 & 449 & 449 & 449 \\ \hline 1,437 & 1,597 & 882 & 1,163 & 1,103 & 1,070 & 1,023 & 888 \\ \hline 978 & 1,438 & 1,472 & 1,472 & 1,472 & 1,472 & 1,472 & 1,472 \\ \hline 841 & 2,000 & -262 & -262 & -262 & -262 & -262 & -262 \\ \hline 9,749 & 12,425 & 11,686 & 12,430 & 13,086 & 13,621 & 14,189 & 14,730 \\ \hline 1,981 & 2,621 & 2,115 & 2,784 & 2,931 & 3,051 & 3,178 & 3,300 \\ \hline 7,768 & 9,804 & 9,571 & 9,646 & 10,155 & 10,570 & 11,010 & 11,431 \\ \hline 21 & 33 & 29 & 29 & 29 & 29 & 29 & 29 \\ \hline \end{tabular} \begin{tabular}{|r|r|r|} \hline$7,747 & $9,771 & $9,542 \\ \hline$1.80 & $2.26 & $2.20 \\ \hline$1.79 & $2.25 & $2.19 \\ \hline 4,295 & 4,315 & 4,328 \\ \hline 28 & 25 & 22 \\ \hline 4,323 & 4,340 & 4,350 \\ \hline \end{tabular} Cost of goods sold Selling, general and administrative expenses Other operating charges \begin{tabular}{|l|l|l|} \hline 0.41 & 0.40 & 0.42 \\ \hline 0.29 & 0.31 & 0.30 \\ \hline 0.03 & 0.02 & 0.03 \\ \hline \end{tabular} CONSOLIDATED STATEMENTS OF INCOME - USD (\$) s 12 Months Ended Dec. 31, 2020 Dec. 31, 2021 Dec. 31, 202212/31/202312/31/2024 12/31/2025 12/31/2026 12/31/2027 Net Operating Revenues $33,014 $38,655 $43,004 Cost of goods sold Gross Profit Selling, general and administrative expenses Other operating charges Operating Income Interest income Interest expense Equity income (loss) - net Other income (loss) - net Income Before Income Taxes Income taxes Consolidated Net Income Net Income (Loss) Attributable to Noncontrolling In \begin{tabular}{|r|r|r|r|r|r|r|r|} \hline 13,433 & 15,357 & 18,000 & 18,404 & 19,324 & 20,097 & 20,901 & 21,528 \\ \hline 19,581 & 23,298 & 25,004 & 26,750 & 28,088 & 29,211 & 30,380 & 31,291 \\ \hline 9,731 & 12,144 & 12,880 & 13,673 & 14,357 & 14,931 & 15,528 & 15,994 \\ \hline 853 & 846 & 1,215 & 1,144 & 1,201 & 1,249 & 1,299 & 1,338 \\ \hline 8,997 & 10,308 & 10,909 & 11,934 & 12,530 & 13,032 & 13,553 & 13,959 \\ \hline 370 & 276 & 449 & 449 & 449 & 449 & 449 & 449 \\ \hline 1,437 & 1,597 & 882 & 1,163 & 1,103 & 1,070 & 1,023 & 888 \\ \hline 978 & 1,438 & 1,472 & 1,472 & 1,472 & 1,472 & 1,472 & 1,472 \\ \hline 841 & 2,000 & -262 & -262 & -262 & -262 & -262 & -262 \\ \hline 9,749 & 12,425 & 11,686 & 12,430 & 13,086 & 13,621 & 14,189 & 14,730 \\ \hline 1,981 & 2,621 & 2,115 & 2,784 & 2,931 & 3,051 & 3,178 & 3,300 \\ \hline 7,768 & 9,804 & 9,571 & 9,646 & 10,155 & 10,570 & 11,010 & 11,431 \\ \hline 21 & 33 & 29 & 29 & 29 & 29 & 29 & 29 \\ \hline \end{tabular} \begin{tabular}{|r|r|r|} \hline$7,747 & $9,771 & $9,542 \\ \hline$1.80 & $2.26 & $2.20 \\ \hline$1.79 & $2.25 & $2.19 \\ \hline 4,295 & 4,315 & 4,328 \\ \hline 28 & 25 & 22 \\ \hline 4,323 & 4,340 & 4,350 \\ \hline \end{tabular} Cost of goods sold Selling, general and administrative expenses Other operating charges \begin{tabular}{|l|l|l|} \hline 0.41 & 0.40 & 0.42 \\ \hline 0.29 & 0.31 & 0.30 \\ \hline 0.03 & 0.02 & 0.03 \\ \hline \end{tabular}
