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é 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é 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)

 

À 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 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 :

François Le May
Écrit il y a 5 mois
Il était grandement temps que je la suive!

Mon efficacité au travail et ma capacité à faire des analyses rapidement vont être grandement améliorées.

Mélanie Bonneau
Écrit il y a 5 mois
Adaptée pour le monde des CPA

Je trouve que cette formation est la plus adaptée pour le monde des CPA. En d'autres mots, j'apprends davantage ici que dans une formation générale de type "débutant - intermédaire - avancé" et je trouve que c'est plus concret pour mes activités quotidiennes. Merci!

Isabelle Madé
Écrit il y a 8 mois
Excellente formation!

Le découpage des sections est parfait, on peut prendre le tout à petites bouchées à notre rythme. J'utilise Excel depuis très longtemps et comme bien des gens j'ai appris sur le tas. Cette mise à niveau m'a permis d'apprendre ou mieux comprendre certaines formules et fonctionnalités que j'aurais dû maîtriser bien avant! Je suis prête pour la suite...

Melissa Forgues
Écrit il y a 8 mois
J'ai adoré!

Je croyais faire cette formation pour rien puisque je travaille avec Excel et prit quelques cours dans le passé. Je croyais que je maîtrisais toute la base! Ayayaye! Je me suis trompé! La formatrice donne énormément de trucs et des informations jusque là inconnue pour moi, jamais montré en salle de formation avec d'Autres compagnies... Déjà là, je vais sauvé beaucoup de temps. OMG! Bravo!

Sylvie Giroux
Écrit il y a 2 ans
Captivant

La formatrice expliquait très clairement chaque exemple et avait un ton de voix dynamique. En suivant une formation en ligne, je craignais de m'endormir, mais non! J'ai été captivée et intéressée tout au long de ma formation.

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

Laisser un commentaire

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

Scroll to Top