Känslighetsanalys i Excel hjälper oss att studera osäkerheten i modellens output med förändringarna i ingångsvariablerna. Det gör främst stresstestning av våra modellerade antaganden och leder till insikter om mervärde.

Inom ramen för DCF-värdering är känslighetsanalys i Excel särskilt användbart för finansiering för modellering av aktiekurs eller värderingskänslighet för antaganden som tillväxttakt eller kapitalkostnad.

I den här artikeln tittar vi på följande känslighetsanalys i Excel för DCF-modellering professionellt.

Viktigast - Ladda ner känslighetsanalys i Excel-mall

Lär dig en variabel och två variabler DATA TABELL Typer i Excel

Känslighetsanalys i Excel

# 1 - Känslighetsanalys med en variabel datatabell i Excel

Låt oss ta exempel på ekonomi (utdelningsrabattmodell) nedan för att förstå detta i detalj.

Konstant tillväxt DDM ger oss ett aktiens verkliga värde som nuvärdet av en oändlig ström av utdelningar som växer i konstant takt.

Gordons tillväxtformel är enligt nedan -

Var:

D1 = Värdet på utdelningen som kommer att erhållas nästa år
D0 = Värdet på erhållen utdelning i år
g = Tillväxt av utdelning
Ke = Diskonteringsränta

Låt oss nu anta att vi vill förstå hur känsligt aktiekursen är med avseende på förväntad avkastning (ke). Det finns två sätt att göra detta -

Åsna sätt :-)
Vad händer om analys

# 1 - Donkey Way

Känslighetsanalys i Excel med Donkeys sätt är mycket enkelt, men svårt att implementera när många variabler är inblandade.

Vill du fortsätta göra detta med tanke på 1000 antaganden? Uppenbarligen inte!

Lär dig följande känslighetsanalys i excel-teknik för att rädda er från besväret.

# 2 - Använda en variabel datatabell

Det bästa sättet att göra känslighetsanalys i Excel är att använda datatabeller. Datatabeller ger en genväg för att beräkna flera versioner i en operation och ett sätt att visa och jämföra resultaten för alla de olika varianterna tillsammans på ditt kalkylblad. Nedan följer stegen som du kan följa för att implementera en endimensionell känslighetsanalys i Excel.

Steg 1 - Skapa tabellen i ett standardformat

I den första kolumnen har du ingångsantaganden. I vårt exempel är ingångar förväntad avkastning (ke). Observera också att det finns en tom rad (färgad i blått i denna övning) under tabellrubriken. Denna tomma rad tjänar ett viktigt syfte för denna endimensionella datatabell som du kommer att se i steg 2.

Steg 2 - Länka referensinmatningen och utmatningen enligt bilden nedan.

Utrymmet som tillhandahålls av den tomma raden används nu för att tillhandahålla inmatning (förväntad retur Ke) och utmatningsformeln. Varför görs det så här?

Vi kommer att använda "Vad händer om analys", detta är ett sätt att instruera excel att för ingången (ke), motsvarande formel som tillhandahålls på höger sida ska användas för att beräkna alla andra ingångar.

Steg 3 - Välj analysverktyget What-if för att utföra känslighetsanalys i Excel

Det är viktigt att notera att detta är indelat i två steg

Välj tabellintervall med början från vänster sida från 10% till det nedre högra hörnet av tabellen.
Klicka på Data -> Vad händer om analys -> Datatabeller

Steg 4 - Dialogrutan Datatabell öppnas.

Dialogrutan söker efter två ingångar - radinmatning och kolumninmatning. Eftersom det bara är en ingång Ke under övervägande kommer vi att tillhandahålla en enda kolumningång.

Steg 5 - Länk kolumninmatningen

I vårt fall tillhandahålls all inmatning i en kolumn och därför kommer vi att länka till kolumninmatningen. Kolumninmatning är länkad till förväntad retur (Ke). Observera att ingången ska länkas från den ursprungliga källan och inte från den som finns i tabellen

Steg 6 - Njut av utdata

# 2 - Tvåvariabel datatabellkänslighetsanalys i Excel

Datatabeller är mycket användbara för känslighetsanalys i excel, särskilt när det gäller DCF. När ett basfall är upprättat bör DCF-analys alltid testas under olika känslighetsscenarier. Testning innebär att man undersöker den inkrementella effekten av olika förändringar i antaganden (kapitalkostnad, terminal tillväxttakt, lägre intäkttillväxt, högre kapitalkrav osv.) På aktiens verkliga värde.

Låt oss ta känslighetsanalysen i excel med ett finansiellt exempel på Alibaba Discounted Cash Flow Analys.

Med basantagandena om kapitalkostnaden som 9% och en konstant tillväxttakt på 3% kom vi till en verklig värdering på 191,45 miljarder dollar.

Låt oss nu anta att du inte är helt överens med antagandena om kostnad för kapitalantaganden eller de antaganden om tillväxttakt som jag har tagit i Alibaba IPO-värdering. Du kanske vill ändra antagandena och få tillgång till effekterna på värderingarna.

Ett sätt är att ändra antaganden manuellt och kontrollera resultaten av varje förändring. (kodord - åsnan-metoden!)

Vi är dock här för att diskutera ett mycket bättre och effektivt sätt att beräkna värdering med hjälp av känslighetsanalys i excel som inte bara sparar tid utan också ger oss ett sätt att visualisera alla utdata i ett effektivt format.

