Analiza wrażliwości w programie Excel pomaga nam zbadać niepewność wyniku modelu ze zmianami zmiennych wejściowych. Przede wszystkim przeprowadza testy warunków skrajnych naszych modelowanych założeń i prowadzi do wglądu w wartość dodaną.

W kontekście wyceny DCF analiza wrażliwości w programie Excel jest szczególnie przydatna w finansach do modelowania ceny akcji lub wrażliwości wyceny na założenia, takie jak stopy wzrostu lub koszt kapitału.

W tym artykule zajmiemy się profesjonalnie następującą analizą wrażliwości w programie Excel do modelowania DCF.

Najważniejsze - Pobierz analizę wrażliwości w szablonie Excel

Naucz się jednej zmiennej i dwóch zmiennych DATA TABLE Typy w programie Excel

Analiza wrażliwości w programie Excel

# 1 - Analiza wrażliwości tabeli danych z jedną zmienną w programie Excel

Weźmy poniższy przykład finansów (model zdyskontowania dywidend), aby szczegółowo to zrozumieć.

Stały wzrost DDM podaje nam wartość godziwą akcji jako wartość bieżącą nieskończonego strumienia dywidend, które rosną w stałym tempie.

Wzór Gordona Growtha jest jak poniżej -

Gdzie:

D1 = Wartość dywidendy do otrzymania w przyszłym roku
D0 = wartość dywidendy otrzymanej w tym roku
g = Stopa wzrostu dywidendy
Ke = stopa dyskontowa

Teraz załóżmy, że chcemy zrozumieć, jak wrażliwa jest cena akcji w odniesieniu do oczekiwanego zwrotu (ke). Można to zrobić na dwa sposoby -

Sposób na osła :-)
Co jeśli analiza

# 1 - Donkey Way

Analiza wrażliwości w programie Excel metodą Donkey jest bardzo prosta, ale trudna do wdrożenia, gdy zaangażowanych jest wiele zmiennych.

Czy chcesz kontynuować to przy 1000 założeniach? Oczywiście, że nie!

Naucz się poniższej analizy wrażliwości w technice Excela, aby uchronić się przed kłopotami.

# 2 - Korzystanie z jednej tabeli danych zmiennych

Najlepszym sposobem przeprowadzenia analizy wrażliwości w programie Excel jest użycie tabel danych. Tabele danych zapewniają skrót do obliczania wielu wersji w jednej operacji oraz sposób przeglądania i porównywania wyników wszystkich różnych odmian razem w arkuszu. Poniżej znajdują się kroki, które można wykonać, aby wdrożyć jednowymiarową analizę wrażliwości w programie Excel.

Krok 1 - Utwórz tabelę w standardowym formacie

W pierwszej kolumnie masz założenia wejściowe. W naszym przykładzie dane wejściowe to oczekiwana stopa zwrotu (ke). Zwróć również uwagę, że pod nagłówkiem tabeli znajduje się pusty wiersz (w tym ćwiczeniu oznaczony kolorem niebieskim). Ten pusty wiersz służy ważnemu celowi dla tej jednowymiarowej tabeli danych, którą zobaczysz w kroku 2.

Krok 2 - Połącz referencyjne wejście i wyjście, jak pokazano na poniższej migawce.

Przestrzeń zapewniana przez pusty wiersz jest teraz używana do dostarczania danych wejściowych (oczekiwany zwrot Ke) i formuły wyjściowej. Dlaczego tak się dzieje?

Zamierzamy użyć „Co by było, gdyby analiza”, jest to sposób poinstruowania programu Excel, że dla danych wejściowych (ke) odpowiedni wzór podany po prawej stronie powinien zostać użyty do ponownego obliczenia wszystkich innych danych wejściowych.

Krok 3 - Wybierz narzędzie Analiza warunkowa, aby przeprowadzić analizę wrażliwości w programie Excel

Należy zauważyć, że jest to podzielone na dwa etapy

Wybierz zakres tabeli zaczynając od lewej strony, zaczynając od 10% do prawego dolnego rogu tabeli.
Kliknij Dane -> Co jeśli Analiza -> Tabele danych

Krok 4 - Otwiera się okno dialogowe Tabela danych.

