Ćwiczenie nr 1

Wykorzystanie narzędzi optymalizacyjnych typu Solver / Excel

CEL ĆWICZENIA

Podstawowym celem ćwiczenia jest przybliżenie metodyki rozwiązywania prostych zagadnień optymalizacyjnych z wykorzystaniem szeroko dostępnych (choć często niedocenianych) narzędzi informatycznych do jakich z całą pewnością zalicza się arkusz kalkulacyjny Excel wraz ze standardowym dodatkiem optymalizacyjnym Solver. W trakcie realizacji procesu poszukiwania optymalnego rozwiązania dla typowego problemu transportowego student zapozna się z etapami definiowania modelu matematycznego problemu, funkcji celu oraz ograniczeń a następnie z zagadnieniami związanymi z wykorzystaniem dodatku Solver do poszukiwania optimum tak przygotowanego zadania.

WSTĘP TEORETYCZNY

Dla lepszego zrozumienia przebiegu ćwiczenia celowe jest zapoznanie się z zagadnieniami teoretycznymi na temat metod rozwiązywania problemu transportowego (w szczególności metodami analitycznymi) - choć dla rozwiązania pozostałych problemów nie jest to wiedza konieczna.

W trakcie realizacji ćwiczenia zostanie przedstawiony przykład rozwiązania typowego problemu transportowego. Korzystając z przedstawionej metodyki student samodzielnie rozwiąże jeden z przedstawionych poniżej problemów:

1. Problem doboru asortymentu produktów dających największy zysk przy uwzględnieniu ograniczeń zapasów magazynowych komponentów składowych niezbędnych do wytwarzania poszczególnych produktów,

2. Problem optymalnej obsady jednorodnych stanowisk o zmiennej w czasie funkcji obciążenia.

UŻYWANE POMOCE DYDAKTYCZNE

Lp.	OPIS	OZNACZENIE STANOWISKA
1.	Zbiór danych wyjściowych i opis metody rozwiązania problemu transportowego
2.	System pomocy pakietu Excle/Solver

PRZEBIEG ĆWICZENIA

Rozwiązanie modelowe problemu transportowego:

Wyznaczyć optymalne ilości towaru, który należy przewieźć od poszczególnych dostawców do odbiorców, tak aby pokryć zapotrzebowanie każdego odbiorcy i nie przekroczyć możliwości produkcyjnych (podaży) dostawców.

Dokonać sprawdzenia czy mamy do czynienia z problemem zbilansowanym czy niezbilansowanym.

Przy rozwiązywaniu problemu przyjmujemy stały koszt transportu jednostki towaru na 1 kilometr (pomijamy szereg istotnych parametrów takich jak kwestia doboru pojazdów i ich pojemności, możliwość tworzenia bardziej złożonych łańcuchów dostaw itp. w celu uproszczenia problemu)

Podaż zakładów produkcyjnych:

1. Poznań - 600,

2. Warszawa - 540,

3. Kraków - 410,

Popyt odbiorców:

4. Toruń - 350

5. Wrocław - 480

6. Łódź - 400

7. Tarnobrzeg - 320

Zakłady:	Odległości z zakładu x do magazynu y:
		4. Toruń	5. Wrocław	6. Łódź	7. Tarnobrzeg
1. Poznań	151	178	212	457
2. Warszawa	209	248	134	217
3. Kraków	384	268	220	175

Odległości są wyrażone w km.

Koszt transportu 0,09 zł / 1 paletę / 1km

W celu rozwiązania tak postawionego problemu należy:

1. Przenieść powyższe dane wyjściowe do arkusza kalkulacyjnego (dowolny, ale przemyślany i czytelny układ, najlepiej z dodatkowymi opisami).
   
   Przykład wyglądu arkusza (po zrealizowaniu kroków od 1 do 5) prezentuje poniższy rysunek. Nie trzeba się nim kierować - stanowi on tylko podpowiedź jednej z wielu możliwych dróg realizacji rozwiązania zadania.

