As we discussed in our last article, debt covenants are provisions within financial agreements such as loans or credit lines between lenders and borrowers that restrict certain activities like dividends and asset sales to protect lenders’ interests and maintain their risk exposure at acceptable levels.

If you are not familiar with debt covenants, I suggest you take a look at the article before you continue.

Build a Covenants Tracking Report

As you might know already, we recently launched our online FP&A platform in beta, and you can request a free account here or at the top of this page.

Let me show you how you can build a reusable debt covenants tracker in half an hour.

If you prefer to play around with the finished project, it is available as a public template on your Magnimetrics Dashboard!

After securing your free spot on our platform, you can go to the Project view and create a new project.

The next thing you’ll see is the Project Dashboard:

This gives you an overview of your (for now empty) project. You see three main sections here:

• Import Your Data
• Reports
• Other Actions

Let’s go ahead and build our project from the ground up, and remember, once you set everything up, it’s a breeze to update it with next period’s numbers!

Import Your Data

This is where we start.

Click on the New Import card and name your new template Balance Sheet. You can then see your empty import template:

We can build our template from the ground up by creating and adding line items to it. However, we can take a shortcut and directly import our data file, which will create the line items for us automatically.

Here’s the file in case you want to follow along:

Click on the Import File button and select the file you just downloaded. Keep in mind that if the file is open in Excel, the import process throws an error once in a while, saying ‘Not Implemented.’ Close the file, and it will import without any issues.

Once you select the file, you will see the import wizard. Make sure to go to the correct spreadsheet tab on the bottom (BS for Balance Sheet). Check that headers are enabled as our file has the period names in the first row and click Next.

The next screen gives you the option to use pre-defined line-item codes from your file. As our file has none of those, we can click Next and Proceed without line codes.

The third step of the import wizard is to select what you want to import. Our software will try to guess what you want and what shouldn’t be imported and pre-select for you. Usually, it would skip Empty rows (ones without data) and Formulas. You can use the switches to select what you want imported. For our example, leave the selection as-is and click Next.

To select the periods you want to import, click on the first one (CY 2018) and click again on the last one (CY 2020). This will highlight the three periods. You can also note that I left both the Short-term and Long-term Bank debt mapped to the BANK_DEBT Line Code. As I will only be using the debt as a total figure, I prefer it to be summed up as a single amount. Click Next.

The wizard will then show a preview of what you are about to import, and you can click the Import button to finish the import process.

Your data is now in, and the app has created the respective line items and periods. However, before we can continue, we need to ensure that we fill in the start and end date of the periods by clicking on the MISSING DATE links.

After you update the dates, your screen should look something like this.

Now go back to Data Imports, create a new template for the Income Statement and follow the same steps to import the data from the IS sheet in the file.

You will notice you don’t have to set the period dates again, as the Income Statement data is imported in the already existing periods.

Now that we have our data in the project let’s set a few Assumptions and Formulas.

Assumptions

We will be calculating the three debt covenants we discussed in our article on debt covenants – Debt to Equity Ratio, Interest Coverage Ratio, and Net Debt to EBITDA Ratio. We can set numeric and text assumptions on the Assumptions screen to reuse and benchmark against.

The beauty of it is that you can later copy the whole project and reuse everything we will build for another company by simply adjusting the covenant targets here.

Let’s set up three assumptions:

• ICR_TARGET = 2.00 (Interest Coverage Ratio)
• DE_TARGET = 1.00 (Debt/Equity Ratio)
• NDE_TARGET = 4.00 (Net Debt to EBITDA Ratio)

Formulas

Now let’s move to the Formulas section of our Project.

Here you can configure pre-defined calculations based on the imported data, the project assumptions, and other formulas.

This allows you to pre-set complex calculations once and not worry about them in the future.

We will add 3 formulas for our covenants and 2 helper formulas.

To be able to calculate the debt covenants we discussed, we would first need the following:

EBITDA

Looking at our Income Statement line items and the imported data, we can quickly grab the line codes we need to calculate EBITDA. However, I will use the abs() function to get the absolute value of each expense line item and then subtract it from Sales. That way, if I were to import a new data set in the future where expenses are shown as positive amounts, my formula would still work.

Taking this into account, we can add the following formula for EBITDA:

SALES - abs(COST_OF_SALES) - abs(GENERAL_AND_ADMIN_EXPENSES) - abs(SELLING_EXPENSES)

EBIT_ICR

This is our EBIT for the Interest Coverage Ratio. It differs from a standard EBIT calculation because it doesn’t consider Interest Expense. The formula for this would start from the EBITDA formula above and look like this:

EBITDA – abs(NON_OPERATING_EXPENSES)

