Hi,

I saw that you helped one of the students calculating the terminal value

I am doing the same exercise but I am using Ford Motors as an example

Below you can find the question. Also, I will appreciate solving and explaining the way to solve the terminal value

For Part II of your Module Project, the project outline, you will continue your analysis of your firm by estimating the terminal value for your firm. Calculations should include all scenarios possible using abnormal earnings methodology, as well as an equity value and asset value estimate based on Week 4s projections and the terminal value estimated this week (refer to Table 8-6 on page 346 of your course text). Prepare a brief written summary of 2 to 3 pages, describing and justifying the methods you used in estimating terminal value and detailing your assumptions. In a table, summarise the terminal values that you will use in your final valuation analysis and that were incorporated in the spreadsheet used in previous weeks.

Financial Analysis and Valuation Ford Motor Co. This template will form the basis for the financial valuation of a company of your choice. The template uses Ford Motor Co. as the target company for purposes of demonstrating the use of the model. An important data source for company specific information is Yahoo!Finance. Students should familiarize themselves with the extensive data available on Yahoo!Finance (or other sites like FT.COM, Google Finance, DailyFinance, etc.). The template specifies the Yahoo! Finance pages from which information can be retrieved. Spreadsheet cells with green text require input. Spreadsheet cells with black text are generally calculated. The Inputs tab is where most assumption data must be entered. The Forecasts tab is generally calculated, but there are a number of forecast assumptions that may be adjusted as appropriate. The valuation summary tab provides the equity valuation summary. The base model applies excess cash flow to debt reduction, as indicated by a 0 in the box to the right TO CHANGE THAT ASSUMPTION TO A CONSTANT DEBT RATIO, Enter 1 in the box to the right. Excess cash flow is then allocated to repurchasing stock in the future. 1 Ford Motor Co. 27-Apr-16 Data source: Yahoo!Finance under COMPANY > Key Statistics Ticker: Current stock price: Stock price date Forward annual dividend rate Dividend yield Shares outstanding Equity market capitalization Beta $ $ $ Capital Structure Costs F 13.04 13-Apr-16 0.60 4.60% 3,910,000,000 50,986,400,000 1.31635 Risk free rate (10 year Treasury) Equity risk premium (Historical average assumed) Cost of equity Net debt Net interest cost Cost of debt, pre tax Cost of debt, after tax 2.38% 7.00% 11.59% $ $ 97,678,000 773,000 0.79% 0.57% Data source: Yahoo!Finance underFINANCIALS > Balance Sheet Balance sheet data for most recent fiscal year end 31-Dec-15 Data source: Yahoo!Finance under FINANCIALS > Income Statement Income statement, fiscal year ended 31-Dec-15 Cash, cash equivalents, and short term investments Net receivables Inventory Other current assets Total current assets $ $ $ $ $ 35,176,000 101,975,000 8,319,000 145,470,000 Sales $ 149,558,000 Long term investments Property, plant, and equipment Goodwill and intangible assets Other assets + Deferred Long Term Asset Charges Total long term assets $ $ $ $ $ 3,224,000 57,256,000 18,975,000 79,455,000 Earnings before interest and taxes Interest expense, net Income before taxation Tax expense Net income from continuing operations Non-recurrent income (loss) + Minority Interest (gain) Preferred stock dividends Net income available to common stock $ $ $ $ $ $ $ $ 11,025,000 773,000 10,252,000 2,881,000 7,371,000 2,000 7,373,000 Diluted earnings per share Net income as a % of sales Tax rate NOPAT NOPAT Margin Dividend payout rate $ 1.89 4.9% 28.1% 7,928,773 5.3% 32% Total assets $ 224,925,000 Accounts payable Short term debt Other current liabilities Total current liabilities $ $ $ $ 20,272,000 20,272,000 Long term debt Other liabilities Deferred long term liabilities Minority interest Total long term liablitieis $ $ $ $ $ 132,854,000 42,546,000 502,000 15,000 175,917,000 $ Data source: Yahoo!Finance under ANALYST COVERAGE > Estimates Analyst Estimate Data Current Year End 31-Dec-16 EPS Average estimate $ 1.96 Revenue Estimate $ 14,607,000 Next Year End EPS Average estimate Revenue estimate Stockholders' equity + Misc Stocks Options Warrants $ 28,736,000 Total liabilities & equity $ 224,925,000 Check (assets - liabilities and equity) $ Net working capital Other long term liabilities Calculations required for other tabs in the spreadsheet $ 90,022,000 Current year estimated net income $ 43,063,000 Next year estimated net incom - $ $ 12/31/2017 2.06 148,880,000 Next 5 years growth rate (per annum) 11.1% $ $ 7,663,600,000.00 8,054,600,000.00 Fiscal year ending 31-Dec-16 31-Dec-17 31-Dec-18 31-Dec-19 31-Dec-20 31-Dec-21 31-Dec-22 31-Dec-23 31-Dec-24 31-Dec-25 Note: Column necessary to calculate free cash flow to equity for final year Balance sheet at beginning of fiscal year Net working capital Long term assets Net operating assets $ $ $ 90,022,000 $ 79,455,000 $ 169,477,000 $ 917,537,849 $ 809,835,038 $ 1,727,372,887 $ 1,018,925,781 $ 899,321,810 $ 1,918,247,591 $ Net debt Other long term liabilities Shareholders' equity Net capital $ $ $ $ 97,678,000 43,063,000 28,736,000 169,477,000 995,570,661 438,914,181 292,888,045 1,727,372,887 1,105,581,219 487,414,198 5,783,915,721 1,918,247,591 Net debt % Net capital Cost of debt, pretax Estimated tax rate After tax interest expense NOPAT NOPAT Margin Net income (Increase) / Decrease in net working capital (Increase) / Decrease in long term assets Increase / (Decrease) in net debt Increase / (Decrease) in other liabilities Free cash flow to equity $ $ $ $ $ $ $ $ 1,227,747,943 541,273,467 11,881,701,956 2,130,213,950 $ $ $ $ 1,256,549,717 $ 1,109,052,874 $ 2,365,602,591 $ 1,363,414,091 601,084,185 18,653,293,569 2,365,602,591 $ $ $ $ 1,395,398,461 $ 1,231,603,216 $ 2,627,001,678 $ 1,514,071,348 667,503,987 26,173,146,055 2,627,001,678 $ $ $ $ 1,549,589,991 $ 1,367,695,372 $ 2,917,285,363 $ 1,681,376,232 741,263,178 34,523,942,241 2,917,285,363 $ $ $ $ 1,720,819,685 $ 1,518,825,710 $ 3,239,645,396 $ 1,867,168,306 823,172,759 43,797,501,406 3,239,645,396 $ $ $ $ 1,910,970,260 $ 1,686,655,951 $ 3,597,626,212 $ 2,073,490,403 914,133,349 54,095,788,858 3,597,626,212 $ $ $ $ 2,122,132,474 $ 1,873,031,434 $ 3,995,163,908 $ 2,302,611,093 1,015,145,084 65,532,037,074 3,995,163,908 $ $ $ $ $ $ 2,557,049,619 1,127,318,616 78,231,990,718 4,436,629,520 57.6% 57.6% 57.6% 57.6% 57.6% 57.6% 57.6% 57.6% 57.6% 57.6% 31-Dec-17 31-Dec-18 31-Dec-19 31-Dec-20 31-Dec-21 31-Dec-22 31-Dec-23 31-Dec-24 31-Dec-25 31-Dec-26 14,607,000 NM 7,663,600,000 NM 31.8% 2,439,127,066 5,224,472,934 $ $ $ $ 0.79% 28.10% 555,773 $ 7,664,155,773 $ 0.2% 616.3% 543.95% 294.8% 148,880,000 11% 8,054,600,000 5.1% 31.8% 2,563,572,324 5,491,027,676 $ $ $ $ 0.79% 28.10% 5,664,644 $ 8,060,264,644 $ 1.8% 616.3% 543.95% 294.8% 165,331,240 11% 8,944,633,300 11% 31.8% 2,846,847,066 6,097,786,234 $ $ $ $ 0.79% 28.10% 6,290,587 $ 8,950,923,887 $ 1.8% 616.3% 543.95% 294.8% 183,600,342 11% 9,933,015,280 11% 31.8% 3,161,423,667 6,771,591,613 $ $ $ $ 0.79% 28.10% 6,985,697 $ 9,940,000,977 $ 1.8% $ 154.65% 11.59% 143.05% 8,274,020,084 $ 83.60% 11.59% 72.00% 8,555,397,287 $ $ $ $ $ $ $ 7,663,600,000 (827,515,849) (730,380,038) 897,892,661 395,851,181 7,399,447,955 8,054,600,000 (101,387,932) (89,486,772) 110,010,558 48,500,017 8,022,235,871 8,944,633,300 (112,591,299) (99,375,060) 122,166,725 53,859,269 8,908,692,935 9,933,015,280 (125,032,637) (110,356,004) 135,666,148 59,810,718 9,893,103,504 0.896 $ $ $ $ $ $ 0.803 $ $ $ $ $ $ 0.720 $ $ $ $ 0.79% 28.10% 7,757,617 $ 11,038,371,085 $ 1.8% 616.3% 543.95% 294.8% 2750.06% 11.59% 2738.47% 8,020,641,242 $ $ $ $ $ $ $ 203,888,180 11% 11,030,613,468 11% 31.8% 3,510,760,982 7,519,852,486 616.3% 543.95% 294.8% 26668.99% 11.59% 26657.39% 7,660,268,219 $ Discount factor $ $ $ $ $ $ 0.645 $ $ $ $ 0.79% 28.10% 8,614,834 $ 12,258,111,090 $ 1.8% 616.3% 543.95% 294.8% 59.13% 11.59% 47.54% 8,867,866,672 $ 11,030,613,468 (138,848,744) (122,550,343) 150,657,257 66,419,802 10,986,291,441 226,417,824 11% 12,249,496,256 11% 31.8% 3,898,700,070 8,350,796,186 $ $ $ $ $ $ 0.578 $ $ $ $ 0.79% 28.10% 9,566,773 $ 13,612,632,365 $ 1.8% 616.3% 543.95% 294.8% 46.80% 11.59% 35.21% 9,214,863,923 $ 12,249,496,256 (154,191,530) (136,092,155) 167,304,884 73,759,191 12,200,276,645 251,436,993 11% 13,603,065,593 11% 31.8% 4,329,506,428 9,273,559,165 $ $ $ $ $ $ 0.518 $ $ $ $ 0.79% 28.10% 10,623,901 $ 15,116,828,242 $ 1.8% 616.3% 543.95% 294.8% 39.40% 11.59% 27.81% 9,600,204,371 $ 13,603,065,593 (171,229,694) (151,130,339) 185,792,074 81,909,581 13,548,407,215 279,220,781 11% 15,106,204,341 11% 31.8% 4,807,916,888 10,298,287,452 $ $ $ $ $ $ 0.464 310,074,677 11% 16,775,439,920 11% 31.8% 5,339,191,704 11,436,248,216 $ $ $ $ 0.79% 28.10% 11,797,842 $ 16,787,237,762 $ 1.8% 616.3% 543.95% 294.8% 344,337,929 11% 18,629,126,031 11% 31.8% 5,929,172,388 12,699,953,644 $ $ 382,387,270 11% 20,687,644,458 11% 31.8% 6,584,345,937 14,103,298,521 0.79% 28.10% 13,101,504 $ 18,642,227,535 $ 1.8% 0.79% 28.10% 14,549,220 20,702,193,678 1.8% 31.01% 11.59% 19.42% 10,503,330,729 $ 28.43% 11.59% 16.83% 11,031,046,759 $ 15,106,204,341 (190,150,575) (167,830,241) 206,322,098 90,960,590 15,045,506,212 16,775,439,920 (211,162,214) (186,375,483) 229,120,690 101,011,735 16,708,034,648 18,629,126,031 (234,495,638) (206,969,973) 254,438,526 112,173,532 18,554,272,477 $ $ $ $ $ $ 0.416 $ $ $ $ $ $ 0.373 0.334 6,864,380,996 $ 6,440,563,981 $ 5,953,725,159 $ 5,516,578,435 $ 5,123,965,235 $ 4,771,262,929 $ 4,454,329,151 $ 4,169,451,894 $ 3,913,304,803 $ 3,682,907,105 Free cash flow to equity present value $ 6,630,659,459 $ 6,441,844,466 $ 6,410,415,822 $ 6,379,140,513 $ 6,348,017,791 $ 6,317,046,911 $ 6,286,227,133 $ 6,255,557,719 $ 6,225,037,936 $ 6,194,667,054 Terminal value calculations NO abnormal returns and hence no terminal value Competitive equilibrium assumption only on incremental sales Abnormal earnings in terminal year remain constant forever Terminal value $ - Final Year Abnormal Earnings $ Cost of equity Abnormal Earnings Terminal Value $ Present Value of Abnormal earnings terminal value $ 11,031,046,759 11.59% 95,140,750,608 31,764,396,800 Final year Free cash flow to equity $ Free cash flow terminal value $ Present value of free cash flow to equity terminal value $ Abnormal earnings and growth can be sustained forever $ 34.49% 11.59% 22.90% 10,028,124,939 $ $ Competitive equilibrium assumption $ 616.3% 543.95% 294.8% Abnormal earnings present value Persistent abnormal performance and growth $ $ $ $ 2,356,628,113 2,080,001,407 4,436,629,520 31-Dec-16 Working capital % Sales Long term assets % Sales Other long term liabilities % Sales Return on equity Cost of equity Abnormal ROE Abnormal Earnings $ $ $ $ 1,131,517,080 $ 998,696,870 $ 2,130,213,950 $ 57.6% Income Statement, Year ending Sales Growth rate Net Income to Common Growth Rate Dividend payout rate Dividends paid Retained income 31-Dec-26 18,554,272,477 160,027,189,535 53,427,864,661 Final Year Abnormal Earnings $ Cost of equity Sustained growth rate Abnormal Earnings Terminal Value $ Present Value of Abnormal earnings terminal value $ 11,031,046,759 11.59% 11.05% 2,026,089,954,787 676,445,422,863 Final year Free cash flow to equity $ Free cash flow terminal value Present value of free cash flow to equity terminal value 18,554,272,477 3,407,892,823,415 1,137,784,379,493 616.3% 543.95% 294.8% 26.44% 11.59% 14.85% 11,617,075,410 Valuation Summary DDM Valuation Required return on equity Constant dividend growth rate Current year dividend per share DDM Value per share $ $ 11.59% 11.05% 0.60 110.20 Abnormal Earnings Growth Model Beginning shareholders' equity Present value of forecast period abnormal earnings Terminal Value - average of three methods Total equity value Shares outstanding Per share value $ $ $ $ $ 28,736,000 50,890,469,688 236,069,939,887 286,989,145,575 3,910,000,000 73.40 Free Cash Flow to Equity Model Present value of forecast period free cash flow to equity Terminal value - average of two methods Total equity value Shares outstanding Per share value $ $ $ $ 63,488,614,806 595,606,122,077 659,094,736,883 3,910,000,000 168.57 Average Value Current stock price $ 117.39 $ 13.04 Financial Analysis and Valuation Financial valuation template for Students. This template will form the basis for the financial valuation of a company of your choice. The template uses Vodafone PLC as the target company for purposes of demonstrating the use of the model. An important data source for company specific information is Yahoo!Finance. Students should familiarize themselves with the extensive data available on Yahoo!Finance (or other sites like FT.COM, Google Finance, DailyFinance, etc.). The template specifies the Yahoo! Finance pages from which information can be retrieved. Spreadsheet cells with green text require input. Spreadsheet cells with black text are generally calculated. The Inputs tab is where most assumption data must be entered. The Forecasts tab is generally calculated, but there are a number of forecast assumptions that may be adjusted as appropriate. The valuation summary tab provides the equity valuation summary. The base model applies excess cash flow to debt reduction, as indicated by a 0 in the box to the right TO CHANGE THAT ASSUMPTION TO A CONSTANT DEBT RATIO, Enter 1 in the box to the right. Excess cash flow is then allocated to repurchasing stock in the future. 0 Vodafone PLC Valuation 4/27/2016 Data source: Yahoo!Finance under COMPANY > Key Statistics Ticker: Current stock price: Stock price date Forward annual dividend rate Dividend yield Shares outstanding Equity market capitalization Beta $ $ $ VOD 25.69 10/12/2010 1.70 6.62% 5,270,000,000 135,386,300,000 0.74 Capital Structure Costs Risk free rate (10 year Treasury) Equity risk premium (Historical average assumed) Cost of equity Net debt Net interest cost Cost of debt, pre tax Cost of debt, after tax 2.38% 7.00% 7.56% $ $ 54,305,000,000 2,294,000,000 4.22% 4.20% Data source: Yahoo!Finance underFINANCIALS > Balance Sheet Balance sheet data for most recent fiscal year end 3/31/2010 Data source: Yahoo!Finance under FINANCIALS > Income Statement Income statement, fiscal year ended 3/31/2010 Cash, cash equivalents, and short term investments Net receivables Inventory Other current assets Total current assets $ $ $ $ $ 7,298,000,000 13,614,000,000 657,000,000 21,569,000,000 Sales $ 67,461,000,000 Long term investments Property, plant, and equipment Goodwill and intangible assets Other assets Total long term assets $ $ $ $ $ 71,042,000,000 31,312,000,000 112,644,000,000 1,567,000,000 216,565,000,000 Earnings before interest and taxes Interest expense, net Income before taxation Tax expense Net income from continuing operations Non-recurrent income (loss) Preferred stock dividends Net income available to common stock $ $ $ $ $ $ $ $ 15,452,000,000 2,294,000,000 13,158,000,000 85,000,000 13,073,000,000 13,073,000,000 Diluted earnings per share Net income as a % of sales Tax rate NOPAT NOPAT Margin Dividend payout rate $ 2.48 19.4% 0.6% 15,352,180,879 22.8% 68.5% Total assets $ 238,134,000,000 Accounts payable Short term debt Other current liabilities Total current liabilities $ $ $ $ 25,721,000,000 16,933,000,000 754,000,000 43,408,000,000 Long term debt Other liabilities Deferred long term liabilities Minority interest Total long term liablitieis $ $ $ $ $ 44,670,000,000 1,114,000,000 11,190,000,000 651,000,000 57,625,000,000 Stockholders' equity $ 137,101,000,000 Total liabilities & equity $ $ Data source: Yahoo!Finance under ANALYST COVERAGE > Estimates Analyst Estimate Data Current Year End 3/31/2011 EPS Average estimate $ 2.53 Revenue Estimate $ 70,170,000,000 Next Year End EPS Average estimate Revenue estimate Net working capital Other long term liabilities - $ $ 3/31/2012 2.60 69,860,000,000 238,134,000,000 Check (assets - liabilities and equity) $ Next 5 years growth rate (per annum) Calculations required for other tabs in the spreadsheet $ (12,204,000,000) Current year estimated net income $ 12,955,000,000 Next year estimated net incom 1.0% $ $ 13,333,100,000.00 13,702,000,000.00 Fiscal year ending 3/31/2011 3/31/2012 3/31/2013 3/31/2014 3/31/2015 3/31/2016 3/31/2017 3/31/2018 3/31/2019 3/31/2020 3/31/2021 Note: Column necessary to calculate free cash flow to equity for final year Balance sheet at beginning of fiscal year Net working capital Long term assets Net operating assets $ (12,204,000,000) $ (12,150,084,652) $ (12,271,585,498) $ (12,394,301,353) $ (12,518,244,367) $ (12,643,426,810) $ (12,769,861,078) $ (12,897,559,689) $ (13,026,535,286) $ (13,156,800,639) $ (13,288,368,645) $ 216,565,000,000 $ 215,608,249,964 $ 217,764,332,464 $ 219,941,975,789 $ 222,141,395,547 $ 224,362,809,502 $ 226,606,437,597 $ 228,872,501,973 $ 231,161,226,993 $ 233,472,839,263 $ 235,807,567,655 $ 204,361,000,000 $ 203,458,165,313 $ 205,492,746,966 $ 207,547,674,436 $ 209,623,151,180 $ 211,719,382,692 $ 213,836,576,519 $ 215,974,942,284 $ 218,134,691,707 $ 220,316,038,624 $ 222,519,199,010 Net debt Other long term liabilities Shareholders' equity Net capital $ 54,305,000,000 $ 49,263,546,445 $ 46,857,207,647 $ 44,426,805,461 $ 41,972,099,253 $ 39,492,845,983 $ 36,988,800,180 $ 34,459,713,919 $ 31,905,336,795 $ 29,325,415,901 $ 26,719,695,797 $ 12,955,000,000 $ 12,897,766,852 $ 13,026,744,520 $ 13,157,011,966 $ 13,288,582,085 $ 13,421,467,906 $ 13,555,682,585 $ 13,691,239,411 $ 13,828,151,805 $ 13,966,433,323 $ 14,106,097,656 $ 137,101,000,000 $ 141,296,852,016 $ 145,608,794,798 $ 149,963,857,009 $ 154,362,469,842 $ 158,805,068,803 $ 163,292,093,754 $ 167,823,988,954 $ 172,401,203,106 $ 177,024,189,400 $ 181,693,405,557 $ 204,361,000,000 $ 203,458,165,313 $ 205,492,746,966 $ 207,547,674,436 $ 209,623,151,180 $ 211,719,382,692 $ 213,836,576,519 $ 215,974,942,284 $ 218,134,691,707 $ 220,316,038,624 $ 222,519,199,010 Net debt % Net capital 26.6% Sales Growth rate Net Income to Common Growth Rate Dividend payout rate Dividends paid Retained income Cost of debt, pretax Estimated tax rate After tax interest expense NOPAT NOPAT Margin $ $ $ $ $ $ 22.8% 21.4% 20.0% 18.7% 17.3% 16.0% 14.6% 13.3% 12.0% 3/31/2011 Income Statement, Year ending 24.2% 3/31/2012 3/31/2013 3/31/2014 3/31/2015 3/31/2016 3/31/2017 3/31/2018 3/31/2019 3/31/2020 3/31/2021 70,170,000,000 NM 13,333,100,000 NM 68.5% 9,137,247,984 4,195,852,016 $ $ $ $ 4.22% 0.65% 2,279,180,879 $ 15,612,280,879 $ 22.2% Working capital % Sales Long term assets % Sales Other long term liabilities % Sales -17.4% 308.63% 18.5% Return on equity Cost of equity Abnormal ROE Abnormal Earnings $ Net income (Increase) / Decrease in net working capital (Increase) / Decrease in long term assets Increase / (Decrease) in net debt Increase / (Decrease) in other liabilities Free cash flow to equity $ $ $ $ $ $ Discount factor $ $ $ $ 4.22% 0.65% 2,067,591,070 $ 15,769,591,070 $ 22.6% -17.4% 308.63% 18.5% 9.73% 7.56% 2.17% 2,968,264,400 $ 13,333,100,000 (53,915,348) 956,750,036 (5,041,453,555) (57,233,148) 9,137,247,984 69,860,000,000 -0.4% 13,702,000,000 2.8% 68.5% 9,390,057,217 4,311,942,783 $ $ $ $ $ $ 0.930 $ $ $ $ 4.22% 0.65% 1,966,597,030 $ 15,805,617,030 $ 22.4% -17.4% 308.63% 18.5% 9.70% 7.56% 2.14% 3,019,957,988 $ 13,702,000,000 121,500,847 (2,156,082,500) (2,406,338,798) 128,977,669 9,390,057,217 70,558,600,000 1% 13,839,020,000 1% 68.5% 9,483,957,789 4,355,062,211 $ $ $ $ $ $ 0.864 $ $ $ $ 4.22% 0.65% 1,864,593,049 $ 15,842,003,249 $ 22.2% 71,976,827,860 1% 14,117,184,302 1% 68.5% 9,674,585,341 4,442,598,961 $ $ $ $ $ $ 0.804 $ $ $ -17.4% 308.63% 18.5% 9.32% 7.56% 1.76% 2,640,142,610 $ 13,977,410,200 123,943,014 (2,199,419,758) (2,454,706,208) 131,570,120 9,578,797,367 $ 4.22% 0.65% 1,761,569,028 $ 15,878,753,330 $ 22.1% -17.4% 308.63% 18.5% 9.50% 7.56% 1.94% 2,830,995,113 $ 13,839,020,000 122,715,855 (2,177,643,325) (2,430,402,186) 130,267,445 9,483,957,789 71,264,186,000 1% 13,977,410,200 1% 68.5% 9,578,797,367 4,398,612,833 $ $ $ $ $ $ 0.747 $ $ $ $ 4.22% 0.65% 1,657,514,767 $ 15,915,870,912 $ 21.9% -17.4% 308.63% 18.5% 9.15% 7.56% 1.59% 2,447,381,582 $ 14,117,184,302 125,182,444 (2,221,413,955) (2,479,253,270) 132,885,821 9,674,585,341 72,696,596,139 1% 14,258,356,145 1% 68.5% 9,771,331,194 4,487,024,951 $ $ $ $ $ $ 0.695 $ $ $ $ 4.22% 0.65% 1,552,419,963 $ 15,953,359,669 $ 21.7% -17.4% 308.63% 18.5% 8.98% 7.56% 1.42% 2,252,692,944 $ 14,258,356,145 126,434,268 (2,243,628,095) (2,504,045,803) 134,214,679 9,771,331,194 73,423,562,100 1% 14,400,939,706 1% 68.5% 9,869,044,506 4,531,895,200 $ $ $ $ $ $ 0.646 $ $ $ $ 4.22% 0.65% 1,446,274,211 $ 15,991,223,315 $ 21.6% -17.4% 308.63% 18.5% 8.82% 7.56% 1.26% 2,056,057,419 $ 14,400,939,706 127,698,611 (2,266,064,376) (2,529,086,261) 135,556,826 9,869,044,506 74,157,797,721 1% 14,544,949,104 1% 68.5% 9,967,734,951 4,577,214,152 $ $ $ $ $ $ 0.600 $ $ $ $ 4.22% 0.65% 1,339,067,002 $ 16,029,465,597 $ 21.4% -17.4% 308.63% 18.5% 8.67% 7.56% 1.11% 1,857,455,539 $ 14,544,949,104 128,975,597 (2,288,725,020) (2,554,377,123) 136,912,394 9,967,734,951 74,899,375,698 1% 14,690,398,595 1% 68.5% 10,067,412,301 4,622,986,294 $ $ $ $ $ $ 0.558 $ $ 76,404,853,150 1% 14,985,675,606 1% 68.5% 10,269,767,288 4,715,908,318 4.22% 0.65% 1,230,787,721 $ 16,068,090,301 $ 21.2% 4.22% 0.65% 1,121,425,647 16,107,101,253 21.1% $ $ $ $ $ $ 0.519 14,837,302,581 131,568,006 (2,334,728,393) (2,605,720,104) 139,664,333 10,168,086,424 0.482 $ 2,759,635,924 $ 2,610,353,438 $ 2,275,027,963 $ 1,972,532,703 $ 1,699,995,296 $ 1,454,779,717 $ 1,234,467,566 $ 1,036,840,800 $ 859,865,796 $ 701,678,651 Free cash flow to equity present value $ 8,495,024,158 $ 8,116,459,978 $ 7,621,443,453 $ 7,156,617,597 $ 6,720,141,105 $ 6,310,284,972 $ 5,925,425,643 $ 5,564,038,583 $ 5,224,692,236 $ 4,906,042,356 Terminal value calculations NO abnormal returns and hence no terminal value Competitive equilibrium assumption only on incremental sales Abnormal earnings in terminal year remain constant forever Terminal value $ Final Year Abnormal Earnings $ Cost of equity Abnormal Earnings Terminal Value $ Present Value of Abnormal earnings terminal value $ 1,454,273,862 7.56% 19,236,426,745 9,281,463,636 Final year Free cash flow to equity $ 10,168,086,424 Free cash flow terminal value $ 134,498,497,670 Present value of free cash flow to equity terminal value $ 64,894,740,159 Persistent abnormal performance and growth Abnormal earnings and growth can be sustained forever $ 8.38% 7.56% 0.82% 1,454,273,862 $ Abnormal earnings present value Competitive equilibrium assumption $ -17.4% 308.63% 18.5% 8.52% 7.56% 0.96% 1,656,867,640 $ 14,690,398,595 130,265,353 (2,311,612,270) (2,579,920,895) 138,281,518 10,067,412,301 75,648,369,455 1% 14,837,302,581 1% 68.5% 10,168,086,424 4,669,216,157 Final Year Abnormal Earnings $ Cost of equity Sustained growth rate Abnormal Earnings Terminal Value $ Present Value of Abnormal earnings terminal value $ 1,454,273,862 7.56% 1.00% 22,168,808,870 10,696,320,897 Final year Free cash flow to equity $ 10,168,086,424 Free cash flow terminal value 155,001,317,437 Present value of free cash flow to equity terminal value 74,787,231,037 -17.4% 308.63% 18.5% 8.25% 7.56% 0.69% 1,249,654,146 Valuation Summary DDM Valuation Required return on equity Constant dividend growth rate Current year dividend per share DDM Value per share $ $ 7.56% 1.00% 1.70 25.91 Abnormal Earnings Growth Model Beginning shareholders' equity Present value of forecast period abnormal earnings Terminal Value - average of three methods Total equity value Shares outstanding Per share value $ 137,101,000,000 $ 16,605,177,854 $ 6,659,261,511 $ 160,365,439,365 5,270,000,000 $ 30.43 Free Cash Flow to Equity Model Present value of forecast period free cash flow to equity Terminal value - average of two methods Total equity value Shares outstanding Per share value $ 66,040,170,081 $ 69,840,985,598 $ 135,881,155,679 5,270,000,000 $ 25.78 Average Value Current stock price $ $ 27.38 25.69 \fFinancial Analysis and Valuation Ford Motor Co. This template will form the basis for the financial valuation of a company of your choice. The template uses Ford Motor Co. as the target company for purposes of demonstrating the use of the model. An important data source for company specific information is Yahoo!Finance. Students should familiarize themselves with the extensive data available on Yahoo!Finance (or other sites like FT.COM, Google Finance, DailyFinance, etc.). The template specifies the Yahoo! Finance pages from which information can be retrieved. Spreadsheet cells with green text require input. Spreadsheet cells with black text are generally calculated. The Inputs tab is where most assumption data must be entered. The Forecasts tab is generally calculated, but there are a number of forecast assumptions that may be adjusted as appropriate. The valuation summary tab provides the equity valuation summary. The base model applies excess cash flow to debt reduction, as indicated by a 0 in the box to the right TO CHANGE THAT ASSUMPTION TO A CONSTANT DEBT RATIO, Enter 1 in the box to the right. Excess cash flow is then allocated to repurchasing stock in the future. 1 Ford Motor Co. 27-Apr-16 Data source: Yahoo!Finance under COMPANY > Key Statistics Ticker: Current stock price: Stock price date Forward annual dividend rate Dividend yield Shares outstanding Equity market capitalization Beta $ $ $ Capital Structure Costs F 13.04 13-Apr-16 0.60 4.60% 3,910,000,000 50,986,400,000 1.31635 Risk free rate (10 year Treasury) Equity risk premium (Historical average assumed) Cost of equity Net debt Net interest cost Cost of debt, pre tax Cost of debt, after tax 2.38% 7.00% 11.59% $ $ 97,678,000 773,000 0.79% 0.57% Data source: Yahoo!Finance underFINANCIALS > Balance Sheet Balance sheet data for most recent fiscal year end 31-Dec-15 Data source: Yahoo!Finance under FINANCIALS > Income Statement Income statement, fiscal year ended 31-Dec-15 Cash, cash equivalents, and short term investments Net receivables Inventory Other current assets Total current assets $ $ $ $ $ 35,176,000 101,975,000 8,319,000 145,470,000 Sales $ 149,558,000 Long term investments Property, plant, and equipment Goodwill and intangible assets Other assets + Deferred Long Term Asset Charges Total long term assets $ $ $ $ $ 3,224,000 57,256,000 18,975,000 79,455,000 Earnings before interest and taxes Interest expense, net Income before taxation Tax expense Net income from continuing operations Non-recurrent income (loss) + Minority Interest (gain) Preferred stock dividends Net income available to common stock $ $ $ $ $ $ $ $ 11,025,000 773,000 10,252,000 2,881,000 7,371,000 2,000 7,373,000 Diluted earnings per share Net income as a % of sales Tax rate NOPAT NOPAT Margin Dividend payout rate $ 1.89 4.9% 28.1% 7,928,773 5.3% 32% Total assets $ 224,925,000 Accounts payable Short term debt Other current liabilities Total current liabilities $ $ $ $ 20,272,000 20,272,000 Long term debt Other liabilities Deferred long term liabilities Minority interest Total long term liablitieis $ $ $ $ $ 132,854,000 42,546,000 502,000 15,000 175,917,000 $ Data source: Yahoo!Finance under ANALYST COVERAGE > Estimates Analyst Estimate Data Current Year End 31-Dec-16 EPS Average estimate $ 1.96 Revenue Estimate $ 14,607,000 Next Year End EPS Average estimate Revenue estimate Stockholders' equity + Misc Stocks Options Warrants $ 28,736,000 Total liabilities & equity $ 224,925,000 Check (assets - liabilities and equity) $ Net working capital Other long term liabilities Calculations required for other tabs in the spreadsheet $ 90,022,000 Current year estimated net income $ 43,063,000 Next year estimated net incom - $ $ 12/31/2017 2.06 148,880,000 Next 5 years growth rate (per annum) 11.1% $ $ 7,663,600,000.00 8,054,600,000.00 Fiscal year ending 31-Dec-16 31-Dec-17 31-Dec-18 31-Dec-19 31-Dec-20 31-Dec-21 31-Dec-22 31-Dec-23 31-Dec-24 31-Dec-25 Note: Column necessary to calculate free cash flow to equity for final year Balance sheet at beginning of fiscal year Net working capital Long term assets Net operating assets $ $ $ 90,022,000 $ 79,455,000 $ 169,477,000 $ 917,537,849 $ 809,835,038 $ 1,727,372,887 $ 1,018,925,781 $ 899,321,810 $ 1,918,247,591 $ Net debt Other long term liabilities Shareholders' equity Net capital $ $ $ $ 97,678,000 43,063,000 28,736,000 169,477,000 995,570,661 438,914,181 292,888,045 1,727,372,887 1,105,581,219 487,414,198 5,783,915,721 1,918,247,591 Net debt % Net capital Cost of debt, pretax Estimated tax rate After tax interest expense NOPAT NOPAT Margin Net income (Increase) / Decrease in net working capital (Increase) / Decrease in long term assets Increase / (Decrease) in net debt Increase / (Decrease) in other liabilities Free cash flow to equity $ $ $ $ $ $ $ $ 1,227,747,943 541,273,467 11,881,701,956 2,130,213,950 $ $ $ $ 1,256,549,717 $ 1,109,052,874 $ 2,365,602,591 $ 1,363,414,091 601,084,185 18,653,293,569 2,365,602,591 $ $ $ $ 1,395,398,461 $ 1,231,603,216 $ 2,627,001,678 $ 1,514,071,348 667,503,987 26,173,146,055 2,627,001,678 $ $ $ $ 1,549,589,991 $ 1,367,695,372 $ 2,917,285,363 $ 1,681,376,232 741,263,178 34,523,942,241 2,917,285,363 $ $ $ $ 1,720,819,685 $ 1,518,825,710 $ 3,239,645,396 $ 1,867,168,306 823,172,759 43,797,501,406 3,239,645,396 $ $ $ $ 1,910,970,260 $ 1,686,655,951 $ 3,597,626,212 $ 2,073,490,403 914,133,349 54,095,788,858 3,597,626,212 $ $ $ $ 2,122,132,474 $ 1,873,031,434 $ 3,995,163,908 $ 2,302,611,093 1,015,145,084 65,532,037,074 3,995,163,908 $ $ $ $ $ $ 2,557,049,619 1,127,318,616 78,231,990,718 4,436,629,520 57.6% 57.6% 57.6% 57.6% 57.6% 57.6% 57.6% 57.6% 57.6% 57.6% 31-Dec-17 31-Dec-18 31-Dec-19 31-Dec-20 31-Dec-21 31-Dec-22 31-Dec-23 31-Dec-24 31-Dec-25 31-Dec-26 14,607,000 NM 7,663,600,000 NM 31.8% 2,439,127,066 5,224,472,934 $ $ $ $ 0.79% 28.10% 555,773 $ 7,664,155,773 $ 0.2% 616.3% 543.95% 294.8% 148,880,000 11% 8,054,600,000 5.1% 31.8% 2,563,572,324 5,491,027,676 $ $ $ $ 0.79% 28.10% 5,664,644 $ 8,060,264,644 $ 1.8% 616.3% 543.95% 294.8% 165,331,240 11% 8,944,633,300 11% 31.8% 2,846,847,066 6,097,786,234 $ $ $ $ 0.79% 28.10% 6,290,587 $ 8,950,923,887 $ 1.8% 616.3% 543.95% 294.8% 183,600,342 11% 9,933,015,280 11% 31.8% 3,161,423,667 6,771,591,613 $ $ $ $ 0.79% 28.10% 6,985,697 $ 9,940,000,977 $ 1.8% $ 154.65% 11.59% 143.05% 8,274,020,084 $ 83.60% 11.59% 72.00% 8,555,397,287 $ $ $ $ $ $ $ 7,663,600,000 (827,515,849) (730,380,038) 897,892,661 395,851,181 7,399,447,955 8,054,600,000 (101,387,932) (89,486,772) 110,010,558 48,500,017 8,022,235,871 8,944,633,300 (112,591,299) (99,375,060) 122,166,725 53,859,269 8,908,692,935 9,933,015,280 (125,032,637) (110,356,004) 135,666,148 59,810,718 9,893,103,504 0.896 $ $ $ $ $ $ 0.803 $ $ $ $ $ $ 0.720 $ $ $ $ 0.79% 28.10% 7,757,617 $ 11,038,371,085 $ 1.8% 616.3% 543.95% 294.8% 2750.06% 11.59% 2738.47% 8,020,641,242 $ $ $ $ $ $ $ 203,888,180 11% 11,030,613,468 11% 31.8% 3,510,760,982 7,519,852,486 616.3% 543.95% 294.8% 26668.99% 11.59% 26657.39% 7,660,268,219 $ Discount factor $ $ $ $ $ $ 0.645 $ $ $ $ 0.79% 28.10% 8,614,834 $ 12,258,111,090 $ 1.8% 616.3% 543.95% 294.8% 59.13% 11.59% 47.54% 8,867,866,672 $ 11,030,613,468 (138,848,744) (122,550,343) 150,657,257 66,419,802 10,986,291,441 226,417,824 11% 12,249,496,256 11% 31.8% 3,898,700,070 8,350,796,186 $ $ $ $ $ $ 0.578 $ $ $ $ 0.79% 28.10% 9,566,773 $ 13,612,632,365 $ 1.8% 616.3% 543.95% 294.8% 46.80% 11.59% 35.21% 9,214,863,923 $ 12,249,496,256 (154,191,530) (136,092,155) 167,304,884 73,759,191 12,200,276,645 251,436,993 11% 13,603,065,593 11% 31.8% 4,329,506,428 9,273,559,165 $ $ $ $ $ $ 0.518 $ $ $ $ 0.79% 28.10% 10,623,901 $ 15,116,828,242 $ 1.8% 616.3% 543.95% 294.8% 39.40% 11.59% 27.81% 9,600,204,371 $ 13,603,065,593 (171,229,694) (151,130,339) 185,792,074 81,909,581 13,548,407,215 279,220,781 11% 15,106,204,341 11% 31.8% 4,807,916,888 10,298,287,452 $ $ $ $ $ $ 0.464 310,074,677 11% 16,775,439,920 11% 31.8% 5,339,191,704 11,436,248,216 $ $ $ $ 0.79% 28.10% 11,797,842 $ 16,787,237,762 $ 1.8% 616.3% 543.95% 294.8% 344,337,929 11% 18,629,126,031 11% 31.8% 5,929,172,388 12,699,953,644 $ $ 382,387,270 11% 20,687,644,458 11% 31.8% 6,584,345,937 14,103,298,521 0.79% 28.10% 13,101,504 $ 18,642,227,535 $ 1.8% 0.79% 28.10% 14,549,220 20,702,193,678 1.8% 31.01% 11.59% 19.42% 10,503,330,729 $ 28.43% 11.59% 16.83% 11,031,046,759 $ 15,106,204,341 (190,150,575) (167,830,241) 206,322,098 90,960,590 15,045,506,212 16,775,439,920 (211,162,214) (186,375,483) 229,120,690 101,011,735 16,708,034,648 18,629,126,031 (234,495,638) (206,969,973) 254,438,526 112,173,532 18,554,272,477 $ $ $ $ $ $ 0.416 $ $ $ $ $ $ 0.373 0.334 6,864,380,996 $ 6,440,563,981 $ 5,953,725,159 $ 5,516,578,435 $ 5,123,965,235 $ 4,771,262,929 $ 4,454,329,151 $ 4,169,451,894 $ 3,913,304,803 $ 3,682,907,105 Free cash flow to equity present value $ 6,630,659,459 $ 6,441,844,466 $ 6,410,415,822 $ 6,379,140,513 $ 6,348,017,791 $ 6,317,046,911 $ 6,286,227,133 $ 6,255,557,719 $ 6,225,037,936 $ 6,194,667,054 Terminal value calculations NO abnormal returns and hence no terminal value Competitive equilibrium assumption only on incremental sales Abnormal earnings in terminal year remain constant forever Terminal value $ - Final Year Abnormal Earnings $ Cost of equity Abnormal Earnings Terminal Value $ Present Value of Abnormal earnings terminal value $ 11,031,046,759 11.59% 95,140,750,608 31,764,396,800 Final year Free cash flow to equity $ Free cash flow terminal value $ Present value of free cash flow to equity terminal value $ Abnormal earnings and growth can be sustained forever $ 34.49% 11.59% 22.90% 10,028,124,939 $ $ Competitive equilibrium assumption $ 616.3% 543.95% 294.8% Abnormal earnings present value Persistent abnormal performance and growth $ $ $ $ 2,356,628,113 2,080,001,407 4,436,629,520 31-Dec-16 Working capital % Sales Long term assets % Sales Other long term liabilities % Sales Return on equity Cost of equity Abnormal ROE Abnormal Earnings $ $ $ $ 1,131,517,080 $ 998,696,870 $ 2,130,213,950 $ 57.6% Income Statement, Year ending Sales Growth rate Net Income to Common Growth Rate Dividend payout rate Dividends paid Retained income 31-Dec-26 18,554,272,477 160,027,189,535 53,427,864,661 Final Year Abnormal Earnings $ Cost of equity Sustained growth rate Abnormal Earnings Terminal Value $ Present Value of Abnormal earnings terminal value $ 11,031,046,759 11.59% 11.05% 2,026,089,954,787 676,445,422,863 Final year Free cash flow to equity $ Free cash flow terminal value Present value of free cash flow to equity terminal value 18,554,272,477 3,407,892,823,415 1,137,784,379,493 616.3% 543.95% 294.8% 26.44% 11.59% 14.85% 11,617,075,410 Valuation Summary DDM Valuation Required return on equity Constant dividend growth rate Current year dividend per share DDM Value per share $ $ 11.59% 11.05% 0.60 110.20 Abnormal Earnings Growth Model Beginning shareholders' equity Present value of forecast period abnormal earnings Terminal Value - average of three methods Total equity value Shares outstanding Per share value $ $ $ $ $ 28,736,000 50,890,469,688 236,069,939,887 286,989,145,575 3,910,000,000 73.40 Free Cash Flow to Equity Model Present value of forecast period free cash flow to equity Terminal value - average of two methods Total equity value Shares outstanding Per share value $ $ $ $ 63,488,614,806 595,606,122,077 659,094,736,883 3,910,000,000 168.57 Average Value Current stock price $ 117.39 $ 13.04