Scribd
Upload
26 views16 pages

Financial Modeling

The document presents a detailed income statement over four years, showing revenue growth from $1.64 million in Year 1 to $10.63 million in Year 4. It includes assumptions for revenue, cost of goods sold, operating expenses, and net income, with projections for different scenarios. The cash flow statement indicates positive operating cash flow and free cash flow, despite debt repayments impacting net cash flow.

Uploaded by

huynhthianhthu.gsc
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
26 views16 pages

Financial Modeling

The document presents a detailed income statement over four years, showing revenue growth from $1.64 million in Year 1 to $10.63 million in Year 4. It includes assumptions for revenue, cost of goods sold, operating expenses, and net income, with projections for different scenarios. The cash flow statement indicates positive operating cash flow and free cash flow, despite debt repayments impacting net cash flow.

Uploaded by

huynhthianhthu.gsc
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
You are on page 1/ 16

Income Statement Year 1 Year 2 Year 3

Revenue

Gross Revenue $ 2,000,000.00 $ 3,000,000 $ 4,000,000


Refunds (160,000) (240,000) (320,000)
Discounts (200,000) (300,000) (400,000)
Net Revenue $ 1,640,000 $ 2,460,000 $ 3,280,000

Cost of Goods Sold


Product $ 574,000 $ 861,000 $ 1,148,000
Fulfillment 82,000 246,000 328,000
Merchant Services 82,000 123,000 164,000
Total COGS $ 738,000 $ 1,230,000 $ 1,640,000

Gross Margin $ 902,000 $ 1,230,000 $ 1,640,000


GM % 55% 50% 50%

Operating Expenses
Personnel $ 328,000 $ 492,000 $ 656,000
Marketing 164,000 246,000 328,000
Other 82,000 123,000 164,000
Depreciation 40,000 90,000 146,000
Total OPEX $ 614,000 $ 951,000 $ 1,294,000

Operating Income $ 288,000 $ 279,000 $ 346,000

Interest $ 192,000 $ 240,000 $ 168,000

NI Before Taxes $ 96,000 $ 39,000 $ 178,000

Taxes $ 19,200 $ 7,800 $ 35,600

Net Income $ 76,800 $ 31,200 $ 142,400


NI % 5% 1% 4%

EBITDA $ 328,000 $ 369,000 $ 492,000

ASSUMPTIONS (Live Case)

Revenue
New Customers $ 50,000 75,000 100,000
AOV $ 40 $ 40 $ 40
Refunds (as % of rev) 8% 8% 8%
Discounts 10% 10% 10%
0 0 0
COGS 0 0 0
Product 35% 35% 35%
Fulfillment 5% 10% 10%
Merchant Services 5% 5% 5%
0 0 0
Operating Expenses 0 0 0
Personnel 20% 20% 20%
Marketing 10% 10% 10%
Other 5% 5% 5%
Tax Rate 20% 20% 20%

Scenario 1 (Lower Case)

Revenue
New Customers 50,000 75,000 100,000
AOV $ 40 $ 40 $ 40
Refunds (as % of rev) 8% 8% 8%
Discounts 10% 10% 10%

COGS
Product 35% 35% 35%
Fulfillment 5% 10% 10%
Merchant Services 5% 5% 5%

Operating Expenses
Personnel 20% 20% 20%
Marketing 10% 10% 10%
Other 5% 5% 5%
Tax Rate 20% 20% 20%

Scenario 2 (Upper Case)

Revenue
New Customers 100,000 200,000 300,000
AOV $ 45 $ 45 $ 45
Refunds (as % of rev) 8% 8% 8%
Discounts 10% 10% 10%

COGS
Product 35% 35% 35%
Fulfillment 5% 10% 10%
Merchant Services 5% 5% 5%
Operating Expenses
Personnel 20% 20% 20%
Marketing 10% 10% 10%
Other 5% 5% 5%
Tax Rate 25% 25% 25%
Year 4

Scenario 1

$ 6,000,000
(480,000)
(600,000)
$ 4,920,000

$ 1,722,000
492,000
246,000
$ 2,460,000

$ 2,460,000
50%

$ 984,000
492,000
246,000
121,000
$ 1,843,000

$ 617,000

$ 96,000

$ 521,000

$ 104,200

$ 416,800
8%

$ 738,000

150,000
$ 40
8%
10%
0
0
35%
10%
5%
0
0
20%
10%
5%
20%

150,000
$ 40
8%
10%

35%
10%
5%

20%
10%
5%
20%

400,000
$ 45
8%
10%

35%
10%
5%
20%
10%
5%
25%
Income Statement Year 1 Year 2 Year 3

Revenue

Gross Revenue $ 3,750,000 $ 5,000,000 $ 7,500,000


Refunds (187,500) (250,000) (375,000)
Discounts (375,000) (500,000) (750,000)
Net Revenue $ 3,187,500 $ 4,250,000 $ 6,375,000

Cost of Goods Sold


Product $ 1,115,625 $ 1,487,500 $ 2,231,250
Fulfillment 159,375 212,500 318,750
Merchant Services 95,625 127,500 191,250
Total COGS $ 1,370,625 $ 1,827,500 $ 2,741,250

Gross Margin $ 1,816,875 $ 2,422,500 $ 3,633,750


GM % 57% 57% 57%

Operating Expenses
Personnel $ 637,500 $ 850,000 $ 1,275,000
Marketing 318,750 425,000 637,500
Other 159,375 212,500 318,750
Depreciation 31,667 65,000 103,333
Total OPEX $ 1,147,292 $ 1,552,500 $ 2,334,583

