Comptes consolidés : calculer les pourcentages d’intérêt à l’aide du calcul matriciel et du langage VBA d’Excel (article publié dans la RFC)

La Revue Française de Comptabilité publie dans son numéro de janvier 2012 (n° 450) un article, que j’ai écrit, intitulé «Comptes consolidés : calculer les pourcentages d’intérêt à l’aide du calcul matriciel et du langage VBA d’Excel ».

Couverture RFC janvier 2012

Couverture RFC janvier 2012

Résumé de l’article :

La détermination des pourcentages d’intérêt s’avère fastidieuse dans les groupes comprenant de nombreuses participations. Les participations circulaires et croisées ajoutent un surcroît de complexité à ces calculs. Les calculs manuels habituellement mis en oeuvre apparaissent complexes tant à poser (consolideur) qu’à contrôler (auditeur) et sources d’erreur.

Méconnu, le calcul matriciel est une méthode de calcul alternative qui offre trois avantages déterminants : rapide à mettre en application, il ne nécessite pas de connaissance approfondie en calcul matriciel et en consolidation, et sécurise les calculs. De surcroît, la modélisation informatique des calculs matriciels est aisée et offre, en plus, une automatisation aboutie de l’ensemble du processus de calculs.

Introduction :

Les pourcentages d’intérêt sont calculés lors de la détermination du périmètre de consolidation. La précision des pourcentages d’intérêt revêt une importance particulière ; en effet, les pourcentages d’intérêt déterminent la part des capitaux propres et des résultats des entités consolidées (intégrées selon la méthode globale) qui appartient au groupe. Ils sont toutefois délicats à déterminer en présence de groupes de taille importante où les nombreuses participations incluent des participations croisées ou circulaires ; le summum de la difficulté étant atteint en cas de participations de filiales sur la mère. Pourtant l’auditeur ou l’expert-comptable qui souhaite valider les pourcentages d’intérêt tout en s’affranchissant de calculs manuels fastidieux souhaitera disposer d’une méthode de contrôle rapide et fiable.

Lecture de l’article : Cet article se présente en deux parties :

  • Une partie « papier », publiée dans la RFC, présentant la démarche du calcul matriciel et l’intérêt du langage VBA,

Comptes consolidés : calculer les pourcentages d’intérêt à l’aide du calcul matriciel et du langage VBA d’Excel (article publié dans la RFC)
431 KiB
9929 téléchargements
Détails...

  • Une partie « numérique », publiée sur ce site, présentant le code source du programme de calcul des pourcentages d’intérêt.

Cf en bas d’article.

Questions, suggestions ? N’hésitez pas à réagir sur le blog !

Retrouvez tous les articles sur l’audit des comptes consolidés

Programme de calcul des pourcentages d’intérêt présenté dans l’article :

Complément à l'article publié dans la Revue Française de Comptabilité de janvier 2012 (n° 450) : programme de calcul des pourcentages d'intérêt
31 KiB
106 téléchargements
Détails...

  • Consulter le code source de l’application : cf en pied d’article.

Pensez à autoriser l’exécution des macros pour permettre le lancement des calculs.

Complément du 29/01/2012 :

Deux compléments d’informations :

– Il ne faut pas modifier le nom du classeur (matrice.xlsm) ; dans le cas contraire le programme VBA renverra une erreur.

– La première version du programme ne pouvait fonctionner au delà de 26 entités (colonne Z) ; la nouvelle version du programme corrige ce bogue.

Bibliographie et échanges avec le lecteur :

  • Article « Matrice (mathématiques) », http://fr.wikipedia.org/wiki/Matrice_(mathématiques).
  • Livre « La consolidation directe, Principes de base », Allen WHITE, Editions De Boeck.
  • Livre « La consolidation, guide pratique », Stéphane MERCIER, Editions des CCI de Wallonie S.A.
  • Livre « Comptes consolidés », Editions FRANCIS LEFEBVRE.
  • Article « Le contrôle des comptes à l’aide du langage VBA d’Excel », Revue Française de Comptabilité n°445, Juillet/août 2011.
  • Ouvrages « VBA Excel 2007 – Maîtrisez la programmation sous Excel » (Editions ENI) et « VBA pour Office 2007 » (Micro-Application).
  • Article « Utiliser les variables tableaux en VBA Excel » sur l’URL : http://silkyroad.developpez.com/vba/tableaux/.
  • Sites internet http://vb.developpez.com/ et http://www.vbfrance.com/.
  • Espaces d’échanges ouverts à tous sur www.pacioli.fr : « Expertise Consolidation » et « Le Cercle des Développeurs ».
  • Echanges avec l’auteur : benoit@auditsi.eu.

Code source VBA du programme :

