Potrzebujesz porównać różne warianty kredytu hipotecznego, przygotować plan finansowy firmy na nadchodzący rok, a może chcesz sprawdzić czy opłacalne jest samodzielne wydanie książki? W każdym z wymienionych przypadków wpływ na ostateczny wynik ma wiele zmiennych, które w zależności od przyjętego scenariusza mogą mieć różne wartości. W przeprowadzeniu analizy warunkowej i znalezieniu właściwej odpowiedzi na powyższe pytania pomocny będzie menedżer scenariuszy.
Scenariusze pomagają wykonać wiele obliczeń na tym samym zestawie danych. Ponadto możesz zobaczyć jak wygląda analiza dla pojedynczego wariantu, jak również wygenerować raport podsumowujący wszystkie zdefiniowane scenariusze. Dzięki temu ich porównanie będzie łatwiejszym zadaniem.
Jak stworzyć nowy scenariusz?
Pierwszą czynnością jaką musisz wykonać jest zidentyfikowanie wszystkich komórek specyficznych dla Twojego scenariusza. Są to komórki, których wartość jest brana pod uwagę przy obliczaniu zdefiniowanych przez Ciebie formuł.
Dla przykładu jeśli poszukujesz najlepszego wariantu finansowania zakupu nieruchomości kredytem hipotecznym, to możesz stworzyć wiele scenariuszy opartych na różnych wartościach takich zmiennych jak kwota bazowa kredytu (B1), prowizja dla banku za udzielenie kredytu (B2), oprocentowanie (B3), długość okresu spłaty (B4) itp.
Wszystkie komórki zawierające powyższe dane są komórkami specyficznymi dla scenariusza, co jest równoznaczne z tym, że każdy scenariusz użyje innej wartości dla każdej z tych komórek. Przechodząc z jednego scenariusza do drugiego, Excel modyfikuje wartość w tych komórkach, co ma wpływ na obliczenie np. wysokości stałej raty kredytu.
Aby dodać nowy scenariusz do arkusza postępuj zgodnie z poniższą procedurą.
KROK 1: Zaznacz komórki, których wartości będą zmieniać się w scenariuszu.
Jeśli zaznaczasz nieciągły zakres komórek, to najpierw wciśnij klawisz Ctrl, a następnie wskaż myszą poszczególne komórki.
KROK 2: Na karcie Dane w grupie Prognoza kliknij rozwijaną listę Analiza warunkowa, a następnie wybierz Menedżer scenariuszy.
Jeśli używasz Excela 2007, to ścieżka dostępu jest nieco inna. Na karcie Dane przejdź do grupy Narzędzia danych, a następnie z listy rozwijanej Analiza symulacji wybierz polecenie Menedżer scenariuszy.
KROK 3: W oknie dialogowym Menedżer scenariuszy kliknij przycisk Dodaj, aby stworzyć nowy scenariusz.
KROK 4: W oknie dialogowym Dodawanie scenariusza zacznij od wprowadzenia nazwy scenariusza.
W naszym przykładzie scenariusze mają nadane litery alfabetu, ale możesz przykładowo użyć nazwy w stylu: Kredyt – zero prowizji, Kredyt – niskie oprocentowanie itd.
KROK 5: Jeśli w kroku 1 nie zaznaczyłeś wszystkich specyficznych komórek lub potrzebujesz je zmienić, to wszelkich modyfikacji dokonaj w polu Komórki zmieniane.
KROK 6: Jeśli wymaga tego sytuacja, to możesz zablokować scenariusz zaznaczając opcje Chroń przed zmianami i/lub Ukryj.
Włączenie pierwszej opcji uniemożliwi użytkownikowi dokonanie jakiejkolwiek modyfikacji lub usunięcia scenariusza. Włączenie drugiej opcji spowoduje, że w oknie dialogowym Menedżer scenariuszy ukryty scenariusz nie będzie w ogóle widoczny.
Należy pamiętać, że żadna z powyższych opcji nie będzie działać, jeśli wcześniej nie zostanie włączona ochrona arkusza. Aby to zrobić należy kliknąć prawym przyciskiem myszy na karcie arkusza, a następnie wybrać polecenie Chroń arkusz, dwukrotnie wprowadzić hasło i kliknąć przycisk OK.
KROK 7: Kliknij OK, aby zamknąć okno dialogowe Dodawanie scenariusza.
KROK 8: W oknie dialogowym Wartości scenariusza wprowadź wartości dla wszystkich zmieniających się komórek, zgodnie z danym scenariuszem.
Na początku Excel automatycznie umieszcza dla każdej zmienianej komórki jej bieżącą wartość. Możesz ją dopasować lub pozostawić jeśli odpowiada wartości definiowanego scenariusza.
KROK 9: Kliknij OK, aby zakończyć proces definiowania nowego scenariusza.
Jeśli planujesz stworzyć więcej niż jeden scenariusz, to dla każdego z nich powtórz kroki od 3 do 9.
W tym miejscu chciałbym zwrócić Twoją uwagę na fakt, iż nie ma żadnych ograniczeń co do liczby tworzonych scenariuszy. Limitowana jest natomiast liczba zmiennych, ograniczona do 32.
Jak zarządzać scenariuszami?
Po zdefiniowaniu wszystkich scenariuszy pozostałe zadania wykonujesz z poziomu Menedżera scenariuszy, który pozwala na:
- wyświetlanie – wybierz z listy scenariusz, który chcesz obejrzeć, a następnie kliknij przycisk Pokaż. Excel natychmiast zaktualizuje zmieniane komórki oraz funkcje i formuły, które się do nich odwołują.
- edytowanie – zaznacz na liście scenariusz i kliknij przycisk Edytuj. Na ekranie pojawi się okno dialogowe Edytowanie scenariusza, w którym będziesz mógł zmienić nazwę scenariusza, odwołania do komórek zmienianych, opcje ochrony oraz wartości dla każdej zmienianej komórki.
- usuwanie – wybierz z listy scenariusz, a następnie kliknij przycisk Usuń. Niestety, nie można skasować wielu scenariuszy jednocześnie.
- scalanie – ta opcja umożliwia łączenie ze sobą scenariuszy znajdujących się w innym arkuszu tego samego skoroszytu lub innego skoroszytu.
- podsumowanie – użyj tej opcji jeśli potrzebujesz raportu podsumowującego wszystkie scenariusze. Szczegóły znajdziesz w dalszej części artykułu.
Aby zakończyć pracę z Menedżerem scenariuszy i powrócić do arkusza należy kliknąć przycisk Zamknij.
Jak stworzyć podsumowanie scenariuszy?
Gotowe scenariusze pozwalają zaobserwować jak różne wartości poszczególnych zmiennych wpływają na wynik końcowy. Aby jednak porównać wszystkie scenariusze jednocześnie należy wygenerować raport podsumowujący, który zaprezentuje wskazane przez Ciebie informacje w oddzielnym arkuszu.
Przygotowanie takiego podsumowania nie jest trudne i sprowadza się do wykonania kilku prostych czynności.
KROK 1: Na karcie Dane w grupie Prognoza kliknij rozwijaną listę Analiza warunkowa, a następnie wybierz Menedżer scenariuszy.
KROK 2: W oknie dialogowym Menedżer scenariuszy kliknij przycisk Podsumowanie.
KROK 3: W oknie Podsumowanie scenariuszy wybierz typ raportu jaki chcesz wygenerować.
Do wyboru masz dwie opcje: Podsumowanie scenariuszy (najczęściej wybierana) albo Raport w formie tabeli przestawnej scenariuszy.
KROK 4: W polu Komórki wynikowe wprowadź adresy komórek (zawierających formuły), które chcesz umieścić w gotowym raporcie. Wartości w nich wyświetlane będą zmieniać się w zależności od zastosowanego scenariusza.
KROK 5: Kliknij przycisk OK.
Jak już wspomniałem Excel tworzy raport podsumowujący w nowym arkuszu, którego nazwa uzależniona jest od wybranego typu raportu. Możliwe są więc dwie następujące nazwy: Podsumowanie scenariuszy lub Tabela przestawna scenariuszy.
Zauważ, że raport podsumowujący nie jest do końca czytelny. Dlaczego? Spójrz na nazwy komórek zmienianych i komórek wynikowych. Wyświetlane są adresy komórek przez co, żeby wiedzieć czego dotyczą musisz przełączać się między arkuszami.
Aby temu zaradzić należałoby przed utworzeniem raportu zaznaczyć w arkuszu źródłowym zakres komórek A1:B11, a następnie na karcie Formuły w grupie Nazwy zdefiniowane kliknąć polecenie Utwórz z zaznaczenia (określając, że chcesz utworzyć nazwy na podstawie wartości z lewej kolumny). Dzięki temu Twój raport będzie zdecydowanie czytelniejszy.
Generowany raport jest wstępnie sformatowany po to, aby można było w prosty sposób analizować zgromadzone informacje.
Warto zapamiętać, że raz stworzone podsumowanie w żaden sposób nie jest połączone z danymi źródłowymi. To oznacza, że w przypadku modyfikacji któregokolwiek ze scenariuszy lub formuł, nie ma możliwości jego zaktualizowania. Jedyne co w takiej sytuacji możesz zrobić, to wygenerowanie nowego podsumowania.
Jeżeli zainteresował Cię ten artykuł, to polecam również lekturę dwóch kolejnych, w których przedstawiłem pozostałe narzędzia analizy warunkowej: Szukaj wyniku oraz Tabela danych.