Profit and loss form

Profit and loss form

EXCEL FILE:

>Pro Forma Financial Statements, (BS/IS) forecast from 2017-2021(include Accounts Receivable Turnover, Accounts Payable Turnover, PPE Turnover, and Inventory Turnover)

>Indirect Valuation Method (Enterprise Value) (WACC)

>Direct Valuation Method (Free Cash Flow to Firm, include entity value, equity value, equity value per share)]

Include EXPLANATIONS of valuations.

Solution

Profit and Loss

Profit and Loss Statement  
      Actual Projection Applicable value for forecasting  
USD Million Dec-13 Dec-14 Dec-15 Dec-16 Dec-17 Dec-18 Dec-19 Dec-20 Dec-21   Rates  
Revenue 713.8 748.7 810.9 828.9 871.3 915.8 962.6 1011.7 1063.4 0.1 CAGR
Cost of Goods Sold 155.9 159.2 161.4 148.6 176.3 185.3 194.8 204.7 215.2 0.0 CAGR
Gross Profit 557.9 589.5 649.5 680.3 695.0 730.5 767.8 807.0 848.3 0.1 CAGR
Gross Margin % 0.8 0.8 0.8 0.8 0.8 0.8 0.8 0.8 0.8 0.8 Average
Selling, General, & Admin. Expense 230.8 226.3 243.8 246.8 252.4 258.1 263.9 269.9 276.0 0.0 CAGR
Advertising
Research & Development
Other Operating Expense 22.4 24.3 86.2 18.8 17.7 16.7 15.8 14.9 14.0 -0.1 CAGR
Operating Income 304.7 338.9 319.5 414.7 424.9 455.7 488.1 522.3 558.3
Operating Margin % 0.4 0.5 0.4 0.5 0.5 0.5 0.5 0.5 0.5
Interest Income 0.4 0.3 0.4 0.6 0.4 0.4 0.4 0.4 0.4 0.4 Average
Interest Expense -80.2 -68.1 -96.8 -100.9 -98.5 -98.5 -98.5 -98.5 -98.5 4% Average cost of debt
Net Interest Income -79.8 -67.8 -96.4 -100.3 -98.1 -98.1 -98.1 -98.1 -98.1
Other Income (Expense) -6.8 -15.3 -21.6 -1.2 0.0 0.0 0.0 0.0 0.0 0.0 Assumption
Other Income (Minority Interest) 0.6 0.8 0.0 0.0 0.0 0.0 0.0 0.0 0.0 Assumption
Pre-Tax Income 218.1 255.8 201.5 313.2 326.8 357.6 390.0 424.2 460.2
Tax Provision -71.8 -80.2 -96.4 -117.7 -122.3 -133.8 -146.0 -158.8 -172.2
Tax Rate % 0.3 0.3 0.5 0.4 0.4 0.4 0.4 0.4 0.4 0.4 Average Tax rate
Net Income (Continuing Operations) 146.3 175.6 105.1 195.5 204.5 223.8 244.1 265.4 288.0
Net Income (Discontinued Operations)
Net Income 146.9 176.4 105.1 195.5 204.5 223.8 244.1 265.4 288.0
Net Margin % 0.2 0.2 0.1 0.2 0.2 0.2 0.3 0.3 0.3

 Balancesheet

Balance Sheet
      Actual Projection Applicable value for forecasting
USD Million Dec-13 Dec-14 Dec-15 Dec-16 Dec-17 Dec-18 Dec-19 Dec-20 Dec-21   Rates  
  Cash And Cash Equivalents 256.9 208.1 260.4 361.4 590.6 849.4 1130.6 1434.4 1764.4
  Marketable Securities
Cash, Cash Equivalents, Marketable Securities 256.9 208.1 260.4 361.4 590.6 849.4 1130.6 1434.4 1764.4
Accounts Receivable 47.2 55.9 53.1 44.5 56.6 59.5 62.6 65.8 69.1 6.5% As percentage of sales
  Inventories, Raw Materials & Components
  Inventories, Work In Process
  Inventories, Inventories Adjustments
  Inventories, Finished Goods
  Inventories, Other
