Scribd
Upload
40 views15 pages

Build a 3-Statement Financial Model

This document outlines the steps to build a three-statement financial model, including the income statement, cash flow statement, and balance sheet. It provides detailed financial projections for a Mail Order Cookie Company over four years, including revenue, expenses, and net income. Additionally, it includes assumptions for revenue, costs, and capital expenditures, along with a checklist to ensure the balance sheet balances correctly.

Uploaded by

Rizky Destiana
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
40 views15 pages

Build a 3-Statement Financial Model

This document outlines the steps to build a three-statement financial model, including the income statement, cash flow statement, and balance sheet. It provides detailed financial projections for a Mail Order Cookie Company over four years, including revenue, expenses, and net income. Additionally, it includes assumptions for revenue, costs, and capital expenditures, along with a checklist to ensure the balance sheet balances correctly.

Uploaded by

Rizky Destiana
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

Build a 3 Statement Financial Model from Scratch

Income Statement & Cash flow=>Flow Statements (Represent Periods of Time)


Balance Sheet=Represents specific period of time
Income Statement=Money made and expenses
Cash Flow= changes in what you own and what you owe (example off a P&L)

Step 1. Build Income Statement (depreciation & bank interest blank)


Step 2. Create CAPEX and depreciation schedule (assets)
Step 3. Build balance sheet on historic data
Step 4. Cash flow tied to balance sheet
h

e)

bank interest blank)


Mail Order Cookie Company
Income Statement 1st Year 2nd Year 3rd Year

Revenue

Gross Revenue $ 1,750,000 $ 2,750,000 $ 5,500,000


Refunds (87,500) (137,500) (275,000)
Discounts (140,000) (220,000) (440,000)
Net Revenue $ 1,522,500 $ 2,392,500 $ 4,785,000

COGS (Cost of Goods Sold)


Product (Materials cost) $ 456,750 $ 717,750 $ 1,435,500
Fullfillment $ 106,575 $ 167,475 $ 334,950
Merchant Services $ 152,250 $ 239,250 $ 478,500
Total COGS $ 715,575 $ 1,124,475 $ 2,248,950

Gross Margin $ 806,925 $ 1,268,025 $ 2,536,050


GM% 53% 53% 53%

Operating Expenses
HR $ 380,625 $ 598,125 $ 1,196,250
Marketing $ 152,250 $ 239,250 $ 478,500
Misc $ 76,125 $ 119,625 $ 239,250
Depreciation (other model) $ 26,667 $ 60,000 $ 99,333
Total OPEX (Operating Expenses) $ 635,667 $ 1,017,000 $ 2,013,333

Operating Income $ 171,258 $ 251,025 $ 522,717

Interest(other model) $ 175,000 $ 196,000 $ 147,000

NI (Net Interest) Before Taxes $ (3,742) $ 55,025 $ 375,717

Taxes $ (823) $ 12,105 $ 82,658

Net Income $ (2,918) $ 42,919 $ 293,059


NI% 0% 2% 6%

EBITDA $ 197,925 $ 311,025 $ 622,050

MY ASSUMPTIONS

Revenue
New Customers 70,000 110,000 220,000
AOV (Average Order Value) $ 25 $ 25 $ 25
Refunds (as % rev) 5% 5% 5%
Discounts 8% 8% 8%

COGS (Cost of Goods Sold)


Product (Materials cost) 30% 30% 30%
Fullfillment 7% 7% 7%
Merchant Services 10% 10% 10%

Operating Expenses
HR 25% 25% 25%
Marketing 10% 10% 10%
Misc 5% 5% 5%
Depreciation (other model)

Interest on Debt (other model)

Tax Rate 22% 22% 22%


4th Year

$ 11,250,000
(562,500)
(900,000)
$ 9,787,500

$ 2,936,250
$ 685,125
$ 978,750
$ 4,600,125

$ 5,187,375
53%

$ 2,446,875
$ 978,750
$ 489,375
$ 82,667
$ 3,997,667

$ 1,189,708

