LABORATORIUM - KOMPUTEROWE WSPOMAGANIE PROCESÓW LOGISTYCZNYCH
Temat: Wykorzystanie narzędzi optymalizacyjnych typu Solver / Excel
Wykonali:	Roman Moch Robert Macniak	Semestr		Wydział	Kierunek - grupa
				VII		MRiT	Transport drogowy 1
Prowadzący: dr inż. Waldemar Walerjańczyk			Data: 18-10-2006 rok

1. 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.”

2. Definicja problemu transportowego w postaci modelu matematycznego:

Szukamy minimum funkcji celu - kosztu całkowitego przewozu:

k_mn - to koszt przewozu od dostawcy m do odbiorcy n

m =1, 2, 3 (1 - Poznań, 2 - Warszawa, 3 - Kraków)

n =4, 5, 6, 7 (4 - Toruń, 5 - Wrocław, 6 - Łódź, 7 - Tarnobrzeg)

Koszt transportu pomiędzy zakładami dla podanego przykładu jest to iloczyn ilości przewiezionych towarów, odległości pomiędzy zakładami oraz jednostkowego kosztu przewiezienia towaru na kilometr.

Q_mn - to ilość przewiezionych towarów [palet]

S_mn - odległość między zakładami [km]

p - jednostkowy koszt przewozu palety ( u nas 0,09 zł/paleta *km)

Ograniczeniami są popyt odbiorców i podaż dostawców.

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

Ilość wywiezionych towarów nie może być większa od podaży danego zakładu:

, dla m=1,2,3

Suma dowiezionych towarów D nie może być większa od popytu:

, dla n=4, 5, 6, 7

Towary są wożone tylko od dostawców do odbiorców:

Wielkość ładunku musi być liczbą całkowitą, gdyż wozimy całe palety:

3. Analityczna metoda rozwiązania problemu:

Moglibyśmy rozwiązać problem ten analitycznie, jako iż nie jest zbyt skomplikowany.

Najpierw sprawdzamy czy zadanie jest problemem zbilansowanym - w tym wypadku jest.

Następnie wyznaczamy rozwiązanie bazowe np. metodą północno-zachodniego kąta.

Macierz kosztów jednostkowych wygląda następująco

Tabela 1

Zakłady:	Koszty z zakładu x do magazynu y
		4. Toruń	5. Wrocław	6. Łódź	7. Tarnobrzeg
1. Poznań	13,59	16,02	19,08	41,13
2. Warszawa	18,81	22,32	12,06	19,53
3. Kraków	34,56	24,12	19,8	15,75

Poszukujemy rozwiązania optymalnego problemu za pomocą np. metody kosztów kary.

I tak zauważamy, że koszt kary dla komórki 2,4 jest ujemny, a zatem wstawiamy tam wartość 140:

Po raz kolejny sprawdzamy komórki zerowe i widzimy, że wszystkie komórki mają dodatni koszt kary a więc jest to rozwiązanie optymalne dla którego koszt całkowity przewozu wynosi: 23769,9 zł

Łatwiej jest posłużyć się jednak metodami numerycznymi:

Jak na przykład posłużenie się dodatkiem Solver.

4. Numeryczne metody rozwiązania problemu (Solver)

W pierwszym kroku tworzymy tabelę kosztów przewozu (Tabela 1).

Następnie tworzymy tabelą zmiennych decyzyjnych:

Tabela przewozowa - rozwiązanie problemu
	4. Toruń	5. Wrocław	6. Łódź	7. Tarnobrzeg		Wywiezione	Podaż
1. Poznań	0	0	0	0		600	0
2. Warszawa	0	0	0	0		540	0
3. Kraków	0	0	0	0		410	0
Dowiezione	350	480	400	320
Popyt	0	0	0	0
Koszt całkowity		0,00 zł

Komórki „wywiezione” i „dowiezione” są sumą ilości towaru wywiezionych i dowiezionych do zakładów.

Komórka „całkowity koszt transportu” to komórka która jest sumą iloczynów macierzy wielkości przewozów i macierzy z kosztem przewozu jednej palety z zakładu produkcyjnego do odbiorczego.

Następnie w dodatku Solver należy wstawić warunki ograniczające:

Ustawienia:

„Komórka celu” - komórka „całkowity koszt transportu”

„Równa” min - zaznaczenie, że poszukujemy minimum funkcji celu.

„Komórki zmieniane” - komórki macierzy wielkości przewozów.

Ograniczenia:

• warunek nie pozwalający na wywiezienie większej ilości towarów, niż wynika to z podaży

• warunek całkowitości przewożonych jednostek ładunkowych

• warunek wymuszający, by dostawy były równe popytowi

Po wprowadzeniu takich ograniczeń należy jeszcze w opcjach ustawić aby przybrał model liniowy i przyjął nieujemne, po czym można przystąpić do rozwiązywania, które wygląda tak:

Tabela przewozowa - rozwiązanie problemu
	4. Toruń	5. Wrocław	6. Łódź	7. Tarnobrzeg		Wywiezione	Podaż
1. Poznań	210	390	0	0		600	600
2. Warszawa	140	0	400	0		540	540
3. Kraków	0	90	0	320		410	410
Dowiezione	350	480	400	320
Popyt	350	480	400	320
Koszt całkowity		23 769,90 zł

5. Wnioski:

Zarówno metoda analityczna jak i numeryczna daje takie same wartości rozwiązania. Nieskomplikowane zadania można rozwiązywać metodą analityczną, przy bardziej skomplikowanych zadaniach metoda numeryczna pozwala zaoszczędzić czas i uniknąć błędów.

6. Drugi problem optymalizacyjny na przykładzie problemu doboru asortymentu produktów.

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:

Dane:

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

Arkusz z rozwiązaniem został utworzony z tabeli ze zmiennymi, w które Solver będzie wstawiał odpowiednie wartości wzbogaconą o kolumnę zużytych części, które mamy jako zapas.

W kolumnie „Zużyto” wstawiono formułę sumującą dane podzespoły np.:

W komórce L6=SUMA(I6:K6)

Kolejną tabelką było zestawienie zysku oraz ilości wyrobu:

Następnie przystąpiliśmy do napisania funkcji celu, która jest maksymalizacja zysku:

F.celu = maxΣ(zysk z wyrobu)*(ilość wyprodukowanego wyrobu)^0,9

Następnie przystąpiliśmy do konfiguracji Solvera i przyjęliśmy ograniczenia:

Komórka celu - zysk całkowity(maksymalizacja)

Komórki zmieniane to komórki ilości wyrobów.

Warunki ograniczające:

Ilość wyrobów musi być liczbą całkowitą, większą lub równą zeru

Kolumna zużyte musi być mniejsza, bądź równa kolumnie zapas.

W opcjach należy zaznaczyć przyjmij nieujemne.

Tak wyglądał mój arkusz po rozwiązaniu:

Wnioski:

Wartość maksymalnego zysku zależy od przyjętego wskaźnika zwrotu. Różnica jest duża blisko dwukrotnie większa przy wartości współczynnika równego 1.

W celu uzyskania prawidłowego wyniku należy pamiętać o odpowiednich ograniczeniach:

- zmienne powinny przyjmować wartości całkowite i dodatnie

- suma użytych podzespołów danego typu nie może przekraczać dostępnych zapasów

---