Comment utiliser Valeur cible dans Excel : guide avec des exemples concrets

Comment fonctionne Valeur cible

Vous utilisez Valeur cible lorsque vous connaissez le résultat voulu d’une formule, mais pas encore la valeur d’entrée nécessaire pour y parvenir. Vous remontez à rebours : vous fixez l’objectif et laissez Excel ajuster la variable d’entrée pour trouver la solution.

Usages courants de Valeur cible

Bien que Valeur cible s’emploie dans de nombreux domaines, on la retrouve surtout en modélisation financière et dans des contextes académiques. Exemples d’applications :

• Prévisions budgétaires : déterminez l’épargne ou l’investissement nécessaires pour atteindre un objectif financier. Par exemple, si vous voulez épargner un montant donné d’ici la fin de l’année, Valeur cible calcule l’épargne mensuelle à réaliser.
• Mensualités d’emprunt : calculez le capital que vous pouvez emprunter pour une mensualité fixe. Si vous pouvez payer un montant mensuel précis, Valeur cible ajuste le capital pour estimer le montant empruntable.
• Seuil de rentabilité : trouvez le volume de ventes nécessaire pour couvrir les coûts. En fixant le profit à zéro, Valeur cible ajuste la quantité vendue ou le prix pour identifier le point mort.
• Calculs de notes : déterminez la note minimale à obtenir à l’examen final pour atteindre la note de cours visée. Par exemple, quel score faut-il au final pour obtenir 85 % au global ? Valeur cible le calcule selon les notes actuelles et le coefficient du final.
• Objectif de GPA : calculez les notes à obtenir aux prochains cours pour atteindre ou maintenir un GPA cible.

Passons maintenant à des exemples concrets dans Microsoft Excel.

Comment utiliser Valeur cible dans Excel

L’application de Valeur cible à vos modèles de données repose essentiellement sur trois étapes récurrentes.

Étape 1 : préparer vos données

Commencez par organiser vos données et formules de façon compréhensible pour Excel. Vérifiez que la formule à résoudre est correctement liée à la cellule d’entrée qui sera ajustée.

Exemple de données pour Valeur cible. Image : auteur.

Selon la formule illustrée ci-dessus, comme le résultat C dépend des entrées A et B, nous pouvons modifier l’une ou l’autre.

Étape 2 : exécuter Valeur cible

Sélectionnez d’abord la cellule contenant la formule dont vous voulez ajuster le résultat (par exemple C2). Accédez à l’onglet Données, cliquez sur Analyse de scénarios, puis choisissez Valeur cible.

Sélection de Valeur cible dans Excel. Image : auteur.

Dans la boîte de dialogue Valeur cible, indiquez la cellule contenant la formule (Définir la cellule), la valeur cible (À la valeur) et la cellule d’entrée à modifier (En modifiant la cellule).

Boîte de dialogue Valeur cible. Image : auteur.

Cliquez sur OK pour laisser Excel rechercher la valeur d’entrée adéquate.

Dernière étape de Valeur cible. Image : auteur.

Vous pouvez aussi cliquer sur Annuler pour annuler l’opération si vous souhaitez conserver les valeurs d’origine. C’est fréquent lorsque le changement ne convient pas et que vous voulez relancer une autre opération Valeur cible.

Étape 3 : analyser le résultat 

Après exécution, Excel ajuste automatiquement la cellule d’entrée sélectionnée pour atteindre le résultat cible. La valeur mise à jour peut alors être analysée pour comprendre l’impact sur votre modèle.

Résultat de Valeur cible. Image : auteur.

Dans l’exemple ci-dessus, en passant l’entrée B de 100 à 200, nous atteignons le résultat C cible de 1 000. Ces trois étapes constituent la méthode standard d’utilisation de Valeur cible sur différents modèles de données. 

Voyons maintenant comment appliquer ces étapes à des problématiques métiers concrètes, comme le calcul d’une mensualité d’emprunt ou l’atteinte d’un objectif de ventes.

