Discuss (0)

Print

RSS

Support » Ici

Méthodes de prévisions et formules Excel

Guillaume Saint-Jacques, 18-06-2008 (revu le 22-02-2010)

Ce guide explique les méthodes élémentaires de prévisions qui peuvent être mises en œuvre dans une feuille Microsoft Excel. Ce guide s'adresse aux professionnels qui souhaitent anticiper la demande client. La théorie est mise en pratique avec Microsoft Excel. Les développeurs trouveront des informations supplémentaires pour reproduire la théorie dans leur propre application.

Table des Matières [Cacher/Visualiser] Méthodes de prévisions et formules Excel    Les avantages de la prévision    Aidez-vous: libellés, commentaires et noms de fichier    Pour commencer : un exemple de prévisions simple avec la courbe de tendance linéaire    Réaliser vos prévisions avec l'Utilitaire d'analyse    Ensuite: l'exemple de la courbe exponentielle    Installer l'Utilitaire d'analyse (ATP)    Utiliser l'Utilitaire d'analyse (ATP)       ... dans une régression linéaire       ... et le lissage exponentiel    Comment choisir le bon modèle ?

Les avantages de la prévision

La prévision peut vous aider à prendre la bonne décision, et vous faire gagner/épargner de l'argent. Quelques exemples :

• Définir des meilleures stratégies de vente
  

Si un produit voit ses ventes baisser, la meilleure idée est peut-être d'arrêter de le produire. Mais peut-être pas : peut-être seules vos ventes sont en train de baisser, mais pas celles de vos concurrents ?

Dans ce cas, est-il possible de récupérer votre part de marché ?

Les méthodes de prévisions fournissent des réponses à ces questions - questions vitales pour votre activité.

• Optimiser le niveau de vos stocks
  

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

Comment ? A l'aide de prévisions!

Aidez-vous: libellés, commentaires et noms de fichier

A mesure que le temps passe et que vos données s'accumulent, il sera de plus en plus facile de vous perdre dans vos fichiers et de commettre des erreurs. La solution ? Rangez vos affaires : faites bon usage des libellés, des commentaires et des noms de fichier pour vous épargner de nombreux problèmes.

• Toujours libeller vos colonnes. Utiliser la première ligne de chaque colonne pour décrire les données qu'elle contient.
• Des données différentes, des colonnes différentes. Ne mettez pas des données différentes (par exemple, vos coûts, et vos ventes), dans la même colonne. Sinon, il est probable que vous vous perdiez dans vos feuilles Excel, vous pouvez rendre vos calculs et la gestion de vos donnéés bien plus difficiles.
• Nommer chacun de vos fichiers de manière compréhensible. Cela ne requière pas beaucoup d'efforts et peut accélérer considérablement les choses. Vos fichiers seront plus faciles à identifier, et à trouver en utilisant l'application Windows Search.
• Utiliser les commentaires. Même si vous ne travailler pas avec beaucoup de données, il est facile de se mélanger les pinceaux, en particulier si vous consultez vos données longtemps après les avoir créées. Excel offre une solution idéale : les commentaires.
  

The usefulness of comments

Cliquez droit sur la cellule à laquelle vous souhaitez ajouter un commentaire et sélectionner « insérer commentaire ».

Vous pouvez les utiliser :

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

Pour commencer : un exemple de prévisions simple avec la courbe de tendance linéaire

A vous de réaliser votre première prévision. Dans cette section, nous allons utiliser le fichier : Example1.xls. Vous pouvez le télécharger pour accomplir, par vous mêmes, les étapes de ce guide.

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.

Notre objectif : Estimer le nombre d'articles, de qualité correspondant à un coût unitaire de 150€, que nous vendrons.

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

Tout d'abord, il est toujours judicieux de créer un graphique avec Microsoft Excel, pour visualiser l'aspect de nos données. Il est alors très facile d'identifier des tendances au sein de vos chiffres.

