3-Statement Modeling Strictly Confidential
Table of Contents Model Checks
Model Balance Sheet Unbalanced? No
© 2015 to 2024 CFI Education Inc.
This Excel model is for educational purposes only and should not be used for any other reason. All content is Copyright material of CFI Education Inc.
All rights reserved. The contents of this publication, including but not limited to all written material, content layout, images, formulas, and code, are protected
under international copyright and trademark laws. No part of this publication may be modified, manipulated, reproduced, distributed, or transmitted in any
form by any means, including photocopying, recording, or other electronic or mechanical methods, without prior written permission of the publisher,
except in the case of certain noncommercial uses permitted by copyright law.
[Link]
Assumptions & Drivers
All figures in USD thousands unless stated Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Days in Period 365 365 365 365 365 365 365 365
Income Statement
Sales Growth 6.0% 6.0% 6.0% 6.0% 6.0%
Gross Margin 55.0% 55.0% 55.0% 55.0% 55.0%
Distribution Expense (Percent of Sales) 7.5% 7.5% 7.5% 7.5% 7.5%
Marketing & Admin Expense (Fixed Cost) 32,063 33,346 34,680 36,067 37,509
Research Expense (Percent of Sales) 2.3% 2.3% 2.3% 2.3% 2.3%
Depreciation (Percent of Sales) 3.2% 3.2% 3.2% 3.2% 3.2%
Long-Term Debt Interest Rate (Average Debt) 6.2% 6.2% 6.2% 6.2% 6.2%
Tax Rate (Percent of EBT) 30.0% 30.0% 30.0% 30.0% 30.0%
Balance Sheet
Capital Asset Turnover Ratio (x) 4.25x 4.25x 4.25x 4.25x 4.25x
Receivable Days (Sales Basis) (Days) 60 60 60 60 60
Inventory Days (COGS Basis) (Days) 75 75 75 75 75
Payable Days (COGS Basis) (Days) 100 100 100 100 100
Income Tax Payable (Percent of Taxes) 37.0% 37.0% 37.0% 37.0% 37.0%
Long Term Debt 20,000 20,000 20,000 20,000 20,000
Common Share Capital 7,627 7,627 7,627 7,627 7,627
Dividend Payout Ratio 70.0% 70.0% 70.0% 70.0% 70.0%
Income Statement
All figures in USD thousands unless stated Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Revenues 81,422 86,698 93,086
Cost of Goods Sold (38,121) (37,756) (39,639)
Gross Profit
Distribution Expenses (5,884) (6,421) (6,166)
Marketing and Administration (23,507) (26,569) (30,830)
Research and Development (1,764) (1,931) (2,026)
Depreciation (2,960) (2,803) (2,907)
EBIT (Operating Profit)
Interest (1,240) (1,240) (1,240)
Earnings Before Taxes
Taxes (2,761) (2,429) (1,570)
Net Income
Common Dividends 4,312 4,209 2,931
Balance Sheet
All figures in USD thousands unless stated Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
ASSETS
Cash
Trade and Other Receivables 12,685 14,074 14,721
Inventories 7,168 7,691 8,036
Total Current Assets
Property Plant and Equipment 19,263 20,371 21,175
TOTAL ASSETS
LIABILITIES
Trade and Other Payables 10,001 10,504 11,077
Income Taxes Payable 1,088 894 581
Total Current Liabilities
Long-Term Debt 20,000 20,000 20,000
Total Liabilities
EQUITY
Common Stock and Additional Paid-In Capital 7,627 7,627 7,627
Retained Earnings 5,689 9,029 14,806
Total Shareholders' Equity
Total Liabilities & Equity
Balance Sheet Check
Cash Flow Statement
All figures in USD thousands unless stated Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Cash Flows from Operating Activities
Net Income
Depreciation
Changes in Operating Assets and Liabilities:
Trade and Other Receivables
Inventories
Trade and Other Payables
Income Taxes Payable
Cash Flows from Operating Activities
Investing Activities
Acquisitions of Property and Equipment (3,005) (3,911) (3,711)
Cash Flows from Investing Activities
Financing Activities
Issuance of Common Stock –
Dividends (current year)
Increase/(Decrease) in Long-Term Debt –
Cash Flows from Financing Activities
Cash and Equivalents, Beginning of the Year 5,671
Increase/(Decrease) in Cash and Equivalents
Cash and Equivalents, End of the Year
Working Capital and PP&E Schedule
All figures in USD thousands unless stated Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Days in Period
Revenue
Cost of Goods Sold
Taxes
Trade and Other Receivables (Days)
Inventory (Days)
Trade and Other Payables (Days)
Income Tax Payable (Percent of Taxes)
Working Capital
Trade and Other Receivables
Inventory
Trade and Other Payables
Income Tax Payable
Cash Changes from Working Capital
Trade and Other Receivables (1,280)
Inventory (470)
Trade and Other Payables 490
Income Taxes Payable 50
Cash from Working Capital Items
Revenue
Capital Asset Turnover Ratio (x)
Depreciation (Percent of Sales)
Property, Plant & Equipment (PP&E)
Beginning of Period
Capital Expenditures/Additions (Disposals)
Depreciation Expense
Net PP&E, End of Period
Capital Structure Schedules
All figures in USD thousands unless stated Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Long Term Debt (LTD)
Beginning of Period
Additions (Repayments) –
LTD, End of Period
Long-Term Debt Interest (Average Debt)
Long Term Debt Interest
Total Interest
Common Stock and Additional Paid-In Capital
Beginning of Period
Additions (Repayments) –
Common Stock, End of Period
Net Income
Dividends
Retained Earnings (RE)
Beginning of Period
Net Income
Dividends
RE, End of Period
Charts & Graphs
All figures in USD thousands unless stated Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Income Statement
Revenue
EBITDA
EBITDA Margin
Net Income
Net Income Margin
Cash Flow
Operating
Investing
Financing
Change in Cash
Income Statement Cash Flow Statement
Revenue EBITDA Margin Net Income Margin Investing Financing Operating Change in Cash
12 30.0% 12
25.0%
10 10
20.0%
8 8
15.0%
6 6
10.0%
4 4
5.0%
2 0.0% 2
– (5.0%) –
Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Assumptions & Drivers
All figures in USD thousands unless stated Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Days in Period 365 365 365 365 365 365 365 365
Income Statement
Sales Growth 6.5% 7.4% 6.0% 6.0% 6.0% 6.0% 6.0%
Gross Margin – – – 55.0% 55.0% 55.0% 55.0% 55.0%
Distribution Expense (Percent of Sales) 7.2% 7.4% 6.6% 7.5% 7.5% 7.5% 7.5% 7.5%
Marketing & Admin Expense (Fixed Cost) 23,507 26,569 30,830 32,063 33,346 34,680 36,067 37,509
Research Expense (Percent of Sales) 2.2% 2.2% 2.2% 2.3% 2.3% 2.3% 2.3% 2.3%
Depreciation (Percent of Sales) 3.6% 3.2% 3.1% 3.2% 3.2% 3.2% 3.2% 3.2%
Long-Term Debt Interest Rate (Average Debt) 6.2% 6.2% 6.2% 6.2% 6.2% 6.2% 6.2% 6.2%
Tax Rate (Percent of EBT) #DIV/0! #DIV/0! #DIV/0! 30.0% 30.0% 30.0% 30.0% 30.0%
Balance Sheet
Capital Asset Turnover Ratio (x) 4.23x 4.26x 4.40x 4.25x 4.25x 4.25x 4.25x 4.25x
Receivable Days (Sales Basis) (Days) 57 59 58 60 60 60 60 60
Inventory Days (COGS Basis) (Days) 69 74 74 75 75 75 75 75
Payable Days (COGS Basis) (Days) 96 102 102 100 100 100 100 100
Income Tax Payable (Percent of Taxes) 39.4% 36.8% 37.0% 37.0% 37.0% 37.0% 37.0% 37.0%
Long Term Debt 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000
Common Share Capital 7,627 7,627 7,627 7,627 7,627 7,627 7,627 7,627
Dividend Payout Ratio #DIV/0! #DIV/0! #DIV/0! 70.0% 70.0% 70.0% 70.0% 70.0%
Income Statement
All figures in USD thousands unless stated Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Revenues 81,422 86,698 93,086 98,671 – – – –
Cost of Goods Sold (38,121) (37,756) (39,639) – – – – –
Gross Profit 43,301 48,942 53,447 – – – – –
Distribution Expenses (5,884) (6,421) (6,166) – – – – –
Marketing and Administration (23,507) (26,569) (30,830) (32,063) (33,346) (34,680) (36,067) (37,509)
Research and Development (1,764) (1,931) (2,026) – – – – –
Depreciation (2,960) (2,803) (2,907) – – – – –
EBIT (Operating Profit) (34,115) (37,724) (41,929) – – – – –
Interest (1,240) (1,240) (1,240) – – – – –
Earnings Before Taxes (1,240) (1,240) (1,240) – – – – –
Taxes (2,761) (2,429) (1,570) – – – – –
Net Income (2,761) (2,429) (1,570) – – – – –
Common Dividends 4,312 4,209 2,931 – – – – –
Balance Sheet
All figures in USD thousands unless stated Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
ASSETS
Cash – – – – – – – –
Trade and Other Receivables 12,685 14,074 14,721 – – – – –
Inventories 7,168 7,691 8,036 – – – – –
Total Current Assets 19,853 21,765 22,758 – – – – –
Property Plant and Equipment 19,263 20,371 21,175 – – – – –
TOTAL ASSETS 19,263 20,371 21,175 – – – – –
LIABILITIES
Trade and Other Payables 10,001 10,504 11,077 – – – – –
Income Taxes Payable 1,088 894 581 – – – – –
Total Current Liabilities 11,089 11,398 11,658 – – – – –
Long-Term Debt 20,000 20,000 20,000 – – – – –
Total Liabilities 20,000 20,000 20,000 – – – – –
EQUITY
Common Stock and Additional Paid-In Capital 7,627 7,627 7,627 – – – – –
Retained Earnings 5,689 9,029 14,806 – – – – –
Total Shareholders' Equity 13,316 16,656 22,433 – – – – –
Total Liabilities & Equity – – – – – – – –
Balance Sheet Check – – – – – – – –
Cash Flow Statement
All figures in USD thousands unless stated Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Cash Flows from Operating Activities
Net Income – – – – – – – –
Depreciation 2,960 2,803 2,907 – – – – –
Changes in Operating Assets and Liabilities:
Trade and Other Receivables (1,280) – – – – – – –
Inventories (470) – – – – – – –
Trade and Other Payables 490 – – – – – – –
Income Taxes Payable 50 – – – – – – –
Cash Flows from Operating Activities – – – – – – – –
Investing Activities
Acquisitions of Property and Equipment (3,005) (3,911) (3,711) – – – – –
Cash Flows from Investing Activities (3,005) (3,911) (3,711) – – – – –
Financing Activities
Issuance of Common Stock – – – – – – – –
Dividends (current year) (4,312) (4,209) (2,931) – – – – –
Increase/(Decrease) in Long-Term Debt – – – – – – – –
Cash Flows from Financing Activities – – – – – – – –
Cash and Equivalents, Beginning of the Year 5,671 – – – – – – –
Increase/(Decrease) in Cash and Equivalents – – – – – – – –
Cash and Equivalents, End of the Year 5,671 – – – – – – –
Working Capital and PP&E Schedule
All figures in USD thousands unless stated Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Days in Period 365 365 365 365 365 365 365 365
Revenue 81,422 86,698 93,086 – – – – –
Cost of Goods Sold 38,121 37,756 39,639 – – – – –
Taxes 2,761 2,429 1,570 – – – – –
Trade and Other Receivables (Days) – – – 60 60 60 60 60
Inventory (Days) – – – 75 75 75 75 75
Trade and Other Payables (Days) – – – 100 100 100 100 100
Income Tax Payable (Percent of Taxes) – – – 37.0% 37.0% 37.0% 37.0% 37.0%
Working Capital
Trade and Other Receivables 12,685 14,074 14,721 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Inventory 7,168 7,691 8,036 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Trade and Other Payables 10,001 10,504 11,077 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Income Tax Payable 1,088 894 581 – – – – –
Cash Changes from Working Capital
Trade and Other Receivables (1,280) – – – – – – –
Inventory (470) – – – – – – –
Trade and Other Payables 490 – – – – – – –
Income Taxes Payable 50 – – – – – – –
Cash from Working Capital Items (1,210) – – – – – – –
Revenue – – – – – – – –
Capital Asset Turnover Ratio (x) – – – 4.25x 4.25x 4.25x 4.25x 4.25x
Depreciation (Percent of Sales) – – – 3.2% 3.2% 3.2% 3.2% 3.2%
Property, Plant & Equipment (PP&E)
Beginning of Period – – – – – – – –
Capital Expenditures/Additions (Disposals) 3,005 3,911 3,711 – – – – –
Depreciation Expense (2,960) (2,803) (2,907) – – – – –
Net PP&E, End of Period 19,263 – – #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Capital Structure Schedules
All figures in USD thousands unless stated Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Long Term Debt (LTD)
Beginning of Period – – – – – – – –
Additions (Repayments) – – – – – – – –
LTD, End of Period – – – 20,000 20,000 20,000 20,000 20,000
Long-Term Debt Interest (Average Debt) – – – 6.2% 6.2% 6.2% 6.2% 6.2%
Long Term Debt Interest 1,240 1,240 1,240 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Total Interest – – – – – – – –
Common Stock and Additional Paid-In Capital
Beginning of Period – – – – – – – –
Additions (Repayments) – – – – – – – –
Common Stock, End of Period – – – 7,627 7,627 7,627 7,627 7,627
Net Income – – – – – – – –
Dividends 4,312 4,209 2,931 – – – – –
Retained Earnings (RE)
Beginning of Period – – – – – – – –
Net Income – – – – – – – –
Dividends – – – – – – – –
RE, End of Period 5,689 – – – – – – –
Charts & Graphs
All figures in USD thousands unless stated Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Income Statement
Revenue 81,422 86,698 93,086 – – – – –
EBITDA 2,960 2,803 2,907 – – – – –
EBITDA Margin #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Net Income – – – – – – – –
Net Income Margin #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Cash Flow
Operating – – – – – – – –
Investing – – – – – – – –
Financing – – – – – – – –
Change in Cash – – – – – – – –
Income Statement Cash Flow Statement
Revenue EBITDA Margin Net Income Margin Investing Financing Operating Change in Cash
100,000 30.0% 1
90,000
25.0%
80,000
70,000 20.0%
60,000 15.0%
50,000 1
10.0%
40,000
30,000 5.0%
20,000
0.0%
10,000
– (5.0%) –
Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Year -2 Year -1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5