Le Starter Kit Avancé des Sorties Excel 6

[StephaneP]

Article 1 : Workflow complet et présentation du contenu 

Article 2 : Les basiques de l’outil Sortie de données

Article 3 : Les basiques des outils de Reporting

Article 4 : Les bases d'utilisation des modèles Excel

Article 5 : Diffusion en masse d'un même template

Article 6: Exemple pratique de génération dynamique avancée de P&L (Vous êtes ici)

Cet article porte sur des explications détaillées du Starter Kit Avancé des sorties Excel. Il se focalise sur l’exemple le plus complexe permettant de générer formatage dynamique, formules Excel dynamiques, copier/coller de ces formules dynamiques tout en assurant une maintenance centralisée et simplifiée. Il s'agit d'une solution à un vrai problème plus qu'un exemple théorique. Vous verrez, l'exemple est vraiment puissant et impressionnant !

Alteryx only !!

Le workflow du Starter Kit se trouve dans le 1er article : LIEN ICI

14. Formatage dynamique via Reporting + génération dynamique de formules à coller dans l'output formaté

Spécifiquement pour cet exemple, pour bien en montrer la valeur et la puissance, on ne va pas se focaliser exclusivement sur comment il est construit. On va repartir des problématiques quotidiennes et du besoin qui a généré sa mise en place. C’est un cas réel que j’ai rencontré de multiples fois chez mes clients, contrôle de gestion ou autre, ayant besoin de consolider des chiffres saisis manuellement.

EXPLICATION DU BESOIN ET DES CONTAINTES ASSOCIEES

Imaginons que je suis un responsable du contrôle de gestion en central. Je dois envoyer chaque mois à mes différentes entités un template de saisie Excel des prévisions (Forecast) de clôture du trimestre. Ils ne doivent saisir que les mois futurs (en orange). Pour les aider, j’extrait pour eux leurs données historiques. Ils peuvent ainsi contrôler les potentielles incohérences de saisie. On doit donc bien avoir du Excel car ils vont saisir dans ces templates.

Pour aider à la saisie, je différencie en bleu l’historique (à ne pas saisir) et en orange les parties Forecast (à saisir). De même, en clair sont les comptes de détail à

saisir et en plus foncé les niveaux d’agrégation intermédiaire à ne pas saisir.

Pour augmenter la qualité de la remontée des données je souhaite les aider à contrôler leur saisie.  Je n’envoie pas que la liste de détail des comptes mais aussi les niveaux d’agrégations. Ils maitrisent les grandes masses de leur business et peuvent ainsi contrôler à la volée lors de la saisie s’ils ont commis une erreur ou une faute de frappe.

L’impact pour moi est important car cela implique qu’au moins pour la partie orange/forecast je dois avoir des formules Excel pour les niveaux d’agrégation intermédiaires. Ils doivent voir en temps réel leur agrégation. Ce sont donc forcément des formules Excel qui doivent se trouver dans mon classeur.

Exemple : On voit ici que le compte 6026-Emballage est la somme des comptes de détail en saisie 60261, 60265 et 60267. Je souhaite donc une formule Excel (=K13+K14+K15) qui somme ces 3 lignes et qui doit être présente pour les 12 mois mais surtout octobre, novembre et décembre qui seront saisis en direct par mes utilisateurs. Ils verront donc en direct le résultat de leurs saisies et pourront les contrôler.

Le principe de sommage des comptes reste le même (somme simple) mais cette formule Excel change donc d’une colonne/mois à l’autre et d’un niveau agrégé à l’autre. è 1 cellule de total= 1 formule différente.

Pour garantir une bonne saisie sécurisée et cohérente je leur affiche leur historique de donnée sur l’année et aussi je ne leur affiche que les comptes (=indicateurs) qui les concernent. En effet chaque entité n’a pas la même taille/ activité/ périmètre… et donc elles ne doivent pas saisir les mêmes infos. D’expérience si je mets à chacune la liste de tous les indicateurs de tous le monde cela complexifie le processus et génère des saisies sur les mauvais indicateurs. Pour sécuriser cette saisie je souhaite donc que chacun ne reçoive pour son template que les comptes qui le concerne. Donc potentiellement pour mes 30 entités je peux avoir 30 templates distincts.

Financis qui est ma plus grosse entité doit saisir sur tous les comptes

Isartis, qui est plus petite, doit saisir sur moins de comptes

Cette customisation qui améliore leur expérience et limite les anomalies à un gros impact pour moi. Cela signifie que d’un template à l’autre:

