IS Excel Participant - Simplified v2 (3)

xlsx

School

Guilford Technical Community College *

*We aren’t endorsed by this school

Course

100

Subject

Finance

Date

Jan 9, 2024

Type

xlsx

Pages

9

Uploaded by UltraCrocodile1962

Report
Participant name: Last Name, First Name Davis Industries - Financial Summary FYE July 31, $ in millions 2015 2016 2017 Revenue $353.1 $415.8 17.7% -100.0% Cost of Goods Sold 188.0 215.2 0.0 Gross Profit 165.1 200.6 Gross margin 46.8% 48.3% #DIV/0! Total operating expenses 116.3 142.6 0.0 Operating income 48.8 58.0 Operating margin 13.8% 14.0% #DIV/0! Net income 27.8 34.9 Net profit margin 7.9% 8.4% #DIV/0! Cash and cash equivalents $19.8 $26.8 Accounts receivable 43.5 58.4 Inventory 45.1 57.3 Total assets 365.0 443.3 501.1 Accounts payable 10.1 16.4 Total debt 78.5 116.0 Equity 224.6 249.7 Liabilities and equity 365.0 443.3 501.1 Income Statement : % growth Balance Sheet :
FYE July 31, $ in millions 2015 2016 2017 Cash flow from operations $39.1 $49.0 Capital expenditures (7.9) (11.4) Free Cash Flow 31.1 37.5 0.0 Acquisitions (20.4) (65.0) Dividends paid (2.6) (3.1) Adjusted EBITDA $70.2 $86.3 $99.8 Total Debt / EBITDA 1.1x 1.3x 1.3x Debt to total capitalization 25.9% 31.7% 30.3% FCF / Total Debt 39.7% 32.4% 34.2% Cash Flow Statement : EBITDA calculation : Credit ratios :
Sources ($mm) Before Tran New Revolving Credit Facility 126.0 ($mm) Amount Total Sources $126.0 Tranche A 80.0 Tranche B 46.0 Uses ($mm) New Revolving Credit Facility - Repay Tranche A 80.0 Total Debt 126.0 Repay Tranche B 46.0 Total Uses $126.0 Adjusted EBITDA $99.8
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
nsaction After Transaction x EBITDA Amount 0.8x - 0.5x - - - 1.3x 0.0 - $99.8 EBITDA
Participant name: Upton, Grace Collateral description Eligible collateral value ($k) Authorized advance rate (%) Accounts receivable 69,078.00 80% Inventories 61,795.00 50% Property and equipment, net 55,211.00 50% Total collateral available Less: (commitments) Excess / (deficiency)
Available collateral ($k) 55,262.40 30,897.50 27,605.50 113,765.40 126,000.00 -12,234.60
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Audited Management Projections JPMorgan Extrapolated CAGR Base Case 2015A 2016A 2017A 2018P 2019P 2020P 2021P 2022P 2023P 2024P 2025P 2026P 2027P CAGR: 15-17 CAGR: 18-22 x Revenue 353.1 415.8 480.3 525.0 588.0 646.8 646.8 646.8 646.8 646.8 646.8 646.8 646.8 x % Growth 17.8% 15.5% 9.3% 12.0% 10.0% 0.0% 0.0% 0.0% 0.0% 0.24% 0.17% (-) Cost of Goods Sold 188.0 215.2 242.5 267.8 299.9 329.9 - - - - - - - % of Revenue 53.3% 51.8% 50.5% 51.0% 51.0% 51.0% 0.0% 0.0% 0.0% 0.0% 0.20% -100.00% x Gross Profit Margin 165.1 200.6 237.7 257.3 288.1 316.9 646.8 646.8 646.8 646.8 646.8 646.8 646.8 x GPM Margin % 46.8% 48.3% 49.5% 49.0% 49.0% 49.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% x (-) Operating Expenses 94.9 114.3 137.9 152.3 170.5 187.6 - - - - - - - x % of Revenue 26.9% 27.5% 28.7% 29.0% 29.0% 29.0% 0.0% 0.0% 0.0% 0.0% 0.29% -100.00% EBITDA 70.2 86.3 99.8 105.0 117.6 129.4 646.8 646.8 646.8 646.8 646.8 646.8 646.8 EBITDA Margin % 19.9% 20.8% 20.8% 20.0% 20.0% 20.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% Income Taxes 16.4 19.8 19.9 20.94 23.45 25.80 129.00 129.00 129.00 129.00 129.00 129.00 129.00 x % of EBITDA 23.4% 22.9% 19.9% 19.9% 19.9% 19.9% 19.9% 19.9% 19.9% 19.9% 19.9% 19.9% 19.9% x Cash Flow x Capital Expenditures 7.9 11.4 16.8 18.4 20.6 22.6 - - - 22.6 22.6 22.6 22.6 % of Revenue 2.3% 2.8% 3.5% 3.5% 3.5% 3.5% 3.5% 3.5% 3.5% 3.5% 0.59% -100.00% Dividends 2.65 3.12 3.60 4.2 4.7 5.2 - - - 5.2 5.2 5.2 5.2 % of Revenue 0.8% 0.8% 0.8% 0.8% 0.8% 0.8% 0.8% 0.8% 0.8% 0.8% 0.24% -100.00% x Balance Sheet x Accounts Receivable 43.5 58.4 69.1 75.5 84.6 93.0 - - - - - - - DSO 45.0 51.3 52.5 52.5 52.5 52.5 0.0% 0.0% 0.0% 0.0% 0.36% -100.00% Inventory 45.1 57.3 61.8 68.2 76.4 84.0 - - - - - - - DIO 87.5 97.3 93.0 93.0 93.0 93.0 0.0% 0.0% 0.0% 0.0% 0.25% -100.00% Accounts Payable 10.12 16.45 17.17 18.96 21.23 23.36 - - - - - - - DPO 19.6 27.9 25.8 25.8 25.8 25.8 0.0% 0.0% 0.0% 0.0% 0.42% -100.00% x Δ in NWC (14.43) (11.08) (14.97) (13.98) 153.73 - - - - - - x
CAGR: 23-27
Year Ended July 31, FCF Model ($mm) 2017PF 2018P 2019P 2020P 2021P 2022P 2023P 2024P 2025P 2026P 2027P Revenue 480.3 525.0 588.0 646.8 646.8 646.8 646.8 646.8 646.8 646.8 646.8 % Growth 15.5% 9.3% 12.0% 10.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% EBITDA 99.8 105.0 117.6 129.4 646.8 646.8 646.8 646.8 646.8 646.8 646.8 % Margin 20.8% 20.0% 20.0% 20.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% Interest Expense (5.4) (3.8) (2.0) (1.0) (1.0) (1.0) (1.0) (1.0) (1.0) (1.0) (1.0) Cash Tax Expense (20.9) (23.5) (25.8) (129.0) (129.0) (129.0) (129.0) (129.0) (129.0) (129.0) Δ in NWC (11.1) (15.0) (14.0) 153.7 - - - - - - Capex (18.4) (20.6) (22.6) - - - (22.6) (22.6) (22.6) (22.6) Dividend payments (4.2) (4.7) (5.2) - - - (5.2) (5.2) (5.2) (5.2) Free cash flow 46.6 51.9 60.8 670.5 516.8 516.8 489.0 489.0 489.0 489.0 Cumulative free cash flow 46.6 98.6 159.3 829.9 1,346.7 1,863.5 2,352.5 2,841.5 3,330.4 3,819.4 1867.0% 7-yr Payout Mandatory debt repayments - - - - - - - - - - Cash available for debt service 46.6 51.9 60.8 670.5 516.8 516.8 489.0 489.0 489.0 489.0 Cumulative free cash flow for debt service 46.6 98.6 159.3 829.9 1,346.7 1,863.5 2,352.5 2,841.5 3,330.4 3,819.4 Year Ended July 31, Covenant Projections ($mm) 2017PF 2018E 2019E 2020E 2021E 2022E 2023E 2024E 2025E 2026E 2027E R/C facility 126.0 79.4 27.4 - - - - - - - - Term Loan A - - - - - - - - - - - Total Debt 126.0 79.4 27.4 - - - - - - - - Leverage Ratio 1.26x 0.76x 0.23x 0.00x 0.00x 0.00x 0.00x 0.00x Leverage Covenant 3.50x 3.50x 3.50x 3.50x 3.50x 3.50x 3.50x 3.50x 3.50x 3.50x 3.50x EBITDA Cushion ($) 63.78 82.32 109.76 129.36 646.80 646.80 646.80 646.80 EBITDA Cushion (%) 63.92% 78.40% 93.33% 100.00% 100.00% 100.00% 100.00% 100.00% Interest Coverage Ratio 18.44x 27.79x 59.99x 129.36x 646.80x 646.80x 646.80x 646.80x Interest Coverage Covenant 3.50x 3.50x 3.50x 3.50x 3.50x 3.50x 3.50x 3.50x 3.50x 3.50x 3.50x EBITDA Cushion ($) 80.84 91.78 110.74 125.86 643.30 643.30 643.30 643.30 EBITDA Cushion (%) 81.02% 87.41% 94.17% 97.29% 99.46% 99.46% 99.46% 99.46%
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help