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$.
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.
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.
Utilisation de la fonctionnalité Valeur cible
La fonctionnalité Excel Valeur cible se trouve dans le menu Données sous Analyse scénarios.
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.
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$.
Voyez le tout en action dans ce tutoriel
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 :
La 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é.
