Projet PADoCC_Ecritures, programme de supervision des comptabilités groupe et d’analyse des données comptables

PADoCC_Ecritures est un programme de supervision des comptabilités d’un groupe et d’analyse des données comptables qui vise à répondre à plusieurs objectifs complémentaires :

  • Superviser de manière rationnelle et en temps réel les activités comptables d’un groupe afin de s’assurer, dans le respect des délais, de la bonne application de la réglementation comptable et fiscale et des normes et procédures du groupe ;
  • Analyser les données comptables en se focalisant sur les écritures importantes ou atypiques au plus près de la survenue de l’événement comptable (analyse des schémas d’écriture, cohérence schéma d’écriture / type de journal / TVA) ;
  • Systématiser les opérations de contrôle tout au long de l’exercice en présence de volumes de données conséquents ;
  • Aider à la justification des comptes (dossier bilan, AppliAUDIT) dans un contexte de réduction des délais ;
  • Collecter et réconcilier les intercompagnies ;
  • Prévenir les risques de fraudes et d’erreurs (analyse des schémas d’écriture, ciblages des écritures atypiques, imprévisibilité des contrôles…).

J’ai commencé ce projet à l’été 2019. Je suis reparti du projet M/ATAC dont j’ai repris les grands principes (analyse de données) tout en veillant à le perfectionner. M/ATAC traitait des comptabilités mono-entité de l’ordre de 100 à 200 mille lignes sous Excel en 15 minutes environ (pour chaque comptabilité). PADoCC_Ecritures a été conçu, dès le départ, pour exploiter des comptabilités (fichiers FEC) de plusieurs entités simultanément avec des temps de traitement réduits. Le moteur de calcul, sous Access (module VBA générant à la volée des requêtes SQL), a été optimisé traitant ainsi plus dix millions de lignes d’écritures (réparties sur près de 80 entités) en environ deux heures et demi, soit un million de lignes traitées tous les quarts d’heure.

