In this post, I am going to provide you with a simple covenant model assembled from a 3-statement model to evaluate a 5-year $10,000 bond issue with an annual interest rate of about 7.5% to fund additional PP&E. You can find the Excel file on the link below s you can follow the next explanation:
Firstly, you must perform a sanity check on the past data included in the three financial statements. In regard to the Income Statement, your revenue growth assumption should be conservative to ensure the firm will be able to cover its debt repayments with much more probability. In this way, revenue growth has been assumed as flat for 2023 and 2024 while achieving the same stable net profit margin level as in the past fiscal years (which by the way, it is a good signal). Then, you must assess the balance sheet capital structure, which is really strong and stable while retained earnings are growing as well. In general, you should also assess PP&E and working capital accounts because they might indicate some tendency or issue. In regard to the cash flow statement, the free cash flow and net change in cash are both positive. Moreover, the financing cash flow shows the company has been able to repay its debts during the past periods.
All in all, this first overview shows the company has been performing pretty well so you can continue by performing ratio analysis to further assess liquidity, leverage, and interest coverage among other aspects. You must interpret the ratios you have chosen and, in case you continue perceiving the company can stand more debt, you should follow with the model assumptions for the forecast periods while including the new debt issue on them. In this case, the forecast amounts have been provided directly for simplicity and the new debt assumption has been included after the ratio analysis in the "loan analysis" section.
Now let´s analyze how this loan will impact every statement. First, both the Long-Term Debt and Current Portion of Long-Term Debt on the Balance Sheet will increase by 8,000 and 2,000 respectively in 2020 while the remaining debt has been paid, as you can also check in the Change in Long Term Debt account in the cash flow statement. Due to this new debt has been agreed to finance PP&E, this balance sheet account will increase by 10,000 in the 2020 period as well. In case you do not know the use of cash coming from the loan, you might prefer to include it in the cash account till it is decided by the company. Lastly, in the Income Statement, you should calculate the interest expense based on the interest-bearing debt for the forecast periods (in this case the same 7,5% interest rate has been assumed for the line of credit).
Then, after forecasting all 3-statement data, you can finish the ratio analysis for these future periods as well. You should perform some stress test by changing the loan amount ($10,000) and the interest rate on the current new debt so that you ensure the firm continues operating without extreme leverage, enough cash flow generation and net profit every period, etc. Once you have reviewed the new debt conditions, you should include some covenants and ensure the company will meet them. In this case, you can see on the graphics that the firm might not meet some covenants during 2020 so either the loan amount or the current covenants might be changed to ensure the firm complies with all the covenants. In fact, this explains why you should perform conservative assumptions as a credit or fixed-income analyst so that the firm is much more likely to meet those covenants in future periods.
.jpg)
0 Comments