What-If-analys i Excel: Målsökning, Scenariemanager och Datatabeller

Du ger den tre saker:

• Ställ in cell: cellen som innehåller din formel (utdata)
• Till värde: målresultatet du vill ha
• Genom att ändra cell: indatacellen som Excel får justera

Låt oss se det i praktiken. 

Du driver ett litet företag som säljer anteckningsböcker. Din nuvarande omsättning är $25,000, men ditt mål är $50,000. Priset är fast på $25, så du behöver ta reda på hur många enheter du måste sälja. 

Så här gör du:

1. Gå till Data > What-If Analysis > Goal Seek

2. I Ställ in-cellen, klicka på cell B4 (din formelcell för omsättning)

3. I Till värde, skriv 50000

4. I Genom att ändra cell, välj B2 (Antal sålda enheter)

5. Klicka på OK

Målsökning justerar B2 från 1 000 till 2 000 enheter — exakt det antal som krävs för att nå $50 000 i omsättning vid $25 per enhet.

Använda Målsökning för att nå ett specifikt resultat. Bild av författaren.

Så använder du Scenariemanager i Excel

Scenariemanager är ett verktyg för What-If-analys som låter dig spara och växla mellan flera uppsättningar indatavärden i samma kalkylblad. Varje sparad version kallas ett scenario. 

Du kan skapa så många du behöver och växla mellan dem direkt för att jämföra resultat. 

Det är användbart när du vill planera för bästa, sämsta och förväntade utfall utan att ha tre separata filer. 

Du anger tre saker:

• Scenarionamn: en etikett för varje uppsättning antaganden
• Föränderliga celler: indatacellerna som skiljer sig mellan scenarierna
• Värden: de specifika siffrorna för varje scenario

Skapa ett scenario

Säg att du planerar kvartalsförsäljning för din anteckningsboksverksamhet.

Anta att din nuvarande vinst är $15,000. Du vill modellera tre scenarier: bästa fall, sämsta fall och förväntat fall. 

Så här skapar du ett scenario:

1. Gå till Data > What-If Analysis > Scenario Manager

2. I dialogrutan Scenario Manager, klicka på Add

3. I Scenario name, skriv Best Case.

4. I Changing cells, välj B2:B4.

5. En dialogruta för Scenario Value visas. Ange följande värden i fälten:

• B2 = 2000, 

• B3 = $30

• B4= $10

6. Klicka på OK. 

Upprepa dessa steg för de två återstående scenarierna med värdena nedan:

Skapa ett scenario med tre fall. Bild av författaren.

För att växla mellan scenarier, välj ett i listan och klicka på Show. Excel byter värden direkt och din vinst uppdateras.

Klicka på Show för att se värdena för varje scenario. Bild av författaren.

Redigera scenarier

Välj valfritt scenario i listan och klicka på Edit. Du kan byta namn, ändra vilka celler det omfattar eller uppdatera värdena. Inget ändras i ditt kalkylblad förrän du klickar på Show

Skapa scenariorapporter

Scenariemanager kan också skapa en rapport som jämför alla sparade scenarier sida vid sida. 

Så här genererar du en scenariorapport:

1. Inne i Scenario Manager, klicka på Summary

2. I Scenario Summary, välj Scenario Summary

3. I Result cells, välj B7 (din vinstcell)

4. Klicka på OK

Excel skapar en sammanställningstabell på ett nytt blad som visar alla tre scenarier sida vid sida

Skapa en scenariorsammanställning. Bild av författaren.

Så använder du Datatabeller i Excel

Datatabeller automatiserar upprepade beräkningar över ett intervall av indatavärden. I stället för att manuellt ändra en indata, anteckna utdata och upprepa, definierar du intervallet en gång och Excel fyller i alla resultat på en gång.

Du anger tre saker:

• Indataintervall: värdena du vill testa
• Rad-/kolumnindatacell: cellen i din formel som dessa värden ersätter
• Formelcell: den utdata som din tabell bygger på

Envariabel-datatabeller

En envariabel-datatabell testar hur ändring av en indata påverkar ett formelresultat.

Till exempel har du en lånebetalningsformel och vill se hur månadsbetalningen ändras vid räntor från 5 % till 10 %.