Total Inventories
Other Current Assets 157.7 129.4 244.3 200.4 217.1 235.1 254.7 275.8 298.8 8.3%
Total Current Assets 461.8 393.4 557.8 606.3 864.3 1144.1 1447.8 1776.0 2132.3
Investments And Advances 170.6 164.5 106.9 114.7 139.2 131.3 123.0 127.1 130.1 Moving average
  Land And Improvements 34.1 33.9 33.3 34.9 34.9 34.9 34.9 34.9 34.9 0% No escalation
  Buildings And Improvements 47.9 49.5 49.3 50.6 51.5 52.5 53.5 54.4 55.4 2% CAGR
  Machinery, Furniture, Equipment 43.1 49.3 53.3 52.9 56.6 60.6 64.9 69.5 74.4 7% CAGR
  Construction In Progress 9.1 5.7 3.8 5.2 6.0 5.2 5.0 5.3 5.4 Moving average
Gross Property, Plant and Equipment 288.7 286.5 294.2 301.3 305.6 310.0 314.4 319.0 323.5 1% CAGR
  Accumulated Depreciation -105.8 -104.4 -111.6 -124.7 -131.7 -139.1 -147.0 -155.3 -164.0 6%
Property, Plant and Equipment 182.9 182.1 182.6 176.6 173.9 170.9 167.5 163.7 159.5
Intangible Assets 2343.8 2317.2 2290.8 2267.0 2242.0 2217.2 2192.7 2168.5 2144.6 -1%
  Goodwill 891.6 891.4 889.6 888.3 888.3 888.3 888.3 888.3 888.3 0%
Other Long Term Assets 75.6 67.3 59.0 62.6 62.6 62.6 62.6 62.6 62.6 0%
Total Assets 3234.7 3124.5 3197.1 3227.2 3481.9 3726.0 3993.6 4297.8 4629.1
  Accounts Payable 12.4 13.8 18.7 12.7 14.4 14.9 15.2 14.3 14.7 Moving average
  Total Tax Payable
  Other Accrued Expense 66.3 64.0 62.1 43.5 37.8 32.8 28.5 24.8 21.6 -13% CAGR
Accounts Payable & Accrued Expense 78.7 77.8 80.8 56.2 52.2 47.7 43.7 39.1 36.2
Current Portion of Long-Term Debt 5.4 4.4 25.5 25.6 25.6 25.6 25.6 25.6 25.6
  Current Deferred Revenue 28.4 30.4 31.5 35.4 35.4 35.4 35.4 35.4 35.4
  Current Deferred Taxes Liabilities
DeferredTaxAndRevenue 28.4 30.4 31.5 35.4 35.4 35.4 35.4 35.4 35.4
Other Current Liabilities 231.7 243.0 280.9 307.0 337.2 370.4 406.8 446.8 490.7 10% CAGR
Total Current Liabilities 344.2 355.6 418.7 424.2 450.4 479.1 511.5 546.9 588.0
  Long-Term Debt 1818.6 1795.6 2420.6 2402.0 2402.0 2402.0 2402.0 2402.0 2402.0
  Capital Lease Obligation 7.0 7.6 7.5 7.6 7.6 7.6 7.6 7.6 7.6
Long-Term Debt & Capital Lease Obligation 1825.6 1803.2 2428.1 2409.6 2409.6 2409.6 2409.6 2409.6 2409.6
Debt-to-Equity 4.5 4.9 -11.1 -14.9 36.7 8.4 4.6 3.0 2.2
PensionAndRetirementBenefit
NonCurrent Deferred Liabilities 572.8 513.7 492.1 474.0 445.0 417.8 392.2 368.3 345.7 -6% CAGR
Minority Interest 0.2 0.0 0.0 0.0 0.0 0.0
Other Long-Term Liabilities 84.6 84.0 78.8 82.9 110.6 129.5 146.2 173.5 198.3 May be used for balancing
Total Liabilities 2827.2 2756.5 3417.7 3390.7 3415.6 3436.0 3459.5 3498.2 3541.6
Common Stock 0.1 0.1 0.1 0.1 0.1 0.1 0.1 0.1 0.1
Preferred Stock
Retained Earnings -779.7 -711.5 -1076.5 -945.8 -741.3 -517.5 -273.5 -8.0 279.9
Accumulated other comprehensive income (loss) 1.3 -14.0 -20.0 -24.0 0.0 0.0 0.0 0.0 0.0
Additional Paid-In Capital 1196.4 1093.4 876.6 807.5 807.5 807.5 807.5 807.5 807.5
Treasury Stock -10.8 -1.1 -1.1 0.0 0.0 0.0 0.0 0.0
Total Equity 407.3 368.0 -220.9 -163.3 66.3 290.1 534.1 799.6 1087.5
Equity-to-Asset 0.1 0.1 -0.1 -0.1 0.0 0.1 0.1 0.2 0.2
Check ok ok ok ok ok ok ok ok ok
Working capital -139.3 -170.3 -121.3 -179.3 -176.7 -184.5 -194.3 -205.3 -220.1
Change in working capital -31.0 49.0 -58.0 2.6 -7.8 -9.8 -11.0 -14.8

