Jak przeprowadzić analizę wrażliwości w Excelu: Krok po kroku

Aby przeprowadzić analizę wrażliwości w Excel, umieść każdą daną wejściową modelu w osobnej komórce. Następnie zmieniaj te dane i zapisuj, jak zmienia się wynik. Użyj tabeli danych z jedną zmienną dla jednego wejścia, tabeli z dwiema zmiennymi dla dwóch oraz Menedżera scenariuszy dla pełnych przypadków. Zakończ wykresem tornado, który szereguje dane wejściowe według ich wpływu.

Ten przewodnik wyjaśnia, czym jest analiza wrażliwości, jak skonfigurować model i jak korzystać z każdego narzędzia Excel. Zawiera on praktyczny przykład z rzeczywistymi formułami, szybszą metodę z wykorzystaniem AI oraz błędy, których należy unikać.

Czym jest analiza wrażliwości

Analiza wrażliwości pokazuje, jak bardzo zmienia się wynik, gdy zmienia się jedno założenie. Odpowiada na pytania typu „Co stanie się z zyskiem, jeśli cena spadnie o 10%?”.

Większość modeli biznesowych opiera się na domysłach. Wielkość sprzedaży, cena, koszty i wskaźniki wzrostu to wartości szacunkowe. Analiza wrażliwości sprawdza, które z tych domysłów mają największe znaczenie.

Excel grupuje te narzędzia w sekcji Analiza What-If. Microsoft definiuje ją jako „proces zmiany wartości w komórkach” w celu sprawdzenia, jak zmieniają się wyniki formuł.

Microsoft wymienia trzy wbudowane narzędzia: „Scenariusze, Szukaj wyniku i Tabele danych”. Każde z nich ma inne zadanie:

• Tabele danych pokazują jeden wynik dla wielu wartości jednej lub dwóch danych wejściowych.
• Menedżer scenariuszy zapisuje pełne zestawy danych wejściowych, takie jak wariant pesymistyczny i optymistyczny.
• Szukaj wyniku działa wstecz. Znajduje wartość wejściową potrzebną do osiągnięcia docelowego wyniku.

Analiza wrażliwości zazwyczaj wykorzystuje dwa pierwsze narzędzia. Szukaj wyniku pomaga w powiązanych kwestiach, takich jak cena progu rentowności.

Skonfiguruj model przed rozpoczęciem

Narzędzia Excel działają tylko wtedy, gdy model jest zbudowany przejrzyście. Najpierw sprawdź poniższe punkty.

Umieść każdą daną wejściową w osobnej komórce. Jednostki, cena, koszty i stawki potrzebują własnych komórek. Nie wpisuj liczb bezpośrednio w formułach.

Połącz wynik z danymi wejściowymi. Komórka wynikowa, np. zysk, musi być formułą odwołującą się do komórek wejściowych. Jeśli tak nie będzie, nic się nie zmieni, gdy Excel podstawi nowe wartości.

Trzymaj dane wejściowe razem. Pogrupuj je w jednym bloku na górze arkusza. Opisz każdą z nich i pokoloruj komórki wejściowe, aby się wyróżniały.

Wybierz jeden wynik do przetestowania. Analiza wrażliwości jest łatwiejsza do odczytania, gdy śledzi jeden parametr wyjściowy, taki jak zysk, wartość pieniężna netto (NPV) lub stan gotówki na koniec roku.

Zanotuj swój przypadek bazowy. Zapisz wartości początkowe. Każdy wynik w analizie jest porównywany z tą bazą.

Oto prosty model zysku, na którym można ćwiczyć. Jest to fikcyjny sklep rowerowy Northgate Cycles:

Komórka	Etykieta	Wartość
B1	Sprzedane jednostki	2,400
B2	Cena za jednostkę	$850
B3	Koszt zmienny na jednostkę	$510
B4	Koszty stałe	$520,000
B5	Zysk	=B1*(B2-B3)-B4

Przy tych wartościach bazowych zysk wynosi $296,000.

Jak wykonać analizę wrażliwości z jedną zmienną za pomocą tabeli danych

Tabela danych z jedną zmienną testuje zakres wartości dla jednej danej wejściowej. Microsoft twierdzi, że pokazuje ona, „jak różne wartości jednej zmiennej w jednej lub kilku formułach zmienią wyniki”.

Oto jak przetestować cenę w przykładowym modelu:

1. W komórkach od D2 do D6 wpisz ceny do przetestowania: $800, $825, $850, $875 i $900.
2. W komórce E1, jeden wiersz powyżej i jedną kolumnę na prawo od pierwszej ceny, wpisz =B5.
3. Zaznacz zakres D1:E6.
4. Na karcie Dane wybierz Analiza What-If > Tabela danych.
5. W polu Kolumnowa komórka wejściowa wprowadź B2 (komórka ceny). Pole Wierszowa komórka wejściowa pozostaw puste.
6. Kliknij OK.

