1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
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
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293# Formula Reference
**IMPORTANT:** Use the formulas outlined in this reference document unless otherwise specified by the user.
---
## Core Linkages
```
Balance Sheet: Assets = Liabilities + Equity
Net Income: IS Net Income β CF Operations (starting point)
Cash Flow: ΞCash = CFO + CFI + CFF
Cash Tie-Out: Ending Cash (CF) = Cash (BS Asset)
Cash Monthly/Annual: Closing Cash (Monthly) = Closing Cash (Annual)
Retained Earnings: Prior RE + Net Income - Dividends = Ending RE
Equity Raise: ΞCommon Stock/APIC (BS) = Equity Issuance (CFF)
Year 0 Equity: Equity Raised (Year 0) = Beginning Equity (Year 1)
```
## Gross Profit Calculation
**IMPORTANT:** Gross Profit must be calculated from Net Revenue, not Gross Revenue.
```
Net Revenue - Cost of Revenue = Gross Profit
```
| Term | Definition |
|------|------------|
| Gross Revenue | Total revenue before any deductions |
| Net Revenue | Gross Revenue - Returns - Allowances - Discounts |
| Cost of Revenue | Direct costs attributable to production of goods/services sold |
| Gross Profit | Net Revenue - Cost of Revenue |
**Note:** Always use Net Revenue (also called "Net Sales" or simply "Revenue" on most financial statements) as the starting point for profitability calculations. Gross Revenue overstates the true top-line performance.
## Margin Formulas
```
Gross Margin % = Gross Profit / Net Revenue
EBITDA = EBIT + D&A (or = Gross Profit - OpEx)
EBITDA Margin % = EBITDA / Net Revenue
EBIT Margin % = EBIT / Net Revenue
Net Income Margin % = Net Income / Net Revenue
```
## Credit Metric Formulas
```
Total Debt = Current Portion of Debt + Long-Term Debt
Net Debt = Total Debt - Cash
Total Debt / EBITDA = Total Debt / EBITDA (from IS)
Net Debt / EBITDA = Net Debt / EBITDA (from IS)
Interest Coverage = EBITDA / Interest Expense (from IS)
Net Int Exp % Debt = Net Interest Expense / Long-Term Debt
Debt / Total Cap = Total Debt / (Total Debt + Total Equity)
Debt / Equity = Total Debt / Total Equity
Current Ratio = Total Current Assets / Total Current Liabilities
Quick Ratio = (Total Current Assets - Inventory) / Total Current Liabilities
```
## Forecast Formulas (% of Net Revenue Method)
```
Cost of Revenue (Forecast) = Net Revenue Γ Cost of Revenue % Assumption
S&M (Forecast) = Net Revenue Γ S&M % Assumption
G&A (Forecast) = Net Revenue Γ G&A % Assumption
R&D (Forecast) = Net Revenue Γ R&D % Assumption
SBC (Forecast) = Net Revenue Γ SBC % Assumption
```
## Working Capital Formulas
```
Accounts Receivable
Prior AR
+ Revenue (from IS)
- Cash Collections (plug)
= Ending AR
DSO = (AR / Revenue) Γ 365
Inventory
Prior Inventory
+ Purchases (plug)
- COGS (from IS)
= Ending Inventory
DIO = (Inventory / COGS) Γ 365
Accounts Payable
Prior AP
+ Purchases (from Inventory calc)
- Cash Payments (plug)
= Ending AP
DPO = (AP / COGS) Γ 365
Net Working Capital = AR + Inventory - AP
ΞWC = Current NWC - Prior NWC
```
## D&A Schedule Formulas
```
Beginning PP&E (Gross)
+ CapEx
= Ending PP&E (Gross)
Beginning Accumulated Depreciation
+ Depreciation Expense
= Ending Accumulated Depreciation
PP&E (Net) = Gross PP&E - Accumulated Depreciation
```
## Debt Schedule Formulas
```
Beginning Debt Balance
+ New Borrowings
- Repayments
= Ending Debt Balance
Interest Expense = Avg Debt Balance Γ Interest Rate
(Use beginning balance to avoid circularity, or iterate if circular refs enabled)
```
## Retained Earnings Formula
```
Beginning Retained Earnings
+ Net Income (from IS)
+ Stock-Based Compensation (SBC) (from IS)
- Dividends
= Ending Retained Earnings
```
## NOL (Net Operating Loss) Schedule Formulas
```
NOL CARRYFORWARD SCHEDULE
Beginning NOL Balance (Year 1 / Formation = 0)
+ NOL Generated (if EBT < 0, then ABS(EBT), else 0)
- NOL Utilized (limited by taxable income and utilization cap)
= Ending NOL Balance
STARTING BALANCE RULE
For a new business or first modeled period:
Beginning NOL Balance = 0
NOL can only increase through realized losses (EBT < 0)
NOL cannot be created from thin air or assumed
NOL UTILIZATION CALCULATION
Pre-Tax Income (EBT)
If EBT > 0:
NOL Available = Beginning NOL Balance
Utilization Limit = EBT Γ 80% (post-2017 federal limit)
NOL Utilized = MIN(NOL Available, Utilization Limit)
Taxable Income = EBT - NOL Utilized
If EBT β€ 0:
NOL Utilized = 0
Taxable Income = 0
NOL Generated = ABS(EBT)
TAX CALCULATION WITH NOL
Taxes Payable = MAX(0, Taxable Income Γ Tax Rate)
(Taxes cannot be negative; losses create NOL asset instead)
DEFERRED TAX ASSET (DTA) FOR NOL
DTA - NOL Carryforward = Ending NOL Balance Γ Tax Rate
ΞDTA = Current DTA - Prior DTA
(Increase in DTA = non-cash benefit on CF)
(Decrease in DTA = non-cash expense on CF)
```
## Balance Sheet Structure
```
ASSETS
Cash (from CF ending cash)
Accounts Receivable (from WC)
Inventory (from WC)
Total Current Assets
PP&E, Net (from DA)
Deferred Tax Asset - NOL (from NOL schedule)
Total Non-Current Assets
Total Assets
LIABILITIES
Accounts Payable (from WC)
Current Portion of Debt (from Debt)
Total Current Liabilities
Long-Term Debt (from Debt)
Total Liabilities
EQUITY
Common Stock
Retained Earnings (from RE schedule)
Total Equity
CHECK: Assets - Liabilities - Equity = 0
```
## Cash Flow Statement Structure
```
CASH FROM OPERATIONS (CFO)
Net Income (LINK: IS)
+ D&A (LINK: DA schedule)
+ Stock-Based Compensation (SBC) (LINK: IS or Assumptions)
- ΞDTA (Deferred Tax Asset) (LINK: NOL schedule; increase in DTA = use of cash)
- ΞAR (LINK: WC)
- ΞInventory (LINK: WC)
+ ΞAP (LINK: WC)
= CFO
CASH FROM INVESTING (CFI)
- CapEx (LINK: DA schedule)
= CFI
CASH FROM FINANCING (CFF)
+ Debt Issuance (LINK: Debt)
- Debt Repayment (LINK: Debt)
+ Equity Issuance (LINK: BS Common Stock/APIC)
- Dividends (LINK: RE schedule)
= CFF
Net Change in Cash = CFO + CFI + CFF
Beginning Cash
+ Net Change in Cash
= Ending Cash (LINK TO: BS Cash)
```
## Income Statement Structure
```
Net Revenue
Growth %
(-) Cost of Revenue
% of Net Revenue
ββββββββββββββββ
Gross Profit (= Net Revenue - Cost of Revenue)
Gross Margin %
(-) S&M
% of Net Revenue
(-) G&A
% of Net Revenue
(-) R&D
% of Net Revenue
(-) D&A
(-) SBC
% of Net Revenue
ββββββββββββββββ
EBIT
EBIT Margin %
EBITDA
EBITDA Margin %
(-) Interest Expense
ββββββββββββββββ
EBT (Pre-Tax Income)
(-) NOL Utilization (from NOL schedule, reduces taxable income)
ββββββββββββββββ
Taxable Income
(-) Taxes (Taxable Income Γ Tax Rate)
ββββββββββββββββ
Net Income
Net Income Margin %
```
## Check Formulas
```
BS Balance Check: = Assets - Liabilities - Equity (must = 0)
Cash Tie-Out: = BS Cash - CF Ending Cash (must = 0)
RE Roll-Forward: = Prior RE + NI + SBC - Div - BS RE (must = 0)
DTA Tie-Out: = NOL Schedule DTA - BS DTA (must = 0)
Equity Raise Tie-Out: = ΞCommon Stock/APIC (BS) - Equity Issuance (CFF) (must = 0)
Year 0 Equity Tie-Out: = Equity Raised (Year 0) - Beginning Equity (Year 1) (must = 0)
Cash Monthly vs Annual: = Closing Cash (Monthly) - Closing Cash (Annual) (must = 0)
NOL Utilization Cap: = NOL Utilized β€ EBT Γ 80% (must be TRUE for post-2017)
NOL Non-Negative: = Ending NOL Balance β₯ 0 (must be TRUE)
NOL Starting Balance: = Beginning NOL (Year 1) = 0 (must be TRUE for new business)
NOL Accumulation: = NOL increases only when EBT < 0 (losses generate NOL)
```