Comment calculer des variations en pourcentage (et déduire des prévisions) sur Excel
Aujourd’hui nous allons voir comment faire pour calculer des pourcentages de variation sur Excel. Nous verrons ainsi quelle formule nous devons utiliser, et comment mettre en forme les cellules pour obtenir le format adéquat. Ensuite nous effectuerons l’exercice inverse, c’est-à-dire que nous élaborerons un prévisionnel d’activité en fonction de taux de variations estimatifs.
Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier en suivant le lien disponible ci-dessous :
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
1. Présentation du cas d’étude
Pour illustrer cet article, nous allons analyser les progressions de Chiffre d’affaires HT d’une entreprise (fictive) :
Nous disposons pour ce faire du montant de ces ventes réalisées au cours des quatre dernières années.
Nous allons calculer les pourcentages de progressions des ventes réalisées au cours de ces trois dernières années, puis nous nous en dégagerons trois tendances, afin d’estimer le montant de ces ventes pour les trois années à venir.
2. Calcul d’une variation en pourcentage
La formule à appliquer pour déterminer quel est le pourcentage de variation entre deux valeurs est la suivante :
Valeur d’arrivée – Valeur de départ x 100
Valeur de départ
Ainsi, dans notre exemple, pour calculer la variation entre le montant des ventes réalisées en 2015 et celui réalisé en 2016 :
Ventes 2016 – Ventes 2015 x 100
Ventes 2015
Et donc la formule à saisir est la suivante :
Soit :
=(B9-B8)/B8*100
Où :
- B8 : correspond au montant des ventes réalisées en 2015
- B9 : montant des ventes réalisées en 2016
La progression des ventes réalisées entre ces deux années est donc de +6,66%
S’agissant d’un pourcentage, il convient d’appliquer le formatage correspondant, en cliquant par exemple sur le bouton Pourcentage disponible dans l’onglet Accueil du ruban :
Une fois le formatage appliqué, nous pouvons constater que la variation affichée n’est plus correcte. En effet, ce dernier ne se contente pas de changer la manière dont le chiffre est affiché, il complète également la formule pour nous, en divisant le résultat par 100.
Nous devons donc modifier la formule afin de retirer le « *100 » qui se trouve à la fin :
Pour que la variation soit évidente, il est également possible d’afficher un symbole plus (« + ») devant une augmentation. Pour cela, nous effectuons un clic-droit sur la cellule afin de choisir Format de cellule :
Puis depuis la catégorie Personnalisée, nous entrons la mise en forme personnalisée suivante : « +0,00%;[rouge]-0,00%; » qui permet :
- D’afficher le symbole plus (« + ») lorsqu’il s’agit d’une hausse,
- D’afficher le symbole mois (« - ») lorsqu’il s’agit d’une baisse (et d’afficher le résultat en rouge, afin d’accentuer l’impact négatif de la variation),
- Ne rien afficher lorsque la variation est nulle
Cela étant fait, nous pouvons également calculer les variations des années suivantes (2017 et 2018) :
3. Mise en place des trois scénarios de progression
A présent, nous allons pouvoir mettre en place un prévisionnel pour les trois années à venir (de 2019 à 2021), en fonction des trois hypothèses suivantes :
- Hypothèse de progression moyenne : les trois années à venir vont connaître une progression similaire à la moyenne des progressions des trois années passées,
- Hypothèse de progression optimiste : les trois années à venir vont connaître une progression similaire à la moyenne des progressions des trois années passées majorée de 20%,
- Hypothèse de progression optimiste : les trois années à venir vont connaître une progression similaire à la moyenne des progressions des trois années passées minorée de 20%
Nous allons calculer les taux de progression à appliquer à chacun de ces trois scénarios dans les cellules correspondantes :
Hypothèse de progression moyenne, en cellule D5 :
=MOYENNE(C9:C11)
Nous appliquons ici simplement la formule MOYENNE() sur les taux de progression constatés au cours des trois dernières années. Cette formule MOYENNE() permet d’obtenir la moyenne simple (c’est-à-dire la somme des données passées en argument, divisée par le nombre d’éléments).
Hypothèse de progression optimiste, en cellule E5 :
Pour calculer une progression positive à une valeur de départ (c’est-à-dire une hausse), il faut ajouter à cette même valeur la part de la progression en pourcentage à appliquer. Ainsi, pour appliquer une progression de 20% sur la valeur de la progression moyenne des trois dernières années (déjà calculée en cellule D5 pour la première hypothèse), il faut utiliser la formule :
Valeur d’arrivée = Valeur de départ + Valeur de départ * Taux de variation
=D5+D5*20%
Note : Il est possible de simplifier cette formule en recourant au principe de factorisation :
Valeur d’arrivée = Valeur de départ * (1 + Taux de variation)
=D5*(1+20%)
Hypothèse de progression pessimiste, en cellule F5 :
Enfin pour la dernière hypothèse ; lorsque nous souhaitons appliquer une variation négative (c’est-à-dire une baisse) à une valeur de départ, il va cette fois-ci être nécessaire de retrancher de cette valeur de départ la part en pourcentage représentant cette baisse :
Valeur d’arrivée = Valeur de départ - Valeur de départ * Taux de variation
=D5-D5*20%
Que nous pouvons également simplifier en factorisant de la manière suivante :
Valeur d’arrivée = Valeur de départ * (1 - Taux de variation)
=D5*(1-20%)
4. Prévisions des ventes annuelles
Maintenant que nous venons de déterminer les taux de progressions annuels à appliquer pour chacun des trois scénarios envisagés, nous allons pouvoir calculer les prévisions de CA HT pour les trois prochaines années en fonction des hypothèses retenues.
La formule permettant d’appliquer un pourcentage de progression est celle que nous venons de voir à l’instant, à savoir :
Valeur d’arrivée = Valeur de départ + Valeur de départ * Taux de variation
Ou la version factorisée de cette formule, que nous allons choisir d’utiliser ici (car elle ne nécessite qu’un seul appel de référence à une autre cellule) :
Valeur d’arrivée = Valeur de départ * (1 + Taux de variation)
La saisie de ces formules se fait en deux temps : dans un premier temps, nous allons saisir les variations de la première ligne (l’année 2019) car l’année précédente se trouve en cellule B11 pour chacun des trois scénarios. Juste après, nous calculerons les estimations de CA pour les deux années suivantes.
Nous commençons par sélectionner les trois cellules de l’année 2019 :
Puis nous saisissons la formule suivante, en cellule D12 (attention de bien valider la formule en appuyant sur la touche [Ctrl] du clavier en même que la touche [Entrée]) :
=$B$11*(1+D$5)
Comme vous pouvez le constater, la référence à la cellule B11 est une référence absolue, car celle-ci contient la valeur de départ (les ventes de l’année 2018) qui est commune aux trois scénarios possibles (c’est pourquoi les coordonnées doivent rester inchangées).
En revanche, la référence à la cellule D5 est une référence mixte, ce qui permet de bloquer la ligne 5 lorsque la formule sera étendue sur d’autres cellules, tout en conservant la possibilité de décaler la colonne. Pour en savoir plus sur les différents types de références (relative, absolue, mixte) et sur l’utilisation du symbole dollar (« $ »), je vous invite à consulter l’article suivant : ici.
À présent, nous allons pouvoir calculer les prévisions de ventes pour les deux années restantes en procédant de la même manière (seule la formule change légèrement) :
- Nous sélectionnons les six cellules correspondantes
- Puis nous saisissons la formule du calcul de prévision des ventes :
(À valider une fois de plus en appuyant sur les touches [Ctrl] + [Entrée])
=D12*(1+D$5)