Okno dialogowe wyszukuje dwa dane wejściowe - wiersz i kolumnę. Ponieważ rozważane jest tylko jedno wejście Ke, zapewnimy jedno wejście kolumnowe.

Krok 5 - Połącz dane wejściowe kolumny

W naszym przypadku wszystkie dane wejściowe znajdują się w kolumnie i dlatego połączymy się z danymi wejściowymi kolumny. Dane wejściowe w kolumnie są powiązane z oczekiwanym zwrotem (Ke). Należy pamiętać, że wejście powinno być połączone z oryginalnego źródła, a nie z tego, które jest wewnątrz tabeli

Krok 6 - Ciesz się wyjściem

# 2 - Analiza wrażliwości tabeli danych z dwiema zmiennymi w programie Excel

Tabele danych są bardzo przydatne do analizy wrażliwości w programie Excel, szczególnie w przypadku DCF. Po ustaleniu scenariusza podstawowego analizę DCF należy zawsze testować w ramach różnych scenariuszy wrażliwości. Testowanie obejmuje zbadanie przyrostowego wpływu różnych zmian założeń (koszt kapitału, końcowe stopy wzrostu, niższy wzrost przychodów, wyższe wymogi kapitałowe itp.) Na wartość godziwą akcji.

Weźmy analizę wrażliwości w programie Excel na przykładzie finansowym analizy zdyskontowanych przepływów pieniężnych Alibaba.

Przy podstawowym założeniu kosztu kapitału na poziomie 9% i stałej stopie wzrostu na poziomie 3% , doszliśmy do godziwej wyceny w wysokości 191,45 miliardów dolarów.

Załóżmy teraz, że nie w pełni zgadzasz się z założeniami dotyczącymi kosztu kapitału lub z założeniami dotyczącymi stopy wzrostu, które przyjąłem w wycenie IPO Alibaba. Możesz zmienić założenia i uzyskać dostęp do wpływu na wyceny.

Jednym ze sposobów jest ręczna zmiana założeń i sprawdzenie wyników każdej zmiany. (słowo kodowe - metoda Donkey!)

Jednak jesteśmy tutaj, aby omówić znacznie lepszy i skuteczny sposób obliczania wyceny za pomocą analizy wrażliwości w programie Excel, który nie tylko oszczędza czas, ale także zapewnia nam sposób wizualizacji wszystkich szczegółów wyjściowych w efektywnym formacie.

Jeśli wykonamy analizę What-if w sposób profesjonalny na powyższych danych, otrzymamy następujący wynik.

Tutaj dane wejściowe wiersza składają się ze zmian w koszcie kapitału lub WACC (7% do 11%)
Dane wejściowe w kolumnie obejmują zmiany tempa wzrostu (1% do 6%)
Punktem przecięcia jest wycena Alibaba. Np. używając naszego scenariusza bazowego 9% WACC i 3% stopy wzrostu, otrzymujemy wycenę jako 191,45 mld USD.

Na tym tle przyjrzyjmy się teraz, jak możemy przygotować taką analizę wrażliwości w programie Excel przy użyciu dwuwymiarowych tabel danych.

Krok 1 - Utwórz strukturę tabeli, jak podano poniżej

Ponieważ mamy dwa zestawy założeń - koszt kapitału (WACC) i stopy wzrostu (g), należy przygotować tabelę podaną poniżej.
Możesz przełączać wiersze i kolumny. Zamiast WACC możesz mieć stopy wzrostu i odwrotnie.

Krok 2 - Połącz punkt przecięcia z komórką wyjściową.

Do połączenia żądanego wyjścia należy wykorzystać punkt przecięcia dwóch wejść. W tym przypadku chcemy zobaczyć wpływ tych dwóch zmiennych (WACC i stopa wzrostu) na wartość kapitału własnego. W związku z tym połączyliśmy przecinającą się komórkę z wyjściem.

Krok 3 - Otwórz dwuwymiarową tabelę danych

Wybierz utworzoną tabelę
Następnie kliknij Dane -> Co jeśli Analiza -> Tabele danych

Krok 4 - Podaj dane wejściowe wierszy i kolumn.

Dane wejściowe w wierszu to Koszt kapitału lub Ke.
Dane wejściowe kolumny to tempo wzrostu.
Pamiętaj, aby połączyć te dane wejściowe z oryginalnego źródła założenia, a nie z dowolnego miejsca w tabeli

