Bien que la longue liste de fonctions d'Excel soit l'une des fonctionnalités les plus séduisantes de l'application tableur de Microsoft, il existe quelques gemmes sous-utilisées qui améliorent ces fonctions. Un outil souvent négligé est l'analyse de simulation.
L'outil d'analyse de simulation d'Excel est décomposé en trois composants principaux. La partie discutée ici est la puissante fonctionnalité de recherche d'objectif qui vous permet de travailler en arrière à 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. Poursuivez votre lecture pour apprendre à utiliser l'outil de recherche d'hypothèses What-If d'Excel.
Exemple d'outil de recherche de but d'Excel
Supposons que vous voulez contracter un prêt hypothécaire pour acheter une maison. vous êtes préoccupé par la façon dont le taux d'intérêt sur le prêt affectera les paiements annuels. Le montant de l'hypothèque est de 100 000 $ et vous rembourserez le prêt sur une période de 30 ans.
En utilisant la fonction PMT d'Excel, vous pouvez facilement déterminer les paiements annuels si le taux d'intérêt était 0 % La feuille de calcul devrait ressembler à ceci:
La cellule de A2 représente le taux d'intérêt annuel, la cellule de B2 est la longueur du prêt dans les années, et la cellule à C2 est le montant du prêt hypothécaire. La formule dans D2 est:
= PMT (A2, B2, C2)
et représente les paiements annuels d'une hypothèque de 100 000 $ sur 30 ans à 0% d'intérêt. Notez que le chiffre en 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 $ à un taux d'intérêt de 0%. Supposons que vous fassiez quelques calculs et que vous puissiez 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 année.
Beaucoup de gens dans cette situation commenceraient simplement à taper des chiffres dans la cellule A2 jusqu'à ce que le chiffre de D2 atteigne environ 6 000 $. Cependant, vous pouvez faire en sorte qu'Excel fasse le travail à votre place en utilisant l'outil Recherche d'objectif d'analyse. Essentiellement, vous allez faire reculer Excel à partir du résultat dans D4 jusqu'à ce qu'il atteigne un taux d'intérêt qui satisfait votre paiement maximal de 6 000 $.
Commencez par cliquer sur l'onglet DonnéesCliquez sur le ruban et localisez le bouton Analyse d'hypothèsesdans la section Outils de données. Cliquez sur le bouton Analyse d'hypothèseset choisissez Recherche d'objectifdans le menu.
Excel ouvre une petite fenêtre et vous demande d'entrer seulement trois variables. La variable Définir la celluledoit être une cellule contenant une formule. Dans notre exemple ici, c'est D2. La variable Valeurest la quantité que vous voulez que la cellule D2soit à la fin de l'analyse.
Pour nous, c'est -6 000. Rappelez-vous que Excel voit les paiements comme un flux de trésorerie négatif. La variable Par cellule changeanteest le taux d'intérêt que vous voulez qu'Excel trouve pour vous, de sorte que le prêt hypothécaire de 100 000 $ ne vous coûtera que 6 000 $ par année. Donc, utilisez la cellule A2.
Cliquez sur le bouton OK. Vous remarquerez peut-être qu'Excel affiche une série de nombres dans les cellules respectives jusqu'à ce que les itérations convergent finalement. un nombre final. Dans notre cas, la cellule à A2 devrait maintenant lire environ 4,31%.
Cette analyse nous dit que pour ne pas dépenser plus de 6 000 $ par an sur une hypothèque de 100 000 $ sur 30 ans, vous devez garantir le prêt à un maximum de 4,31%. Si vous voulez continuer à faire des analyses de simulation, vous pouvez essayer différentes combinaisons de nombres et de variables pour explorer les options que vous avez quand vous essayez d'obtenir un bon taux d'intérêt sur une hypothèque.
L'outil Goal Seek est un complément puissant aux diverses fonctions et formules trouvées dans la feuille de calcul classique. En retraçant les résultats d'une formule dans une cellule, vous pouvez explorer plus clairement les différentes variables de vos calculs.