Pour cela, sélectionner l'ensemble de vos données dans la feuille, puis: 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 (l'axe horizontal) et les ventes en ordonnée (l'axe vertical).

Maintenant, prenez le temps d'observer le graphique: la relation semble reposer sur un modèle linéaire.

Afin de se faire une meilleure idée de la relation entre le coût et les ventes, il suffit de sélectionner les points présents sur le graphique, clique droit, et Ajouter une courbe de tendance.

Creating a trendline

A présent, il nous faut choisir le modèle qui semble coïncider au mieux avec nos données. De nouveau, le graphique apporte la solution : dans le cas présent, les points sont pratiquement tous sur une ligne droite, le modèle "régression linéaire" est donc adéquat. Par la suite, nous allons utiliser d'autres modèles, un peu plus compliqués, mais parfois plus proches de la réalité, comme la régression exponentielle.

La courbe de tendance (une ligne droite) apparaît maintenant sur le graphique. Un autre clique droit nous permet d'afficher la formule exacte de la relation : y = 102.4x - 191.64.

Interprétation : Nombre d'unités vendues = 102,04 fois coût unitaire - 191,64.

Ainsi, 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.

Cependant, restez attentifs : Le logiciel est capable de trouver une relation entre les deux colonnes, même si, en réalité, elle est très faible! Voilà pourquoi vous devez absolument vérifier la qualité de vos résultats:

• Tout d'abord, observez votre graphique. Si vous remarquez que les points sont proches de la droite représentant la tendance, comme c'est le cas dans l'exemple ci-dessus, il est alors probable que le relation soit juste. Dans le cas inverse, si les points sont plutôt éloignés de la droite de régression et placées de manière aléatoire, soyez prudents : la corrélation est faible et la relation calculée ne vous aide probablement pas.
  

Les points sont éparpillés : aucune relation évidente et pas de prévision fiable

Les points sont presque alignés, et permettent de réaliser des prévisions plus fiables

• Après avoir observé le graphique, utilisez 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 il n'y a pas de tendance linéaire. Si elle est proche de 1, alors la corrélation est forte , ce qui est réconfortant, car cela vient attester la fiabilité de la relation mathématique que nous venons de trouver.
  

Il y a des manières plus subtiles pour évaluer le niveau de corrélation; nous reviendrons là-dessus un peu plus tard.

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. Il vous faut l'Utilitaire d'analyse !

Réaliser vos prévisions avec l'Utilitaire d'analyse

Avant de commencer, vérifiez que l' Excel ATP (Utilitaire d'analyse) soit installé. Veuillez vous référer à la section Installer l'Utilitaire d'analyse, pour en savoir plus.

Malheureusement des données de ventes si parfaites avec une relation linéaire idéale sont peu communes en réalité. Voyons ce qu'Excel peut offrir en cas de situations plus compliquées, avec des données plus complexes.

Ensuite: l'exemple de la courbe exponentielle

Comme vous pouvez l'imaginer, il n'est pas toujours très probable que vos données suivent un modèle linéaire. En fait, il est plus probable que vos données suivent un modèle exponentiel: de nombreux facteurs dans l'économie l'attestent: les capitaux placés à intérêts composés en sont un exemple omniprésent.

Voici comment obtenir une courbe de tendance exponentielle :

1) Observez d'abord vos données: créer sous Excel un graphique. Si les points forment une courbe de tendance exponentielle, elle devrait apparaître ainsi :

Courbe de tendance exponentielle parfaite

Ceci est l'exemple parfait. Bien sûr, les données ne vont jamais avoir exactement cet aspect. Mais si les points semblent suivre une courbe de ce type, il est alors judicieux de considérer la courbe de tendance exponentielle.

Utiliser les courbes de tendance

Comme dans l'exemple précédent, vous pouvez 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, vous pouvez faire tout cela directement avec le programme Utilitaire d'analyse. Placez vos données dans un onglet vierge de votre feuille Excel. Allez ensuite dans Outils => Analyse de données

Installer l'Utilitaire d'analyse (ATP)