Excel wypełni zakres E2:E6 zyskiem dla każdej ceny. Umiejscowienie formuły ma znaczenie. Microsoft zaleca wpisanie jej „jeden wiersz powyżej i jedną komórkę na prawo od kolumny wartości”.

Wyniki dla Northgate Cycles:

Cena	Zysk
$800	$176,000
$825	$236,000
$850	$296,000
$875	$356,000
$900	$416,000

Każda zmiana ceny o $25 zmienia zysk o $60,000. To jest właśnie wrażliwość zysku na cenę.

Jak wykonać analizę wrażliwości z dwiema zmiennymi

Tabela danych z dwiema zmiennymi testuje dwie dane wejściowe jednocześnie. Microsoft twierdzi, że pokazuje ona, „jak różne wartości dwóch zmiennych w jednej formule zmienią wyniki tej formuły”.

Oto jak przetestować cenę i liczbę jednostek razem:

1. W pustej komórce, np. D10, wpisz =B5.
2. Poniżej, w komórkach D11 do D13, wpisz wolumeny jednostek: 2,000, 2,400 i 2,800.
3. Po prawej stronie, w komórkach E10 do G10, wpisz ceny: $800, $850 i $900.
4. Zaznacz zakres D10:G13.
5. Wybierz Analiza What-If > Tabela danych.
6. W polu Wierszowa komórka wejściowa wprowadź B2. W polu Kolumnowa komórka wejściowa wprowadź B1.
7. Kliknij OK.

Wynikiem jest siatka zysków:

Jednostki \ Cena	$800	$850	$900
2,000	$60,000	$160,000	$260,000
2,400	$176,000	$296,000	$416,000
2,800	$292,000	$432,000	$572,000

Siatka pokazuje, że sklep pozostaje rentowny w każdej testowanej kombinacji. Najsłabszy wariant, 2,000 jednostek przy cenie $800, nadal przynosi $60,000 zysku.

Istnieje jedno sztywne ograniczenie. Microsoft podaje, że „Tabela danych nie może obsługiwać więcej niż dwóch zmiennych”. Dla większej liczby danych wejściowych należy użyć Menedżera scenariuszy.

Tabele danych są również przeliczane przy każdym przeliczeniu arkusza. W dużym skoroszycie może to spowolnić działanie Excel. Microsoft sugeruje ustawienie obliczeń tak, aby tabele danych były przeliczane tylko po naciśnięciu klawisza F9.

Jak porównywać pełne przypadki za pomocą Menedżera scenariuszy

Menedżer scenariuszy zapisuje kompletne zestawy danych wejściowych i podmienia je. Microsoft opisuje scenariusz jako „zestaw wartości, które Excel zapisuje i może automatycznie podstawiać w arkuszu”.

Oto jak skonfigurować trzy przypadki:

1. Na karcie Dane wybierz Analiza What-If > Menedżer scenariuszy.
2. Kliknij Dodaj, nazwij scenariusz Baza i wprowadź B1:B4 jako komórki zmieniające. Zachowaj bieżące wartości.
3. Dodaj przypadek Pesymistyczny: 2,000 jednostek, cena $825, koszt zmienny $530 i koszty stałe $540,000.
4. Dodaj przypadek Optymistyczny: 2,800 jednostek, cena $875, koszt zmienny $495 i koszty stałe $510,000.
5. Wybierz scenariusz i kliknij Pokaż, aby załadować jego wartości.
6. Kliknij Podsumowanie i wybierz B5 jako komórkę wyniku, aby wygenerować raport.

Dla Northgate Cycles podsumowanie pokazuje trzy poziomy zysku. Wariant pesymistyczny przynosi $50,000, bazowy $296,000, a optymistyczny $554,000.

Microsoft zauważa kilka ograniczeń. Każdy scenariusz może zawierać do 32 zmieniających się wartości. Raport podsumowujący nie jest automatyczny: „Raporty scenariuszy nie są automatycznie przeliczane”. Jeśli zmienisz scenariusz, utwórz nowe podsumowanie.

Jak znaleźć próg rentowności za pomocą narzędzia Szukaj wyniku

Analiza wrażliwości pokazuje, jak bardzo zmienia się wynik. Szukaj wyniku odpowiada na odwrotne pytanie: jaka wartość wejściowa daje docelowy wynik?

Microsoft twierdzi, że Szukaj wyniku „pobiera wynik i określa możliwe wartości wejściowe, które go generują”. Przykładem może być znalezienie stopy procentowej potrzebnej do spłaty kredytu.

Oto jak znaleźć cenę progu rentowności dla Northgate Cycles:

