ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1
2
DISCOUNTED CASH FLOW (DCF) VALUATION MODEL
https://goo.gl/PPjbCJ
3
INPUT
4
NOTE: Throughout Valuation Model, complete only non-shaded cells. Shaded cells contain formulae which populate automatically.
5
LAST FISCAL YEAR2019
6
CORPORATE TAX RATE22%
7
8
DISCOUNTED CASH FLOW (DCF)
9
ACTUAL
FORECAST PD
CAGRCAGR
10
2016A2017A2018A2019A2020E2021E2022E2023E2024E2016-2019A2020-2024E
11
Net Sales5,5004,5005,0005,5006,1056,7167,3207,9058,4590.0%8.5%
12
Growth, %-18.2%11.1%10.0%11.0%10.0%9.0%8.0%7.0%
13
COGS-2,000-2,000-2,000-3,000-4,747-5,221-5,691-6,147-6,57714.5%8.5%
14
GROSS PROFIT8571,0531,2721,3521,3581,4941,6291,7591,88216.4%8.5%
15
Margin, %15.6%23.4%25.4%24.6%22.2%22.2%22.2%22.2%22.2%
16
OPEX-220-225-230-235-244-254-263-272-2812.2%3.5%
17
Growth, %2.3%2.2%2.2%4.0%3.8%3.6%3.4%3.3%
18
In % of Net Sales4.0%5.0%4.6%4.3%4.0%3.8%3.6%3.4%3.3%
19
TOTAL COSTS-2,220-2,225-2,230-3,235-4,991-5,475-5,954-6,419-6,85813.4%8.3%
20
Growth, %0.2%0.2%45.1%54.3%9.7%8.8%7.8%6.8%
21
EBITDA3,2802,2752,7702,2651,1141,2401,3661,4871,601-11.6%9.5%
22
Margin, %59.6%50.6%55.4%41.2%18.2%18.5%18.7%18.8%18.9%
23
Depreciation-60-70-65-77-82-90-98-106-1138.7%8.5%
24
In % of Net Sales1.1%1.6%1.3%1.4%1.3%1.3%1.3%1.3%1.3%
25
26
BETA and CAPITAL STRUCTURE
27
COMPARABLE COMPANIES UNLEVERED BETA
28
* OPTIONAL: HIDE SECTION ON OUTPUT *
29
COMPANYLEVERED BETA (1)MARKET VALUE OF DEBT (2)MARKET VALUE OF EQUITY (3)DEBT / EQUITYEQUITY / TOTAL ASSETSTAX RATEUNLEVERED BETA (4)
30
[Company 1]1.1360098061.2%62.0%22%0.76
31
[Company 2]1.4850088056.8%63.8%22%1.03
32
[Company 3]1.5240078051.3%66.1%22%1.09
33
[Company 4]1.1730068044.1%69.4%22%0.87
34
[Company 5]1.2620058034.5%74.4%22%0.99
35
MEDIAN1.2651.3%66.1%0.99
36
MEAN1.3149.6%67.1%0.95
37
(1) From Bloomberg
38
(2) Book Value of Debt
39
(3) From Bloomberg
40
(4) Unlevered Beta = Predicted Levered Beta / (1 + Debt/Equity) x (1-t))
41
RELEVERED BETAMEAN UNLEVERED BETAMEAN TARGET DEBT / EQUITYTARGET MARGINAL TAX RATERELEVERED BETA
42
Target Company0.9549.6%22%1.31
43
44
WACC CALCULATION
45
* OPTIONAL: HIDE SECTION ON OUTPUT *
46
TARGET CAPITAL STRUCTURE
COMMENTS
47
Debt to Total Capitalization32.9%
48
Equity to Total Capitalization67.1%
49
Debt to Equity Ratio49.6%
50
COST OF EQUITY
51
Risk-free rate (2)3.1%
52
Market risk Premium (3)6.2%
53
Levered Beta (4)1.31
54
Size Premium (5)1.4%
55
COST OF EQUITY12.6%
56
COST OF DEBT
57
Cost of Debt5.9%
58
Corporate Tax Rate22.0%
59
AFTER TAX COST OF DEBT4.6%
60
WACC10.0%
61
62
DCF continued…
63
64
EBIT3,2202,2052,7052,1881,0321,1511,2681,3811,488-12.1%9.6%
65
Tax-111-121-131-141-227-253-279-304-3278.3%9.6%
66
Capex-80-50-110-97-107-117-126-135N/A0
67
In % of Net Sales1.8%1.0%2.0%1.6%1.6%1.6%1.6%1.6%
68
Net Working Capital200225210200222244266287308
69
In % of Net Sales3.6%5.0%4.2%3.6%3.6%3.6%3.6%3.6%3.6%
70
Increase / Decrease in NWC-251510-22-22-22-21-20
71
UNLEVERED FREE CASH FLOW2,0492,6042,0247688589481,0361,119
72
WACC10.0%10.0%10.0%10.0%10.0%
73
Discount Period0.51.52.53.54.5
74
Discount Factor0.950.870.790.720.65
75
PRESENT VALUE OF FREE CASH FLOW732744747742728
76
77
DCF-VALUATION
IMPLIED MULTIPLES
2019A2020E2021E2022E2023E
78
Enterprise value ("EV")12,537Sales2.3x2.1x1.9x1.7x1.6x
79
Less: Total Debt600
* HIDE ROW ON OUTPUT *
80
Plus: Cash and Cash Equi.150
* HIDE ROW ON OUTPUT *
81
Net Debt negative number equals net cash position
450
* HIDE ROW ON OUTPUT *
82
Equity Value market cap12,087EBITDA5.5x11.3x10.1x9.2x8.4x
83
Outstanding Shares10,000
* HIDE ROW ON OUTPUT *
84
Price Per Share1.21EBIT5.7x12.1x10.9x9.9x9.1x
85
86
TERMINAL VALUE
87
Terminal Year Free Cash Flow1,119
88
Perpetuity Growth Rate2.0%
89
Terminal Year EBITDA1,601
90
Terminal Value14,250
91
Implied Exit Multiple9.3x
92
Discount Period5.0
93
Discount Factor0.6
94
PRESENT VALUE OF TERMINAL VALUE8,844
95
% of Enterprise Value71%
96
97
CLICK HERE TO CREATE IN SMARTSHEET
98
99
100