La commande valeur cible dans Microsoft Excel

■ Pour un prix donné, combien de verres de limonade un magasin de limonade doit-il vendre par an pour atteindre le seuil de rentabilité?

■ Nous voulons rembourser notre hypothèque dans 15 ans. Le taux d’intérêt annuel est de 6%. La banque nous a dit que nous pouvions nous permettre des paiements mensuels de 2 000 $. Combien pouvons-nous emprunter?

■ J’ai toujours eu des problèmes avec les problèmes d’histoire dans l’algèbre du secondaire. Excel peut-il faciliter la résolution des problèmes d’histoire?

La fonction de recherche d’objectifs dans Microsoft Excel 2013 vous permet de calculer une valeur pour une entrée de feuille de calcul qui fait correspondre la valeur d’une formule donnée à l’objectif que vous spécifiez. Par exemple, dans l’exemple de magasin de limonade de article 16, «Analyse de sensibilité avec des tableaux de données», supposons que vous ayez des frais généraux fixes, des coûts unitaires fixes et un prix de vente fixe. Compte tenu de ces informations, vous pouvez utiliser la valeur cible pour calculer le nombre de verres de limonade que vous devez vendre pour atteindre le seuil de rentabilité. Essentiellement, valeur cible intègre un puissant solveur d’équations dans votre feuille de calcul. Pour utiliser valeur cible, vous devez fournir à Excel trois informations:

■ Définir la cellule Spécifie que la cellule contient la formule qui calcule les informations que vous recherchez. Dans l’exemple de la limonade, Définir la cellule contiendrait la formule du profit.

■ To Value Spécifie la valeur numérique de l’objectif calculé dans Définir la cellule. Dans l’exemple lemonade, parce que vous voulez déterminer le volume des ventes qui représente le seuil de rentabilité, To Value serait 0.

■ En changeant de cellule Spécifie la cellule d’entrée qu’Excel modifie jusqu’à ce que Définir la cellule calcule l’objectif défini dans la cellule À valeur. Dans l’exemple de la limonade, By Changer de  Cellule contiendrait les ventes annuelles de limonade.

Réponses aux questions de cet article

Cette section fournit les réponses aux questions répertoriées au début du chapitre.

Pour un prix donné, combien de verres de limonade un magasin de limonade doit-il vendre par an pour atteindre le seuil de rentabilité?

Le travail pour cette section se trouve dans le fichier Lemonadegs.xlsx, qui est illustré à la figure 1. Comme au chapitre précédent, supposons un coût fixe annuel de 45 000 $ et un coût unitaire variable de 0,45 $. Supposons un prix de 3,00 $. La question est de savoir combien de verres de limonade devez-vous vendre chaque année pour atteindre le seuil de rentabilité.

FIGURE  1: Utilisez ces données pour configurer la fonction de recherche d’objectif afin d’effectuer une analyse du seuil de rentabilité.

Pour commencer, insérez un nombre pour la demande dans la cellule D2. Dans le groupe Analyse des hypothèses de l’onglet Données, cliquez sur Recherche d’objectifs. Remplissez maintenant la boîte de dialogue de recherche d’objectif, comme illustré à la figure ciè.

FIGURE  2:La boîte de dialogue valeur cible est remplie d’entrées pour une analyse du seuil de rentabilité.

La boîte de dialogue indique que vous souhaitez modifier la cellule D2 (demande annuelle ou ventes) jusqu’à ce que la cellule D7 (profit) atteigne une valeur de 0. Après avoir cliqué sur OK, vous obtenez le résultat illustré dans la figure . Si vous vendez environ 17 647 verres de limonade par an (ou 48 verres par jour), vous atteindrez le seuil de rentabilité. Pour trouver la valeur que vous recherchez, Excel fait varier la demande dans la cellule D2 (en alternant entre les valeurs hautes et basses) jusqu’à ce qu’il trouve une valeur qui rend le profit égal à 0 $. Si un problème a plusieurs solutions, valeur cible n’affichera qu’une seule réponse.