1. Na karcie Dane wybierz Analiza What-If > Szukaj wyniku.
2. W polu Ustaw komórkę wprowadź B5 (komórka zysku).
3. W polu Do wartości wprowadź 0.
4. W polu Zmieniając komórkę wprowadź B2 (komórka ceny).
5. Kliknij OK.

Excel zmienia cenę, aż zysk osiągnie zero. Przy 2,400 jednostkach i kosztach bazowych cena progu rentowności wynosi około $726.67. To o około 15% poniżej obecnej ceny $850.

Szukaj wyniku ma jedno ograniczenie. Microsoft podaje, że „działa tylko z jedną zmienną wartością wejściową”. W przypadku więcej niż jednej danej wejściowej Microsoft wskazuje na dodatek Solver.

Jak zbudować wykres tornado

Wykres tornado szereguje dane wejściowe według tego, jak bardzo zmieniają one wynik. Jest to najbardziej przejrzysty sposób prezentacji analizy wrażliwości.

Oto jak go zbudować:

1. Wybierz zakres testowy, np. każda dana wejściowa o 10% niższa i o 10% wyższa.
2. Zmieniaj po jednej danej wejściowej na raz i zapisuj niski oraz wysoki wynik. Pozostałe dane pozostaw na poziomie bazowym.
3. Umieść wyniki w tabeli z jednym wierszem dla każdej danej wejściowej.
4. Posortuj wiersze tak, aby dana o najszerszym zakresie zmian znalazła się na górze.
5. Wstaw wykres słupkowy niskich i wysokich wyników.

Dla Northgate Cycles, przy zmianie każdej danej o 10% w górę i w dół:

Dana wejściowa	Zysk przy −10%	Zysk przy +10%	Zakres
Cena	$92,000	$500,000	$408,000
Koszt zmienny	$418,400	$173,600	$244,800
Sprzedane jednostki	$214,400	$377,600	$163,200
Koszty stałe	$348,000	$244,000	$104,000

Cena ma największe znaczenie, i to ze znaczną przewagą. Spadek ceny o 10% obniża zysk z $296,000 do $92,000. Koszty stałe mają najmniejsze znaczenie. To mówi właścicielowi, na sprawdzaniu których założeń powinien skupić swój czas.

Wykres słupkowy w Excel rysuje słupki od lewej do prawej. Aby uzyskać kształt tornada, wykreśl zmianę każdej danej wejściowej względem zysku bazowego zamiast wartości surowych. Wtedy słupki dla niskiego wariantu będą skierowane w lewo, a dla wysokiego w prawo.

Jak wykonać analizę wrażliwości z AI

Budowanie tabel danych, scenariuszy i wykresu tornado dla dużego modelu zajmuje czas. Narzędzie AI może przeprowadzić te same testy bezpośrednio na Twoim skoroszycie.

Oto metoda w trzech krokach, przy użyciu Powerdrill Bloom.

Krok 1: Prześlij swój model

Zacznij od arkusza kalkulacyjnego, który zawiera Twój model. AI Sensitivity Analysis Generator prosi o przesłanie „DCF, business case, planu operacyjnego lub dowolnego modelu w arkuszu”.

Upewnij się, że dane wejściowe i komórka wynikowa są wyraźnie opisane. Narzędzie musi wiedzieć, które liczby są założeniami.

Krok 2: Wybierz widok wrażliwości w języku naturalnym

Opisz test, który chcesz przeprowadzić. Na przykład: „Pokaż wykres tornado dla zysku przy zmianie każdej danej wejściowej o 10% w górę i w dół. Dodaj tabelę dwukierunkową dla ceny i sprzedanych jednostek”.

Strona narzędzia wymienia cztery widoki: „tornado, dwukierunkowy, Monte Carlo lub próg rentowności”. Opisuje wykres tornado jako wykres „szeregujący dane wejściowe, które najbardziej zmieniają wynik”. Oferuje również „Tabelę wrażliwości dla dwóch najważniejszych założeń”.

Krok 3: Sprawdź wyniki i wyeksportuj analizę

Przed udostępnieniem porównaj jeden lub dwa wyniki z własną tabelą danych. Przypadek bazowy powinien dokładnie pasować do Twojego modelu.

Następnie wyeksportuj dane. Strona narzędzia oferuje „skoroszyt Excel, markowy PDF lub link do obszaru roboczego”. W przypadku prezentacji narzędzie Excel to PPT tool zamieni ten sam plik w slajdy. Strona AI Financial Modeling opisuje proces budowania samego modelu.

Jak czytać i prezentować wyniki

Analiza wrażliwości jest przydatna tylko wtedy, gdy ludzie podejmują na jej podstawie działania. Pomocne są poniższe nawyki.

