|
1
|
# Financial Model Recipes |
|
2
|
|
|
3
|
Ready-made patterns for common financial models. Each recipe includes the sheet structure, key formulas, and input/output design. |
|
4
|
|
|
5
|
## 3-Statement Model (Income Statement, Balance Sheet, Cash Flow) |
|
6
|
|
|
7
|
```python |
|
8
|
|
|
9
|
import xlsxwriter |
|
10
|
|
|
11
|
wb = xlsxwriter.Workbook("3_statement_model.xlsx") |
|
12
|
|
|
13
|
input_fmt = wb.add_format({"bg_color": "#FFF2CC", "border": 1, "num_format": "#,##0.0"}) |
|
14
|
|
|
15
|
input_pct = wb.add_format({"bg_color": "#FFF2CC", "border": 1, "num_format": "0.0%"}) |
|
16
|
|
|
17
|
calc_fmt = wb.add_format({"num_format": '"$"#,##0.0', "locked": True}) |
|
18
|
|
|
19
|
hdr = wb.add_format({"bold": True, "bg_color": "#1B3A5C", "font_color": "white", "bottom": 2}) |
|
20
|
|
|
21
|
section = wb.add_format({"bold": True, "bg_color": "#D6E4F0", "bottom": 1}) |
|
22
|
|
|
23
|
total = wb.add_format({"bold": True, "num_format": '"$"#,##0.0', "top": 1, "bottom": 6}) |
|
24
|
|
|
25
|
YEARS = 5 |
|
26
|
|
|
27
|
# --- Assumptions Sheet --- |
|
28
|
|
|
29
|
ws_a = wb.add_worksheet("Assumptions") |
|
30
|
|
|
31
|
ws_a.set_column(0, 0, 35) |
|
32
|
|
|
33
|
ws_a.set_column(1, 1, 16) |
|
34
|
|
|
35
|
assumptions = [ |
|
36
|
|
|
37
|
("Base Revenue ($M)", 1000, input_fmt, "rev"), |
|
38
|
|
|
39
|
("Revenue Growth Rate", 0.08, input_pct, "rev_g"), |
|
40
|
|
|
41
|
("COGS (% of Revenue)", 0.60, input_pct, "cogs_pct"), |
|
42
|
|
|
43
|
("SG&A (% of Revenue)", 0.15, input_pct, "sga_pct"), |
|
44
|
|
|
45
|
("D&A (% of Revenue)", 0.03, input_pct, "da_pct"), |
|
46
|
|
|
47
|
("Interest Rate", 0.05, input_pct, "int_rate"), |
|
48
|
|
|
49
|
("Tax Rate", 0.25, input_pct, "tax_rate"), |
|
50
|
|
|
51
|
("Capex (% of Revenue)", 0.05, input_pct, "capex_pct"), |
|
52
|
|
|
53
|
("Debt Balance ($M)", 500, input_fmt, "debt"), |
|
54
|
|
|
55
|
("AR Days", 45, wb.add_format({"bg_color": "#FFF2CC", "border": 1, "num_format": "#,##0"}), "ar_days"), |
|
56
|
|
|
57
|
("Inventory Days", 30, wb.add_format({"bg_color": "#FFF2CC", "border": 1, "num_format": "#,##0"}), "inv_days"), |
|
58
|
|
|
59
|
("AP Days", 40, wb.add_format({"bg_color": "#FFF2CC", "border": 1, "num_format": "#,##0"}), "ap_days"), |
|
60
|
|
|
61
|
] |
|
62
|
|
|
63
|
refs = {} |
|
64
|
|
|
65
|
for i, (label, val, fmt, key) in enumerate(assumptions): |
|
66
|
|
|
67
|
ws_a.write(i + 1, 0, label) |
|
68
|
|
|
69
|
ws_a.write(i + 1, 1, val, fmt) |
|
70
|
|
|
71
|
refs[key] = f"Assumptions!$B${i+2}" |
|
72
|
|
|
73
|
# --- Income Statement --- |
|
74
|
|
|
75
|
ws_is = wb.add_worksheet("Income Statement") |
|
76
|
|
|
77
|
ws_is.set_column(0, 0, 30) |
|
78
|
|
|
79
|
ws_is.write_row(0, 1, [f"Year {y}" for y in range(1, YEARS + 1)], hdr) |
|
80
|
|
|
81
|
rows_is = [ |
|
82
|
|
|
83
|
("Revenue", f"={refs['rev']}*(1+{refs['rev_g']})^{{y}}", calc_fmt), |
|
84
|
|
|
85
|
("(-) COGS", f"=-{{rev}}*{refs['cogs_pct']}", calc_fmt), |
|
86
|
|
|
87
|
("Gross Profit", "={rev}+{cogs}", total), |
|
88
|
|
|
89
|
("(-) SG&A", f"=-{{rev}}*{refs['sga_pct']}", calc_fmt), |
|
90
|
|
|
91
|
("(-) D&A", f"=-{{rev}}*{refs['da_pct']}", calc_fmt), |
|
92
|
|
|
93
|
("EBIT", "={gp}+{sga}+{da}", total), |
|
94
|
|
|
95
|
("(-) Interest", f"=-{refs['debt']}*{refs['int_rate']}", calc_fmt), |
|
96
|
|
|
97
|
("EBT", "={ebit}+{interest}", calc_fmt), |
|
98
|
|
|
99
|
("(-) Taxes", f"=-MAX(0,{{ebt}})*{refs['tax_rate']}", calc_fmt), |
|
100
|
|
|
101
|
("Net Income", "={ebt}+{taxes}", total), |
|
102
|
|
|
103
|
] |
|
104
|
|
|
105
|
# Build row-by-row with formula references pointing to previous rows |
|
106
|
|
|
107
|
# --- Balance Sheet --- |
|
108
|
|
|
109
|
ws_bs = wb.add_worksheet("Balance Sheet") |
|
110
|
|
|
111
|
# Assets: Cash, AR, Inventory, PP&E |
|
112
|
|
|
113
|
# Liabilities: AP, Debt |
|
114
|
|
|
115
|
# Equity: Retained Earnings |
|
116
|
|
|
117
|
# AR = Revenue / 365 * AR Days, Inventory = COGS / 365 * Inv Days, AP = COGS / 365 * AP Days |
|
118
|
|
|
119
|
# --- Cash Flow Statement --- |
|
120
|
|
|
121
|
ws_cf = wb.add_worksheet("Cash Flow") |
|
122
|
|
|
123
|
# Operating: Net Income + D&A + Changes in WC |
|
124
|
|
|
125
|
# Investing: -Capex |
|
126
|
|
|
127
|
# Financing: Debt changes, dividends |
|
128
|
|
|
129
|
# Ending Cash = Beginning + Operating + Investing + Financing |
|
130
|
|
|
131
|
wb.close() |
|
132
|
|
|
133
|
``` |
|
134
|
|
|
135
|
**Key design principles:** |
|
136
|
|
|
137
|
- Yellow cells = user inputs, all other cells are formulas |
|
138
|
- Income Statement drives Balance Sheet (retained earnings) and Cash Flow (net income) |
|
139
|
|
|
140
|
- Balance Sheet working capital items use days-based formulas tied to revenue/COGS |
|
141
|
- Cash Flow reconciles back to Balance Sheet cash line |
|
142
|
|
|
143
|
## DCF (Discounted Cash Flow) Model |
|
144
|
|
|
145
|
```python |
|
146
|
|
|
147
|
# Sheet structure: |
|
148
|
|
|
149
|
# 1. Assumptions: revenue, growth, margins, WACC, terminal growth, tax rate |
|
150
|
|
|
151
|
# 2. Projections: 5-10 year operating model |
|
152
|
|
|
153
|
# 3. DCF Valuation: FCF, discount factors, terminal value, enterprise value, equity bridge |
|
154
|
|
|
155
|
# Key formulas: |
|
156
|
|
|
157
|
# Unlevered FCF = EBIT * (1 - Tax) + D&A - Capex - Change in NWC |
|
158
|
|
|
159
|
# Discount Factor = 1 / (1 + WACC) ^ year |
|
160
|
|
|
161
|
# PV of FCF = FCF * Discount Factor |
|
162
|
|
|
163
|
# Terminal Value = Final Year FCF * (1 + g) / (WACC - g) |
|
164
|
|
|
165
|
# PV of Terminal = Terminal Value * Final Discount Factor |
|
166
|
|
|
167
|
# Enterprise Value = Sum of PV FCFs + PV of Terminal |
|
168
|
|
|
169
|
# Equity Value = EV - Net Debt + Cash |
|
170
|
|
|
171
|
# Per Share = Equity Value / Shares Outstanding |
|
172
|
|
|
173
|
# Sensitivity table: WACC vs Terminal Growth Rate |
|
174
|
|
|
175
|
# Use xlsxwriter data table or manual grid of IFERROR formulas |
|
176
|
|
|
177
|
``` |
|
178
|
|
|
179
|
**Formula patterns for DCF:** |
|
180
|
|
|
181
|
| Line Item | Formula | |
|
182
|
|
|
183
|
|---|---| |
|
184
|
|
|
185
|
| UFCF | `=EBIT*(1-TaxRate)+DA-Capex-DeltaNWC` | |
|
186
|
|
|
187
|
| Discount Factor | `=1/(1+WACC)^Year` | |
|
188
|
|
|
189
|
| PV of FCF | `=UFCF*DiscountFactor` | |
|
190
|
|
|
191
|
| Terminal Value (Gordon Growth) | `=FinalFCF*(1+TermGrowth)/(WACC-TermGrowth)` | |
|
192
|
|
|
193
|
| Terminal Value (Exit Multiple) | `=FinalEBITDA*ExitMultiple` | |
|
194
|
|
|
195
|
| Implied Share Price | `=(SUM(PV_FCFs)+PV_Terminal-NetDebt+Cash)/SharesOut` | |
|
196
|
|
|
197
|
## LBO (Leveraged Buyout) Model |
|
198
|
|
|
199
|
```python |
|
200
|
|
|
201
|
# Sheet structure: |
|
202
|
|
|
203
|
# 1. Assumptions: entry multiple, capital structure, operating assumptions, exit |
|
204
|
|
|
205
|
# 2. Sources & Uses: debt tranches, equity, fees |
|
206
|
|
|
207
|
# 3. Operating Model: revenue, EBITDA, EBIT, net income, FCF |
|
208
|
|
|
209
|
# 4. Debt Schedule: beginning balance, paydowns (mandatory + sweep), interest, ending balance |
|
210
|
|
|
211
|
# 5. Returns Analysis: exit EV, equity value, MOIC, IRR |
|
212
|
|
|
213
|
# Key formulas: |
|
214
|
|
|
215
|
# Entry EV = LTM EBITDA * Entry Multiple |
|
216
|
|
|
217
|
# Senior Debt = EBITDA * Senior Turns |
|
218
|
|
|
219
|
# Sponsor Equity = Total Uses - Total Debt |
|
220
|
|
|
221
|
# Cash Sweep Paydown = MIN(Beginning Balance, MAX(0, LFCF) * Sweep%) |
|
222
|
|
|
223
|
# Exit Equity = Exit EV - Net Debt at Exit + Cash |
|
224
|
|
|
225
|
# MOIC = Exit Equity / Initial Equity |
|
226
|
|
|
227
|
# IRR = (Exit Equity / Initial Equity) ^ (1 / Years) - 1 |
|
228
|
|
|
229
|
``` |
|
230
|
|
|
231
|
## Comparable Company Analysis (Comp Table) |
|
232
|
|
|
233
|
```python |
|
234
|
|
|
235
|
# Sheet structure: |
|
236
|
|
|
237
|
# 1. Company Data: name, ticker, market cap, enterprise value, revenue, EBITDA, net income |
|
238
|
|
|
239
|
# 2. Multiples: EV/Revenue, EV/EBITDA, P/E, EV/EBIT |
|
240
|
|
|
241
|
# 3. Summary Stats: mean, median, 25th/75th percentile for each multiple |
|
242
|
|
|
243
|
# 4. Implied Valuation: apply median multiples to target company metrics |
|
244
|
|
|
245
|
# Key formulas: |
|
246
|
|
|
247
|
# EV/Revenue = Enterprise Value / LTM Revenue |
|
248
|
|
|
249
|
# EV/EBITDA = Enterprise Value / LTM EBITDA |
|
250
|
|
|
251
|
# Implied EV = Target Metric * Median Multiple |
|
252
|
|
|
253
|
# Mean = AVERAGE(range), Median = MEDIAN(range) |
|
254
|
|
|
255
|
# Percentiles = PERCENTILE.INC(range, 0.25) and PERCENTILE.INC(range, 0.75) |
|
256
|
|
|
257
|
``` |
|
258
|
|
|
259
|
## Merger Model (M&A Accretion/Dilution) |
|
260
|
|
|
261
|
```python |
|
262
|
|
|
263
|
# Sheet structure: |
|
264
|
|
|
265
|
# 1. Acquirer Standalone: EPS, shares out, P/E, net income |
|
266
|
|
|
267
|
# 2. Target Standalone: same metrics |
|
268
|
|
|
269
|
# 3. Deal Assumptions: offer price, premium, % cash vs stock, synergies, integration costs |
|
270
|
|
|
271
|
# 4. Pro Forma: combined net income + synergies - financing costs, new share count |
|
272
|
|
|
273
|
# 5. Accretion/Dilution: (Pro Forma EPS - Acquirer EPS) / Acquirer EPS |
|
274
|
|
|
275
|
# Key formulas: |
|
276
|
|
|
277
|
# New Shares Issued = (Offer Price * Target Shares * Stock%) / Acquirer Share Price |
|
278
|
|
|
279
|
# Pro Forma Shares = Acquirer Shares + New Shares |
|
280
|
|
|
281
|
# Financing Cost = Offer Price * Target Shares * Cash% * Interest Rate * (1 - Tax) |
|
282
|
|
|
283
|
# Pro Forma NI = Acquirer NI + Target NI + Synergies - Integration - Financing Cost |
|
284
|
|
|
285
|
# Pro Forma EPS = Pro Forma NI / Pro Forma Shares |
|
286
|
|
|
287
|
# Accretion = (Pro Forma EPS / Acquirer EPS) - 1 |
|
288
|
|
|
289
|
``` |