Méthodes et formules de prévision dans Excel
Ce guide explique les méthodes de prévision élémentaires qui peuvent être facilement appliquées dans les feuilles de calcul Microsoft Excel. Ce guide s’adresse aux gestionnaires et aux cadres qui ont besoin d’anticiper la demande des clients. La théorie est illustrée avec Microsoft Excel. Des notes avancées sont disponibles pour les développeurs de logiciels qui souhaitent reproduire la théorie dans une application personnalisée.
Avantages de la prévision
La prévision peut vous aider à prendre les bonnes décisions et à gagner/économiser de l’argent. Voici un exemple.
- Dimensionnez vos stocks de manière optimale
Le temps, c’est de l’argent. L’espace, c’est de l’argent. Donc ce que vous voulez faire, c’est utiliser tous les moyens à votre disposition pour réduire vos stocks - sans subir de ruptures de stock, bien sûr.
Comment ? En prévoyant !
Comment simplifier les choses : étiquettes, commentaires, noms de fichiers
Au fil du temps, à mesure que vos données s’accumulent, vous avez de plus en plus de chances de vous tromper ; de faire des erreurs. La solution ? Ne soyez pas désordonné : une bonne utilisation des étiquettes, des commentaires et une bonne dénomination de vos fichiers peuvent vous éviter beaucoup de problèmes.
- Étiquetez toujours vos colonnes. Utilisez la première ligne de chaque colonne pour décrire les données qu’elle contient.
- Données différentes, colonnes différentes. Ne mettez pas des chiffres différents (par exemple, vos coûts et vos ventes) dans la même colonne. Il est extrêmement probable que vous vous trompiez, et cela rend les calculs et la manipulation des données plus difficiles.
- Donnez à chaque fichier un nom clairement compréhensible. Cela demande peu d’efforts et accélère les choses. Cela les rend faciles à identifier visuellement et plus faciles à trouver à l’aide de la fonction de recherche de Windows.
- Utilisez des commentaires.
Même si vous ne travaillez généralement pas avec une grande quantité de données, il est très facile de se tromper. Cela s’applique particulièrement si vous revenez sur les données que vous avez créées il y a longtemps. Excel a une excellente solution à offrir : les commentaires.
Faites simplement un clic droit sur la cellule sur laquelle vous souhaitez ajouter un commentaire, puis sélectionnez « insérer un commentaire ».
Vous pouvez les utiliser :
- pour expliquer le contenu d’une cellule (par exemple, coût unitaire selon les estimations de M. Doe)
- pour laisser des avertissements aux futurs utilisateurs de la feuille (par exemple, J’ai un doute sur ce calcul…)
Pour commencer : un exemple simple de prévision en utilisant des tendances
Visualisation de vos données
Passons maintenant à notre première prévision. Dans cette partie, nous utiliserons ce fichier : Exemple1.xls. Pour reproduire les étapes par vous-même, vous pouvez télécharger le fichier. Ces données ne servent qu’à titre d’exemple.
Nos données : Dans la première colonne, des données sur les coûts unitaires de produits similaires (le coût unitaire reflète la qualité du produit). Dans la deuxième colonne, des données sur les quantités vendues.
Ce que nous voulons savoir : Si nous vendons un autre produit, d’une qualité correspondant à un coût de 150 $/unité, combien d’unités pouvons-nous espérer vendre ?
Comment y parvenir : Ici, c’est assez simple. Nous voulons trouver une relation mathématique simple entre le coût unitaire et les ventes, puis utiliser cette relation pour faire notre prévision.
Tout d’abord, il est toujours utile de créer un graphique dans Excel, afin de jeter un coup d’œil aux données. Vos yeux sont d’excellents outils qui peuvent vous aider à identifier les tendances en quelques secondes.
Pour ce faire, nous sélectionnons nos données, puis utilisons Insertion > Graphique, et choisissons l’option XY (Nuage de points). Nous voulons estimer les ventes en fonction de la qualité, nous plaçons donc le coût unitaire sur l’axe horizontal et les ventes sur l’axe vertical.
Maintenant, nous nous arrêtons quelques secondes et examinons attentivement ce que nous voyons : la relation semble être croissante et linéaire.
Pour avoir une idée de la forme exacte de la relation, nous faisons un clic droit sur le graphique, puis sélectionnons l’option “Courbe de tendance”.
Maintenant, nous devons sélectionner la relation qui semble “correspondre” (c’est-à-dire décrire au mieux) nos données. Encore une fois, nous utilisons nos yeux : dans ce cas, les points sont presque alignés, nous utilisons donc le réglage “linéaire”. Plus tard, nous utiliserons d’autres réglages - plus complexes, mais souvent plus réalistes - comme “exponentiel”.
Notre courbe de tendance est maintenant affichée sur le graphique. Un autre clic droit nous permet d’afficher la forme exacte de la relation : y = 102,4x - 191,64.
Comprendre : Nombre d’unités vendues = 102,4 fois le coût unitaire - 191,64.
Ainsi, si nous décidons de produire à un coût unitaire de 150 $, nous pouvons nous attendre à vendre 102,4 * 150 - 191,64 = 15168 unités.
Nous venons de réaliser notre première prévision avec succès.
Cependant, faites attention : le logiciel est toujours capable de trouver une relation entre les deux colonnes, même si cette relation est en réalité très faible ! Par conséquent, une vérification de la robustesse est nécessaire. Voici comment vous pouvez le faire rapidement :
- Tout d’abord, jetez toujours un coup d’œil au graphique. Si vous constatez que les points sont étroitement situés près de la courbe de tendance, comme c’est le cas dans notre exemple ci-dessus, il y a de fortes chances que la relation soit robuste. Cependant, si les points semblent être situés presque au hasard et sont en général assez éloignés de la courbe de tendance, alors vous devez être prudent : la corrélation est faible et la relation estimée ne doit pas être aveuglément fiable.
- Après avoir examiné le graphique, vous pouvez utiliser la fonction CORREL. Dans notre exemple, la fonction serait : CORREL(A2:A83,B2:B83). Si le résultat est proche de 0, alors la corrélation est faible et la conclusion est : il n’y a tout simplement aucune tendance réelle. Si le résultat est proche de 1, alors la corrélation est forte. Cette dernière est utile car elle augmente le pouvoir explicatif de la relation que vous avez trouvée.
Il existe des moyens plus subtils de s’assurer que la corrélation est élevée ; nous y reviendrons plus tard.
Bien sûr, ces dernières étapes peuvent être automatisées : vous n’avez pas besoin de noter la relation et d’utiliser votre calculatrice de poche pour effectuer le calcul. Vous avez besoin de l’Analysis Toolpak !
Prévisions à l’aide de l’Analysis Toolpak
Avant de continuer, vous devez vérifier si l’Excel ATP (Analysis Toolpak) est installé. Consultez la section Installation de l’Analysis Toolpak pour plus d’informations.
Malheureusement, des données de vente parfaites avec une relation linéaire aussi agréable et simple sont assez rares dans la vie réelle. Jetons un coup d’œil à ce qu’Excel propose pour des situations plus compliquées, avec des données plus complexes.
Aller plus loin : l’exemple de l’ajustement exponentiel
Comme vous pouvez l’imaginer, un tel modèle linéaire de vos données n’est pas toujours probable. En fait, il y a de nombreuses raisons de croire qu’il devrait suivre un modèle exponentiel. De nombreux comportements dans l’économie sont régis par des équations exponentielles (par exemple, les calculs de capitalisation des intérêts en sont un exemple classique).
Voici comment effectuer un ajustement exponentiel :
- Regardez vos données. Tracez un graphique simple et regardez-le simplement. S’ils suivent une évolution exponentielle, ils devraient ressembler à ceci :
C’est le cas parfait. Bien sûr, les données ne ressembleront jamais exactement à cela. Mais si les points semblent suivre approximativement cette répartition, cela devrait vous encourager à envisager un ajustement exponentiel.
Comme dans l’exemple précédent, vous pouvez toujours tracer un graphique de vos données, demander une tendance et choisir « exponentielle » au lieu de linéaire. Ensuite, récupérez l’équation affichée, comme d’habitude.
- Heureusement, vous pouvez également faire tout cela directement en utilisant l’outil d’analyse : placez toutes vos données dans une feuille Excel vide et allez dans Outils => Analyse des données
Installation de l’outil d’analyse (ATP)
L’ATP est un complément fourni avec Microsoft Excel, mais il n’est pas toujours installé par défaut. Pour l’installer, vous pouvez procéder comme suit :
- Assurez-vous d’avoir votre CD Office avec vous. Excel peut vous demander d’insérer le CD pour installer les fichiers ATP.
- Ouvrez une feuille Excel, allez dans le menu Outils, puis sélectionnez Compléments. Cochez la première case de la fenêtre, intitulée « 2. Outil d’analyse ».
- Insérez votre CD Office si le logiciel vous le demande.
- C’est tout ! Remarquez que votre menu « Outils » inclut maintenant de nombreuses fonctionnalités supplémentaires, notamment une option « Analyse des données ». C’est celle que nous utiliserons le plus souvent.
Utilisation de l’outil d’analyse (ATP)
… dans un cadre linéaire
Maintenant, revenons à notre exemple linéaire. Si vos données semblent bonnes (voir l’illustration ci-dessus), vous pouvez utiliser l’ATP pour obtenir une estimation directe de la forme fonctionnelle, sans passer par le processus de « tendance ».
Ouvrez votre feuille de données, puis ouvrez le menu « Outils » et sélectionnez « Analyse des données ». Une fenêtre apparaît, vous demandant quel type d’analyse vous souhaitez effectuer. Sélectionnez « régression » pour les paramètres linéaires.
Maintenant, vous devez donner à Excel deux arguments : une « plage Y » et une « plage X ». La plage Y indique ce que vous voulez estimer (c’est-à-dire vos ventes), et la plage X contient les données que vous pensez pouvoir expliquer vos ventes (ici, votre coût unitaire). Dans notre exemple (voir example1.xls), nos données de ventes se trouvent dans la colonne B, de la ligne 3 à la ligne 90, vous devez donc mettre « $B$3:$B$90 » comme plage Y, et « $A$3:$A$90 » comme plage X. Lorsque vous avez terminé, cliquez sur « OK ».
Une nouvelle feuille apparaît, contenant les « résultats de régression ».
Le résultat le plus important se trouve dans la colonne « Coefficients » en bas de la feuille. L’intercept est la constante, et le coefficient de la « variable X » est le coefficient de X (ici, votre coût unitaire). Ainsi, nous trouvons la même équation que celle trouvée à l’aide de la fonction « trendline ». Ventes = Intercept + CoefficientX * coût unitaireVentes = -126 + 100 * coût unitaire
Cette feuille contient également un nombre utile qui vous donne des informations sur la qualité de votre estimation : le « R carré ». S’il est proche de 1, alors votre estimation est bonne, ce qui signifie que l’équation que vous avez trouvée est une assez bonne représentation de vos données. S’il est proche de 0, alors l’estimation n’est pas bonne, et vous devriez probablement essayer un autre type d’ajustement (voir l’ajustement exponentiel ci-dessous).
Cette méthode est probablement plus rapide que les techniques de « trendline ». Cependant, elle est un peu plus technique et beaucoup moins visuelle. Donc, si vous ne voulez pas vous embêter à tracer et à examiner vos données, assurez-vous de vérifier au moins la valeur du « R carré ».
… en utilisant un ajustement exponentiel
Si l’estimation linéaire ne fonctionne pas bien (par exemple, si vous obtenez un faible R carré, c’est-à-dire 0,1), vous voudrez peut-être utiliser un ajustement exponentiel.
Lancez l’outil d’analyse, comme d’habitude : ouvrez votre feuille de données, puis ouvrez le menu « Outils » et sélectionnez « Analyse des données ». Une fenêtre apparaît, vous demandant quel type d’analyse vous souhaitez effectuer.
Dans notre configuration exponentielle, ce que nous voulons sélectionner est « exponentiel ».
Remarquez qu’Excel ne vous demande qu’une seule plage d’entrée. Sélectionnez la colonne qui contient les données que vous souhaitez prévoir (c’est-à-dire le coût unitaire), et choisissez un « facteur de lissage ».
Comment savoir quel modèle choisir ?
Notez que vous n’avez pas besoin d’essayer chaque méthode d’estimation pour trouver celle qui fonctionne le mieux pour vous. Cela ne peut être réalisé que par automatisation, car il existe un si grand nombre de méthodes disponibles. Si vous souhaitez que tous les modèles soient comparés à vos données, vous pouvez envisager de les envoyer à Lokad. Nous disposons d’un puissant système informatique qui « teste » tous les modèles et ne sélectionne que ceux qui fonctionnent le mieux avec les données de votre entreprise.