Cashflows

Cash flows
      Actual Projection Applicable value for forecasting
USD Million Dec-13 Dec-14 Dec-15 Dec-16 Dec-17 Dec-18 Dec-19 Dec-20 Dec-21   Rates  
Profit 146.9 176.4 105.1 195.5 204.5 223.8 244.1 265.4 288.0
Depreciation 49.4 45.5 45.2 42.5 42.5 42.5 42.5 42.5 42.5
Interest *(1-t) 50.2 42.6 60.6 63.1 61.6 61.6 61.6 61.6 61.6
Change in working capital 39.9 -14.6 -37.9 47.8 2.6 -7.8 -9.8 -11.0 -14.8
Capex -31.1 -23.6 -30.2 -15.2 -15.2 -15.2 -15.2 -15.2 -15.2 Same as historical rate
FCFF 175.5 255.5 218.6 238.1 290.8 320.5 342.8 365.4 391.7
Calculation of FCFE
Profit 146.9 176.4 105.1 195.5 204.5 223.8 244.1 265.4 288.0
Depreciation 49.4 45.5 45.2 42.5 42.5 42.5 42.5 42.5 42.5
Net debt raised 24.2 -15 662.2 -25 0 0 0 0 0 Debt repaid = debt raised , Since capital is les sso manageble
Change in working capital 39.9 -14.6 -37.9 47.8 2.6 -7.8 -9.8 -11.0 -14.8
Capex -31.1 -23.6 -30.2 -15.2 -15.2 -15.2 -15.2 -15.2 -15.2
FCFE 149.5 197.9 820.2 150.0 229.2 258.8 281.2 303.8 330.1
Acuumulated Cash 256.9 208.1 260.4 361.4 590.6 849.4 1130.6 1434.4 1764.4

 Ratio

Cash flows
Key Ratio
Actual Projection Applicable value for forecasting
Dec-13 Dec-14 Dec-15 Dec-16 Dec-17 Dec-18 Dec-19 Dec-20 Dec-21   Rates  
Debt to Equity Ratio 4.5 4.9 -11.1 -14.9 36.7 8.4 4.6 3.0 2.2 Total debt/Total Equity
Equity-to-Asset 0.1 0.1 -0.1 -0.1 0.0 0.1 0.1 0.2 0.2 Total equity/ Total asset
Current Ratio 1.3 1.1 1.3 1.4 1.9 2.4 2.8 3.2 3.6
Account receivable turnover 15.1 14.5 14.9 17.0 17.2 15.8 15.8 15.8 15.8
Account payable turnover 12.6 12.2 9.9 9.5 13.0 12.6 13.0 13.9 14.8
Number of days of accounts receivable 24.1 25.1 24.5 21.5 21.2 23.1 23.1 23.1 23.1
Number of days of accounts payable 29.0 30.0 36.7 38.6 28.1 28.9 28.2 26.3 24.6

 Wacc