1. Na rysunku kolorem zielonym zaznaczone są wartości, które były dane w treści zadania.

2. Kolor niebieski to komórki obliczane przy pomocy wyrażeń matematycznych (formuł) na podstawie innych komórek arkusza

3.  Obramowanie w kolorze czerwonym pokazuje komórki tabeli przewozowej  (patrz pkt.3).

4.  Komórka na żółtym tle to komórka celu (patrz pkt.6):

2. W arkuszu, do którego przeniesiono dane wyjściowe utworzyć dodatkową tabelę, która będzie przechowywać informacje o koszcie jednostkowym transportu 1 palety pomiędzy każdym dostawcą a odbiorcą (problem da się rozwiązać i bez tej tabeli - zwiększa ona jednak czytelność rozwiązania i stąd propozycja jej wprowadzenia)

3. Utworzyć kolejną tabelę, która będzie przechowywać rozwiązanie problemu (wartości zmiennych decyzyjnych). Nazwiemy ją tabelą przewozową, ponieważ określa ilości towaru do przewiezienia pomiędzy określonymi nadawcami i odbiorcami.

4. Utworzyć komórki zliczające z tabeli przewozowej ilości towaru dowiezione do poszczególnych odbiorców

5. Utworzyć komórki zliczające z tabeli przewozowej ilości towaru wywiezione od poszczególnych dostawców

6. Utworzyć  komórkę celu  podającą dla danego stanu tabeli przewozowej koszt całkowity procesu transportowego (komórka celu to funkcja celu z modelu matematycznego)

7. Wykorzystując utworzony arkusz kalkulacyjny jako prosty system wspomagający proces obliczeniowy, zastosować dowolną znaną metodę analityczną do rozwiązania zadania (lub metodę „intuicyjną” w przypadku braku innej wiedzy ).

8. Porównać uzyskane wyniki z innymi osobami w grupie.

9. Zgłosić zakończenie tego etapu ćwiczenia prowadzącemu. Dalsza część zajęć poświęcona będzie wykorzystaniu narzędzia Solver do rozwiązania problemu transportowego i prowadzona będzie w trybie pracy „synchronicznej” na wszystkich stanowiskach.

10. Uruchomić moduł Solver (menu Narzędzia -> Solver) i skonfigurować parametry optymalizacji:

1. Komórka celu: komórka celu w arkuszu

2. Komórki zmieniane: tabela przewozowa w arkuszu (wnętrze!)

3. Warunki ograniczające: podaż>=wywiezione; popyt<=dowiezione, ·
   (jakie jeszcze nie wyrażone wprost w treści zadania?)

11. Rozwiązać zadanie poprzez kliknięcie przycisku Rozwiąż.

12. Porównać rozwiązanie własne z rozwiązaniem z Solver'a

Po zrealizowaniu powyższych kroków przystąpić do samodzielnego rozwiązania jednego z poniższych problemów:

1. Problem doboru asortymentu produktów dających największy zysk przy uwzględnieniu ograniczeń zapasów magazynowych komponentów składowych niezbędnych do wytwarzania poszczególnych produktów:

Firma produkuje 3 wyroby: Wyrób A, B i C używając standardowych części zgromadzonych w magazynie: Podzespół 1,2,3,4 oraz 5.

Zapasy części są ograniczone stąd należy określić najbardziej zyskowny zestaw wyrobów do wytworzenia. Ponadto, zysk na wytworzonej jednostce produktu zmniejsza się wraz ze wzrostem liczby wyprodukowanych sztuk ze względu na dodatkowe koszty i bodźce finansowe niezbędne w procesie dystrybucji (modelujemy to zjawisko poprzez tzw. wskaźnik zwrotu = 0,9). Stąd w obliczeniach:

zysk = zysk_jednostkowy*(ilość) ^{wskaźnik_zwrotu} [wskaźnik_zwrotu to potęga ilości !]

