Debt Modeling

$000's 2015A 2016A 2017A

Debt Schedules
Base Rates
Prime 3.75%
LIBOR (90 days) 0.30%
Year 2015 4.00%
Year 2016 3.10%
Year 2017 5.70%
Year 2018 5.05%

Debt

Annual Interest Rate Expense (43,148) (20,250) (19,975)

Principal Repayment (63,153) (5,000) (5,000)
Total Annual Payments (106,301) (25,250) (24,975)

Opening Balance 289,053 525,900 520,900

Principal Addition 300,000 100,000
Principal Repayment (63,153) (5,000) (5,000)
Closing Balance 525,900 520,900 615,900

Term Loan A - Equal Payments

Reference Base Rate Year 2017
Base Rate 5.70%
Spread 0.75%
All-In Rate 6.45%
Term 7
Loan Amount 100000
Year of debt 2017

Annual Interest Rate Expense

Principal Repayment
Total Annual Payments

Opening Balance
Principal Repayment
Closing Balance 100,000
Other Debt - Equal Amortization
Reference Base Rate Year 2015
Base Rate 4.00%
Spread 1.50%
All-In Rate 5.50%
Term 10
Loan Amount 50000
Year of debt 2015

Annual Interest Rate Expense (2,750) (2,475)

Principal Repayment (5,000) (5,000)
Total Annual Payments (7750) (7475)

Opening Balance 50,000 45,000

Principal Repayment (5000) (5000)
Closing Balance 50,000 45,000 40,000

Other Debt (Bond)

Reference Base Rate Year 2015
Base Rate 4.00%
Spread 3.00%
All-In Rate 7.00%
Term 5
Loan Amount 250000
Year of debt 2015

Annual Interest Rate Expense (17,500) (17,500)

Principal Repayment - -
Total Annual Payments (17500) (17500)

Opening Balance 250,000 250,000

Principal Repayment 0 0
Closing Balance 250,000 250,000 250,000

Operating Line of Credit

Borrowing Base
Accounts Receivable 75%
Inventory 50%
Total Borrowing Base

Reference Base Rate LIBOR (90 days)

Base Rate 0.30%
Spread 0.75%
All-In Rate 1.05%
Receivable Days
Inventory Days
Payable Days
Working Capital Funding Gap Days
WC Gap x Cost of Sales / 365

Funding Gap / (Cash on Hand)

Total Operating Line of Credit Utilization

% of Borrowing Base Utilized

Interest Expense
 2018A 2019A 2020A 2021A 2022A

(26,150) (25,117) (24,035) (5,401) (4,212)

(16,751) (17,509) (268,316) (19,175) (20,089)
(42,901) (42,626) (292,351) (24,576) (24,301)

615,900 599,149 581,640 313,324 294,149

(16,751) (17,509) (268,316) (19,175) (20,089)

599,149 581,640 313,324 294,149 274,060

(6,450) (5,692) (4,885) (4,026) (3,112)

(11,751) (12,509) (13,316) (14,175) (15,089)
(18,201) (18,201) (18,201) (18,201) (18,201)

100,000 88,249 75,740 62,424 48,249

(11,751) (12,509) (13,316) (14,175) (15,089)
88,249 75,740 62,424 48,249 33,160
 (2,200) (1,925) (1,650) (1,375) (1,100) (825) (550)
(5,000) (5,000) (5,000) (5,000) (5,000) (5,000) (5,000)
(7200) (6925) (6650) (6375) (6100) (5825) (5550)

40,000 35,000 30,000 25,000 20,000 15,000 10,000

(5000) (5000) (5000) (5000) (5000) (5000) (5000)
35,000 30,000 25,000 20,000 15,000 10,000 5,000

(17,500) (17,500) (17,500)

- - (250,000)
(17500) (17500) (267500)

250,000 250,000 250,000

0 0 (250000)
250,000 250,000 0
 (275)
(5,000)
(5275)

5,000
(5000)
0
Debt Modeling
$000's 2015A 2016A

Income Statement
Revenue 547,817 659,373
Cost of Goods Sold 303,153 390,112
Gross Profit 244,664 269,261
Operating Expenses:
General and Administration 52,344 43,737
Stock-based Compensation 2,132 2,145
Advertising and Promotion 3,176 3,375
Insurance 924 1,378
Total Operating Expenses 58,575 50,634
EBITDA 186,089 218,627
Depreciation 62,866 82,078
Net Operating Profit (EBIT) 123,222 136,549
Interest Expenses (43,148) (20,250)
Income Before Taxes (EBT) 80,075 116,299
Total Taxes 26,567 29,118
Net Income 53,508 87,181

