Faire des prévisions avec Excel

Vous souhaitez faire des prévisions avec Excel ? Que ce soit pour anticiper et faire un prévisionnel de vos ventes, de vos chiffres d’affaires… Nous verrons deux méthodes pour réussir à faire des prévisions dans le logiciel.

Il est souvent très utile de réaliser des prévisionnels. En effet, cela peut vous servir pour présenter des données chiffrées si vous créez votre entreprise ou pour piloter votre activité. Ci-dessous, nous verrons deux méthodes différentes qui vont vous permettre de réussir à faire des prévisions avec Excel.

Faire des prévisions avec Excel

La première se fera avec la courbe de tendance logarithmique et l’application du modèle logarithmique sur plusieurs semaines. Dit comme cela, on peut avoir l’impression que c’est compliqué, mais en réalité c’est très simple, vous verrez.

La seconde méthode se fera avec la fonction « PREVISION » d’Excel et le graphique prévisionnel. Vous pourrez ainsi choisir la méthode qui vous convient le mieux pour l’appliquer à vos prévisions.

Comment faire des prévisions avec Excel ?

Dans cette partie, nous allons découvrir deux méthodes pour faire des prévisions avec Excel. Celles-ci vous permettront de faire des représentations graphiques de vos prévisions de chiffre d’affaires (ventes…), mais aussi de compléter votre tableau avec les données prévisionnelles.

Les deux méthodes sont très simples à mettre en œuvre. Selon les circonstances, vous pouvez utiliser celle qui vous semble la plus appropriée. Le tableau que vous trouvez ci-dessous en image sera celui utilisé.

Tableau de données dans Excel

Il s’agit d’un tableau résumant 30 semaines de chiffre d’affaires. L’objectif sera donc de faire des prévisions avec Excel pour atteindre la semaine 40. Nous ferons donc des prévisions de la semaine 31 à la semaine 40.

Méthode 1 – La courbe de tendance logarithmique

La première méthode que nous allons découvrir pour faire des prévisions avec Excel est avec la courbe de tendance logarithmique. Le mot peut sembler compliquer pour ceux qui n’apprécient pas les mathématiques, mais soyez rassurés, Excel va tout faire pour nous.

La première chose à faire est de prendre notre tableur Excel. Dans celui-ci, vous devez sélectionner la plage de données sur laquelle vous souhaitez faire des prévisions. Dans le cas ci-dessous, il faut sélectionner les 30 premières semaines.

On va maintenant créer un graphique en nuage de point pour représenter nos données. La capture ci-dessous vous guide dans cette étape. Après avoir sélectionné les données, rendez-vous dans la partie « Insérer ». Dans la section des graphiques, vous devez choisir « Nuages de points ».

Créer nuages de points

Vous n’êtes pas obligé de le faire, mais pour une meilleure lecture du graphique vous pouvez modifier les limites de l’axe comme sur la capture ci-dessous.

Modifier les limites de l’axe

1.1 Créer la courbe de tendance

Maintenant, nous allons créer notre courbe de tendance logarithmique. Rendez-vous dans la partie « Création » des outils de graphique. Ensuite, accédez à « Ajouter un élément graphique » => « Courbe de tendance » => « Autres options de la courbe de tendance ».

Créer une courbe de tendance

Vous devez modifier les paramètres de la courbe de tendance. Dans « Options de courbe de tendance », cocher « Logarithmique », et tout en bas cochez les cases « Afficher l’équation sur le graphique » et « Afficher le coefficient de détermination (R²) sur le graphique ». Vous aurez donc votre courbe qui devrait changer légèrement de forme et une équation va apparaître. Sous cette dernière vous trouverez la valeur de « R² ». Nous allons voir à quoi cela correspond.

Les paramètres de la courbe de tendance

La valeur « R² » correspond au coefficient de détermination. Ce dernier doit-être le plus proche possible de 1 pour que le modèle logarithmique soit appliquer dans les meilleures conditions. Autrement dit, pour qu’on puisse faire des prévisions avec Excel les plus fiables possible. Si nos données « chiffre d’affaires » font des « yoyos » trop importants entre chaque semaine, cela fera baisser le coefficient de détermination ce qui rend les prévisions moins fiables.

