La fonctionnalité méconnue pour des prévisions en un clin d’œil sur Excel

Dans ce tutoriel, je vais vous montrer comment transformer vos données en décisions stratégiques grâce à une fonction Excel peu connue mais incroyablement puissante. Que vous cherchiez à optimiser vos finances ou encore à gérer votre entreprise plus efficacement, restez avec nous jusqu'à la fin pour devenir comment automatiser vos calculs les plus complexes.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, imaginons que vous gériez une petite entreprise et que vous êtes en quête d'une analyse précise de vos revenus et dépenses pour maximiser vos profits.

Nous allons alors utiliser le tableau suivant, qui reprend les postes de revenus et de dépenses relevés au cours du premier trimestre d’une année donnée.

Il comprend les mois de janvier à mars, avec les revenus, les dépenses variables (qui représentent 25% des revenus), les dépenses fixes, et le profit réalisé chaque mois.

Ici, nous pouvons observer que pour chaque mois, les dépenses variables sont calculées comme un pourcentage des revenus. Ce taux est constant à 25%, ce qui signifie que si nos revenus augmentent, les dépenses variables augmentent également de manière proportionnelle.

Les dépenses fixes, quant à elles, sont constantes chaque mois, indépendamment des revenus.

Le profit est calculé par différence entre les revenus moins les dépenses variables et les dépenses fixes.

En avril, l’entreprise a installé une nouvelle unité de production afin d’accroître sa capacité de production. Les dépenses fixes ont également augmenté à la suite de cet investissement.

L’entreprise souhaite maintenant connaître les revenus nécessaires pour atteindre l’objectif fixé de 10000€ de profit.

2. Qu’est-ce que la « Valeur Cible » ?

Pour obtenir en quelques secondes le montant des revenus que cela représente, nous allons pouvoir utiliser un outil très puissant d’Excel : la « Valeur Cible ».

Cette « Valeur Cible » est une fonctionnalité d’Excel qui agit un peu comme un calculateur de scénario inversé.

Au lieu de fournir des entrées pour obtenir une sortie, nous lui donnons le résultat souhaité, et elle nous indique quelle entrée nous devons ajuster pour y parvenir.

Cette fonction est particulièrement utile pour les analyses financières où vous devez atteindre des objectifs précis, comme un certain niveau de profit ou de marge.

3. Vérification des données

Pour comprendre le mécanisme de la valeur cible, nous allons maintenant souhaiter calculer quel niveau de revenu l’entreprise doit être capable d’atteindre au cours du mois d’avril pour atteindre l’objectif défini de 10000€.

Pour commencer, nous devons identifier le résultat final à atteindre.

Ici, il s’agit de la cellule qui contient le profit que nous souhaitons cibler, pour le mois d'avril (cellule E11) :

Avant de pouvoir aller plus loin, nous devons nous assurer que celle-ci contient une formule correcte, dont le résultat doit dépendre directement de la cellule dans laquelle nous allons venir la valeur cible, c’est-à-dire la cellule B11 dans laquelle nous allons saisir les revenus du mois d’avril.

En sélectionnant la cellule E11, nous pouvons constater que celle-ci contient la formule :

=B11-C11-D11

Où :

- B11 est la cellule où nous entrerons notre revenu (à déterminer par la valeur cible).

- C11 est la cellule qui calcule automatiquement les dépenses variables, en fonction des revenus réalisés (25% de B11).

- D11 contient le montant des dépenses fixes pour avril, qui est de 5 000 €.

Cette configuration est essentielle car la fonction « Valeur Cible » ajustera la cellule des revenus (B11) pour obtenir le profit souhaité dans E11. Si la formule dans E11 est incorrecte ou si elle ne dépend pas directement de la cellule que nous cherchons à modifier (dans ce cas, B11), la valeur cible ne pourra pas fonctionner correctement.

Une fois ces vérifications faites, nous pouvons poursuivre en toute confiance avec l'utilisation de la fonction « Valeur Cible » pour atteindre notre objectif de profit.

4. Utilisation de la « Valeur Cible »

Maintenant, pour déterminer le montant des revenus à atteindre, nous procédons de la manière suivante :

- Après avoir sélectionné la cellule E11, nous nous rendons dans l'onglet « Données » du Ruban,

- Puis, nous déroulons le menu « Analyse de scénario », dans le groupe « Prévisions », puis nous cliquons sur « Valeur Cible… » :

- Dans la boîte de dialogue, nous sélectionnons la cellule de profit (E11) dans le champ « Cellule à définir » :

Normalement, si vous aviez sélectionné cette cellule avant d’appeler la fonctionnalité de valeur cible, celle-ci devrait correctement préremplie.

- Ensuite, nous saisissons la valeur à laquelle nous souhaitons que cette cellule soit égale dans le champ « Valeur à atteindre ». Ici, il s’agit du montant de profit à atteindre, soit 10000€ :

- Et pour finir, nous nous plaçons sur le champ « Cellule à modifier », afin de sélectionner la cellule dans laquelle nous souhaitons que la valeur cible soit renseignée, ici ce sera la cellule reprenant les revenus d'avril (B11) :

Après avoir saisi ces informations, nous validons en cliquant sur « OK ».

Excel calcule alors le chiffre d’affaires nécessaire pour obtenir le profit visé compte tenu des nouvelles dépenses.

Comme nous avons pu nous en rendre compte, Excel utilise un algorithme d'itérations successives pour définir la valeur cible. Cela consiste à ajuster de manière répétée la valeur de la cellule à modifier (dans notre cas, le revenu du mois d'avril) et à recalculer la formule du profit jusqu'à ce que le résultat souhaité soit atteint ou que l'algorithme dépasse un nombre prédéfini d'itérations.

Lorsqu'on déclenche la fonction « Valeur Cible », Excel commence par une estimation de la valeur de départ et effectue des ajustements incrémentiels. À chaque étape, il évalue si ces ajustements rapprochent le résultat de l'objectif fixé. Dans notre exemple, il aura donc fallu 17 itérations pour calculer que le montant des revenus à atteindre en avril s'élève à 20 000 € pour obtenir un profit de 10 000 €.

Pour votre information, nous aurions également pu utiliser une approche manuelle avec des formules telles que :

=B11-B11*25%-D11

Mais étant donné qu'Excel propose une fonctionnalité d'automatisation de calcul, aussi simple à utiliser, il serait dommage de s'en priver !

La fonction « Valeur Cible » est accessible aux utilisateurs de tous niveaux et réduit considérablement le risque d'erreurs humaines.

D’autant plus qu'ici les modalités de calcul sont volontairement très simples, pour que ce tutoriel soit à la portée de tous, mais dans la réalité, nous pouvons très souvent être confrontés à des cas bien plus complexes, tels que :

- Des modèles financiers où plusieurs variables interdépendantes doivent être ajustées simultanément pour atteindre un certain équilibre.

- Des calculs de seuil de rentabilité où les coûts fixes et variables peuvent changer en fonction de différents scénarios de volume de vente.

- Des prévisions de trésorerie où les entrées et sorties de fonds sont affectées par des délais de paiement variables et des dates d'encaissement.

La beauté de la fonction « Valeur Cible » réside dans sa simplicité et sa puissance. Elle élimine le besoin de faire des calculs manuels fastidieux ou de deviner quelles modifications apporter à vos données d’entrée. C’est un outil précieux pour les prévisions financières, la budgétisation, et pour tester l’impact de différentes variables sur vos résultats financiers.