MS Excel - wspomaganie decyzji

1. ZIiPzaoczne sem.3 MS Excel - wspomaganie decyzji W3

2. Excel - złożone problemy decyzyjne DECYZJE SEMI-STRUKTURYZOWANE – OPTYMALIZACJA LINIOWA Z OGRANICZENIAMI Cel: znalezienie optymalnej wartości funkcji dla kilku zmiennych decyzyjnych.

3. Proces tworzenia:

4. Można podjąć decyzję korzystając z doświadczenia i intuicji - nie jest to sposób optymalny • Opis analityczny • Zbudowanie modelu sytuacji decyzyjnej • Przeprowadzenie optymalizacji • Interpretacjawyników

5. Przykładowo: wybór asortymentu produkcji - problem wyboru asortymentu produkcji polega na określeniu, które wyroby i w jakich ilościach powinno przedsiębiorstwo produkować, aby nie przekraczając dostępnych zasobów środków produkcji oraz przy spełnieniu ewentualnych dodatkowych ograniczeń maksymalizować zysk lub przychód ze sprzedaży,

6. problemy mieszania decydent chce określić optymalny skład mieszaniny, która powinna spełniać pewne wymagania aby zminimalizować koszty związane z uzyskaniem produktu końcowego (np. benzyna, ciekłe metale oraz inne chemikalia przerabiane na gotowe do sprzedaży wyroby),

7. określenie optymalnego portfela inwestycyjnego - polegający na doborze alokacji kapitału, którym dysponuje inwestor pomiędzy pewne preferowane walory, tworzące portfel, w celu minimalizacji poziomu ryzyka związanego z portfelem przy zachowaniu określonej stopy zwrotu,

8. zagadnienie transportowe- sprowadzające się do określania planu przewozu jednorodnego produktu z kilku różnych źródeł zaopatrzenia do kilku punktów zgłaszających zapotrzebowanie na ten towar, takiego aby zminimalizować łączne koszty transportu (czasem minimalizacja odległości lub czasu transportu),

9. zagadnienie harmonogramowania- sprowadzające się do określania planu wykonania pewnych prac, które muszą być zrealizowane w określonych miejscach pracy (zakładach, stanowiskach pracy, maszynach) przy znanych ograniczeniach (np. czas pracy pracowników lub liczba pracowników).

10. Przykładowe problemy należą do grupy zagadnień programowania liniowego- metoda wyznaczania decyzji przy spełnieniu warunków i przy zadanym kryterium oceny efektu decyzji. • Decyzje- wektor zmiennych decyzyjnych • Warunki ograniczające- układ nierówności lub równości liniowych • Funkcja celu– reguła wyboru - pewna pojedyncza zmienna wartość, mająca być obliczona, wymagająca maksymalizacji, minimalizacji lub przyjęcia określonej wartości (zwykle jako zysk lub koszt). • Kryterium oceny - funkcja liniowa.

11. NARZĘDZIE SOLVER Program Solver(dodatek wymagający instalacji) - bardziej skomplikowane problemy decyzyjne niż przy użyciu Szukaj wyniku. Daje możliwość wspomagania procesu podejmowania decyzji semistrukturyzowanych.

12. Proces - kilka etapów: • identyfikacja problemu, • określenie zmiennych decyzyjnych, • sformułowanie funkcji celu, • określenie i sformułowanie ograniczeń, • zapis modelu w postaci analitycznej, • przejście na zapis w postaci wyrażeń arkusza Excel, • uruchomienie programu Solver, • wprowadzenie informacji dotyczących adresów komórek stanowiących zmienne decyzyjne funkcji celu i ich ograniczenia, • uruchomienie optymalizacji, • interpretacja otrzymanych wyników.

13. Uruchomienie narzędzia - Solver

14. Parametry i opcje: • komórka celu– adres komórki zawierającej wzór stanowiący zapis funkcji celu optymalizacji, • równa– grupa opcji pozwalająca określić czy funkcja celu ma przyjąć wartość maksymalną, minimalną czy konkretną wartość, określoną przez użytkownika, • komórki zmieniane(zmienne decyzyjne)– adresy lub zakresy komórek stanowiących zmienne decyzyjne, których wartości początkowe ustawiane są arbitralnie a po procesie optymalizacji przyjmują wartości optymalne (jeżeli program znajdzie rozwiązanie), • warunki ograniczające – ograniczenia zdefiniowane w problemie do rozwiązania, można je dodawać, usuwać i modyfikować z wykorzystaniem odpowiednich przycisków Dodaj , Usuń i Zmień, • rozwiąż – przycisk uruchamiania procesu optymalizacji.