Retained Earnings
Start of Year 29,684 67,302
Net Earnings 53,508 87,181
Dividends 15,890 57,241
End of Year 67,302 97,243

Balance Sheet
ASSETS
Current Assets:
Cash 572,521 553,869
Accounts Receivable 124,864 144,197
Inventory 15,861 17,256
Prepaid Expenses 2,505 12,016
Other Current Assets 924 1,207
Total Current Assets 716,674 728,544
Non-Current Assets:
Property, Plant & Equipment 581,449 785,643
Total Non-Current Assets 581,449 785,643
Total Assets 1,298,124 1,514,186

LIABILITIES AND SHAREHOLDERS' EQUITY

Current Liabilities:
Accounts Payable 68,425 88,250
Accrued Liabilities 8,366 12,445
Other Short-Term Liabilities 1,495 5,000
Total Current Liabilities 78,285 105,695
Non-Current Liabilities:
 Long-Term Debt 525,900 520,900
Total Non-Current Liabilities 525,900 520,900
Total Liabilities 604,185 626,595
Shareholders' Equity:
Common Stock 626,636 790,349
Retained Earnings 67,302 97,243
Total Shareholders' Equity 693,938 887,592
Total Liabilities & Shareholders' Equity 1,298,124 1,514,186

Check 0.000 0.000

Cash Flow Statement

Net Income 53,508 87,181
Depreciation 62,866 82,078
Adjusted for Changes in Working Capital:
Change in Accounts Receivable (54,548) (19,333)
Change in Inventory (1,288) (1,395)
Change in Accounts Payable 24,161 19,825
Other Non-Cash Items:
Change in Prepaid Expenses 4,914 (9,511)
Change in Other Current Assets (183) (283)
Change in Accrued Liabilities 6,273 4,079
Change in Other Short-Term Liabilities 870 3,505
Cash from Operating Activities 96,573 166,147

Capital Expenditures (Net) (303,999) (286,271)

Cash from Investing Activities (303,999) (286,271)

Change in Debt 236,847 (5,000)

Change in Common Stock 11,105 163,713
Payment of Dividends (15,890) (57,241)
Cash from Financing Activities 232,062 101,472

Net Change in Cash 24,635 (18,652)

Starting Cash Balance 547,885 572,521
Ending Cash Balance 572,521 553,869

Check 0.000 0.000

PP&E Schedule
Gross PP&E
Opening Balance 429,279 733,278
CapEx 303,999 286,271
Closing Balance 733,278 1,019,549

Accumulated Depreciation
Beginning of Year 88,963 151,829
Current Year's Expense 62,866 82,078
End of Year 151,829 233,906

Net PP&E 581,449 785,643

Debt Schedule

Annual Interest Rate Expense (43,148) (20,250)

Principal Repayment (63,153) (5,000)
Total Annual Payments (106,301) (25,250)

Opening Balance 225,900 525,900

Principal Addition 300,000 -
Principal Repayment (63,153) (5,000)
Closing Balance 525,900 520,900
 2017A 2018A 2019A 2020A 2021A 2022A

680,854 761,268 851,180 951,710 1,064,114 1,189,794

292,189 399,457 446,635 499,386 558,368 624,315
388,666 361,812 404,544 452,324 505,747 565,479

46,212 58,301 65,187 72,886 81,495 91,120

3,798 3,228 3,610 4,036 4,513 5,046
3,552 4,094 4,578 5,118 5,723 6,399
1,737 1,606 1,795 2,007 2,244 2,509
55,299 67,229 75,169 84,047 93,974 105,073
333,367 294,582 329,375 368,277 411,773 460,406
75,718 88,928 99,431 111,174 124,305 138,986
257,649 205,655 229,944 257,102 287,468 321,420
(19,975) (26,150) (25,117) (24,035) (5,401) (4,212)
237,674 179,505 204,827 233,067 282,067 317,208
34,246 48,510 54,574 61,354 68,934 77,410
203,428 130,994 150,253 171,713 213,132 239,798

97,243 237,345 301,069 375,642 462,275 579,814

203,428 130,994 150,253 171,713 213,132 239,798
63,326 67,271 75,679 85,081 95,593 107,347
237,345 301,069 375,642 462,275 579,814 712,265

728,998 643,809 569,373 246,913 199,960 173,561

179,566 180,257 201,547 225,351 251,966 281,725
17,729 20,935 23,408 26,173 29,264 32,720
13,624 10,862 12,145 13,579 15,183 16,977
1,644 2,194 2,928 3,906 5,213 6,955
941,561 858,058 809,400 515,922 501,586 511,938

847,212 1,058,759 1,295,291 1,559,760 1,855,464 2,186,094

