Gevoeligheidsanalyse in Excel helpt ons de onzekerheid in de output van het model te bestuderen met de veranderingen in de inputvariabelen. Het voert voornamelijk stresstests uit van onze gemodelleerde aannames en leidt tot inzichten met toegevoegde waarde.

In de context van DCF-waardering is Gevoeligheidsanalyse in Excel vooral nuttig in de financiële sector om de aandelenkoers of waarderingsgevoeligheid te modelleren voor veronderstellingen zoals groeipercentages of kapitaalkosten.

In dit artikel bekijken we de volgende gevoeligheidsanalyse in Excel voor professionele DCF-modellering.

Belangrijkste - Download gevoeligheidsanalyse in Excel-sjabloon

Leer een variabele en twee variabele DATA TABLE-typen in Excel

Gevoeligheidsanalyse in Excel

# 1 - Gevoeligheidsanalyse van één variabele gegevenstabel in Excel

Laten we het financiële voorbeeld (model voor dividendkorting) hieronder nemen om dit in detail te begrijpen.

Constante groei DDM geeft ons de reële waarde van een aandeel als een contante waarde van een oneindige stroom dividenden die met een constante snelheid groeien.

Gordon Growth-formule is zoals hieronder -

Waar:

D1 = Waarde van volgend jaar te ontvangen dividend
D0 = waarde van dividend ontvangen dit jaar
g = Groeisnelheid van dividend
Ke = kortingspercentage

Laten we nu aannemen dat we willen begrijpen hoe gevoelig de aandelenkoers is met betrekking tot het verwachte rendement (ke). Er zijn twee manieren om dit te doen -

Ezel manier :-)
Wat als analyse

# 1 - Donkey Way

Gevoeligheidsanalyse in Excel met behulp van de manier van Donkey is heel eenvoudig, maar moeilijk te implementeren als er veel variabelen bij betrokken zijn.

Wil je dit blijven doen, uitgaande van 1000 aannames? Duidelijk niet!

Leer de volgende gevoeligheidsanalyse in Excel-techniek om uzelf van de problemen te redden.

# 2 - Eén variabele gegevenstabel gebruiken

De beste manier om gevoeligheidsanalyses in Excel uit te voeren, is door gegevenstabellen te gebruiken. Gegevenstabellen bieden een snelkoppeling voor het berekenen van meerdere versies in één bewerking en een manier om de resultaten van alle verschillende variaties samen op uw werkblad te bekijken en te vergelijken. Hieronder staan de stappen die u kunt volgen om een eendimensionale gevoeligheidsanalyse in Excel te implementeren.

Stap 1 - Maak de tabel in een standaardformaat

In de eerste kolom heb je de inputaannames. In ons voorbeeld zijn inputs het verwachte rendement (ke). Houd er ook rekening mee dat er een lege rij is (blauw gekleurd in deze oefening) onder de tabelkop. Deze lege rij dient een belangrijk doel voor deze eendimensionale gegevenstabel die u in stap 2 zult zien.

Stap 2 - Koppel de referentie-invoer en uitvoer zoals weergegeven in de onderstaande snapshot.

De ruimte die door de lege rij wordt geboden, wordt nu gebruikt om invoer (verwacht rendement Ke) en de uitvoerformule te leveren. Waarom wordt het zo gedaan?

We gaan “What if Analysis” gebruiken, dit is een manier om Excel te instrueren dat voor de Input (ke) de corresponderende formule aan de rechterkant moet worden gebruikt om alle andere inputs opnieuw te berekenen.

Stap 3 - Selecteer de tool What-if-analyse om een gevoeligheidsanalyse in Excel uit te voeren

Het is belangrijk op te merken dat dit is onderverdeeld in twee stappen

Selecteer het tafelbereik vanaf de linkerkant vanaf 10% tot de rechter benedenhoek van de tafel.
Klik op Gegevens -> Wat als analyse -> Gegevenstabellen

Stap 4 - Dialoogvenster Gegevenstabel wordt geopend.

Het dialoogvenster zoekt naar twee invoer: rij-invoer en kolominvoer. Aangezien er slechts één invoer Ke wordt overwogen, zullen we een enkele kolominvoer bieden.

