La remontée des informations comptables (chiffre d’affaires, résultats…), financières, commerciales (carnet de commande, satisfaction client…), etc. des filiales vers la holding s’opère généralement à l’aide de tableaux de bord mensuels standardisés ; cette opération est communément appelée reporting. L’agrégation de toutes ces données permet d’effectuer des comparaisons (entre filiales, par rapport au prévisionnel…) et de prendre des mesures correctrices le cas échéant. L’informatique décisionnelle revêt ici tout son sens.

Parmi les solutions les plus pertinentes, celle du VBA offre de sérieux atouts :

• Rapidité d’exécution : quelques secondes suffisent pour récupérer des données disséminées dans des dizaines de feuilles de calcul,
• Flexibilité : en cas de changement dans la présentation des tableaux de bord, il suffit de changer quelques lignes de code VBA et non pas toutes les formules de calcul de chacune des feuilles…
• Sécurité : lors de l’entrée d’une nouvelle filiale dans le groupe, elle est intégrée automatiquement dans le processus d’agrégation de données sans qu’il soit nécessaire de modifier quelque ligne de code que ce soit.

Exemple : remontée des tableaux de bord d’un groupe de distributeurs automobiles PEUGEOT

Afin d’illustrer notre propos, nous allons prendre le cas d’un groupe de distributeurs automobiles. Tous les mois, les concessionnaires doivent produire au constructeur, Peugeot, un tableau de bord standardisé « REFECO ». Ce tableau de bord (diffusé sous forme de fichier EXCEL) mentionne différentes informations commerciales par activité (vente de véhicules neufs (VN), de véhicules d’occasion (VO), activité atelier (APV)…).

Un groupe réunissant en son sein plusieurs concessionnaires de marque PEUGEOT pourra souhaiter « consolider » ces tableaux. C’est précisément cette « consolidation » que réalise l’application que je vous propose ci-après.

REFECO – Activité VN

Ce code source, écrit en VBA, reprend le chiffre d’affaires de l’activité VN (en K€ et en quantité) du tableau de bord de chaque société et effectue quelques calculs (comparaison N/N-1, prix de vente moyen au VN…).

La « consolidation » de l’activité VN apparaît dans le tableau reproduit ci-après (extrait) :

Code source de l’application :

'CentralREFECO
'=> Agrégation des données des REFECO dans un tableau "groupe"
'
'Programmé par Benoît-René RIVIERE, www.auditsi.eu, benoit@auditsi.eu (06/2011)
 
Option Explicit
 
'Déclaration des constantes & variables
'---Fichiers
Const ExtXLS = "xls"
Const NomDossierREFECO = "REFECO"
'---Général
Const NomOngletGal = "00a"
Const AdresseNomEntité = "C4"
'---Activité VN
Const NomOngletVN = "10a"
Const AdresseQtéVN_N = "K11"
Const AdresseQtéVN_N1 = "P11"
Const AdresseCAVN_N = "K13"
Const AdresseCAVN_N1 = "P13"
 
'Déclaration des variables
'---Chemin
Dim chemin As String
'---Objets dossier et fichier
Dim ObjFSO, ObjDossier, ObjFichier
'---Classeurs & onglets
Dim NomClasseurREFECOEnCours As String
Dim ClasseurREFECO As Workbook
Dim ClasseurAgrégat As Workbook
Dim NomEntité As String
Dim NumEntité As Integer
Dim NbFichiers As Integer
 
Function FormatCellule(ColCell As Integer)
  Select Case ColCell
    Case 1, 2, 4, 5, 7, 8
      FormatCellule = "# ###"
    Case 3, 6, 9
      FormatCellule = "#.00%"
  End Select
End Function
 
