Analiza wrażliwości w programie Excel (przykładowy arkusz danych)

Procesy w obszarze finansów są zawsze ze sobą powiązane – jeden czynnik zależy od drugiego i zmienia się wraz z nim. Śledź te zmiany i dowiedz się, czego możesz się spodziewać w przyszłości, być może za pomocą funkcji programu Excel i metod arkusza kalkulacyjnego.

Uzyskiwanie wielu wyników za pomocą tabeli danych

Funkcje arkusza danych to elementy analizy warunkowej — często wykonywanej za pomocą programu Microsoft Excel. To drugie określenie analizy wrażliwości.

Przegląd

Tabela danych to rodzaj zakresu komórek, który można wykorzystać do rozwiązywania problemów poprzez zmianę wartości w niektórych komórkach. Jest tworzony, gdy konieczne jest śledzenie zmian w składnikach formuły i otrzymywanie aktualizacji wyników zgodnie z tymi zmianami. Dowiedzmy się, jak korzystać z tabel danych w badaniach i jakie to typy.

Podstawy dotyczące tabel danych

Istnieją dwa rodzaje tabel danych, różnią się one liczbą elementów. Musisz skompilować tabelę z naciskiem na liczbę wartości uXNUMXbuXNUMXbktóre musisz z nią sprawdzić.

Statystycy używają tabeli z jedną zmienną, gdy w co najmniej jednym wyrażeniu znajduje się tylko jedna zmienna, która może zmienić ich wynik. Na przykład jest często używany w połączeniu z funkcją PMT. Formuła ma na celu obliczenie kwoty regularnej płatności i uwzględnia oprocentowanie określone w umowie. W takich obliczeniach zmienne są zapisywane w jednej kolumnie, a wyniki obliczeń w drugiej. Przykład tabliczki znamionowej z 1 zmienną:

Następnie rozważ tablice z 2 zmiennymi. Stosuje się je w przypadkach, gdy dwa czynniki wpływają na zmianę dowolnego wskaźnika. Obie zmienne mogą trafić do kolejnej tabeli związanej z pożyczką, która może posłużyć do określenia optymalnego okresu spłaty oraz wysokości miesięcznej raty. W tych obliczeniach musisz również użyć funkcji PMT. Przykład tabeli z 2 zmiennymi:

Tworzenie tabeli danych z jedną zmienną

Rozważ metodę analizy na przykładzie małej księgarni, która ma na stanie tylko 100 książek. Niektóre z nich można sprzedać drożej (50 dolarów), reszta będzie kosztować kupujących mniej (20 dolarów). Liczy się łączny dochód ze sprzedaży wszystkich towarów – właściciel zdecydował, że sprzeda 60% książek po wysokiej cenie. Musisz dowiedzieć się, jak wzrosną przychody, jeśli podniesiesz cenę większej ilości towaru – 70% i tak dalej.

Uwaga! Całkowity dochód należy obliczyć za pomocą formuły, w przeciwnym razie nie będzie możliwe skompilowanie tabeli danych.

1. Wybierz wolną komórkę z dala od krawędzi arkusza i wpisz w niej formułę: =Komórka całkowitego dochodu. Na przykład, jeśli dochód jest zapisany w komórce C14 (wskazano losowe oznaczenie), musisz napisać to: =S14.
2. Procentową ilość towaru piszemy w kolumnie po lewej stronie tej komórki – nie pod nią, to bardzo ważne.
3. Wybieramy zakres komórek, w których znajduje się kolumna procentowa i link do całkowitego dochodu.

4. W zakładce „Dane” znajdujemy pozycję „Co jeśli analiza” i klikamy ją – w menu, które się otworzy, wybierz opcję „Tabela danych”.

5. Otworzy się małe okienko, w którym w kolumnie „Zastąp wartości wierszami w…” należy podać komórkę z procentem książek sprzedanych początkowo po wysokiej cenie. Ten krok jest wykonywany w celu ponownego obliczenia całkowitego dochodu z uwzględnieniem rosnącego procentu.

