Analiza warunkowa w programie Excel

Co to jest analiza warunkowa w programie Excel?

Analiza warunkowa w programie Excel to narzędzie, które pomaga nam tworzyć różne modele, scenariusze, tabele danych. W tym artykule przyjrzymy się sposobom korzystania z analizy warunkowej.

Mamy 3 części analizy warunkowej w programie Excel. Są one następujące:

  1. Menedżer scenariuszy
  2. Szukaj celu w programie Excel
  3. Tabela danych w programie Excel
Możesz pobrać ten szablon analizy warunkowej programu Excel tutaj - szablon analizy warunkowej programu Excel

Menedżer scenariuszy nr 1 w analizie warunkowej

Jako szef biznesu ważne jest, aby znać różne scenariusze przyszłego projektu. Na podstawie scenariuszy szef biznesu podejmie decyzje. Na przykład podejmiesz jeden z ważnych projektów. Odrobiłeś pracę domową i wypisałeś wszystkie możliwe wydatki z twojego końca, a poniżej znajduje się lista wszystkich twoich wydatków.

Oczekiwany przepływ środków pieniężnych z tego projektu wynosi 75 milionów, co znajduje się w komórce C2. Łączne wydatki obejmują wszystkie wydatki stałe i zmienne, a całkowity koszt w komórce C12 wynosi 57,45 mln. Całkowity zysk wynosi 17,55 miliona w komórce C14, a% zysku to 23,40% wpływów pieniężnych.

To jest podstawowy scenariusz twojego projektu. Teraz musisz znać scenariusz zysków, jeśli niektóre z Twoich wydatków wzrosną lub spadną.

Scenariusz 1

  • W ogólnym scenariuszu oszacowałeś koszt licencji na projekt na 10 milionów, ale na pewno spodziewasz się na 15 milionów
  • Koszt surowców ma wzrosnąć o 2,5 mln
  • Pozostałe wydatki mają zostać zmniejszone o 50 tys.

Scenariusz 2

  • Koszt projektu wyniesie 20 milionów.
  • Dzienne zarobki pracowników mają wynosić 5 milionów
  • Koszt operacyjny ma wynieść 3,5 miliona.

Teraz w formularzu wypisałeś wszystkie scenariusze. W oparciu o te scenariusze musisz utworzyć tabelę scenariuszy, w jaki sposób wpłynie to na Twój zysk i% zysku. Aby stworzyć scenariusze analizy warunkowej, wykonaj poniższe kroki.

  • Krok 1: Przejdź do DATA> Analiza warunkowa> Menedżer scenariuszy.

  • Krok 2: Po kliknięciu Menedżera scenariuszy pojawi się poniżej okna dialogowego.

  • Krok 3: Kliknij DODAJ. Nadaj nazwę Scenariuszowi.

  • Krok 4: Zmieniając komórki, wybierz pierwsze zmiany scenariusza, które wymieniłeś. Zmiany to Koszt projektu (komórka C10) na poziomie 15 milionów, Koszt surowców (komórka C7) na 11 milionów i inne wydatki (komórka C11) na 4,5 miliona. Wspomnij o tych 3 komórkach.

  • Krok 5: Kliknij OK. Poprosi Cię o podanie nowych wartości, podanie nowych wartości, jak podano w scenariuszu 1.

  • Krok 6: Nie klikaj OK, ale kliknij OK Dodaj. To zapisze ten scenariusz za Ciebie.
  • Krok 7: Teraz poprosi Cię o utworzenie jeszcze jednego scenariusza. Jak wymieniliśmy w scenariuszu 2, wprowadź zmiany. Tym razem musimy zmienić koszt projektu (C10), koszt pracy (C8) i koszt operacyjny (C9)

  • Krok 8: Teraz dodaj tutaj nowe wartości.

  • Krok 9: Teraz kliknij OK. Pokaże wszystkie stworzone przez nas scenariusze.

  • Krok 10: Kliknij PODSUMOWANIE, zapyta Cię, które komórki wynikowe chcesz zmienić. Tutaj musimy zmienić komórkę całkowitego kosztu (C12), komórkę całkowitego zysku (C14) i komórkę zysku% (C16).

  • Krok 11: Kliknij OK. Stworzy dla Ciebie raport podsumowujący w nowym arkuszu.

Total excel stworzył 3 scenariusze, mimo że dostarczyliśmy tylko 2 zmiany scenariuszy. Ponieważ program Excel pokaże istniejące raporty jako jeden scenariusz.

