Financial Modeling
FINANCIAL STATEMENTS
Balance Sheet Check
Historical Results
Forecast Period
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
OK
OK
OK
OK
OK
OK
OK
OK
OK
OK
Assumptions
Income statement
Revenue Growth (% Change)
Cost of Goods Sold (% of Revenue)
Salaries and Benefits (% of Revenue)
Rent and Overhead ($000's)
Depreciation & Amortization (% of PP&E)
Interest (% of Debt)
Tax Rate (% of Earnings Before Tax)
Balance Sheet
Accounts Receivable (Days)
Inventory (Days)
Accounts Payable (Days)
Capital Expenditures ($000's)
Debt Issuance (Repayment) ($000's)
Equity Issued (Repaid) ($000's)
38.3%
25.9%
10,963
39.0%
5.0%
31.2%
15.8%
40.7%
19.2%
10,125
39.9%
5.0%
29.2%
11.2%
37.4%
18.2%
10,087
40.6%
5.0%
28.7%
8.4%
37.0%
16.2%
11,020
41.2%
5.0%
29.0%
5.9%
37.6%
16.7%
11,412
41.7%
5.0%
29.1%
5.0%
37.0%
17.0%
10,000
40.0%
5.0%
28.0%
4.5%
37.0%
17.0%
10,000
40.0%
5.0%
28.0%
4.0%
36.0%
17.0%
10,000
40.0%
5.0%
28.0%
3.5%
36.0%
17.0%
10,000
40.0%
5.0%
28.0%
3.0%
35.0%
17.0%
10,000
40.0%
5.0%
28.0%
-,000
170,000
-,000
-
-,000
(20,000)
-
-,000
-
-,000
-
-,000
-
-,000
-
-,000
(20,000)
-
-,000
-
-,000
-
102,007
39,023
62,984
118,086
48,004
70,082
131,345
49,123
82,222
142,341
52,654
89,687
150,772
56,710
94,062
158,311
58,575
99,736
165,435
61,211
104,224
172,052
61,939
110,113
178,074
64,107
113,967
183,416
64,196
119,220
26,427
10,963
19,500
2,500
59,390
3,594
22,658
10,125
18,150
2,500
53,433
16,649
23,872
10,087
17,205
1,500
52,664
29,558
23,002
11,020
16,544
1,500
52,066
37,622
25,245
11,412
16,080
1,500
54,237
39,825
26,913
10,000
15,008
1,500
53,421
46,314
28,124
10,000
15,005
1,500
54,629
49,595
29,249
10,000
15,-,752
55,361
30,273
10,000
15,-,774
58,193
31,181
10,000
15,-,682
62,539
1,120
2,474
4,858
11,791
8,483
21,075
10,908
26,713
11,598
28,227
12,968
33,346
13,887
35,708
15,501
39,860
16,294
41,899
17,511
45,028
Income Statement
Reveneue
Cost of Goods Sold (COGS)
Gross Profit
Expenses
Salaries and Benefits
Rent and Overhead
Depreciation & Amortization
Interest
Total Expenses
Earnings Before Tax
Taxes
Net Earnings
FINANCIAL STATEMENTS
Balance Sheet Check
Historical Results
Forecast Period
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
OK
OK
OK
OK
OK
OK
OK
OK
OK
OK
167,971
5,100
7,805
45,500
226,376
181,210
5,904
9,601
42,350
239,065
183,715
6,567
9,825
40,145
240,252
211,069
7,117
10,531
38,602
267,319
239,550
7,539
11,342
37,521
295,951
272,530
7,807
11,715
37,513
329,564
307,632
8,158
12,242
37,508
365,540
327,097
8,485
12,388
37,505
385,474
368,487
8,782
12,821
37,503
427,592
413,243
9,045
12,839
37,502
472,629
3,902
50,000
53,902
4,800
50,000
54,800
4,912
30,000
34,912
5,265
30,000
35,265
5,671
30,000
35,671
5,938
30,000
35,938
6,205
30,000
36,205
6,279
10,000
16,279
6,498
10,000
16,498
6,507
10,000
16,507
170,000
2,474
172,474
226,376
170,000
14,265
184,265
239,065
170,000
35,340
205,340
240,252
170,000
62,053
232,053
267,319
170,000
90,280
260,280
295,951
170,000
123,627
293,627
329,564
170,000
159,335
329,335
365,540
170,000
199,195
369,195
385,474
170,000
241,094
411,094
427,592
170,000
286,122
456,122
472,629
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
Balance Sheet
Assets
Cash
Accounts Receivable
Inventory
Property & Equipment
Total Assets
Liabilities
Accounts Payable
Debt
Total Liabilities
Shareholder's Equity
Equity Capital
Retained Earnings
Shareholder's Equity
Total Liabilities & Shareholder's Equity
Check
FINANCIAL STATEMENTS
Historical Results
Forecast Period
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
OK
OK
OK
OK
OK
OK
OK
OK
OK
OK
Operating Cash Flow
Net Earnings
Plus: Depreciation & Amortization
Less: Changes in Working Capital
Cash from Operations
2,474
19,500
9,003
12,971
11,791
18,150
1,702
28,239
21,075
17,-,505
26,713
16,-,354
28,227
16,-,480
33,346
15,-,980
35,708
15,-,102
39,860
15,-,465
41,899
15,-,390
45,028
15,-,757
Investing Cash Flow
Investments in Property & Equipment
Cash from Investing
15,000
15,000
15,000
15,000
15,000
15,000
15,000
15,000
15,000
15,000
15,000
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
Issuance (repayment) of equity
Cash from Financing
170,000
170,000
-
(20,000)
(20,000)
-
-
-
-
(20,000)
(20,000)
-
-
Net Increase (decrease) in Cash
Opening Cash Balance
Closing Cash Balance
167,971
167,971
13,239
167,971
181,210
2,505
181,210
183,715
27,354
183,715
211,069
28,480
211,069
239,550
32,980
239,550
272,530
35,102
272,530
307,632
19,465
307,632
327,097
41,390
327,097
368,487
44,757
368,487
413,243
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000
Balance Sheet Check
Cash Flow Statement
Check
FINANCIAL STATEMENTS
Historical Results
Forecast Period
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
OK
OK
OK
OK
OK
OK
OK
OK
OK
OK
5,100
7,805
3,902
9,003
9,003
5,904
9,601
4,800
10,705
1,702
6,567
9,825
4,912
11,480
775
7,117
10,531
5,265
12,382
903
7,539
11,342
5,671
13,210
827
7,807
11,715
5,938
13,584
375
8,158
12,242
6,205
14,196
611
8,485
12,388
6,279
14,594
398
8,782
12,821
6,498
15,105
511
9,045
12,839
6,507
15,377
272
Depreciation Schedule
PPE Opening
Plus Capex
Less Depreciation
PPE Closing
50,000
15,000
19,500
45,500
45,500
15,000
18,150
42,350
42,350
15,000
17,205
40,145
40,145
15,000
16,544
38,602
38,602
15,000
16,080
37,521
37,521
15,000
15,008
37,513
37,513
15,000
15,005
37,508
37,508
15,000
15,003
37,505
37,505
15,000
15,002
37,503
37,503
15,000
15,001
37,502
Debt & Interest Schedule
Debt Opening
Issuance (repayment)
Debt Closing
Interest Expense
50,000
50,000
2,500
50,000
50,000
2,500
50,000
(20,000)
30,000
1,500
30,000
30,000
1,500
30,000
30,000
1,500
30,000
30,000
1,500
30,000
30,000
1,500
30,000
(20,000)
10,000
500
10,000
10,000
500
10,000
10,000
500
Balance Sheet Check
Supporting Schedules
Working Capital Schedule
Accounts Receivable
Inventory
Accounts Payable
Net Working Capital (NWC)
Change in NWC
Historical Results
FINANCIAL STATEMENTS
Balance Sheet Check
Forecast Period
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
OK
OK
OK
OK
OK
OK
OK
OK
OK
OK
DCF Model
Assumptions
Tax Rate
Discount Rate
Perpetural Growth Rate
EV/EBITDA Mulltiple
Transaction Date
Fiscal Year End
Current Price
Shares Outstanding
Discounted Cash Flow
Date
Time Periods
Year Fraction
EBT
Interest
EBIT
Less: Cash Taxes
Plus: D&A
Less: Capex
Less: Changes in NWC
Unlevered FCF
(Entry)/Exit
Net FCF
Intrinsic Value
Enterprise Value
Plus: Cash
Less: Debt
Equity Value
Equity Value/Share
25%
12%
3%
7.0x
12/31/2017
6/30/-,000
Entry
12/31/2017
(290,450)
(290,450)
484,768
239,550
30,000
694,-
2018
6/30/-
2019
6/30/-
2020
6/30/-
2021
6/30/-
2022
6/30/-
46,314
1,500
47,814
11,954
15,008
15,-,495
49,595
1,500
51,095
12,774
15,005
15,-,715
55,-,861
13,965
15,003
15,-,501
58,-,693
14,673
15,002
15,-,511
62,-,039
15,760
15,001
15,-,008
17,747
37,715
41,501
43,511
47,008
Market Value
Market Cap
Plus: Debt
Less: Cash
EV
Equity Value/Share
500,000
30,000
239,550
290,-
Exit
6/30/2022
Terminal Value
Perpetural Growth
EV/EBITDA
Average
534,293
534,293
Rate of Return
Target Price Upside
IRR
39%
26%
522,309
546,278
534,293
Historical Results
FINANCIAL STATEMENTS
Forecast Period
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
OK
OK
OK
OK
OK
OK
OK
OK
OK
OK
Balance Sheet Check
Charts and Graphs
2018
17,747
Free Cash Flow
2019
37,715
2020
41,501
2021
43,511
2022
47,008
Free Cash Flow
$47,008
$50,000
$45,000
$41,501
$43,511
$37,715
$40,000
$35,000
$30,000
$25,000
$20,000
$17,747
$15,000
$10,000
$5,000
$0
1
2
3
4
5
Market Value vs Intrinsic Value
Market Value
Upside
Intrinsic Value
-