Po kliknięciu przycisku „OK” w oknie, w którym wprowadzono dane do zestawienia tabeli, w wierszach pojawią się wyniki obliczeń.

Dodawanie formuły do ​​tabeli danych pojedynczej zmiennej

Z tabeli, która pomogła obliczyć działanie tylko z jedną zmienną, możesz stworzyć zaawansowane narzędzie analityczne, dodając dodatkową formułę. Należy go wpisać obok już istniejącej formuły – na przykład, jeśli tabela jest zorientowana wierszowo, wpisujemy wyrażenie w komórce po prawej stronie już istniejącej. Po ustawieniu orientacji kolumny wpisujemy nową formułę pod starą. Następnie postępuj zgodnie z algorytmem:

1. Wybierz ponownie zakres komórek, ale teraz powinien zawierać nową formułę.
2. Otwórz menu analizy „co jeśli” i wybierz „Arkusz danych”.
3. Dodajemy nową formułę do odpowiedniego pola w wierszach lub kolumnach, w zależności od orientacji płytki.

Utwórz tabelę danych z dwiema zmiennymi

Początek takiej tabeli jest nieco inny – nad wartościami procentowymi należy umieścić link do całkowitego przychodu. Następnie wykonujemy następujące kroki:

1. Napisz opcje cenowe w jednym wierszu z linkiem do dochodu – po jednej komórce na każdą cenę.
2. Wybierz zakres komórek.

3. Otwórz okno tabeli danych, tak jak przy kompilacji tabeli z jedną zmienną – poprzez zakładkę „Dane” na pasku narzędzi.
4. Zastąp w kolumnie „Zastąp wartości kolumnami w…” komórkę z początkową wysoką ceną.
5. Dodaj komórkę z początkowym procentem sprzedaży drogich książek do kolumny „Zastąp wartości wierszami w…” i kliknij „OK”.

W rezultacie cała tabela jest wypełniona kwotami możliwych dochodów z różnymi warunkami sprzedaży towarów.

Przyspieszenie obliczeń dla arkuszy roboczych zawierających tabele danych

Jeśli potrzebujesz szybkich obliczeń w tabeli danych, które nie powodują ponownego obliczenia całego skoroszytu, możesz zrobić kilka rzeczy, aby przyspieszyć ten proces.

1. Otwórz okno opcji, wybierz pozycję „Formuły” w menu po prawej stronie.
2. Wybierz pozycję „Automatycznie, z wyjątkiem tabel danych” w sekcji „Obliczenia w skoroszycie”.

3. Przeliczmy wyniki w tabeli ręcznie. Aby to zrobić, wybierz formuły i naciśnij klawisz F.

Inne narzędzia do wykonywania analizy wrażliwości

W programie dostępne są inne narzędzia ułatwiające przeprowadzanie analizy wrażliwości. Automatyzują niektóre czynności, które w innym przypadku musiałyby być wykonywane ręcznie.

1. Funkcja „Wybór parametrów” jest odpowiednia, jeśli pożądany wynik jest znany, a do uzyskania takiego wyniku konieczna jest znajomość wartości wejściowej zmiennej.
2. „Szukaj rozwiązania” to dodatek do rozwiązywania problemów. Konieczne jest ustalenie limitów i wskazanie ich, po czym system znajdzie odpowiedź. Rozwiązanie określa się zmieniając wartości.
3. Analizę wrażliwości można przeprowadzić za pomocą Menedżera scenariuszy. To narzędzie znajduje się w menu analizy warunkowej na karcie Dane. Podmienia wartości w kilku komórkach – liczba może sięgać 32. Dyspozytor porównuje te wartości, aby użytkownik nie musiał ich ręcznie zmieniać. Przykład wykorzystania menedżera skryptów:

Analiza wrażliwości projektu inwestycyjnego w Excelu