We can then add the calculations for the 3 debt covenants we are looking to track. Following the same logic, we get the following by looking at the formulas we discussed above and using our imported data.

Debt/Equity Ratio

BANK_DEBT / (SHARE_CAPITAL + RETAINED_EARNINGS)

Interest Coverage Ratio

EBIT_ICR / abs(INTEREST_EXPENSE)

Net Debt to EBITDA Ratio

(BANK_DEBT - CASH_AND_CASH_EQUIVALENTS) / EBITDA

Reports

We now have all the needed items set up, and we can start building our Debt Covenants report. Go to the Reports screen and click the New Report button. Give it a name, fill out additional details if you want to, and click Create.

What you see next is an empty report waiting for you to fill out. We do that via the Add Report Item button.

Go ahead and add a Heading. We can reference assumptions here, so if we type in the following, we would have a dynamic title that would update every time we reuse the project.

{{COMPANY_NAME}}: Debt Covenants Calculation

Next, let’s add a Table to show our Debt/Equity Ratio.

Once you add the Table, start typing the lines, formulas, and assumptions you want to add.

I added the following items for the Debt/Equity Ratio and formatted them with the options on the right side.

Save the Table and run your report via the Launch Report button at the top right.

You can see the result below.

Looking at the heading, you can see {{COMPANY_NAME}} is replaced with the name I have set for my business (Templates Company). Additionally, the data we previously imported is used to calculate the Debt-to-Equity Ratio formula and then presented in the table.

Click Edit Report to go back, and let’s add some logic to check if our average D/E Ratio is above the target we have set.

Adding Headings (and Rich Text for that matter) is excellent, but let’s look at what we can do when we want more dynamic content for our Reports.

Click the Add Report button and select the Analysis Point. This opens up a screen where you can script your logic. On the right side of it, you’ll see a help section that outlines the functionality you can use in your scripts. As we won’t need this to be calculated for each period separately, go ahead and switch the Analysis Point to Aggregated. Otherwise, we will get the same calculation repeated over each period.

Copy the following script in the Analysis Point and read through its comments, which outline what the script does. We concatenate a mix of functions, data points, and text, using the ~ sign (the one below the Escape key on your keyboard). It looks long, but that’s because of all the comments.

"Over the period from " ~
// Get the name from the earliest period for which the DE_RATIO formula can be calculated
DE_RATIO.earliestPeriod().name ~
" to " ~
// Get the name from the latest Period for which the DE_RATIO formula can be calculated
DE_RATIO.latestPeriod().name ~
// We can reference assumptions within text strings the same way as in Headings and Rich Text
" {{COMPANY_NAME}} operated at an average Debt/Equity Ratio of " ~
// The numberFormat() function allows us to set the number of digits after the decimal sign.
// For the value we want to format, we use the .average() function applied to the DE_RATIO formula.
// This will take the average value of the DE_RATIO across all imported periods and round it
// to 2 digits after the decimal point.
numberFormat(DE_RATIO.average(),2) ~
", which is " ~
// We use (CONDITION ? TRUE : FALSE) to add if-else logic. Here we check if the average DE_RATIO
// is below the DE_TARGET, we set in our Assumptions. If it is, we return the string "below" styled
// in green color, as for this covenant, we don't want to exceed the target. Alternatively, if
// the average is above the target, we get the string "above" in red color to reflect the fact we
// are in breach of the covenant.
(DE_RATIO.average() < DE_TARGET ? style("below", color('#006400')) : style("above", color('#DC143C'))) ~
" the target value of " ~
// We again use the numberFormat() function to present our DE_TARGET assumption with 2 digits
// after the decimal point.
numberFormat(DE_TARGET,2) ~
"." ~
// We also have some helper functions, the newLine() being one of them, and simply starting the
// next string on a new line.
newLine() ~
"The ratio " ~
// Here, we want to see if the DE_RATIO increased over the period. We can use cagr(), another
// helper function that we can use to see whether any line item/formula is increasing or decreasing
// over time.
(DE_RATIO.cagr() > 0 ? "increased" : "decreased") ~
" from " ~
// We can also reference a line/formula for a specific period with the .for() function.
// What we are doing below is taking the index of the earliest period where DE_RATIO can be calculated,
// and pass this index to the DE_RATIO.for() function. That way we get the DE_RATIO for the first
// period where it can be calculated.
numberFormat(DE_RATIO.for(DE_RATIO.earliestPeriod().index),2) ~
" to " ~
// Same as above but for the latest period.
numberFormat(DE_RATIO.for(DE_RATIO.latestPeriod().index),2) ~
", with the highest value of " ~
// To show the highest value of DE_RATIO we use the .max() function.
numberFormat(DE_RATIO.max(),2) ~
" achieved in " ~
// We can then use the .maxPeriod().name to get the name of the period where DE_RATIO has its
// highest value.
DE_RATIO.maxPeriod().name ~
" and the lowest value of " ~
// We can do the same for the lowest value via the .min() function
numberFormat(DE_RATIO.min(),2) ~
" achieved in " ~
// And the .minPeriod() would reference the period where DE_RATIO has the lowest value.
DE_RATIO.minPeriod().name ~
"."

