Utilisation de l’outil de recherche d’objectifs d’analyse de simulation d’Excel
Bien que la longue liste de fonctions d’Excel soit l’une des fonctionnalités les plus attrayantes de l’application de feuille de calcul de Microsoft, il existe quelques gemmes sous-utilisées qui améliorent ces fonctions. L’analyse hypothétique est un outil souvent négligé.
L’outil d’analyse des hypothèses d’Excel est divisé en trois composants principaux. La partie discutée ici est la puissante fonction de recherche d’objectif qui vous permet de travailler à rebours à partir d’une fonction et de déterminer les entrées nécessaires pour obtenir la sortie souhaitée à partir d’une formule dans une cellule. Lisez la suite pour savoir comment utiliser l’outil de recherche d’objectifs d’analyse hypothétique d’Excel.
Exemple d’outil de recherche d’objectifs d’Excel
Supposons que vous souhaitiez contracter un prêt hypothécaire pour acheter une maison et que vous vous inquiétiez de la façon dont le taux d’intérêt du prêt affectera les paiements annuels. Le montant de l’hypothèque est de 100 000 $ et vous rembourserez le prêt sur 30 ans.
En utilisant la fonction PMT d’Excel, vous pouvez facilement déterminer quels seraient les paiements annuels si le taux d’intérêt était de 0%. La feuille de calcul ressemblerait probablement à ceci:
La cellule en A2 représente le taux d’intérêt annuel, la cellule en B2 est la durée du prêt en années et la cellule en C2 est le montant du prêt hypothécaire. La formule en D2 est:
= PMT (A2, B2, C2)
et représente les paiements annuels d’un prêt hypothécaire de 30 ans de 100 000 $ à 0% d’intérêt. Notez que le chiffre de D2 est négatif car Excel suppose que les paiements sont un flux de trésorerie négatif de votre situation financière.
Malheureusement, aucun prêteur hypothécaire ne vous prêtera 100 000 $ à 0% d’intérêt. Supposons que vous fassiez des calculs et que vous découvriez que vous pouvez vous permettre de rembourser 6 000 $ par année en paiements hypothécaires. Vous vous demandez maintenant quel est le taux d’intérêt le plus élevé que vous pouvez prendre pour le prêt pour vous assurer de ne pas payer plus de 6 000 $ par an.
De nombreuses personnes dans cette situation commenceraient simplement à taper des nombres dans la cellule A2 jusqu’à ce que le chiffre dans D2 atteigne environ 6 000 $. Cependant, vous pouvez faire en sorte qu’Excel effectue le travail à votre place à l’aide de l’outil de recherche d’objectifs d’analyse de simulation. Essentiellement, vous ferez fonctionner Excel à l’envers à partir du résultat dans D4 jusqu’à ce qu’il arrive à un taux d’intérêt qui satisfait votre paiement maximal de 6000 $.
Commencez par cliquer sur le Les données sur le ruban et localiser le Analyse des hypothèses bouton dans le Outils de données section. Clique sur le Analyse des hypothèses bouton et choisissez Recherche d’objectifs du menu.
Excel ouvre une petite fenêtre et vous demande de n’entrer que trois variables. le Définir la cellule variable doit être une cellule contenant une formule. Dans notre exemple ici, c’est D2. le Évaluer variable est le montant auquel vous voulez que la cellule D2 être à la fin de l’analyse.
Pour nous, c’est -6 000. N’oubliez pas qu’Excel considère les paiements comme un flux de trésorerie négatif. le En changeant de cellule La variable est le taux d’intérêt que vous voulez qu’Excel trouve pour vous afin que l’hypothèque de 100 000 $ ne vous coûte que 6 000 $ par an. Alors, utilisez la cellule A2.
Clique le D’accord et vous remarquerez peut-être qu’Excel fait clignoter un tas de nombres dans les cellules respectives jusqu’à ce que les itérations convergent enfin vers un nombre final. Dans notre cas, la cellule en A2 devrait maintenant lire environ 4,31%.
Cette analyse nous indique que pour ne pas dépenser plus de 6 000 $ par année sur une hypothèque de 100 000 $ sur 30 ans, vous devez garantir le prêt à un taux ne dépassant pas 4,31%. Si vous souhaitez continuer à faire des analyses hypothétiques, vous pouvez essayer différentes combinaisons de nombres et de variables pour explorer les options qui s’offrent à vous lorsque vous essayez d’obtenir un bon taux d’intérêt sur une hypothèque.
L’outil de recherche d’objectifs d’analyse de simulation d’Excel est un complément puissant aux diverses fonctions et formules présentes dans la feuille de calcul typique. En travaillant à rebours à partir des résultats d’une formule dans une cellule, vous pouvez explorer plus clairement les différentes variables de vos calculs.
