Financial Ratios

[paulakeen]

About financial ratios:

There would be the need of modifying the current model 1.0.2 to enhance the current structure for RATIOS. Below some considerations about how to approach according to ES:

every MS and public administration has its own practice; i.e. different schools for the same type of ratio, therefore it is not easy to come up with a clear and objective nomenclature different countries may interpret the same ratio in different ways. However, in the end, the ratio is adimensional, meaning there isn’t any unit measure associated to it. Possible solutions could be:

1. XBRL approach: a ratio is represented as a numerator divided by a denominator in a structured and machine-oriented way. The most complex case could be that the numerator and/or the denominator are the result of a complex addition (or formula);
2. Codified ratios: Use some of the ratios defined by the European Banks (see for instance the BACH system supported by 11 European Central Banks at ). This would take advantage of the support that the main countries already using them could provide. Also, these countries have databases with temporal values, percentiles, etc. The solution would consist in using the exact terminology and values used by these banks. This database is currently managed (kept) by the Bank of France;
3. Self-maintenance of the ratio taxonomy: Alternatively, if any of the two previous options were appropriate, the Contracting Authority would define its own taxonomy with different codes per ratio. In this case the CA could chose the type of ratio applicable to the specific sector the CA is interested in.

Amongst the three options the second one is probably the simplest as it would take advantage of a well-proven (and free) system managed by trusted European institutions with a large experience in financial subjects.

As pointed above, the ratios are adimensionals, as they are the quocient between two amounts expressed in one single unit (the currency), but must be referred to a well defined temporal context (a period, with a start and end dates; or a specific point in time, e.g. a date). In general the temporal context will be a specific date, although for some ratios both the numerator and the denominator are "varying flows" (e.g. "profit margin"), in which case the temporal context is the period (generally an annual period, not necesarily the "natural" year) through which numerator and denominator have been measured ("net income" and "sales", for example).

From the technical perspective, none of the above cases would impact on the the XSD Schema; the impacts would be on (i) the XML instantiation; the GUIs, (ii) the business rules currently expressed in Schematron.

Proposed milestone: v1.1.0

The figure below would illustrate a data-structure that would cover any of these possibilities. Ideally if the RatioType code is used the Numerator and Denominator values should not be used (and vice-versa). This would be controlled via Schematron. However one could also consider that Numerator, Denominator and RatioType could simultaneously be specified; the con: possible inconsistencies wouldn't be so easy to validate. Let's recall you that if the RatioType is used, the code issuer agency should be specified (compulsorily) amongst the code attributes (so to identify XBRL, European Banks, the CA, other).

This proposal will have to be readjusted to match the final decisions on the above three possibilities.

[ec-mcs]

I would suggest that we clearly specify: What information needs to be provided by the CA and which by the EO. We could use different colours for the circles.

[ec-mcs]

Comments from chat

Timo Rantanen - Hansel - Finland: examples current ratio, quick ratio, ROI, etc...

Jose Luis Cueva - Spain: I thin the BACH system has around 30 standard ratios

Rudolf: Can somebody give an example where and how this is used in public procurement? I just know rating systems provide by various Institutions in various countries.

Jose Luis Cueva - Spain 2: https://www.bach.banque-france.fr/

Jose Luis Cueva - Spain 2: i have echo I cannot hear well

Jose Luis Cueva - Spain 2: could you repeat please

Rudolf: I think this istoo complicated. It is more practicable from both sides CA and EO to use confirmation that tghe EO has a certain insurence in relation to the contract to be awardedor a statement of a bank which porves that the bak takes responsibility i ncase the EO fails.

Timo Rantanen - Hansel - Finland: fixed!!!

Steve Patterson: I think it would be bteer to have a fxed list from a data perspective, but I don't kno how realistc/achievable that would be.

Rudolf: How can the CA check/eveluatge al these aspects. What is the proof that the given data are right? What about small and middle sized companies.?

