Optymalizacja asortymentu produkcji

Optymalizacja asortymentu produkcji

Firma zamierza uruchomić produkcję trzech wyrobów A, B i C (w tys. sztuk). Do wytworzenia tychże wyrobów potrzebne są trzy limitowane surowce S1, S2 i S3. Nakłady surowców na wytworzenie jednostki produktu, możliwości przerobu surowców oraz oczekiwane ceny zbytu podaje poniższa tabela.

Dla przykładu do wytworzenia surowca A potrzeba 8 jednostek surowca S1, 10 jednostek surowca S2 i 2 jednostki surowca S3. Firma dąży do maksymalizacji przychodu. Wiadomo, że:

– ze wzg. na wysokie koszty magazynowania surowiec S3 ma być wykorzystany w całości

– wielkość produkcji produktu A powinna być przynajmniej dwukrotnie wyższa od produkcji produktu B

– wielkość produkcji produktu C nie może przekroczyć 2500 szt.

Pytania dotyczące asortymentu produkcji

Właściciel przedsiębiorstwa chciałby wiedzieć:

1. Jaki będzie optymalny plan produkcji i wielkość maksymalnego przychodu
2. Jaki będzie stopień wykorzystania poszczególnych surowców

Dodatkowo właściciel chciałby znać odpowiedzi na pytania typu „co, jeśli”:

1. Jak zmieniłby się przychód, gdyby zniesiono ograniczenie dotyczące produktu C
2. Czy wzrost  ceny jednej sztuki produktu A do 10 zł zmieni plan produkcji i przychód?
3. W jakim przedziale powinna znajdować się cena produktu B, aby optymalny plan produkcji nie uległ zmianie?
4. Jaka musiałaby być cena produktu B, aby opłacało się zwiększyć jego produkcję?
5. Czy opłaca się firmie zwiększyć zasób surowca S1 o jedną tonę, jeśli koszt zakupu tony tego surowca wynosi 150 zł?
6. Czy na podstawie odpowiedniego raportu jesteśmy w stanie stwierdzić, ile wyniesie przychód firmy, gdy zasób surowca S3 spadnie o 2 tony?
7. W jakim przedziale powinien się znaleźć zasób surowca S2, aby struktura rozwiązania nie uległa zmianie?
8. Jaka jest minimalna wielkość produkcji wyrobu C, przy której plan produkcji jest dopuszczalny?

Aby rozwiązać to zagadnienie należy w pierwszej kolejności zapisać warunki ograniczające surowców, produktów i cen w model optymalizacyjny. W tym przykładzie będzie to wyglądać następująco:

6A+ 12B + 10C ≥ max

8A + 16B + 4C ≤ 80

10A + 4B + 10C ≤ 100

2A + 10B + 10C = 50

1A – 2B ≥ 0

C ≤2,5

6A+ 12B + 10C ≥ max; ponieważ maksymalizujemy przychód firmy a więc 6 zł * ilość produktu A + 12 zł * il produktu B + 10 zł * il produktu C ma być maksymalna.

8A + 16B + 4C ≤ 80; ograniczenie to przedstawia zasób surowca S1, którego firma posiada 80.000 ton. Do jednostki produktu A zużywane jest 8 tony surowca S1, dla produktu B 16 ton a dla produktu C firma zużywa w produkcji 10 ton tego surowca.

10A + 4B + 10C ≤ 100 dotyczy surowca S2, gdzie firma ma go tylko 100.000 ton.

2A + 10B + 10C = 50 to ograniczenie dla surowca S3. Znak równości to spełnienie warunku, iż ze względu na wysokie koszty magazynowania, należy go wykorzystać w całości.

1A – 2B ≥ 0 to warunek mówiący o tym iż produktu A należy wyprodukować minimum 2 razy więcej niż produktu B

C ≤2,5 mówi o tym, iż ilość produktu C nie może przekroczyć 2.500 sztuk.

Warunki ograniczające

Aby można było rozwiązać ten problem należy te dane w odpowiedni sposób zapisać w Excelu. Na rysunku poniżej przedstawiony został sposób w jaki należy umieścić te dane.

W komórka D2, E2 i F2 wstawiono 0 ponieważ w tych komórkach pokaże się wynik naszych obliczeń dotyczący optymalnej produkcji poszczególnych produktów.

W wierszu 4 przedstawiono ceny poszczególnych produktów. I tak produkt A to przychód w wysokości 6 zł (komórka D4), produkt B to 12 zł (komórka E4) a produkt C to 10 zł (komórka F4). Wiersz 6 to warunki dotyczące surowca S1. Firma posiada 80.000 ton tego surowca (komórka C6), w produkcji wyrobu A zużywane jest 8 ton, dla produktu B 16 ton a przy produkcji wyrobu C 4 tony. Wiersz 7 to warunki ograniczające dla surowca S2 a wiersz 8 dla surowca S3. Wiersz 9 to ograniczenie mówiące o tym iż produkt A należy wyprodukować przynajmniej dwa razy więcej niż produktu B. Wiersz 10 przedstawia informację, iż produktu C należy wyprodukować mniej niż 2.500 sztuk.