Krok 5 - Ciesz się wynikami.

Większość pesymistycznych wartości produkcji znajduje się w prawym górnym rogu, gdzie koszt kapitału wynosi 11%, a stopa wzrostu tylko 1%
Najbardziej optymistyczna wartość IPO Alibaba ma miejsce, gdy Ke wynosi 7%, a g 6%
Podstawowy przypadek, który obliczyliśmy dla 9% ke i 3% stóp wzrostu, leży pośrodku.
Ta dwuwymiarowa analiza wrażliwości w tabeli programu Excel zapewnia klientom łatwą analizę scenariuszy, która oszczędza dużo czasu.

# 3 - Poszukiwanie celu dla analizy wrażliwości w programie Excel

Polecenie Szukaj wyniku służy do sprowadzenia jednej formuły do określonej wartości
Robi to, zmieniając jedną z komórek, do których odwołuje się formuła
Goal Seek pyta o odwołanie do komórki, które zawiera formułę (komórka Set). Pyta również o wartość, która jest liczbą, którą ma równać komórka
Na koniec Goal Seek prosi o zmianę komórki, aby ustawić komórkę Set do wymaganej wartości

Przyjrzyjmy się wycenie DCF Alibaba IPO.

Jak wiemy z DCF, stopy wzrostu i wycena są ze sobą bezpośrednio powiązane. Rosnące stopy wzrostu powodują wzrost ceny akcji.

Załóżmy, że chcemy sprawdzić, przy jakim tempie wzrostu cena akcji osiągnie 80 USD?

Jak zawsze, możemy to zrobić ręcznie, zmieniając stopy wzrostu, aby nadal widzieć wpływ na cenę akcji. To znowu będzie żmudny proces, być może będziemy musieli wielokrotnie wprowadzać wskaźniki wzrostu, aby w naszym przypadku cena akcji odpowiadała 80 USD.

Możemy jednak użyć funkcji takiej jak Goal Seek w programie Excel, aby rozwiązać ten problem w prostych krokach.

Krok 1 - Kliknij komórkę, której wartość chcesz ustawić. (Komórka Set musi zawierać formułę)

Krok 2 - Wybierz Narzędzia, Szukaj wyniku z menu i pojawi się następujące okno dialogowe:

Polecenie Goal Seek automatycznie sugeruje aktywną komórkę jako komórkę Set.
Można to nadpisać nowym odwołaniem do komórki lub kliknąć odpowiednią komórkę w arkuszu kalkulacyjnym.
Teraz wprowadź żądaną wartość, jaką ma osiągnąć ta formuła.
Kliknij w polu „Do wartości” i wpisz wartość, której ma równać wybrana formuła
Na koniec kliknij w polu „Przez zmianę komórki” i wpisz lub kliknij komórkę, której wartość można zmienić, aby uzyskać żądany wynik
Kliknij przycisk OK, a arkusz kalkulacyjny zmieni komórkę na wartość wystarczającą do osiągnięcia celu przez formułę.

Krok 3 - Ciesz się wynikami.

Goal Seek informuje również, że cel został osiągnięty

Wniosek

Analiza wrażliwości w programie Excel pozwala lepiej zrozumieć finansowe i operacyjne zachowania firmy. Jak dowiedzieliśmy się z trzech podejść - jednowymiarowych tabel danych, dwuwymiarowych tabel danych i poszukiwania celu, analiza wrażliwości jest niezwykle przydatna w dziedzinie finansów, szczególnie w kontekście wycen - DCF lub DDM.

Możesz jednak również uzyskać ogólne zrozumienie firmy i branży na poziomie makro. Możesz opracować przypadki odzwierciedlające wrażliwość wyceny na zmiany stóp procentowych, recesję, inflację, PKB itp. W wycenie. Podczas opracowywania rozsądnych i użytecznych przypadków wrażliwości należy kierować się myślą i zdrowym rozsądkiem.

Co następne?

Jeśli dowiedziałeś się czegoś o analizie wrażliwości w Excelu, zostaw komentarz poniżej. Powiedz mi co myślisz. Wielkie dzięki i uważaj. Przyjemnej nauki!

Możesz również zapoznać się z poniższymi artykułami, aby dowiedzieć się więcej o wycenach i finansach korporacyjnych -