• le nombre de lignes varie
• l’alimentation de l’historique varie car je n’ai pas le même nombre de lignes
• les formules de calculs Excel varient car je n’ai pas le même nombre de ligne à sommer
• le positionnement vertical de ces formules varie (car le nombre de ligne de détail varie et donc le positionnement des niveaux agrégés)
• le formatage varie (nombre de ligne différent et positionnement des niveaux agrégés différents)

Avec évidement la contrainte que d’un Forecast trimestriel à l’autre le nombre de mois d’historique ou de saisie varie lui aussi.

Ca va ? Vous gérez ? Facile ?

No stress, on va mettre tous cela sous Alteryx et ça va rouler. Sachez qu’il ne faut que 32 objets pour réaliser cette merveille de dynamisme et de maintenabilité !

Administrons centralement les saisies par entité

Le 1^er point de maintenabilité est de centraliser :

• la définition de quelle entité à le droit de saisir quels compte
• la définition de la hiérarchie et des regroupements de notre Plan de Compte.

Note : C’est quoi un Plan de compte ? (=Profit & Loss en anglais).

Pour ceux qui ne sont pas dans le domaine de la Finance, le Plan de Compte est une hiérarchie d’indicateur de gestion agrégés suivant des regroupements (les couts, les dépenses, la marge…). Pour vous aider à vous projeter, voyez cela comme une hiérarchie d’indicateurs métiers, organisée comme tout axe d’analyse qui se doit.

Pour passer d’un niveau fin à un niveau agrégé on fait simplement la somme des niveaux plus détaillés. Pas d’autre formule dans cet exemple.

On voit ici justement que notre compte 6026 – Emballages est de niveau 3 => Il est la somme des comptes de génération 4 qui sont sous lui. 60261, 60265, 60267.

Lui-même s’agrège dans son compte de génération supérieure 602-Achats stockés.

On voit aussi que dans la colonne Niveau Fin il est à 0. Cela signifie qu’il ne doit pas être saisissable, contrairement à ses enfant 60261… qui eux sont à 1 dans cette colonne. C’est donc eux qui devront être saisie, et 6026 sera le résultat de leur somme.

Les colonnes MAPPING PRODUIT, une par entité (Financis ou Isartis) permettent de définir pour chaque entité quels comptes devront être affichés et donc autorisés à la saisie.

1 si l’entité devra le voir/ saisir, 0 sinon.

Avec ce simple onglet on peut donc avoir une vision centralisée de ce que l’on souhaite et assurer en 1 seul endroit nos 2 points précédents :

• modification de notre hiérarchie de compte
• modification de qui à le droit de voir/saisir quoi

Encore faut-il pouvoir le lire dynamiquement pour en générer automatiquement les templates. Mais Alteryx s’en charge très bien. 😊

Note : dans la réalité cette table peut même être générée dynamiquement à partir des sources de donnée de type EPM (Enterprise Performance Management) ou ERP qui stockent la définition de ces plans de comptes et des accès par entité. On alors une boucle complète totalement automatisée sans intervention manuelle. La maintenance dans le système source impacte alors directement les reportings utilisateurs sans intervention manuelle intermédiaire.

Centralisons les données

La 2^ème source d’information est notre historique. Suivant les cas elle peut se trouver dans votre applicatif, EPM, ERP… et permet de récupérer les données des mois précédents pour les afficher dans les classeurs Excel (zone bleu).

On a donc toujours une base brute avec des lignes de donnée. Dans l’exemple on a ce format.

Dans ce type de source on a généralement que le détail, pas toujours les niveaux agrégés.

Calcul des niveaux agrégés

Il faut donc reconstruire dans Excel les formules d’agrégations afin de pouvoir les afficher pour nos utilisateurs. C’est le précédent fichier d’administration qui porte ces règles avec l’ordre des comptes et la notion de génération.

Sur le papier la règle est simple :

Un compte de génération N est la somme des comptes de génération N+1 qui se trouvent « au-dessus ».

Exemple 1 : Calcul du compte 6026 – Emballages pour le mois de Janvier

Exemple 2 : Calcul du compte 602 – Achats stockés pour le mois de Janvier

On voit donc qu’avec ce classeur on a bien tout à notre disposition pour automatiser la génération dynamique de nos N templates. Une simple mise à jour de cette table (nouveau compte, nouvelle entité, nouvelle autorisation de saisie) permettra de regénérer des templates cohérents en masse en 1 clic.

Il n’y plus qu’à automatiser via Alteryx en 32 petits objets. 😊

PRINCIPE GENERAL DANS ALTERYX

Dans cette partie, ne sont détaillées que les grands principes qui restent valables quelque soient les cas d’usage. Donc même si ce cas du contrôle de gestion n’est pas le vôtre vous y trouverez matière à appliquer à vos besoins.

