Obwohl die lange Liste der Funktionen von Excel eines der verlockendsten Features von Microsofts Tabellenkalkulationsprogramm ist, gibt es ein paar ungenutzte Juwelen, die diese Funktionen verbessern. Ein oft übersehenes Tool ist die What-If-Analyse.

Das What-If-Analyse-^{(What-If Analysis)} Tool von Excel ist in drei Hauptkomponenten unterteilt. Der hier besprochene Teil ist die leistungsstarke Zielsuchfunktion^{(Goal Seek)} , mit der Sie von einer Funktion aus rückwärts arbeiten und die Eingaben bestimmen können, die erforderlich sind, um die gewünschte Ausgabe aus einer Formel in einer Zelle zu erhalten. Lesen Sie weiter, um zu erfahren, wie Sie das What-If-Analyse-Zielsuchtool^{(What-If Analysis Goal Seek)} von Excel verwenden.

Beispiel für das Goal Seek Tool von Excel

Angenommen, Sie möchten ein Hypothekendarlehen aufnehmen, um ein Haus zu kaufen, und Sie sind besorgt darüber, wie sich der Zinssatz für das Darlehen auf die jährlichen Zahlungen auswirkt. Die Höhe der Hypothek beträgt 100.000 USD und Sie zahlen das Darlehen über einen Zeitraum von 30 Jahren zurück.

Mit der PMT^(PMT) -Funktion von Excel können Sie leicht herausfinden, wie hoch die jährlichen Zahlungen wären, wenn der Zinssatz 0 % wäre. Die Tabelle würde wahrscheinlich so aussehen:

Die Zelle bei A2 stellt den jährlichen Zinssatz dar, die Zelle bei B2 die Laufzeit des Darlehens in Jahren und die Zelle bei C2 die Höhe des Hypothekendarlehens. Die Formel in D2 lautet:

=PMT(A2,B2,C2)

und repräsentiert die jährlichen Zahlungen einer 30-jährigen Hypothek in Höhe von 100.000 $ zu 0 % Zinsen. Beachten Sie^(Notice) , dass die Zahl in D2 negativ ist, da Excel annimmt, dass die Zahlungen ein negativer Cashflow aus Ihrer Finanzposition sind.

Leider wird Ihnen kein Hypothekengeber 100.000 $ zu 0 % Zinsen leihen. Angenommen^(Suppose) , Sie rechnen etwas nach und finden heraus, dass Sie es sich leisten können, 6.000 $ pro Jahr an Hypothekenzahlungen zurückzuzahlen. Sie fragen sich jetzt, was der höchste Zinssatz ist, den Sie für das Darlehen akzeptieren können, um sicherzustellen, dass Sie am Ende nicht mehr als 6.000 US-Dollar pro Jahr zahlen.

Viele Leute in dieser Situation würden einfach anfangen, Zahlen in Zelle A2 einzugeben, bis die Zahl in D2 ungefähr 6.000 $ erreicht hat. Sie können Excel jedoch die Arbeit für Sie erledigen lassen, indem Sie das What-If- Analyse-Zielsuchtool^{(Analysis Goal Seek)} verwenden. Im Wesentlichen werden Sie Excel vom Ergebnis in D4 rückwärts arbeiten lassen, bis es zu einem Zinssatz kommt, der Ihre maximale Auszahlung von 6.000 $ erfüllt.

Beginnen Sie, indem Sie in der Multifunktionsleiste auf die Registerkarte^(Ribbon) „ Daten^(Data) “ klicken und die Schaltfläche „Was-wäre-wenn-Analyse“ im Abschnitt „ ^{(What-If Analysis)}Datentools^{(Data Tools)} “ suchen . Klicken Sie auf die Schaltfläche „Was-wäre-wenn-Analyse“^{(What-If Analysis)} und wählen Sie „ Zielwertsuche^{(Goal Seek)} “ aus dem Menü aus.

