Méthodes de prévision et formules Excel

Par Guillaume Saint-Jacques, 18-06-2008 (dernière version, 22-02-2010)

Ce guide explique les méthodes élémentaires de prévisions pouvant être mises en œuvre dans une feuille Microsoft Excel. Il s'adresse aux professionnels qui souhaitent anticiper la demande client. La théorie y est illustrée avec Microsoft Excel. Les développeurs logiciels souhaitant reproduire la théorie dans leur propre application trouveront ici les informations nécessaires.

Les avantages de la prévision    Simplifiez-vous la vie : étiquettes, commentaires et noms de fichiers    Pour commencer : un exemple simple de prévisions avec les courbes de tendance    Réaliser vos prévisions avec l'utilitaire d'analyse, Analysis Toolpak    Pour aller plus loin : l'exemple de la courbe exponentielle    Installer l'utilitaire d'analyse, Analysis Toolpak (ATP)    Utiliser Analysis Toolpak (ATP)       ... dans une régression linéaire       ... dans un lissage exponentiel    Comment savoir quel modèle choisir ?

Les avantages de la prévision

La prévision peut vous aider à prendre les bonnes décisions et vous faire gagner/économiser de l'argent. En voici un exemple :

• Optimiser le niveau de vos stocks
  

Le temps, c'est de l'argent. L'espace, c'est de l'argent. Pour économiser, il vous faut utiliser tous les moyens à votre disposition pour réduire vos stocks - sans pour autant souffrir de ruptures de stocks, bien sûr.

Comment ? A l’aide de prévisions !

Simplifiez-vous la vie : étiquettes, commentaires et noms de fichiers

A mesure que le temps passera et que vos données s'accumuleront, il sera de plus en plus facile de vous perdre dans vos fichiers et de commettre des erreurs. La solution ? Soyez organisé : faites bon usage des étiquettes et des commentaires et nommez correctement vos fichiers pour vous épargner de nombreux problèmes.

• Toujours étiqueter les 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 données différentes (par exemple, vos coûts et vos ventes) dans la même colonne, faute de quoi vous vous perdrez très probablement dans vos feuilles Excel et vous rendrez vos calculs et la gestion de vos données bien plus difficiles.
• Donner à chaque fichier un nom compréhensible. Cela ne requiert pas beaucoup d'efforts et peut accélérer considérablement les choses. Vos fichiers seront plus faciles à identifier et à trouver en utilisant la fonction Recherche de Windows.
• Utiliser les commentaires.
  

Même si vous ne travaillez pas avec de gros volumes de données, il est facile de se perdre, en particulier si vous consultez vos données longtemps après les avoir créées. Excel offre une solution idéale : les commentaires.

L’utilité des commentaires

Faites un clic droit sur la case à laquelle vous souhaitez ajouter un commentaire et sélectionnez « insérer commentaire ».

Vous pouvez les utiliser :

• Pour expliquer le contenu d'une case (ex : le coût unitaire selon l'estimation de M. Durand...)
• Pour avertir les utilisateurs futurs (ex : ce calcul me semble erroné... )
  

Pour commencer : un exemple simple de prévisions avec les courbes de tendance

Réalisons à présent notre premier lot de prévisions. Dans cette section, nous allons utiliser le fichier Example1.xls. Vous pouvez le télécharger pour refaire par vous-même les étapes de ce guide. Ces données servent uniquement d’exemple.

Nos données : dans la première colonne, les données concernant le coût unitaire (cost per unit) de produits similaires (le coût unitaire reflète la qualité du produit). Dans la seconde colonne, la quantité de produits vendus.

Ce que nous cherchons à connaître : Si l'on met en vente un autre produit, d'une qualité correspondant à un coût unitaire de 150€, combien d'unités peut-on espérer en vendre ?

Comment faire : Cette étape est assez simple. Il suffit de trouver une simple relation mathématique entre le coût unitaire et les ventes, et d'utiliser cette relation pour réaliser les prévisions.

Tout d'abord, avec Excel, il est toujours judicieux de créer un graphique pour visualiser les données. Il devient alors très facile d'identifier des tendances au premier coup d'oeil.

Pour cela, sélectionnez l'ensemble de vos données dans la feuille, puis utilisez Insertion > Graphique, et choisissez le graphique en nuage de points (XY). Nous voulons estimer le niveau de ventes en fonction de la qualité du produit. Il faut donc indiquer le coût unitaire en abscisse (axe horizontal) et les ventes en ordonnée (axe vertical).

A présent, prenez le temps d'observer le graphique : la relation semble augmenter de façon linéaire.