Så här skapar du en envariabel-datatabell:

1. Ställ in ditt dataset. En sak att notera: i mitt dataset, i cell E1, direkt ovanför din första ränta, inte bredvid kolumnrubriken, anger jag =B5. Om E1 lämnas tom eller om formelhänvisningen hamnar i en annan cell, körs datatabellen men returnerar bara nollor i stället för betalningsvärden.

Skapa en envariabel-datatabell. Bild av författaren.

2. Välj sedan hela området D1:E9 (inklusive E1)

3. Gå till Data > What-If Analysis > Data Table

4. Lämna Rad-indatacell tom

5. I Kolumn-indatacell, välj B4 (ränta)

6. Klicka på OK.

Excel fyller tabellen med beräknade resultat för varje indatavärde. Notera hur betalningen inte ökar jämnt. Hoppet från 3 % till 4 % är $49, men från 9 % till 10 % är det $61. Ränteförändringar slår hårdare ju högre de blir.

Beräknade resultat för varje indatavärde. Bild av författaren.

Tvåvariabel-datatabeller

En tvåvariabel-datatabell testar hur ändring av två indata samtidigt påverkar ett formelresultat.

Till exempel kan en prissättningsmodell bero på både produktpris och antal sålda enheter. En tvåvariabel-datatabell kan visa vinst över olika prisnivåer och försäljningsvolymer i en vy.

Börja med att ställa in datasetet. 

Titta på datasetet jag använder:

• I cell D1, skriv =B5. Detta är formelhänvisningen och den måste ligga i det övre vänstra hörnet av rutnätet, inte bredvid en etikett

• Över rad 1, med start från E1, lista de värden för antal sålda enheter du vill testa: 500, 1000, 1500, 2000

• Ned längs kolumn D, med start från D2, lista de prisnivåer du vill testa: $20, $25, $30, $35

Ställ in ett dataset för tvåvariabel-datatabell. Bild av författaren.

Så skapar du en tvåvariabel-datatabell:

1. Markera hela området D1:H5.

2. Gå till Data > What-If Analysis > Data Table

3. I Row input cell, välj B3 (antal sålda enheter)

4. I Column input cell, välj B2 (pris per enhet)

5. Klicka på OK.

Excel fyller i vinsten för varje kombination av pris och antal sålda enheter

Beräknade resultat för indatavärden. Bild av författaren.

Tvåvariabel-datatabeller är särskilt hjälpsamma när ett enda antagande inte ger hela bilden. Pris kan se bra ut i sig, men vinsten beror också på hur många enheter kunder faktiskt köper. Den andra variabeln är där modellen blir mer användbar.

Målsökning vs. Scenariemanager vs. Datatabeller

Målsökning, Scenariemanager och Datatabeller ingår alla i Excels verktygslåda för What-If-analys, men de löser olika problem. 

Här är en snabb jämförelse mellan dem 

What-If-analys vs. Solver

What-If-analys och Solver hjälper båda till att utforska olika utfall i Excel, men de löser olika typer av problem.

• What-If-analys testar antaganden. Du ändrar en eller flera indata och ser hur dessa ändringar påverkar resultatet. Målet är att förstå möjliga utfall snarare än att hitta det bästa.
• Solver fungerar annorlunda. Du ger det ett mål, som att maximera vinst eller minimera kostnad, och det söker den kombination av indata som bäst uppfyller målet. Det testar inte ett antagande. Det löser för det optimala. 

Till exempel kan en lånesökande använda Målsökning för att bestämma lånebeloppet som ger en månatlig betalning på $500. Men ett tillverkningsföretag kan använda Solver för att bestämma produktionsmixen som genererar högst vinst inom arbets- och materialbegränsningar. 

Observera: Solver är inte en del av de standardverktyg för What-If-analys. Det är ett separat tillägg. I de flesta versioner av Excel måste du aktivera det manuellt i Excel-alternativ innan det visas på fliken Data.

Bästa praxis för What-If-analys

Här är några bästa praxis att följa när du arbetar med What-If-analys: 

1. Håll antaganden åtskilda från formler

Lagra indatavärden i dedikerade celler och referera till dessa celler i dina formler. Undvik att hårdkoda siffror direkt i beräkningar. 

Till exempel är den här formeln svår att uppdatera:

=(50*1000)-10000

Men den här versionen är mycket enklare att underhålla:

=(B2*B3)-B4

När antagandena ändras uppdaterar du bara indatacellerna i stället för att redigera formler i hela arbetsboken.

2. Märk scenarier tydligt

Scenarionamn bör omedelbart tala om för läsaren vad de representerar. ”Scenario 1”, ”Scenario 2” och ”Scenario 3” säger ingenting sex månader senare. Namnge dem efter vad de faktiskt representerar, som ”Bästa fall: 15 % tillväxt”. 

3. Validera indata

What-If-analys är bara så tillförlitlig som de indata som testas. Innan du kör en analys, kontrollera att:

• Procenttal är korrekt inmatade
• Datum använder ett konsekvent format
• Måttenheter är konsekventa
• Indatavärdena ligger inom realistiska intervall

En intäktsprognos baserad på en försäljningsökning på 500 % kan vara tekniskt korrekt men ändå oanvändbar för beslutsfattande.

4. Dokumentera antaganden

En modell som är självklar för dig idag är inte nödvändigtvis det för dig eller någon annan om tre månader. 

En kort notis bredvid varje antagande, även bara ”baserat på utfall Q3”, sparar mycket gissningar senare. Att dokumentera dem hjälper andra att förstå var siffrorna kommer ifrån och gör framtida uppdateringar mycket enklare.

Vanliga problem med What-If-analys

De flesta problem med What-If-analys beror på hur kalkylbladet är uppsatt snarare än på verktygen i sig. Om Målsökning, Scenariemanager eller Datatabeller ger oväntade resultat, kontrollera några vanliga problemområden; det brukar lösa saken.

1. Formler uppdateras inte 

Du ändrar en indata och inget händer. Vanligtvis betyder det att Excels beräkningsläge har växlats till manuellt någonstans på vägen. 

Åtgärda så här: gå till Formulas > Calculation Options och säkerställ att det är inställt på Automatic. Om det redan är det, tryck F9 för att tvinga en omberäkning.

2. Felaktiga cellreferenser

What-If-analysverktyg är beroende av korrekta cellreferenser. Om fel celler väljs blir resultaten felaktiga även om Excel genomför beräkningen. 

Till exempel kan Målsökning ändra fel indatacell. En datatabell kan hänvisa till fel rad- eller kolumnindatacell. Scenariemanager kan lagra värden för fel celler.

Åtgärda så här: Granska dina indataceller innan du kör analysen, dubbelkolla vad som är valt i varje verktygs dialogruta och använd beskrivande etiketter så att det är uppenbart vad varje cell faktiskt representerar. 

3. Missförstånd kring Målsökningens begränsningar

Många förväntar sig att Målsökning ska lösa problem med flera föränderliga variabler. Det kan den inte. Målsökning ändrar bara en indatacell åt gången för att nå ett målvärde. 

Till exempel kan Målsökning avgöra försäljningsvolymen som krävs för att generera $15 000 i vinst. Den kan inte samtidigt justera volym, pris och kostnader för att nå det målet.

Åtgärda så här: Använd Målsökning när bara en variabel behöver ändras. Använd Scenariemanager eller Solver när flera variabler är inblandade. 

4. Alltför komplexa scenariomodeller

Scenariemanager tillåter tekniskt sett upp till 32 föränderliga celler per scenario, men bara för att du kan betyder det inte att du bör. 

Efter fyra–fem variabler blir scenarier svåra att jämföra och ännu svårare att förklara för någon annan. Om din modell behöver så många rörliga delar är det troligen dags att strukturera om den i stället för att trycka in allt i ett scenario.

Avslutande tankar 

Varje kalkylblad har antaganden någonstans. Det kan vara en tillväxttakt, ett pris eller en ränta.

What-If-analys tar inte bort dessa antaganden. Den visar vad som händer om de visar sig vara fel, vilket i stort sett är vad bra beslutsfattande handlar om.

Om du är ny på detta, börja inte med en modell som har fem variabler och tre nästlade scenarier. Bygg något litet först, som en indata, en utdata, en Målsökning, och lär känna hur indata och resultat rör sig tillsammans. Det komplexa kan komma senare, när grunderna sitter.