Bien que la longue liste de fonctions d'Excel soit l'une des fonctionnalités les plus attrayantes du tableur de Microsoft, il existe quelques joyaux sous-utilisés qui améliorent ces fonctions. Un outil souvent négligé est l'analyse What-If.

L'outil d' analyse What-If^{(What-If Analysis)} d'Excel est divisé en trois composants principaux. La partie abordée ici est la puissante fonctionnalité de recherche d' objectif^{(Goal Seek)} 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 apprendre à utiliser l' outil de recherche d' objectif d'analyse What-If d'Excel.^{(What-If Analysis Goal Seek)}

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 soyez préoccupé par l'impact du taux d'intérêt sur le prêt sur les paiements annuels. Le montant de l'hypothèque est de 100 000 $ et vous rembourserez le prêt en 30 ans.

En utilisant la fonction PMT d'Excel , vous pouvez facilement déterminer ce que 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 dans D2 est :

=PMT(A2,B2,C2)

et représente les versements annuels d'une hypothèque de 100 000 $ sur 30 ans à 0 % d'intérêt. Notez^(Notice) que le chiffre dans 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^(Suppose) vous fassiez des calculs et découvriez que vous pouvez vous permettre de rembourser 6 000 $ par an en versements hypothécaires. Vous vous demandez maintenant quel est le taux d'intérêt le plus élevé que vous pouvez accepter pour le prêt afin de vous assurer de ne pas payer plus de 6 000 $ par année.

De nombreuses personnes dans cette situation commenceraient simplement à taper des chiffres dans la cellule A2 jusqu'à ce que le chiffre dans D2 atteigne environ 6 000 $. Cependant, vous pouvez faire en sorte qu'Excel^(Excel) fasse le travail à votre place en utilisant l' outil What-If Analysis Goal Seek . 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 satisfasse votre paiement maximum de 6 000 $.

Commencez par cliquer sur l' onglet Données du ^(Data)ruban^(Ribbon) et recherchez le bouton Analyse de simulation dans la section ^{(What-If Analysis)}Outils de données^{(Data Tools)} . Cliquez sur le bouton What-If Analysis et choisissez Goal Seek dans le menu.

Excel ouvre une petite fenêtre et vous demande de ne saisir que trois variables. La variable Set Cell doit être une cellule qui contient une formule. Dans notre exemple ici, il s'agit de D2 . La variable To Value est le montant que vous voulez que la cellule à D2 soit à la fin de l'analyse.

Pour nous, c'est -6 000 . N'oubliez pas qu'Excel^(Excel) considère les paiements comme un flux de trésorerie négatif. La variable En changeant de cellule^{(By Changing Cell)} 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 .

Cliquez sur le bouton OK et vous remarquerez peut-être qu'Excel^(Excel) fait clignoter un tas de nombres dans les cellules respectives jusqu'à ce que les itérations convergent finalement vers un nombre final. Dans notre cas, la cellule en A2 devrait maintenant afficher 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 à effectuer des analyses de simulation, vous pouvez essayer différentes combinaisons de nombres et de variables pour explorer les options dont vous disposez lorsque vous essayez d'obtenir un bon taux d'intérêt sur un prêt hypothécaire.

L'outil What-If Analysis Goal Seek^{(What-If Analysis Goal Seek)} d'Excel est un complément puissant aux diverses fonctions et formules trouvées 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.

Using Excel’s What-If Analysis Goal Seek Tool

Although Excel’s long list of functions is one of the most enticing features of Micrоѕoft’s spreadsheet application, there a few underutilized gemѕ that enhance these functions. One often-overlooked tool is the What-If Analysis.

Excel’s What-If Analysis tool is broken down into three main components. The part discussed here is the powerful Goal Seek feature that lets you work backwards from a function and determine the inputs necessary to get the desired output from a formula in a cell. Read on to learn how to use Excel’s What-If Analysis Goal Seek tool.

