CFADS & DSCR Dynamic Graph

In this article, we introduce how to use a dynamic graph of CFADS/DSCR as a useful tool for debt sculpting/sizing and credit analysis, particularly in project financings.

In Project Finance, Cashflow Available for Debt Service (CFADS) is analysed by project lenders (financiers) to determine debt sizes and repayment criteria. With CFADS larger than Debt Service, it is clear that there is a buffer in the project to protect the lenders from adverse scenarios.

CFADS also drives the financial modelling of the debt metrics, including the most common Debt Service Coverage Ratio (DSCR). A DSCR of less than one means that the CFADS are not strong enough to support the level of debt.

CFADS / DSCR Dynamic Graph

This example shows the debt repaid as an Annuity (“Credit Foncier”). The principal and interest together sum up to an equal amount each period, with more principal repaid in the later periods since the interest component decreases in line with the debt balance.

Screenshot #1: Graph of CFADS vs Debt Service

The first graph above illustrates the proportions of CFADS compared with Total Debt Service. In addition, it also highlights the period when the CFADS is the weakest which, in this example, is shown as “Min DSCR at 30-Jun-10″.

Screenshot #2: Graph of DSCR’s relative to Term Sheet covenant

The second graph shows the DSCR’s relative to the Term Sheet DSCR Covenant of 1.30x. In addition, this graph also shows the period of minimum DSCR, ie “Min DSCR of 1.47x on repayment date of 30-Jun-10″.

Application of the Dynamic Graph in the Project Finance analysis

The above graphs dynamically highlight the period when the minimum DSCR occurs, which is an important tool when the project finance lenders perform the following

Sculpting Debt Repayments
The above charts highlight the weakest DSCR and will thus assist lenders in debt sculpting to ensure that lower principal repayment is applied in a period with lower CFADS

Structuring the Grace Period
Grace Period is the number of periods where there is no obligation to repay the principal. The above charts also assist the lenders to determine the Grace Period, which is particularly common in projects where there is a ramp-up phase, such as toll roads and infrastructure projects

Scenarios and Sensitivity Testing
As graphs are dynamic, they will always highlight the minimum DSCR when the lenders perform testing of Scenarios and Sensitivities.
The chart can be copied and pasted to the credit papers or Information Memorandum to illustrate the results under various scenarios.

Create dynamic graphs in Excel

Below are the suggested steps to create the graph. The two graphs illustrated above can be plotted using the data as shown in the table.

Screenshot #3: Data to create Graph

The two graphs illustrated above can be plotted using the data as shown in the table.

Step 1: Create the dynamic “Min DSCR” Object
First, create the desired objects and text (using the varieties of auto shapes/text box from Drawing toolbar in Excel). Then select the objects you want to group (hold down SHIFT while you select each group) and click Draw – Group on the Drawing toolbar.

Screenshot #4: Example of “Min DSCR” object

Step 2: Create the Dynamic Series
This is created by identifying the period when the minimum DSCR occurs. The formula for “CFADS for Min DSCR” is:

“If Flag for Minimum = 1, then the cell is equal to the CFADS, or else equal to any negative number”

Following the same logic, the formula for “Min DSCR” is:

“If Flag for Minimum = 1, then the cell is equal to the DSCR, or else equal to any negative number”

Screenshot #5: Calculation to identify period of minimum DSCR

Step 3: Add Dynamic Series to the Graph
The “CFADS for Min DSCR” series is added to the first graph and the “Min DSCR” series is added to the second graph. The Chart Type used for the dynamic series is Line with Markers. It is important to set the Minimum Scale for the Format Axis in the graphs to be at least “zero” because it is not needed to show the negative numbers in the above table.

Step 4: Replace the Markers with “Min DSCR” Object
The last step is to replace the Markers with the previously created “Min DSCR” object and remove the line on the dynamic series. To replace the Markers, copy the “Min DSCR” object, then select the Markers on the graph and paste the object. To remove the line, double-click the dynamic series on the graph, then select Patterns – Line – None Optionally, data label could be added to the dynamic series to highlight when the minimum DSCR occurs and/or what the minimum DSCR is. This can easily be done by double-clicking the dynamic series on the graph, then select Data Labels – Category Name or Value

Share this post


You must be logged in to view the Tutorial