'POURCINT V1.0 08/2011
'Application calculant les pourcentages d'intérêt
'Programmée par Benoît-René RIVIERE, benoit@auditsi.eu
'Explications sur le fonctionnement du programme : www.auditsi.eu

Option Explicit

'=======================================================================
'DECLARATION DES CONSTANTES ET VARIABLES
'--------------------------------------------------------------------------------------------------------------------------
'Déclaration des constantes
'---Classeur et feuilles
Const NomClasseurAppli = "matrice.xlsm"
'------Onglet Entités
Const FeuilleEntités = "Entités"
Const ColonneRéfEntité = "B"
'------Onglet Détentions
Const FeuilleDétentions = "Détentions"
'Déclaration des variables
'---Classeur Excel
Dim classeurAppli As Workbook
'---Entités
'------Liste des entités
Dim Entités() As String
'------Nombre d'entités
Dim NbEntités As Integer

'---Détentions
'------Liste des détentions stockée sous forme de tableau à deux dimensions (matrice)
Dim MatriceDétentions() As Double 'X, Y : détenteur, détenu
Dim MatriceI() As Double
Dim MatriceImoinsDétentions() As Double
Dim PourcentagesIntérêt() As Double

'Lecture de la liste des entités
Sub LectureEntités()
  'Entité 1 = entité fictive
  'Entité 2 = entité mère
  '3, 4... = entités filles

  'ATTENTION : les références identiques (doublons) ne sont pas testées
  'Définition des variables propres à la procédure en cours
  Dim s As String

  '---Définition de l'entité fictive
  NbEntités = 1
  ReDim Entités(NbEntités)
  Entités(NbEntités) = "X"

  '---Lecture des entités
  Do
    s = classeurAppli.Sheets(FeuilleEntités).Range(ColonneRéfEntité & NbEntités).Value
    If s <> "" Then
      NbEntités = NbEntités + 1
      ReDim Preserve Entités(NbEntités)
      Entités(NbEntités) = s
    End If
  Loop While s <> "" 'dès qu'une référence est vierge, la lecture est arrêtée

  'Dimensionnement des matrices
  ReDim MatriceDétentions(1 To NbEntités, 1 To NbEntités)
  ReDim MatriceI(1 To NbEntités, 1 To NbEntités)
  ReDim MatriceImoinsDétentions(1 To NbEntités, 1 To NbEntités)
  ReDim PourcentagesIntérêt(2 To NbEntités)
End Sub

Function DonneCodeEntité(RéfEntité As String)
  Dim i As Integer

  i = 0
  Do
    i = i + 1
  Loop While (Entités(i) <> RéfEntité) And (i < NbEntités)
  If Entités(i) = RéfEntité Then
    DonneCodeEntité = i
  Else: DonneCodeEntité = 0 'Référence entité non reconnue
  End If
End Function

Sub ConstructionMatriceDétentions()
  'ATTENTION : en cas d'entité inexistente => aucune erreur ne sera signalée, les données inconnues sont ignorées

  'Définition des variables propres à la procédure en cours
  Dim s1 As String
  Dim s2 As String
  Dim i As Integer
  Dim v As Double
  Dim détenteur As Integer
  Dim détenu As Integer

  '---Lecture des détentions
  i = 0
  Do
    i = i + 1
    With classeurAppli.Sheets(FeuilleDétentions)
    s1 = .Range("A" & i).Value
    s2 = .Range("C" & i).Value
    détenteur = DonneCodeEntité(s1)
    détenu = DonneCodeEntité(s2)
    If (s1 <> "") And (s2 <> "") And (détenteur <> 0) And (détenu <> 0) Then
      v = .Range("B" & i).Value
      MatriceDétentions(détenteur, détenu) = v
    End If
  End With
  Loop While (s1 <> "") And (s2 <> "") 'dès qu'une référence est vierge, la lecture est arrêtée

  'Traitement de l'entité fictive
  MatriceDétentions(1, 2) = 1 'la détention de l'entité fictive sur la mère est de 100 % en l'absence de participations de filles sur la mère
  For détenteur = 2 To NbEntités
    MatriceDétentions(1, 2) = MatriceDétentions(1, 2) - MatriceDétentions(détenteur, 2)
  Next détenteur
End Sub

Sub ConstructionMatriceI()
  'Définition des variables propres à la procédure en cours
  Dim i As Integer

  For i = 1 To NbEntités
    MatriceI(i, i) = 1
  Next i
End Sub

Sub ConstructionMatriceImoinsDétentions()
  Dim détenteur As Integer
  Dim détenu As Integer

  For détenteur = 1 To NbEntités
    For détenu = 1 To NbEntités
      MatriceImoinsDétentions(détenteur, détenu) = MatriceI(détenteur, détenu) - MatriceDétentions(détenteur, détenu)
    Next détenu
  Next détenteur
