EFM DCF Model Template 4.4-Basic
EFM DCF Model Template 4.4-Basic
EFM DCF Model Template 4.4-Basic
Project name
Model Version
4.4 Basic
Worksheets
Terms and Abbreviationss
Executive Summary
Assumptions
Financials
DCF
Terminal Value
License: The duration of the license to use this spreadsheet is unlimited, and the user will have the right to download, store,
copy, edit, print & use this spreadsheet for personal or professional use. The license does not entitle for future model
updates.
Restrictions: The user is not allowed to publish our spreadsheet online, sell our spreadsheet, or provide public access. The
model can be used within your team provided that your team size does not exceed 20 persons.
Disclaimer: This financial model template was developed by eFinancialModels and all rights are reserved.
eFinancialModels.com provides the template as is and assumes no liability for any eventual mistakes within the model nor
ommissions of it. All eventual data includes serves as example only and cannot be relied upon. Each template needs to be
adjusted for the individual project and customized by the user. The user is self-responsible to thoroughly review and adjust
the model. eFinancialModels.com welcomes your feedback.
Confidential
Powered by
www.eFinancialModels.com
Instructions
Cell Colors
Assumption Cells which contain the assumptions. Are not linked to any other cells.
Linked Assumption Cells which also contain assumptions but are linked to other assumption. Can either be left "as is" or overwritten if needed.
Link and calculations Cells which contain links or calculations. Should not be changed unless you want to change the logic of the model
Linked to cells in other sheets Cells which contains links from cells from other sheets
Powered by
www.eFinancialModels.com
Financial Summary
All amounts in USD
1 Income Statement Unit 2023 2024 2025 2026 2027 2028 Balance Sheet Unit 2023 2024 2025 2026 2027 2028
Year Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Revenues USD 5,000,000 5,500,000 5,912,500 6,208,125 6,518,531 6,844,458 Cash USD 600,000 473,997 612,598 959,226 1,359,875 1,679,703
Revenue Growth % 5.3% 10.0% 7.5% 5.0% 5.0% 5.0% Receivables USD 450,000 527,397 566,952 595,300 625,065 656,318
Inventory USD 350,000 357,534 408,205 420,962 442,010 464,110
Direct Costs USD (2,500,000) (2,900,000) (3,311,000) (3,414,469) (3,585,192) (3,764,452) Other Current Assets USD 20,000 - - - - -
Gross Profit USD 2,500,000 2,600,000 2,601,500 2,793,656 2,933,339 3,080,006 Fixed Assets USD 2,400,000 2,660,000 2,770,000 2,740,000 2,630,000 2,630,000
Gross Profit Margin % % of Revenue 50.0% 47.3% 44.0% 45.0% 45.0% 45.0% Assets USD 3,820,000 4,018,928 4,357,756 4,715,488 5,056,950 5,430,131
OPEX USD (1,580,000) (1,350,000) (1,451,250) (1,567,350) (1,708,412) (1,879,253) Payables USD 340,000 119,178 136,068 140,321 147,337 154,703
EBITDA USD 920,000 1,250,000 1,150,250 1,226,306 1,224,928 1,200,753 Other Current Liabilities USD 10,000 - - - - -
EBITDA Margin % % of Revenue 18.4% 22.7% 19.5% 19.8% 18.8% 17.5% Financial debt USD 2,000,000 1,700,000 1,400,000 1,100,000 800,000 500,000
Equity USD 1,470,000 2,199,750 2,821,688 3,475,167 4,109,613 4,775,428
Depreciation USD (100,000) (240,000) (290,000) (330,000) (360,000) (300,000) Liabilities & Shareholder's Equity USD 3,820,000 4,018,928 4,357,756 4,715,488 5,056,950 5,430,131
EBIT USD 820,000 1,010,000 860,250 896,306 864,928 900,753
EBIT Margin % % of Revenue 16.4% 18.4% 14.5% 14.4% 13.3% 13.2% Financial Ratios
Current Ratio x 4.1x 11.4x 11.7x 14.1x 16.5x 18.1x
Extraordinary Expenses USD (220,000) - - - - - Financial Debt/EBITDA x 2.2x 1.4x 1.2x 0.9x 0.7x 0.4x
Interest Expense USD (36,500) (37,000) (31,000) (25,000) (19,000) (13,000) Equity % % 51.2% 64.2% 78.2% 96.1% 115.8% 132.8%
Income Taxes USD (140,875) (243,250) (207,313) (217,827) (211,482) (221,938)
Net Income USD 422,625 729,750 621,938 653,480 634,446 665,815 Cash Flow Statement Unit 2024 2025 2026 2027 2028
Net Income Margin % % of Revenue 8.5% 13.3% 10.5% 10.5% 9.7% 9.7% Year Year 1 Year 2 Year 3 Year 4 Year 5
Profitability & Efficiency Cash Flow from Operations USD 673,997 838,602 946,628 950,649 919,828
Revenues/Capital Employed x 1.8x 1.7x 1.7x 1.7x 1.8x 1.9x Cash Flow from Investments USD (500,000) (400,000) (300,000) (250,000) (300,000)
ROCE % 0.0% 24.1% 18.3% 18.6% 18.1% 18.9% Cash Flow from Financing USD (300,000) (300,000) (300,000) (300,000) (300,000)
ROA % 12.4% 19.6% 15.6% 15.0% 13.4% 12.9% Change in Cash USD (126,003) 138,602 346,628 400,649 319,828
ROE % 27.8% 39.8% 24.8% 20.8% 16.7% 15.0%
Financial Ratios
Interest Coverage x 22.5x 27.3x 27.8x 35.9x 45.5x
Debt Service Coverage x 0.2x 0.4x 0.4x 0.6x 0.8x
7,000,000 3,500,000
25.0%
6,000,000 3,000,000
20.0%
5,000,000 2,500,000
3,000,000 1,500,000
10.0%
2,000,000 1,000,000
5.0% 500,000
1,000,000
0
0 0.0% Year 1 Year 2 Year 3 Year 4 Year 5
Year 1 Year 2 Year 3 Year 4 Year 5
Net Working Capital Fixed Assets Cash
✘ Revenues ✘ Net Income ✘ ROCE
✘ EBITDA ✘ EBITDA Margin % ✘ Net Working Capital ✘ Fixed Assets Cash
3,000,000
4,000,000
2,000,000 PV of Terminal Value
Implied Multiples Year 1 TV Year 5
1,000,000 EV/EBITDA Exit Multiple x 6.2x 7.2x 3,000,000
0 2,000,000
Year 1 Year 2 Year 3 Year 4 Year 5 Terminal Value
1,000,000
Free Cash Flow to Firm Discounted Cash Flows
-
✘ Free Cash Flow to Firm ✘
Terminal Value (TV) ✘ Discounted Cash Flows Enterprise Value
8,000,000
Gordon Growth 8,691,146 5,677,314 8,691,146
7,204,520
7,000,000 Exit Multiple EV/EBITDA Exit Multiple (6.0x) 7,204,520 4,706,206 8,691,146
6,000,000 5,677,314
✘ TV (Year 5)
5,000,000 4,706,206
✘ PV of TV (Year 0)
4,000,000
0
Gordon Growth Exit Multiple EV/EBITDA Exit Multiple (6.0x)
Powered by
www.eFinancialModels.com
Assumptions
All amounts in USD
General Settings Business Plan Assumptions
Settings Income Statement -Year 2 -Year 1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Currency USD
First Forecast Year Year 2024 Revenues USD 4,500,000 4,750,000 5,000,000 5,500,000 5,912,500 6,208,125 6,518,531 6,844,458
Revenue Growth % NA 5.6% 5.3% 10.0% 7.5% 5.0% 5.0% 5.0%
General Assumptions
Depreciation period Years 10 Direct Costs USD 2,000,000 2,300,000 2,500,000 2,900,000 3,311,000 3,414,469 3,585,192 3,764,452
Interest rate % 2.0% Gross Profit USD 2,500,000 2,450,000 2,500,000 2,600,000 2,601,500 2,793,656 2,933,339 3,080,006
Income Tax rate % 25.0% Gross Profit Margin % % 55.6% 51.6% 50.0% 47.3% 44.0% 45.0% 45.0% 45.0%
Dividend Payout Ratio % 0.0%
OPEX USD 1,190,000 1,010,000 1,580,000 1,350,000 1,451,250 1,567,350 1,708,412 1,879,253
Net Working Capital EBITDA USD 1,310,000 1,440,000 920,000 1,250,000 1,150,250 1,226,306 1,224,928 1,200,753
Days Receivables Days Sales 35 EBITDA Margin % % 29.1% 30.3% 18.4% 22.7% 19.5% 19.8% 18.8% 17.5%
Days Inventory Days COGS 45
Days Payables Days COGS 15 Depreciation USD 100,000 100,000 100,000 240,000 290,000 330,000 360,000 300,000
Extraordinary Expenses USD 200,000 200,000 220,000 -
Interest Expense USD 30,000 31,500 36,500 37,000 31,000 25,000 19,000 13,000
Discount Rate Estimation - Weighted Average Cost of Capital Income Taxes USD 245,000 277,125 140,875 243,250 207,313 217,827 211,482 221,938
Net Income USD 735,000 831,375 422,625 729,750 621,938 653,480 634,446 665,815
Weight Net Income Margin % % 16.3% 17.5% 8.5% 13.3% 10.5% 10.5% 9.7% 9.7%
Equity % 70.0%
Debt % 30.0%
Total % 100.0% Balance Sheet -Year 2 -Year 1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Cost of Equity Cash USD 300,000 450,000 600,000 473,997 612,598 959,226 1,359,875 1,679,703
Risk free interest rate 2) % 2.0% Receivables USD 600,000 550,000 450,000 527,397 566,952 595,300 625,065 656,318
Beta (unlevered) 3) % 1.0x Inventory USD 450,000 550,000 350,000 357,534 408,205 420,962 442,010 464,110
Beta (levered) 4) x 1.43 Other Current Assets USD 40,000 40,000 20,000 - - - - -
Market Risk Premium USA 5) % 6.0% Fixed Assets USD 1,900,000 2,000,000 2,400,000 2,660,000 2,770,000 2,740,000 2,630,000 2,630,000
Equity Risk Premium % 8.57% Assets USD 3,290,000 3,590,000 3,820,000 4,018,928 4,357,756 4,715,488 5,056,950 5,430,131
Other Risk Premium % 2.0%
Cost of Equity % 12.6% Payables USD 230,000 330,000 340,000 119,178 136,068 140,321 147,337 154,703
Other Current Liabilities USD 40,000 40,000 10,000 - - - - -
Cost of Debt Financial debt USD 1,500,000 1,650,000 2,000,000 1,700,000 1,400,000 1,100,000 800,000 500,000
Risk free interest rate % 2.0% Equity USD 1,520,000 1,570,000 1,470,000 2,199,750 2,821,688 3,475,167 4,109,613 4,775,428
Debt risk premium 8) % 3.0% Liabilities & Shareholder's Equity USD 3,290,000 3,590,000 3,820,000 4,018,928 4,357,756 4,715,488 5,056,950 5,430,131
Pre-tax cost of debt % 5.0%
Tax rate % 25.0% Cash Flow Statement -Year 2 -Year 1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Cost of Debt (after tax) % 3.8%
CAPEX USD 500,000 400,000 300,000 250,000 300,000
Discount Rate (WACC) % 9.9% Change in Financial debt USD - 150,000 350,000 (300,000) (300,000) (300,000) (300,000) (300,000)
Discounting Method Years Mid-Year Terminal Value Model Terminal Value (Year 5) Calculations Explanation
Discount Rate (WACC) % 9.9% USD
Gordon Growth 8,691,146 => Gordon Growth Capitalized Free Cash Cash Flows which are assumed to grow forever
Model Checks Exit Multiple EV/EBITDA Exit Multiple (6.0x) 7,204,520 => EV/EBITDA Exit Multiple Determines the Terminal Value based on an Exit Multiple
Balance Sheet 0
Executive Summary 0 Terminal Value Assumptions by Model Selectors
Assumptions 0
Financials 7,902,239 Gordon Growth Discounting Period
DCF 0 Discount Rate % 9.9% Mid-Year 0.5
Terminal Value 0 Growth Rate % 2.5% End of Year 1.0
Exit Multiples
Label Exit Multiple
EV/EBITDA Exit Multiple x 6.0x
Powered by
www.eFinancialModels.com
Financial Projections
All amounts in USD
1 Income Statement Unit 2021 2022 2023 2024 2025 2026 2027 2028
Year -Year 2 -Year 1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Revenues USD 4,500,000 4,750,000 5,000,000 5,500,000 5,912,500 6,208,125 6,518,531 6,844,458
Revenue Growth % NA 5.6% 5.3% 10.0% 7.5% 5.0% 5.0% 5.0%
Direct Costs USD (2,000,000) (2,300,000) (2,500,000) (2,900,000) (3,311,000) (3,414,469) (3,585,192) (3,764,452)
Gross Profit USD 2,500,000 2,450,000 2,500,000 2,600,000 2,601,500 2,793,656 2,933,339 3,080,006###
Gross Profit Margin % % of Revenue 55.6% 51.6% 50.0% 47.3% 44.0% 45.0% 45.0% 45.0%
OPEX USD (1,190,000) (1,010,000) (1,580,000) (1,350,000) (1,451,250) (1,567,350) (1,708,412) (1,879,253)
EBITDA USD 1,310,000 1,440,000 920,000 1,250,000 1,150,250 1,226,306 1,224,928 1,200,753###
EBITDA Margin % % of Revenue 29.1% 30.3% 18.4% 22.7% 19.5% 19.8% 18.8% 17.5%
Depreciation USD (100,000) (100,000) (100,000) (240,000) (290,000) (330,000) (360,000) (300,000)
EBIT USD 1,210,000 1,340,000 820,000 1,010,000 860,250 896,306 864,928 900,753###
EBIT Margin % % of Revenue 26.9% 28.2% 16.4% 18.4% 14.5% 14.4% 13.3% 13.2%
Income Taxes USD (245,000) (277,125) (140,875) (243,250) (207,313) (217,827) (211,482) (221,938)
Net Income USD 735,000 831,375 422,625 729,750 621,938 653,480 634,446 665,815###
Net Income Margin % % of Revenue 16.3% 17.5% 8.5% 13.3% 10.5% 10.5% 9.7% 9.7%
Cash USD 300,000 450,000 600,000 473,997 612,598 959,226 1,359,875 1,679,703
Receivables USD 600,000 550,000 450,000 527,397 566,952 595,300 625,065 656,318
Inventory USD 450,000 550,000 350,000 357,534 408,205 420,962 442,010 464,110
Other Current Assets USD 40,000 40,000 20,000 - - - - -
Fixed Assets USD 1,900,000 2,000,000 2,400,000 2,660,000 2,770,000 2,740,000 2,630,000 2,630,000
Assets USD 3,290,000 3,590,000 3,820,000 4,018,928 4,357,756 4,715,488 5,056,950 5,430,131
Payables USD 230,000 330,000 340,000 119,178 136,068 140,321 147,337 154,703
Other Current Liabilities USD 40,000 40,000 10,000 - - - - -
Financial debt USD 1,500,000 1,650,000 2,000,000 1,700,000 1,400,000 1,100,000 800,000 500,000
Equity USD 1,520,000 1,570,000 1,470,000 2,199,750 2,821,688 3,475,167 4,109,613 4,775,428
Liabilities & Shareholder's Equity USD 3,290,000 3,590,000 3,820,000 4,018,928 4,357,756 4,715,488 5,056,950 5,430,131
Check 0 0 0 0 0 0 0 ###
0
Depreciation
Year 0 USD 2,400,000 240,000 240,000 240,000 240,000 240,000
Year 1 USD 500,000 50,000 50,000 50,000 50,000
Year 2 USD 400,000 40,000 40,000 40,000
Year 3 USD 300,000 30,000 30,000
Year 4 USD 250,000 25,000
Normalization adjustment USD 300,000 (85,000)
Depreciation USD 240,000 290,000 330,000 360,000 300,000
Financial Ratios
All amounts in USD
5 Financial Ratios Unit 2021 2022 2023 2024 2025 2026 2027 2028
Year -Year 2 -Year 1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Bank Ratios
Financial Debt/EBITDA x 1.1x 1.1x 2.2x 1.4x 1.2x 0.9x 0.7x 0.4x
Interest Coverage x 40.3x 42.5x 22.5x 27.3x 27.8x 35.9x 45.5x 69.3x
Debt Service Coverage x 0.5x 0.5x 0.2x 0.4x 0.4x 0.6x 0.8x 1.3x
Equity % % 55.9% 56.7% 51.2% 64.2% 78.2% 96.1% 115.8% 132.8%
Net Debt/Equity % 78.9% 76.4% 95.2% 55.7% 27.9% 4.1% -13.6% -24.7%
Liquidity Ratios
Cash Ratio x 1.1x 1.2x 1.7x 4.0x 4.5x 6.8x 9.2x 10.9x
Quick Ratio x 3.3x 2.7x 3.0x 8.4x 8.7x 11.1x 13.5x 15.1x
Current Ratio x 5.1x 4.3x 4.1x 11.4x 11.7x 14.1x 16.5x 18.1x
Growth Rates
Revenue growth % NA 5.6% 5.3% 10.0% 7.5% 5.0% 5.0% 5.0%
Gross Profit Growth % NA -2.0% 2.0% 4.0% 0.1% 7.4% 5.0% 5.0%
EBITDA Growth % NA 9.9% -36.1% 35.9% -8.0% 6.6% -0.1% -2.0%
EBIT Growth % NA 10.7% -38.8% 23.2% -14.8% 4.2% -3.5% 4.1%
Net Income Growth % NA 13.1% -49.2% 72.7% -14.8% 5.1% -2.9% 4.9%
Asset Growth % NA 9.1% 6.4% 5.2% 8.4% 8.2% 7.2% 7.4%
Equity Growth % NA 3.3% -6.4% 49.6% 28.3% 23.2% 18.3% 16.2%
Profitability
NOPLAT USD - - - 757,500 645,188 672,230 648,696 675,565
Revenues/Capital Employed x 1.7x 1.7x 1.8x 1.7x 1.7x 1.7x 1.8x 1.9x
ROCE % 0.0% 0.0% 0.0% 24.1% 18.3% 18.6% 18.1% 18.9%
ROA % 23.3% 25.1% 12.4% 19.6% 15.6% 15.0% 13.4% 12.9%
ROE % 48.4% 53.8% 27.8% 39.8% 24.8% 20.8% 16.7% 15.0%
Efficiency
Days Receivables Days 49 42 33 35 35 35 35 35
Days Payables Days 42 52 50 15 15 15 15 15
Cash Conversion Days 91 95 82 50 50 50 50 50
Tax rate % 25.0% 25.0% 25.0% 25.0% 25.0% 25.0% 25.0% 25.0%
Net Working Capital USD 820,000 770,000 470,000 765,753 839,089 875,941 919,738 965,725
Fixed Assets USD 1,900,000 2,000,000 2,400,000 2,660,000 2,770,000 2,740,000 2,630,000 2,630,000
Capital Employed USD 2,720,000 2,770,000 2,870,000 3,425,753 3,609,089 3,615,941 3,549,738 3,595,725
Chart Data
All amounts in USD
6 Chart Data Unit 2021 2022 2023 2024 2025 2026 2027 2028
Year -Year 2 -Year 1 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Balance Sheet
Net Working Capital 1 USD 765,753 839,089 875,941 919,738 965,725
Fixed Assets 1 USD 2,660,000 2,770,000 2,740,000 2,630,000 2,630,000
Cash 0 USD #N/A #N/A #N/A #N/A #N/A
Free Cash Flows Year 1 Year 2 Year 3 Year 4 Year 5 Terminal Value
Free Cash Flow to Firm 1 USD 201,747 461,852 665,378 714,899 629,578 8,691,146
Terminal Value (TV) 1 USD
Discount Factor x 0.95 0.87 0.79 0.72 0.65 0.65
Discounted Cash Flows 1 USD 192,423 400,736 525,203 513,342 411,259 5,677,314
Powered by
www.eFinancialModels.com
DCF Valuation
All amounts in USD
2 DCF Valuation Unit 2024 2025 2026 2027 2028
Year Year 1 Year 2 Year 3 Year 4 Year 5
Free Cash Flow to Firm USD 201,747 461,852 665,378 714,899 629,578
Terminal Value (TV) USD 8,691,146
Free Cash Flow to Firm incl. TV USD 201,747 461,852 665,378 714,899 9,320,724
Discounting Period Years 0.50 1.50 2.50 3.50 4.50
Powered by
www.eFinancialModels.com
TV Terminal Value
FCF Free Cash Flows
g Long-term Growth Rate
WACC Weighted Average Cost of Capital (Discount Rate)
Exit Valuation Method Exit Multiple Fundamentals (Year 5) Terminal Value Debt Cash TV - Equity Value
EV/EBITDA Exit Multiple x 6.0x EBITDA USD 1,200,753 7,204,520 (500,000) 1,679,703 8,384,223 ###
=> Compare Terminal Value Models
10,000,000
Terminal Value Models TV Factor (Y5) PV of TV 9,000,000 8,691,146
TV Year 5
Year 5 Year 0 8,000,000 7,204,520
USD x USD 7,000,000
PV of TV Year 0
6,000,000
Gordon Growth USD 8,691,146 0.65 5,677,314 5,000,000
Exit Multiple EV/EBITDA Exit Multiple (6.0x) USD 7,204,520 0.65 4,706,206 4,000,000
3,000,000
2,000,000
Selected Terminal Value Model
1,000,000
Gordon Growth USD 8,691,146 0.65 5,677,314 -
Gordon Growth Exit Multiple EV/EBITDA Exit Multiple
(6.0x)
Powered by
www.eFinancialModels.com