Ce qui est logique, car cela signifie que vos chiffres d’affaires varient beaucoup d’une semaine à l’autre, donc c’est difficile d’anticiper la prochaine variation. Un peu comme un cours de bourse. Ici, « R² » est à 0,97, donc très proche de 1. C’est idéal pour appliquer le modèle logarithmique.

Pour faire des prévisions avec Excel, il faudra donc utiliser la formule suivante :

Chiffre d’affaires = 840,87 * LN(Semaine)+12197

En appliquant celle-ci dans la cellule « B32 » qui correspond à la semaine 31, la prévision de chiffre d’affaires apparaît. On peut faire glisser la formule vers le bas jusqu’à la semaine 40 pour obtenir des prévisions avec Excel sur 10 semaines. Sur la capture en dessous vous pouvez retrouver la formule appliquée.

Calculer des prévisions avec Excel

Méthode 2 – Utiliser la fonction PREVISION dans Excel

Une deuxième méthode pour faire des prévisions avec Excel est d’utiliser la fonction PREVISION à travers un graphique. Celle-ci est vraiment intéressante, car elle permet de créer la courbe de prévision et deux limites. Une limite haute et une basse. Ainsi, les résultats devraient logiquement se situer entre ces deux limites.

De plus la mise en place de ce graphique va automatiquement remplir la suite de notre tableau. Actuellement, nous sommes à la semaine 30 concernant le chiffre d’affaires. Nous souhaitons aller jusqu’à la semaine 40 avec des prévisions. Non seulement Excel va remplir une colonne de prévision, mais en plus il remplira une colonne de limite haute et basse. Vous n’avez pas grand-chose à faire le logiciel s’occupe de tout. Voyons tout de suite comment procéder.

2.1 – Créer une prévision dans Excel

Nous repartons donc du tableau avec la plage de données sur les 30 semaines. Nous allons, sélectionner l’ensemble des données. Vous pouvez ajouter trois colonnes (« Prévision », « Limite inférieure », Limite supérieure »), mais Excel va le faire automatiquement.

Vous devez ensuite accéder au menu « Données ». Dans celui-ci, sélectionnez « Feuille de prévision ». Ceci va créer un graphique dans lequel vous allez pouvoir choisir le début et la fin de votre prévision. Nous allons donc indiquer semaine 40 comme fin. Vous pouvez consulter les autres paramètres, ils resteront par défaut dans l’exemple. Vous n’avez plus qu’à cliquer sur « Créer ».

Créer une prévision avec Excel

Comme vous le constatez ci-dessous, cela va donc créer un graphique et renseigner trois nouvelles colonnes de la semaine 30 à 40. La première « Prévision », est le montant prévisionnel calculé par Excel pour chaque semaine jusqu’à la semaine 40. Mais Excel va créer deux nouvelles colonnes. En effet, il va anticiper le fait que le résultat ne sera pas exactement celui calculé. Il va donc créer une fourchette basse et haute. On considère que le résultat se situera donc entre ces deux limites.

Dans le tableau, vous avez donc désormais trois nouvelles colonnes, la première pour le résultat prévu. La seconde est le résultat de confiance inférieure, donc le résultat minimum attendu. La troisième le résultat de confiance supérieure, donc le résultat maximum attendu.

Pour plus de simplicité dans la compréhension du graphique, mais c’est facultatif. Vous pouvez sélectionner l’axe de gauche (les montants de chiffre d’affaires). Modifier les limites hautes et basses sur le graphique pour que celui soit zoomé sur votre graphique. Dans le cas présent, la limite basse à 12 000 € et la haute à 18 000 € pour centrer la courbe.

Comment faire des prévisions avec Excel

Vous savez désormais, comment faire des prévisions avec Excel. Il faut noter que celle-ci ne prenne pas en compte des prévisions avec des données qui seraient saisonnières. Pour cela, on peut utiliser une autre fonction prévue à cet effet, il s’agit de PREVISION.ETS.SAISONNIERE. Mais, il s’agira d’un article complémentaire à celui-ci.