Astuce Excel: Valeur cible (goal seek)

Publié le 29 avril 2011
par Sophie Marchand M.Sc., CPA, CGA, MVP
Valeur cible paramètres

Astuce Excel: Valeur cible (goal seek)

Supposons que votre entreprise vend plusieurs types de forfaits, qui possèdent chacun un prix de vente et un coût différent. Supposons maintenant qu’en faisant un exercice budgétaire, vous arriviez à une marge brute totale de 430K$. On vous informe que l’objectif de l’entreprise serait plutôt de se rendre à 480K$. On vous demande alors combien d’unités supplémentaires de chaque forfait l’entreprise devra vendre pour atteindre cet objectif.

 

Pour effectuer ce type de calculs, tirés de l’ingénierie inversée, la fonctionnalité sous Excel Valeur cible (Goal seek) vous facilitera largement la tâche.

 

Scénario sous analyse

Votre budget prévoit les nombres d’unités par forfait présentés à la ligne 5. Les marges bénéficiaires par forfait sont connues. Avec ces informations, vous présentez donc au budget, une marge brute de 430K$.

 

Valeur cible scénario original

 

Règle de 3

Pour calculer le nombre d’unités additionnelles qui vous mènera à une marge total de 480K$, tout en respectant la répartition du nombre d’unités vendues par forfait, vous devrez d’abord utiliser une règle de 3 pour exprimer le nombre d’unités additionnelles des forfaits 2 et 3, en fonction du nombre d’unités du forfait 1.

En effet, non seulement ceci est logique d’un point de vue commercial mais sera aussi nécessaire pour utiliser la fonctionnalité valeur cible, qui va s’opérer uniquement sur les unités du forfaits 1. Ce sont les formules Excel avec des règles de 3 qui permettront de calculer les valeurs des forfaits 2 et 3.

 

Valeur cible règle de 3

 

Reconstitution de la marge brute

Vous pouvez maintenant compléter votre fichier pour montrer le nombre d’unités additionnelles requises par forfait et les marges additionnelles générées pour aboutir à la marge brute totale souhaitée. Ici, on ne sait pas encore combien d’unités ajouter donc tout est à 0. On a donc toujours une marge brute de 430K$ avant et après l’ajout des nouvelles unités.

 

Valeur cible reconstitution de marge

 

Utilisation de la fonctionnalité Valeur cible

La fonctionnalité Excel Valeur cible se trouve dans le menu Données sous Analyse scénarios.

 

Données Valeur Cible

 

Lorsque vous cliquez sur l’option Valeur cible, on vous demande de fournir 3 paramètres :

  • Cellule à définir (doit toujours être une formule… ici, c’est la marge brute additionnelle)
  • Valeur à atteindre (doit toujours être entré à la main… ici c’est le 50K$ additionnel que l’on souhaite atteindre dans la cellule F14)
  • Cellule à modifier (doit toujours être une cellule sans formule… ici c’est le nombre d’unités du forfait 1)

 

Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière.

 

À noter qu’il est impossible de modifier plus d’une cellule. C’est la raison pour laquelle on utilise la règle de 3 pour déterminer le nombre d’unités des forfaits 2 et 3.

 

Valeur cible paramètres

 

Après avoir cliqué sur OK, Excel proposera la solution, comme suit. On voit bien que la règle de 3 s’est appliquée pour déterminer le nombre d’unités des forfaits 2 et 3 et que la marge brute additionnelle est de 50K$.

 

Valeur cible résultat final

 

Conclusion

La fonctionnalité Valeur cible sous Excel est simple à utiliser. Ce qui est plus complexe, c’est monter le modèle dans Excel afin qu’il réagisse comme souhaité à l’opération de valeur cible.

 


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 

Formation complémentaire

Afin d’approfondir vos connaissances avec des fonctionnalités comme la Valeur cible dans Excel, nous vous recommandons notre formation Excel – Mise à niveau.

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top