847,212 1,058,759 1,295,291 1,559,760 1,855,464 2,186,094
1,788,772 1,916,816 2,104,691 2,075,682 2,357,050 2,698,032

135,012 115,977 129,675 144,990 162,115 181,261

13,957 13,866 15,504 17,335 19,383 21,672
3,283 6,568 13,141 26,293 52,609 105,262
152,251 136,411 158,320 188,619 234,106 308,195
 615,900 599,149 581,640 313,324 294,149 274,060
615,900 599,149 581,640 313,324 294,149 274,060
768,151 735,560 739,960 501,943 528,255 582,255

783,277 880,188 989,089 1,111,465 1,248,981 1,403,512

237,345 301,069 375,642 462,275 579,814 712,265
1,020,622 1,181,256 1,364,732 1,573,739 1,828,795 2,115,777
1,788,772 1,916,816 2,104,691 2,075,682 2,357,050 2,698,032

0.000 0.000 0.000 0.000 0.000 0.000

203,428 130,994 150,253 171,713 213,132 239,798

75,718 88,928 99,431 111,174 124,305 138,986

(35,369) (691) (21,290) (23,804) (26,616) (29,759)

(473) (3,206) (2,473) (2,765) (3,091) (3,456)
46,762 (19,035) 13,698 15,316 17,124 19,147

(1,609) 2,762 (1,283) (1,434) (1,604) (1,793)

(438) (550) (734) (979) (1,306) (1,743)
1,511 (90) 1,638 1,831 2,047 2,289
(1,717) 3,285 6,573 13,152 26,315 52,653
287,814 202,397 245,814 284,204 350,308 416,122

(137,287) (300,475) (335,963) (375,643) (420,009) (469,616)

(137,287) (300,475) (335,963) (375,643) (420,009) (469,616)

95,000 (16,751) (17,509) (268,316) (19,175) (20,089)

(7,072) 96,911 108,902 122,375 137,516 154,531
(63,326) (67,271) (75,679) (85,081) (95,593) (107,347)
24,602 12,889 15,713 (231,022) 22,748 27,095

175,129 (85,188) (74,436) (322,460) (46,953) (26,399)

553,869 728,998 643,809 569,373 246,913 199,960
728,998 643,809 569,373 246,913 199,960 173,561

0.000 0.000 0.000 0.000 0.000 0.000

1,019,549 1,156,836 1,457,311 1,793,274 2,168,917 2,588,926

137,287 300,475 335,963 375,643 420,009 469,616
1,156,836 1,457,311 1,793,274 2,168,917 2,588,926 3,058,542

233,906 309,624 398,552 497,983 609,157 733,462

75,718 88,928 99,431 111,174 124,305 138,986
309,624 398,552 497,983 609,157 733,462 872,448

847,212 1,058,759 1,295,291 1,559,760 1,855,464 2,186,094

(19,975) (26,150) (25,117) (24,035) (5,401) (4,212)

(5,000) (16,751) (17,509) (268,316) (19,175) (20,089)
(24,975) (42,901) (42,626) (292,351) (24,576) (24,301)

520,900 615,900 599,149 581,640 313,324 294,149

100,000 - - - - -
(5,000) (16,751) (17,509) (268,316) (19,175) (20,089)
615,900 599,149 581,640 313,324 294,149 274,060
Bond

Log on to Finra Bond market using your internet browser ( https://finra-markets.morningstar.com/BondCenter/Default.jsp) an

the following symbol:
AAPL4001810
Extract the bond value using the bond evaluation method and compare it with the listed bond price found on the site.

Value of a Bond (Semi Annual)

Face Value 100

Coupon Rate 3.85% Today Maturity
Time to maturity (years) 19.41643836 11/9/2023 4/5/2043
Discount Rate 5.08%

Value of Bond (84.91)

ar.com/BondCenter/Default.jsp) and search for the Apple bond with

ond price found on the site.

Days
7087
19.40555556
Bond

The Market Value of Debt refers to the market price investors would be willing to buy a company’s debt for, which differs from
on the balance sheet. A company’s debt doesn’t always come in the form of publicly traded bonds, which have a specified ma
Instead, many companies own debt that can be classified as non-traded, such as bank loans.

To estimate the Market Value of Debt, an analyst can think of the Total Debt on the books as a single coupon bond, with the c
to the interest expenses on all debt and the maturity as the weighted average maturity of the debt.

https://corporatefinanceinstitute.com/resources/fixed-income/market-value-of-debt/
mpany’s debt for, which differs from the book value
d bonds, which have a specified market value.
s.

as a single coupon bond, with the coupon being equal

he debt.