Z tej tabeli możemy łatwo zobaczyć wpływ zmian na procent naliczania zysku.

# 2 Szukaj celu w analizie warunkowej

Teraz znamy przewagę menedżera scenariuszy. Analiza typu „co, jeśli”, „Poszukiwanie celu” może powiedzieć Ci, co musisz zrobić, aby osiągnąć wyznaczony cel.

Andrew jest uczniem klasy 10. Jego celem jest uzyskanie średniej oceny 85 punktów na egzaminie końcowym i zdał już 5 egzaminów, a na koniec zdał tylko 1 egzamin. W ukończonych 5 egzaminach przewidywane wyniki są następujące.

Aby obliczyć bieżącą średnią, zastosuj formułę Średnia w komórce B7.

Obecna średnia wynosi 82,2.

GOAL Andrzeja to 85, a jego obecna średnia to 82,2 i krótszy o 3,8 na jeden egzamin.

Teraz pytanie brzmi, ile musi uzyskać na egzaminie końcowym, aby ostatecznie uzyskać ogólną średnią 85. Można to sprawdzić za pomocą narzędzia do analizy typu „co, jeśli”.

  • Krok 1: Przejdź do DANE> Analiza warunkowa> Szukanie celu

  • Krok 2: Wyświetli się poniżej okna dialogowego.

  • Krok 3: Tutaj musimy najpierw ustawić komórkę. Set Cell to nic innego jak komórka, której potrzebujemy ostateczny wynik, czyli nasza ogólna średnia komórka (B7). Następny jest To Value, to nic innego, jak tylko wartość potrzebna do ustawienia komórki, tj. Ogólny średni GOAL Andrew (85).

Następną i ostatnią częścią jest zmiana komórki, w której chcesz zobaczyć wpływ. Musimy więc zmienić komórkę B6, która jest komórką dla wyniku końcowego badanego.

  • Krok 4: Kliknij OK. Excel zajmie kilka sekund, aby zakończyć proces, ale ostatecznie pokaże wynik końcowy podobny do poniższego.

Teraz mamy tutaj nasze wyniki. Aby uzyskać ogólną średnią 85 punktów, Andrew musi uzyskać 99 punktów z egzaminu końcowego.

# 3 Tabela danych w analizie warunkowej

Widzieliśmy już dwie wspaniałe techniki w ramach analizy warunkowej w programie Excel. Tabela danych może tworzyć różne tabele scenariuszy w oparciu o zmianę zmiennej. Mamy tutaj dwa rodzaje tabel danych „Jedna tabela danych zmiennych” i „Tabela danych z dwiema zmiennymi”. W tym artykule pokażę jedną tabelę danych zmiennych Excel.

Załóżmy, że sprzedajesz 1000 produktów po 15 rupii, całkowity przewidywany koszt to 12500, a zysk to 2500.

Nie jesteś zadowolony z osiąganych zysków. Twój przewidywany zysk to 7500. Zdecydowałeś się zwiększyć swoją cenę jednostkową, aby zwiększyć swój zysk, ale nie wiesz, ile musisz zwiększyć.

Tabele danych mogą Ci pomóc. Utwórz poniższą tabelę

Teraz w komórce F1 podaj link do komórki całkowitego zysku, czyli B6

  • Krok 1: Wybierz nowo utworzoną tabelę.

  • Krok 2: Przejdź do DATA> Analiza warunkowa> Tabela danych.

  • Krok 3: Teraz zobaczysz poniższe okno dialogowe.

  • Krok 4: Ponieważ pokazujemy wynik pionowo, opuść komórkę wejściową Row. W kolumnie wejściowej wybierz komórkę B2, która jest oryginalną ceną sprzedaży.

  • Krok 5: Kliknij OK, aby uzyskać wyniki. W nowej tabeli pojawi się lista zysków.

Mamy więc gotową tabelę danych. Jeśli chcesz zarobić od 7500, musisz sprzedać po 20 za sztukę.

Rzeczy do zapamiętania 

  • Tabelę danych analizy typu „co, jeśli” można wykonać z dwiema zmiennymi zmianami. Zapoznaj się z naszym artykułem na temat analizy typu „co, jeśli” - tabeli danych dla dwóch zmiennych.
  • Analiza typu „co, jeśli” Wyszukiwanie celu zajmuje kilka sekund, aby wykonać obliczenia.
  • Menedżer scenariuszy analizy typu „co, jeśli” może przedstawić podsumowanie zawierające łącznie liczby wejściowe i bieżące wartości.