Scribd
Upload
25 views25 pages

Free Cash Flow and Valuation Analysis

The document outlines a financial analysis including Free Cash Flow (FCF) calculations, Terminal Value (TV) estimations, and detailed financial statements for a company over several years. Key assumptions include a tax rate of 30%, WACC of 10%, and a growth rate of 2.5%. The analysis also includes historical results and forecasts for revenue, expenses, and balance sheet items.

Uploaded by

danielshenkut21
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
25 views25 pages

Free Cash Flow and Valuation Analysis

The document outlines a financial analysis including Free Cash Flow (FCF) calculations, Terminal Value (TV) estimations, and detailed financial statements for a company over several years. Key assumptions include a tax rate of 30%, WACC of 10%, and a growth rate of 2.5%. The analysis also includes historical results and forecasts for revenue, expenses, and balance sheet items.

Uploaded by

danielshenkut21
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

Calculate the FCF

Assumptions
EBIT 150
Tax Rate % 30%
Depreciation 15
Amortization 20
CapEx 50
Non-cash Working Capital (inc) / dec (12)
Free Cash Flow
EBIT
Tax
D&A
CapEx
Non-cash Working Capital (inc) / dec
Free Cash Flow 0
Terminal Value (TV) Calculation
Assumptions
WACC 10%
Growth rate 2.50%
EV/EBITDA Multiple 7.00x

Period 0 1 2
EBITDA 100 125
FCF 50 60
0.909 0.826
Discount Factor 0.909 0.826
PV/EV
V) Calculation

3 4 5
135 140 142
65 70 72
0.751 0.683 0.621
0.751 0.683 0.621

Perpertuity Growth TV
EV/ EBITDA TV
Average
Discounted Cash Flow Full

Assumptions pt1
Growth rate 1.7%
EV/EBITDA Multiple 7.00x
Cost of Debt 5%
Tax Rate 30%
10y Treasury 1.5%

Period 0 1

Free Cash Flows


EBIT 5,000
Tax 1,500
D&A 325
CapEx (1,550)
Non-cash Work. Capital (inc) / dec (180)
FCF

WACC
Cost of Equity
D/D+E
E/D+E
WACC 0.00%

Terminal Value
EBITDA
Exit Multiple (EV/EBITDA)
Perpetuity Growth
Average

Discounting
Discount Factor 1.00
PV of FCF 0
PV of TV
Enterprise Value 0

Enterprise Value to Equity Value


Cash 500
Marketable Securities 4,500
Short term Debt 3,650
Long term Debt 16,540
Equity Value

Shares Outstanding 1,000


Implied Share Price 0.00
ted Cash Flow Full

Assumptions pt2
Beta 1.3
Market Return 10%
Equity value 17,500
Debt value 15,000

2 3 4 5

5,200 5,400 5,500 5,500


1,560 1,620 1,650 1,650
330 330 320 320
(1,550) (1,500) (1,500) (1,500)
(170) (160) (150) (145)

5,820

0
0

1.00 1.00 1.00 1.00


0 0 0 0
0
Historical Results Forecast Period
FINANCIAL STATEMENTS 2019 2020 2021 2022 2023 2024 2025 2026 2027
Balance Sheet Check OK OK OK OK OK OK OK OK OK