End Sub

Sub AfficheMatrice()
  Dim détenteur As Integer
  Dim détenu As Integer

  classeurAppli.Sheets.Add after:=Worksheets(Worksheets.Count)
  With classeurAppli.Sheets(classeurAppli.Sheets.Count)
    .Cells(1, 1).Value = "Matrice I-M"
    For détenteur = 1 To NbEntités
      For détenu = 1 To NbEntités
        If détenteur = 1 Then
          .Cells(1, détenu + 1).Value = Entités(détenu)
          .Cells(3 + NbEntités, détenu + 1).Value = Entités(détenu)
        End If
        If détenu = 1 Then
          .Cells(détenteur + 1, 1).Value = Entités(détenteur)
          .Cells(détenteur + NbEntités + 3, 1).Value = Entités(détenteur)
        End If
        .Cells(détenteur + 1, détenu + 1).Value = MatriceImoinsDétentions(détenteur, détenu)
      Next détenu
    Next détenteur
    .Cells(3 + NbEntités, 1).Value = "Matrice (I-M)^-1"
    .Range("B" & 4 + NbEntités & ":" & Chr(64 + NbEntités + 1) & NbEntités * 2 + 3).FormulaArray = "=minverse(B2:" & Chr(64 + NbEntités + 1) & NbEntités + 1 & ")"
    'FormulaArray équivaut à Ctrl+Maj+Entrée
    For détenu = 2 To NbEntités
      PourcentagesIntérêt(détenu) = Cells(4 + NbEntités, détenu + 1)
    Next détenu
  End With
End Sub

Sub AffichePourcentagesIntérêt()
  Dim détenu As Integer

  With classeurAppli.Sheets(classeurAppli.Sheets.Count)
    .Cells(NbEntités * 2 + 1 + 4, 1).Value = "% intérêt"
    For détenu = 2 To NbEntités
      .Cells(NbEntités * 2 + détenu + 4, 1).Value = Entités(détenu)
      With .Cells(NbEntités * 2 + détenu + 4, 2)
        .NumberFormat = "##.00%"
        .Value = PourcentagesIntérêt(détenu)
      End With
    Next détenu
  End With
End Sub

Sub Calculs()
  '---Déclaration du classeur de l'application
  Set classeurAppli = Workbooks(NomClasseurAppli)
  LectureEntités

  ConstructionMatriceDétentions
  ConstructionMatriceI
  ConstructionMatriceImoinsDétentions

  AfficheMatrice
  AffichePourcentagesIntérêt

End Sub

Approfondir le sujet : programmer en VBA

Share Button
Comptes consolidés : calculer les pourcentages d’intérêt à l’aide du calcul matriciel et du langage VBA d’Excel (article publié dans la RFC)
431 KiB
9929 téléchargements
Détails...
Complément à l'article publié dans la Revue Française de Comptabilité de janvier 2012 (n° 450) : pas-à-pas du code source
104 KiB
838 téléchargements
Détails...
Complément à l'article publié dans la Revue Française de Comptabilité de janvier 2012 (n° 450) : programme de calcul des pourcentages d'intérêt
31 KiB
106 téléchargements
Détails...
The following two tabs change content below.
Benoît RIVIERE
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

