Analiza warunkowa z Menedżerem scenariuszy

Menedżer scenariuszy jest przydatny w przypadkach, gdy w analizie wrażliwości masz więcej niż dwie zmienne. Menedżer scenariuszy tworzy scenariusze dla każdego zestawu wartości wejściowych dla rozważanych zmiennych. Scenariusze pomagają zbadać zestaw możliwych wyników, wspierając następujące -

• Zmienne aż 32 zestawy wejść.
• Scalanie scenariuszy z kilku różnych arkuszy lub skoroszytów.

Jeśli chcesz przeanalizować więcej niż 32 zestawy wejściowe, a wartości reprezentują tylko jedną lub dwie zmienne, możesz użyć tabel danych. Chociaż jest ograniczona tylko do jednej lub dwóch zmiennych, tabela danych może zawierać dowolną liczbę różnych wartości wejściowych. Zapoznaj się z sekcją Analiza warunkowa z tabelami danych w tym samouczku.

Scenariusze

Scenariusz to zestaw wartości, które program Excel zapisuje i może automatycznie zastąpić w arkuszu. Możesz tworzyć i zapisywać różne grupy wartości jako scenariusze w arkuszu, a następnie przełączać się między tymi scenariuszami, aby wyświetlić różne wyniki.

Na przykład możesz mieć kilka różnych scenariuszy budżetowych, które porównują różne możliwe poziomy dochodów i wydatków. Możesz również mieć różne scenariusze pożyczek z różnych źródeł, które porównują różne możliwe stopy procentowe i okresy trwania pożyczki.

Jeśli informacje, których chcesz użyć w scenariuszach, pochodzą z różnych źródeł, możesz zebrać informacje w oddzielnych skoroszytach, a następnie scalić scenariusze z różnych skoroszytów w jeden.

Po przygotowaniu wszystkich potrzebnych scenariuszy możesz utworzyć raport podsumowujący scenariusze -

• Obejmuje to informacje ze wszystkich scenariuszy.
• To pozwala porównać scenariusze obok siebie.

Menedżer scenariuszy

Menedżer scenariuszy jest jednym z narzędzi analizy warunkowej w programie Excel.

Aby utworzyć raport z analizy za pomocą Menedżera scenariuszy, musisz wykonać następujące kroki -

Step 1 - Zdefiniuj zestaw wartości początkowych i zidentyfikuj komórki wejściowe, które chcesz zmieniać, zwane komórkami zmieniającymi.

Step 2 - Utwórz każdy scenariusz, nazwij scenariusz i wprowadź wartość dla każdej zmieniającej się komórki wejściowej dla tego scenariusza.

Step 3- Wybierz komórki wyjściowe, zwane komórkami wynikowymi, które chcesz śledzić. Te komórki zawierają formuły w początkowym zestawie wartości. Formuły wykorzystują zmieniające się komórki wejściowe.

Menedżer scenariuszy tworzy raport zawierający wartości wejściowe i wyjściowe dla każdego scenariusza.

Początkowe wartości scenariuszy

Zanim utworzysz kilka różnych scenariuszy, musisz zdefiniować zestaw wartości początkowych, na których scenariusze będą oparte.

Kroki konfigurowania wartości początkowych dla Scenariuszy są następujące:

• Zdefiniuj komórki zawierające wartości wejściowe.
• Nazwij odpowiednio komórki wejściowe.
• Zidentyfikuj komórki wejściowe za pomocą stałych wartości.
• Określ wartości stałych wejść.
• Zidentyfikuj komórki wejściowe ze zmieniającymi się wartościami.
• Określ wartości początkowe dla zmieniających się danych wejściowych.
• Zdefiniuj komórki zawierające wyniki. Wynikowe komórki zawierają formuły.
• Nazwij odpowiednio komórki wynikowe.
• Umieść formuły w komórkach wynikowych.

Rozważmy poprzedni przykład pożyczki. Teraz wykonaj następujące czynności -