Jose Luis Cueva - Spain 2: I will put BACH ratios on github

Jose Luis Cueva - Spain 2: thankyou all, bye

Steve Patterson: bye

[rantati7]

Financial Structure Ratios

R11 Assets to Equity ratio Total balance sheet/Total equity R12 Liabilities to Equity ratio Total liabilities/Total equity R13 Current assets/Total assets Current assets/Total balance sheet R14 Other financial assets and cash and bank/ Total assets Other financial assets and cash and bank/Total balance sheet R15 Non-current debt/ Total assets Non-current debt/Total balance sheet R16 Current debt/Total assets Current debt/Total balance sheet

Financial and Debt Service Ratios

R21 Financial income net of charges other than interest/EBITDA Financial income net of charges other than interest/EBITDA R22 EBITDA/Interest on financial debt EBITDA/Interest on financial debts
R23 Interest burden EBT/EBIT R24 Interest and similar charges/Net turnover Interest and similar charges/Net turnover R25 Interest and similar charges/Gross operating profit Interest and similar charges/ Gross operating profit R26 Net financial income/Gross operating profit Financial income net of charges/ Gross operating profit R27 Gross operating profit/Total net debt Gross operating profit/Total net debt

Profitability Ratios

R31 Gross value added/Net turnover Gross value added/Net turnover
R32 Gross operating profit/Net turnover (ROS) Gross operating profit/Net turnover
R33 EBITDA/Net turnover EBITDA/Net turnover R34 Net operating profit/Net turnover Net operating profit/Net turnover
R35 EBIT/Net turnover EBIT/Net turnover
R36 EBT/Net turnover EBT/ Net turnover
R37 Net financial income/Net turnover Net financial income/Net turnover
R38 Return on equity Net profit or loss for the period/Total equity
R39 Net operating profit/Total assets Net operating profit/Total balance sheet
R310 Profit or loss for the year before taxes/Equity Profit or loss for the year before taxes/Total equity

Activity and Technical Ratios

R41 Asset turnover ratio Net turnover/Total balance sheet
R42 Employee expenses/Gross value Staff costs/Gross value added ¨ WorkingCapital Ratios

R51 Inventories/Net turnover Inventories/Net turnover R52 Trade receivables/Net turnover Trade receivables/Net turnover R53 Trade payables/Net turnover Trade payables/Net turnover R54 Operating working capital/Net turnover Operating working capital/Net turnover

[JoseLuisCueva]

Thank you! (so quick!) These are the ratios provided by BACH.

I attach the excel file with ratios names and definitions as downloaded from BACH (just the ratios, they have much more stuff). BACH-DataBase-Ratios.xlsx

[JoseLuisCueva]

I attach as well the full excel file that (besides ratios) includes codes, names and definitions of all financial concepts managed and used (from the balance sheet and the profit&loss statement). BACH_DataBase-Chapter_1.1_Templates_and_contents.xlsx

[JoseLuisCueva]

Answering Steve Patterson: "I think it would be better to have a fxed list from a data perspective, but I don't kno how realistc/achievable that would be"

Steve: I believe the beauty of using BACH is: they (European Central Banks and European Stat Institutions) already did such "fixed list of ratios", it is up and running, tested, open and free! Why not use it? So we can leverage their effort and save a lot of work (and trial/error). The questions are, I think: Are our needs covered by the set of ratios provided by BACH? Are they "good enough" for our needs? Do we really need additional/different ratios? Is it a cost-efficient solution for Public procurement, or should we strive for something better/more customized?

[rantati7]

These are all good and taught in business schools all over the world. Reason why these are not used more in public procurement: all procurement people have not gone to business schools and learned how these could be used. I recon law schools do not teach these...

One disclaimer: all ratios MUST be calculated from adjusted financial statements and doing this is not straight forward. There are also different methods used for adjusting the financial statements...

[JoseLuisCueva]

Thanks, Timo. I fully agree.

