Korzystanie z dodatku Solver w celu określenia optymalnego asortymentu produktu

Jak mogę ustalić miesięczny asortyment produktu, który maksymalizuje rentowność?

Firmy często muszą określić ilość każdego produktu, który ma być wytwarzany co miesiąc. W najprostszym formularzu problem z mieszaniem produktu polega na ustaleniu kwoty każdego produktu, który ma zostać wyprodukowany w ciągu miesiąca, aby zmaksymalizować zyski. Kombinacja produktów musi zwykle spełniać następujące warunki:

• Miks produktu nie może korzystać z większej liczby zasobów niż jest dostępne.

• Dla każdego produktu istnieje ograniczone zapotrzebowanie. Nie możemy utworzyć większej ilości produktu w ciągu miesiąca niż dzwonienie, ponieważ nadwyżka produkcji jest zajęta (na przykład nietrwały narkotyk).

Teraz rozwiążemy problem z przykładem miksu produktu. Rozwiązanie tego problemu można znaleźć w pliku prodmix. xlsx przedstawionym na rysunku 27-1.

Załóżmy, że pracujemy nad firmą narkotykową, która produkuje w swoim zakładzie sześć różnych produktów. Produkcja każdego produktu wymaga robocizny i surowca. W wierszu 4 na rysunku 27-1 jest wyświetlana liczba godzin robocizny potrzebnych do wygenerowania funta każdego produktu, a w wierszu 5 przedstawiono funty surowca potrzebnego do wytworzenia funta każdego produktu. Na przykład wytworzenie funta produktu 1 wymaga sześciu godzin robocizny i 3,2 funtów surowca. Dla każdego narkotyku cena za funt jest podana w wierszu 6, koszt jednostkowy na Funt jest podany w wierszu 7, a udział w zysku na funtach jest podany w wierszu 9. Na przykład produkt 2 sprzedaje się na $11,00 za funt, ponosi koszt jednostkowy w wysokości $5,70 za funt, a program Contribute $5,30 zysk na funtach. Zapotrzebowanie na miesiąc każdego narkotyku jest podane w wierszu 8. Na przykład popyt na produkt 3 wynosi 1041 funtów. W tym miesiącu dostępne są 4500 godzin robocizny i 1600 funty surowca. W jaki sposób ta firma może zmaksymalizować miesięczne zyski?

Jeśli nie mamy nic na temat dodatku Solver dla programu Excel, wystąpił ten problem, tworząc arkusz w celu śledzenia zużycia zysku i zasobów związanych z produktem mix. Następnie będziemy korzystać z wersji próbnej i błędu w celu zamieszania produktu w celu zoptymalizowania zysku bez korzystania z większej ilości robocizny lub surowca, niż jest on dostępny i bez wyprodukowania żadnego narkotyku poza popytem. Używamy dodatku Solver w tym procesie tylko na etapie próbno-i-Error. W zasadzie dodatek Solver jest aparatem optymalizacyjnym, który flawlessly przeprowadzać wyszukiwanie próbne i błędy.

Klucz do rozwiązywania problemów z mieszaniem produktu polega na efektywnym obliczaniu obciążenia zasobów i zysku związanego z danym asortymentem produktu. Ważne narzędzie, za pomocą którego można wykonać to obliczenie, to funkcja iloczynów. Funkcja iloczynów mnoży odpowiadające sobie wartości w zakresach komórek i zwraca sumę tych wartości. Każdy zakres komórek użyty w ocenie iloczynów musi mieć te same wymiary, co oznacza, że można użyć iloczynów z dwoma wierszami lub dwiema kolumnami, ale nie z pojedynczą kolumną i jednym wierszem.

Jako przykład, w jaki sposób możemy korzystać z funkcji iloczynów. w naszych przykładach miksu produktów, spróbujemy obliczyć nasze obciążenie zasobów. Nasze wykorzystanie robocizny jest obliczane przez