Stap 5 - Koppel de kolominvoer

In ons geval wordt alle invoer in een kolom geleverd en daarom zullen we linken naar de kolominvoer. Kolominvoer is gekoppeld aan het verwachte rendement (Ke). Houd er rekening mee dat de invoer moet worden gekoppeld vanaf de originele bron en niet vanaf de bron in de tabel

Stap 6 - Geniet van de output

# 2 - Gevoeligheidsanalyse van twee variabele gegevenstabellen in Excel

Gegevenstabellen zijn erg handig voor gevoeligheidsanalyse in Excel, vooral in het geval van DCF. Zodra een basisscenario is vastgesteld, moet de DCF-analyse altijd worden getest onder verschillende gevoeligheidsscenario's. Testen omvat het onderzoeken van het incrementele effect van verschillende veranderingen in veronderstellingen (kapitaalkosten, terminale groeipercentages, lagere omzetgroei, hogere kapitaalvereisten, enz.) Op de reële waarde van de aandelen.

Laten we de gevoeligheidsanalyse in Excel nemen met een financieel voorbeeld van Alibaba Discounted Cash Flow Analysis.

Met de basisaannames van de kapitaalkosten van 9% en een constant groeipercentage van 3% , kwamen we uit op de eerlijke waardering van $ 191,45 miljard.

Laten we nu aannemen dat u het niet volledig eens bent met de veronderstellingen over de kosten van kapitaal of de veronderstellingen over het groeipercentage die ik heb aangenomen bij de IPO-waardering van Alibaba. Misschien wilt u de aannames wijzigen en de impact op waarderingen bekijken.

Een manier is om de aannames handmatig te wijzigen en de resultaten van elke wijziging te controleren. (codewoord - Donkey-methode!)

We zijn hier echter om een veel betere en efficiëntere manier te bespreken om waardering te berekenen met behulp van gevoeligheidsanalyse in Excel die niet alleen tijd bespaart, maar ons ook een manier biedt om alle uitvoerdetails in een effectief formaat te visualiseren.

Als we de What-if analyse in Excel op een professionele manier uitvoeren op bovenstaande data, dan krijgen we de volgende output.

Hier bestaan de invoer van rijen uit wijzigingen in kapitaalkosten of WACC (7% tot 11%)
Kolominvoeringen bestaan uit veranderingen in groeipercentages (1% tot 6%)
Het snijpunt is Alibaba-waardering. Voor bijv. gebruikmakend van ons basisscenario van 9% WACC en 3% groeipercentages, krijgen we de waardering als $ 191,45 miljard.

Laten we met deze achtergrond kijken hoe we een dergelijke gevoeligheidsanalyse in Excel kunnen voorbereiden met behulp van tweedimensionale gegevenstabellen.

Stap 1 - Maak de tabelstructuur zoals hieronder aangegeven

Aangezien we twee sets aannames hebben: kapitaalkosten (WACC) en groeipercentages (g), moet u een onderstaande tabel opstellen.
U bent vrij om de invoer van rijen en kolommen om te schakelen. In plaats van WACC kunt u groeipercentages hebben en vice versa.

Stap 2 - Verbind het snijpunt met de outputcel.

Het snijpunt van de twee ingangen moet worden gebruikt om de gewenste uitgang te koppelen. In dit geval willen we het effect zien van deze twee variabelen (WACC en groeipercentage) op de aandelenwaarde. Daarom hebben we de kruisende cel aan de uitgang gekoppeld.

Stap 3 - Open de tweedimensionale gegevenstabel

Selecteer de tafel die u heeft gemaakt
Klik vervolgens op Gegevens -> Wat als analyse -> Gegevenstabellen

Stap 4 - Geef de rij-invoer en kolom-invoer op.

Rij-invoer is de kosten van kapitaal of Ke.
De kolominvoer is de groeisnelheid.
Vergeet niet om deze invoer te koppelen vanuit de oorspronkelijke bron van aannames en niet ergens in de tabel

Stap 5 - Geniet van de output.