Afin de se faire une idée plus précise de la forme de cette relation, il suffit de faire un clic droit sur le graphique et de sélectionner l'option Ajouter une courbe de tendance.

Créer une courbe de tendance

A présent, il nous faut choisir le modèle qui semble coïncider le mieux avec nos données. De nouveau, c'est le simple fait de visualiser le graphique qui nous aide. Dans le cas présent, les points sont pratiquement tous sur une ligne droite, par conséquent nous utilisons le modèle "régression linéaire". Par la suite, nous allons utiliser d'autres modèles - un peu plus complexes, mais souvent plus proches de la réalité - tels que la régression exponentielle.

Notre courbe de tendance apparaît maintenant sur le graphique. Un autre clic droit nous permet d'afficher la formule exacte de la relation : y = 102.4x - 191.64.

Il faut l'interpréter de la façon suivante : Nombre d'unités vendues = 102,04 fois le coût unitaire - 191,64.

Par conséquent, si nous décidons de produire à un coût de 150€ par unité, nous pouvons nous attendre à vendre 102,04*150 - 191,64 = 15 168 unités.

Une courbe de tendance linéaire

Nous venons de réaliser notre première prévision.

Toutefois, il faut rester prudents : le logiciel est toujours capable de trouver une relation entre les deux colonnes, même si en réalité elle est très faible! Voilà pourquoi il est nécessaire de vérifier la robustesse. Voici une manière rapide de le faire :

• Tout d'abord, observez votre graphique. Si vous remarquez que les points sont proches de la courbe de tendance, comme c'est le cas dans l'exemple ci-dessus, il est alors probable que la relation soit robuste. A l'inverse, si les points sont plutôt éloignés et diposés de façon assez aléatoire, soyez prudents : la corrélation est faible et la relation calculée est peu fiable.
  

Les points sont éparpillés : il n'y a aucune relation évidente et les prévisions ne sont pas fiables

Les points "font sens" et permettent de réaliser des prévisions plus fiables

• Après avoir observé 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 la suivante : il n'y a tout simplement pas de tendance réelle. Si elle est proche de 1, alors la corrélation est forte. Ce dernier cas est un bon indice et atteste de la fiabilité de la relation mathématique trouvée.
  

Il y a des manières plus subtiles pour évaluer le niveau de corrélation ; nous y reviendrons ultérieurement.

Bien sûr, ces dernières étapes peuvent être automatisées : il est inutile de noter la relation et d'utiliser votre calculette pour faire le calcul. Ce dont vous avez besoin, c'est de l'utilitaire d'analyse, Analysis Toolpak !

Réaliser vos prévisions avec l'utilitaire d'analyse, Analysis Toolpak

Avant de commencer, nous vous suggérons de vérifier que Excel ATP est bien installé. Pour plus d'informations, vous pouvez vous référer à la section Installer Analysis Toolpak.

Malheureusement des données de ventes comme celles présentées dans l'exemple, parfaites, avec une relation linéaire idéale sont peu communes en réalité. Voyons ce qu'Excel peut offrir pour des cas plus complexes, avec des données plus compliquées.

Pour aller plus loin : l'exemple de la courbe exponentielle

Comme vous pouvez l'imaginer, il est peu probable que vos données suivent un modèle linéaire. En réalité, pour diverses raisons, il y a fort à parier qu'elles suivent plutôt un modèle exponentiel. Dans l'économie, de nombreux comportements suivent des équations exponentielles (les calculs d'intérêts composés en sont un exemple classique).

Voici comment obtenir une courbe de tendance exponentielle :

1) Créez un simple graphique et observez d'abord vos données. Si les points suivent une courbe de tendance exponentielle, elle devrait ressembler à ceci :

Forme exponentielle parfaite

Ceci est l’exemple parfait. Bien sûr, les données n'auront jamais exactement cet aspect. Mais si les points semblent suivre approximativement une répartition de ce type, il est alors judicieux de choisir la courbe de tendance exponentielle.

Utiliser les courbes de tendance

Comme dans l'exemple précédent, vous pouvez toujours créer un graphique, demander une courbe de tendance, et choisir « exponentielle » au lieu de linéaire.

Ensuite, utilisez l'équation affichée sur le graphique, comme d'habitude.

2) Heureusement, une fois encore, vous pouvez faire tout cela directement avec Analysis Toolpak : placez vos données dans une feuille Excel vierge, et allez ensuite dans Outils => Analyse de données

Installer l'utilitaire d'analyse, Analysis Toolpak (ATP)

ATP est un module complémentaire qui vient avec Microsoft Excel, mais qui n'est pas toujours installé par défaut. Pour l'installer, vous pouvez procéder de la manière suivante :