Assumptions
LIVE SCENARIO
Revenue Growth (% Change) 0.0% 15.8% 11.2% 8.4% 5.9% 10.0% 10.0% 10.0% 10.0%
Cost of Goods Sold (% of Revenue) 38.3% 40.7% 37.4% 37.0% 37.6% 42.0% 47.0% 50.0% 36.0%
Salaries and Benefits (% of Revenue) 25.9% 19.2% 18.2% 16.2% 16.7% 17.0% 17.0% 17.0% 17.0%
Rent and Overhead ($000's) 10,963 10,125 10,087 11,020 11,412 15,000 15,000 15,000 15,000
Depreciation & Amortization (% of PP&E) 42.9% 42.9% 42.9% 42.9% 42.9% 35.0% 35.0% 35.0% 35.0%
Interest (% of Debt) 5.0% 5.0% 5.0% 5.0% 5.0% 10.0% 10.0% 10.0% 10.0%
Tax Rate (% of Earnings Before Tax) 31.2% 29.2% 28.7% 29.0% 29.1% 28.0% 28.0% 28.0% 28.0%
Accounts Receivable (Days) 18 18 18 18 18 18 18 18 18
Inventory (Days) 73 73 73 73 73 80 90 100 100
Accounts Payable (Days) 37 37 37 37 37 37 37 37 37
Capital Expenditures ($000's) 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000
Debt Issuance (Repayment) ($000's) - - (20,000) - - - - (20,000) -
Equity Issued (Repaid) ($000's) 70,000 - - - - - - - -

Income Statement
Reveneue 102,007 118,086 131,345 142,341 150,772 165,849 182,434 200,678 220,745
Cost of Goods Sold (COGS) 39,023 48,004 49,123 52,654 56,710 69,657 85,744 100,339 79,468
Gross Profit 62,984 70,082 82,222 89,687 94,062 96,193 96,690 100,339 141,277
Expenses
Salaries and Benefits 26,427 22,658 23,872 23,002 25,245 28,194 31,014 34,115 37,527
Rent and Overhead 10,963 10,125 10,087 11,020 11,412 15,000 15,000 15,000 15,000
Depreciation & Amortization 19,500 18,150 17,205 16,544 16,080 13,132 13,786 14,211 14,487
Interest 2,500 2,500 1,500 1,500 1,500 3,000 3,000 1,000 1,000
Total Expenses 59,390 53,433 52,664 52,066 54,237 59,327 62,800 64,326 68,014
Earnings Before Tax 3,594 16,649 29,558 37,622 39,825 36,866 33,890 36,013 73,263

Taxes 1,120 4,858 8,483 10,908 11,598 10,322 9,489 10,084 20,514
Net Earnings 2,474 11,791 21,075 26,713 28,227 26,543 24,401 25,929 52,749

Balance Sheet
Assets
Cash 67,971 81,210 83,715 111,069 139,550 161,050 179,175 178,547 233,396
Accounts Receivable 5,100 5,904 6,567 7,117 7,539 8,179 8,997 9,896 10,886
Inventory 7,805 9,601 9,825 10,531 11,342 15,267 21,142 27,490 21,772
Property & Equipment 45,500 42,350 40,145 38,602 37,521 39,389 40,603 41,392 41,905
Total Assets 126,376 139,065 140,252 167,319 195,951 223,885 249,916 257,325 307,959

Liabilities
Accounts Payable 3,902 4,800 4,912 5,265 5,671 7,061 8,692 10,171 8,056
Debt 50,000 50,000 30,000 30,000 30,000 30,000 30,000 10,000 10,000
Total Liabilities 53,902 54,800 34,912 35,265 35,671 37,061 38,692 20,171 18,056
Shareholder's Equity
Equity Capital 70,000 70,000 70,000 70,000 70,000 70,000 70,000 70,000 70,000
Retained Earnings 2,474 14,265 35,340 62,053 90,280 116,824 141,225 167,154 219,903
Shareholder's Equity 72,474 84,265 105,340 132,053 160,280 186,824 211,225 237,154 289,903
Total Liabilities & Shareholder's Equity 126,376 139,065 140,252 167,319 195,951 223,885 249,916 257,325 307,959

Check 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000

Cash Flow Statement


Operating Cash Flow
Net Earnings 2,474 11,791 21,075 26,713 28,227 26,543 24,401 25,929 52,749
Plus: Depreciation & Amortization 19,500 18,150 17,205 16,544 16,080 13,132 13,786 14,211 14,487
Less: Changes in Working Capital 9,003 1,702 775 903 827 3,175 5,062 5,768 (2,613)
Cash from Operations 12,971 28,239 37,505 42,354 43,480 36,500 33,125 34,372 69,849

Investing Cash Flow


Investments in Property & Equipment 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000
Cash from Investing 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000

Financing Cash Flow


Issuance (repayment) of debt - - (20,000) - - - - (20,000) -
Issuance (repayment) of equity 70,000 - - - - - - - -
Cash from Financing 70,000 - (20,000) - - - - (20,000) -

Net Increase (decrease) in Cash 67,971 13,239 2,505 27,354 28,480 21,500 18,125 (628) 54,849
Opening Cash Balance - 67,971 81,210 83,715 111,069 139,550 161,050 179,175 178,547
Closing Cash Balance 67,971 81,210 83,715 111,069 139,550 161,050 179,175 178,547 233,396
Supporting Schedules
Working Capital Schedule
Accounts Receivable 5,100 5,904 6,567 7,117 7,539 8,179 8,997 9,896 10,886
Inventory 7,805 9,601 9,825 10,531 11,342 15,267 21,142 27,490 21,772
Accounts Payable 3,902 4,800 4,912 5,265 5,671 7,061 8,692 10,171 8,056
Net Working Capital (NWC) 9,003 10,705 11,480 12,382 13,210 16,385 21,447 27,215 24,603
Change in NWC 9,003 1,702 775 903 827 3,175 5,062 5,768 (2,613)

Depreciation Schedule
PPE Opening 50,000 45,500 42,350 40,145 38,602 37,521 39,389 40,603 41,392
Plus Capex 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000
Less Depreciation 19,500 18,150 17,205 16,544 16,080 13,132 13,786.04 14,210.93 14,487.10
PPE Closing 45,500 42,350 40,145 38,602 37,521 39,389 40,603 41,392 41,905

Debt & Interest Schedule


Debt Opening 50,000 50,000 50,000 30,000 30,000 30,000 30,000 30,000 10,000
Issuance (repayment) - - (20,000) - - - - (20,000) -
Debt Closing 50,000 50,000 30,000 30,000 30,000 30,000 30,000 10,000 10,000
Interest Expense 2,500 2,500 1,500 1,500 1,500 3,000 3,000 1,000 1,000

DCF Model
Assumptions
Tax Rate 25%
Discount Rate 12%
Perpetual Growth Rate 4%
EV/EBITDA Mulltiple 8.0x
Transaction Date 3/31/2024
Fiscal Year End 12/31/2024
Current Price 16.00
Shares Outstanding 20,000

Discounted Cash Flow Entry 2024 2025 2026 2027 2028 Exit Terminal Value EV
Date 3/31/2024 12/31/2024 12/31/2025 12/31/2026 12/31/2027 12/31/2028 12/31/2028 EV/EBITDA 812,428.08
Time Period - 1 2 3 4 Perpetuty Growth 816,278.44 784,883.11
Year Fraction 1 1 1 1 1 1 Average 814,353.26
EBIT 39,866 36,890 37,013 74,263 86,887
Less Cash Tax 9,966 9,223 9,253 18,566 21,722
NOPAT 29,899 27,668 27,759 55,697 65,165
Depn&Amort 13,132 13,786 14,211 14,487 14,667
EBITDA 52,998 50,676 51,224 88,750 101,554
Less CapEx 15,000 15,000 15,000 15,000 15,000
Less Inc NWC 3,175 5,062 5,768 (2,613) 2,041
Unlevered FCFF 24,856 21,391 21,203 57,797 62,791 814,353

Transaction FCFF 0 18,642 21,391 21,203 57,797 62,791 814,353

IRR FCFF

Intrinsic Value
Enterprise Value 599,605
Plus Cash 139,550
Less Debt 30,000
Equity Value 709,154

Equity Value Per Share 35.46

Charts and Graphs


This chart isn't available in your version of Excel.

Editing this shape or saving this workbook into a different file format will permanently break
the chart.
2028
OK

10.0%
35.0%
17.0%
15,000
35.0%
10.0%
28.0%
18
100
37
15,000
-
-

242,820
84,987
157,833

41,279
15,000
14,667
1,000
71,946
85,887

24,048
61,839
292,860
11,975
23,284
42,238
370,357

8,615
10,000
18,615

70,000
281,742
351,742
370,357

0.0000

61,839
14,667
2,041
74,464

15,000
15,000

-
-
-

59,464
233,396
292,860
11,975
23,284
8,615
26,644
2,041

41,905
15,000
14,666.62
42,238

10,000
-
10,000
1,000
812,428

t will permanently break


Historical Results
FINANCIAL STATEMENTS 2019 2020 2021 2022
Balance Sheet Check OK OK OK OK

Assumptions
LIVE SCENARIO
Revenue Growth (% Change) 0.0% 15.8% 11.2% 8.4%
Cost of Goods Sold (% of Revenue) 38.3% 40.7% 37.4% 37.0%
Salaries and Benefits (% of Revenue) 25.9% 19.2% 18.2% 16.2%
Rent and Overhead ($000's) 10,963 10,125 10,087 11,020
Depreciation & Amortization (% of PP&E) 42.9% 42.9% 42.9% 42.9%
Interest (% of Debt) 5.0% 5.0% 5.0% 5.0%
Tax Rate (% of Earnings Before Tax) 31.2% 29.2% 28.7% 29.0%
Accounts Receivable (Days) 18 18 18 18
Inventory (Days) 73 73 73 73
Accounts Payable (Days) 37 37 37 37
Capital Expenditures ($000's) 15,000 15,000 15,000 15,000
Debt Issuance (Repayment) ($000's) - - (20,000) -
Equity Issued (Repaid) ($000's) 70,000 - - -

Income Statement
Reveneue 102,007 118,086 131,345 142,341
Cost of Goods Sold (COGS) 39,023 48,004 49,123 52,654
Gross Profit 62,984 70,082 82,222 89,687
Expenses
Salaries and Benefits 26,427 22,658 23,872 23,002
Rent and Overhead 10,963 10,125 10,087 11,020
Depreciation & Amortization 19,500 18,150 17,205 16,544
Interest 2,500 2,500 1,500 1,500
Total Expenses 59,390 53,433 52,664 52,066
Earnings Before Tax 3,594 16,649 29,558 37,622

Taxes 1,120 4,858 8,483 10,908


Net Earnings 2,474 11,791 21,075 26,713

Balance Sheet
Assets
Cash 67,971 81,210 83,715 111,069
Accounts Receivable 5,100 5,904 6,567 7,117
Inventory 7,805 9,601 9,825 10,531
Property & Equipment 45,500 42,350 40,145 38,602
Total Assets 126,376 139,065 140,252 167,319

Liabilities
Accounts Payable 3,902 4,800 4,912 5,265
Debt 50,000 50,000 30,000 30,000
Total Liabilities 53,902 54,800 34,912 35,265
Shareholder's Equity
Equity Capital 70,000 70,000 70,000 70,000
Retained Earnings 2,474 14,265 35,340 62,053
Shareholder's Equity 72,474 84,265 105,340 132,053
Total Liabilities & Shareholder's Equity 126,376 139,065 140,252 167,319

Check 0.0000 0.0000 0.0000 0.0000

Cash Flow Statement


Operating Cash Flow
Net Earnings 2,474 11,791 21,075 26,713
Plus: Depreciation & Amortization 19,500 18,150 17,205 16,544
Less: Changes in Working Capital 9,003 1,702 775 903
Cash from Operations 12,971 28,239 37,505 42,354

Investing Cash Flow


Investments in Property & Equipment 15,000 15,000 15,000 15,000
Cash from Investing 15,000 15,000 15,000 15,000

Financing Cash Flow


Issuance (repayment) of debt - - (20,000) -
Issuance (repayment) of equity 70,000 - - -
Cash from Financing 70,000 - (20,000) -

Net Increase (decrease) in Cash 67,971 13,239 2,505 27,354


Opening Cash Balance - 67,971 81,210 83,715
Closing Cash Balance 67,971 81,210 83,715 111,069

Supporting Schedules
Working Capital Schedule
Accounts Receivable 5,100 5,904 6,567 7,117
Inventory 7,805 9,601 9,825 10,531
Accounts Payable 3,902 4,800 4,912 5,265
Net Working Capital (NWC) 9,003 10,705 11,480 12,382
Change in NWC 9,003 1,702 775 903

Depreciation Schedule
PPE Opening 50,000 45,500 42,350 40,145
Plus Capex 15,000 15,000 15,000 15,000
Less Depreciation 19,500 18,150 17,205 16,544
PPE Closing 45,500 42,350 40,145 38,602

Debt & Interest Schedule


Debt Opening 50,000 50,000 50,000 30,000
Issuance (repayment) - - (20,000) -
Debt Closing 50,000 50,000 30,000 30,000
Interest Expense 2,500 2,500 1,500 1,500

DCF Model
Assumptions
Tax Rate 25%
Discount Rate 12%
Perpetual Growth Rate 4%
EV/EBITDA Mulltiple 8.0x
Transaction Date 3/31/2024
Fiscal Year End 12/31/2024
Current Price 16.00
Shares Outstanding 20,000

Discounted Cash Flow Entry 2024 2025 2026 2027


Date 3/31/2024 12/31/2024 12/31/2025 12/31/2026 12/31/2027
Time Period - 1 2 3
Year Fraction 1 1 1 1

EBIT
Less Cash Tax
NOPAT - - - -
Depn&Amort
EBITDA - - - -
Less CapEx
Less Inc NWC
Unlevered FCFF - - - -
Transaction FCFF 0 0 0 0 0

IRR FCFF

Intrinsic Value
Enterprise Value 0
Plus Cash
Less Debt
Equity Value 0

Equity Value Per Share -

Charts and Graphs


Date 12/31/24 12/31/25 12/31/26 12/31/27
FCFF - - - -

This chart isn't available


FCFF
Editing this shape or sav
the chart.
Forecast Period
2023 2024 2025 2026 2027 2028
OK OK OK OK OK OK

5.9% 10.0% 10.0% 10.0% 10.0% 10.0%


37.6% 42.0% 47.0% 50.0% 36.0% 35.0%
16.7% 17.0% 17.0% 17.0% 17.0% 17.0%
11,412 15,000 15,000 15,000 15,000 15,000
42.9% 35.0% 35.0% 35.0% 35.0% 35.0%
5.0% 10.0% 10.0% 10.0% 10.0% 10.0%
29.1% 28.0% 28.0% 28.0% 28.0% 28.0%
18 18 18 18 18 18
73 80 90 100 100 100
37 37 37 37 37 37
15,000 15,000 15,000 15,000 15,000 15,000
- - - (20,000) - -
- - - - - -

150,772 165,849 182,434 200,678 220,745 242,820


56,710 69,657 85,744 100,339 79,468 84,987
94,062 96,193 96,690 100,339 141,277 157,833

25,245 28,194 31,014 34,115 37,527 41,279


11,412 15,000 15,000 15,000 15,000 15,000
16,080 13,132 13,786 14,211 14,487 14,667
1,500 3,000 3,000 1,000 1,000 1,000
54,237 59,327 62,800 64,326 68,014 71,946
39,825 36,866 33,890 36,013 73,263 85,887

11,598 10,322 9,489 10,084 20,514 24,048


28,227 26,543 24,401 25,929 52,749 61,839

139,550 161,050 179,175 178,547 233,396 292,860


7,539 8,179 8,997 9,896 10,886 11,975
11,342 15,267 21,142 27,490 21,772 23,284
37,521 39,389 40,603 41,392 41,905 42,238
195,951 223,885 249,916 257,325 307,959 370,357

5,671 7,061 8,692 10,171 8,056 8,615


30,000 30,000 30,000 10,000 10,000 10,000
35,671 37,061 38,692 20,171 18,056 18,615

70,000 70,000 70,000 70,000 70,000 70,000


90,280 116,824 141,225 167,154 219,903 281,742
160,280 186,824 211,225 237,154 289,903 351,742
195,951 223,885 249,916 257,325 307,959 370,357

0.0000 0.0000 0.0000 0.0000 0.0000 0.0000

28,227 26,543 24,401 25,929 52,749 61,839


16,080 13,132 13,786 14,211 14,487 14,667
827 3,175 5,062 5,768 (2,613) 2,041
43,480 36,500 33,125 34,372 69,849 74,464

15,000 15,000 15,000 15,000 15,000 15,000


15,000 15,000 15,000 15,000 15,000 15,000

- - - (20,000) - -
- - - - - -
- - - (20,000) - -

28,480 21,500 18,125 (628) 54,849 59,464


111,069 139,550 161,050 179,175 178,547 233,396
139,550 161,050 179,175 178,547 233,396 292,860

7,539 8,179 8,997 9,896 10,886 11,975


11,342 15,267 21,142 27,490 21,772 23,284
5,671 7,061 8,692 10,171 8,056 8,615
13,210 16,385 21,447 27,215 24,603 26,644
827 3,175 5,062 5,768 (2,613) 2,041

38,602 37,521 39,389 40,603 41,392 41,905


15,000 15,000 15,000 15,000 15,000 15,000
16,080 13,132 13,786.04 14,210.93 14,487.10 14,666.62
37,521 39,389 40,603 41,392 41,905 42,238

30,000 30,000 30,000 30,000 10,000 10,000


- - - (20,000) - -
30,000 30,000 30,000 10,000 10,000 10,000
1,500 3,000 3,000 1,000 1,000 1,000

2028 Exit Terminal Value EV


12/31/2028 12/31/2028 EV/EBITDA -
4 Perpetuty Growth - H133*(1+C113)/(C112-C113)
1 1 Average -

- -
0 0

12/31/28 Market Value vs Intrinsic Value


- Market Value $0.00
Upside $0.00
Intrinsic Value $0.00

This chart isn't available in your version of Excel.

Editing this shape or saving this workbook into a different file format will permanently break
the chart.
C113)/(C112-C113)
Discounting Cash Flows

Assumption
WACC 8%

Period 0 1 2 3
FCF 100 100 100
Terminal Value
Discount factor 0.93 0.87 0.80

PV of FCF 93.02 86.53 80.50


PV of TV

Enterprise Value 961.8


EVE=
4 5
100 100
800
0.75 0.70

74.88 69.66
557.2

You might also like