L’Analyse de scénarios est l’une des fonctionnalités Excel les plus puissantes et l’une des moins comprises. En termes plus généraux, Analyse de scénarios vous permet de tester divers scénarios et de déterminer une gamme de résultats possibles. En d’autres termes, cela vous permet de voir l’impact d’une certaine modification sans modifier les données réelles. Dans cette section particulière, nous nous concentrerons sur l’un des outils d’analyse Analyse scénarios d’Excel – Valeur cible.

1 Qu’est-ce que la valeur cible ou recherche d’objectifs?

 La valeur cible est l’outil d’analyse de scénarios intégré d’Excel qui montre comment une valeur dans une formule a un impact sur une autre. Plus précisément, il détermine quelle valeur vous devez entrer dans une cellule d’entrée pour obtenir le résultat souhaité dans une cellule de formule.

La meilleure chose à propos de la Valeur Cible d’Excel est qu’il effectue tous les calculs en coulisses, et il vous est uniquement demandé de spécifier ces trois paramètres :

• Cellule de formule
• Valeur cible/souhaitée
• La cellule à changer pour atteindre l’objectif

L’outil Valeur Cible est particulièrement utile pour effectuer une analyse de sensibilité dans la modélisation financière et est largement utilisé par les majors de la gestion et les propriétaires d’entreprise. Mais il existe de nombreuses autres utilisations qui peuvent vous être utiles.

Par exemple, Valeur Cible peut vous dire combien de ventes vous devez réaliser au cours d’une certaine période pour atteindre un bénéfice net annuel de 100 000 € ( exemple 1 ). Ou, quelle note vous devez obtenir lors de votre dernier examen pour recevoir une note de passage globale de 70 % ( exemple 2 ). Ou combien de votes vous devez obtenir pour gagner l’élection ( exemple 3 ).

Dans l’ensemble, chaque fois que vous souhaitez qu’une formule renvoie un résultat spécifique mais que vous ne savez pas quelle valeur d’entrée dans la formule ajuster pour obtenir ce résultat, arrêtez de deviner et utilisez la fonction Valeur Cible d’excel!

Remarque : Valeur Cible ne peut traiter qu’une seule valeur d’entrée à la fois. Si vous travaillez sur un modèle commercial avancé avec plusieurs valeurs d’entrée, utilisez le complément Solveur pour trouver la solution optimale.

2 Comment utiliser la valeur cible dans Excel

Le but de cette section est de vous expliquer comment utiliser la fonction de valeur cible ou recherche d’objectif. Nous allons donc travailler avec un ensemble de données très simple :

Le tableau ci-dessus indique que si vous vendez 100 articles à 5€ chacun, moins la commission de 10 %, vous gagnerez 450€. La question est :  combien d’articles devez-vous vendre pour gagner 1 000€ ?

Voyons comment trouver la réponse avec Valeur Cible:

1. Configurez vos données de manière à avoir une cellule de formule et une cellule changeante dépendant de la cellule de formule.
2. Accédez à l’onglet Données / groupe Prévision, cliquez sur le bouton Analyse scénarios, et sélectionnez valeur cible

3. Dans la boîte de dialogue Valeur Cible, définissez les cellules/valeurs à tester et cliquez sur OK :

• Définir la cellule– la référence à la cellule contenant la formule (B5).
• Pour évaluer– le résultat de la formule que vous essayez d’obtenir (1000).
• En changeant de cellule– la référence de la cellule d’entrée que vous souhaitez ajuster (B3).

4. La boîte de dialogue État de la recherche d’ objectif apparaîtra et vous indiquera si une solution a été trouvée. Si cela réussit, la valeur dans la “cellule changeante” sera remplacée par une nouvelle. Cliquez sur OK pour conserver la nouvelle valeur ou sur Annuler pour restaurer celle d’origine.

Dans cet exemple, Valeur Cible a découvert que 223 articles (arrondis au nombre entier supérieur) doivent être vendus pour générer un revenu de 1000€.

Si vous n’êtes pas sûr de pouvoir vendre autant d’articles, vous pouvez peut-être atteindre l’objectif de revenus en modifiant le prix de l’article ? Pour tester ce scénario, effectuez une analyse de recherche d’objectif exactement comme décrit ci-dessus, sauf que vous spécifiez une autre cellule de changement (B2) :

Par conséquent, vous découvrirez que si vous augmentez le prix unitaire à 11 $, vous pouvez atteindre 1 000 $ de revenus en ne vendant que 100 articles :

Remarques :

■ Valeur Cible d’Excel ne modifie pas la formule, il modifie uniquement la valeur d’entrée que vous fournissez à la zone En changeant de cellule .

■ Si la Valeur Cible n’est pas en mesure de trouver la solution, il affiche la valeur la plus proche qu’il a trouvée.

■ Vous pouvez restaurer la valeur d’entrée d’origine en cliquant sur le bouton Annuler ou en appuyant sur le raccourci Annuler (Ctrl + Z).

