Valuation homework, please see the excel file for all the data information
Input Company nameCaterpillarImportant: Update Yellow cells first-> think -> update blue if necessary Date of valuation5/24/16 Country of incorporationUSA Industry (Global) 1Construction Supplies Aswath Damodaran: If you are in multiple businesses, you can construct your own weighted averages using the industry average table from this spreadsheet and your company's business breakdown.41.16% Industry (Global) 2Metals & Mining18.68% Industry (Global) 3Transportation (Railroads)40.16% UnitMillions USD Aswath Damodaran: Chen Liu: Enter the operating income or EBIT from the most recent time period, even if that number is negative. Current (TTM) Revenues50,969,000,000 Aswath Damodaran: Enter the revenues from the most recent period (you can either use annual or the trailing 12 months). If your company had no revenues, enter a very small positive number. (You need a base for your growth rate) Operating income or EBIT7,487,000,000 Aswath Damodaran: Chen Liu: Enter the operating income or EBIT from the most recent time period, even if that number is negative. Interest expense(431,000,000) Net Income5,303,000,000 Book value of equity14,197,000,000 Aswath Damodaran: Total Equity from the most recent balance sheet. This book equity will include everything - paid in capital, retained earnings etc. and may even be negative for companies that have been losing money for a while. Book value of debt37,093,000,000 Aswath Damodaran: Chen Liu: Total Debt from the most recent balance sheet. = long-term debt + debt in current liabilities + notes payable i.e. total interest-bearing debt` Total Assets75,894,000,000 Cash & Cash Equivalent7,123,000,000 Aswath Damodaran: Enter the cash balance from the most recent balance sheet. This should include marketable securities. Number of shares outstanding =541,951,509 Current stock price =120.74 Aswath Damodaran: Enter the most recent stock price (how about today's?) in here. Market Capitalization =65,435,225,197 Effective tax rate =25.27% Marginal tax rate =21.00% Market numbers What your company's numbers look like, relative to industry. Riskfree rate0.68% Aswath Damodaran: Chen Liu: Today's long term riskfree rate. For US: 10-yr Tbond (Fred) For Euro: 10-yr German Bondhttps://fred.stlouisfed.org/series/DGS10In most recent year:CaterpillarConstruction SuppliesMetals & MiningTransportation (Railroads)Ind Average Equity Risk Premium (ERP) (Trailing 12 month cash yield)5.65%http://pages.stern.nyu.edu/~adamodar/% in industries41.16%18.68%40.16% Beta (Yahoo or Morning Star)1.08CAGR in Revenues- Last 5 years3.82%4.33%14.84%0.08%4.587051311293660% current WACC4.84%Expected Growth in Revenues - Next 2 years1.53%0.27%1.18%1.152% Operating margin14.69%12.02%11.37%38.21%22.42% The value drivers below:basebestworstSales to capital ratio1.151.571.010.461.02 Annual Revenue growth rate, next 5 yrs =3.00% Aswath Damodaran: this is your prediction for the company's growth in the first 5 years. Start by looking at past 5 year's growth rate and industry average. 9.00% Aswath Damodaran: this is your prediction for the company's growth in the first 5 years. Start by looking at past 5 year's growth rate and industry average. 1.00% Aswath Damodaran: this is your prediction for the company's growth in the first 5 years. Start by looking at past 5 year's growth rate and industry average. Cost of capital4.84%7.13%7.24%11.17%8.77% Annual Revenue growth rate, in perpetuity =2.50%get industry data from "Ratio by Industry" sheet Pre-tax operating margin in year 10 =14.00% Aswath Damodaran: Chen Liu: Use industry average operating margin; unless you think the company will not be at industry level in year 1038.21% Aswath Damodaran: Chen Liu: Use industry average operating margin; unless you think the company will not be at industry level in year 1012.02% Aswath Damodaran: Chen Liu: Use industry average operating margin; unless you think the company will not be at industry level in year 10 Sales to capital ratio(start) =1.151.151.15 Sales to capital ratio(end) =1.001.57 Aswath Damodaran: Chen Liu: = Revenue/ [BV(debt)+BV(equity)-cash] To calculate reinvestment The higher you set this number, the more efficiently you are growing and the higher the value of your growth. Again, look at your company's current number (check on the right). Look at the industry averages as well in the worksheet. Aswath Damodaran: If you are in multiple businesses, you can construct your own weighted averages using the industry average table from this spreadsheet and your company's business breakdown.0.46 Aswath Damodaran: Chen Liu: = Revenue/ [BV(debt)+BV(equity)-cash] To calculate reinvestment The higher you set this number, the more efficiently you are growing and the higher the value of your growth. Again, look at your company's current number (check on the right). Look at the industry averages as well in the worksheet. Aswath Damodaran: Chen Liu: Enter the operating income or EBIT from the most recent time period, even if that number is negative. Aswath Damodaran: Enter the revenues from the most recent period (you can either use annual or the trailing 12 months). If your company had no revenues, enter a very small positive number. (You need a base for your growth rate) Aswath Damodaran: Chen Liu: Enter the operating income or EBIT from the most recent time period, even if that number is negative. Aswath Damodaran: Total Equity from the most recent balance sheet. This book equity will include everything - paid in capital, retained earnings etc. and may even be negative for companies that have been losing money for a while. Aswath Damodaran: Chen Liu: Total Debt from the most recent balance sheet. = long-term debt + debt in current liabilities + notes payable i.e. total interest-bearing debt` Aswath Damodaran: Enter the cash balance from the most recent balance sheet. This should include marketable securities. Aswath Damodaran: Enter the most recent stock price (how about today's?) in here. Aswath Damodaran: Chen Liu: Today's long term riskfree rate. For US: 10-yr Tbond (Fred) For Euro: 10-yr German Bond Aswath Damodaran: this is your prediction for the company's growth in the first 5 years. Start by looking at past 5 year's growth rate and industry average. http://pages.stern.nyu.edu/~adamodar/https://fred.stlouisfed.org/series/DGS10 DCF-base 20152016201720182019TTM12345678910Terminal year Total Revenue49,011,000,00038,537,000,00045,462,000,00054,722,000,00053,800,000,00050,969,000,00050,969,000,00052,498,070,00054,073,012,10055,695,202,46357,366,058,53759,029,674,23460,682,505,11362,320,932,75163,941,277,00365,539,808,92867,178,304,151 Revenue growth %-21.37%17.97%20.37%-1.68%0.00%3.00%3.00%3.00%3.00%2.90%2.80%2.70%2.60%2.50%2.50% Operating Income (EBIT)3,256,000,0001,093,000,0004,406,000,0008,293,000,0008,290,000,0007,487,000,0007,483,486,6007,704,372,3967,931,776,2028,165,890,3018,406,912,6478,646,644,0758,884,567,1379,120,154,5379,352,870,9499,582,174,9269,404,962,581 Operating Margin %6.64%2.84%9.69%15.15%15.41%14.69%14.68%14.68%14.67%14.66%14.65%14.65%14.64%14.63%14.63%14.62%14.00% Pretax Income2,855,000,000139,000,0004,082,000,0007,822,000,0007,812,000,0007061000000 Provision for Income Tax-742,000,000-192,000,000-3,339,000,000-1,698,000,000-1,746,000,000-1784000000 Tax Rate26%138%82%22%22%25%24.84%24.41%23.99%23.56%23.13%22.71%22.28%21.85%21.43%21.00%21% EBIT(1- tax rate)2,409,782,137-416,755,396801,974,0326,492,755,3066,437,165,8995,595,368,7865,624,664,2015,823,547,5316,029,269,8656,242,061,5946,462,160,7496,683,318,2866,905,115,5257,127,117,2597,348,872,9757,569,918,1917,429,920,439 Cash and Cash Equivalents6,460,000,0007,168,000,0008,261,000,0007,890,000,0008,292,000,0007,123,000,000 Book Value of Debt38,093,000,00036,783,000,00034,878,000,00036,553,000,00037,657,000,00037,093,000,000 Current Debt and Capital Lease Obligation12,846,000,00013,965,000,00011,031,000,00011,553,000,00011,376,000,00012,724,000,000 Long Term Debt and Capital Lease Obligation25,247,000,00022,818,000,00023,847,000,00025,000,000,00026,281,000,00024,369,000,000 Book Value of Equity14,885,000,00013,213,000,00013,766,000,00014,080,000,00014,629,000,00014,197,000,000 Invested Capital46,518,000,00042,828,000,00040,383,000,00042,743,000,00043,994,000,00044,167,000,00049,554,000,00049,554,000,00050,915,345,30552,337,024,07953,821,997,23455,373,391,03256,940,470,62558,520,309,90360,109,779,83761,705,548,23663,304,080,161 Sales/Capital Ratio1.050.901.131.281.221.151.141.121.111.091.081.061.051.031.021.001.00 Capital Expenditure (Capex)-1,388,000,000-2,928,000,000-2,336,000,000-2,916,000,000-2,669,000,000-2,670,000,000 Depreciation & Amortization2,164,000,0002,144,000,0002,016,000,0001,895,000,0001,713,000,000402,000,000 Changes in Working Capital1,008,000,000628,000,000-194,000,000-4,077,000,000-2,930,000,000-2,315,000,000 Reinvestment = Chen Liu: Chen Liu: You might want to consider expand reinvestment to its several components.-2,544,000,000-4,444,000,0004,546,000,0008,888,000,0007,312,000,0005,387,000,00001,361,345,3051,421,678,7741,484,973,1551,551,393,7991,567,079,5921,579,839,2781,589,469,9341,595,768,3991,598,531,9251,638,495,223 FCFF Chen Liu: Chen Liu: FCFF (Free Cash Flow to Firm) =EBIT(1-Tc) - Reinvestment EBIT = Revenue * operating margin (i.e. operating margin = EBIT/ revenue) Reinvesment include capital expenditure and investment in NWC $ 4,198,000,000$ 2,680,000,000$ 3,366,000,000$ 3,642,000,000$ 4,243,000,000208,368,7865,624,664,2014,462,202,2264,607,591,0914,757,088,4404,910,766,9505,116,238,6945,325,276,2475,537,647,3255,753,104,5765,971,386,2665,791,425,216 Terminal Value216,097,955,817 Cost of capital (WACC)4.84%4.84%4.84%4.84%4.84%4.91%4.98%5.04%5.11%5.18%5.18% Cumulated discount factor0.95380.90980.86770.82770.78940.75250.71680.68240.64920.6172 PV(FCFF)5,364,890,7444,059,549,2373,998,220,8253,937,298,6223,876,776,2483,849,963,7663,817,268,8553,778,863,2363,734,944,159137,068,381,313 Sum of PV(FCFF)$ 173,486,157,004 Value of operating assets =$173,486,157,004 - Debt$37,093,000,000 + Cash & MKt Securities$7,123,000,000 Value of equity$143,516,157,004 Number of shares$541,951,509 Estimated value /share$ 264.81 Price$ 120.74 Price as % of value45.59% This is how much capital you invested over the ten year period. This is is how much your operating income grew over the ten-year period. DCF-best 20152016201720182019TTM12345678910Terminal year Total Revenue49,011,000,00038,537,000,00045,462,000,00054,722,000,00053,800,000,00050,969,000,00050,969,000,00055,556,210,00060,556,268,90066,006,333,10171,946,903,08077,127,080,10281,291,942,42784,218,452,35585,734,384,49785,734,384,49785,734,384,497 Revenue growth %-21.37%17.97%20.37%-1.68%0.00%9.00%9.00%9.00%9.00%7.20%5.40%3.60%1.80%0.00%0