Financial planning on excel for crisis indicators

A model of financial analysis and planning on excel allows to quantify the indicators of the business crisis and in particular the DSCR

Financial planning on excel for crisis indicators

The recent adoption of the Corporate Crisis Code has introduced alert indicators capable of signaling the existence of conditions of probable financial insolvency.

The National Council of Chartered Accountants and Foreign Accountants has produced a document, which defines the structure of the indicators and the related threshold values.

The chosen INDICATORS are the following:

·         Negative net assets;

·         Dscr (debt service coverage ratio);

·         Sustainability index of financial expenses;

·         Capital adequacy index;

·         Liquid return index of assets;

·         Liquidity index;

·         Social security and tax debt index.

The NEGATIVE NET EQUITY is an absolute value (not a relationship like all the others) and expresses the maximum crisis value characterized by the share capital totally eroded by operating losses. In the case of a limited company, the phenomenon occurs if the capital falls below the legal minimum limit.

The DSCR (Debt Service Coverage Ratio) is the ratio between the cash or cash flows produced by the management and the cash or cash flows for the repayment of the financial debts. It is the only prospective indicator , as it must express a value> = 1 in a prospective time horizon equal to 6 months.

L ' INDEX SUSTAINABILITY' OF FINANCIAL EXPENSES , the ratio between financial expenses and revenues, expresses the maximum value of the sustainability of the debt cost from product sales.

The BALANCE SHEET ADEQUACY INDEX , given by the ratio between shareholders' equity and total debt, expresses the degree of balance of the overall financial structure;

L ' INDEX RETURN OF LIQUID ASSET , given by the ratio between the cash flow and capital activities, indicating the adequacy of self management product compared to the total of the invested capital;

The LIQUIDITY INDEX , from the ratio between current assets and current liabilities, expresses the equilibrium degree of current financial management;

The SOCIAL SECURITY AND TAX INDEBT INDEX , given by the relationship between social security and tax indebtedness and Asset assets, defines the sustainable dimensioning of social security and tax payables compared to the total capital invested.

For all indicators no. 3, 4, 5, 6 and 7 the threshold value is differentiated by sector of the enterprise (agriculture, manufacturing, commerce, etc.).

The approach procedure for the analysis of critical issues involves the following steps :

1.     Check conditions NET EQUITY : if NEGATIVE, the reasonable presumption of the state of crisis is triggered; if POSITIVE follows the second step;

2.     Check DSCR conditions : if> = 1, the absence of a reasonable presumption of the state of crisis is triggered and therefore the procedure is stopped; otherwise (<1), the third step follows;

3. Analysis of all the other indicators indicated and verification of any exceeding of the set thresholds. To find the reasonable presumption of the state of crisis it will be necessary that all indicators exceed the tolerance threshold.

In order to be able to monitor the entire system of indicators thus configured, in particular with regard to the DSCR, the undersigned has prepared a model of FINANCIAL ANALYSIS AND PLANNING ON EXCEL to be implemented in each company, useful for calculating all the indicators indicated not only on the FINAL but, above all, according to both a short-term (BUDGET) and a medium-term (INDUSTRIAL PLAN) perspective with which to target their probable future value.

Read Also: Auditors in UAE

Therefore, on the basis of this model, optimize all decisions (income, financial, organizational) both to achieve the predefined objectives typical of the normal financial planning process and to monitor the system of alert indicators for the business crisis.