(Robocizna wykorzystana za 1 funtów) * (produkcja 1 funtów wyprodukowana) +
(robocizna używana za funta 2) * (wyprodukowano 2 funty) +...
(Robocizna wykorzystana za 1 funtów *) * (wyprodukowana jest 6 funtów)

Możemy obliczyć zużycie robocizny w bardziej nużące sposób, jak D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * i4. Podobnie, zużycie surowca może zostać obliczone jako D2 * od + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * i5. Jednak wprowadzenie tych formuł w arkuszu dla sześciu produktów jest czasochłonne. Wyobraźmy sobie, jak długo zajmiesz się, jeśli pracujesz z firmą, która wyprodukowała, na przykład produkty 50 w ich zakładach. Znacznie łatwiej jest obliczyć zużycie robocizny i surowców, aby skopiować je z D14 do D15 formuły iloczynów ($D $2: $I $2, D4: i4). W tej formule jest obliczana D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * i4 (to jest zużycie w naszej robociznie), ale jest znacznie łatwiejsze do wprowadzenia! Zwróć uwagę, że używam podpisu $ z zakresem D2: I2, aby po skopiowaniu formuły przechwycić produkt mix z wiersza 2. Formuła w komórce D15 oblicza zużycie surowców.

W podobny sposób zysk jest określany na podstawie

(Zysk 1-procentowy na funt) * (wyprodukowano 1 funtów) +
(zysk 2-procentowy na funt) * (wyprodukowano 2 funty) +...
(Zysk z zysku 6 zł za 1 funt) * (wyprodukowana jest 6 funtów)

Zysk jest w prosty sposób obliczany w komórce D12 z formułą iloczynów (D9: i9, $D $2: $I $2).

Teraz możemy znaleźć trzy składniki naszego modelu dodatku Solver mix.

• Komórka Target.Naszym celem jest zmaksymalizowanie zysku (obliczanego w komórce D12).

• Zmienianie komórek.Liczba funtów wyprodukowanych dla każdego produktu (wymieniona w zakresie komórek D2: I2)

• Wstrzymywać. Mamy następujące ograniczenia:

  • Nie używaj większej liczby robocizny lub surowców niż jest to dostępne. Oznacza to, że wartości w komórkach D14: D15 (używane zasoby) muszą być mniejsze lub równe wartościom w komórkach F14: F15 (dostępne zasoby).

  • Nie produkuje więcej narkotyków niż jest to na żądanie. Oznacza to, że wartości w komórkach D2: I2 (funty wyprodukowane z każdego narkotyku) muszą być mniejsze lub równe zapotrzebowaniu na każdy narkotyk (wymieniony w komórkach D8: i8).

  • Nie możemy uzyskać ujemnej kwoty żadnego narkotyku.

Pokażę, jak wprowadzić komórkę docelową, zmieniające komórki i ograniczenia na Solver. Następnie wystarczy kliknąć przycisk Rozwiąż, aby znaleźć produkt z możliwością maksymalizowania zysku.

Aby rozpocząć, kliknij kartę dane, a następnie w grupie Analiza kliknij pozycję Solver.

Zostanie wyświetlone okno dialogowe Parametry dodatku Solver, jak pokazano na rysunku 27-2.

Kliknij pole Ustaw komórkę doCelową, a następnie wybierz pozycję nasza komórka zysku (komórka D12). Kliknij pole przez zmianę komórek, a następnie wskaż zakres D2: I2, który zawiera funty wyprodukowane dla każdego narkotyku. Okno dialogowe powinno teraz wyglądać pod obrazem 27-3.

Teraz możesz dodać ograniczenia do modelu. Kliknij przycisk Dodaj. Zostanie wyświetlone okno dialogowe Dodawanie ograniczenia, wyświetlane na rysunku 27-4.

Aby dodać ograniczenia dotyczące użycia zasobów, kliknij pole odwołanie do komórki, a następnie wybierz zakres D14: D15. Wybierz < = z środkowej listy. Kliknij pole ograniczenia, a następnie wybierz zakres komórek F14: F15. Okno dialogowe Dodawanie ograniczenia powinno teraz wyglądać następująco: Rysunek 27-5.

