Une courbe en Z permet d'analyser le chiffre d'affaire (CA) d'un exercice de travail d'une entreprise.
Une courbe en Z a pour abscisse les mois de l'exercice et en ordonnée le montant de CA. Une courbe en Z est en réalité composée de trois courbes distinctes :
- La courbe du CA mensuel
- La courbe de cumul du CA
- La courbe du CA annuel mobile
Le CA mensuel est le chiffre d'affaire constaté pour chaque mois de l'exercice, ce n'est pas une valeur calculée.
Le cumul du CA est, pour chaque mois, la somme des CA des précédents mois de l'exercice à laquelle est ajoutée le CA du mois. Le cumul du CA et le montant du CA du mois de janvier sont identiques.
Le CA mobile est, pour chaque mois, la somme des CA des onze mois précédents à laquelle est ajoutée le CA du mois. Le CA mobile du mois de mars 2011 est la somme des CA des mois de mars 2010 à février 2011.
Les trois courbes représentées dans le même graphique forment un "Z" d'où provient le nom de courbe en Z.
On cherche à obtenir une telle courbe dans Excel pour un nombre d'exercices indéfini.
L'idée générale
On part des données constatées du problème, à savoir les montants de CA pour chaque mois de chaque exercice.
Sur la base de ces données, on calcule, pour chaque mois de chaque exercice, le cumul du CA ainsi que le CA mobile.
Sur la base des données constatées et calculées, on construit un tableau croisé dynamique présentant pour chaque exercice les résultats mensuels.
Enfin, le tableau croisé dynamique obtenu est utilisé pour générer le graphique des courbes en Z.
Admettons, pour l'exemple, que nous disposons de toutes les données concernant les exercices 2009, 2010 et 2011.
Les données constatées
On consigne les données constatées dans une feuille Excel contenant trois colonnes :
- Colonne A : Exercice
- Colonne B : Mois
- Colonne C : CA
Dans notre exemple, la feuille de calcul résultante comporte 37 lignes numérotées de 1 à 37 : 1 ligne d'en-tête + 12 lignes pour l'exercice 2009 + 12 lignes pour l'exercice 2010 + 12 lignes pour l'exercice 2011.
Attention : les données saisies doivent être ordonnées chronologiquement pour que le tableau croisé dynamique puisse se construire sans difficulté. Si ce n'est pas le cas, il faut ordonner ces données à l'aide des outils proposés par Excel.
Les données calculées
On ajoute deux colonnes à la feuille Excel des données constatées :
- Colonne D : CA annuel mobile
- Colonne E : Cumul CA
Calcul du CA annuel mobile
On ne peut calculer le CA annuel mobile d'un mois que si l'on dispose des CA des onze mois précédents. Dans notre exemple, on ne peut calculer le CA mobile qu'à partir du mois de janvier 2010 ; la première formule de calcul du CA mobile se situe donc dans la cellule de coordonnées D14 :
D14=SOMME(C3:C14)
On étire ensuite cette formule sur l'ensemble des lignes de la feuille Excel (D15=SOMME(C4:C15), ...).
Le calcul du CA annuel mobile pour chaque mois des exercices 2010 et 2011 est ainsi effectué.
Calcul du cumul CA
Il est possible de calculer le cumul CA de tous les mois des exercices pour lesquels on dispose des CA des mois de janvier à décembre. Dans notre exemple, on peut calculer les cumuls CA de tous les mois des exercices 2009, 2010 et 2011.
Le cumul CA d'un mois est égal au cumul CA du mois précédent auquel il faut ajouter le CA du mois sauf pour le mois de janvier de chaque exercice pour lequel "on repart à 0".
Prenons le problème pas à pas.
Pour une cellule contenant le cumul CA d'un mois quelconque, admettons E12 correspondant au cumul CA de novembre 2009, on peut traduire "est égal au cumul CA du mois précédent auquel on ajoute le CA du mois" par la formule
E12=E11+C12Mais cette formule ne tient pas compte du "redépart à 0" en début de chaque exercice. De plus, cette formule échoue sur la ligne de janvier 2009, la cellule E1 n'étant pas une valeur mais une en-tête de colonne. On corrige la formule qui prend ainsi en compte les mises à 0 et qui est applicable à la cellule E2 correspondant au cumul CA du mois de janvier 2009
E2=SI(B2="janvier";0+C2;E1+C2)
On étire ensuite cette formule sur l'ensemble des lignes de la feuille Excel (E2=SI(B3="janvier";0+C3;E2+C3), ...).
Le calcul du cumul CA pour chaque mois des exercices 2009, 2010 et 2011 est ainsi effectué.
Le tableau croisé dynamique
Le tableau croisé dynamique est utilisé comme intermédiaire entre les données (constatées et calculées) et le graphique des courbes en Z.
Pour générer le tableau croisé dynamique, on utilise les colonnes A:E de notre feuille de données.
- La colonne Exercice est utilisée comme filtre du rapport
- La colonne Mois est utilisée comme étiquette de ligne
- Les colonnes CA, CA annuel mobile et Cumul CA sont utilisées comme valeurs
Utiliser la colonne Exercice comme filtre du rapport permet de filtrer les données pour afficher un ou plusieurs exercices.
Pour les valeurs du tableau croisé dynamique, Excel applique une fonction d'agrégation (somme, moyenne, max, nombre ou compte, ...) sur les données.
Lorsque le tableau n'affiche les données d'un seul exercice, certaines fonctions d'agrégation (somme, moyenne, ...) peuvent donner des résultats similaires étant donné qu'à chaque cellule de valeur du tableau croisé dynamique correspond une et une seule données dans notre feuille de données (l'utilisation de la fonction nombre ou compte, qui sert à retourner le nombre de données utilisées, renvoi alors toujours 1).
Il faut donc trouver la fonction d'agrégation adéquate lorsque le tableau est utilisé pour afficher les résultats de plusieurs exercices. La fonction qui parait être la mieux adaptée est la moyenne pour les trois types de valeur
- La moyenne des CA de chaque mois pour l'ensemble des exercices
- La moyenne des CA annuels mobiles de chaque mois pour l'ensemble des exercices
- La moyenne des cumuls CA de chaque mois pour l'ensemble des exercices
On retire ensuite les totaux de colonnes du tableau croisé dynamique car, si la moyenne générale des CA et la moyenne des CA annuels mobiles peuvent avoir un sens, la moyenne générale des cumuls CA n'en a aucun.
Le graphique
Le graphique des courbes en Z se génère ensuite sans grande difficulté, presque automatiquement. En sélectionnant le tableau croisé dynamique et en insérant un graphique en courbe, le graphique proposé par Excel ne nécessite pas d'ajustement particulier.
Par la suite
Des données constatées peuvent être ajoutées à la feuille des données. Il faut alors étirer les formules de calcul sur les nouvelles lignes créées.
Si le tableau croisé dynamique est bien basé sur la plage de colonne A:D et non pas sur la plage de cellules A1:D37, il suffit d'actualiser le tableau pour que celui-ci prenne en compte les nouvelles données.
Quelque-soit les manipulations apportées au tableau croisé dynamique (mise à jour des valeurs, filtrage d'un exercice), le graphique correspondant (la courbe en Z) est mise à jour automatiquement.
Aucun commentaire:
Enregistrer un commentaire