$ 98,000

$ 1,091,708

$ 240,176

$ 851,533
9%

$ 1,272,375
450,000
$ 25
5%
8%

30%
7%
10%

25%
10%
5%

22%
Capex & Depreciation Useful Life (Years) Year 1 Year 2 Year 3

Capex
Laptops 5 $ 50,000
Custom Software Apps 3 $ 50,000 $ 100,000 $ 100,000
New Ovens 5 $ 30,000
Total Capex $ 100,000 $ 100,000 $ 130,000

Depreciation
Laptops $ 10,000 $ 10,000 $ 10,000
Custom Software Apps $ 16,667 $ 50,000 $ 83,333
New Ovens $ 6,000
Total D&A (Depreciation & Amortization) $ 26,667 $ 60,000 $ 99,333
Year 4

$ -

$ 10,000
$ 66,667
$ 6,000
$ 82,667
Balance Sheet Dec 31-Year 0 Year 1 Year 2
Historicals
ASSETS

Cash $ 4,250,000 $ 3,700,773 $ 4,116,293


Account Receivable (AR) $ 120,000 130,000 119,625
Total Current Assets $ 4,370,000 3,830,773 4,235,918

Fixed Assets $ 40,000 140,000 240,000


Accumulated Depreciation $ (10,000) (36,667) (96,667)
Net Fixed Assets $ 30,000 103,333 143,333

Total Assets $ 4,400,000 3,934,107 4,379,251

LIABILITIES

Accounts Payable (AP) $ 75,000 91,350 143,550


Deferred Revenue $ 25,000 45,675 95,700
Total Current Assets $ 100,000 137,025 239,250

Long Term Debt $ 3,000,000 $ 2,500,000 $ 2,800,000

Total Liabilities 3,100,000 2,637,025 3,039,250

EQUITY

Common Stock $ 50,000 $ 50,000 $ 50,000


Retained Earnings $ 1,250,000 $ 1,247,082 $ 1,290,001
Total Shareholders Equity $ 1,300,000 $ 1,297,082 $ 1,340,001

Liabilities & Shareholders Equity $ 4,400,000 $ 3,934,107 $ 4,379,251

Balance Check $ - $ - $ -

ASSUMPTIONS

Net Revenue $ 1,522,500 $ 2,392,500


Accounts Receivable (% of revenue) 5% 5%
Accounts Payable (% of revenue) 6% 6%
Deferred Revenue 3% 4%

Net Borrowing (New Debt Taken On) $ 1,000,000


Debt Payments $ 500,000 $ 700,000
Interest Rate 7% 7%
Interest Payments $ 175,000 $ 196,000
Year 3 Year 4

$ 3,846,160 $ 4,674,134
239,250 489,375
4,085,410 5,163,509

370,000 370,000
(196,000) (278,667)
174,000 91,333

4,259,410 5,254,843

287,100 587,250
239,250 783,000
526,350 1,370,250

$ 2,100,000 $ 1,400,000

2,626,350 2,770,250

$ 50,000 $ 50,000
$ 1,583,060 $ 2,434,593
$ 1,633,060 $ 2,484,593

$ 4,259,410 $ 5,254,843

$ - $ -

$ 4,785,000 $ 9,787,500
5% 5%
6% 6%
5% 8%

$ 700,000 $ 700,000
7% 7%
$ 147,000 $ 98,000
Cash Flow Statement 1st Year 2nd Year 3rd Year

Net Income $ (2,918) $ 42,919 $ 293,059

Operating Activities
Depreciations 26,667 60,000 99,333
Change in AR (10,000) 10,375 (119,625)
Change in AP 16,350 52,200 143,550
Change in Def Rev 20,675 50,025 143,550
Operating Cash Flow $ 50,773 $ 215,519 $ 559,867

Investing Activities
CapEx 100,000 100,000 130,000

Free Cash Flow $ (49,227) $ 115,519 $ 429,867

Financing Activities
Debt Repayment (500,000) (700,000) (700,000)
Net Borrowings - 1,000,000 -
NCF From Financing (500,000) 300,000 (700,000)

