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) :

 

Excel formation - Calculer des pourcentages de variation sur Excel - 01

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 :

Excel formation - Calculer des pourcentages de variation sur Excel - 02

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

 

Excel formation - Calculer des pourcentages de variation sur Excel - 03

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 :

Excel formation - Calculer des pourcentages de variation sur Excel - 04

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 :

Excel formation - Calculer des pourcentages de variation sur Excel - 05

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 :

Excel formation - Calculer des pourcentages de variation sur Excel - 06

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

Excel formation - Calculer des pourcentages de variation sur Excel - 07

Cela étant fait, nous pouvons également calculer les variations des années suivantes (2017 et 2018) :

Excel formation - Calculer des pourcentages de variation sur Excel - 08

 

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)

Excel formation - Calculer des pourcentages de variation sur Excel - 09

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%)

 

Excel formation - Calculer des pourcentages de variation sur Excel - 10

 

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%)

Excel formation - Calculer des pourcentages de variation sur Excel - 11

 

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 :

Excel formation - Calculer des pourcentages de variation sur Excel - 12

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]) :

Excel formation - Calculer des pourcentages de variation sur Excel - 13

 

=$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.

Excel formation - Calculer des pourcentages de variation sur Excel - 14

À 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

Excel formation - Calculer des pourcentages de variation sur Excel - 15

  • Puis nous saisissons la formule du calcul de prévision des ventes :

Excel formation - Calculer des pourcentages de variation sur Excel - 16

 

(À valider une fois de plus en appuyant sur les touches [Ctrl] + [Entrée])

 

=D12*(1+D$5)

 

 



Articles qui pourraient vous intéresser

Comment créer un sommaire automatique et généré en 1 clic sur Excel

Comment empêcher la suppression ou la modification des cellules sur Excel

Comment créer un formulaire de saisie intégré dans une feuille de calcul sur Excel

Calculer la racine n-ième d’un nombre sur Excel

Commentaires

Il n'y a aucun commentaire...

Poster un commentaire

Pseudo
Commentaire

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.