I have a Project I am working on for Portfolio Management. I finished most of the data entry and creating of tabs, but I still have about 30% of the project that I need help on. I expect it to take from 1-3hrs depending on excel proficiency. I am going to attach an example of a previous student's project that our teacher gave us. No formulas are givenonthis excel file. The 2nd attachment will be the rubric/ assignment guidelines. The third file will be my project that needs to be completed. If any more resources are needed then just let me know! P.s. Feel free to change whatever I already have in my excel file.....could very well not be right at all haha.

Thanks.

Dorman Products (DORM) Income (Operating) Yellow Highlight = Our Calculation 2011 529.3 Total Revenue ($M) Revenue Growth 2012 570.4 7.8% 2013 664.5 16.5% 338.2 355.2 403.5 63.9% 62.3% 60.7% 191.1 215.2 261 Gross Margin 36.10% 37.73% 39.28% 106.4 111 133 SGA % of Sales 20.1% 19.5% 20.0% Operating Income (EBIT) 84.7 104.2 127.9 Income Tax - Total Expense % of EBIT 31.2 37.7 45.8 36.8% 36.2% 35.8% 0.2 0.1 0.2 Cost of Revenue, Total COGS Margin Gross Profit Selling General & Admin Expenses Other Research & Development Other Indirect Operating Expenses Total Other Other % of Sales Interest Expense (Income), Net (IS) Debt (BS) (LT Debt + ST Debt + Other NonC L) if NonC Liabilities are interest bearing Depreciation & Amortization (from CF statement or bottom IS) D&A % of Sales 7.7 8.2 10.2 1.5% 1.4% 1.5% Orange = Used 10k for tweak 2014 751.5 13.1% 2015 Hist Average 803 663.74 6.9% 12.5% 464.3 495 61.8% 61.6% 287.2 308 38.22% 38.36% 62.2% 37.83% 2016 858 6.0% 532 62.0% 326 38.00% 172 20.0% 2017 930 6.0% 576 62.0% 353 38.00% 186 20.0% 2018 983 5.7% 609 62.0% 373 38.00% 197 20.0% 2019 1032 5.0% 640 62.0% 392 38.00% 206 20.0% 2020 1073 4.0% 665 62.0% 408 38.00% 215 20.0% 2021 1113 3.7% 690 62.0% 423 38.00% 223 20.0% 2022 1146 3.0% 711 62.0% 436 38.00% 229 20.0% 146.5 161.9 19.5% 20.2% 140.7 146.2 154 167 177 186 193 200 206 50.5 53.6 35.9% 36.7% 56 36.0% 60 36.0% 64 36.0% 67 36.0% 70 36.0% 72 36.0% 74 36.0% 0.2 0.2 12.7 16.2 1.7% 2.0% 19.8% 36.2% 1.5% 13.728 14.87568 15.723594 16.509773 17.170164 17.80546 18.339624 1.6% 1.6% 1.6% 1.6% 1.6% 1.6% 1.6% 2023 1273 *First two years from Yahoo! Analyst Estimate, then fade to GDP 11.1% 789 62.0% 484 38.00% 255 20.0% 229 82 36.0% 20.366505 1.6% Dorman Products (DORM) NWC Detail Revenue Cost of Revenue ASSETS Accounts Receivable Daily Sales Outstanding Other Receivables % Sales Inventory Inventory Turnover Prepaid Expenses % Sales Other Current Assets % Sales Total Non-Cash Current Assets LIABILITIES Accounts Payable Days Payable Outstanding Other Current Liabilities % COGS Total Current Liabilities Net Working Capital Change in Net Working Capital 2011 529.3 Yellow Highlight = Our Calculation 2012 2013 2014 2015 Average 570.4 664.5 751.5 803 338.2 355.2 403.5 464.3 495 124.3 133.8 180.8 206 204 85.7 85.6 99.3 100.1 92.7 115.8 145.3 164.4 173.5 193.7 4.6 3.9 4.0 4.3 4.1 19.8 22.9 26.6 28.2 3.7% 259.9 4.0% 302 4.0% 371.8 3.8% 407.7 44.6 57.3 91.7 90.8 98.6 48.1 58.9 83.0 71.4 72.7 44.6 57.3 91.7 90.8 98.6 215.3 #REF! 244.7 29.4 280.1 35.4 316.9 36.8 -98.6 Orange = Used 10k for tw 2016 858 532 212.737 92.7 90.5 4.2 209 4.1 3.9% 3.7% 65.3 152 64.7 #REF! #REF! 32 Orange = Used 10k for tweak 2017 2018 930 983 576 609 2019 1032 640 2020 1073 665 2021 1113 690 2022 2023 1146 1273 711 789.2021 230.5221 243.6619 255.8449 266.0787 275.9237 284.2014 315.6111 *Project based on daily sales outstanding 90.5 90.5 90.5 90.5 90.5 90.5 90.5 227 4.1 240 4.1 252 4.1 262 4.1 271 4.1 280 4.1 310 *Project based on days inventory held 4.1 3.7% 3.7% 3.7% 3.7% 3.7% 3.7% 165 64.7 174 64.7 183 64.7 190 64.7 197 64.7 203 64.7 #REF! 32 #REF! 32 #REF! 32 #REF! 32 #REF! 32 #REF! 32 3.7% 226 *Project based on days payable outstanding 64.7 y sales outstanding s inventory held s payable outstanding Yellow Highlight = Our Calculation 2012 2013 2014 2015 Average 570.4 664.5 751.5 803 Dorman Products (DORM) CapEx Detail Revenue 2011 529.3 Gross Property, Plant & Equipment % Sales Accumulated Depreciation Net Property, Plant & Equipment % Sales 107.5 121 143.2 165.1 179.7 20.3% 21.2% 21.6% 22.0% 22.4% 68.6 38.9 72.2 48.8 78.4 64.8 82.9 82.3 92.6 87 7.3% 8.6% 9.8% 11.0% 10.8% 9.2% -18.1 -3.4% -18.1 -3.2% -24.7 -3.7% -29.9 -4.0% -21.7 -2.7% -3.6% 7.7 8.2 10.2 12.7 16.2 42.5% 45.3% 41.3% 42.5% 74.7% Capital Expenditures % Sales Depreciation (off IS, for FCF) % CapEx 21.3% 42.9% Orange = Used 10k for tweak 2016 2017 2018 858 930 983 2019 1032 2020 1073 2021 1113 2022 1146 2023 1273 180 21.0% 195 21.0% 206 21.0% 217 21.0% 225 21.0% 234 21.0% 241 21.0% 267 21.0% 74 8.6% 80 8.6% 85 8.6% 89 8.6% 92 8.6% 96 8.6% 99 8.6% 109 8.6% -30 -3.5% 14 48.0% -33 -3.5% 16 48.0% -34 -3.5% 17 48.0% -36 -3.5% 17 48.0% -38 -3.5% 18 48.0% -39 -3.5% 19 48.0% -40 -3.5% 19 48.0% -45 *From 10-k -3.5% 21 *Same estimates as from Income Sheet 48.0% mates as from Income Sheet Revenue EBIT Less: Taxes Add: D&A Less: CapEx Less: Change in NWC Free Cash Flow Change in FCF Beta Rm-Rf Rf Cost of Equity Cost of Debt Tax Rate Book Debt Market Equity Year-End Share Price Weight of Debt Weight of Equity WACC 2011 529.3 2012 570.4 2013 664.5 2014 751.5 2015 803 2016 858 2017 930 2018 983 84.7 104.2 127.9 140.7 146.2 31.23 7.7 18.1 16.2 19.7 #REF! 37.7 8.2 18.1 27.2 30.83 63.9% 45.8 10.2 24.7 32.2 36.89 83.6% 50.5 12.7 29.9 36.2 29.8 123.8% 53.6 16.2 21.7 151 96 109 79.55 32 52.325 #REF! 161 103 117 85.79 32 57.085 91.7% 170 109 124 91.01 32 61.865 92.3% 70.372 42.3% 0.15333 * Avg. then Smoothed 5.5 * Fernandez Market Premium Paper 2.4 5.490 0.000 * Weighted yields of outstanding bonds 36.355 *Use Historical $ Income Taxes/EBIT % 0.00E+00 and hold going forward. Calculation on IS 61 0.000 * (BV Debt/(BV Debt + MV Equity)) 1.000 * (1-Wd) 4.5800 Cash (from BS) WACC Sum Future CF MV of Debt Millions Shares Base Implied Price 5.62% #REF! Permanent Growth WACC 4.58 2019 1032 2020 1073 178 114 130 95.36 32 66.515 93.0% 185 118 134 97.97 32 70.905 93.8% 2021 1113 2022 1146 192 198 199 123 127 127 140 145 145 102.64 106.52 106.52 32 32 32 74.235 77.355 78.48 95.5% 96.0% Terminal (or Horizon) value of all CF 2023 and beyond Permanent GDP Forecast *Used fair value of outstanding debt (10k) or All Bond Issuances, or use BV of debt if any private borrowing Permanent Growth 2023 1273 Gross Domestic Product Percent change from preceding period http://www.bea.govational/index.htm Source: Annual 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 GDP percent change based GDP percent on current change based on dollars chained 2009 (Nominal) dollars (Real) -11.9 -8.5 -16.0 -6.4 -23.1 -12.9 -4.0 -1.3 16.9 10.8 11.1 8.9 14.3 12.9 9.6 5.1 -6.1 -3.3 7.0 8.0 10.1 8.8 25.7 17.7 28.3 18.9 22.4 17.0 10.5 8.0 1.6 -1.0 -0.2 -11.6 9.7 -1.1 9.9 4.1 -0.7 -0.5 10.0 8.7 15.7 8.1 5.9 4.1 6.0 4.7 0.4 -0.6 9.0 7.1 5.6 2.1 5.5 2.1 1.5 -0.7 8.4 6.9 4.0 2.6 3.7 2.6 7.4 6.1 5.5 4.4 7.4 5.8 8.4 6.5 9.6 6.6 5.7 2.7 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 9.4 8.2 5.5 8.5 9.8 11.4 8.4 9.0 11.2 11.1 13.0 11.7 8.8 12.2 4.2 8.8 11.1 7.6 5.6 6.1 7.9 7.7 5.7 3.3 5.9 5.2 6.3 4.9 5.7 6.3 5.6 6.3 6.5 3.3 3.3 4.9 6.6 6.7 5.8 4.5 1.7 -2.0 3.8 3.7 4.2 3.7 3.9 4.9 3.1 0.2 3.3 5.2 5.6 -0.5 -0.2 5.4 4.6 5.6 3.2 -0.2 2.6 -1.9 4.6 7.3 4.2 3.5 3.5 4.2 3.7 1.9 -0.1 3.6 2.7 4.0 2.7 3.8 4.5 4.5 4.7 4.1 1.0 1.8 2.8 3.8 3.3 2.7 1.8 -0.3 -2.8 2.5 1.6 2.3 2.2 2.4 Average 6.33 3.30 Average (2001present 3.84 1.78 Standard Motor Products (SMP) Income (Operating) Yellow Highlight = Our Calculation 2011 2012 2013 874.6 948.9 983.7 Revenue Growth #REF! 8.5% 3.7% 645.5 689.2 693.2 COGS Margin 73.8% 72.6% 70.5% 229.1 259.7 290.5 Gross Margin 26.19% 27.37% 29.53% 163.8 187.5 201.3 SGA % of Sales 18.7% 19.8% 20.5% 0.4 0.4 0.7 0.7 2.3 2.3 0.0% 0.1% 0.2% 64.9 71.4 86.9 0.1 25 31.9 0.2% 35.0% 36.7% 3.8 2.6 1.6 Total Revenue ($M) Cost of Revenue, Total Gross Profit Selling General & Admin Expenses Other Research & Development Other Indirect Operating Expenses Total Other Other % of Sales Operating Income (EBIT) Income Tax - Total Expense % of EBIT Interest Expense (Income), Net (IS) Debt (BS) (LT Debt + ST Debt + Other NonC L) if NonC Liabilities are interest bearing Depreciation & Amortization (from CF statement or bottom IS) D&A % of Sales 13.1 15.4 17.4 1.5% 1.6% 1.8% Orange = Used 10k for tweak 2014 2015 Hist Average 2016 #REF! 5.0% #REF! 72.0% #REF! 27.65% #REF! 19.7% 2017 #REF! 5.0% #REF! 72.0% #REF! 27.65% #REF! 19.7% 2018 #REF! 4.3% #REF! 72.0% #REF! 27.65% #REF! 19.7% 2019 #REF! 4.0% #REF! 72.0% #REF! 27.65% #REF! 19.7% 2020 #REF! 3.7% #REF! 72.0% #REF! 27.65% #REF! 19.7% 2021 #REF! 3.0% #REF! 72.0% #REF! 27.65% #REF! 19.7% 2022 #REF! 3.0% #REF! 72.0% #REF! 27.65% #REF! 19.7% #REF! 0.3% #REF! #REF! 0.3% #REF! #REF! 0.3% #REF! #REF! 0.3% #REF! #REF! 0.3% #REF! #REF! 0.3% #REF! #REF! 0.3% #REF! 26.4% #REF! 27.7% #REF! 27.7% #REF! 27.7% #REF! 27.7% #REF! 27.7% #REF! 27.7% #REF! 27.7% 1.7% #REF! 1.7% #REF! 1.7% #REF! 1.7% #REF! 1.7% #REF! 1.7% #REF! 1.7% #REF! 1.7% 980.4 -0.3% #REF! 690.8 70.5% 71.8% 289.6 29.54% 28.16% 193.5 19.7% 19.7% 10.8 10.8 1.1% 0.4% 85.3 28.9 33.9% 1.3 16.9 1.7% *First two years from Yahoo! Analyst Estimate, then fade to GDP Standard Motor Products( SMP) NWC De Revenue Cost of Revenue Accounts Receivable Daily Sales Outstanding Other Receivables % Sales Inventory Inventory Turnover Prepaid Expenses % Sales Other Current Assets % Sales Total Non-Cash Current Assets 2011 Yellow Highlight = Our Calculation 2012 2013 2014 Average 874.6 645.5 948.9 689.2 983.7 693.2 980.4 690.8 104.1 98.6 125.2 126.5 43.4 37.9 46.5 47.1 248.1 267.5 269.4 278.1 3.5 3.5 3.7 3.5 37.9 39.4 45.9 47.7 4.3% 4.2% 4.7% 4.9% 390.1 405.5 440.5 452.3 37.9 39.4 45.9 47.7 LIABILITIES Accounts Payable Days Payable Outstanding Other Current Liabilities % COGS Total Current Liabilities 15.8 15.2 17.0 17.8 228.9 222.2 220.3 250.8 Net Working Capital Change in Net Working Capital 161.2 #REF! 183.3 22.1 220.2 36.9 201.5 -18.7 Orange = Used 10k for tw 2016 1081 778 130.2992 43.7 44 3.6 309 3.5 4.5% 46 4.3% 16.4 46 15.5 #REF! #REF! 11 Orange = Used 10k for tweak 2017 2018 1135 1184 817 852 2019 1231 886 136.8141 142.6972 148.405 2020 1277 919 2021 1315 947 2022 1354 975 153.896 158.5129 163.2683 44 44 44 44 44 44 324 3.5 338 3.5 352 3.5 365 3.5 376 3.5 387 3.5 49 4.3% 51 4.3% 53 4.3% 55 4.3% 57 4.3% 58 4.3% 48 15.5 50 15.5 52 15.5 54 15.5 56 15.5 58 15.5 #REF! 11 #REF! 11 #REF! 11 #REF! 11 #REF! 11 #REF! 11 *Project based on daily sales outstanding *Project based on days inventory held *Project based on days payable outstanding y sales outstanding s inventory held s payable outstanding Standard Motor Products (SMP) CapEx De Revenue Gross Property, Plant & Equipment % Sales Accumulated Depreciation Net Property, Plant & Equipment % Sales Capital Expenditures % Sales Depreciation (off IS, for FCF) % CapEx 2011 Yellow Highlight = Our Calculation 2012 2013 2014 2015 Average 874.6 948.9 983.7 980.4 235.4 242 246.3 253.1 26.9% 25.5% 25.0% 25.8% 171.4 64 177.5 64.4 182.7 63.6 188.5 64.6 7.3% 6.8% 6.5% 6.6% 6.8% -11 -1.3% -11.8 -1.2% -11.4 -1.2% -13.9 -1.4% -1.3% #DIV/0! 25.8% Orange = Used 10k for tweak 2016 2017 2018 1080.891 1135 1184 2019 1231 2020 1277 2021 1315 2022 1354 281 26.0% 295 26.0% 308 26.0% 320 26.0% 332 26.0% 342 26.0% 352 26.0% 75 6.9% 78 6.9% 82 6.9% 85 6.9% 88 6.9% 91 6.9% 93 6.9% -14 -1.3% -15 -1.3% -15 -1.3% -16 -1.3% -17 -1.3% -17 -1.3% -18 -1.3% 2023 *From 10-k *Same estimates as from Income Sheet mates as from Income Sheet Revenue EBIT Less: Taxes Add: D&A Less: CapEx Less: Change in NWC Free Cash Flow Change in FCF Beta Rm-Rf Rf Cost of Equity Cost of Debt Tax Rate Book Debt Market Equity Year-End Share Price Weight of Debt Weight of Equity WACC 2011 2012 2013 2014 874.6 948.9 983.7 980.4 64.9 0.1 13.1 71.4 25 15.4 86.9 31.9 17.4 85.3 28.9 16.9 -11 3.4 63.5 #REF! -11.8 22.1 27.9 227.6% -11.4 36.9 24.1 115.8% -13.9 -18.7 78.1 30.9% 2015 2.0331 * Avg. then Smoothed 5.5 * Fernandez Market Premium Paper 2.4 * Weighted yields of outstanding bonds 39 *Use Historical $ Income Taxes/EBIT % and hold going forward. Calculation on IS * (BV Debt/(BV Debt + MV Equity)) * (1-Wd) 16.2700 2016 1081 2017 1135 2018 1184 87 92 96 24 25 27 18 19 20 -14 -15 -15 11 11 11 56.550329 59.627846 63.309843 #REF! 94.8% 94.2% Cash (from BS) WACC 16.27 Sum Future CF MV of Debt Millions Shares Base Implied Price WACC #REF! 773.7 Permanent Growth 1.5% 1.75% 2019 1231 2020 1277 2021 1315 2022 1354 2023 100 103 106 109 28 29 29 30 21 22 22 23 -16 -17 -17 -18 11 11 11 11 65.882237 68.318879 71.208446 73.184699 96.1% 96.4% 95.9% 97.3% Terminal (or Horizon) value of all CF 2023 and beyond Permanent GDP Forecast *Used fair value of outstanding debt (10k) or All Bond Issuances, or use BV of debt if any private borrowing Permanent Growth 2.0% 2.25% 2.5% Amgen (AMGN) Income (Operating) Yellow Highlight = Our Calculation 2010 15053 2220 14.7% 12833 85.3% 3983 2011 15582 3.5% 2427 15.6% 13155 84.4% 4486 2012 17265 10.8% 2918 16.9% 14347 83.1% 4801 2013 18676 8.2% 3346 17.9% 15330 82.1% 5184 26.5% 28.9% 27.9% 27.8% 2894 411 3167 1190 3380 589 4083 196 Operating Income (EBIT) 3305 22.0% 5545 4063 26.1% 4593 3675 21.3% 5858 4279 22.9% 5867 Income Tax - Total Expense % of EBIT 690 12.4% 467 10.2% 664 11.3% 184 3.1% Interest Expense (Income), Net (IS) Debt (BS) (LT Debt + ST Debt + Other NonC L) if NonC Liabilities are interest bearing 228 10874 162 21344 568 24034 602 29623 1017 6.8% 1060 6.8% 1088 6.3% 1286 6.9% Total Revenue ($M) Revenue Growth Cost of Revenue, Total COGS Margin Gross Profit Gross Margin Selling General & Admin Expenses SGA % of Sales Other Research & Development Other Indirect Operating Expenses Total Other Other % of Sales Depreciation & Amortization (from CF statement or bottom IS) D&A % of Sales Orange = Used 10k for tweak 2014 Hist Average 20063 7.4% 7.5% 4422 22.0% 17.4% 15641 78.0% 82.6% 4699 23.4% 2015 20960 4.5% 3773 18.0% 17187 82.0% 5030 24.0% 2016 21830 4.2% 3929 18.0% 17901 82.0% 5239 24.0% 2017 22660 3.8% 4079 18.0% 18581 82.0% 5438 24.0% 2018 23430 3.4% 4217 18.0% 19213 82.0% 5623 24.0% 2019 24133 3.0% 4344 18.0% 19789 82.0% 5792 24.0% 2020 24760 2.6% 4457 18.0% 20303 82.0% 5942 24.0% 2021 25305 2.2% 4555 18.0% 20750 82.0% 6073 24.0% 2022 25862 2.2% 4655 18.0% 21207 82.0% 6207 24.0% 4926 23.5% 7231 5130 23.5% 7531 5325 23.5% 7818 5506 23.5% 8083 5671 23.5% 8326 5819 23.5% 8542 5947 23.5% 8730 6078 23.5% 8922 8.8% 1085 15.0% 1130 15.0% 1173 15.0% 1213 15.0% 1249 15.0% 1281 15.0% 1310 15.0% 1338 15.0% 7.4% 1676.8 8.0% 1746.4 1812.7632 1874.3971 1930.6291 1980.8254 2024.4036 2068.9405 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 26.8% 4297 454 4751 23.7% 6191 427 6.9% 23.9% 606 30215 2092 10.4% *First two years from Yahoo! Analyst Estimate, then fade to GDP Amgen (AMGN) NWC Detail Revenue Cost of Revenue 2010 15053 2220 2011 15582 2427 2012 17265 2918 2013 18676 3346 2014 Average 20063 4422 ASSETS Accounts Receivable Daily Sales Outstanding Other Receivables % Sales Inventory Inventory Turnover Prepaid Expenses % Sales Other Current Assets % Sales Total Non-Cash Current Assets 2335 56.6 0 0% 2022 7.4 0 0% 1350 9.0% 5707 2896 67.8 0 0% 2484 6.3 0 0% 1572 10.1% 6952 2518 53.2 0 0% 2744 6.3 0 0% 1886 10.9% 7148 2697 52.7 0 0% 3019 6.2 0 0% 2250 12.0% 7966 2546 46.3 0 0% 2647 7.6 0 0% 2494 12.4% 7687 LIABILITIES Accounts Payable Days Payable Outstanding Other Current Liabilities % COGS Total Current Liabilities 4082 671.1 2488 112.1% 6570 5670 852.7 84 3.5% 5754 5696 712.5 2495 85.5% 8191 5442 593.6 2505 74.9% 7947 6508 537.2 500 11.3% 7008 -863 1198 2061 -1043 -2241 19 1062 679 660 Net Working Capital Change in Net Working Capital 55.3 6.8 10.9% 673.4 57% 2015 20960 3773 2016 21830 3929 2017 22660 4079 2018 23430 4217 2019 24133 4344 2020 24760 4457 2021 25305 4555 2022 25862 4655 3178 3310 3436 3553 3659 3754 3837 3921 *Project based on daily sales outstand 3103 3232 3354 3469 3573 3665 3746 3829 *Project based on days inventory held 2283 10.9% 8564 2378 10.9% 8919 2468 10.9% 9258 2552 10.9% 9573 2629 10.9% 9860 2697 10.9% 10117 2756 10.9% 10339 6961 7250 7525 7781 8015 8223 8404 2167 57% 9128 2257 57% 9507 2343 57% 9868 2423 57% 10204 2495 57% 10510 2560 57% 10783 2616 57% 11020 2674 57% 11263 -564 -1243 -588 -23 -610 -22 -631 -21 -650 -19 -667 -17 -681 -15 -696 -15 2817 10.9% 10567 8589 *Project based on days payable outsta ased on daily sales outstanding ased on days inventory held ased on days payable outstanding Amgen (AMGN) CapEx Detail Revenue 2010 15053 2011 15582 2012 17265 2013 18676 2014 Average 20063 Gross Property, Plant & Equipment % Sales Accumulated Depreciation Net Property, Plant & Equipment % Sales 10688 71.0% 5166 5522 36.7% 11231 72.1% 5811 5420 34.8% 11947 69.2% 6621 5326 30.8% 12291 65.8% 6942 5349 28.6% 12256 61.1% 7033 5223 26.0% -580 3.9% 1017 175.3% -567 3.6% 1060 186.9% -689 4.0% 1088 157.9% -693 3.7% 1286 185.6% -718 3.6% 2092 291.4% Capital Expenditures % Sales Depreciation (off IS, for FCF) % CapEx 67.8% 8.1% 31.4% 3.8% 199.4% 2015 20960 2016 21830 2017 22660 2018 23430 2019 24133 2020 24760 2021 25305 2022 25862 13624 65.0% 7606 6078 29.0% 14190 65.0% 8225 6331 29.0% 14729 65.0% 8896 6571 29.0% 15229 65.0% 9620 6795 29.0% 15686 65.0% 10404 6999 29.0% 16094 65.0% 11251 7180 29.0% 16448 65.0% 12168 7338 29.0% 16810 65.0% 13159 7500 29.0% -800 11.4% 1677 209.6% -851 3.9% 1746 205.1% -884 3.9% 1813 205.1% -914 3.9% 1874 205.1% -941 3.9% 1931 205.1% -966 3.9% 1981 205.1% -987 3.9% 2024 205.1% -1009 *From 10-k 3.9% 2069 *Same estimates as from Income She 205.1% mates as from Income Sheet Revenue EBIT Less: Taxes Add: D&A Less: CapEx Less: Change in NWC Free Cash Flow Change in FCF Beta Rm-Rf Rf Cost of Equity Cost of Debt Tax Rate Book Debt Market Equity Year-End Share Price Weight of Debt Weight of Equity WACC 2010 15053 2011 15582 2012 17265 2013 18676 2014 20063 2015 20960 2016 21830 2017 22660 5545 690 1017 580 4312 467 1060 567 2061 2277 -57.0% 5577 664 1088 689 -2241 7553 231.7% 5867 184 1286 693 1062 5214 -31.0% 6191 427 2092 718 660 6478 24.2% 7231 1085 1677 800 -1243 8266 27.6% 7531 1130 1746 851 -23 7320 -11.4% 7818 1173 1813 884 -22 7596 3.8% 5292 0.72 * Avg. then Smoothed 0.054 * Fernandez Market Premium Paper 0.0224 0.061 0.040 * Weighted yields of outstanding bonds 0.15 *Use Historical $ Income Taxes/EBIT % 3.02E+10 and hold going forward. Calculation on IS 1.23E+11 159.13 0.198 * (BV Debt/(BV Debt + MV Equity)) 0.802 * (1-Wd) 0.0559 Cash (from BS) WACC Sum Future CF MV of Debt Millions Shares Base Implied Price WACC 6.59% 6.34% 6.09% 5.84% 5.59% 5.34% 5.09% 4.84% 4.59% 27026 5.59% 210017 33600 770 264.21 Permanent Growth 1.5% 1.75% 189.36 196.67 199.64 207.88 211.04 220.37 223.75 234.40 238.02 250.26 254.15 268.33 272.53 289.11 293.66 313.25 318.22 341.64 2018 23430 8083 1213 1874 914 -21 7851 3.4% 2019 24133 2020 24760 2021 25305 8326 8542 8730 8922 1249 1281 1310 1338 1931 1981 2024 2069 941 966 987 1009 -19 -17 -15 -15 8085 8293 8472 8659 3.0% 2.6% 2.2% 2.2% 246140 Terminal (or Horizon) value of all CF 2023 and beyond 2.0% Permanent GDP Forecast *Used fair value of outstanding debt (10k) or All Bond Issuances, or use BV of debt if any private borrowing Permanent Growth 2.0% 204.77 217.06 230.86 246.45 264.21 284.64 308.37 336.29 369.60 2022 25862 2.25% 213.81 227.37 242.70 260.17 280.25 303.59 331.03 363.78 403.53 2.5% 223.95 239.02 256.20 275.94 298.89 325.87 358.07 397.15 445.59 Gross Domestic Product Percent change from preceding period http://www.bea.govational/index.htm Source: Annual 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 GDP percent change based GDP percent on current change based on dollars chained 2009 (Nominal) dollars (Real) -11.9 -8.5 -16.0 -6.4 -23.1 -12.9 -4.0 -1.3 16.9 10.8 11.1 8.9 14.3 12.9 9.6 5.1 -6.1 -3.3 7.0 8.0 10.1 8.8 25.7 17.7 28.3 18.9 22.4 17.0 10.5 8.0 1.6 -1.0 -0.2 -11.6 9.7 -1.1 9.9 4.1 -0.7 -0.5 10.0 8.7 15.7 8.1 5.9 4.1 6.0 4.7 0.4 -0.6 9.0 7.1 5.6 2.1 5.5 2.1 1.5 -0.7 8.4 6.9 4.0 2.6 3.7 2.6 7.4 6.1 5.5 4.4 7.4 5.8 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 8.4 9.6 5.7 9.4 8.2 5.5 8.5 9.8 11.4 8.4 9.0 11.2 11.1 13.0 11.7 8.8 12.2 4.2 8.8 11.1 7.6 5.6 6.1 7.9 7.7 5.7 3.3 5.9 5.2 6.3 4.9 5.7 6.3 5.6 6.3 6.5 3.3 3.3 4.9 6.6 6.7 5.8 4.5 1.7 -2.0 3.8 3.7 4.2 6.5 6.6 2.7 4.9 3.1 0.2 3.3 5.2 5.6 -0.5 -0.2 5.4 4.6 5.6 3.2 -0.2 2.6 -1.9 4.6 7.3 4.2 3.5 3.5 4.2 3.7 1.9 -0.1 3.6 2.7 4.0 2.7 3.8 4.5 4.5 4.7 4.1 1.0 1.8 2.8 3.8 3.3 2.7 1.8 -0.3 -2.8 2.5 1.6 2.3 2013 2014 3.7 3.9 2.2 2.4 Average 6.33 3.30 Average (2001present 3.84 1.78 FIN 4514 Fall 2016 Project 1 In this project we will use a free cash flow analysis to consider the values of two competing stocks. Let's consider the case of two auto parts companies: Dorman Products (DORM) and Standard Motor Products (SMP). Use the financial statements of these companies, available from Intrinsic Research. You must use the income statement and balance sheet information (and maybe a little bit of the cash flow statement) for year-end 2011, 2012, 2013, 2014, and 2015. Each of these companies operates on a calendar fiscal year, so after the year-end on December 31st, the 10-k is available a few weeks later (at sec.gov), and then the information is available on Intrinsic Research (and can be downloaded into Excel). The username is uwf1. The password is LMC16 Use these items to fill in a set of four tabs (one set for each company...so you'll have eight tabs total). Follow the example based on the company Amgen (AMGN) that we discussed in class and is available in your Project 1 folder (sometimes certain pieces of information are not applicable, and thus you may leave a '0' in given spots). Formulas are NOT available for you to see, so you'll need to think through how the pieces come together in many spots...but these are usually standard calculations that you've seen before and/or could easily research. You can follow the AMGN calculations and reverse engineer many of the calculations. When YOU create YOUR analyses (4 Excel tabs for DORM, and 4 Excel tabs for SMP) you must show the calculations, via an Excel formula, for most ALL projections into the future (there are a few small exceptions). I do NOT want to see raw projection numbers and wonder \"where did that come from?\" I want you to SHOW your calculations, via Excel, in the cells for years 2016 and beyond. Use the guide attached to the back of this document to aid in filling out your two company analyses. You are also to read the \"management discussion of financials\" section of each 2015 10-k (i.e. for SMP and DORM)...you should then make some effort to slightly adjust your projections for future values, away from historical trends, based on your reading. Highlight those items you \"tweak\" in Orange on your Excel work (see the AMGN example). As discussed in the guide, your final product will be a matrix of potential \"fair\" price estimates based on a range of WACC estimates (centered on the value you produce in your work) and a range of permanent GDP growth. For this project, you must turn in your completed (filled out) template sheets. In addition to this work in the Excel Templates, you are also to write answers to the following questions as a Microsoft Word Document. Put time and thought into your answers, and make sure you've proofread for perfect grammar and punctuation! You can answer in bullet-point response form or narrative form in Word, but either way, I'm a stickler for spelling/grammar and care. What \"tweaks\" did you decide to implement to your analyses after reading the company 10k for Dorman? For Standard Motor Products? Why did you make these changes? What effect did these changes have on your intrinsic valuation of the companies (up or down)? Compare the WACC that you calculated for you estimation of Dorman's value to Standard Motor's WACC value. Were they close? Why/why not? Do you think they SHOULD be close? Why/why not? If applicable, what impact might this dichotomy have on your analysis? If you could only do one of the following today, based on your analysis, which would it be? (EXPLAIN WHY!): Buy DORM, Sell DORM, Buy SMP, or Sell SMP. How confident do you feel in this decision? Why do you feel this confidence (or lack thereof)? Do NOT try to filibuster or speak in generalities...I want you to explicitly choose one of these four actions and tell me why you chose it! I will create a Dropbox folder on eLearning where you may submit your Project 1. Your completed templates (8 tabs total...I'd probably just include 4 DORM tabs and 4 SMP tabs on one Excel sheet) as well as your one page Word memo are due in this Dropbox folder by Sunday, September 25th, at 11:59pm. Note our syllabus' policy of 20% deductions, per day, on any project not submitted in time. I will be happy to answer detailed questions in class, outside of class, or via email until Wednesday, September 21st, after which my help will be more limited. Some thoughts to help: Overall, the idea is to record historical financial information (using Intrinsic Research) and then provide estimates for future values of relevant cash flow determinants by 1) projecting historical trends; 2) viewing the most recent (and ideally the previous) company 10-k as well as searching the popular financial press for impactful news items, especially those that might have occurred since the release of the most recent 10-k; 3) applying methods for generating other estimates. Make sure to base all information at the same point in time (e.g., by using 12/31 values in all respects) for an apples-to-apples comparison. It is the analyst's job to \"tweak\" values away from raw calculations based on emerging trends, company disclosures (particularly in the 10-k) and news items in the popular financial press. Analysts may also err slightly on the side of conservatism when projecting items forward, but should keep track of such conservative adjustments. In many cases, it is appropriate to project past growth rates (or average growth rates) forward, but it is often useful to adjust these values in conjunction with company information in the 10-k. It is vital to read the 10-k! The financial statements of the 10-k can be pulled for easy use via Intrinsic Research and copy/pasted into the templates in many places, but the notes to the financial statements, discussion of the business and its risks, and management discussion and analysis of the financial condition are all crucial in developing superior insight to the pure forward projection of past trends. Specifically: 1) Report historical income statement information detailing the revenue, cost of revenue, gross profit, SG&A expense, Depreciation and Amortization (often found on the cash flow statement), and income tax expense. Calculation/verification of operating income (EBIT) is the next step. HINT! (while we're here)... \"capital expenditure\" information can often be found directly on Statements of Cash Flows downloadable from Intrinsic Research. Future projections of these items should continue until that time at which the analyst believes the company's growth rate becomes permanent (and this growth rate should match, or be less than, the long-term projection of GDP!! Otherwise, the rate is not \"permanent,\" for the company would, in the extreme, eventually overtake the value of the entire economy). Yahoo! Finance provides analyst estimates for the next two years of revenue. These estimates are reasonable starting points which I encourage you to plug in directly for the next two years of revenue forecasts. Then, it is expected that the revenue growth rate will trend toward the projected long-term GDP growth, or be less. This process typically will take from 5-20 years (though for mature firms, it might already be near-complete...in which case detailing 5 future years of specific inputs is suggested), and it need not be straight-lined in nature...its path should be projected by the analyst. For our purposes, we'll look 8 years ahead. Cost of revenue margin, gross profit margin, SG&A % of sales, D&A % of sales, other expenses % of sales, and income tax expense as a % of EBIT are all useful values to aid in the projection of future cash flows until the terminal value point. Using the historical averages of these rates (we'll use past 5 years) may be good growth projections of these items going forward. However, the analyst should use the 10-k and popular news items in order to potentially adjust these values (being particularly careful to note how the impacts of \"special\" events in the past might need to be excluded when growth rates are used to project future values). 2) Describe the historical changes in assets of net working capital (NWC) by detailing the accounts receivable, other receivables, inventory, prepaid expenses, and other current assets. Note the typical % of sales these items detail as well as values like Daily Sales Outstanding and Days Inventory Held. On the liabilities side, detail the historical trends of accounts payable and other current liabilities, as well as the Days Payable Outstanding. These items detail the working capital situation over the past 5 years of the company. Use the average values of these items to project the future 8 years of the same items, again tweaking values based on company disclosures in the 10-k and/or readings of the popular financial press. The projected changes, year over year, provide the Changes in NWC used in the projection of future cash flows. 3) Detail the historical PP&E values, accumulated depreciation, and Net PP&E (of depreciation). These are used to show how historical capital expenditures occurred. Find the historical % sales these values account for, on average, and project these percentages for the forward 8 years. Utilize any projections from the 10-k and/or popular financial press to adjust the projections away from purely historical percentages. 4) Combine the operating income and tax information (detailed in the 1 st Excel Tab, per company), the NWC change information (2nd Excel Tab), and CapEx information (3rd Excel Tab) together into a summary sheet (the \"final\" of the 4 tabs, per company) that detail the projected 8 years of future cash flows. After this point, calculate the terminal values of all cash flows BEYOND this \"horizon\" point. The formula: HV = (Last CF*(1+Permanent Growth Rate))/(WACC-Permanent Growth Rate) may be useful in this regard. Provide the annual and HV detail. Then, to get a stock price estimate: Add together the present values of all of the future cash flows, all discounted via the firm's WACC estimate. Subtract the market value of the debt of the firm (this may be noted as the \"fair value of outstanding debt\" in the 10-k of the company. If the firm holds some private (bank, etc.) debt (beyond bonds), then either the fair value noted on the 10-k, or the pure book value of debt (add short and long-term debt from the balance sheet...occasionally, but not usually, you might add other noncurrent liabilities too...all items that are interest bearing is the key) can be used as the market value of debt. Consider the cash on hand with the firm! Add the most recent statement's cash/equivalents amount from the balance sheet when determining today's cash value of the company! Divide by the number of diluted shares outstanding of the company (from the most recent the income statement). Be careful to avoid scaling errors...e.g., sometimes financial statement data is in thousands of dollars, sometimes millions, sometimes billions. The number of shares might be in raw shares, thousands, or millions. You can easily be off by a factor of 1000 if you aren't careful...if you get very large or very small share price estimates, this is something you'd definitely want to double check. Detailing the WACC calculation is an important task and can be challenging. To get the weight of debt, it's advisable to use the equation: Wd = Book Value of Debt/(Book Value of Debt + Market Value of Equity) Then, weight of equity (We) is usually (1-Wd) when preferred stock holdings are insubstantial. Market Value of Equity is just #shares x share price. Remember to use the date of the share price that matches with those of the accounting statements (not today's). Some companies might have very little (or even zero) debt! (In this case the WACC is really just the cost of equity). The cost of equity can be found from Rs = Rf + Smoothed Beta(s)(Rm - Rf). The risk-free rate, Rf, can be taken as the treasury yield, on the day of the calculations (the year-end, matching the financial statements, for an apples-to-apples comparison), that closest matches the weighted maturity time of the company's debt. i.e, if the weighted average company bond has 10 years until maturity, use the 10-year Treasury yield. If debt is insubstantial or hard to trace, the 10-year rate is still a good approximation for Rf. The market premium (Rm - Rf) can be found from the \"Market Premium\" paper by Fernandez (just do an internet search and use the most recent year's (2016) value for the primary country of operations...the United States for this project). The beta of the company can be taken from Google Finance (other places are fine too, or you might create your own, but for this project, please use Google Finance). But first, \"smooth\" the beta toward the historical market average of 1 by applying: Smoothed Beta = 1/3 + (2/3) * Average Beta The cost of debt (Rd) for the company can be calculated by using \"interest expense/Longterm debt average of last 2 years\"). Of course, this only makes sense for companies that HAVE debt! Information on tax rate, weights of debt and equity (and preferred stock if applicable), and costs of debt and equity (and preferred stock if applicable) can be combined to calculate the WACC as: WACC = WdRd(1-T) + WeRs This WACC value is used to discount projected future cash flows (including the terminal value) to present value terms. After the present market value of debt is subtracted, cash and equivalents are added, and other (less common) considerations like preferred stock and minority interest are included (immaterial in this project), the market value should be divided by the number of diluted shares to provide a current share price estimate. By the way, the Stern Business School (from NYU) keeps a regularly updated estimate list of costs of debt and equity by industry: http://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/wacc.htm It is advisable to check the cost of capital elements used in company analysis versus these values just as a safety measure (to be on the lookout for a big error). However, it is NOT advisable to adopt these cost of capital elements directly! Every company will have deviations of their cost of capital specifics away from their industry average. Beyond this very specific point estimate, it's imperative to show some \"sensitivity analysis\" that provides a range of how likely we are to see the estimate range when our assumptions are off. Provide a grid of values that shows what our projected fair stock price estimate is for a range of WACC estimates and terminal growth (at most, long-term GDP growth) estimates. Show only a sensible range of values. This might require limiting the range of both WACC and long-term GDP (or permanent growth) to a fairly limited range...You can see an example in the AMGN work posted on eLearning. DO NOT ignore this task! It's challenging, but do-able! Grading template: Data gathering/reporting: 10% Detailing 10-k findings/ \"tweaks\" to historical averages: 10% Calculations/Execution of projections, including a final \"grid\" sensitivity analysis: 50% Excel presentation: 15% Word memo (including spelling, grammar, presentation, and a solid effort to \"take a stand\" and answer the specific questions): 15%