Net Cash Flow $ (549,227) $ 415,520 $ (270,133)


4th Year

$ 851,533

82,667
(250,125)
300,150
543,750
$ 1,527,974

$ 1,527,974

(700,000)
-
(700,000)

$ 827,974
Final Checks-Why doesn't my balance sheet balance?

Check-Off List

Is your net income (Profilt & Loss) linked to your Retained Earnings?
Is your cash flow (Cash Flows Statement) linked to your cash?
Hae you included all your balance sheet accounts in the cash flow statement?
Are your historical financial accounts balancing?

Common questions

Powered by AI

The Cash Flow Statement illustrates changes in the balance sheet's cash account as a result of operational, investing, and financing activities. Accuracy is vital because discrepancies can lead to incorrect financial analysis and decision-making. Ensuring the cash flow statement ties to the balance sheet confirms that all cash movements are properly recorded and that the cash position is accurately represented, reflecting the company's liquidity and financial stability .

Rising interest expenses, as the given data suggests, affect the company's net income negatively and signal increased debt levels or higher interest rates. This scenario necessitates strategic financial management, potentially redirecting focus on revenue growth or operational efficiencies to cover increased costs, affecting investment, expansion capabilities, and overall credit risk assessments .

Linking the income statement to retained earnings ensures that the net income figure accurately impacts the equity section of the balance sheet, maintaining the balance. Similarly, linking the cash flow statement to cash ensures that all changes in cash from operating, investing, and financing activities are accurately reflected, keeping the balance sheet balanced. This linkage is crucial for maintaining the integrity of financial statements and ensuring they reflect the true financial position of the entity .

Depreciation affects the income statement by reducing reported earnings, impacting net income and taxes payable. On the balance sheet, it accumulates over time, reducing the value of fixed assets. This systematic allocation impacts cash flow calculations, as depreciation is added back to net income in the cash flow statement, reflecting its non-cash nature, thus influencing liquidity analyses and financial projections .

Operating expenses, such as HR, marketing, and miscellaneous expenses, directly reduce the company's operating income. HR expenses consistently make up 25% of net revenue, indicating a significant impact on profitability. Marketing also consistently represents 10%, suggesting its role in driving revenue growth. Analysing these expenses' proportional impact on financial performance aids strategic decisions on resource allocation to optimize profitability .

Deferred revenue represents cash received for services not yet delivered, showing as a liability until the service is rendered. Its increase year over year indicates growth in prepayments for future services, affecting liquidity and forecasting future revenue recognition. Accurate management is critical for balanced cash flow and revenue projections, ensuring liability levels reflect actual service fulfillment obligations .

The company's consistent investment in capital expenditures, such as laptops, software, and new ovens, suggests a focus on long-term operational efficiency and competitiveness. These investments, while initially reducing free cash flow, are expected to enhance production capabilities and technological capacity, leading to future revenue growth and profitability. The strategy reflects a balance between maintaining liquidity and pursuing growth opportunities .

The Gross Margin percentage, which remained constant at 53% over the evaluated period, indicates the consistency in the company's efficiency in converting revenue into actual profit before deducting other operational costs. Its stability over time suggests effective management of production and sales strategies. Analyzing trends in gross margin helps assess pricing strategy, COGS management, and overall cost-efficiency, crucial for long-term financial health .

Changes in accounts receivable impact cash flow through operating activities, as they represent cash not yet received from sales. An increase in accounts receivable, as seen from Year 1 to Year 3, reduces cash flow, indicating sales converted into income on paper but not yet actual cash, stressing liquidity. A decrease implies cash collection acceleration, improving liquidity .

EBITDA provides insight into a company's operational profitability, excluding the impact of non-cash and non-operating costs like taxes, interest, depreciation, and amortization, offering a view of cash-generating operational capacity. However, it ignores capital structure, working capital changes, and capital expenditures, which can mislead regarding cash flow sustainability and actual financial health .

You might also like