Určení optimálního mixu produktů pomocí Řešitele

Tento článek popisuje použití Řešitele – doplňku Microsoft Excelu, který můžete používat pro citlivostní analýzu, k určení optimální kombinace produktů.

Jak mohu určit měsíční produktový mix, který maximalizuje ziskovost?

Společnosti často potřebují určit množství každého produktu, které mají vyrábět na měsíční bázi. Ve své nejjednodušší podobě problém produktového mixu zahrnuje, jak určit množství každého produktu, které by mělo být vyrobeno během měsíce, aby se maximalizovaly zisky. Produktový mix musí obvykle splňovat následující omezení:

• Produktový mix nemůže využívat více zdrojů, než je k dispozici.
• Po každém produktu je omezená poptávka. Nemůžeme během měsíce vyrobit více produktu, než vyžaduje poptávka, protože přebytečná produkce je zbytečná (například lék podléhající rychlé zkáze).

Pojďme nyní vyřešit následující příklad problému produktového mixu. Řešení tohoto problému najdete v souborovém Prodmix.xlsx, znázorněném na obrázku 27-1.

Řekněme, že pracujeme pro farmaceutickou společnost, která ve svém závodě vyrábí šest různých produktů. Výroba každého produktu vyžaduje práci a suroviny. Řádek 4 na obrázku 27-1 ukazuje hodiny práce potřebné k výrobě libry každého produktu a řádek 5 ukazuje libry suroviny potřebné k výrobě libry každého produktu. Například výroba libry produktu 1 vyžaduje šest hodin práce a 3,2 libry suroviny. Pro každý lék je cena za libru uvedena v řádku 6, jednotkové náklady na libru je uvedeny v řádku 7 a příspěvek zisku na libru je uveden v řádku 9. Například výrobek 2 se prodává za 11,00 Kč za libru, má jednotkové náklady 5,70 Kč za libru a přispívá ziskem 5,30 Kč za libru. Měsíční poptávka po každém léku je uvedena v řádku 8. Například poptávka po produktu 3 je 1041 liber. Tento měsíc je k dispozici 4500 hodin práce a 1600 liber surovin. Jak může tato společnost maximalizovat svůj měsíční zisk?

Kdybychom o Řešiteli v Excelu nic nevěděli, vyřešili bychom tento problém vytvořením listu pro sledování zisku a využití zdrojů spojených s daným produktovým mixem. Pak bychom použili pokus a omyl k obměně produktového mixu, abychom optimalizovali zisk, aniž bychom spotřebovali více práce nebo surovin, než je k dispozici, a aniž bychom vyrobili jakýkoli lék převyšující poptávku. Řešitele v tomto procesu používáme jenom ve fázi pokusů a omylů. Řešitel je v podstatě optimalizační modul, který bezchybně provádí vyhledávání pokusů a omylů.

Klíčem k vyřešení problému produktového mixu je efektivní výpočet využití zdrojů a zisku spojeného s daným produktovým mixem. Důležitým nástrojem, který můžeme použít k provedení tohoto výpočtu, je funkce SOUČIN.SKALÁRNÍ. Funkce SOUČIN.SKALÁRNÍ vynásobí odpovídající hodnoty v oblastech buněk a vrátí součet těchto hodnot. Každá oblast buněk použitá ve vyhodnocení funkce SOUČIN.SKALÁRNÍ musí mít stejné rozměry, což znamená, že funkci SOUČIN.SKALÁRNÍ lze použít se dvěma řádky nebo se dvěma sloupci, nikoli však s jedním sloupcem a jedním řádkem.

Jako příklad použití funkce SOUČIN.SKALÁRNÍ v našem příkladu produktového mixu zkusíme vypočítat naše využití prostředků. Naše využití práce se vypočítává podle

(Práce spotřebovaná na libru léku 1)*(Vyrobený lék 1 libra)+
(Práce spotřebovaná na libru drogy 2)*(Droga 2 libry vyrobena) + ...
(Práce spotřebovaná na libru drogy 6)*(Droga 6 vyrobených liber)