Operating Income $ 669,583 $ 870,000 $ 1,299,167

Interest $ 192,000 $ 240,000 $ 168,000

NI Before Taxes $ 477,583 $ 630,000 $ 1,131,167

Taxes $ 100,292 $ 132,300 $ 237,545

Net Income $ 377,291 $ 497,700 $ 893,622


NI % 12% 12% 14%

EBITDA $ 701,250 $ 935,000 $ 1,402,500

ASSUMPTIONS

Revenue
New Customers $ 75,000 $ 100,000 $ 150,000
AOV $ 50 $ 50 $ 50
Refunds (as % of rev) 5% 5% 5%
Discounts 10% 10% 10%
COGS
Product 35% 35% 35%
Fulfillment 5% 5% 5%
Merchant Services 3% 3% 3%

Operating Expenses
Personnel 20% 20% 20%
Marketing 10% 10% 10%
Other 5% 5% 5%
Tax Rate 21% 21% 21%
Year 4

$ 12,500,000
(625,000)
(1,250,000)
$ 10,625,000

$ 3,718,750
531,250
318,750
$ 4,568,750

$ 6,056,250
57%

$ 2,125,000
1,062,500
531,250
86,667
$ 3,805,417

$ 2,250,833

$ 96,000

$ 2,154,833

$ 452,515

$ 1,702,318
16%

$ 2,337,500

$ 250,000
$ 50
5%
10%
35%
5%
3%

20%
10%
5%
21%
Capex & Depreciation Useful Life (Years) Year 1 Year 2

Capex
Servers 5 $ 75,000
Custom Software 2 $ 50,000 $ 100,000
Forklift 6
Total Capex $ 125,000 $ 100,000

Depreciation
Servers $ 15,000 $ 15,000
Custom Software $ 25,000 $ 75,000
Forklift
Total D&A $ 40,000 $ 90,000
Year 3 Year 4

$ 100,000
$ 30,000
$ 130,000 $ -

$ 15,000 $ 15,000
$ 125,000 $ 100,000
$ 5,000 $ 5,000
$ 145,000 $ 120,000
Balance Sheet Dec 31, Year 0 Year 1
Historicals
ASSETS

Cash $ 4,250,000 $ 4,089,791


Accounts Receivable $ 120,000 159,375
Total Current Assets 4,370,000 4,249,166

Fixed Assets $ 40,000 165,000


Accumulated Depreciation $ (10,000) (50,000)
Net Fixed Assets 30,000 115,000

Total Assets 4,400,000 4,364,166

LIABILITIES

Accounts Payable $ 75,000 191,250


Deferred Revenue $ 25,000 95,625
Total Current Assets 100,000 286,875

Long Term Debt $ 3,000,000 $ 2,400,000

Total Liabilities 3,100,000 2,686,875

EQUITY

Common Stock $ 50,000 $ 50,000


Retained Earnings $ 1,250,000 $ 1,627,291
Total Shareholders Equity 1,300,000 1,677,291

Liabilities & Shareholders Equity 4,400,000 4,364,166

Balance Check - -

ASSUMPTIONS

Net Revenue $ 3,187,500


AR (% of revenue) 5%
AP 6%
Deferred Rev 3%

Net Borrowing
Debt Payments $ 600,000
Interest Rate 8%
Interest Payments $ 192,000
Year 2 Year 3 Year 4

$ 5,304,991 $ 5,632,363 $ 7,149,681


212,500 318,750 531,250
5,517,491 5,951,113 7,680,931

265,000 395,000 395,000


(140,000) (285,000) (405,000)
125,000 110,000 (10,000)

5,642,491 6,061,113 7,670,931

255,000 382,500 637,500


212,500 510,000 1,062,500
467,500 892,500 1,700,000

$ 3,000,000 $ 2,100,000 $ 1,200,000

3,467,500 2,992,500 2,900,000

$ 50,000 $ 50,000 $ 50,000


$ 2,124,991 $ 3,018,613 $ 4,720,931
2,174,991 3,068,613 4,770,931

5,642,491 6,061,113 7,670,931

- - -

$ 4,250,000 $ 6,375,000 $ 10,625,000


5% 5% 5%
6% 6% 6%
5% 8% 10%

$ 1,500,000
$ 900,000 $ 900,000 $ 900,000
8% 8% 8%
$ 240,000 $ 168,000 $ 96,000
Cash Flow Statement Year 1 Year 2 Year 3

Net Income $ 377,291 $ 497,700 $ 893,622

Operating Activities
Depreciation 40,000 90,000 145,000
Chg in AR (39,375) (53,125) (106,250)
Chg in AP 116,250 63,750 127,500
Chg in Def Rev 70,625 116,875 297,500
Operating Cash Flow $ 564,791 $ 715,200 $ 1,357,372

Investing Activities
Capex 125,000 100,000 130,000

Free Cash Flow $ 439,791 $ 615,200 $ 1,227,372

Financing Activities
Debt Repayment (600,000) (900,000) (900,000)
Net Borrowings - 1,500,000 -
NCF from Financing (600,000) 600,000 (900,000)

Net Cash Flow $ (160,209) $ 1,215,200 $ 327,372


Year 4

$ 1,702,318

120,000
(212,500)
255,000
552,500
$ 2,417,318

$ 2,417,318

(900,000)
-
(900,000)

$ 1,517,318

You might also like