1. Veillez à avoir votre CD Office à portée de main. Excel peut vous demander de l'insérer pour installer les fichiers d'ATP.
2. Ouvrez une feuille Excel et allez dans le Menu Outils, puis sélectionnez Compléments. Cochez la première case de la fenêtre, nommée « Utilitaire d'analyse ».
3. Insérez votre CD Office si le logiciel vous le demande.
4. Et voilà ! Vous remarquez que votre menu « Outils » inclut à présent beaucoup plus de fonctions, y compris une option « Analyse de données ». C'est celle que vous serez amené à utiliser le plus.
   

Utiliser Analysis Toolpak (ATP)

... dans une régression linéaire

Revenons maintenant à notre exemple linéaire. Si vos données « paraissent » bonnes (cf. illustration ci-dessus), vous pouvez utiliser ATP pour obtenir une estimation directe de la forme fonctionnelle, sans passer par le procédé de « courbe de tendance ».

Ouvrez une feuille Excel, puis dans le menu « Outils » choisissez « Analyse de données ». Une fenêtre s'ouvre et vous invite à choisir le type d'analyse que vous voulez effectuer. Sélectionnez « Régression linéaire ».

Vous devez maintenant renseigner deux champs : la « plage pour la variable Y » et « la plage pour la variable X ». La plage pour la variable Y correspond à ce que vous souhaitez estimer (par exemple, vos ventes), et la plage pour la variable X contient les données qui, selon vous, peuvent expliquer vos ventes (dans le cas présent, votre coût unitaire). Dans notre exemple (example1.xls), nos données de vente se trouvent dans la colonne B, de la ligne 3 à la ligne 90; vous devez donc indiquer comme plage pour la variable Y « $B$3:$B$90 », et «$A$3:$A$90 » pour la variable X. Cliquez ensuite sur OK.

Une nouvelle feuille apparaît alors, contenant contenant les « résultats de la régression ».

Les résultats d'ATP, dans le cas d'une méthode des moindres carrés ordinaires

Le résultat le plus important est situé au bas du rapport dans la colonne intitulée, « Coefficients ». Le point d'intersection avec les ordonnées (en anglais, intercept) est la constante, et «la variable X» est le coefficient de X (ici, le coût unitaire). Ainsi, nous retrouvons la même équation qu'en utilisant la fonction de la « courbe de tendance ». Ventes = point d'ntersection + coefficientX * coût unitaire. Ventes = -126 + 100 * coût unitaire

La feuille contient également un indicateur de la qualité de votre estimation : le « R² ». S'il est proche de 1, votre estimation est fiable, ce qui signifie que l'équation trouvée est un modèle tout à fait représentatif de vos données. S'il est plus proche de 0, alors l'estimation n'est pas valable et vous devriez probablement vous orienter vers un autre type de régression (ex: une régression exponentielle, cf. ci-dessous).

Cette méthode est probablement plus rapide que la méthode de « courbe de tendance », vue précédemment. Cependant, elle est aussi un peu plus technique et beaucoup moins visuelle. Par conséquent, si vous ne voulez pas prendre le temps de vérifier et d'observer vos données, vérifiez au moins la valeur du « R² ».

... dans un lissage exponentiel

Si l'estimation linéaire ne convient pas (par exemple si le R² est trop faible, de l'ordre de 0,1), il sera sans doute préférable d'utiliser une méthode de lissage exponentiel.

Démarrez ATP comme d'habitude ; ouvrez une feuille Excel, puis dans le menu « Outils » choisissez « Analyse de données ». Une fenêtre s'ouvre et vous invite à choisir le type d'analyse que vous voulez effectuer.

Dans notre configuration exponentielle, il nous faut sélectionner « exponentiel ».

Remarquez qu'Excel ne requiert cette fois qu'une seule plage d'entrée. Sélectionnez la colonne contenant les données que vous souhaitez prévoir (dans le cas présent : le coût unitaire), et choisissez une "constante de lissage".

Comment savoir quel modèle choisir ?

Notez que vous n'avez pas besoin d'essayer chaque méthode de prévision pour déterminer celle qui convient le mieux. Compte tenu du nombre important de méthodes disponibles, on ne peut fonctionner qu'en automatisant le processus. Si vous souhaitez appliquer tous les modèles à vos données, vous pouvez les envoyer à Lokad. Notre solution logicielle permet de « tester » un grand nombre de modèles et de sélectionner ceux qui s'appliquent le mieux aux données de votre entreprise (pour en savoir plus sur ce que Lokad peut vous offrir, cliquez ici.