Spotřebu práce bychom mohli vypočítat zdlouhavějším způsobem jako D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. Podobně lze využití surovin vypočítat jako D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Zadávání těchto vzorců do listu pro šest produktů je ale časově náročné. Představte si, jak dlouho by to trvalo, kdybyste pracovali se společností, která ve svém závodě vyrábí například 50 produktů. Mnohem jednodušším způsobem, jak vypočítat spotřebu práce a surovin, je zkopírovat z buňky D14 do buňky D15 vzorec SOUČIN.SKALÁRNÍ($D$2:$I$2;D4:I4). Tento vzorec počítá D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (což je naše pracovní využití), ale je mnohem jednodušší ho zadat! Všimněte si, že používám znaménko $ s rozsahem D2:I2, takže když zkopíruji vzorec, stále zachycuji produktový mix z řádku 2. Vzorec v buňce D15 počítá využití surovin.

Podobným způsobem je náš zisk určen

(Droga 1 zisk na libru)*(Droga 1 libra vyrobena) +
(Droga 2 zisk na libru) * (Droga 2 libry vyrobena) + ...
(Droga 6 zisk na libru)*(Droga 6 vyrobených liber)

Zisk se snadno vypočítá v buňce D12 pomocí vzorce SOUČIN.SKALÁRNÍ(D9:I9;$D 2:$I 2Kč).

Nyní můžeme v našem modelu Řešitele produktového mixu rozeznat tři složky.

• Cílová buňka. Naším cílem je maximalizovat zisk (počítáno v buňce D12).

• Měněné buňky. Počet liber vyrobených z každého produktu (uvedený v oblasti buněk D2:I2)

• Omezení. Pro nás platí následující omezení:

  • Nepoužívejte více práce nebo surovin, než je k dispozici. To znamená, že hodnoty v buňkách D14:D15 (použité zdroje) musí být menší nebo rovny hodnotám v buňkách F14:F15 (dostupné zdroje).
  • Nevyrábějte více drogy, než je poptávka. To znamená, že hodnoty v buňkách D2:I2 (libry vyrobené z každého léku) musí být menší nebo rovny poptávce po každém léku (uvedené v buňkách D8:I8).
  • Nemůžeme vyrobit záporné množství žádného léku.

Ukážu vám, jak zadat cílovou buňku, změnit buňky a omezení do Řešitele. Pak vše, co musíte udělat, je kliknout na tlačítko Řešit a najít produktový mix maximalizující zisk!

Začněte kliknutím na kartu Data a ve skupině Analýza klikněte na Řešitel.

Zobrazí se dialogové okno Parametry řešitele, jak je znázorněno na obrázku 27-2.

Klikněte na pole Nastavit cílovou buňku a vyberte naši buňku zisku (buňku D12). Klikněte na pole Změnou buněk a pak ukažte na oblast D2:I2, která obsahuje libry vyrobené z každého léku. Dialogové okno by nyní mělo vypadat na obrázku 27-3.

Teď jsme připravení přidat do modelu omezení. Klikněte na tlačítko Přidat. Zobrazí se dialogové okno Přidat omezení, znázorněné na obrázku 27-4.

Pokud chcete přidat omezení používání zdrojů, klikněte na pole Odkaz na buňku a vyberte oblast D14:D15. V prostředním seznamu vyberte <=. Klikněte na pole Omezující podmínka a vyberte oblast buněk F14:F15. Dialogové okno Přidat vazbu by nyní mělo vypadat jako na obrázku 27-5.

Teď jsme zajistili, že když Řešitel zkouší různé hodnoty pro měněné buňky, budou brány v úvahu pouze kombinace, které splňují požadavky D14<=F14 (použitá práce je menší než nebo rovna dostupné práci) a D15<=F15 (použitá surovina je menší nebo rovna dostupné surovině). Kliknutím na tlačítko Přidat zadejte omezení požadavků. Vyplňte dialog Přidat vazbu, jak je znázorněno na obrázku 27-6.

Přidáním těchto omezení zajistíte, že když bude Řešitel zkoušet různé kombinace pro změny hodnot buněk, budou brány v úvahu pouze kombinace, které splňují následující parametry:

• D2<=D8 (množství vyrobené léčiva 1 je menší nebo rovno poptávce po léčivu 1)
• E2<=E8 (množství vyrobeného léčiva 2 je menší nebo rovno poptávce po léčivu 2)
• F2<=F8 (vyrobené množství vyrobeného léčiva 3 je menší nebo rovno poptávce po léčivu 3)
• G2<=G8 (vyrobené množství vyrobeného léčiva 4 je menší nebo rovno poptávce po léčivu 4)
• H2<=H8 (vyrobené množství vyrobeného léčiva 5 je menší nebo rovno poptávce po léčivu 5)
• I2<=I8 (vyrobené množství vyrobeného léčiva 6 je menší nebo rovno poptávce po léčivu 6)

V dialogovém okně Přidat vazbu klikněte na tlačítko OK. Okno Řešitele by mělo vypadat jako na obrázku 27-7.

Do dialogového okna Možnosti Řešitele zadáme omezení, že měněné buňky musí být nezáporné. V dialogovém okně Parametry Řešitele klikněte na tlačítko Možnosti. Zaškrtněte políčka Předpokládat lineární model a Převzít zápor, jak je znázorněno na obrázku 27-8 na další stránce. Klikněte na OK.

Zaškrtnutím políčka Předpokládat nezáporný zajistíte, že Řešitel bude brát v úvahu pouze kombinace měněných buněk, u kterých každá měněná buňka předpokládá nezápornou hodnotu. Zaškrtli jsme políčko Předpokládat lineární model, protože problém produktového mixu je speciálním typem problému Řešitele, kterému se říká lineární model. Model Řešitele je v podstatě lineární za následujících podmínek:

• Cílová buňka se vypočítá sečtením členů tvaru (měněná buňka)*(konstanta).
• Každá vazba splňuje "požadavek lineárního modelu". To znamená, že každé omezení je vyhodnoceno sečtením členů formuláře (měněná buňka)*(konstanta) a porovnáním součtů s konstantou.

Proč je tento problém lineární? Naše cílová buňka (zisk) se vypočítá jako

(Droga 1 zisk na libru)*(Droga 1 libra vyrobena) +
(Droga 2 zisk na libru) * (Droga 2 libry vyrobena) + ...
(Droga 6 zisk na libru)*(Droga 6 vyrobených liber)

Tento výpočet se řídí vzorem, ve kterém je hodnota cílové buňky odvozena sečtením členů tvaru (měnící se buňka)*(konstanta).

Naše pracovní omezení je hodnoceno porovnáním hodnoty odvozené z (práce spotřebovaná na libru léku 1)*(Lék 1 libra) + (Práce použitá na libru léku 2)*(Lék 2 libry)+ ... (Práce spotřebovaná na libru léku 6)*(Vyrobený lék 6 liber) k dostupné práci.

Proto se pracovní omezení vyhodnotí sečtením členů formuláře (měněná buňka)*(konstanta) a porovnáním součtů s konstantou. Omezení práce i omezení surovin splňují požadavek lineárního modelu.

Naše poptávková omezení mají podobu

(Léčivo 1 vyrobeno)<=(Lék 1 Poptávka)
(Léčivo 2 vyrobeno)<=(Poptávka po léku 2)
§
(Léčivo 6 vyrobeno)<=(Lék 6 Poptávka)

Každé požadované omezení také splňuje požadavek lineárního modelu, protože každé z nich je vyhodnoceno sečtením členů tvaru (měněná buňka)*(konstanta) a porovnáním součtů s konstantou.

Když jsme ukázali, že náš model produktového mixu je lineárním modelem, proč by nás to mělo zajímat?

• Pokud je model Řešitele lineární a zvolíme možnost Předpokládat lineární model, je zaručeno, že Řešitel najde optimální řešení modelu. Pokud model Řešitele není lineární, Řešitel může, ale nemusí najít optimální řešení.
• Pokud je model Řešitele lineární a zvolíme možnost Předpokládat lineární model, Řešitel použije k nalezení optimálního řešení modelu velmi efektivní algoritmus (simplexovou metodu). Pokud je model Řešitele lineární a nevybereme možnost Předpokládat lineární model, Řešitel používá velmi neefektivní algoritmus (metodu GRG2) a může mít potíže s nalezením optimálního řešení modelu.

