Directions 1. Complete the cash flow model based on the assumed outlined below - Company A sells widgets, and in 2017, they sold 100 widgets - Widget units are expected to grow at 5% per annum, but revenue per widget is expected to stay flat cluring the projection period - In 2017, Company A's gross margin was 45%, and its variable COGS represented 75% of its total COGS - Fixed COGS are growing at 4% per annum and Variable COGS per unit are growing at 8% per annum - SG\&A is expected to fall 2% per annum during the projection period - In 2017, Company A's EBITDA margin was 20\% - In 2017, Company A's Capex was $125, and the average useful life of assets acquired was 10 years - Both are expected to remain consistent for each year of the projection period - Assume all assets were acquired on the last day of the fiscal year - Depreciation on the Company's existing asset base at the end of FY2017 is expected to fall by $12 per annum consistently during the projection period - Company A has a Revolver with a capacity of $80 and a PIK Loan with a principal balance of $400 YE17 - Revolver interest rate of 3% per annum .- PIK Loan interest rate of 9% cash / 1% PIK per annum - Company A's tax rate is 21%, and it pays all of its taxes in cash at year-end - Company A's working capital changes are equal to 10% of it's change in EBITDA 2. Calculate the IRR to investors in the PIK Loan using XIRR function - PIK Loan principal of $400 was funded at par on 1/1/2017 - PIK Loan investors received penny warrants for 2% of the Company's equity, exercisable on maturity of the PIK Loan (12/31/2022) - Company A's peers each trade at 10.0x EV / LTM EBITDA Total Leverage Net Leverage EBITDA / Cash Interest Income Statement Revenue COGS Gross Profit SG\&A EBITDA D\&A Interest Expense EBT Taxes Net Income Cash Flow Net Income Non-Cash Adjustment 1 Non-Cash Adjustment 2 10.0% Changes in Working Capital Cash Flow From Operations Capex Cash Flow from Investment (125) Debt Paid Down Debt Raised / Drawn Cash Flow from Financing Beginning Cash Net Cash Flow Ending Cash Depreciation Tables 12 Depreciation on 2017 Asset Base 125 D\&A from 2017 Capex D\&A from 2018 Capex D\&A from 2019 Capex D\&A from 2020 Capex D\&A from 2021 Capex Total Depreciation Debt Tables PIK Loan Beginning Balance 0.01 (+) PIK Accrual Ending Balance Revolver Beginning Balance (+) Draw (-) Paydown Ending Balance Capacity Beginning Availability Interest Calculations 3.0% Revolver 9.0% PIK Loan 80 Total Interest Directions 1. Complete the cash flow model based on the assumed outlined below - Company A sells widgets, and in 2017, they sold 100 widgets - Widget units are expected to grow at 5% per annum, but revenue per widget is expected to stay flat cluring the projection period - In 2017, Company A's gross margin was 45%, and its variable COGS represented 75% of its total COGS - Fixed COGS are growing at 4% per annum and Variable COGS per unit are growing at 8% per annum - SG\&A is expected to fall 2% per annum during the projection period - In 2017, Company A's EBITDA margin was 20\% - In 2017, Company A's Capex was $125, and the average useful life of assets acquired was 10 years - Both are expected to remain consistent for each year of the projection period - Assume all assets were acquired on the last day of the fiscal year - Depreciation on the Company's existing asset base at the end of FY2017 is expected to fall by $12 per annum consistently during the projection period - Company A has a Revolver with a capacity of $80 and a PIK Loan with a principal balance of $400 YE17 - Revolver interest rate of 3% per annum .- PIK Loan interest rate of 9% cash / 1% PIK per annum - Company A's tax rate is 21%, and it pays all of its taxes in cash at year-end - Company A's working capital changes are equal to 10% of it's change in EBITDA 2. Calculate the IRR to investors in the PIK Loan using XIRR function - PIK Loan principal of $400 was funded at par on 1/1/2017 - PIK Loan investors received penny warrants for 2% of the Company's equity, exercisable on maturity of the PIK Loan (12/31/2022) - Company A's peers each trade at 10.0x EV / LTM EBITDA Total Leverage Net Leverage EBITDA / Cash Interest Income Statement Revenue COGS Gross Profit SG\&A EBITDA D\&A Interest Expense EBT Taxes Net Income Cash Flow Net Income Non-Cash Adjustment 1 Non-Cash Adjustment 2 10.0% Changes in Working Capital Cash Flow From Operations Capex Cash Flow from Investment (125) Debt Paid Down Debt Raised / Drawn Cash Flow from Financing Beginning Cash Net Cash Flow Ending Cash Depreciation Tables 12 Depreciation on 2017 Asset Base 125 D\&A from 2017 Capex D\&A from 2018 Capex D\&A from 2019 Capex D\&A from 2020 Capex D\&A from 2021 Capex Total Depreciation Debt Tables PIK Loan Beginning Balance 0.01 (+) PIK Accrual Ending Balance Revolver Beginning Balance (+) Draw (-) Paydown Ending Balance Capacity Beginning Availability Interest Calculations 3.0% Revolver 9.0% PIK Loan 80 Total Interest