Regarding your disclaimer, we should expect that any respectable EO before filling his ESPD will ask proper advice when computing his own ratios, in case he is not knowledgeable enough about accounting (it is a one-time task, not a task for every procurement procedure).

Regards, Jose Luis Cueva

De: Timo Rantanen [mailto:notifications@github.com] Enviado el: jueves, 03 de noviembre de 2016 15:28 Para: ESPD/ESPD-EDM CC: Cueva Calabia, José Luis; Comment Asunto: Re: [ESPD/ESPD-EDM] Financial Ratios (#46)

These are all good and taught in business schools all over the world. Reason why these are not used more in public procurement: all procurement people have not gone to business schools and learned how these could be used. I recon law schools do not teach these...

One disclaimer: all ratios MUST be calculated from adjusted financial statements and doing this is not straight forward. There are also different methods used for adjusting the financial statements...

— You are receiving this because you commented. Reply to this email directly, view it on GitHubhttps://github.com/ESPD/ESPD-EDM/issues/46#issuecomment-258157776, or mute the threadhttps://github.com/notifications/unsubscribe-auth/ATTmvBnH4hwfWykU1nNEjRQ1cCtT6k4Qks5q6e-HgaJpZM4KnIH8.

***** Aviso Legal ***** Este mensaje y cualquier fichero adjunto está dirigido únicamente a sus destinatarios y contiene información confidencial. Si usted considera que ha recibido este correo electrónico por error (por el asunto, por el remitente o por cualquier otra causa), le informamos que cualquier revisión, alteración, impresión, copia o transmisión de este mensaje o de cualquier fichero adjunto está prohibida y puede constituir un acto ilegal. Por favor, notifíquele el error al remitente respondiendo a este e-mail y elimine el mensaje y su contenido inmediatamente. El Ministerio de Hacienda y Administraciones Públicas se reserva las acciones legales que le correspondan contra todo tercero que acceda de forma ilegítima al contenido de cualquier mensaje externo procedente del mismo.

***** Disclaimer ***** This e-mail and any files transmitted with it are intended solely for the use of the intended recipients and may contain confidential information. If it appears (from the subject matter or address information or otherwise) that you received this email in Error, please note that any review, dissemination, disclosure, alteration, printing, copying or transmission of this e-mail or any file transmitted with it is prohibited and may be unlawful. Please notify us by return email and delete this email and its contents immediately. Spanish Ministry of Finance and Public Administrations, according with the illegal access to the content of any external message from the Ministry.

[rantati7]

Now that I checked the ECCBSO instructions, they have standardised the way the adjusted financial statements must be presented. This is good news. However this of course applies to companies in EU, only.

For US and as we can finally deal with Canadian companies, the rules are slightly different. But on larger scale this does not matter....

[JoseLuisCueva]

Rudolf: Regarding the question of how can the CA check the ratios provided by the EO, AFAIK the proof is in his (hopefully audited) accounts. The same applies for turnover, profits and any other financial data. In Spain (and I guess in many other countries) every EO must formally state and approve every year his financial accounts, and deposit them (filed and made available) in a public Registry. This is a legally binding obligation, and any falsehood or misrepresentation constitutes a serious legal breach. In some cases an audit report is also required; in other cases it is optional, but it might be requested by a partner or stakeholder. So the attestations to be requested from the EO as a proof (if the CA chooses to do so) are the (audited) formally approved and filed financial accounts for fiscal year(s) X, Y, Z.

[rantati7]

One more comment: majority of these ratios have recommended or "classified as good" values. For example Quick Ratio or as it's commonly called "Acid Test" has the following recommended values:

Excellent >1.5 Good 1-1.5 Satisfying 0.5 - 1 Passable 0.3 - 0.5 Weak <0.3

Generally at least 1:1 is seen as a line separating good from the bad. And over 1 does not really tell much.

Ratios, too, must not be used so that only one of those is looked at. Using a combination of different ratios gives generally a much better picture. In my view these should be used more in PP. We "just" need to train the users in every country to use these professionally.

[JoseLuisCueva]

I agree. A good set of ratios, properly chosen according to the nature and size of the contract, could be a good criterion for financial standing (much better than i.e. profit alone). Its use could contribute to a better, more professional, public procurement. I guess the BACH system could serve also as a "knowledge tool" for public procurement civil service.

[rantati7]

I initiated a questionnaire in our Yammer forum to CAs in Finland who are using eTendering - asking if they would use the ratios defined by the central banks.

Currently majority of CAs are using only one ratio: a private sector supplier's "Risk Meter" (TM) and they have to pay for using it. It is rather simplistic but easy to use giving scores between 1 - 5 on how "good" the company is (1 bad - 5 excellent).

I would expect to receive answers rather quick: normally within the same day but in this case I would allow them to ponder the question a bit longer... say until next Monday. I will post the results here.

[rantati7]

Regarding CAs willingness to use the ready made ratios:

I have results from my short questionnaire from our Yammer group. Votes go exacly 50 / 50.

The other half of CAs want to use the current easy to undestand private sector provided ratio service and another vote for having ready made ratios inside ESPD.

With a little training I bet I could get more users for the ready made ratios!

[ec-mcs]

Example CA Multiple Financial Ratios (0..n) and this defines maximum or minimum automatically Each of them (Pass/Fail) CA need to define threshold

Answer EO Number (automatically if pass or fail)

[ec-mcs]

Timo Rantane / Hansel / Finland: copied ... copied...

Steve Patterson (Scottish Government): I only asked one person in our accountancy services team, and he was happy with the BACH list.

Timo Rantane / Hansel / Finland: some ratios need to have CA defining the minimum

Timo Rantane / Hansel / Finland: some of the ratios need to have maximum

Steve Patterson (Scottish Government): we always go pass/fail for financial assessment

Timo Rantane / Hansel / Finland: yes multiple ratios are MUST

Zilvaras: No

Timo Rantane / Hansel / Finland: yes they are pass pail but ca must ...

Jose Luis Cueva - Spain: yes. I theik so

Timo Rantane / Hansel / Finland: define minimum of maximum

Timo Rantane / Hansel / Finland: or

Jose Luis Cueva - Spain: sorry : I think so, pass/fail

Timo Rantane / Hansel / Finland: answer from Eo would be number

Timo Rantane / Hansel / Finland: and automatically pass / fail

Enric Staromiejski (GROW): @MC: can you please add this nex to the Pass/Fail?: --> Busines Rule!

Jose Luis Cueva - Spain: I guess the nature of the ratio tell us if there is max or min

Zilvaras: We can give points for bigger turnover

Timo Rantane / Hansel / Finland: this will require a lot of training to CAs on what to use but this is a completely other matter and not related to how ESPD data model works / is defined

Jose Luis Cueva - Spain: so I guess the threshold suffices: no need to tell if the actual ratio must be higher or lower

Timo Rantane / Hansel / Finland: it can be maximum or minimum

Timo Rantane / Hansel / Finland: yes

Zilvaras: Is this financial ratio: Critical liquidity=(Current assets - Inventories) / Current liabilities

Jose Luis Cueva - Spain: It is implicit on the nature of the ratio

Zilvaras: I think it is possible to give points for that

Zilvaras: We ask minimum 0.5, but the better is 1

Jose Luis Cueva - Spain: Ratios can work either as threshold (pass/fail) or weighted

Timo Rantane / Hansel / Finland: I do not think this is "legally correct"

Timo Rantane / Hansel / Finland: ratios are pass / fail as they concern the company

Timo Rantane / Hansel / Finland: these are selection criteria .... in two phase procedure these might be used in first phase but only in two phase procedures

Steve Patterson (Scottish Government): We would not normally weight any financial criteria. A comany either meets the minimum standard set by the authority, ot they do not

Timo Rantane / Hansel / Finland: and the given scoring

Jose Luis Cueva - Spain: We always use pass/fail, but someone eles could weight ratios as any other quantitative criterion

Timo Rantane / Hansel / Finland: only in the first phase of two phase procedure

Zilvaras: Yes we can

Jose Luis Cueva - Spain: OK

[rantati7]

Here are some examples from our self-contained ESPD:

1. CA defines Economic and Financial Standing

[rantati7]

1. CA defines three ratios: Current ratio, Quick ratio and Net Gearing and minimum or maximum passable values for each (>1,5; > 1 and <100%, subsequently)

![Uploading 2.jpg…]()

[rantati7]

Currently I cannot get screen shots how this looks to the EOs - guys are fiddling with the EO side of the test system and it does not work at the moment

[JoseLuisCueva]

I guess a simplification can be done. I think that, from the viewpoint of solvency/financial standing, there is no need to specify whether the required threshold is a minimum or a maximum, nor is required to specify an interval. The ratio itself implies if "the higher the better" or "the lower the better" (again, from a solvency point of view, which is the relevant function in the ESPD). So, if I'm right, for every ratio selected as criterion, the CA has to specify just its identification (code and/or proper name) and the "worst aceptable value" (adimensional, no units required). Any EO with better values than those specified by the CA will be selected, otherwise rejected.

But I might be wrong and this does not hold for some ratios.

[paulakeen]

See below a new proposal for the element ccv:Requirement that provides a solution for the specification of minimum and maximum values. This can be used, for example in the case of Ratios and Turnovers to specify the expected minimum and/or maximum values.

Beware that the attribute "ResponseDataType" has been revamped into a sub-element of the ccv:Requirement element named "ExpectedDataType", thus aligning the ESPD-EDM model with the UBL-2.2 approach.

[paulakeen]

A possible user interface from the CA point of view could be the one represented below:

[rantati7]

It would be more user-friendly If the number of ratios did not have to be chosen in the beginning. Maybe not possible but "add new ratio" after each "box" (I mean those boxes that have: select ratio type, definition and minimum requirement inside the blue line making a box) would be more handy.

Timo

---

From: paulakeen notifications@github.com<mailto:notifications@github.com> Sent: Tuesday, December 13, 2016 11:33 AM Subject: Re: [ESPD/ESPD-EDM] Financial Ratios (#46) To: ESPD/ESPD-EDM espd-edm@noreply.github.com<mailto:espd-edm@noreply.github.com> Cc: Rantanen Timo timo.rantanen@hansel.fi<mailto:timo.rantanen@hansel.fi>, Comment comment@noreply.github.com<mailto:comment@noreply.github.com>

A possible user interface from the CA point of view could be the one represented below:

[mu_financialratio_ca]https://cloud.githubusercontent.com/assets/16134316/21134736/82c60b88-c11f-11e6-80f0-c92538af710f.png

- You are receiving this because you commented. Reply to this email directly, view it on GitHubhttps://github.com/ESPD/ESPD-EDM/issues/46#issuecomment-266690018, or mute the threadhttps://github.com/notifications/unsubscribe-auth/ATTSAMrZY9ZruJCidh6qsF-VumU_VrkPks5rHmZSgaJpZM4KnIH8.

[paulakeen]

A possible user interface from the EO point of view could be the one represented below:

[paulakeen]

Following Timo's proposals we herein copy a slightly modified mockup:

[paulakeen]

The figure below illustrates a data structure with the modifications discussed above:

[paulakeen]

The XSD Schema corresponding to the above UML diagram looks like the one below. Please notice that the Requirement attribute ResponseDataType has been converted into a element name ExpectedDataType (inside the Requirement complex element).

[paulakeen]

The XML instance corresponding to the above to the structure would be similar to this:

[paulakeen]

The figure below illustrates a data structure with the modifications discussed above and new period field (with a start and end dates):