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ć:
- Jaki będzie optymalny plan produkcji i wielkość maksymalnego przychodu
- Jaki będzie stopień wykorzystania poszczególnych surowców
Dodatkowo właściciel chciałby znać odpowiedzi na pytania typu „co, jeśli”:
- Jak zmieniłby się przychód, gdyby zniesiono ograniczenie dotyczące produktu C
- Czy wzrost ceny jednej sztuki produktu A do 10 zł zmieni plan produkcji i przychód?
- W jakim przedziale powinna znajdować się cena produktu B, aby optymalny plan produkcji nie uległ zmianie?
- Jaka musiałaby być cena produktu B, aby opłacało się zwiększyć jego produkcję?
- Czy opłaca się firmie zwiększyć zasób surowca S1 o jedną tonę, jeśli koszt zakupu tony tego surowca wynosi 150 zł?
- Czy na podstawie odpowiedniego raportu jesteśmy w stanie stwierdzić, ile wyniesie przychód firmy, gdy zasób surowca S3 spadnie o 2 tony?
- W jakim przedziale powinien się znaleźć zasób surowca S2, aby struktura rozwiązania nie uległa zmianie?
- 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.