4 Exemples d’utilisation de la recherche d’objectifs

Vous trouverez ci-dessous quelques exemples supplémentaires d’utilisation de la fonction Valeur Cible dans Excel. La complexité de votre modèle économique n’a pas vraiment d’importance tant que votre formule dans la cellule a définir dépend de la valeur dans la cellule Changeante, directement ou via des formules intermédiaires dans d’autres cellules.

Exemple 1 : Atteindre l’objectif de bénéfice

Problème : Il s’agit d’une situation commerciale typique – vous avez les chiffres des ventes des 3 premiers trimestres et vous voulez savoir combien de ventes vous devez réaliser au cours du dernier trimestre pour atteindre le bénéfice net cible pour l’année, disons 100 000 €.

Solution : avec les données source organisées comme indiqué dans la capture d’écran ci-dessus, configurez les paramètres suivants pour la fonction de recherche d’objectif :

• Définir la cellule – la formule qui calcule le bénéfice net total (D6).
• Pour valoriser – le résultat de la formule que vous recherchez (100 000 €).
• En changeant de cellule – la cellule contenant les revenus bruts du trimestre 4 (B5).

Résultat : L’analyse Valeur Cible montre que pour obtenir un bénéfice net annuel de 100 000 €, votre chiffre d’affaires du quatrième trimestre doit être de 185 714€

Exemple 2 : déterminer la note de réussite à l’examen

Problème :A la fin du cours, un étudiant passe 3 examens. La note de passage est de 70 %. Tous les examens ont le même poids, la note globale est donc calculée en faisant la moyenne des 3 notes. L’étudiant a déjà passé 2 examens sur 3. La question est la suivante : quelle note l’étudiant doit-il obtenir au troisième examen pour réussir l’ensemble du cours ?

Solution :Faisons Valeur Cible pour déterminer le score minimum à l’examen 3 :

• Cellule à définir – la formule qui fait la moyenne des scores des 3 examens (B5).
• Pour valoriser – la note de passage (70%).
• En changeant de cellule – la 3 ^ème note d’examen (B4).

Résultat :Afin d’obtenir la note globale souhaitée, l’étudiant doit obtenir un minimum de 67% au dernier examen :

Exemple 3 : Analyse par simulation de l’élection

Problème : Vous vous présentez à un poste où une majorité des deux tiers (66,67 % des voix) est requise pour remporter l’élection. En supposant qu’il y ait au total 200 membres votants, combien de voix devez-vous obtenir ?

Actuellement, vous avez 98 votes, ce qui est plutôt bien mais pas suffisant car cela ne fait que 49% du total des votants :

Solution : Utilisez Valeur cible pour connaître le nombre minimum de votes “Oui” dont vous avez besoin pour obtenir :

• Définir la cellule – la formule qui calcule le pourcentage des votes “Oui” actuels (C2).
• Pour évaluer – le pourcentage requis de votes “Oui” (66,67%).
• En changeant de cellule – le nombre de votes “Oui” (B2).

Résultat :l’analyse de scénarios  avec Valeur cible montre que pour atteindre la barre des deux tiers ou 66,67 %, vous avez besoin de 133 votes “Oui” :

En formatant la cellule C2 avec les décimales on obtient 66,67%

5 Valeur cible Excel ne fonctionne pas

Parfois, Valeur Cible n’est pas en mesure de trouver une solution simplement parce qu’elle n’existe pas. Dans de telles situations, Excel obtiendra la valeur la plus proche et vous informera que la valeur cible n’a peut-être pas trouvé de solution :

Si vous êtes certain qu’il existe une solution à la formule que vous essayez de résoudre, consultez les conseils de dépannage suivants.

1. Vérifiez les paramètres de recherche d’objectif

Tout d’abord, assurez-vous que la cellule à définir fait référence à la cellule contenant une formule, puis vérifiez si la cellule de formule dépend, directement ou indirectement, de la cellule changeante.

2. Ajustez les paramètres d’itération

Dans votre Excel, cliquez sur Fichier / Options / Formules et modifiez ces options :

• Nombre maximal d’itérations – augmentez ce nombre si vous souhaitez qu’Excel teste davantage de solutions possibles.
• Modification maximale – diminuez ce nombre si votre formule nécessite plus de précision. Par exemple, si vous testez une formule avec une cellule d’entrée égale à 0 mais que la recherche d’objectif s’arrête à 0,001, la définition de la modification maximale sur 0,0001 devrait résoudre le problème.

La capture d’écran ci-dessous montre les paramètres d’itération par défaut :

3. Pas de références circulaires

Pour que Valeur cible (ou n’importe quelle formule Excel) fonctionne correctement, les formules impliquées ne doivent pas être co-dépendantes les unes des autres, c’est-à-dire qu’il ne doit pas y avoir de références circulaires .