L'utilitaire d'analyse est un programme complémentaire disponible lorsque vous installez Microsoft Excel, mais qui n'est pas toujours chargé par défaut. Pour le charger, procéder de la manière suivante:

1. Veillez à avoir le CD d'Office à portée de main. Vous devrez peut-être insérer le CD pour installer les fichiers de l'utilitaire d'analyse
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 d'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 l'option « Analyse de données ». C'est celle que vous utiliserez le plus.
   

Utiliser l'Utilitaire d'analyse (ATP)

... dans une régression linéaire

Revenons maintenant à notre exemple linéaire. Si vos données « paraissent » bonnes (voir l'illustration ci-dessus), vous pouvez utiliser l'utilitaire d'analyse pour obtenir une estimation directe de la formule fonctionnelle, sans passer par le procédé de « courbe exponentielle ».

A vous d'ouvrir une feuille Excel, puis dans le menu « Outils » choisissez « Utilitaire d'analyse ». Une fenêtre s'ouvre et vous invite à choisir le type d'analyse souhaité, sélectionnez « Régression linéaire ».

Vous devez maintenant renseigner deux champs: la « plage pour la variable Y » et « la plage pour les variables X ». La plage pour la variable Y correspond à ce que vous souhaitez estimer (par exemple, vos ventes), et la plage pour les variables X constituent les données explicatives (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 83, indiquez comme plage pour la variable Y « $B$3:$B$83 », et «$A$3:$A$83 » comme plage pour les variables explicatives. Cliquez ensuite sur OK.

Une nouvelle feuille est alors insérée contenant les « résultats de la régression ».

Les résultats de l'Utilitaire d'analyse, suivant une méthode des moindres carrés ordinaire

Les informations les plus utiles se trouvent en bas du rapport, dans la colonne intitulée, « Coefficients ». La constante (en anglais, intercept) et «la variable X»: le coefficient multiplicateur de X (ici, le coût unitaire). Ainsi, nous trouvons la même équation qu'en utilisant la méthode de la « courbe de tendance ».
Ventes = Constante + coefficientX * coût unitaire.
Ventes = -126 + 100 * coût unitaire

La feuille contient également un indicateur de la qualité de votre estimation: le coefficient de détermination « R² » (R Square). S'il est proche de 1, votre estimation est fiable, et l'équation est un modèle représentatif de vos données. S'il est plus proche de 0, alors l'estimation n'est pas valable et un autre type de régression pourrait faire l'affaire (ex: une régression exponentielle, voir ci-dessous).

Cette méthode est certainement plus rapide que la méthode de « courbe de tendance linéaire », vue précédemment. Cependant, elle est plus technique et moins visuelle. Donc, si vous l'utilisez, restez attentif à la valeur du « R² ».

... et le lissage exponentiel

Si l'estimation par le modèle linéaire ne convient pas (par exemple si le R² est trop faible, de l'ordre de 0,1), vous pouvez utiliser une méthode par régression exponentielle.

Démarrer l'utilitaire d'analyse. Une fois votre feuille Excel ouverte, allez dans le menu « Outils » et cliquez sur « Utilitaire d'analyse ». Une fenêtre apparaît et vous invite à sélectionner le type d'analyse à effectuer.

Cette fois-ci, sélectionnez : « lissage exponentiel ».

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

Comment choisir le bon modèle ?

Vous n'avez pas besoin d'essayer toutes les méthodes de prévisions pour déterminer celle qui convient le mieux. Compte tenu du nombre important de modèles de prévisions, ce serait pratiquement impossible de le faire sans système automatisé. Si vous souhaitez tester un maximum de méthodes de prévisions sur vos données, envoyez les à Lokad. Notre solution logiciel permet de tester un grand nombre de modèles de prévisions et de sélectionner ceux qui marchent le mieux avec vos données. Pour en savoir plus qu'est-ce que Lokad offre.

Cependant, n'oubliez pas de vous servir de vos yeux: faites un graphique (voir la première section), comparez les aux illustrations fournies dans ce guide, et choisissez le modèle qui correspond le mieux à vos données.