ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
This is the sheet used in class, pls copy data and format to a blank sheet for practice
2
3
4
5
General Assumptions:
6
7
Company Name:
8
Ticker:
9
Current Share Price:
10
Last Historical Year:2021-12-31
11
12
Annual Gross Margin Improvement: 0.30%
13
Effective Tax Rate:30.0%
14
Interest Rate on Debt:4.0%
15
Interest Rate on Cash:0.5%
16
17
HistoricalProjected
18
Financial Statement Drivers:
Units:FY19FY20FY21FY22FY23FY24FY25FY26
19
Income Statement Drivers:
20
Revenue Growth:% 2.5% (7.4%)5.0%4.0%4.0%3.0%3.0%
21
Gross Margin:%39.5%40.1%41.2%41.5%41.8%42.1%42.4%42.7%
22
SG&A % Revenue:% 19.9% 18.5% 18.0% 18.0%17.5%17.5%17.0%17.0%
23
24
Amortization of Intangible Assets:$ M 217.0 196.0 175.0 153.0 134.0
25
Impairment of Goodwill:$ M - - - - -
26
27
Amortization of Debt:%10.0%10.0%10.0%10.0%10.0%
28
29
Beginning Debt Balance:$ M 7,422.0 6,679.8 5,937.6 5,195.4 4,453.2
30
(-) Amortization:$ M (742.2) (742.2) (742.2) (742.2) (742.2)
31
Ending Debt Balance:$ M 6,679.8 5,937.6 5,195.4 4,453.2 3,711.0
32
33
Interest Expense:$ M 296.9 267.2 237.5 207.8 178.1
34
Interest Income:$ M 15.5 - - - -
35
36
Income from Discontinued Operations:$ M - - - - -
37
38
Balance Sheet Drivers:
39
Days Sales Outstanding:# Days61.1 57.8 60.0 59.6 59.6 59.6 59.6 59.6
40
Days Sales of Inventory:# Days53.2 49.7 50.3 50.0 49.0 49.0 48.0 48.0
41
Days Payable Outstanding:# Days27.1 21.5 20.8 20.0 19.0 19.0 18.0 18.0
42
43
Prepaid Expenses & Other % SG&A:%91.87%14.97%14.11%13.0%13.0%12.0%12.0%11.0%
44
Other Long-Term Assets % Revenue:% 11.0% 10.0% 10.7% 10.6%10.6%10.6%10.6%10.6%
45
Accrued Expenses % SG&A:% 45.2% 48.1% 47.0% 46.7%46.7%46.7%46.7%46.7%
46
47
Other Current Liabilities % SG&A:s 20.5% 9.3% 10.6% 10.0%10.0%10.0%10.0%10.0%
48
Other Long-Term Liabilities % SG&A:% 32.8% 37.4% 40.6% 36.9%36.9%36.9%36.9%36.9%
49
50
Cash Flow Statement Drivers:
51
Capital Expenditures:$ M (285.0) (325.0) (375.0) (425.0) (475.0)
52
Depreciation % Revenue:% 2.1% 1.8% 1.8% 1.7%1.8%1.9%2.0%2.1%
53
54
Stock-Based Compensation % Revenue:% 0.3% 0.3% 0.3% 0.3%0.3%0.3%0.3%0.3%
55
56
Deferred Taxes % Taxes:% 0.8% 6.8% (1.3%)2.1%2.1%2.1%2.1%2.1%
57
Dividends % Net Income:% 31.4% 24.1% 39.1% 31.6%31.6%31.6%31.6%31.6%
58
59
Stock Repurchases:$ M 1,900.0 4,198.0 1,943.0 1,000.0 1,000.0 1,000.0 1,000.0 1,000.0
60
61
Other Non-Cash Items:$ M - - - - -
62
Other Investing Items:$ M - - - - -
63
Other Financing Items:$ M - - - - -
64
65
FX Rate Effects % Revenue:%(0.7%)(3.7%)(3.5%)(2.6%)(2.6%)(2.6%)(2.6%)(2.6%)
66
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216