Excel’s Goal Seek Tool Example

Suppose that you want to take out a mortgage loan to buy a house and you are concerned about how the interest rate on the loan will affect the yearly payments. The amount of the mortgage is $100,000 and you will pay back the loan over the course of 30 years.

Using Excel’s PMT function, you can easily figure out what the yearly payments would be if the interest rate were 0%. The spreadsheet would likely look something like this:

The cell at A2 represents the yearly interest rate, the cell at B2 is the length of the loan in years, and the cell at C2 is the amount of the mortgage loan. The formula in D2 is:

=PMT(A2,B2,C2)

and represents the yearly payments of a 30-year, $100,000 mortgage at 0% interest. Notice that the figure in D2 is negative since Excel assumes that the payments are a negative cash flow from your financial position.

Unfortunately, no mortgage lender is going to lend you $100,000 at 0% interest. Suppose you do some figuring and find out that you can afford to pay back $6,000 per year in mortgage payments. You are now wondering what is the highest interest rate you can take on for the loan to make sure you don’t end up paying more than $6,000 per year.

Many people in this situation would simply start typing numbers in cell A2 until the figure in D2 reached approximately $6,000. However, you can make Excel do the work for you by using the What-If Analysis Goal Seek tool. Essentially, you will make Excel work backwards from the result in D4 until it arrives at an interest rate that satisfies your maximum payout of $6,000.

Begin by clicking on the Data tab on the Ribbon and locating the What-If Analysis button in the Data Tools section. Click on the What-If Analysis button and choose Goal Seek from the menu.

Excel opens up a small window and asks you to input only three variables. The Set Cell variable must be a cell that contains a formula. In our example here, it is D2. The To Value variable is the amount you want the cell at D2 to be at the end of the analysis.

For us, it is -6,000. Remember that Excel sees payments as a negative cash flow. The By Changing Cell variable is the interest rate you want Excel to find for you so that the $100,000 mortgage will cost you only $6,000 per year. So, use cell A2.

Click the OK button and you may notice that Excel flashes a bunch of numbers in the respective cells until the iterations finally converge on a final number. In our case, the cell at A2 should now read about 4.31%.

This analysis tells us that in order not to spend more than $6,000 per year on a 30-year, $100,000 mortgage, you need to secure the loan at no more than 4.31%. If you want to continue doing what-if analyses, you can try different combinations of numbers and variables to explore the options you have when trying to secure a good interest rate on a mortgage.

Excel’s What-If Analysis Goal Seek tool is a powerful complement to the various functions and formulas found in the typical spreadsheet. By working backwards from the results of a formula in a cell, you can explore the different variables in your calculations more clearly.

Related posts

• Comment utiliser l'analyse What-If d'Excel

• Comment utiliser la fonctionnalité Speak Cells d'Excel

• 2 façons d'utiliser la fonction de transposition d'Excel

• Comment comprendre l'analyse What-If dans Microsoft Excel

• Comment supprimer des lignes vides dans Excel

• Comment utiliser la fonction PMT dans Excel

• Comment créer une macro ou un script VBA dans Excel

• Comment insérer CSV ou TSV dans une feuille de calcul Excel

• Comment écrire une formule/instruction IF dans Excel

• 4 façons d'utiliser une coche dans Excel

• Comment fusionner des données dans plusieurs fichiers Excel

• Utilisez le clavier pour modifier la hauteur de ligne et la largeur de colonne dans Excel

• Comment réparer une ligne dans Excel

• Qu'est-ce qu'un tableau VBA dans Excel et comment en programmer un

• Comment effectuer une recherche dans Excel

• Comment insérer une feuille de calcul Excel dans un document Word

• Comment personnaliser la fonction Coller de Word

• Centrez vos données de feuille de calcul dans Excel pour l'impression

• Comment utiliser les instructions If et If imbriquées dans Excel

• Comment utiliser le remplissage Flash dans Excel