Sub TraitementREFECOEnCours()
  Dim ligneencours As Integer
  Dim i As Integer
  Dim s As String
  Dim c As Variant
  Dim c1 As Variant
  'Ouverture du REFECO
  Set ClasseurREFECO = Workbooks.Open(chemin & NomClasseurREFECOEnCours)
  'Agrégation des données
  ligneencours = 3
  With ClasseurAgrégat.Sheets(1)
    If NumEntité = 1 Then
      .Range("A1:L100").ClearContents
      For i = 1 To 9
        Select Case i
          Case 1
            s = "CA VN N"
          Case 2
            s = "CA VN N-1"
          Case 3
            s = "VAR° CA VN %"
          Case 4
            s = "Qté VN N"
          Case 5
            s = "Qté VN N-1"
          Case 6
            s = "VAR° Qté VN %"
          Case 7
            s = "CA moy au VN N"
          Case 8
            s = "CA moy au VN N-1"
          Case 9
            s = "VAR° CA moy"
        End Select
        .Cells(ligneencours, 4 + i - 1).Value = s
      Next i
    End If
    ligneencours = ligneencours + NumEntité
    NomEntité = ClasseurREFECO.Sheets(NomOngletGal).Range(AdresseNomEntité).Value
    .Cells(ligneencours, 1).Value = NomEntité
    For i = 1 To 9
      Select Case i
        Case 1
          c = ClasseurREFECO.Sheets(NomOngletVN).Range(AdresseCAVN_N).Value
        Case 2
          c = ClasseurREFECO.Sheets(NomOngletVN).Range(AdresseCAVN_N1).Value
        Case 3, 6, 9
          c1 = .Cells(ligneencours, 4 + i - 1 - 1).Value
          If c1 <> 0 Then
            c = (.Cells(ligneencours, 4 + i - 1 - 2).Value - c1) / c1
          Else: c = 0
          End If
        Case 4
          c = ClasseurREFECO.Sheets(NomOngletVN).Range(AdresseQtéVN_N).Value
        Case 5
          c = ClasseurREFECO.Sheets(NomOngletVN).Range(AdresseQtéVN_N1).Value
        Case 7, 8
          c1 = .Cells(ligneencours, 4 + i - 1 - 3).Value
          If c1 <> 0 Then
            c = .Cells(ligneencours, 4 + i - 1 - 6).Value / c1 * 1000
          Else: c = 0
          End If
      End Select
      With .Cells(ligneencours, 4 + i - 1)
        .NumberFormat = FormatCellule(i)
        .Value = c
      End With
    Next i
  End With
  'Fermeture du REFECO
  ClasseurREFECO.Close SaveChanges:=False
End Sub
 
Sub Totaux()
  Dim i As Integer
 
  For i = 1 To 9
    With ClasseurAgrégat.Sheets(1)
      If i = 1 Then .Cells(5 + NumEntité, 1).Value = "TOTAUX"
      With .Cells(5 + NumEntité, 4 + i - 1)
        .NumberFormat = FormatCellule(i)
        Select Case i
          Case 1, 2, 4, 5, 7, 8
            .FormulaLocal = "=somme(" & Chr(4 + 64 + i - 1) & "4:" & Chr(4 + 64 + i - 1) & 4 + NumEntité - 1 & ")"
          Case 3, 6, 9
            .FormulaLocal = "=(" & Chr(4 + 64 + i - 3) & 5 + NumEntité & "-" & Chr(4 + 64 + i - 2) & 5 + NumEntité & ")/" & Chr(4 + 64 + i - 2) & 5 + NumEntité
        End Select
      End With
    End With
  Next i
End Sub
 
Sub Exploitation_REFECO()
  NumEntité = 0
  Set ClasseurAgrégat = ActiveWorkbook
  chemin = ThisWorkbook.Path & "\" & NomDossierREFECO & "\"
  Set ObjFSO = CreateObject("Scripting.FileSystemObject")
  Set ObjDossier = ObjFSO.GetFolder(chemin)
  NbFichiers = ObjDossier.Files.Count
  If NbFichiers > 0 Then
    For Each ObjFichier In ObjDossier.Files
      If (InStr(1, ObjFichier.Name, ExtXLS, 1) > 0) Then
        NomClasseurREFECOEnCours = ObjFichier.Name
        NumEntité = NumEntité + 1
        TraitementREFECOEnCours
      End If
    Next
    Totaux
  End If
End Sub

Les grands groupes disposent d’outils permettant de centraliser l’information de manière automatisée. Par contre, les groupes de PME ne disposent généralement pas de ce genre d’outils. L’agrégation des données des filiales est fréquemment effectuée à la main ou à l’aide de nombreuses formules liant des feuilles de calcul, générant des risques d’erreurs notamment en cas de création de nouvelles filiales…

The following two tabs change content below.

• Bio
• Derniers articles

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

Derniers articles parBenoît RIVIERE (voir tous)

• VBA/SQL vs Power Query : deux solutions complémentaires - mercredi 2 octobre 2024
• L’IA dans les cabinets comptables : cas concrets - jeudi 26 septembre 2024
• EXCEL : insérer une image ou un logo dans une cellule - lundi 16 septembre 2024
• Lancer l’exécution d’un script Python à partir d’une macro VBA - lundi 9 septembre 2024
• Open Data : quoi de neuf ? - lundi 2 septembre 2024