De meeste pessimistische outputwaarden bevinden zich in de rechterbovenhoek, waar de kapitaalkosten 11% bedragen en het groeipercentage slechts 1%
De meest optimistische Alibaba IPO-waarde is wanneer Ke 7% is en g 6%
Het basisscenario dat we hebben berekend voor groeipercentages van 9% ke en 3% ligt in het midden.
Deze tweedimensionale gevoeligheidsanalyse in de Excel-tabel biedt de klanten een eenvoudige scenario-analyse die veel tijd bespaart.

# 3 - Doel zoeken voor gevoeligheidsanalyse in Excel

De opdracht Doel zoeken wordt gebruikt om een formule naar een specifieke waarde te brengen
Het doet dit door een van de cellen te wijzigen waarnaar wordt verwezen door de formule
Doel zoeken vraagt om een celverwijzing die een formule bevat (de cel Set). Het vraagt ook om een waarde, dit is het cijfer dat u wilt dat de cel gelijk is
Ten slotte vraagt Goal Seek om een cel die moet worden gewijzigd om de Set-cel naar de vereiste waarde te brengen

Laten we eens kijken naar de DCF van Alibaba IPO Valuation.

Zoals we van DCF weten, zijn groeipercentages en waardering direct gerelateerd. Toenemende groeipercentages verhogen de aandelenkoers van het aandeel.

Laten we aannemen dat we willen controleren met welk groeipercentage de aandelenkoers $ 80 zal raken?

Zoals altijd kunnen we dit handmatig doen door de groeipercentages te wijzigen om de impact op de aandelenkoers te blijven zien. Dit zal opnieuw een vervelend proces worden, het kan zijn dat we vaak groeipercentages moeten invoeren om ervoor te zorgen dat de aandelenkoers in ons geval overeenkomt met $ 80.

We kunnen echter een functie als Goal Seek in Excel gebruiken om dit in eenvoudige stappen op te lossen.

Stap 1 - Klik op de cel waarvan u de waarde wilt instellen. (De cel Set moet een formule bevatten)

Stap 2 - Kies Tools, Doel zoeken in het menu en het volgende dialoogvenster verschijnt:

De opdracht Doel zoeken stelt automatisch de actieve cel voor als de cel Set.
Dit kan worden overgetypt met een nieuwe celverwijzing of u kunt op de juiste cel in de spreadsheet klikken.
Voer nu de gewenste waarde in die deze formule moet bereiken.
Klik in het vak "Naar waarde" en typ de waarde die u wilt dat uw geselecteerde formule gelijk is
Klik ten slotte in het vak 'Door cel te veranderen' en typ of klik op de cel waarvan de waarde kan worden gewijzigd om het gewenste resultaat te bereiken
Klik op de knop OK en de spreadsheet verandert de cel in een waarde die voldoende is om de formule uw doel te laten bereiken.

Stap 3 - Geniet van de output.

Goal Seek informeert u ook dat het doel is bereikt

Gevolgtrekking

Gevoeligheidsanalyse in Excel vergroot uw begrip van het financiële en operationele gedrag van het bedrijf. Zoals we hebben geleerd van de drie benaderingen - eendimensionale gegevenstabellen, tweedimensionale gegevenstabellen en doelgerichtheid, is die gevoeligheidsanalyse buitengewoon nuttig in de financiële sector, vooral in de context van taxaties - DCF of DDM.

U kunt echter ook op macroniveau inzicht krijgen in het bedrijf en de branche in het algemeen. U kunt cases ontwikkelen om de waarderingsgevoeligheid voor veranderingen in rentetarieven, recessie, inflatie, bbp, enz. Op de waardering weer te geven. Bij het ontwikkelen van redelijke en bruikbare gevallen van overgevoeligheid moeten gedachten en gezond verstand worden gebruikt.

Wat nu?

Als u iets over gevoeligheidsanalyse in Excel hebt geleerd, laat dan hieronder een opmerking achter. Laat me weten wat je denkt. Veel dank en wees voorzichtig. Veel plezier met leren!

U kunt ook deze artikelen hieronder bekijken voor meer informatie over waarderingen en bedrijfsfinanciering -