This template will form the basis for the financial valuation of a company of your choice.

| March 30, 2017

Question
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 1
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.

Tesla Motors, Inc. 2/18/2016
Data source: Yahoo!Finance under COMPANY > Key Statistics Capital Structure Costs
Ticker: TSLA Risk free rate (10 year Treasury) 2.40%
Current stock price: $ 182.78 Equity risk premium (Historical average assumed) 7.00%
Stock price date 2/2/2016 Cost of equity 10.87%
Forward annual dividend rate $ –
Dividend yield 0.00% Net debt $ 506,223
Net interest cost $ 100,886
Shares outstanding 130,950,000 Cost of debt, pre tax 19.93%
Equity market capitalization $ 23,935,041,000 Cost of debt, after tax 12.95%
Beta 1.21
Data source: Yahoo!Finance underFINANCIALS > Balance Sheet Data source: Yahoo!Finance under FINANCIALS > Income Statement
Balance sheet data for most recent fiscal year end 12/31/2014 Income statement, fiscal year ended 12/31/2014
Cash, cash equivalents, and short term investments $ 1,923,660 Sales $ 3,198,356
Net receivables $ 226,604
Inventory $ 953,675 Earnings before interest and taxes $ (183,750)
Other current assets $ – Interest expense, net $ 100,886
Total current assets $ 3,103,939 Income before taxation $ (284,636)
Tax expense $ (284,636)
Long term investments $ – Net income from continuing operations $ –
Property, plant, and equipment $ 2,596,011 Non-recurrent income (loss) $ –
Goodwill and intangible assets $ – Preferred stock dividends $ –
Other assets $ 54,583 Net income available to common stock $ –
Total long term assets $ 2,650,594
Diluted earnings per share $ –
Total assets $ 5,754,533 Net income as a % of sales 0.0%
Tax rate 35.0%
Accounts payable $ 1,046,830 NOPAT $ 65,576
Short term debt $ 611,098 NOPAT Margin 2.1%
Other current liabilities $ 449,238 Dividend payout rate 0.0%
Total current liabilities $ 2,107,166
Data source: Yahoo!Finance under ANALYST COVERAGE > Estimates
Long term debt $ 1,818,785 Analyst Estimate Data
Other liabilities $ 661,123 Current Year End 12/31/2015
Deferred long term liabilities $ 292,271 EPS Average estimate $ (1.28)
Minority interest $ – Revenue Estimate $ 5,350,000,000
Total long term liablitieis $ 2,772,179
Next Year End 12/31/2016
Stockholders’ equity $ 911,710 EPS Average estimate $ 1.76
Revenue estimate $ 8,620,000,000
Total liabilities & equity $ 5,791,055
Check (assets – liabilities and equity) $ (36,522) Next 5 years growth rate (per annum) 10.0%
Calculations required for other tabs in the spreadsheet
Net working capital $ (315,789) Current year estimated net income $ (167,616,000.00)
Other long term liabilities $ 953,394 Next year estimated net incom $ 230,472,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
Balance sheet at beginning of fiscal year Note: Column necessary to calculate free cash flow to equity for final year
Net working capital $ (315,789) $ (508,804) $ (559,684) $ (615,653) $ (677,218) $ (744,940) $ (819,434) $ (901,377) $ (991,515) $ (1,090,666) $ (1,199,733)
Long term assets $ 2,650,594 $ 4,270,677 $ 4,697,744 $ 5,167,519 $ 5,684,271 $ 6,252,698 $ 6,877,968 $ 7,565,764 $ 8,322,341 $ 9,154,575 $ 10,070,032
Net operating assets $ 2,334,805 $ 3,761,873 $ 4,138,060 $ 4,551,866 $ 5,007,053 $ 5,507,758 $ 6,058,534 $ 6,664,387 $ 7,330,826 $ 8,063,908 $ 8,870,299
Net debt $ 506,223 $ 803,072 $ 883,379 $ 971,717 $ 1,068,889 $ 1,175,778 $ 1,293,356 $ 1,422,691 $ 1,564,960 $ 1,721,456 $ 1,893,602
Other long term liabilities $ 953,394 $ 1,536,123 $ 1,689,735 $ 1,858,708 $ 2,044,579 $ 2,249,037 $ 2,473,941 $ 2,721,335 $ 2,993,469 $ 3,292,815 $ 3,622,097
Shareholders’ equity $ 911,710 $ 1,422,678 $ 231,894,678 $ 485,413,878 $ 764,284,998 $ 1,071,043,230 $ 1,408,477,285 $ 1,779,654,746 $ 2,187,949,953 $ 2,637,074,680 $ 3,131,111,880
Net capital $ 2,371,327 $ 3,761,873 $ 4,138,060 $ 4,551,866 $ 5,007,053 $ 5,507,758 $ 6,058,534 $ 6,664,387 $ 7,330,826 $ 8,063,908 $ 8,870,299
Net debt % Net capital 21.3% 21.3% 21.3% 21.3% 21.3% 21.3% 21.3% 21.3% 21.3% 21.3% 21.3%
Income Statement, 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
Sales $ 5,350,000,000 $ 8,620,000,000 $ 9,482,000,000 $ 10,430,200,000 $ 11,473,220,000 $ 12,620,542,000 $ 13,882,596,200 $ 15,270,855,820 $ 16,797,941,402 $ 18,477,735,542 $ 20,325,509,096
Growth rate NM 61.1% 10% 10% 10% 10% 10% 10% 10% 10% 10%
Net Income to Common $ (167,616,000) $ 230,472,000 $ 253,519,200 $ 278,871,120 $ 306,758,232 $ 337,434,055 $ 371,177,461 $ 408,295,207 $ 449,124,727 $ 494,037,200 $ 543,440,920
Growth Rate NM -237.5% 10% 10% 10% 10% 10% 10% 10% 10% 10%
Dividend payout rate 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Dividends paid $ – $ – $ – $ – $ – $ – $ – $ – $ – $ – $ –
Retained income $ (167,616,000) $ 230,472,000 $ 253,519,200 $ 278,871,120 $ 306,758,232 $ 337,434,055 $ 371,177,461 $ 408,295,207 $ 449,124,727 $ 494,037,200 $ 543,440,920
Cost of debt, pretax 19.93% 19.93% 19.93% 19.93% 19.93% 19.93% 19.93% 19.93% 19.93% 19.93% 19.93%
Estimated tax rate 35.00% 35.00% 35.00% 35.00% 35.00% 35.00% 35.00% 35.00% 35.00% 35.00% 35.00%
After tax interest expense $ 65,576 $ 104,030 $ 114,433 $ 125,876 $ 138,463 $ 152,310 $ 167,541 $ 184,295 $ 202,724 $ 222,997 $ 245,296
NOPAT $ (167,550,424) $ 230,576,030 $ 253,633,633 $ 278,996,996 $ 306,896,695 $ 337,586,365 $ 371,345,001 $ 408,479,502 $ 449,327,452 $ 494,260,197 $ 543,686,217
NOPAT Margin -3.1% 2.7% 2.7% 2.7% 2.7% 2.7% 2.7% 2.7% 2.7% 2.7% 2.7%
Working capital % Sales 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Long term assets % Sales 0.05% 0.05% 0.05% 0.05% 0.05% 0.05% 0.05% 0.05% 0.05% 0.05% 0.05%
Other long term liabilities % Sales 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Return on equity -18384.79% 16199.87% 109.33% 57.45% 40.14% 31.51% 26.35% 22.94% 20.53% 18.73% 17.36%
Cost of equity 10.87% 10.87% 10.87% 10.87% 10.87% 10.87% 10.87% 10.87% 10.87% 10.87% 10.87%
Abnormal ROE -18395.66% 16189.00% 98.46% 46.58% 29.27% 20.64% 15.48% 12.07% 9.66% 7.86% 6.49%
Abnormal Earnings $ (167,715,103) $ 230,317,355 $ 228,312,249 $ 226,106,631 $ 223,680,453 $ 221,011,656 $ 218,075,980 $ 214,846,736 $ 211,294,568 $ 207,387,182 $ 203,089,059
Net income $ (167,616,000) $ 230,472,000 $ 253,519,200 $ 278,871,120 $ 306,758,232 $ 337,434,055 $ 371,177,461 $ 408,295,207 $ 449,124,727 $ 494,037,200
(Increase) / Decrease in net working capital $ 193,015 $ 50,880 $ 55,968 $ 61,565 $ 67,722 $ 74,494 $ 81,943 $ 90,138 $ 99,151 $ 109,067
(Increase) / Decrease in long term assets $ (1,620,083) $ (427,068) $ (469,774) $ (516,752) $ (568,427) $ (625,270) $ (687,797) $ (756,576) $ (832,234) $ (915,457)
Increase / (Decrease) in net debt $ 296,849 $ 80,307 $ 88,338 $ 97,172 $ 106,889 $ 117,578 $ 129,336 $ 142,269 $ 156,496 $ 172,146
Increase / (Decrease) in other liabilities $ 582,729 $ 153,612 $ 168,973 $ 185,871 $ 204,458 $ 224,904 $ 247,394 $ 272,134 $ 299,347 $ 329,282
Free cash flow to equity $ (168,163,490) $ 230,329,732 $ 253,362,705 $ 278,698,976 $ 306,568,874 $ 337,225,761 $ 370,948,337 $ 408,043,171 $ 448,847,488 $ 493,732,237
Discount factor 0.902 0.814 0.734 0.662 0.597 0.538 0.486 0.438 0.395 0.356
Abnormal earnings present value $ (151,271,853) $ 187,369,358 $ 167,527,871 $ 149,643,242 $ 133,523,527 $ 118,995,597 $ 105,903,302 $ 94,105,798 $ 83,476,053 $ 73,899,489
Free cash flow to equity present value $ (151,676,279) $ 187,379,427 $ 185,909,056 $ 184,450,222 $ 183,002,836 $ 181,566,808 $ 180,142,048 $ 178,728,468 $ 177,325,981 $ 175,934,499
Terminal value calculations
Competitive equilibrium assumption NO abnormal returns and hence no terminal value Terminal value $ –
Competitive equilibrium assumption only on incremental sales Abnormal earnings in terminal year remain constant forever Final Year Abnormal Earnings $ 207,387,182
Cost of equity 10.87%
Abnormal Earnings Terminal Value $ 1,907,885,763
Present Value of Abnormal earnings terminal value $ 679,848,106
Final year Free cash flow to equity $ 493,732,237
Free cash flow terminal value $ 4,542,154,890
Present value of free cash flow to equity terminal value $ 1,618,532,650
Persistent abnormal performance and growth Abnormal earnings and growth can be sustained forever Final Year Abnormal Earnings $ 207,387,182
Cost of equity 10.87%
Sustained growth rate 10.00%
Abnormal Earnings Terminal Value $ 23,837,607,182
Present Value of Abnormal earnings terminal value $ 8,494,194,153
Final year Free cash flow to equity $ 493,732,237
Free cash flow terminal value 56,750,831,788
Present value of free cash flow to equity terminal value 20,222,356,207

Valuation Summary
DDM Valuation
Required return on equity 10.87%
Constant dividend growth rate 10.00%
Current year dividend per share $ –
DDM Value per share $ –
Abnormal Earnings Growth Model
Beginning shareholders’ equity $ 911,710
Present value of forecast period abnormal earnings $ 963,172,384
Terminal Value – average of three methods $ 3,058,014,086 Average Value $ 41.81
Total equity value $ 4,022,098,180 Current stock price $ 182.78
Shares outstanding 130,950,000
Per share value $ 30.71
Free Cash Flow to Equity Model
Present value of forecast period free cash flow to equity $ 1,482,763,067
Terminal value – average of two methods $ 10,920,444,428
Total equity value $ 12,403,207,496
Shares outstanding 130,950,000
Per share value $ 94.72

Get a 30 % discount on an order above $ 50
Use the following coupon code:
COCONUT
Order your essay today and save 30% with the discount code: COCONUTOrder Now
Positive SSL