Poniższa tabela definiuje zapasy części oraz ilości poszczególnych części wchodzących w skład gotowego wyrobu A, B oraz C:

Nazwa części	Zapas	Wyrób A	Wyrób B	Wyrób C
Podzespół 1	450	1	1	0
Podzespół 2	250	1	0	0
Podzespół 3	800	2	2	1
Podzespół 4	450	1	1	0
Podzespół 5	600	2	1	1

Marżę zysku dla każdego produktu przedstawia poniższa tabela

	Wyrób A	Wyrób B	Wyrób C
Zysk	75	50	45

W związku z tym, iż zapasy magazynowe są ograniczone, problem polega na określeniu optymalnej ilości wyprodukowanych wyrobów A, B oraz C maksymalizujących zysk przy ustalonym stanie magazynu.

Proszę obliczyć najkorzystniejszy wariant ilościowy wytwarzanych produktów oraz podać zysk całkowity dla wskazanego wariantu. Obliczenia proszę wykonać również dla wskaźnika zwrotu =1 (skomentować w arkuszu zaobserwowane różnice).

2. Problem optymalnej obsady jednorodnych stanowisk o zmiennej w czasie funkcji obciążenia.

Należy znaleźć taką obsadę jednorodnych stanowisk, aby zminimalizować koszty płac przy zapewnionej wystarczającej liczbie personelu zdefiniowanej niezależnie dla każdego dnia tygodnia. Przy poszukiwaniu rozwiązania należy zapewnić iż każdy pracownik pracuje pięć kolejnych dni, a dwa następne dni odpoczywa (dwa dni wolne to niekoniecznie weekend). Wymaganą minimalną obsadę stanowisk na przestrzeni tygodnia przedstawia poniższa tabela:

Dzień tygodnia	N	Pn	Wt	Śr	Cz	Pt	So
Ilość pracowników:	22	17	13	14	15	18	24

Każdy z pracowników wykonuje taką samą pracę i otrzymuje takie samo wynagrodzenie
(przyjmijmy, choć nie jest to konieczne, iż dniówka pracownika wynosi 40 zł).

Proszę znaleźć minimalną obsadę stanowisk (podać wymaganą liczbę pracowników, grafik pracy, sumę tygodniowej pensji wszystkich pracowników - koszt zaproponowanego rozwiązania)

SPRAWOZDANIE

Sprawozdanie z ćwiczeń każdy student wykonuje samodzielnie.

Powinno ono zawierać:

1. Definicję powyższego problemu transportowego w postaci modelu matematycznego (sformalizować zapis funkcji celu i ograniczeń w postaci równań - ale dla konkretnego, rozwiązanego na laboratorium, przypadku).

2. Krótko omówić możliwe metody rozwiązania problemu (analityczne i numeryczne).

3. Omówić etapy tworzenia arkusza kalkulacyjnego, konfiguracji Solvera i uzyskane w ćwiczeniu wyniki. Jeśli wynikły rozbieżności pomiędzy rozwiązaniem analitycznym a komputerowym dokonać ich analizy.

4. Szczegółowo omówić etapy postępowania i uzyskane wyniki przy realizacji drugiego (wybranego) problemu optymalizacyjnego.

LITERATURA

1. Materiały wykładowe z przedmiotu Komputerowe Wspomaganie Procesów Logistycznych

2. Szapiro T. (red.), Decyzje menedżerskie z Excelem. Wydawnictwo PWE, Warszawa 2000

3. System pomocy on-line arkusza kalkulacyjnego Excel z dodatkiem Solver

POLITECHNIKA POZNAŃSKA	KIERUNEK: Transport		SEMESTR VII
		LABORATORIUM - KOMPUTEROWE WSPOMAGANIE PROCESÓW LOGISTYCZNYCH

1

opracowanie ćwiczenia - dr inż. Waldemar Walerjańczyk

---