Exemples d’utilisation de Valeur cible dans Excel

Voici deux exemples de Valeur cible que vous pourriez rencontrer au travail. 

Exemple 1 : calcul de mensualité d’emprunt

Vous envisagez d’acheter un bien et souhaitez déterminer le montant maximal du prêt avec une mensualité de 1 000 $. Après comparaison des banques, vous avez un taux et une durée en tête, mais vous devez estimer le capital à demander. Valeur cible va vous y aider.

Étape 1 : préparer vos données 

Ouvrez une nouvelle feuille Excel et créez la configuration ci-dessous. 

Jeu de données de mensualités. Image : auteur.

Saisissez le taux d’intérêt retenu (par ex. 5 %) en cellule A2, la durée du prêt (par ex. 30 ans) en B2, et une première estimation du montant emprunté (par ex. 200 000 $) en C2. En D2, saisissez la formule de calcul de la mensualité avec la fonction PMT :

=PMT(B1/12, B2*12, -B3)

Cette formule divise le taux annuel par 12 (taux mensuel), multiplie la durée par 12 (nombre total d’échéances) et utilise le montant du prêt en négatif pour calculer la mensualité.

Étape 2 : exécuter Valeur cible 

Notre objectif est de trouver le montant du prêt donnant une mensualité de 1 000 $. Autrement dit, nous allons modifier C2 pour que D2 soit égal à 1 000 $. Comme vu plus haut, ouvrez Valeur cible via la section Analyse de scénarios de l’onglet Données pour afficher la boîte de dialogue suivante.

Options de Valeur cible pour l’exemple d’emprunt. Image : auteur.

Dans la boîte de dialogue Valeur cible, saisissez :

• Définir la cellule : D2, la cellule de mensualité.

• À la valeur : 1000 (votre mensualité souhaitée).

• En modifiant la cellule : C2 (le montant du prêt).

Après avoir cliqué sur OK, Excel ajuste la valeur en C2 pour déterminer combien vous pouvez emprunter tout en gardant une mensualité de 1 000 $.

Étape 3 : interpréter les résultats 

L’opération Valeur cible s’exécute et s’arrête sur l’écran ci-dessous :

Résultat de Valeur cible pour l’exemple d’emprunt. Image : auteur.

En cliquant sur OK, Excel affiche le montant ajusté en C2. C’est le capital maximal que vous pouvez emprunter compte tenu du taux, de la durée et de la mensualité.

Le montant ajusté est de 186 282 $. Vous pouvez donc financer ce capital avec une mensualité de 1 000 $. N’hésitez pas à tester d’autres valeurs et à relancer Valeur cible.

Exemple 2 : atteindre un objectif de chiffre d’affaires

Vous êtes responsable des ventes et visez 500 000 $ de chiffre d’affaires pour un produit donné. Vous connaissez le prix unitaire, mais pas encore le nombre d’unités à vendre pour atteindre l’objectif. Valeur cible permet de déterminer ce volume.

Étape 1 : préparer vos données  

Ouvrez une nouvelle feuille et saisissez les données comme ci-dessous. 

Jeu de données d’objectif de ventes. Image : auteur.

En A2, renseignez le prix unitaire réel et, en B2, une estimation du nombre d’unités. En C2, saisissez la formule de calcul du chiffre d’affaires :

=A2 * B2

Cette formule multiplie le prix unitaire par le nombre d’unités vendues pour obtenir le chiffre d’affaires total.

Étape 2 : exécuter Valeur cible  

Ouvrons Valeur cible via la section Analyse de scénarios de l’onglet Données pour afficher la boîte de dialogue suivante.

Options de Valeur cible pour l’objectif de ventes. Image : auteur.

Dans la boîte de dialogue Valeur cible, saisissez :

• Définir la cellule : C2 (cellule du chiffre d’affaires total).

• À la valeur : 500000 (objectif de chiffre d’affaires).

• En modifiant la cellule : B2 (nombre d’unités vendues).

