Även om Excels långa lista med funktioner är en av de mest lockande funktionerna i Microsofts kalkylprogram, finns det några underutnyttjade ädelstenar som förbättrar dessa funktioner. Ett verktyg som ofta förbises är What-If Analysis.

Excels What-If Analysis- verktyg är uppdelat i tre huvudkomponenter. Den del som diskuteras här är den kraftfulla målsökningsfunktionen^{(Goal Seek)} som låter dig arbeta bakåt från en funktion och bestämma de ingångar som krävs för att få önskad utdata från en formel i en cell. Läs vidare för att lära dig hur du använder Excels What-If Analysis Goal Seek- verktyg.

Excels exempel på målsökningsverktyg

Anta att du vill ta ett bolån för att köpa ett hus och du är orolig för hur räntan på lånet kommer att påverka de årliga betalningarna. Bolånebeloppet är $100 000 och du kommer att betala tillbaka lånet under loppet av 30 år.

Med hjälp av Excels PMT - funktion kan du enkelt räkna ut vad de årliga betalningarna skulle vara om räntan var 0%. Kalkylarket skulle troligen se ut ungefär så här:

Cellen vid A2 representerar den årliga räntan, cellen vid B2 är längden på lånet i år, och cellen vid C2 är beloppet för bolånelånet. Formeln i D2 är:

=PMT(A2,B2,C2)

och representerar de årliga betalningarna för ett 30-årigt bolån på 100 000 USD till 0 % ränta. Observera^(Notice) att siffran i D2 är negativ eftersom Excel antar att betalningarna är ett negativt kassaflöde från din finansiella ställning.

Tyvärr kommer ingen hypotekslångivare att låna dig 100 000 USD till 0% ränta. Anta^(Suppose) att du funderar lite och får reda på att du har råd att betala tillbaka $6 000 per år i bolånebetalningar. Du undrar nu vad som är den högsta räntan du kan ta på lånet för att se till att du inte betalar mer än 6 000 USD per år.

Många människor i den här situationen skulle helt enkelt börja skriva siffror i cell A2 tills siffran i D2 nådde ungefär $6 000. Du kan dock få Excel att göra jobbet åt dig genom att använda verktyget What-If Analysis Goal Seek . I huvudsak kommer du att få Excel att arbeta baklänges från resultatet i D4 tills det når en ränta som uppfyller din maximala utbetalning på 6 000 USD.

Börja med att klicka på fliken Data på menyfliksområdet^(Ribbon) och hitta knappen What-If Analysis i avsnittet Dataverktyg^{(Data Tools)} . Klicka på knappen What-If Analysis och välj Målsökning^{(Goal Seek)} från menyn.

Excel öppnar ett litet fönster och ber dig att bara mata in tre variabler. Variabeln Set Cell måste vara en cell som innehåller en formel. I vårt exempel här är det D2 . Variabeln To Value är det belopp du vill att cellen vid D2 ska vara i slutet av analysen.

För oss är det -6 000 . Kom ihåg att Excel ser betalningar som ett negativt kassaflöde. Variabeln By Changing Cell är den ränta du vill att Excel ska hitta åt dig så att bolånet på 100 000 USD bara kommer att kosta dig 6 000 USD per år. Så använd cell A2 .

Klicka på OK -knappen och du kanske märker att Excel blinkar en massa siffror i respektive celler tills iterationerna slutligen konvergerar till ett slutligt nummer. I vårt fall bör cellen vid A2 nu läsa cirka 4,31%.

Denna analys säger oss att för att inte spendera mer än 6 000 $ per år på ett 30-årigt, 100 000 $ inteckning, måste du säkra lånet till högst 4,31%. Om du vill fortsätta göra vad-om-analyser kan du prova olika kombinationer av siffror och variabler för att utforska de alternativ du har när du försöker säkra en bra ränta på ett bolån.

Excels What-If Analysis Goal Seek- verktyg är ett kraftfullt komplement till de olika funktionerna och formlerna som finns i det typiska kalkylbladet. Genom att arbeta baklänges från resultaten av en formel i en cell kan du utforska de olika variablerna i dina beräkningar tydligare.

Using Excel’s What-If Analysis Goal Seek Tool

Although Εxcel’s long lіst of functions is one of the most enticing features of Microsoft’s spreadsheet applicatіon, there a few underutilized gems that enhancе these functions. One often-overlooked tool is the Whаt-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

• Hur man använder Excels What-If-analys

• Hur man använder Excels Speak Cells-funktion

• 2 sätt att använda Excels transponeringsfunktion

• Hur man förstår vad-om-analys i Microsoft Excel

• Hur man tar bort tomma rader i Excel

• Varför du bör använda namngivna intervall i Excel

• Hur man spårar beroende i Excel

• Hur man fixar en rad i Excel

• Hur man infogar CSV eller TSV i ett Excel-kalkylblad

• Hur man åtgärdar #N/A-fel i Excel-formler som VLOOKUP

• Hur man infogar ett Excel-kalkylblad i ett Word-dokument

• Hur man delar en Excel-fil för enkelt samarbete

• Grundläggande datasortering med en kolumn och flera kolumner i Excel-kalkylblad

• Centrera dina kalkylbladsdata i Excel för utskrift

• Hur man skapar ett VBA-makro eller skript i Excel

• Hur man använder absoluta referenser i Excel

• Ansluter Excel till MySQL

• Hur man öppnar Word och Excel i felsäkert läge

• Hur man använder If och Nested If-satser i Excel

• En avancerad VBA-guide för MS Excel