Ok, now that was a lot of code. But it’s pretty simple to read and understand if you look at it. Click Save, and let’s Launch Report once more.

If you look below the table we previously added, you see the result of the Analysis Point we just typed.

Over the period from CY 2018 to CY 2020, Templates Company operated at an average Debt/Equity Ratio of 0.49, which is below the target value of 1.00. The ratio decreased from 0.48 to 0.47, with the highest value of 0.50 achieved in CY 2019 and the lowest value of 0.47 achieved in CY 2020.

We now have a short analysis of our Debt-to-Equity covenant over time.

I repeated the same process for the other two covenants (Interest Coverage Ratio and Net Debt to EBITDA Ratio).

Here are the scripts for the two Analysis Points. You will notice they are pretty much the same with some minor changes. The most major one is that for the Interest Coverage Ratio we want to be above the target.

Interest Coverage Ratio Script

"Over the period from " ~
ICR_RATIO.earliestPeriod().name ~
" to " ~
ICR_RATIO.latestPeriod().name ~
" {{COMPANY_NAME}} operated at an average Interest Coverage Ratio of " ~
numberFormat(ICR_RATIO.average(),2) ~
", which is " ~
(ICR_RATIO.average() > ICR_TARGET ? style("above", color('#006400')) : style("below", color('#DC143C'))) ~
" the target value of " ~
numberFormat(ICR_TARGET,2) ~
"." ~
newLine() ~
"The ratio " ~
(ICR_RATIO.cagr() > 0 ? "increased" : "decreased") ~
" from " ~
numberFormat(ICR_RATIO.for(ICR_RATIO.earliestPeriod().index),2) ~
" to " ~
numberFormat(ICR_RATIO.for(ICR_RATIO.latestPeriod().index),2) ~
", with the highest value of " ~
numberFormat(ICR_RATIO.max(),2) ~
" achieved in " ~
ICR_RATIO.maxPeriod().name ~
" and the lowest value of " ~
numberFormat(ICR_RATIO.min(),2) ~
" achieved in " ~
ICR_RATIO.minPeriod().name ~
"."

Net Debt to EBITDA Ratio Script

"Over the period from " ~
NDE_RATIO.earliestPeriod().name ~
" to " ~
NDE_RATIO.latestPeriod().name ~
" {{COMPANY_NAME}} operated at an average Net Debt to EBITDA Ratio of " ~
numberFormat(NDE_RATIO.average(),2) ~
", which is " ~
(NDE_RATIO.average() < NDE_TARGET ? style("below", color('#006400')) : style("above", color('#DC143C'))) ~
" the target value of " ~
numberFormat(NDE_TARGET,2) ~
"." ~
newLine() ~
"The ratio " ~
(NDE_RATIO.cagr() > 0 ? "increased" : "decreased") ~
" from " ~
numberFormat(NDE_RATIO.for(NDE_RATIO.earliestPeriod().index),2) ~
" to " ~
numberFormat(NDE_RATIO.for(NDE_RATIO.latestPeriod().index),2) ~
", with the highest value of " ~
numberFormat(NDE_RATIO.max(),2) ~
" achieved in " ~
NDE_RATIO.maxPeriod().name ~
" and the lowest value of " ~
numberFormat(NDE_RATIO.min(),2) ~
" achieved in " ~
NDE_RATIO.minPeriod().name ~
"."

And the best part is that everything is now set up, and it takes a few minutes to import new data and have your report run for it.

Here’s the file with additional data for you to try it out.

If you open your Import Templates and add the new data within the already existing Income Statement and Balance Sheet, all you need to do is fix the MISSING DATES of the new periods, and you can Launch Report right away.

Here’s what the Interest Coverage Ratio section looks like once we add the additional data from the file above.

Wrapping Up

If you’ve made it this far, you should now have a pretty good understanding of how you can leverage Magnimetrics to build all kinds of reports to help you quickly translate your financial data into meaningful insights.

Now, check some of the other templates (we will be adding more of those) or build your own report from scratch!

Don’t forget to subscribe and stay updated on all the additional features we plan to release!