Po kliknutí na tlačítko OK v dialogovém okně Možnosti řešitele se vrátíme do hlavního dialogového okna Řešitel, které je znázorněno dříve na obrázku 27-7. Po kliknutí na tlačítko Solve Řešitel vypočítá optimální řešení (pokud nějaké existuje) pro náš model produktového mixu. Jak jsem uvedl v kapitole 26, optimálním řešením modelu produktového mixu by byl soubor měnících se hodnot buněk (liber vyrobených z každého léku), který maximalizuje zisk oproti souboru všech proveditelných řešení. Opět platí, že proveditelným řešením je sada hodnot měnících se buněk splňujících všechna omezení. Hodnoty měněných buněk znázorněné na obrázku 27-9 jsou proveditelným řešením, protože všechny úrovně produkce jsou nezáporné, úrovně výroby nepřekračují poptávku a využití zdrojů nepřekračuje dostupné zdroje.

Změny hodnot buněk znázorněné na obrázku 27-10 na další stránce představují neproveditelné řešení z následujících důvodů:

• Vyrábíme více léku 5, než je poptávka po něm.
• Spotřebováváme více práce, než kolik je k dispozici.
• Používáme více surovin, než kolik je k dispozici.

Po kliknutí na Vyřešit Řešitel rychle najde optimální řešení znázorněné na obrázku 27-11. Pokud chcete na listu zachovat optimální hodnoty řešení, musíte vybrat Uchovat řešení Řešitele.

Naše farmaceutická společnost může maximalizovat svůj měsíční zisk na úrovni 6 625,20 USD tím, že vyrobí 596,67 liber léku 4, 1084 liber léku 5 a žádný z dalších léků! Nemůžeme určit, zda můžeme dosáhnout maximálního zisku 6 625,20 USD jinými způsoby. Jediné, čím si můžeme být jisti, je, že s našimi omezenými zdroji a poptávkou neexistuje způsob, jak tento měsíc vydělat více než 6 627,20 $.

Má model Řešitele vždy řešení?

Předpokládejme, že musí být uspokojena poptávka po jednotlivých produktech. (Viz list Žádné možné řešení v Prodmix.xlsx.) Poté musíme změnit naše omezení poptávky z D2:I2<=D8:I8 na D2:I2>=D8:I8. Uděláte to tak, že otevřete Řešitele, vyberete omezení D2:I2<=D8:I8 a kliknete na Změnit. Zobrazí se dialogové okno Změnit vazbu, znázorněné na obrázku 27-12.

Vyberte >= a pak klikněte na OK. Teď jsme zajistili, že Řešitel bude zvažovat změnu pouze hodnot buněk, které splňují všechny požadavky. Po kliknutí na Vyřešit se zobrazí zpráva Řešitel nenalezl možné řešení. Tato zpráva neznamená, že jsme udělali chybu v našem modelu, ale spíše, že s našimi omezenými zdroji nemůžeme uspokojit poptávku po všech produktech. Řešitel nám jednoduše říká, že pokud chceme uspokojit poptávku po každém produktu, musíme přidat více práce, více surovin nebo více obojího.

Co znamená, když model Řešitele vrátí výsledek Nastavené hodnoty nekonvergují?