WACC
As per Google Finance Date Nasdaq Composite Percentage Change Date DNKN Price Percentage Change
Range 55.66 – 56.70 5/23/2016 4933.50 0.18% 5/23/2016 43.650 1.00%
52 week Range 41.29 – 58.43 5/30/2016 4942.52 0.18% 5/30/2016 44.090 1.0%
Open 56.32 6/6/2016 4894.55 -0.97% 6/6/2016 45.180 2.5%
Vol / Avg. 0.00/1.40M 6/13/2016 4800.34 -1.92% 6/13/2016 44.980 -0.4%
Mkt cap 5.13B 6/20/2016 4707.98 -1.92% 6/20/2016 42.320 -5.9%
P/E 25 6/27/2016 4862.57 3.28% 6/27/2016 44.240 4.5%
Div/yield 0.32/2.32 7/4/2016 4956.76 1.94% 7/4/2016 44.550 0.7%
EPS 2.23 7/11/2016 5029.59 1.47% 7/11/2016 46.600 4.6%
Shares 92.16M 7/18/2016 5100.16 1.40% 7/18/2016 47.640 2.2%
Beta 0.22 7/25/2016 5162.13 1.22% 7/25/2016 45.310 -4.9%
Inst. own 99% 8/1/2016 5221.12 1.14% 8/1/2016 45.820 1.1%
8/8/2016 5232.89 0.23% 8/8/2016 47.300 3.2%
As per regression model 8/15/2016 5238.38 0.10% 8/15/2016 47.630 0.7%
Beta 0.75 8/22/2016 5218.92 -0.37% 8/22/2016 49.010 2.9%
Risk Free Rate 2.88% 8/29/2016 5249.90 0.59% 8/29/2016 49.350 0.7%
Market risk Premium 5.69% 9/5/2016 5125.91 -2.36% 9/5/2016 47.860 -3.0%
Cost of Equity 7.13% 9/12/2016 5244.57 2.31% 9/12/2016 48.050 0.4%
Average Cost of Debt 4.10% 9/19/2016 5305.75 1.17% 9/19/2016 50.110 4.3%
Marginal Tax rate 37.4% 9/26/2016 5312.00 0.12% 9/26/2016 52.080 3.9%
Target Debt To Equity 2.2 10/3/2016 5292.40 -0.37% 10/3/2016 51.000 -2.1%
Debt ratio 69.13% 10/10/2016 5214.16 -1.48% 10/10/2016 51.330 0.6%
Growth rate 2% 10/17/2016 5257.40 0.83% 10/17/2016 48.930 -4.7%
WACC 3.98% 10/24/2016 5190.10 -1.28% 10/24/2016 48.990 0.1%
10/31/2016 5046.37 -2.77% 10/31/2016 46.780 -4.5%
11/7/2016 5237.11 3.78% 11/7/2016 48.850 4.4%
11/14/2016 5321.51 1.61% 11/14/2016 50.900 4.2%
11/21/2016 5398.92 1.45% 11/21/2016 53.990 6.1%
11/28/2016 5255.65 -2.65% 11/28/2016 54.270 0.5%
12/5/2016 5444.50 3.59% 12/5/2016 53.990 -0.5%
12/12/2016 5437.16 -0.13% 12/12/2016 55.440 2.7%
12/19/2016 5462.69 0.47% 12/19/2016 53.590 -3.3%
12/26/2016 5383.12 -1.46% 12/26/2016 52.440 -2.1%
1/2/2017 5521.06 2.56% 1/2/2017 51.470 -1.8%
1/9/2017 5574.12 0.96% 1/9/2017 51.840 0.7%
1/16/2017 5555.33 -0.34% 1/16/2017 51.020 -1.6%
1/23/2017 5660.78 1.90% 1/23/2017 51.820 1.6%
1/30/2017 5666.77 0.11% 1/30/2017 51.590 -0.4%
2/6/2017 5734.13 1.19% 2/6/2017 55.920 8.4%
2/13/2017 5838.58 1.82% 2/13/2017 55.010 -1.6%
2/20/2017 5845.31 0.12% 2/20/2017 54.690 -0.6%
2/27/2017 5870.75 0.44% 2/27/2017 54.790 0.2%
3/6/2017 5861.73 -0.15% 3/6/2017 53.950 -1.5%
3/13/2017 5901.00 0.67% 3/13/2017 56.530 4.8%
3/20/2017 5828.74 -1.22% 3/20/2017 55.980 -1.0%
3/27/2017 5911.74 1.42% 3/27/2017 54.680 -2.3%
4/3/2017 5877.81 -0.57% 4/3/2017 53.110 -2.9%
4/10/2017 5805.15 -1.24% 4/10/2017 53.500 0.7%
4/17/2017 5910.52 1.82% 4/17/2017 55.200 3.2%
4/24/2017 6047.61 2.32% 4/24/2017 55.860 1.2%
5/1/2017 6100.76 0.88% 5/1/2017 55.970 0.2%
5/8/2017 6121.23 0.34% 5/8/2017 56.130 0.3%
5/15/2017 6011.24 -1.80% 5/15/2017 55.660 -0.8%
5/17/2017 6011.24 0.00% 5/17/2017 55.660 0.0%
For calculating beta
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.393425789
R Square 0.154783851
Adjusted R Square 0.138210986
Standard Error 0.027126607
Observations 53
ANOVA
  df SS MS F Significance F
Regression 1 0.006872567 0.006873 9.339595 0.003564
Residual 51 0.037528494 0.000736
Total 52 0.044401061
  Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 0.002298986 0.003845225 0.597881 0.552565 -0.00542 0.010019 -0.00542 0.010019
