3 statement financial model: How to build from start to finish
Income statement / cash flow => flow statements, represents period of time
Balance sheet => represents one specific moment in time
Income statement => money you made and expenses related to those sales
Cash flow => changes in what you own and what you owe (fundraising/debt/cash, for example off P&L)
Step 1 Build income statement => leave depreciation & interest blank
Step 2 Create CAPEX and depreciation schedules
Step 3 Build balance sheet on historical #s
Step 4 Tied cash flow to the balance sheet
Income Statement Year 1 Year 2 Year 3 Year 4
Revenue
Gross Revenue $ 3,000,000 $ 4,800,000 $ 10,000,000 $ 18,000,000
Refunds - 150,000 - 240,000 - 500,000 - 900,000
Discounts - 240,000 - 384,000 - 800,000 - 1,440,000
Net Revenue $ 2,610,000 $ 4,176,000 $ 8,700,000 $ 15,660,000
Cost of Goods Sold
Product $ 913,500 $ 1,461,600 $ 3,045,000 $ 5,481,000
Fulfillment 130,500 208,800 435,000 783,000
Merchant Services 78,300 125,280 261,000 469,800
Total COGS $ 1,122,300 $ 1,795,680 $ 3,741,000 $ 6,733,800
Gross Margin $ 1,487,700 $ 2,380,320 $ 4,959,000 $ 8,926,200
GM % 57% 57% 57% 57%
Operating Expenses
Personnel $ 522,000 $ 835,200 $ 1,740,000 $ 3,132,000
Marketing 261,000 417,600 870,000 1,566,000
Other 130,500 208,800 435,000 783,000
Depreciation $ 31,667 $ 65,000 $ 103,333 $ 86,667
Total OPEX $ 945,167 $ 1,526,600 $ 3,148,333 $ 5,567,667
Operating Income $ 542,533 $ 853,720 $ 1,810,667 $ 3,358,533
Interest $ 192,000 $ 240,000 $ 168,000 $ 96,000
NI Before Taxes $ 350,533 $ 613,720 $ 1,642,667 $ 3,262,533
Taxes $ 73,612 $ 128,881 $ 344,960 $ 685,132
Net Income $ 276,921 $ 484,839 $ 1,297,707 $ 2,577,401
NI % 11% 12% 15% 16%
EBITDA $ 574,200 $ 918,720 $ 1,914,000 $ 3,445,200
ASSUMPTIONS
Revenue
New Customers 75,000 120,000 250,000 450,000
AOV $ 40 $ 40 $ 40 $ 40
Refunds (as % of rev) 5% 5% 5% 5%
Discounts 8% 8% 8% 8%
COGS
Product 35% 35% 35% 35%
Fulfillment 5% 5% 5% 5%
Merchant Services 3% 3% 3% 3%
Operating Expenses
Personnel 20% 20% 20% 20%
Marketing 10% 10% 10% 10%
Other 5% 5% 5% 5%
Depreciation other model other model other model other model
Interest other model other model other model other model
Tax Rate 21% 21% 21% 21%
Capex and Depreciation Useful Life (Years) Year 1 Year 2 Year 3
Capex
Servers 5 $ 75,000
Custom Software 3 $ 50,000 $ 100,000 $ 100,000
Forklift 6 $ 30,000
Total Capex $ 125,000 $ 100,000 $ 130,000
Depreciation
Servers $ 15,000 $ 15,000 $ 15,000
Custom Software $ 16,667 $ 50,000 $ 83,333
Forklift $ 5,000
Total D&A $ 31,667 $ 65,000 $ 103,333
Year 4
$ 15,000
$ 66,667
$ 5,000
$ 86,667
Balance Sheet Dec.31, Year 0 Year 1 Year 2 Year 3
Historicals
ASSETS
Cash $ 4,250,000 $ 3,957,988 $ 5,153,987 $ 6,057,467
Accounts Receivable $ 120,000 130,500 208,800 435,000
Total Current Assets 4,370,000 4,088,488 5,362,787 6,492,467
Fixed Assets $ 40,000 165,000 265,000 395,000
Accumulated Depreciation $ -10,000 - 41,667 - 106,667 - 210,000
Net Fixed Assets 30,000 123,333 158,333 185,000
Total Assets 4,400,000 4,211,821 5,521,120 6,677,467
LIABILITIES
Accounts Payable $ 75,000 156,600 250,560 522,000
Deferred Revenue $ 25,000 78,300 208,800 696,000
Total Current Assets 100,000 234,900 459,360 1,218,000
Long Term Debt $ 3,000,000 $ 2,400,000 $ 3,000,000 $ 2,100,000
TOTAL LIABILITIES 3,100,000 2,634,900 3,459,360 3,318,000
EQUITY
Common Stock $ 50,000 $ 50,000 $ 50,000 $ 50,000
Retained Earnings $ 1,250,000 $ 1,526,921 $ 2,011,760 $ 3,309,467
Total Shareholders Equity 1,300,000 $ 1,576,921 $ 2,061,760 $ 3,359,467
Liabilities & Shareholders Equity 4,400,000 4,211,821 5,521,120 6,677,467
Balance Check 0 0 0 0
ASSUMPTIONS
Net Revenue $ 2,610,000 $ 4,176,000 $ 8,700,000
AR (% of revenue) 5% 5% 5%
AP 6% 6% 6%
Deferred Revenue 3% 5% 8%
Net Borrowing $ 1,500,000
Debt Payments $ 600,000 $ 900,000 $ 900,000
Interest Rate 8% 8% 8%
Interest Payments $ 192,000.0 $ 240,000.0 $ 168,000.0
Year 4
$ 8,761,135
783,000
9,544,135
395,000
- 296,667
98,333
9,642,468
939,600
1,566,000
2,505,600
$ 1,200,000
3,705,600
$ 50,000
$ 5,886,868
$ 5,936,868
9,642,468
$ 15,660,000
5%
6%
10%
$ 900,000
8%
$ 96,000.0
Cash Flow Satatement Year 1 Year 2 Year 3 Year 4
Net Income $ 276,921 $ 484,839 $ 1,297,707 $ 2,577,401
Operating Activities
Depreciation 31,667 65,000 103,333 86,667
Chng in AR - 10,500 - 78,300 - 226,200 - 348,000
Chng in AP 81,600 93,960 271,440 417,600
Chng in Def Rev 53,300 130,500 487,200 870,000
Operating Cash Flow $ 432,988 $ 695,999 $ 1,933,480 $ 3,603,668
Investing Activities
Capex 125,000 100,000 130,000 -
Free Cash Flow $ 307,988 $ 595,999 $ 1,803,480 $ 3,603,668
Financing Activities
Debt Repayment - 600,000 - 900,000 - 900,000 - 900,000
Net Borrowings - 1,500,000 - -
NCF from Financing - 600,000 600,000 - 900,000 - 900,000
Net Cash Flow $ -292,012 $ 1,195,999 $ 903,480 $ 2,703,668
Why doesn't my balance sheet balance?
Checklist:
Is your net income (P&L) linked to retained earnings?
Is your net cash flow (CF statement) linked to cash
Have you included all of your balance sheet accounts in your cash flow statement?
Do your historical financials balance?
