Controlling operacyjny w przedsiębiorstwie handlowym - lab. z programu Excel 2007
Wydział Zarządzania, WZ, UTP, Bydgoszcz
=========================================================
=========================================================
=========================================================
"Menedżer scenariuszy" to coś odwrotnego do "Szukaj wyniku". Tym razem mamy podane wartości wstępne i dla ich różnych kombinacji obliczamy wynik finansowy.
Więcej o "Menedżerze scenariuszy" można poczytać tu.
Najpierw przepisujemy informacje wstępne, tabelę 1 oraz polecenia tak jak to widać na obrazku poniżej.
Jeśli chcesz stworzyć swój własny plik, to postaraj się wszystkie rzeczy przepisywać do tych samych komórek co ja.
Teraz przepisujemy tabelę 2. (Także zachowaj te same numery co ja).
Ostatnią tabelą do przepisania jest tabela numer 3. Zrobiłem przerwę w wierszu 33, żeby zachować większą przejrzystość, niemniej nie stanowi ona podziału tej tabeli na dwie mniejsze.
Uzupełnianie tabeli numer 3
Najpierw uzupełniamy kolumnę "Baza", a następnie zaznaczone wszystkie komórki z danymi w tej kolumnie przenosimy do pozostałych trzech kolumn, zawierających Warianty 1-3.
- Wzrost ceny sprzedaży -> Wzrost cen sprzedaży (tab 2) "=B23"
- Wzrost wolumenu sprzedaży -> Wzrost wolumenu sprzedaży (tab 2) "=B24"
Zmiana danych w tabeli 2 automatycznie spowoduje zmiany w tabeli 3. Poprzez tabelę 1 i 2 wprowadzamy zmienne, a tabela 3 pokazuje tylko efekty tych działań, dlatego w tabeli 3 dla pierwszych dwóch wierszy jest odniesienie do tabeli 2.
- Cena sprzedaży -> Cena sprzedaży (tab 1) + Cena sprzedaży (tab 1) * Wzrost ceny sprzedaży (tab 3) "=$B$12+$B$12*B31"
- Wolumen -> Wielkość sprzedaży (tab 1) + Wielkość sprzedaży (tab 1) * Wzrost wolumenu sprzedaży (tab 3) "=$B$9+$B$9*B32"
- Przychody -> Cena sprzedaży (tab 3) * Wolumen (tab 3) "=B34*B35"
- Wartość zakupu -> Cena zakupu (tab 1) * Wolumen (tab 3) "=$B$10*B35"
- Marża I stopnia (handlowa) -> Przychody (tab 3) - Wartość zakupu (tab 3) "=B36-B37"
- Koszty zmienne sprzedaży -> Jednostkowe koszty zmienne sprzedaży (tab 1) * Wolumen (tab 3) "=$B$11*B35"
- Marża II stopnia -> Marża I stopnia (tab 3) - Koszty zmienne sprzedaży (tab 3) "=B38-B39"
- Koszty stałe sprzedaży -> Koszty stałe sprzedaży (tab 1) "=$B$13"
- Marża III stopnia -> Marża II stopnia (tab 3) - Koszty stałe sprzedaży (tab 3) "=B40-B41"
Uzupełniona tabela wygląda tak:
Teraz kopiujemy cały arkusz. Przypominam:
- kliknij PPM na nazwę arkusza
- wybierz "Przenieś lub kopiuj"
- zaznacz: (przenieś na koniec)
- zaznacz opcję: Utwórz kopię
- kliknij OK
W nowo przekopiowanym arkuszu zmieniamy tylko tabelę 3, ucinając kolumny zawierające Warianty 1-3.
Teraz odpalamy "Menedżera scenariuszy". Możemy od razu zaznaczyć komórki B30:B32, wtedy po włączeniu menedżera od razu są one uznane za komórki zmienne. Ale nie jest to konieczny krok.
Także komórka B30 zawierająca może być zmieniana. Dzięki temu przy zmianie wariantu/scenariusza wyświetla się jego nazwa, wzrost ceny sprzedaży oraz wzrost wolumenu sprzedaży.
Wchodzimy w zakładkę "Dane", potem w "Analizę symulacji" lub "Analizę warunkową" i następnie wybieramy "Menedżer scenariuszy" i w oknie, które się pojawi, klikamy "Dodaj".
W kolejnym oknie, jako nazwę scenariusza wpisujemy "Baza", a komórki zmienne to B30:B32. Klikamy OK.
Pojawiło się nowe okno "Wartości scenariusza", a w nim wybrane wcześniej trzy komórki zmienne, którym przypisujemy następujące wartości:
- $B$30: Baza
- $B$31: 0
- $B$32: 0
Klikamy Dodaj.
Pojawia się ponownie okno do wprowadzenia kolejnego wariantu. Jako "Nazwę scenariusza" wpisujemy: "Wariant 1". Komórki zmienne pozostają bez zmian. Klikamy OK.
Ponownie pojawia się okno wartości scenariusza, wprowadzamy w nie:
- $B$30: Wariant 1
- $B$31: 0,1
- $B$32: 0
Klikamy Dodaj.
Pojawia się ponownie okno do wprowadzenia kolejnego wariantu. Jako "Nazwę scenariusza" wpisujemy: "Wariant 2". Komórki zmienne pozostają bez zmian. Klikamy OK.
Ponownie pojawia się okno wartości scenariusza, wprowadzamy w nie:
- $B$30: Wariant 2
- $B$31: 0
- $B$32: 0,1
Klikamy Dodaj.
Pojawia się ponownie okno do wprowadzenia kolejnego wariantu. Jako "Nazwę scenariusza" wpisujemy: "Wariant 3". Komórki zmienne pozostają bez zmian. Klikamy OK.
Ponownie pojawia się okno wartości scenariusza, wprowadzamy w nie:
- $B$30: Wariant 3
- $B$31: 0,1
- $B$32: 0,05
Klikamy OK. We wszystkich przypadkach musimy wpisywać wartości dziesiętne, zamiast procentowych, czyli zamiast 10% -> 0,1.
Pozostajemy w oknie Menedżera, mamy do wyboru 4 warianty, wybieramy np. Wariant 1 i klikamy na "Pokaż". Tabela 3 automatycznie pokazuje obliczenia tylko dla Wariantu 1.
Tak możemy sobie wyświetlać każdy z wariantów.
Pliki do pobrania
Plik Excel - Menedżer scenariuszy - Uzupełniony
Plik Excel - Menedżer scenariuszy - Pusty
Pliki do pobrania
Plik Excel - Menedżer scenariuszy - Uzupełniony
Plik Excel - Menedżer scenariuszy - Pusty