Analiza warunkowa jest szczególnie przydatna w sytuacjach, w których wymagane jest prognozowanie, takich jak inwestycje. Analitycy wykorzystują tę metodę, aby dowiedzieć się, jak zmieni się wartość akcji firmy w wyniku zmian niektórych czynników.

Metoda analizy wrażliwości inwestycyjnej

Analizując „co jeśli” stosuj enumerację – ręczną lub automatyczną. Zakres wartości jest znany i są one podstawiane do wzoru jeden po drugim. Rezultatem jest zestaw wartości. Wybierz z nich odpowiednią liczbę. Rozważmy cztery wskaźniki, dla których przeprowadzana jest analiza wrażliwości w obszarze finansów:

1. Wartość bieżąca netto — obliczana przez odjęcie kwoty inwestycji od kwoty dochodu.
2. Wewnętrzna stopa zwrotu/zysku – wskazuje, ile zysku trzeba uzyskać z inwestycji w ciągu roku.
3. Wskaźnik zwrotu to stosunek wszystkich zysków do początkowej inwestycji.
4. Wskaźnik zdyskontowanych zysków – wskazuje na efektywność inwestycji.

Formuła

Czułość osadzania można obliczyć za pomocą tego wzoru: Zmiana parametru wyjściowego w % / Zmiana parametru wejściowego w %.

Parametrami wyjściowymi i wejściowymi mogą być wartości opisane wcześniej.

1. Musisz znać wynik w standardowych warunkach.
2. Zamieniamy jedną ze zmiennych i monitorujemy zmiany w wyniku.
3. Obliczamy procentową zmianę obu parametrów w stosunku do ustalonych warunków.
4. Otrzymane wartości procentowe wstawiamy do wzoru i określamy czułość.

Przykład analizy wrażliwości projektu inwestycyjnego w Excelu

Dla lepszego zrozumienia metodologii analizy potrzebny jest przykład. Przeanalizujmy projekt z następującymi znanymi danymi:

1. Wypełnij tabelę, aby przeanalizować projekt na niej.

2. Przepływy pieniężne obliczamy za pomocą funkcji OFFSET. Na początkowym etapie przepływ jest równy inwestycjom. Następnie stosujemy formułę: =JEŻELI(OFFSET(Liczba;1;)=2;SUM(Wpływ 1:Wypływ 1); SUMA(Wpływ 1:Wypływ 1)+$B$ 5)
   Oznaczenia komórek w formule mogą się różnić w zależności od układu tabeli. Na koniec dodawana jest wartość z danych początkowych – wartość ratownictwa.

3. Określamy okres, za jaki projekt się opłaci. Dla początkowego okresu używamy tego wzoru: =PODSUMOWANIE(G7: G17;»<0″). Zakres komórek to kolumna przepływów pieniężnych. Dla dalszych okresów stosujemy ten wzór: =Okres początkowy+JEŻELI(Pierwszy e.strumień>0; Pierwszy e.strumień;0). Projekt jest na granicy opłacalności za 4 lata.

4. Tworzymy kolumnę na numery tych okresów, kiedy projekt się opłaca.

5. Obliczamy zwrot z inwestycji. Konieczne jest wyrażenie, w którym zysk w danym okresie dzieli się przez początkową inwestycję.

6. Współczynnik dyskontowy określamy za pomocą tego wzoru: =1/(1+dysk.%) ^Numer.

7. Wartość bieżącą obliczamy za pomocą mnożenia – przepływ pieniężny mnożymy przez współczynnik dyskonta.

8. Obliczmy PI (wskaźnik rentowności). Wartość bieżąca w czasie jest dzielona przez inwestycję na początku projektu.

9. Zdefiniujmy wewnętrzną stopę zwrotu za pomocą funkcji IRR: =IRR(Zakres przepływów pieniężnych).

Analiza wrażliwości inwestycyjnej za pomocą arkusza danych