39 commentaires

  1. Pingback: Les calculs matriciels en VBA | Audit & Systèmes d'Information

  2. Bonjour,
    Je viens de télécharger votre macro et je n’arrive pas à l’utiliser. En effet, un message d’erreur (type 9) signal que la ligne “Set classeurAppli = Workbooks(NomClasseurAppli)” n’est pas conforme.

    Pouvez-vous me dire la raison de ce bug?
    Vous en remerciant par avance
    Cordialement

  3. Bonsoir,
    Sur mon PC, la macro fonctionne très bien (je viens de la télécharger depuis le blog). Avez-vous modifié la macro ou le nom des onglets ? Essayez de télécharger de nouveau le fichier. Sinon quelle est votre version d’EXCEL ? Tenez-moi au courant (même si finalement le problème est résolu).
    Merci de votre confiance,
    Cordialement,
    B. RIVIERE

  4. Bonjour,

    J’ai trouvé l’erreur : lorsque l’on enregistre le fichier, celui ci prend le nom de “matrice_v2.xlsm” alors que dans le programme le tiret “_” n’est pas présente ->Const NomClasseurAppli = “matrice v2.xlsm” Il suffit de rajouter le tiret entre matrice et v2.

    Je suis Consolideur et je suis assez “friand” de ce genre d’application. Si vous en avez d’autre je suis preneur (calcul du goodwill, interet mino…)
    Vous en remerciant par avance

    Cordialement

  5. Pingback: Audit & Programmation : S’initier au langage VBA d’EXCEL | Audit & Systèmes d'Information

  6. Pingback: Conversion de programmes entre les langages VBA et DELPHI | Audit & Systèmes d'Information

  7. Bonjour,
    J’ai tenté de télécharger le code de “calculer les pourcentages d’intérêt à l’aide du calcul matriciel et du langage VBA d’Excel ” en m’inscrivant sur le site : aucune réponse de leur part.
    Merci de me transmettre si possible un lien de téléchargement.
    Votre site est génial.
    Mes salutations
    Olivier

  8. Bonjour,
    Après inscription sur le site, je n’arrive pas à trouver le lien pour télécharger le fichier excel avec le VBA.
    Pourriez-vous le reposter ?
    Merci aussi pour le partage de connaissances sur le sujet.
    Bien cdt

  9. Bonjour.
    Je n’arrive pas à trouver le lien pour le téléchargement. Pouvez vous m’aider ?
    Je suis sur un projet portant sur plus de 300 sociétés, je vous tiendrai au courant …
    Merci d’avance.
    Cordialement.

  10. Bonjour,

    Merci ce code correspond tout à fait à ce dont j’avais besoin !
    En revanche je ne parviens moi non plus pas à trouver le lien renvoyant vers le classeur excel bien que j’ai accès au code source. Je ne le trouve pas non plus dans compléments.

    Serait-il possible de m’indiquer où pourrais-je le trouver svp ?

    Merci d’avance.

  11. Je n’ai rien dit, le commentaire précédent peut être supprimé.
    Il suffit de commenter l’article pour que le lien apparaisse.

    Merci encore et désolé pour le dérangement.

  12. Bonjour,

    Le lien est en effet apparu dans “Compléments”, mais seulement après avoir commenté l’article…

    J’ai du louper une étape.

    Merci en tout cas pour ce fichier très utile !

    Bien Cordialement,

    Simon

  13. Bonjour,
    Votre site est très intéressant.
    Je ne parviens pas à trouver le lien pour télécharger le fichier… Pourriez-vous svp m´aider ?
    Cordialement
    Alexandre

  14. j’espère de trouver le lien pour télécharger. Il faut apparrement “commenter” sur l’article (qui correspond bien aux espérances, j’espère) 🙂

  15. Merci pour partager cetter article et le code VBA. J’ai quelques questions néanmoins:
    – Petite remarque: la matrice I-M ne peut pas être calculé juste comme ça, car ils ont des dimensions différentes. Matrice M doit d’abord étre élargi avec société fictive “X”;
    – Dans figure 4, sous X:SM je trouve le montant -0,93. Je comprend pas d’ou ce montant vient. S’agit-il de la différence entre 100% et le actions propres detenus par la groupe? La formule n’est donc pas exactement ‘I-M’. En général je voudrais mieux comprendre la theorie suivie pour arriver à ce résultat.

    Veuillez m’excuser pour mon pauvre connaissance de la langue Française 🙂

  16. Bonjour,
    Merci pour votre retour.
    Je tiens à préciser que je ne suis pas mathématicien et que je n’ai que quelques notions en calcul matriciel, notions que j’ai acquise en lisant des ouvrages sur le sujet et sur internet. J’avais lu quelque part que l’on pouvait résoudre des systèmes d’équations à l’aide du calcul matriciel. La problématique à laquelle je souhaitais répondre était celle des participations croisées ou circulaires qui sont particulièrement difficiles à traiter manuellement. L’idée d’utiliser le calcul matriciel pour résoudre le calcul des pourcentages d’intérêt m’est venu de là.
    Pour répondre à vos interrogations :
    – vous avez parfaitement raison et c’est bien ce que fait mon programme VBA.
    – il est parfaitement normal que le pourcentage d’intérêt du groupe sur la mère ne soit pas de 100% si une filiale du groupe détient une participation sur la mère (participation circulaire) dans la mesure où cette participation ne doit pas être prise en compte ni en consolidation, ni d’un point de vue légal (par exemple, les droits de vote ne prennent pas en compte ces actions).
    Bien cordialement,
    Benoît RIVIERE

  17. Bonjour,

    Merci pour cet excellent article.
    Par contre je n’arrive pas à ouvrir le fichier excel; je reçois un message d’erreur m’indiquant que le fichier est corrompu.

    Merci d’avance pour votre aide,

  18. Bonjour,

    C’était windows qui bloquait l’ouverture du fichier.

    Il suffisait d’aller dans les propriétés du fichier et de le débloquer.

    Merci,
    Yassine

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.