1. Génération dynamique des templates

Via notre table d’administration on a la structure de notre plan de compte et leur génération.

On connaît aussi les mois du Forecast (Octobre à décembre). On peut donc gérer tout ce qui est color coding.

Comme d’une entité à l’autre le nombre de lignes varie, si on veut que cela puisse être fait dynamiquement il n’y a que l’outil Table qui s’applique. Il permettra aussi de splitter les données par produit.

Les principales étapes pour préparer les données sont:

• Lire la table d’administration (ce qui garantie le dynamisme)
• Ajouter un ordre sur les lignes pour pouvoir reconstruire le plan de compte dans le bon ordre après les transformations
• Basculer des informations par entité (1 par colonne) en ligne pour pouvoir filtrer
• Filtrer pour ne conserver que les lignes à afficher/saisir par entité
• Ajouter un label « Détail » ou « Total » pour simplifier le color coding
• Fusionner les données de détail avec les données de total et de leurs caractéristiques (permettra ultérieurement les calculs des formules Excel)
• Tri final pour réaligner ceci par Entité (et plus tard calculer correctement les formule)

On obtient bien un dataset brut avec uniquement les bonnes lignes pour chaque entité.

Il nous reste à assurer le formatage et générer le fichier Excel + onglets.

Les principales étapes pour le formatage final sont :

• Via Table, color coder dynamiquement chaque ligne en fonction de son mois et de sa génération
• Ajouter le nom de l’entité
• Définir les entités comme critère d’onglet
• Produire le classeur Excel

On obtient bien un classeur Excel avec un onglet par Entité, un nombre de ligne distinct, un color coding prenant en compte le niveau des totaux dans la hiérarchie.

2. Génération des formules Excel à copier ainsi que de leur zone Excel de collage

Lorsque l’on souhaite coller des formules dans Excel il y a 2 informations importantes à fournir à Alteryx.

1. La zone Excel ou placer cette formule
2. La formule

Exemple dans notre cas, voici ce que l’on cherche à obtenir :

Pour le compte 6026 – Emballages on doit coller dans la zone B11 :M11 pour chaque mois les formules indiquées dans la zone rouge.

On a donc ici pour chaque ligne de compte intermédiaire la zone où coller les formules et les formules. Ceci doit être généré par Alteryx pour que l’outil Sortie de données sache où coller ces informations.

Cette partie est ultra spécifique à ce cas d’usage, vous pouvez vous en inspirer pour l’adapter au votre mais l’idée est de trouver le moyen de rendre dynamique l’obtention de ces 2 informations via votre workflow.

Dans cet exemple un objet Saisie de texte en dur initie précise le coin supérieur de la zone où coller les formules.

Ce sera entre B3 en haut à gauche et MX en bas à droite. X variera d’une entité à l’autre puisqu’elles n’ont pas le même nombre de lignes. Cela permettra aux règles de gestion qui génèrent les formules de savoir d’où ils partent et jusqu’où ils vont.

Pourquoi B3 ? Car dans le template le 1^er compte du Plan de compte se trouve en B3. Et quel que soit le template ce sera toujours cette cellule. Si vous changez votre format vous pouvez mettre à jour cette référence de manière centralisée.

Notes :

• Dans cet exemple seul le nombre de ligne est variable (car on a toujours 12mois). Il n’y a donc pas de calcul dynamique sur les colonnes.
• Pour une autre maquette le point de départ peut être distinct et mis à jour ici. Pour certains cas d’usages point de départ et de fin peuvent être aussi calculés.

Pour déterminer les zones où coller les formules on partira toujours de cette référence en ajoutant le nombre de ligne par rapport à celle-ci.

Le champs Tile_Num ajouté via Alteryx nous donne l’ordre croissant des comptes par rapport à ce point de départ du Plan de compte. Il suffit alors d’automatiser ce calcul.

Exemple : Pour le compte 6022 – Fournitures Consommables en orange

Le 1^er compte se trouvant en B3, si je veux coller une formule sur le total intermédiaire 6022 – Fournitures  qui est en 5^ème ligne je peux donc en déduire qu’il est en B3 + 5 lignes = B8 et ainsi de suite pour tous les totaux. La zone de fin sera forcément M8 puisqu’on a toujours 12 mois.

Le numéro de ligne à ajouter pour un compte (ici 5) correspond à son Tile_Num -1 = 6 -1 = 5

Donc 2021 – Matières consommables sera à coller sur B8:M8

En ce qui concerne la détermination de la formule c’est plus complexe.