Zapewniamy, że gdy dodatek Solver próbuje różne wartości dla zmienianych komórek, tylko kombinacje spełniające oba D14< = F14 (używana robocizna jest mniejsza niż lub równa robocizny) i D15< = F15 (użyte surowce jest mniejsze niż lub równe będą brane pod uwagę dostępne surowce). Kliknij przycisk Dodaj, aby wprowadzić ograniczenia popytu. Wypełnij okno dialogowe Dodawanie ograniczenia, jak pokazano na rysunku 27-6.

Dodanie tych ograniczeń zapewnia, że gdy dodatek Solver próbuje inne kombinacje zmian wartości komórek, będą brane pod uwagę tylko te kombinacje, które spełniają następujące parametry:

• D2< = D8 (kwota wyprodukowana 1 jest mniejsza lub równa zapotrzebowaniu na narkotyk 1)

• E2< = E8 (kwota wyprodukowanego narkotyku 2 jest mniejsza niż lub równa zapotrzebowaniu na narkotyk 2)

• F2< = F8 (ilość zrealizowanych środków na 3 rzecz jest mniejsza niż lub równa zapotrzebowaniu na narkotyki 3)

• G2< = G8 (kwota wyprodukowana z 4 leków jest mniejsza niż lub równa zapotrzebowaniu na narkotyk 4)

• H2< = H8 (ilość uzyskana z przeprowadzonych narkotyków jest mniejsza niż lub równa zapotrzebowaniu na 5)

• I2< = i8 (kwota wyprodukowana z poziomu 6 jest mniejsza niż lub równa zapotrzebowaniu na narkotyk 6)

Kliknij przycisk OK w oknie dialogowym Dodawanie ograniczenia. Okno funkcji Solver powinno wyglądać następująco: Rysunek 27-7.

Wprowadzamy ograniczenie, które zmienia komórki w oknie dialogowym Opcje dodatku Solver muszą być nieujemne. Kliknij przycisk Opcje w oknie dialogowym Parametry dodatku Solver. Zaznacz pole Przyjmij model liniowy i pole przyjmij nieUjemnie, jak pokazano na rysunku 27-8 na następnej stronie. Kliknij przycisk OK.

Zaznaczenie pola przyjmij nieUjemne pole wyboru gwarantuje, że dodatek Solver uzna tylko kombinacje zmienianych komórek, w których każda zmieniona komórka przyjmuje wartość nieujemną. Pole Przyjmij model liniowy jest zaznaczone, ponieważ problem z mieszaniem produktu jest specjalnym typem dodatku Solver, nazywanym modelem liniowym. Zasadniczo model dodatku Solver jest liniowy pod następującymi warunkami:

• Komórka Target jest obliczana przez dodanie razem warunków formularza (zmieniając komórkę) * (stała).

• Każde ograniczenie spełnia wymagania "model liniowy". Oznacza to, że każde ograniczenie jest oceniane przez dodanie do niego warunków formularza (zmieniając komórkę) * (stała) i porównanie sum z stałą.

Dlaczego problem jest występujący w dodatku Solver? Nasza komórka Target (zysk) jest obliczana jako

(Zysk 1-procentowy na funt) * (wyprodukowano 1 funtów) +
(zysk 2-procentowy na funt) * (wyprodukowano 2 funty) +...
(Zysk z zysku 6 zł za 1 funt) * (wyprodukowana jest 6 funtów)

To obliczenie jest zgodne ze wzorcem, w którym wartość komórki docelowej jest uzyskiwana przez dodanie razem warunków formularza (zmiana komórki) * (stała).

Nasze ograniczenie robocizny ocenia się, porównując wartość pochodną od (robocizna wykorzystana za 1 funtów) * (wytworzono 1 funty) + (robocizna wyprodukowana w funtach 2) * (wyprodukowano 2 funty) +... (Robocizna w Stanach Zjednoczonych ) Ed na Funt o wartości 6) * (wytworzono więcej niż 6 funtów) do dostępnej robocizny.

