Discuter (0)

Imprimer

RSS

Lokad News

Support » Ici

Méthodes de prévisions et formules Excel

Guillaume Saint-Jacques, 18-06-2008

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 2003. 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: les prévisions avec la courbe de tendance linéaire    Ensuite: la courbe de tendance exponentielle    Réaliser vos prévisions avec l'Utilitaire d'analyse       L'utilitaire d'analyse et la régression linéaire       ... et le lissage exponentiel    Comment choisir le bon modèle?    Préparez vos données

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 inventaires
  

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

L'utilité des commentaires

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: les prévisions 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 (Sold units).

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.

Ajouter une courbe de tendance

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. Dans l'onglet Options, de la fenêtre Ajouter une courbe de tendance, cocher la case Afficher l'équation sur le graphique La formule de la relation mathématique est: y = 102,04x - 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 COEFFICIENT.CORRELATION. Dans notre exemple, la fonction serait: COEFFICIENT.CORRELATION(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.

Vous pensez peut-être qu'il faut être particulièrement chanceux pour tomber sur des données de ventes aussi parfaites qui produisent une relation linéaire à la fois simple et jolie. Vous avez raison. Il nous reste à voir les solutions qu'offre Excel pour faire face à des situations plus compliquées, avec des données plus compliquées!

Ensuite: la courbe de tendance 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.

Voci comment obtenir une courbe de tendance exponentielle :

• 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, ajouter une courbe de tendance et enfin choisir le type de régression souhaité. Cette fois-ci, sélectionnez la régression exponentielle au lieu de celle linéaire.

Ensuite, utilisez l'équation affichée sur le graphique, pour calculer vos prévisions de ventes comme effectué précédemment.

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 le menu Outils, et cliquez sur Utilitaire d'analyse.

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

Avant de commencer, vérifiez que l’Utilitaire d'analyse est chargé.

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. Allez dans le menu outils, et cliquez sur macros complémentaires.
2. Dans la zone Macros complémentaires disponibles, cochez la case en regard de Utilitaire d'analyse, puis cliquez sur OK.
   
   Conseil - Si l'Utilitaire d'analyse n'est pas répertorié, cliquez sur Parcourir pour le trouver.
3. Si un message vous invite à installer l'utilitaire d'analyse, cliquer sur OK.
4. Dans la barre de menus, cliquez sur Outils, la commande Utilitaire d'analyse a été ajoutée au menu Outils.
   

L'utilitaire d'analyse et la régression linéaire

Maintenant, reprenons les données de notre premier exemple (la courbe de tendance linéaire). Les données « semblent » suivre une courbe linéaire et l'utilitaire d'analyse va nous permettre de calculer une estimation des ventes futures, sans avoir à utiliser la méthode précédente de «courbe de tendance».

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.

Résultats de la régression linéaire réalisée avec l'utilitaire d'analyse Excel

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 sur les produits Lokad.

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.

Préparez vos données

L'Addin Lokad pour Excel offre de nombreuses fonctionnalités qui rendent la manipulation de vos données bien plus simple.