Un fois qu’on a filtré et trié les données par entité, pour chacune d’elle on va passer 5 étapes itératives successives de préparation pour obtenir la formule Excel de chaque sous total.

En rouge nous suivrons le compte 602 – Achats stockées de génération 2 qui est la somme des 3 comptes de génération 3 en vert :

• 6021 – Matières consommables (B5)
• 6022 – Fournitures Consommables (B8)
• 6026 – Emballages (B11)

Pour le mois de janvier, la cellule B12 devra donc recevoir la formule =B5+B8+B11

1. A partir de l’ordre du compte et de la zone B3 initiale on détermine le numéro de ligne Excel de chaque compte (on prend le numéro d’ordre du compte et on lui rajoute 3-1=2)
2. On rajoute B devant pour avoir la cellule associée (3 => B3)
3. Avec une colonne par génération on ne garde cette cellule que pour les comptes qui sont de la génération correspondante. On voit l’arborescence qui se recrée. On voit visuellement que B12 de génération 2 est bien la somme de B11, B8 et B5 de la génération 3.

4. Pour chaque génération on somme les cellules de génération supérieure qui lui sont rattachées. On utilise l’objet Formule à plusieurs lignes pour répliquer la valeur tant qu’on n'a pas trouvé un autre membre de même génération qui s’agrège dans le même compte (ici B12). Lorsque c’est le cas, on concatène les 2 avec un « + » au milieu. On voit qu’on passe de B5 à B5+B8 puis B5+B8+B11. On s’arrête à B11 car B13 le compte de génération 3 suivant ne s’agrège pas dans B12.

5. On consolide en une seule colonne en ne gardant par génération que la formule stockée dans la colonne de la génération correspondante. On rajoute le = devant. Les valeurs saisies sont mises à NULL.

On voit sur l’image ci-dessous que le compte

• 602 – Achats Stockés est un compte de génération 2, on va donc chercher sa formule finale dans la colonne Gen2 Calc è =B5+B8+B11
• 60 – Achats est un compte de génération 1, on va donc chercher sa formule finale dans la colonne Gen1 Calc è = B3 +B12+B14
• 60212 – Matière D est une compte de génération 4 et saisissable il portera donc la valeur NULL
• 604 – Achats d’études et prestations est un compte de génération 2, mais il est saisissable il portera donc la valeur NULL

On a donc déterminé pour janvier, pour chaque compte sa cellule de copie et la formule à y placer.

On va dupliquer ces formules pour les 12 mois. Ici cela est réalisé en dur car on n’a pas besoin de dynamisme. Sur le même principe que les lignes on aurait pu rendre dynamique la génération de

ces colonnes en fonction du point de départ B3.

Ce qui permet via l’outil Formule de générer le chemin d’écriture final pour le Sortie de Donnée

Mais alors c’est fini ?

Et après la 1^ère passe via l’outil Rendu qui nous a permis de générer dynamiquement le formatage on vient compléter les onglets avec ces formules Excel elles aussi dynamique.

Alors ?

non ?

Quelle valeur ?

Si on revient à la valeur générée elle est souvent conséquente sur ces situations car le nombre de taches automatisées est nombreuse et réduit drastiquement les anomalies classiques. On gagne donc et en temps de production et en temps de contrôle. On parle ici de plusieurs jours/h de travail souvent sur des cycles de clôture à forte tension ou toute minute gagnée et cruciale.

La maintenance est elle aussi extrêmement simplifiée car tout est automatisé et se regénère en 1 clic et quelques secondes. Souvenez vous que la mise à jour du classeur d’administration est souvent elle aussi dynamiquement mise à jour à partir des sources fonctionnelles de type ERP ou EPM ce qui accélère et sécurise encore plus la chaine complète.

Note : sur ce cas d’usage, est souvent associé un workflow de contrôle et de tracking qui scanne les templates une fois renseignés pour identifier et mettre en avant les erreurs de saisies. On sait directement si il y a des anomalies et on peut suivre qui à remonter quoi. Là aussi on gagne du temps et de la qualité.

Conclusion

Merci à vous d’avoir suivi cette série jusqu’au bout. J’espère qu’elle vous inspirera. Prenez le temps de progresser étape par étape mais gardez en tête là ou vous pourrez allez une fois à l’aise. N’oubliez pas qu’il y a aussi le pdf associé qui résume les principaux principes des 14 exemples.

Cet article étant le résultat d’un loooong travail, s’il vous a plu, mon plus beau cadeau serait que vous le diffusiez pour qu’il aide d’autres personnes à aller encore plus loin dans leurs usages.

 Enjoy !!