Do analizy projektów z zakresu inwestycji lepiej nadają się inne metody niż tabela danych. Wielu użytkowników doświadcza zamieszania podczas kompilowania formuły. Aby poznać zależność jednego czynnika od zmian w innych, musisz wybrać odpowiednie komórki do wprowadzania obliczeń i odczytu danych.

Analiza czynnikowa i dyspersyjna w Excelu z automatyzacją obliczeń

Inną typologią analizy wrażliwości jest analiza czynnikowa i analiza wariancji. Pierwszy typ określa relację między liczbami, drugi ujawnia zależność jednej zmiennej od innych.

ANOVA w Excelu

Celem takiej analizy jest podzielenie zmienności wartości na trzy składowe:

1. Zmienność w wyniku wpływu innych wartości.
2. Zmiany wynikające z relacji wartości, które na nią wpływają.
3. Losowe zmiany.

Przeprowadźmy analizę wariancji za pomocą dodatku Excel „Analiza danych”. Jeśli nie jest włączony, można go włączyć w ustawieniach.

Początkowa tabela musi być zgodna z dwiema regułami: dla każdej wartości jest jedna kolumna, a dane w niej są ułożone w porządku rosnącym lub malejącym. Konieczne jest sprawdzenie wpływu poziomu wykształcenia na zachowania w konflikcie.

1. Znajdź narzędzie Analiza danych w zakładce Dane i otwórz jego okno. Z listy musisz wybrać jednokierunkową analizę wariancji.

2. Wypełnij wiersze okna dialogowego. Interwał wejściowy to wszystkie komórki, z wyłączeniem nagłówków i liczb. Grupuj według kolumn. Wyniki wyświetlamy na nowym arkuszu.

Ponieważ wartość w żółtej komórce jest większa niż jeden, założenie można uznać za błędne – nie ma związku między wykształceniem a zachowaniem w konflikcie.

Analiza czynnikowa w Excelu: przykład

Przeanalizujmy relację danych w zakresie sprzedaży – konieczne jest zidentyfikowanie popularnych i niepopularnych produktów. Informacje wstępne:

1. Musimy dowiedzieć się, na które towary w ciągu drugiego miesiąca najbardziej wzrósł popyt. Przygotowujemy nową tabelę, aby określić wzrost i spadek popytu. Wzrost obliczany jest za pomocą tego wzoru: =JEŻELI((Zapotrzebowanie 2–Zapotrzebowanie 1)>0; Zapotrzebowanie 2–Zapotrzebowanie 1;0). Zmniejsz formułę: =JEŻELI(Wzrost=0; Zapotrzebowanie 1- Zapotrzebowanie 2;0).

2. Oblicz wzrost popytu na towary w procentach: =JEŻELI(Wzrost/Wynik 2 =0; Spadek/Wynik 2; Wzrost/Wynik 2).

3. Zróbmy wykres dla przejrzystości – wybierz zakres komórek i utwórz histogram za pomocą zakładki „Wstaw”. W ustawieniach musisz usunąć wypełnienie, można to zrobić za pomocą narzędzia Formatuj serię danych.

Dwukierunkowa analiza wariancji w Excelu

Analiza wariancji prowadzona jest z kilkoma zmiennymi. Rozważ to na przykładzie: musisz dowiedzieć się, jak szybko reakcja na dźwięk o różnej głośności objawia się u mężczyzn i kobiet.

1. Otwieramy „Analiza danych”, na liście musisz znaleźć dwukierunkową analizę wariancji bez powtórzeń.
2. Interwał wejściowy – komórki zawierające dane (bez nagłówka). Wyświetlamy wyniki na nowym arkuszu i klikamy „OK”.

Wartość F jest większa niż F-krytyczna, co oznacza, że ​​podłoga wpływa na szybkość reakcji na dźwięk.

Wnioski

W tym artykule szczegółowo omówiono analizę wrażliwości w arkuszu kalkulacyjnym Excel, aby każdy użytkownik mógł zrozumieć metody jej stosowania.