Om vi utför vad-om-analysen på ett professionellt sätt utmärker sig på ovanstående data, får vi följande resultat.

Här består radingångar av förändringar i kapitalkostnad eller WACC (7% till 11%)
Kolumningångar består av förändringar i tillväxttakter (1% till 6%)
Skärningspunkten är Alibaba-värdering. För t.ex. med vårt basfall på 9% WACC och 3% tillväxttakt får vi värderingen till 191,45 miljarder dollar.

Med denna bakgrund, låt oss nu titta på hur vi kan förbereda en sådan känslighetsanalys i Excel med hjälp av tvådimensionella datatabeller.

Steg 1 - Skapa tabellstrukturen enligt nedan

Eftersom vi har två uppsättningar antaganden - Cost of Capital (WACC) och Growth Rates (g), måste du förbereda en tabell nedan.
Du är fri att byta rad- och kolumningångar. Istället för WACC kan du ha tillväxttakter och vice versa.

Steg 2 - Länka skärningspunkten till utdatacellen.

Skärningspunkten för de två ingångarna bör användas för att länka önskad utgång. I det här fallet vill vi se effekten av dessa två variabler (WACC och tillväxttakt) på eget kapital. Därför har vi kopplat den skärande cellen till utgången.

Steg 3 - Öppna tvådimensionell datatabell

Välj den tabell som du har skapat
Klicka sedan på Data -> Vad händer om analys -> Datatabeller

Steg 4 - Ange radingångar och kolumningångar.

Radinmatning är kapitalkostnaden eller Ke.
Kolumninmatningen är tillväxttakten.
Kom ihåg att länka dessa ingångar från den ursprungliga antagandekällan och inte var som helst i tabellen

Steg 5 - Njut av utdata.

De flesta pessimistiska utgångsvärdena ligger i det högra högra hörnet där kapitalkostnaden är 11% och tillväxttakten bara är 1%
Det mest optimistiska Alibaba IPO-värdet är när Ke är 7% och g är 6%
Basfallet vi beräknade för 9% ke och 3% tillväxthastigheter ligger i mitten.
Denna tvådimensionella känslighetsanalys i exceltabellen ger kunderna enkel scenarianalys som sparar mycket tid.

# 3 - Målsökning för känslighetsanalys i Excel

Kommandot Målsökning används för att få en formel till ett visst värde
Det gör detta genom att ändra en av cellerna som refereras av formeln
Goal Seek ber om en cellreferens som innehåller en formel (Set Cell). Det ber också om ett värde, vilket är den siffra du vill att cellen ska vara lika med
Slutligen ber Goal Seek att en cell ska ändras för att ta Set-cellen till önskat värde

Låt oss ta en titt på DCF för Alibaba IPO-värdering.

Som vi vet från DCF är tillväxttakter och värdering direkt relaterade. Ökande tillväxttakt ökar aktiens aktiekurs.

Låt oss anta att vi vill kontrollera vid vilken tillväxttakt kommer aktiekursen att röra $ 80?

Som alltid kan vi göra detta manuellt genom att ändra tillväxttakten för att fortsätta se effekterna på aktiekursen. Detta kommer återigen att bli en tråkig process, vi kan behöva lägga in tillväxthastigheter många gånger för att säkerställa att aktiekursen matchar $ 80 i vårt fall.

Vi kan dock använda en funktion som Goal Seek i Excel för att lösa detta i enkla steg.

Steg 1 - Klicka på cellen vars värde du vill ställa in. (Set-cellen måste innehålla en formel)

Steg 2 - Välj Verktyg, Målsökning från menyn och följande dialogruta visas:

Kommandot Målsökning föreslår automatiskt den aktiva cellen som Set-cellen.
Detta kan överskrivas med en ny cellreferens eller så kan du klicka på lämplig cell i kalkylarket.
Ange nu önskat värde som denna formel ska nå.
Klicka inuti rutan "Till värde" och skriv in det värde du vill att din valda formel ska vara lika med
Slutligen klickar du inuti rutan "Genom att ändra cell" och antingen skriver eller klickar du på cellen vars värde kan ändras för att uppnå önskat resultat.
Klicka på OK-knappen och kalkylbladet ändrar cellen till ett värde som är tillräckligt för att formeln ska nå ditt mål.

Steg 3 - Njut av utdata.

Målsökning informerar dig också om att målet uppnåddes

Slutsats

Känslighetsanalys i Excel ökar din förståelse för företagets ekonomiska och operativa beteende. Som vi lärde oss av de tre tillvägagångssätten - En dimensionell datatabell, tvådimensionell datatabell och målsök att känslighetsanalys är extremt användbar inom finansområdet särskilt i samband med värderingar - DCF eller DDM.

Du kan dock också få en förståelse på makronivå av företaget och branschen i allmänhet. Du kan utveckla fall för att återspegla värderingskänsligheten för förändringar i räntor, lågkonjunktur, inflation, BNP, etc. på värderingen. Tanke och sunt förnuft bör användas för att utveckla rimliga och användbara känslighetsfall.

Vad nästa?

Om du har lärt dig något om känslighetsanalys i Excel, vänligen lämna en kommentar nedan. Låt mig veta vad du tycker. Stort tack och ta hand. Happy Learning!

Du kan också titta på dessa artiklar nedan för att lära dig mer om värderingar och företagsekonomi -