Excel ajustera le nombre d’unités en B2 pour déterminer combien vendre afin d’atteindre 500 000 $ de chiffre d’affaires.

Étape 3 : interpréter les résultats  

L’opération Valeur cible s’exécute et s’arrête à l’étape ci-dessous :

Résultats de Valeur cible pour l’objectif de ventes. Image : auteur.

En cliquant sur OK, Excel affiche le nombre d’unités ajusté en B2. Résultat : 2 000 unités — vous devez vendre 2 000 unités à 250 $ pour atteindre 500 000 $. Si le prix unitaire ou l’objectif évoluent, relancez Valeur cible avec les nouvelles valeurs.

Erreurs courantes avec Valeur cible et comment les corriger

Comme l’illustrent les deux exemples ci-dessus, nous suivons toujours les mêmes étapes. Néanmoins, certaines erreurs fréquentes peuvent fausser vos calculs avec Valeur cible. Voici comment les comprendre et les éviter.

1. Excel indique que Valeur cible n’a peut-être pas trouvé de solution

L’un des problèmes les plus courants survient lorsque l’outil ne parvient pas à atteindre la cible. Deux cas typiques :

• la solution n’existe pas ;
• la solution n’a pas convergé dans le nombre d’itérations défini.

Reprenons l’exemple du chiffre d’affaires : si, par erreur, le prix unitaire est saisi à 0 $ au lieu de 250 $, les données deviennent :

Jeu de données d’objectif de ventes avec erreur. Image : auteur.

Suivons nos étapes habituelles de Valeur cible :

Valeur cible sur le jeu d’objectif de ventes erroné. Image : auteur.

Après avoir cliqué sur OK, que se passe-t-il ?

Erreur : Valeur cible n’a peut-être pas trouvé de solution. Image : auteur.

Le message s’affiche : « Valeur cible n’a peut-être pas trouvé de solution. » Cette erreur survient car l’équation n’est pas résoluble. Avant d’exécuter Valeur cible, vérifiez toujours la formule et les valeurs d’entrée.

Si vous êtes certain qu’une solution existe, il se peut que l’algorithme n’ait pas convergé dans la limite des 100 itérations. Pour aider à converger :

1. Augmenter le nombre maximal d’itérations : une valeur plus élevée laisse plus de temps au calcul. Modifiez-la via Fichier -> Options -> Formules -> Itérations max.
2. Démarrer avec une meilleure estimation : si vous pouvez estimer l’ordre de grandeur de la solution, saisissez un point de départ dans cette plage.

Vérifier la résolubilité de l’équation et autoriser une convergence suffisante permet d’éviter cette erreur.

2. Erreur « La cellule doit contenir une valeur »

Valeur cible attend que la cellule à modifier contienne une valeur. Sinon, elle renvoie l’erreur « La cellule doit contenir une valeur ». Cela arrive souvent lorsque la cellule est calculée à partir d’autres cellules.

Illustrons avec le même exemple : si le prix unitaire est calculé en divisant les coûts totaux par le nombre total d’unités, la configuration devient :

Jeu de données mis à jour. Image : auteur.

Lors de l’exécution de Valeur cible, une erreur survient si nous sélectionnons la cellule A2 dans l’option En modifiant la cellule.

Erreur : la cellule doit contenir une valeur. Image : auteur.

Cela se produit car Valeur cible ne peut pas modifier une cellule dont la valeur provient d’un calcul. Assurez-vous que la cellule à modifier contient une valeur saisie pour éviter l’erreur.

Conclusion

Cet article vous a présenté un outil clé de l’analyse de scénarios : Valeur cible. Au fil de plusieurs exemples, nous avons vu comment l’utiliser simplement dans Excel et les erreurs à éviter.

Nous vous invitons à expérimenter Valeur cible dans vos modèles afin d’en maîtriser les usages. Pour aller plus loin en modélisation financière dans Excel avec des outils similaires, découvrez notre cours pour débutants Financial Modelling in Excel.