• Zdefiniuj komórkę dla kwoty pożyczki.

  • Ta wartość wejściowa jest stała dla wszystkich scenariuszy.

  • Nazwij komórkę Kwota_pożyczki.

  • Określ wartość jako 5 000 000.

• Zdefiniuj komórki oprocentowania, liczby płatności i rodzaju (płatność na początku lub na końcu miesiąca).

  • Te wartości wejściowe będą się zmieniać w różnych scenariuszach.

  • Nazwij komórki Interest_Rate, NPER i Type.

  • Określ początkowe wartości analizy w tych komórkach jako odpowiednio 12%, 360 i 0.

• Zdefiniuj komórkę dla EMI.

  • To jest wartość wyniku.

  • Nazwij komórkę EMI.

  • Umieść formułę w tej komórce jako -

    =PMT (Interest_Rate/12, NPER, Loan_Amount, 0, Type)

Twój arkusz wygląda jak pokazano poniżej -

Jak widać, komórki wejściowe i komórki wynikowe znajdują się w kolumnie C z nazwami podanymi w kolumnie D.

Tworzenie scenariuszy

Po skonfigurowaniu wartości początkowych dla Scenariuszy, można tworzyć scenariusze za pomocą Menedżera scenariuszy w następujący sposób -

• Kliknij kartę DANE na Wstążce.
• Kliknij opcję Analiza warunkowa w grupie Narzędzia danych.
• Wybierz Menedżera scenariuszy z listy rozwijanej.

Zostanie wyświetlone okno dialogowe Menedżer scenariuszy. Możesz zauważyć, że zawiera wiadomość -

“No Scenarios defined. Choose Add to.”

Musisz utworzyć scenariusze dla każdego zestawu zmieniających się wartości w Menedżerze scenariuszy. Dobrze jest mieć zdefiniowany pierwszy scenariusz z wartościami początkowymi, ponieważ umożliwia to powrót do wartości początkowych w dowolnym momencie podczas wyświetlania różnych scenariuszy.

Utwórz pierwszy scenariusz z wartościami początkowymi w następujący sposób -

• Kliknij Add w oknie dialogowym Menedżer scenariuszy.

Plik Add Scenario pojawi się okno dialogowe.

• W polu Nazwa scenariusza wpisz Scenariusz 1.
• W polu Zmiana komórek wprowadź odwołania do komórek, np. C3, C4 i C5 z wciśniętym klawiszem Ctrl.

Nazwa okna dialogowego zmieni się na Edytuj scenariusz.

• Edytuj tekst w Comment as – Initial Values pudełko.

• Wybierz opcję Zapobiegaj zmianom w obszarze Ochrona, a następnie kliknij przycisk OK.

Plik Scenario Valuespojawi się okno dialogowe. Wstępne wartości, które zdefiniowałeś, pojawią się w każdym z pól zmieniających się komórek.

Scenario 1 z wartościami początkowymi.

Utwórz trzy kolejne scenariusze z różnymi wartościami w zmieniających się komórkach w następujący sposób -

• Kliknij Add w oknie dialogowym Wartości scenariusza.

Pojawi się okno dialogowe Dodaj scenariusz. Zwróć uwagę, że C3, C4, C5 pojawiają się w polu Zmiana komórek.

• W polu Nazwa scenariusza wpisz Scenariusz 2.

• Edytuj tekst w Comment as - Inna stopa procentowa.

• Wybierz Zapobiegaj zmianom w sekcji Ochrona i kliknij OK.

Plik Scenario Valuespojawi się okno dialogowe. Początkowe wartości pojawiają się w zmieniających się komórkach. Zmień wartośćInterest_Rate do 0.13 i kliknij Add.

Plik Add Scenariopojawi się okno dialogowe. Zwróć uwagę, że C3, C4, C5 pojawiają się w polu pod zmieniającymi się komórkami.

• W polu Nazwa scenariusza wpisz Scenariusz 3.