'Création table COHE_VAR_DEPRCLT
'Teste la cohérence dépréciation créances clients (variation comptes 491x et comptes 68174x/78174x)
'
NomReq = "ECRITUREStemp"
NomTable = "B105_COHE_VAR_DEPRCLT"
'---BDD ENTITE
TexteReq = "SELECT CodeEntité, Round(tVAR_491x,2) AS VAR_491x, Round(tCpte68174x,2) AS Cpte68174x, Round(tCpte78174x,2) AS Cpte78174x, Round(tECART,2) AS ECART, Secteur, Périmètre"
TexteReq = TexteReq & " INTO " & NomTable & " IN '" & dossier_Bases & NomBaseDestinationEnCours & "'"
TexteReq = TexteReq & " FROM (SELECT CodeEntité, Sum(VAR_491x) AS tVAR_491x, Sum(Cpte68174x) AS tCpte68174x, Sum(Cpte78174x) AS tCpte78174x, Sum([VAR_491x]+([Cpte68174x]+[Cpte78174x])) AS tECART, Secteur, Périmètre"
TexteReq = TexteReq & " FROM (SELECT [_ECRITURES].CodeEntité, Sum([_ECRITURES].Solde) AS VAR_491x, 0 AS Cpte68174x, 0 AS Cpte78174x, Secteur, Périmètre"
TexteReq = TexteReq & " FROM [_ECRITURES]" & " IN '" & dossier_Bases & BDD_Final & "'"
TexteReq = TexteReq & " WHERE ([_ECRITURES].Cpte3 = ""491"") And ([_ECRITURES].TypeJournal <> ""A_NOUVEAUX"")"
TexteReq = TexteReq & " GROUP BY [_ECRITURES].CodeEntité, Secteur, Périmètre"
TexteReq = TexteReq & " UNION ALL"
TexteReq = TexteReq & " SELECT [_ECRITURES].CodeEntité, 0 AS VAR_491x, Sum([_ECRITURES].Solde) AS Cpte68174x, 0 AS Cpte78174x, Secteur, Périmètre"
TexteReq = TexteReq & " FROM [_ECRITURES]" & " IN '" & dossier_Bases & BDD_Final & "'"
TexteReq = TexteReq & " WHERE ([_ECRITURES].Cpte5 = ""68174"")"
TexteReq = TexteReq & " GROUP BY [_ECRITURES].CodeEntité, Secteur, Périmètre"
TexteReq = TexteReq & " UNION ALL"
TexteReq = TexteReq & " SELECT [_ECRITURES].CodeEntité, 0 AS VAR_491x, 0 AS Cpte68174x, Sum([_ECRITURES].Solde) AS Cpte78174x, Secteur, Périmètre"
TexteReq = TexteReq & " FROM [_ECRITURES]" & " IN '" & dossier_Bases & BDD_Final & "'"
TexteReq = TexteReq & " WHERE ([_ECRITURES].Cpte5 = ""78174"")"
TexteReq = TexteReq & " GROUP BY [_ECRITURES].CodeEntité, Secteur, Périmètre)"
TexteReq = TexteReq & " GROUP BY CodeEntité, Secteur, Périmètre);"
If FEC_A_Traiter Then ExéReq
'---BDD SUPERVISION
If TableExiste(DB_Groupe_SUPERVISION, NomTable) Then
    If FEC_A_Traiter And (AnciennetéFEC <> 0) Then
        TexteReq = "DELETE FROM " & NomTable & " IN '" & dossier_Bases & BDD_Groupe_SUPERVISION & "' WHERE CodeEntité=""" & EntitéEnCours & """;"
        ExéReq
    End If
    TexteReq = ""
    TexteReq = TexteReq & "INSERT INTO " & NomTable & " IN '" & dossier_Bases & BDD_Groupe_SUPERVISION & "' "
Else:
    TexteReq = ""
End If
TexteReq = TexteReq & "SELECT *"
If Not (TableExiste(DB_Groupe_SUPERVISION, NomTable)) Then TexteReq = TexteReq & " INTO " & NomTable & " IN '" & dossier_Bases & BDD_Groupe_SUPERVISION & "'"
TexteReq = TexteReq & " FROM " & NomTable & " IN '" & dossier_Bases & BDD_Final & "';"
If FEC_A_Traiter Then ExéReq

PADoCC_Ecritures génère des bases de données Groupe et Entités que le superviseur peut exploiter à loisir pour mener ses travaux de révision. Ces travaux concourent, entre autres, à justifier de la qualité du contrôle interne et de la conformité des comptabilités telles que prescrit par la Loi et notamment en matière de Piste d’Audit Fiable et du respect des nombreuses obligations des entreprises (délais de règlements fournisseurs notamment, anticipation des CFCI sur Alto2).

Il est également possible de réaliser des tableaux de bord (reporting) sous Excel à l’aide d’un connecteur de données (fonctionnement similaire aux modules de BI du marché).

A ce jour, le projet, déjà parfaitement fonctionnel, est en cours de test.

Approfondir le sujet : Suivre le projet PADoCC… / Voir mes autres réalisations…

___

Principe général de fonctionnement du programme :

La matière première dont se nourrit le programme est le FEC. Le FEC présente l’avantage de reprendre l’intégralité de la comptabilité d’une entité sous une forme normalisée ; quel que soit le système informatique sur lequel est tenue la comptabilité, le contenu des champs de données est (quasiment) présenté de la même manière.

Le programme met en forme et analyse le contenu des FEC de l’ensemble du groupe. Cette analyse consiste à passer toutes les données dans un moteur de calcul composé de près de deux cents requêtes. Des champs de données additionnels sont ajoutés au FEC (taux de TVA, contrôles de cohérence…). Ce processus totalement automatisé épargne un temps précieux à l’analyste qui se focalise sur l’analyse de données pertinentes et non plus sur la mise en forme. La supervision des comptabilités d’un groupe passe ainsi par le contrôle transversal des comptes (par nature de transactions) et non plus entité par entité (toutes les données comptables sont passées au crible quelle que soit la taille de l’entité). L’analyse des risques est facilitée et documentée. L’analyse de données permet de tendre vers le full audit.

De fait, au fil du temps et au gré des progrès technologiques, l’auditeur, qui il y a encore une vingtaine d’années pointait les comptes au moyen d’un crayon à papier et de fluos sur des listings imprimés, se mue progressivement en data analyst.

Exemples d’analyses exécutées par le programme PADoCC_Ecritures :

Consultation du détail d’un compte (lignes d’écritures) à partir de la balance générale :

PADoCC_Ecritures - Consultation d'écritures à partir de la balance générale

Revue des comptes auxiliaires (clients, fournisseurs…) :

Les balances auxiliaires peuvent être couplées aux fiches de tiers des DMS ou des systèmes de GRC, à la cotation d’entreprises et aux informations juridiques publiées au RCS.

L’adjonction de ces informations aux balances auxiliaires permet de mieux cibler les contrôles à opérer sur les tiers.

PADoCC_Ecritures - BAUX avec cotation et infos jur

Ainsi les contrôles sont axés, non seulement, sur les arriérés de créances les plus importants, mais également concernant les tiers les moins biens cotés.

Par ailleurs, l’identification des tiers permet de connaître instantanément la position comptable de chacun d’eux (qu’ils soient clients ou fournisseurs) dans l’ensemble des entités du groupe et d’évaluer l’exposition du groupe au risque d’insolvabilité. En outre, un simple clic permet de consulter les informations juridiques fournies par le site internet Pappers (nom des mandataires sociaux, statuts, annonces légales, publications au BODACC…) et financières (comptes annuels, rapports des commissaires aux comptes) publiées au RCS.

Cela permet également de valoriser les volumes d’affaires traités avec chacun d’eux (achats et CA) à l’échelle du groupe.

Reporting & BI : les connecteurs de données Excel permettent d’interroger toutes les bases de données ACCESS et de bâtir rapidement des tableaux de bord (reporting) pour accélérer la prise de décisions ; toutes les tables de données sont interrogeables de cette manière (balances, écritures, tables de cohérence et d’anomalies…)

Exemple ici une synthèse des stocks établie à partir de la table de données _BGMULTEX :

PADoCC_Ecritures - Reporting stocks

Exemple de formule de calcul Excel permettant de calculer le stock :

=SOMME.SI.ENS(BGMULTEX!$G:$G;BGMULTEX!$A:$A;STOCKS!D$3;BGMULTEX!$I:$I;STOCKS!$A5;BGMULTEX!$H:$H;STOCKS!$B5;BGMULTEX!$V:$V;”371″)

Synthèse mensuelle des comptes de location (à partir de la table des écritures comptables _ECRITURES et du champ AAAAMM) sous forme de TCD :

PADoCC_Ecritures - Reporting achats

Les puissantes fonctions de visualisation et d’analyse de données d’Excel et les tableaux croisés dynamiques (TCD) peuvent être mises à profit pour bâtir des tableaux de bord dynamiques à partir de bases de données ACCESS très volumineuses (y compris au delà de la fameuse limite des un millions de lignes).

Nombreux contrôles de cohérence :

_ANOMALIES_BG
CodeEntité CompteNumHarmonisé CompteNum CompteLib Solde Type_Cond Libellé_Cond
H 1200000000000 12000000 RESULTAT EXERCICE (BENEF) XX ANOMALIE APPARENTE Sur le principe, le résultat de l’exercice antérieur doit être soldé
H 1711020000000 17110200 DETTE MA C XX ANOMALIE APPARENTE Sur le principe, ce compte doit être créditeur
H 2791780000000 27917800 PARTS NON LIB SCI XX POINT A JUSTIFIER Versements restant à effectuer sur titres : justification comptable et légale
H 4570000000000 45700000 ASSOCIES DIVID. A PAYER XX ANOMALIE APPARENTE Sur le principe, les dividendes auraient dû être payé dans les 9 mois de la clôture N-1
TS 2800000000000 2800000 Amts Immo Incorporelles XX ANOMALIE EF Ce compte n’est pas affecté à un poste dans les états financiers : les états financiers ne sont pas équilibrés
PAL 4886220000000 488622 PROV HONORAIRES XX ANOMALIE APPARENTE Sur le principe, ce compte doit être créditeur
  • Analyse des flux sur écritures de stocks :
C100_COHE_VAR_STK
CodeEntité SOLDE_Cptes3x ANV_Cptes3x VAR_Cpte3x Cpte603 Cpte713 CpteGest ECART Secteur Périmètre
TMBRK 4509711,49 3576517,81 933193,68 -922244,72 -10948,96 -933193,68 0 AUTO_AP HFM_HTM
TMFRU 1445842,82 521374,31 924468,51 -1095952,03 171483,52 -924468,51 0 AUTO_AP HFM_HTM
TMHES 738839,04 301841,88 436997,16 -501178,01 64180,85 -436997,16 0 AUTO_AP HFM_HTM
TMNOY 3569140,64 2149391 1419749,64 -1400759,02 -18990,62 -1419749,64 0 AUTO_AP HFM_HTM
  • Contrôle de cohérence des dates : validation des écritures au plus tard au jour de comptabilisation de la TVA, date pièce < date de validation…
  • Détection des doublons d’achats
  • Détection des encaissements en espèces > 1000 €
  • Audit de la TVA (taux de TVA par transaction…)

Nombreuses bases de données permettant au superviseur une foule de sujets d’audit :

Quelque 170 requêtes passent au crible le FEC de chaque entité du groupe et aboutissent à la génération de bases de données Entités et Groupe :

Liste des BDD « Entités » :

Type de base de données (BDD) Contenu
ENTITE Contient toutes les données (sources et calculées) d’une même entité : balances comptables, écritures comptables, contrôles…

Une BDD est calculée par entité.

Cette BDD est à l’usage du superviseur pour obtenir le détail des écritures

ENTITE – CONTRÔLE TVA Contient deux tables reprenant respectivement l’ensemble des écritures d’ACHATS et de VENTES avec des champs de données permettant d’analyser la TVA

Liste des BDD « Groupe » :

Type de base de données (BDD) Contenu
SUPERVISION Reprend l’ensemble des données de contrôle de toutes les entités de l’ensemble du groupe

Permet de piloter les opérations de supervision des activités comptables de l’ensemble du groupe

GROUPE – INTERCO Fournit des tables reprenant l’ensemble des transactions identifiées comme étant des intercompagnies
GROUPE – INTERCO_ECARTS Fournit les informations nécessaires à l’analyse des écarts sur transactions intragroupe
GROUPE – ACHATS Contient une table reprenant l’ensemble des écritures d’ACHATS du groupe afin de faciliter l’analyse de l’exécution des contrats et l’analyse des budgets.
GROUPE – BALAGEES Balances âgées (clients, fournisseurs…) de l’ensemble du groupe
GROUPE – FLXFI Reprend l’ensemble des lignes d’écritures enregistrant les flux financiers (journaux comptables de type BANQUE et CAISSE) de toutes les entités du groupe
GROUPE – DATES Reprend toutes les lignes d’écritures des entités du groupe présentant, a priori, un champ de date incohérent
Share Button
The following two tabs change content below.
Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog. Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter