LIUC - Exercise.xlsx - Google Sheets

	A	C	D	E	F	G	H	I	J	K	M	N	P	Q	R
1
2				Legend	Calculation required
3					Input
4
5	1	Key financials and assumptions
6
7			€/mln	2023A	2024E	2025E	2026E	2027E	2028E	Terminal Value	Forecasted period Assumptions		Terminal Value Assumptions
8			Net revenues	1,200	1,296	1,426	1,597				Net revenues annual growth 2027	5%	Long-Term inflation ECB target	2%
9			% growth		8%	10%	12%				Net revenues annual growth 2027	8%	EBITDA Margin	33%
10			EBITDA	396	402	456	527				EBITDA Margin expansion 2027 vs. 2026	5%	EBIT Margin	23%
11			Margin %	33%	31%	32%	33%				EBITDA Margin expansion 2028 vs. 2027	2%
12			D&A	(216)	(194)	(200)	(160)	-	-
13			EBIT	180	207	257	367	-	-
14			Margin %	15%	16%	18%	23%	23%	23%
15			EBT	156	194	242	351	-	-
16			Margin %	13%	15%	17%	22%	22%	22%
17			Taxes	(47)	(58)	(73)	(105)	-	-
18			Tax rate %	-30%	-30%	-30%	-30%	-30%	-30%
19			Net Income	109	136	170	246	-	-
20
21			Capex	(60)	(91)	(100)	(128)	-	-	-
22			% on revenue	-5%	-7%	-7%	-8%	-8%	-7%	-8%
23			Change in net working capital	(12)	(65)	(43)	(16)	-	-	-
24			% on revenue	-1%	-5%	-3%	-1%	4%	6%	0%
25
26
27
28
29	2	Valuation
30
31			€/mln	2023A	2024A	2025A	2026A	2027A	2028A	Terminal Value	WACC Calculation	%	WACC Assumptions		Notes
32			EBIT										Risk free rate	1%	German Sovereign Bond
33													Market risk premium	6%	Source: Damodaran, US Equity Risk Premium
34													Beta unlevered	1.02	Panel median
35													Beta levered	1.06	Hamada Formula
36											Cost of Equity (Ke)		Cost of debt	3%	Cost of Debt on Balance Sheet
37													x Tax shield	24%	Italian IRES
38													D/(D+E)	4%	Panel median
39											Net cost of debt (Kd)
40
41
42											WACC
43			Present Value of Free Cash Flow
44
45			Present value of discrete cash flows										Valuation assumptions
46			Present value of terminal cash flow										Corporate tax rate for Free Cash Flow	28%
47			Enterprise Value										NFP 2023A	2,500
48			NFP
49			Equity Value
50
51			EV/ EBITDA entry multiple	-
52
53	3	IRR Calculation
54
55
56			€/mln	2023A	2024A	2025A	2026A	2027A	2028A				IRR assumptions
57			Entry price										Entry date	2023
58			Dividend										Exit date	2028
59			Exit valuation						(4,000)				Dividend 2024 E	100
60			Total Cash Flows										Dividend growth	5%
61													NFP 2028E	4,000
62			IRR	#NUM!
63
64
65	4	Goal Seek Analysis
66
67
68			€/mln	2023A	2024A	2025A	2026A	2027A	2028A
69			Entry price
70			Dividend
71			Exit valuation						(4,000)
72			Total Cash Flows	-	-	-	-	-	(4,000)
73
74			IRR	#NUM!
75
76	x	End of worksheet
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100