Menedżer scenariuszy w Excelu – przykład zastosowania

Zapewne wielu z Was spotkało się kiedyś z sytuacją, w której próbuje kupić jakiś towar bądź usługę i w której ma do wyboru kilka wariantów cenowych. I w zasadzie nie byłoby w tym nic nadzwyczajnego, gdyby nie fakt, że bardzo często te warianty różnią się np. jednym elementem (czyli np. dokupujemy dodatkowe ubezpieczenie na dany towar). I może też być tak, że w tych ofertach będą istnieć różne parametry, które są stałe dla każdej oferty i będą istnieć także parametry, które będą się różnić w zależności od konkretnego wariantu. Jak to się jednak wszystko ma do Excela?

Otóż istnieje w Excelu narzędzie, tzw. menedżer scenariuszy, dzięki któremu można przygotować właśnie różne warianty tych samych obliczeń. W naszym wypadku narzędzie to będzie służyło do przygotowania różnych wariantów ofert na ten sam produkt lub tę samą usługę.

Narzędzie Menedżer scenariuszy znajdziemy na wstążce na karcie Dane, wybierając narzędzie Analiza warunkowa, a następnie Menedżer scenariuszy (rysunek 1).

Załóżmy więc, że sprzedajemy towar, który ma pewną ustaloną cenę początkową. Do tego możliwe są następujące dodatki:

• transport (dla uproszczenia przyjmijmy, że transport jest w cenie 500 zł),
• dodatkowe ubezpieczenie (200 zł),
• przedłużenie gwarancji (300 zł)
• oraz połączenia powyższych:
• transport i dodatkowe ubezpieczenie (700 zł),
• transport i przedłużenie gwarancji (800 zł),
• dodatkowe ubezpieczenie i przedłużenie gwarancji (500 zł)
• transport, dodatkowe ubezpieczenie i przedłużenie gwarancji (1000 zł).

Spróbujemy teraz wprowadzić pewne informacje do excelowego arkusza, zakładając dodatkowo, że wyjściowa cena naszego towaru wynosi 2000 zł. Wprowadzone dane zostały pokazane na rysunku 2.

Warto dodać, że w komórce B6 została wprowadzona formuła sumująca składowe ceny ostatecznej, czyli:

=SUMA(B1:B4)

Teraz w zależności od tego, jaki wariant wybierze klient, to takie wartości powinny być do tej sumy doliczane.

Oczywiście, można by teraz te komórki uzupełniać ręcznie, szykując oferty dla każdego klienta i każdego towaru. Ale jeżeli oferty te będą analogiczne dla poszczególnych klientów (w tym wypadku będą się różnić tylko wyjściową ceną towaru), to warto wykorzystać narzędzie Menedżer scenariuszy.

Mając przygotowane dane w arkuszu tak, jak pokazuje rysunek 2, możemy już uruchomić wspomniane narzędzie ze wstążki (rysunek 1). Pojawi nam się wtedy okienko, w którym będziemy dodawać kolejne scenariusze, czyli kolejne warianty dla naszych danych (rysunek 3).

W oknie tym klikamy opcję Dodaj w prawym górnym rogu okna i pojawi nam się kolejne okienko do dodawania konkretnego scenariusza (rysunek 4).

Zacznijmy wprowadzać pierwszy scenariusz (tylko z opcją transportu). Nazwijmy go więc Transport. Komórki zmieniane to te komórki, dla których wartości będziemy podawać w tym konkretnym scenariuszu (a więc te komórki, które w różnych scenariuszach będą miały różne wartości). W naszym przypadku będą to komórki B2:B4. Uzupełnione okno pokazuje rysunek 5.