/wp-content/uploads/2022/10/Group-780.svg 0 0 Alan Hart, Centage Guest Blog Author, Principal Consultant at Pacific Shine Group /wp-content/uploads/2022/10/Group-780.svg Alan Hart, Centage Guest Blog Author, Principal Consultant at Pacific Shine Group2015-07-27 18:01:022022-11-01 19:39:33Forecast and Monitor your Loan Covenants Compliance
How loan covenant compliance can be forecasted with Budget Maestro and Displayed by Analytics Maestro There has been a recent series of blog posts on this site of why companies must regularly forecast their balance sheets Why you Must Forecast your Balance Sheet Part 2. There is also an older blog post on one of the benefits of doing that: Knowing whether or not the company will be able to meet its loan covenants imposed by their lender Will you breach your loan covenants?. In this post I would like to present a simple example demonstrating how Budget Maestro with Analytics can monitor both actual and budgeted loan covenants using Analytics Maestro with actual data collected by Budget Maestro from the ERP or accounting software and budget data provided by Budget Maestro from the budget plan(s). The easiest way to explain this is by looking at a very common loan covenant that the majority of lenders use with lines of credit and other types of asset based lending. The typical language found in the loan agreement reads as follows: “Borrower to maintain a Debt Service Coverage ratio of no less than 1.35 to 1 (this ratio can vary), evaluated quarterly. It is defined as the ratio of Cash Flow to Debt Service, where Cash Flow is defined as: The sum of net profit, income tax expense, depreciation, depletion, amortization and interest expense minus distributions, withdrawals and dividends. Debt Service is defined as: The current portion of long term debt plus interest expense.” Using the above definition as expressed in the loan agreement we construct the following table using actual results from a fiscal year-end quarter ending on 3/31/2015 plus four budgeted quarters of the 2016 fiscal year: The actual and four quarterly forecasted sets of numbers are derived from the Budget Maestro plan and presented by Analytics Maestro. Notice that the Current Portion of Long-Term Debt is derived directly from the forecasted Balance Sheet. Depreciation, Amortization and Interest are derived from the forecasted Income Statement but are dependent on activities occurring within the forecasted Balance Sheet that depend on asset acquisitions, sales and disposals, and changes in borrowings that affect the Interest Expense. These are part of the budget model and are entered through the Capital Assets and Financing modules in Budget Maestro. As you can see, Budget Maestro handles all this automatically and all forecasted financial statements are seamlessly interlinked, just like in an actual accounting system. Using Analytics Maestro, both actual and forecasted numbers can be represented in a template, similar to our example template, formatted any way you like, with charts, graphs and other custom formatting. Then, when an accounting period is closed, your actual data will display within this Budget Analytics template, along with all forecasted data. The table shown above is an example of data available in Budget Maestro and used by Analytics Maestro to display the actual and forecasted Debt Service Coverage ratio defining this loan covenant. In our example, you can clearly detect a deterioration of the Debt Service Coverage ratio. In the last forecasted fiscal quarter, ending on 3/31/2016 the ratio drops to 1.53, not much higher than the minimum required 1.35 ratio. This is a concern since additional deterioration of this ratio can cause the company to breech its loan covenants and may result in the lender calling the loan or in other adverse consequences to the company. However, with Analytics Maestro you see this well in advance. Note that in this example you’ll be able to display this ratio monthly if that’s how you set up your plan in Budget Maestro (although in this example the lender only requires a quarter-end analysis of this ratio). Now you can revisit your plan objectives, goals, assumptions and drivers, as well as all data supplied by business unit managers and other data used in building the budget. You can contemplate changes, rethink some of the initiatives and implement changes in the business that will result in maintaining a healthy ratio. For example: Maybe declaring a cash dividend for the next fiscal year (2016) is not such a good idea. Removing the dividend will improve the ratio and create a higher safety margin. This approach is true for any changes that must be made in response to forecasted results of not just the P&L but the entire chart of accounts. As we have seen in many of the posts in this blog, real visibility into the future financial health of the company is greatly dependent on the ability to budget all balance sheet accounts and have a way (e.g., via Analytics Maestro) to properly display this forecasted data. Another idea would be to use the what-if analysis feature available in Budget Maestro. You can also have multiple plans created as part of the budget model, and applying each version can further assist you in analyzing how this loan covenant ratio behaves with each version of the plan. The benefits gained through using Budget Maestro’s integrated financial statements, including a complete and accurate forecasted balance sheet cannot be over-emphasized. Seeing and understanding your company’s future financial health in Analytics Maestro should be one of the most compelling reasons for implementing this software solution. The example in this blog post illustrates only one of the many uses of this software and how important it is to have the right tools at your disposal.