W związku z tym ograniczenie robocizny jest oceniane przez dodanie razem warunków formularza (zmiana komórki) * (stała) i porównanie sum z stałą. Zarówno ograniczenie robocizna, jak i ograniczenie surowca spełniają wymagania modelu liniowego.

Nasze ograniczenia popytu zajmują formularz

(Wyprodukowana wartość 1) < = (zapotrzebowanie na
narkotyki 1) (wyprodukowana wartość 2) < = (narkotyk 2
) §
(wyprodukowana wartość 6) < = ( narkotyka 6 popytu)

Każde ograniczenie na żądanie spełnia także wymagania modelu liniowego, ponieważ każdy z nich jest obliczany przez dodanie do siebie warunków formularza (zmiana komórki) * (stała) i porównanie sum z stałą.

Po sprawdzeniu, że nasz model miksu produktów jest modelem liniowym, dlaczego warto zadbać o to?

• Jeśli model dodatku Solver jest liniowy, a wybieram pozycję Przyjmij model liniowy, funkcja Solver gwarantuje optymalne rozwiązanie w modelu dodatku Solver. Jeśli model dodatku Solver nie jest liniowy, dodatek Solver może lub nie może znaleźć optymalnego rozwiązania.

• Jeśli model dodatku Solver jest liniowy, a wybieram pozycję Przyjmij model liniowy, dodatek Solver używa bardzo wydajnego algorytmu (metody jednostronne), aby znaleźć optymalne rozwiązanie. Jeśli model dodatku Solver jest liniowy i nie wybieramy opcji Przyjmij model liniowy, dodatek Solver używa bardzo niewydajnego algorytmu (metody GRG2) i może mieć trudności z znalezieniem optymalnego rozwiązania modelu.

Po kliknięciu przycisku OK w oknie dialogowym Opcje dodatku Solver powrócimy do głównego okna dialogowego dodatku Solver, pokazanego wcześniej na rysunku 27-7. Gdy klikam pozycję Rozwiąż, dodatek Solver obliczy optymalne rozwiązanie (jeśli istnieje) dla modelu miksu produktów. Jak stwierdzono w rozdziale 26, najlepszym rozwiązaniem w modelu miksu produktu jest zestaw zmian wartości komórek (funty wyprodukowane dla każdego narkotyku), które maksymalizują zyski wszystkich możliwych rozwiązań. Z tego względu jest to, że możliwe rozwiązanie to zestaw zmian wartości komórek spełniających wszystkie ograniczenia. Zmiany wartości komórek przedstawionych na rysunku 27-9 to wykonalne rozwiązanie, ponieważ wszystkie poziomy produkcji są nieujemne, poziomy produkcji nie przekraczają popytu, a użycie zasobów nie przekracza dostępnych zasobów.

Zmiany wartości komórek przedstawionych na rysunku 27-10 na następnej stronie stanowią niewykonalne rozwiązanie z następujących powodów:

• Oferujemy więcej niż 5-tego zapotrzebowania.

• Używamy więcej robocizny niż jest to dostępne.

• Używamy bardziej surowych materiałów, niż są dostępne.

Po kliknięciu przycisku Rozwiąż dodatek Solver szybko znajdzie optymalny roztwór przedstawiony na rysunku 27-11. Musisz wybrać pozycję Zachowaj rozwiązanie dodatku Solver, aby zachować optymalne wartości w arkuszu.

Nasze przedsiębiorstwo z narkotykami może zmaksymalizować miesięczny zysk na poziomie $6 625,20 przez produkowanie 596,67 funta 4, 1084 funtów 5 i żadnych innych narkotyków! Nie możemy ustalić, czy można uzyskać maksymalny zysk $6 625,20 w inny sposób. Wszystko, o czym możemy się upewnić, że masz ograniczone zasoby i popyt, nie ma sposobu na dokonywanie ponad $6 627,20 w tym miesiącu.