Jeden, dwa… trzy czynniki? Czyli szybka analiza wrażliwości w MS Excel
Powiedzieć, że Excel jest potężnym narzędziem, to nie powiedzieć nic. W tym programie można wykonywać naprawdę skomplikowane operacje przy użyciu wbudowanych narzędzi. Ale warto pamiętać o tym, że Excel ułatwia również pracę nad tymi nieco prostszymi zagadnieniami.
Jedną z takich kwestii jest możliwość przeprowadzenia analizy wrażliwości za pomocą narzędzia nazywanego Tabelą danych. Zanim jednak zagłębię się w opis korzystania z tej funkcji, kilka słów o samej analizie.
Analiza wrażliwości jest jednym z najczęściej stosowanych narzędzi do oceny ryzyka, wykorzystywanym nie tylko w przypadku przedsiębiorstw czy projektów inwestycyjnych. Dzięki niej możliwe jest sprawdzenie, jak wahania pojedynczych zmiennych przekładają się na wynik ostateczny, na przykład inwestycji. Dzięki temu możliwe jest sprawne zarządzanie ryzykiem. W skrócie można ją opisać jako odpowiedź na pytanie, o ile zmieni się wartość zmiennej objaśnianej, jeśli zmienna objaśniająca zmieni się o dany procent. Dlatego w modelowej analizie wrażliwości zmianom poddaje się tylko jeden z czynników, dla którego badamy wpływ na całość przedsięwzięcia.
Dzięki Excelowi nie jesteśmy ograniczeni tylko do jednej zmiennej. Możemy łatwo przebadać wrażliwość naszego projektu dla dwóch zmiennych. Zaprezentuję to w poniższym artykule. A na sam koniec zastanowimy się, w jaki sposób zbadać wrażliwość trzech zmiennych.
Zacznijmy jednak od czegoś prostego. Przyjmijmy bardzo uproszczoną analizę projektu inwestycyjnego na podstawie wskaźnika NPV. W roku zerowym ponosimy nakłady w wysokości 15 000 zł. Projekt zapewnia nam stałe, coroczne przychody w kwocie 3000 zł przez okres siedmiu lat. Przyjmijmy, że oceniamy tę inwestycję przy zastosowaniu stałej stopy dyskontowej, wynoszącej 4%. Dzięki zastosowaniu funkcji NPV widzimy, że wartość bieżąca przepływów netto dla powyższych założeń wynosi prawie 2900 zł (Rysunek 1).
Poszczególne dane do naszego modelu są wpisywane w pojedynczych komórkach. Stopa dyskontowa zawarta jest w komórce B8, kwota inwestycji początkowej – w komórce B4, natomiast kwota stałego przychodu rocznego pobierana jest z komórki K3.
Załóżmy teraz, że chcemy zbadać wrażliwość naszego projektu na wysokość kwoty inwestowanej. Jak zmieniałaby się wartość NPV, gdybyśmy mogli zainwestować nieco mniej lub trochę więcej środków? Moglibyśmy oczywiście wstawiać kolejne wartości do naszego arkusza i obserwować zmianę wartości funkcji NPV, ale byłoby to działanie wysoce nieefektywne. Dlatego w jednej kolumnie wypiszmy zakładane przez nas poziomy finansowania inwestycji, poczynając od 10 000 zł i zwiększając tę kwotę o 500 zł, aż do poziomu 20 000 zł. Moglibyśmy teraz wpisać we wszystkie komórki obok tych kwot odpowiednio dopasowaną funkcję NPV, ale ponownie – Excel upraszcza nasze życie. Przecież nie zawsze mamy do czynienia z tak prostą funkcją jak NPV, a wpisywanie czy nawet kopiowanie funkcji mogłoby powodować ryzyko pomyłki.
Aby móc skorzystać z narzędzia ułatwiającego analizę wrażliwości, musimy dołożyć do naszego zestawienia jeszcze dwie dane. Nad kolumną kwot umieszczamy wartość 15 000, będącą de facto podlinkowaną wartością z naszej tabeli wejściowej. Obok niej zaś wstawiamy odniesienie do komórki z wynikową wartością NPV. To będzie swoisty nagłówek, do którego odwoływać będzie się stosowane przez nas narzędzie.
Zaznaczamy teraz cały zakres, w którym przeprowadzać będziemy analizę wrażliwości. W naszym przykładzie będzie to od A11 do B32. Następnie na karcie Dane excelowej Wstążki, w grupie Prognoza, klikamy na przycisk Analiza warunkowa. Z rozwijanego menu wybieramy Tabelę danych. Pojawi się małe okno określenia parametrów narzędzia zawierające dwa pola: Wierszowa komórka wejściowa i Kolumnowa komórka wejściowa. Ponieważ nasze dane są w kolumnie, interesuje nas tylko to drugie pole. Wybieramy dla niego komórkę zawierającą pierwotną kwotę naszej inwestycji – a zatem w podanym przykładzie będzie to komórka B4. Zatwierdzamy przyciskiem OK i gotowe (Rysunek 2). W pustych komórkach obok naszych danych wejściowych pojawiły się odpowiednie kwoty NPV dla poszczególnych poziomów zainwestowanej kwoty.
Przyjrzyjmy się temu, co właśnie wstawiliśmy do naszego arkusza. W wypełnionych komórkach pojawiła się funkcja TABELA. Jak widać po otaczających ją klamrowych nawiasach, jest to funkcja tablicowa. Oznacza to choćby tyle, że nie będziemy w stanie zmienić czy usunąć pojedynczej komórki w naszym zakresie, musimy edytować całość. Ale czy jest to możliwe? Spróbujmy zatem zmienić tę funkcję, zaznaczając komórki od B12 do B32, a następnie dodając do naszej funkcji dowolny składnik, np. +100. Ponieważ jest to funkcja tablicowa, zatwierdzamy ją kombinacją Ctrl + Shift + Enter i... Pojawia się komunikat, że funkcja nie jest poprawna. Funkcja TABELA jest nietypową funkcją, ponieważ można ją wstawić jedynie za pomocą narzędzia Tabela danych. Nie można jej znaleźć w bibliotece funkcji Excela, nie wyświetla się na liście podpowiedzi przy wpisywaniu jej w komórce.
Jest to jednak normalna funkcja, a co za tym idzie – jej wynik jest zależny od przyjętych argumentów. W naszym przykładzie zmienną określającą była wysokość nakładów inwestycyjnych, ale na naszą zmienną określaną, czyli NPV, mają wpływ także inne dane: stopa dyskonta i wielkość przychodów w poszczególnych latach. Zmieńmy jeden z nich w naszych danych źródłowych, na przykład przyjmijmy stopę dyskontową w komórce B8 na poziomie 3%. Zauważmy, że dane w naszej analizie wrażliwości uległy zmianie.
Możemy w łatwy sposób porównać nasze wyniki dla dwóch wartości tej zmiennej. Wystarczy, że w komórce C11, obok naszego pierwszego wyniku branego pod uwagę w tabeli danych, dodamy nową funkcję NPV, bazującą na tych samych założeniach przepływów, ale niższej stopie dyskontowej. Zaznaczamy obszar od A11 do C32, wybieramy ponownie polecenie Tabela danych, zaznaczamy odpowiednią komórkę kolumnową i gotowe. Narzędzie dołoży dane w kolumnie C, prezentując nowe wartości NPV dla niższej stopy i odpowiednich nakładów inwestycyjnych.
Można się domyślać, że takie dołożenie kolejnej zmiennej to wstęp do analizy wrażliwości opartej na dwóch zmiennych. Jednak aby tego dokonać, musimy nieco inaczej zaprezentować nasz nagłówek. W tym celu stwórzmy nowy zakres. Ponownie w jednej kolumnie umieśćmy wartości nakładu inwestycyjnego – niech to będzie zakres E12:E32. Następnie wiersz powyżej tego zakresu, zaczynając od kolumny na prawo od niego, wpisujemy wartości drugiej zmiennej objaśniającej. Załóżmy, że chcemy zbadać wpływ kwoty rocznego przychodu, zatem umieszczamy dziewięć kwot, poczynając od 2000 zł i zwiększając je każdorazowo o 250 zł – w naszym przykładzie jest to zakres. W polu na przecięciu tych dwóch zakresów, czyli w komórce E11, umieszczamy funkcję NPV lub też odwołanie do niej z komórki B9.
Zaznaczamy całość i z menu Analiza warunkowa wybieramy ponownie Tabela danych. Jako Kolumnową komórkę wejściową ponownie wskazujemy B4, zawierającą nakłady inwestycyjne. Z kolei jako Wierszową komórkę wejściową wybieramy to, co ma odzwierciedlenie w naszym wierszu z danymi, czyli komórkę K3, zawierającą podstawową wartość rocznego przychodu. Zatwierdzamy i tabela jest wypełniona. Mamy w niej zaprezentowane wartości NPV dla poszczególnych kwot nakładów inwestycyjnych oraz wartości rocznego przepływu (Rysunek 3).
W ten sposób można łatwo przeprowadzić analizę wrażliwości dla dwóch czynników określających. Na początku tego artykułu wspomniałem jednak o możliwości zbadania jej dla trzech czynników. Oczywiście możemy żmudnie wpisywać kolejne wartości tej trzeciej zmiennej, czyli w naszym przypadku stopy dyskontowej, a następnie patrzeć na zmiany w tabeli, ale to nie jest zbyt efektywny sposób. Proponuję pewne usprawnienie. Skoro analiza oparta na dwóch zmiennych to dwuwymiarowa tabela, w przypadku trzech musiałaby to być jakaś przestrzenna kostka. Excel jeszcze nie pozwala na wpisywanie danych w trzech wymiarach, dlatego musimy sobie ten dodatkowy wymiar stworzyć.
W tym celu przyda nam się na wstążce karta Deweloper. Jeśli jej nie mamy, klikamy na wstążce prawym przyciskiem myszy i wybieramy Dostosuj Wstążkę. Na karcie Deweloper rozwijamy menu Wstaw i z grupy Kontrolki formularza wybieramy Pasek przewijania. Rysujemy go wedle uznania, poziomo lub pionowo. Na otrzymanym w ten sposób suwaku klikamy prawym przyciskiem myszy i wybieramy opcję Formatuj formant. Pojawi się nowe okno, w którym interesuje nas karta Kontrolka. Jako Wartość minimalną wpisujemy 1, ponieważ będziemy badać naszą wrażliwość od stopy na poziomie 1%. Jako wartość maksymalną wpisujemy 8. Zmiana przyrostowa to 1, ponieważ nie chcemy analizować NPV dla połówek procentów. Ostatni ważny element to pole Łącze komórki, w którym podajemy adres dowolnej pustej komórki (Rysunek 4). Zatwierdzamy.
Widzimy teraz, że przesuwając nasz suwak, zmieniamy wartość wskazanej komórki. Ale jak to połączyć z naszymi danymi? Bardzo prosto. W komórce prezentującej stopę dyskontową, czyli B8, wpisujemy prostą formułę, dzielącą przez 100 wartość naszej komórki połączonej z suwakiem. Gotowe. Mamy teraz dwuwymiarową tabelę, prezentującą NPV dla różnych poziomów finansowania i rocznego przychodu, której wartości możemy zmieniać suwakiem określającym wysokość stopy dyskontowej.
Jeśli chcemy mieć bardziej przejrzyste dane, zaznaczmy jeszcze wszystkie pola zawierające funkcję TABELA i skorzystajmy z Formatowania warunkowego na karcie Narzędzia główne. Wybieramy Reguły wyróżniania komórek, następnie Mniejsze niż – i jako wartość graniczną podajemy zero. Teraz bardzo łatwo widzimy obszar niekorzystny dla naszej hipotetycznej inwestycji.
Nic nie stoi na przeszkodzie, aby takich suwaków mieć więcej i powiązać je z innymi zmiennymi, wpływającymi na nasz główny parametr. W naszym przykładzie moglibyśmy do naszego przychodu dodać koszty stałe i zmienne, jak też procentową wartość rocznego wzrostu tych kwot. Bez problemu można też stworzyć model oceniający warunki kredytu hipotecznego, gdzie naszymi dwoma czynnikami będą procent wkładu własnego i marża bankowa oferowana przez różnych kredytodawców, a wartością wyliczaną wysokość stałej raty łącznej. Możemy użyć rozbudowanej formuły wybierającej różne warunki dla różnych poziomów wkładu własnego. Suwakami możemy określać okres kredytowania oraz wysokość stóp procentowych. W ten sposób stworzymy przydatne i intuicyjne narzędzie do oceny możliwości kredytowych.
Zależało mi jednak na tym, aby w prosty sposób zaprezentować możliwość analizy wrażliwości w Excelu z wykorzystaniem wbudowanych narzędzi. Analiza inwestycji oparta na NPV nadawała się do tego idealnie. Zachęcam każdego do dalszych indywidualnych poszukiwań. Excel ma olbrzymie możliwości. Korzystajmy z nich, zamiast męczyć się z ręcznym liczeniem.
