1. Complete the cash flow model based on the assumed outlined below - Company A sells midgets, 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 during the projection period - In 2017 , Company A's gross margin was 45%, and its variable COGS represented 75% of its total COGS - Fired 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 Caper 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 vear-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 \begin{tabular}{|c|c|c|c|c|c|} \hline Actual & & & Projections \\ \hline 2017 & 2018 & 2019 & 2020 & 2021 & 2022 \\ \hline \end{tabular} Operating Metrics \& Credit Statistics Widgets sold Widget unit growth S per Widget Gross Margin 0.25 Fixed COGS Fixed COGS growth 0.75 Variable COGS Variable COGS per unit Variable COGS per unit growth EBIID A Margin SG\&AA growth Total Leverage Net Leverage EBIDA / Cash Interest Debt Raised / Drawn Cash Flow from Financing Beginning Cash Net Cash Flow Ending Cash Depreciation Tables 12 Depreciation on 2017 Asse 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(t) PIK Accrual. Ending Balance: Revolver Beginning Balance (+) Draw (-) Paydown Ending Balance B Revolver Beginning Balance (t) Draw (-) Paydown Ending Balance Capacity Beginning Availability Interest Calculations 3.0% Revolver 9.0\% PIK Loan 1 36 Total Interest PIK Loan IRR Analysis 1. Complete the cash flow model based on the assumed outlined below - Company A sells midgets, 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 during the projection period - In 2017 , Company A's gross margin was 45%, and its variable COGS represented 75% of its total COGS - Fired 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 Caper 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 vear-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 \begin{tabular}{|c|c|c|c|c|c|} \hline Actual & & & Projections \\ \hline 2017 & 2018 & 2019 & 2020 & 2021 & 2022 \\ \hline \end{tabular} Operating Metrics \& Credit Statistics Widgets sold Widget unit growth S per Widget Gross Margin 0.25 Fixed COGS Fixed COGS growth 0.75 Variable COGS Variable COGS per unit Variable COGS per unit growth EBIID A Margin SG\&AA growth Total Leverage Net Leverage EBIDA / Cash Interest Debt Raised / Drawn Cash Flow from Financing Beginning Cash Net Cash Flow Ending Cash Depreciation Tables 12 Depreciation on 2017 Asse 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(t) PIK Accrual. Ending Balance: Revolver Beginning Balance (+) Draw (-) Paydown Ending Balance B Revolver Beginning Balance (t) Draw (-) Paydown Ending Balance Capacity Beginning Availability Interest Calculations 3.0% Revolver 9.0\% PIK Loan 1 36 Total Interest PIK Loan IRR Analysis