Nous voulons rembourser notre hypothèque dans 15 ans. Le taux d’intérêt annuel est de 6%. La banque nous a dit que nous pouvions nous permettre des paiements mensuels de 2 000 $. Combien pouvons-nous emprunter?

Vous pouvez commencer à répondre à cette question en créant une feuille de calcul pour calculer les versements mensuels sur un prêt de 15 ans (supposons des versements à la fin du mois) en fonction du taux d’intérêt annuel et du montant du prêt d’essai. Regardez le travail effectué dans le fichier Paymentgs.xlsx et dans la figure ci-dessous.

Dans la cellule E6, la formule =-VPM(annual_int_rate/12;years;amt._borrowed) calcule le paiement mensuel associé au montant emprunté, qui est répertorié dans la cellule E5. Le remplissage de la boîte de dialogue de recherche d’objectifs, comme le montre la figure 4, calcule le montant emprunté qui se traduit par des paiements mensuels égaux à 2 000 $. Avec une limite de 2 000 $ pour les paiements mensuels, vous pouvez emprunter jusqu’à 237 007,03 $.

FIGURE 3 :Vous pouvez utiliser des données comme celle-ci avec la fonction de recherche d’objectifs pour déterminer le montant que vous pouvez emprunter en fonction d’un paiement mensuel défini.

FIGURE 4:  La boîte de dialogue Recherche d’objectif est configurée pour calculer l’exemple d’hypothèque.

J’ai toujours eu des problèmes avec les problèmes d’histoire dans l’algèbre du lycée. Excel peut-il faciliter la résolution des problèmes d’histoire?

Si vous repensez à l’algèbre du lycée, la plupart des problèmes d’histoire vous obligent à choisir une variable (généralement appelée x) qui résout une équation particulière. Valeur cible est un résolveur d’équations, il est donc parfaitement adapté à la résolution de problèmes d’histoire. Voici un problème d’algèbre typique du secondaire:

Maria et Edmund ont une querelle d’amant pendant leur lune de miel à Seattle. Maria fait irruption dans sa Mazda Miata et conduit 64 miles par heure vers la maison de sa mère à Los Angeles. Deux heures après le départ de Maria, Edmund j    monte dans sa BMW à la poursuite, conduisant 80 miles par heure. Combien de kilomètres chaque personne aura-t-elle parcourus quand Edmund attrapera Maria?

Vous pouvez trouver la solution dans le fichier , illustré à la figure  ci-dessous.

FIGURE 5 : La recherche d’objectifs peut vous aider à résoudre des problèmes d’histoire.

Définir la cellule sera la différence entre les distances parcourues par Maria et Edmund. Vous pouvez définir cette valeur sur 0 en modifiant le nombre d’heures de conduite de Maria. Bien sûr, Edmund conduit deux heures de moins que Maria.

Entrez un nombre d’heures d’essai que Maria conduit dans la cellule D2. Associez les noms de plage de la plage de cellules C2: C8 aux cellules D2: D8. Parce qu’Edmund conduit deux heures de moins que Maria, entrez la formule Time_Maria_drives-2 dans la cellule D4. Dans les cellules D6 et D7, utilisez le fait que distance = vitesse * temps pour calculer la distance totale parcourue par Maria et Edmund. La différence entre les distances est calculé dans la cellule D8 avec la formule Maria_distance-Edmund_distance. Vous pouvez maintenant remplir la boîte de dialogue de recherche d’objectif, comme illustré à la figure ci-dessous.

FIGURE  6: La boîte de dialogue valeur cible est remplie pour résoudre un problème d’algèbre.

Modifiez les heures de conduite de Maria (cellule D2) jusqu’à ce que la différence entre les kilomètres parcourus (cellule D8) soit égale à 0. Comme vous pouvez le voir, après que Maria aura conduit 10 heures et Edmund 8 heures, ils auront chacun parcouru une distance de 640 miles.

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x