Zacznij od rankingu. Rozpocznij od wykresu tornado. Pokazuje on na jednym obrazku, które założenia napędzają wynik.

Przełóż zakresy na decyzje. „Zysk spadnie do $92,000, jeśli cena spadnie o 10%” to fakt. „Powinniśmy przetestować cenę przed podpisaniem umowy najmu” to decyzja.

Pokaż próg rentowności. Połącz analizę z wartością, przy której wynik osiąga zero. Dla Northgate Cycles próg rentowności to około 1,530 jednostek przy bazowej cenie i kosztach. Nasz przewodnik po analizie progu rentowności w Excel opisuje te obliczenia.

Używaj realistycznych zakresów. Zmiana o 10% pasuje do stabilnych danych. Wolumen nowego produktu może wymagać znacznie szerszego zakresu.

Określ, co pozostało stałe. Każdy test typu „jeden na raz” zakłada, że pozostałe dane są na poziomie bazowym. Zaznacz to na slajdzie lub w przypisie. Czytelnicy będą wiedzieć, że 10% zmiana ceny zakłada ten sam wolumen, koszty i wydatki stałe. Ta jedna linijka zapobiega większości pytań o metodologię.

Wróć do analizy po uzyskaniu wyników. Gdy pojawią się rzeczywiste liczby, porównaj je ze swoimi zakresami. Raport budżet vs. wykonanie jest naturalnym krokiem po analizie.

Typowe błędy w analizie wrażliwości

Te błędy są powszechne i łatwe do uniknięcia.

Liczby wpisane na sztywno w formułach. Jeśli formuła zawiera 850 zamiast B2, tabela danych nie może tego zmienić. Trzymaj każdą daną wejściową w komórce.

Testowanie zbyt wielu danych naraz. Tabela danych obsługuje jedną lub dwie. Dla większej liczby przejdź do Menedżera scenariuszy lub wykresu tornado.

Używanie tego samego zakresu dla każdej danej. Cena i wolumen rzadko niosą ze sobą taką samą niepewność. Wybieraj zakresy, które odzwierciedlają Twoją rzeczywistą wiedzę.

Ignorowanie powiązań między danymi. Niższa cena może zwiększyć wolumen. Test „jeden na raz” to pomija, więc dodaj scenariusz, który zmienia oba parametry.

Nieaktualne podsumowania scenariuszy. Raporty scenariuszy nie aktualizują się same. Przebuduj je po każdej zmianie.

Zapominanie o przypadku bazowym. Każda tabela powinna pokazywać wynik bazowy, aby czytelnicy mogli zobaczyć skalę każdej zmiany.

Gdy będziesz gotowy do przetestowania swojego kolejnego modelu, wypróbuj Powerdrill Bloom ze skoroszytem, którego używasz do planowania.

Często zadawane pytania

Co to jest analiza wrażliwości w Excel?

To sposób na sprawdzenie, jak zmienia się wynik, gdy zmieniają się jego dane wejściowe. W Excel używa się narzędzi Analizy What-If, głównie tabel danych i Menedżera scenariuszy, aby podstawiać nowe wartości wejściowe i zapisywać wyniki.

Jak wykonać analizę wrażliwości z jedną zmienną w Excel?

Wypisz wartości wejściowe w kolumnie. Umieść formułę odwołującą się do wyniku jeden wiersz powyżej i jedną kolumnę na prawo. Zaznacz zakres, wybierz Analiza What-If > Tabela danych i wprowadź komórkę wejściową jako kolumnową komórkę wejściową.

Jaka jest różnica między tabelą danych a Menedżerem scenariuszy?

Tabela danych testuje jedną lub dwie zmienne dla wielu wartości i pokazuje wszystkie wyniki w jednej siatce. Menedżer scenariuszy zapisuje pełne zestawy danych wejściowych (do 32 zmieniających się wartości każdy) i podmienia je po kolei.

Jak zrobić wykres tornado w Excel?

Zmieniaj każdą daną wejściową w górę i w dół o tę samą wartość, po jednej na raz, i zapisuj wynik. Posortuj dane według wielkości zakresu zmian, a następnie przedstaw zmianę każdej danej względem wyniku bazowego jako poziome słupki.

Czy AI może wykonać analizę wrażliwości?

Tak. Narzędzie AI może odczytać Twój model w arkuszu, przeprowadzić testy tornado i dwukierunkowe oraz wyeksportować wyniki jako skoroszyt lub raport. Nadal musisz sprawdzić przypadek bazowy i wybrać realistyczne zakresy.

Źródła: Microsoft Support, Wprowadzenie do analizy What-If · Microsoft Support, Obliczanie wielu wyników przy użyciu tabeli danych · Microsoft Support, Przełączanie między różnymi zestawami wartości przy użyciu scenariuszy.