15. Dodatkowe opcje (wybór przycisku Opcje), np.: Przyjmij model liniowy – wskazująca, że rozwiązywany problem należy do grupy zagadnień programowania liniowego, Przyjmij nieujemne – założenie, że zmienne decyzyjne mogą przyjmować tylko wartości nieujemne.

16. PRZYKŁAD – WYBÓR ASORTYMENTU PRODUKCJI • Dwa modele produktu aparatu fotograficznego • Model1 – zysk jednostkowy z1=1 jednostka (umowne), • Model2 – zysk jednostkowy z2=5 jednostek • Podzespoły do wymienionych modeli są produkowane na trzech wydziałach: WM, WO, WME • Liczba godzin przy tworzeniu podzespołów dla wymienionych modeli i ograniczenia czasowe na wydziałach:

17. Funkcja celu: Jakie wybrać wielkości produkcji obydwu modeli by zmaksymalizować zysk.

18. Etapy rozwiązywania problemu • 1) Identyfikacja problemu: • określenie wielkości produkcji modelu 1 oraz modelu 2 • 2) Określenie zmiennych decyzyjnych: • M1 – wielkość produkcji modelu 1 • M2 – wielkość produkcji modelu 2 • 3) Sformułowanie funkcji celu: • zysk zapisany w postaci wzoru • z1*M1 + z2*M2  MAX • gdzie współczynniki z1 i z2 oznaczają zysk jednostkowy (w przykładzie odpowiednio 1 i 5), a M1 i M2 to wielkość produkcji poszczególnych modeli.

19. 4) Określenie i sformułowanie ograniczeń: w1 t1a*M1 + t2a*M2 ga – ograniczenia czasu pracy na wydziale WM, w2 t1b*M1 + t2b*M2 gb – ograniczenia czasu pracy na wydziale WO, w3 t1c*M1 + t2c*M2 gc – ograniczenia czasu pracy na wydziale WME, M1, M2 0 – założenie o nieujemnych wartościach zmiennych decyzyjnych.

20. w3 M1 w1 funkcja celu w2 M2 5. Interpretacja graficzna

21. 6) Przejście na zapis w postaci wzorów w arkuszu Excel: • wprowadzenie informacji do arkusza,

22. Można użyć funkcji suma.iloczynów(…) - mnoży odpowiadające sobie elementy w danych tablicach (identycznych rozmiarów) i podaje w wyniku sumę tych iloczynów. Składnia = SUMA.ILOCZYNÓW(tablica1;tablica2;tablica3;...) Tablica1;tablica2;tablica3;... to od 2 do 30 tablic, których elementy mają być pomnożone i dodane. • Argumenty tablic muszą mieć ten sam rozmiar. Jeśli tak nie jest, to funkcja SUMA.ILOCZYNÓW podaje w wyniku wartość błędu #ARG!. • SUMA.ILOCZYNÓW traktuje elementy tablic, które nie są liczbami jako równe zero.

23. 7) Uruchomienie programu Solver: w oknie dialogowym wprowadza się informacje dotyczące adresów komórek zawierających funkcję celu i ograniczenia oraz określa się, które komórki stanowią zmienne decyzyjne, po naciśnięciu przycisku Opcje wybiera się ustawienia: Przyjmij model liniowy Przyjmij nieujemne.

25. Wykonanie optymalizacji: • Proces optymalizacji aktywizuje się przyciskiem Rozwiąż. Po wykonaniu optymalizacji zmienne decyzyjne przyjmują optymalne wartości a program Solver wyświetla okno informacyjne o powodzeniu lub braku możliwości uzyskania rozwiązania optymalnego.

26. Interpretacja otrzymanych wyników: linia prosta funkcji celu opiera się na "ostrzu" • aby uzyskać w rozpatrywanym problemie maksymalny zysk, przy zadanych ograniczeniach, należy model 1 produkować w liczbie 440 egzemplarzy natomiast model 2 produkować w liczbie 445 egzemplarzy, • do produkcji aparatów potrzebny będzie następujący czas pracy poszczególnych wydziałów: • WM - 3995 h <=4000 • WO - 1770 h <=2000 • WME - 4000 h <=4000 • przy wskazanej decyzji zysk firmy przyjmie wartość 2665 jednostek umownych.

27. Przykład2: DORADCA GIEŁDOWY przygotowanie danych…