• Edytuj tekst w Commentpudełko jako - Różne nie. płatności.

• Wybierz Zapobiegaj zmianom w sekcji Ochrona i kliknij OK.

Zostanie wyświetlone okno dialogowe Wartości scenariusza. Początkowe wartości pojawiają się w zmieniających się komórkach. Zmień wartość NPER na 300 i kliknijAdd.

Plik Add Scenariopojawi się okno dialogowe. Zwróć uwagę, że C3, C4, C5 pojawiają się w polu Zmiana komórek.

• W polu Nazwa scenariusza wpisz Scenariusz 4.

• Edytuj tekst w Comment pole as - Inny rodzaj płatności.

• Wybierz Zapobiegaj zmianom w sekcji Ochrona i kliknij OK.

Plik Scenario Valuespojawi się okno dialogowe. Początkowe wartości pojawiają się w zmieniających się komórkach. Zmień wartość Type na 1. Kliknij OK po dodaniu wszystkich scenariuszy, które chcesz dodać.

Plik Scenario Managerpojawi się okno dialogowe. W polu pod Scenariuszami znajdziesz nazwy wszystkich stworzonych przez siebie scenariuszy.

• Kliknij Scenariusz 1. Jak wiesz, Scenariusz 1 zawiera wartości początkowe.


Teraz kliknij Summary. Zostanie wyświetlone okno dialogowe Podsumowanie scenariusza.

Raporty podsumowujące scenariusze

Excel udostępnia dwa typy raportów podsumowania scenariusza -

• Podsumowanie scenariusza.
• Raport w formie tabeli przestawnej scenariusza.

W oknie dialogowym Podsumowanie scenariusza można znaleźć te dwa typy raportów.

Wybierz Podsumowanie scenariusza w obszarze Typ raportu.

Podsumowanie scenariusza

w Result cells wybierz komórkę C6 (Tutaj umieściliśmy plik PMTfunkcjonować). Kliknij OK.

Raport podsumowania scenariusza pojawi się w nowym arkuszu. Arkusz nosi nazwę Podsumowanie scenariusza.

W raporcie Podsumowanie scenariusza można zaobserwować:

• Changing Cells- Powiększa wszystkie komórki używane jako zmieniające się komórki. Ponieważ nazwałeś komórki, Interest_Rate, NPER i Type, wydają się one nadawać raportowi znaczenie. W przeciwnym razie wyświetlone zostaną tylko odwołania do komórek.

• Result Cells - Wyświetla określoną komórkę wynikową, tj. EMI.

• Current Values - Jest to pierwsza kolumna i zawiera wartości tego scenariusza, który został wybrany w oknie dialogowym Menedżer scenariuszy przed utworzeniem raportu podsumowującego.

• We wszystkich scenariuszach, które utworzyłeś, zmieniające się komórki zostaną podświetlone na szaro.

• W wierszu EMI zostaną wyświetlone wartości wyników dla każdego scenariusza.

Możesz uczynić raport bardziej zrozumiałym, wyświetlając komentarze dodane podczas tworzenia scenariuszy.

• Kliknij przycisk + po lewej stronie wiersza zawierającego nazwy scenariuszy. Komentarze do scenariuszy pojawiają się w wierszu pod nazwami scenariuszy.

Scenariusze z różnych źródeł

Załóżmy, że otrzymujesz scenariusze z trzech różnych źródeł i musisz przygotować raport podsumowujący scenariusz w skoroszycie głównym. Możesz to zrobić, scalając scenariusze z różnych skoroszytów do skoroszytu głównego. Wykonaj czynności podane poniżej -

• Załóżmy, że scenariusze znajdują się w skoroszytach, Bank1_Scenarios, Bank2_Scenarios i Bank3_Scenarios. Otwórz trzy skoroszyty.

• Otwórz skoroszyt Główny, w którym masz wartości początkowe.

• Kliknij opcję DANE> Analiza warunkowa> Menedżer scenariuszy w skoroszycie głównym.