Uvidíme, co se stane, když umožníme neomezenou poptávku po každém produktu a dovolíme, aby se každé drogy vyrábělo negativní množství. (Tento problém Řešitele můžete vidět na listu Nastavené hodnoty nekonvergují v Prodmix.xlsx souboru.) Chcete-li najít optimální řešení pro tuto situaci, otevřete Řešitele, klikněte na tlačítko Možnosti a zrušte zaškrtnutí políčka Předpokládat nezáporné. V dialogovém okně Parametry Řešitele vyberte požadované omezení D2:I2<=D8:I8 a kliknutím na tlačítko Odstranit je odstraňte. Když kliknete na Vyřešit, Řešitel vrátí zprávu "Nastavené hodnoty buněk nekonvergují". Tato zpráva znamená, že pokud má být cílová buňka maximalizována (jako v našem příkladu), existují proveditelná řešení s libovolně velkými hodnotami cílových buněk. (Pokud má být cílová buňka minimalizována, zpráva "Nastavit hodnoty buněk nekonvergují" znamená, že existují proveditelná řešení s libovolně nízkými hodnotami cílových buněk.) V naší situaci tím, že umožníme negativní výrobu drogy, ve skutečnosti "vytváříme" zdroje, které mohou být použity k výrobě libovolně velkého množství jiných drog. Vzhledem k naší neomezené poptávce nám to umožňuje dosahovat neomezených zisků. V reálné situaci nemůžeme vydělat nekonečné množství peněz. Stručně řečeno, pokud vidíte "Nastavené hodnoty nekonvergují", váš model obsahuje chybu.

Problémy

1. Předpokládejme, že naše farmaceutická společnost může koupit až 500 hodin práce za 1 dolar za hodinu více, než jsou současné mzdové náklady. Jak můžeme maximalizovat zisk?

2. V závodě na výrobu třísek vyrábějí čtyři technici (A, B, C a D) tři produkty (produkty 1, 2 a 3). Tento měsíc může výrobce čipů prodat 80 jednotek produktu 1, 50 jednotek produktu 2 a maximálně 50 jednotek produktu 3. Technik A může vyrábět pouze produkty 1 a 3. Technik B může vyrábět pouze produkty 1 a 2. Technik C může vyrábět pouze produkt 3. Technik D může vyrábět pouze Produkt 2. Za každou vyrobenou jednotku přispívají produkty následujícím ziskem: Produkt 1, 6 USD; Produkt 2, 7 $; a Produkt 3, 10 USD. Čas (v hodinách), který každý technik potřebuje k výrobě produktu, je následující:

   Produkt	Technik A	Technik B	Technik C	Technik D
   1	2	2,5	Nelze provést	Nelze provést
   2	Nelze provést	3	Nelze provést	3,5
   3	3	Nelze provést	4	Nelze provést

3. Každý technik může pracovat až 120 hodin měsíčně. Jak může výrobce čipů maximalizovat svůj měsíční zisk? Předpokládejme, že je možné vyrobit zlomkový počet jednotek.

4. Závod na výrobu počítačů vyrábí myši, klávesnice a pákové ovladače pro videohry. Zisk na jednotku, využití práce na jednotku, měsíční poptávka a využití strojového času na jednotku jsou uvedeny v následující tabulce:

   	Myši	Klávesnice	Pákové ovladače
   Zisk/jednotka	$8	11 Kč	9 dolarů
   Využití práce/jednotka	0,2 hodiny	0,3 hodiny	24 hodin
   Strojní čas/jednotka	0,04 hodiny	0,055 hod.	0,04 hodiny
   Měsíční poptávka	15 000	27,000	11,000

5. Každý měsíc je k dispozici celkem 13 000 pracovních hodin a 3000 hodin strojního času. Jak může výrobce maximalizovat svůj měsíční příspěvek k zisku z rostliny?

6. Vyřešte náš příklad léku za předpokladu, že musí být splněna minimální poptávka 200 jednotek pro každý lék.

7. Jason vyrábí diamantové náramky, náhrdelníky a náušnice. Chce odpracovat maximálně 160 hodin měsíčně. Má 800 uncí diamantů. Zisk, pracovní doba a unce diamantů potřebné k výrobě každého produktu jsou uvedeny níže. Pokud je poptávka po každém produktu neomezená, jak může Jason maximalizovat svůj zisk?

   Produkt	Jednotkový zisk	Počet pracovních hodin na jednotku	Unce diamantů na jednotku
   Náramek	300 Kč	.35	1,2
   Náhrdelník	2 000 Kč	.15	.75
   Náušnice	100 Kč	0,05	.5