Percentage Change 0.747035057 0.244442639 3.056075 0.003564 0.256296 1.237774 0.256296 1.237774
RESIDUAL OUTPUT
Observation Predicted Percentage Change Residuals
1 0.003643649 0.006356351
2 0.003664806 0.006415331
3 -0.004951451 0.029673611
4 -0.012079894 0.007653156
5 -0.012074175 -0.04706322
6 0.026828409 0.018540258
7 0.016769357 -0.009762192
8 0.013275234 0.032740458
9 0.012780655 0.009536964
10 0.011375869 -0.060284309
11 0.010835728 0.000420043
12 0.003983034 0.028317249
13 0.003082689 0.003894097
14 -0.000476161 0.029449433
15 0.006733454 0.000203906
16 -0.015344148 -0.014848295
17 0.019592097 -0.015622226
18 0.011013473 0.031858578
19 0.003178969 0.03613456
20 -0.000457407 -0.020279958
21 -0.008744743 0.015215371
22 0.008493964 -0.055250284
23 -0.007263785 0.008490068
24 -0.018388733 -0.026722573
25 0.03053498 0.013714679
26 0.014338009 0.027627274
27 0.013165846 0.047541421
28 -0.017524929 0.022711037
29 0.029142031 -0.034301382
30 0.001291893 0.025564876
31 0.005806634 -0.039176025
32 -0.008582365 -0.012876881
33 0.021441411 -0.039938703
34 0.009478354 -0.00228972
35 -0.000219226 -0.015598675
36 0.016478995 -0.000798869
37 0.003089498 -0.007527939
38 0.011178856 0.0727521
39 0.015906623 -0.032179871
40 0.003160074 -0.00897718
41 0.00555023 -0.003721705
42 0.001151216 -0.016482481
43 0.007303666 0.040518354
44 -0.006848714 -0.002880616
45 0.012936604 -0.036159183
46 -0.001988589 -0.026723902
47 -0.006935692 0.014278923
48 0.015858528 0.015917192
49 0.019625874 -0.007669353
50 0.008864363 -0.006895155
51 0.004805554 -0.00194688
52 -0.011124165 0.002750729
53 0.00229844 -0.00229844

  Valuation

FCFF Approach   Actual Projection
USD Million Dec-13 Dec-14 Dec-15 Dec-16 Dec-17 Dec-18 Dec-19 Dec-20 Dec-21
Profit 146.9 176.4 105.1 195.5 204.5 223.8 244.1 265.4 288.0
Depreciation 49.4 45.5 45.2 42.5 42.5 42.5 42.5 42.5 42.5
Interest *(1-t) 50.2 42.6 60.6 63.1 61.6 61.6 61.6 61.6 61.6
Change in working capital 39.9 -14.6 -37.9 47.8 2.6 -7.8 -9.8 -11.0 -14.8
Capex -31.1 -23.6 -30.2 -15.2 -15.2 -15.2 -15.2 -15.2 -15.2
FCFF 255.3 226.3 142.8 333.7 296.0 304.9 323.2 343.4 362.1
Terminal Value 18700
Total value 255.3 226.3 142.8 333.7 296.0 304.9 323.2 343.4 19061.8
Value of firm  $   17,207.36 Million
Equity value of Firm  $   14,805.36 Million
FCFE Approach
Profit 146.9 176.4 105.1 195.5 204.4897 223.7713 244.0719 265.4498 287.9659
Depreciation 49.4 45.5 45.2 42.5 42.5 42.5 42.5 42.5 42.5
Net debt raised 24.2 -15 662.2 -25 0 0 0 0 0
Change in working capital 39.90 -14.60 -37.90 47.80 2.60 -7.76 -9.80 -11.01 -14.79
Capex -31.1 -23.6 -30.2 -15.2 -15.2 -15.2 -15.2 -15.2 -15.2
FCFE 229.30 168.70 744.40 245.60 234.38 243.31 261.57 281.74 300.48
Terminal value 5973.7
Total value 229.3 168.7 744.4 245.6 234.4 243.3 261.6 281.7 6274.1
Total Equity value  $     5,447.37 Million
Dividend Discount model
Historical Dividend Per Share 0.76 0.92 1.06 1.2
Growth rate 16.4%
So next year dividend would be 1.40
Price of stock  $           27.24
Number of stock outstanding 92.16 Million
Total Value  $     2,510.01 Million
Dividend Discount model
Historical Dividend Per Share 0.76 0.92 1.06 1.2
Growth rate 16.4%
So next year dividend would be 1.40
Price of stock  $           27.24
Number of stock outstanding 92.16 Million
Total Value  $     2,510.01 Million
Approach Valuation in $ Million
FCFF approach  $ 14,805.36
FCFE approach  $   5,447.37
Dividend Discount model  $   2,510.01
Market Cap model  $   5,190.45