W kolumnie B należy dodać warunki dla każdego z produktów, tak jak na rysunku poniżej.

W komórce B4 funkcja ma postać =D2*D4+E2*E4+F2*F4, z której wynika, iż przychód firmy to cena produktu A * ilość produktu A + cena produktu B * ilość produktu B + cena produktu C * ilość produktu C. Ten sam schemat został wykorzystany w komórce B6 gdzie funkcja ma postać =D2*D6+E2*E6+F2*F6. W pozostałych komórka w kolumnie B schemat ten został powtórzony aż do wiersza 10.

Aby ułatwić kopiowanie funkcji z komórki B4 do pozostałych komórek w kolumnie B można użyć funkcji SUMA.ILOCZYNÓW dostępnej na karcie „narzędzia główne” w menu „autosumowanie”. Okno dialogowe dla tej funkcji należy wypełnić jak na rysunku poniżej

A później należy kliknąć w na komórkę B4 i na pasku funkcji dodać znaki $ przy komórkach D2, E2 i F2.

Po tym zabiegu należy znów kliknąć w komórkę B4 i pociągnąć za dolny prawy róg aż do komórki B10. Rysunek poniżej przedstawia funkcję która powstała w komórce B10.

Analiza asortymentu produkcji

Aby odpowiedzieć na wszystkie pytania właściciela firmy należy skorzystać z narzędzia Solver dostępnego w menu „dane”. Okno dialogowe wypełniamy w następujący sposób:

Ustaw cel: komórka B4, czyli maksymalizacja przychodu firmy, poniżej zaznaczamy „max”.

Przez zmienianie komórek zmiennych – komórki D2:F2 gdzie pokaże się wynik obliczeń.

Podlegających ograniczeniom – tutaj należy wstawić warunki ograniczające. W tym celu należy kliknąć pole „dodaj” znajdujące się obok okna. Wyskoczy kolejne okno dialogowe które należy wypełnić dla każdego warunku oddzielnie po czym kliknąć pole „dodaj”.

W tym przypadku to okno wypełniamy w następujący sposób:

B6:B7 ≤ C6:C7

B8 = C8

B9 ≥ C9

B10 ≤C10

Po tych warunkach klikamy OK i wracamy do okna dialogowego solver gdzie warunki powinny się pojawić tak jak na rysunku:

Dodatkowo w tym oknie należy zaznaczyć „LP simpleks” w polu „wybierz metodę rozwiązywania” a także zaznaczyć pole przy „ustaw wartości nieujemne dla zmiennych bez ograniczeń”.

Po wszystkim należy kliknąć „rozwiąż” i powinno wyskoczyć okno dialogowe „wyniki dodatku solver” gdzie w polu raporty podświetlamy kliknięciem wszystkie 3 raporty: wyników, wrażliwości, granic.

Po kliknięciu w OK Solver pokazuje wynik optymalny dla tego zagadnienia.

Optymalny wybór asortymentu produkcji

Dodatkowo pojawiły się trzy nowe arkusze z raportami wyników, wrażliwości i granic. W arkuszu gdzie wstawialiśmy dane w komórce B4 jest pokazany maksymalny przychód na jaki firma może liczyć przy warunkach ograniczających dla produktów i surowców i wynosi on 77,50 tys. zł. Produkt A należy wyprodukować 6,25 tys. szt. (komórka D2), produkt B 1,25 tys. szt. (komórka E2), produkt C 2,5 tys. szt.(komórka F2). W komórce B6 pokazano iż wykorzystano wszystkie 80.000 ton surowca S1, w komórce B7 z 50 tys. ton dostępnego surowca S2 wykorzystano 92,50 tys. ton. Komórka B8 to informacja iż wykorzystano 50 tys ton surowca S3.

Te same wyniki można znaleźć w raporcie wyników.

W komórkach E21:E23 podane zostały ilości produktów jakie należy wyprodukować aby przychód był maksymalny (komórka E16). Wykorzystane ilości surowców zostały podane w komórkach D28:D32. W komórkach F28:F32 jest informacja czy dane ilości surowców zostały wykorzystane w 100%. Jeśli jest jakiś zapas to excel podaje tą informację jako „niewiążące”. W komórkach G28:G32 jest informacje ile zostało wolnego surowca.

Odpowiedzi na pytania dotyczące zmian cen i ilości dostępnych surowców a przez to zmiany przychodów firmy znajdują się w raporcie granic i wrażliwości, które zostały omówione pod tym adresem.