Excel öffnet ein kleines Fenster und fordert Sie auf, nur drei Variablen einzugeben. Die Variable Set Cell muss eine Zelle sein, die eine Formel enthält. In unserem Beispiel hier ist es D2 . Die Variable Bis-Wert^{(To Value)} ist der Betrag, den die Zelle bei D2 am Ende der Analyse haben soll.

Bei uns sind es -6.000 . Denken Sie daran, dass Excel Zahlungen als negativen Cashflow betrachtet. Die Variable By Changing Cell ist der Zinssatz, den Excel für Sie ermitteln soll, sodass die Hypothek von 100.000 USD Sie nur 6.000 USD pro Jahr kostet. Verwenden Sie also Zelle A2 .

Klicken Sie auf die Schaltfläche OK , und Sie werden möglicherweise feststellen, dass Excel eine Reihe von Zahlen in den entsprechenden Zellen blinkt, bis die Iterationen schließlich zu einer endgültigen Zahl zusammenlaufen. In unserem Fall sollte die Zelle bei A2 jetzt etwa 4,31 % anzeigen.

Diese Analyse sagt uns, dass Sie, um nicht mehr als 6.000 $ pro Jahr für eine 30-jährige Hypothek von 100.000 $ auszugeben, das Darlehen mit nicht mehr als 4,31 % absichern müssen. Wenn Sie weiterhin Was-wäre-wenn-Analysen durchführen möchten, können Sie verschiedene Kombinationen von Zahlen und Variablen ausprobieren, um die Möglichkeiten zu erkunden, die Sie haben, wenn Sie versuchen, einen guten Zinssatz für eine Hypothek zu sichern.

Das What-If-Analyse-Zielsuchtool^{(What-If Analysis Goal Seek)} von Excel ist eine leistungsstarke Ergänzung zu den verschiedenen Funktionen und Formeln, die in einer typischen Tabellenkalkulation zu finden sind. Indem Sie von den Ergebnissen einer Formel in einer Zelle rückwärts arbeiten, können Sie die verschiedenen Variablen in Ihren Berechnungen klarer untersuchen.

Using Excel’s What-If Analysis Goal Seek Tool

Although Excel’s lоng list of funсtions is one of the most entiсing featυres of Microsoft’s spreadsheet application, there a fеw underutilized gems that enhance these functions. One often-overlooked tool is the What-If Analyѕis.

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

• So verwenden Sie die Was-wäre-wenn-Analyse von Excel

• So verwenden Sie die Speak Cells-Funktion von Excel

• 2 Möglichkeiten, die Transponierungsfunktion von Excel zu verwenden

• So verstehen Sie die Was-wäre-wenn-Analyse in Microsoft Excel

• So löschen Sie leere Zeilen in Excel

• So verwenden Sie If- und verschachtelte If-Anweisungen in Excel

• So teilen Sie eine Excel-Datei für eine einfache Zusammenarbeit

• 3 Möglichkeiten, eine Zelle in Excel zu teilen

• So verwenden Sie Flash Fill in Excel

• So fügen Sie CSV oder TSV in ein Excel-Arbeitsblatt ein

• So erstellen Sie ein Kreisdiagramm in Excel

• Microsoft Outlook lässt sich nicht öffnen? 10 Möglichkeiten zur Behebung

• Verwenden Sie die Tastatur, um die Zeilenhöhe und Spaltenbreite in Excel zu ändern

• So passen Sie die Einfügefunktion von Word an

• So öffnen Sie Word und Excel im abgesicherten Modus

• So suchen Sie in Excel

• Ein fortgeschrittener VBA-Leitfaden für MS Excel

• Verwenden Sie dynamische Bereichsnamen in Excel für flexible Dropdowns

• Was ist Microsoft Publisher? Ein Leitfaden für Anfänger

• 4 Möglichkeiten, Excel in Google Sheets zu konvertieren