Comment utiliser la valeur cible d’Excel ?
Aujourd’hui, je vous propose de découvrir une fonctionnalité très pratique d’Excel : la valeur cible qui permet d’obtenir automatiquement la valeur à affecter à une cellule pour obtenir un résultat donné sur une autre cellule. Nous verrons que cela nous permettra par exemple de déterminer le prix de vente à donner à un produit vendu pour qu’une entreprise atteigne son point mort.
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. La valeur cible
Dans ce tutoriel, nous allons découvrir le fonctionnement de la valeur cible.
Il s’agit d’un outil de simulation très pratique proposé par Excel qui permet de déterminer la valeur à donner à une cellule afin d’arriver à un résultat donné sur une autre cellule dont le résultat dépend de la première.
Pour que les choses soient plus claires, voyons un exemple volontairement très simple.
Dans cet exemple, nous avons trois cellules :
- Dans la cellule B6 nous avons une valeur de départ, ici 100,
- Dans la cellule B7, nous avons un montant que nous allons vouloir ajouter à la valeur de départ, ici 50,
- Et enfin dans la cellule B8, nous retrouvons une cellule avec une formule très simple qui additionne les deux cellules précédentes :
=B6+B7
Maintenant, si nous souhaitons que la valeur d’arrivée ne soit plus égale à 150, mais à 200, sans modifier la cellule B7, nous allons avoir deux possibilités :
- Soit effectuer le calcul permettant de déterminer la valeur de départ (Valeur d’arrivée – Valeur ajoutée = 200 – 50 = 150),
- Soit demander à Excel d’effectuer le calcul à notre place ! Et pour cela, nous allons faire appel à l’outil de détermination de la valeur cible.
Nous commençons par sélectionner la cellule dans laquelle nous souhaitons voir insérée la nouvelle valeur d’arrivée, ici il s’agit de la cellule B8 :
Puis, nous nous rendons dans le menu Données > Analyse scénarios > Valeur cible… :
Excel ouvre alors la fenêtre de paramétrage de la valeur cible :
- La « Cellule à définir » est la cellule dans laquelle se trouve la valeur finale que nous allons vouloir atteindre. Excel y insère automatique la référence de la cellule sélectionnée (c’est pour cela que nous avions au préalable sélectionné la cellule B8). Si la cellule insérée dans le champ n’est pas la bonne, il suffit de cliquer sur la cellule correspondante sur la feuille de calcul qui se trouve en dessous pour modifier la référence de la cellule à définir :
- « Valeur à atteindre » est la valeur que nous voulons donner à la cellule qui a été sélectionnée dans le champ « Cellule à définir » (ici, 200),
- Et enfin le champ « Cellule à modifier » permet de renseigner la référence de la cellule qui devra être modifiée pour permettre d’atteindre la valeur cible
Lorsque les trois champs sont renseignés, nous pouvons demander à Excel de calculer la valeur à donner à la « Cellule à modifier » pour que la « Cellule à définir » atteigne la « Valeur à atteindre » en cliquant sur le bouton [OK].
Excel effectue alors ses calculs et présente un résultat qui permette de se rapprocher au maximum de la demande formulée juste avant. Pour cela, il affecte diverses valeurs à la cellule à modifier jusqu’à ce que le résultat demandé soit atteint dans la cellule à définir :
Étant donné la simplicité de la demande, le résultat est très rapide à obtenir.
Nous pouvons également constater que la « Valeur cible » est strictement égale à la « Valeur actuelle », c’est évidemment le cas car il ne s’agit ici que d’une simple addition. Lorsque le calcul sera plus complexe, il pourra y avoir une légère différence, Excel cherchera en effet à atteindre la valeur la plus proche.
Nous pouvons à ce moment-là cliquer sur le bouton [Ok] pour valider l’insertion du calcul, tel que nous pouvons le voir sur la feuille de calculs en dessous :
Nous pouvons également cliquer sur le bouton [Annuler] pour revenir à la situation de départ.
Bien entendu, avant d’utiliser l’outil de recherche de valeur cible, il faut s’assurer que le résultat de la valeur finale, dépende effectivement de la valeur initiale !
En effet, imaginons maintenant que le résultat de la valeur d’arrivée soit égal à la valeur ajoutée multipliée par 3 :
=B7*3
Ici, nous aurons beau utiliser la recherche de valeur cible, celle-ci n’aboutira tout simplement jamais, car le changement opéré sur la valeur de départ n’influe en rien sur la valeur de la valeur d’arrivée :
Attention de ne pas valider à ce moment-là sous peine de modifier la valeur de la cellule :
Heureusement, il sera toujours possible de restaurer le résultat en annulant l’opération (en utilisant par exemple la combinaison de touches [Ctrl]+[Z]).
2. Autre exemple
Pour cet autre exemple, nous allons partir de l’analyse de la rentabilité d’une entreprise concernant l’activité de production d’un modèle de pièces détachées :
Comme nous pouvons nous en rendre compte, cette analyse démontre que compte tenu du prix de vente de 120€ TTC, l’activité de l’entreprise étudiée ici n’est pas rentable.
À partir de cette étude, l’entreprise cherche donc à savoir à partir de quel prix de vente TTC celle-ci espérer atteindre son point mort.
Compte tenu de l’ensemble des paramètres entrant en jeu dans la détermination de la marge nette, cette recherche peut être assez complexe (taux de TVA, taux de coûts directs, coûts indirects) …
Sauf bien sûr en utilisant la valeur cible d’Excel :
Ici nous cherchons à savoir pour quelle valeur donnée à la cellule C15 (contenant le prix de vente unitaire TTC), la valeur de la cellule C22 (contenant le montant de marge) sera égale à zéro !
Conclusion, l’entreprise doit vendre une unité 137.14€ au minimum pour ne plus perdre d’argent sur cette activité (soit une augmentation du prix de vente de 12,14€) !