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.
- Jedna zmienna tabela danych
- Tabela danych dla dwóch zmiennych
- Szukaj celu
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 rosnących 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ć przyjmowanie tych 1000 założeń? 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 poniżej.
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 na poinstruowanie programu Excel, że dla danych wejściowych (ke) należy użyć odpowiedniego wzoru podanego po prawej stronie, aby ponownie obliczyć wszystkie inne dane wejściowe.
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, dlatego połączymy się z danymi wejściowymi kolumny. Dane wejściowe w kolumnie są połączone 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 dla dwóch zmiennych w programie Excel
Tabele danych są bardzo przydatne do analizy wrażliwości w programie Excel, szczególnie w przypadku DCF. Po ustaleniu przypadku podstawowego analizę DCF należy zawsze testować w ramach różnych scenariuszy wrażliwości. Testowanie obejmuje badanie 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 przy użyciu analizy wrażliwości w programie Excel, który nie tylko oszczędza czas, ale także umożliwia wizualizację wszystkich szczegółów wyjściowych w efektywnym formacie.
Jeśli przeprowadzimy analizę Co-jeśli w profesjonalny sposób 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. Na przykład, 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 wynosi tylko 1%
- Najbardziej optymistyczna wartość IPO Alibaba ma miejsce, gdy Ke wynosi 7%, a g wynosi 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ą chcesz, aby komórka była równa
- 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 celów, 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. Miłej nauki!
Możesz również zapoznać się z poniższymi artykułami, aby dowiedzieć się więcej o wycenach i finansach korporacyjnych -
- Wzór na wrażliwość cenową
- Analiza ryzyka - metody
- Analiza progu rentowności programu Excel
- Analiza Excel Pareto