Plik Scenario Manager Pojawi się okno dialogowe.

Jak widać, nie ma żadnych scenariuszy, ponieważ jeszcze żadnego nie dodałeś. KliknijMerge.

Zostanie wyświetlone okno dialogowe Scenariusze scalania.

Jak widać, w obszarze Scal scenariusze z masz dwa pola -

• Book
• Sheet

Możesz wybrać określony arkusz z określonego skoroszytu zawierającego scenariusze, które chcesz dodać do wyników. Kliknij strzałkę w dół obokBook aby zobaczyć skoroszyty.

Note - Odpowiednie skoroszyty powinny być otwarte, aby pojawiły się na tej liście.

Wybierz książkę - Bank1_Scenarios.

Wyświetlany jest arkusz Bank1. W dolnej części okna dialogowego jest wyświetlana liczba scenariuszy znalezionych w arkuszu źródłowym. Kliknij OK.

Pojawi się okno dialogowe Menedżer scenariuszy. Dwa scenariusze, które zostały scalone ze skoroszytem głównym, zostaną wymienione w obszarze Scenariusze.

Kliknij Mergeprzycisk. PlikMerge Scenariospojawi się okno dialogowe. Teraz wybierzBank2_Scenarios z listy rozwijanej w polu Książka.

Arkusz Bank2 jest wyświetlany. W dolnej części okna dialogowego wyświetlana jest liczba scenariuszy znalezionych w arkuszu źródłowym. Kliknij OK.

Plik Scenario ManagerPojawi się okno dialogowe. Cztery scenariusze, które zostały scalone ze skoroszytem głównym, są wymienione w sekcji Scenariusze.

Kliknij Mergeprzycisk. PlikMerge Scenariospojawi się okno dialogowe. Teraz wybierzBank3_Scenarios z listy rozwijanej w polu Książka.

Wyświetlany jest arkusz Bank3. W dolnej części okna dialogowego zostanie wyświetlona liczba scenariuszy znalezionych w arkuszu źródłowym. Kliknij OK.

Zostanie wyświetlone okno dialogowe Menedżer scenariuszy. Pięć scenariuszy, które zostały scalone ze skoroszytem głównym, zostanie wyświetlonych w obszarze Scenariusze.

Teraz masz wszystkie wymagane scenariusze do wygenerowania raportu podsumowującego scenariusz.

Kliknij przycisk Podsumowanie. PlikScenario Summary pojawi się okno dialogowe.

• Wybierz Podsumowanie scenariusza.
• W polu komórki wynikowe wpisz C6 i kliknij OK.

Raport podsumowujący scenariusz pojawia się w nowym arkuszu w skoroszycie głównym.

Wyświetlanie scenariuszy

Załóżmy, że prezentujesz swoje scenariusze i chcesz dynamicznie przełączać się z jednego scenariusza do drugiego i wyświetlać zestaw wartości wejściowych i wartości wyników odpowiedniego scenariusza.

• Kliknij opcję DANE> Analiza warunkowa> Menedżer scenariuszy w grupie Narzędzia danych. Zostanie wyświetlone okno dialogowe Menedżer scenariuszy. Pojawi się lista scenariuszy.

• Wybierz scenariusz, który chcesz wyświetlić. KliknijShow.

Wartości w arkuszu są aktualizowane do wartości wybranego scenariusza. Wartości wyników są ponownie obliczane.

Raport w formie tabeli przestawnej scenariusza

Możesz również zobaczyć raport scenariusza w formie tabeli przestawnej.

• Kliknij przycisk Podsumowanie w Scenario ManagerOkno dialogowe. Zostanie wyświetlone okno dialogowe Podsumowanie scenariusza.

• Wybierz Scenario PivotTable report w obszarze Typ raportu.

• Wpisz C6 w Result cells pudełko.

Raport w formie tabeli przestawnej scenariusza pojawia się w nowym arkuszu.