Použití řešitele k určení optimálního sortimentu produktů

Jak můžu určit měsíční skladbu produktů, která maximalizuje ziskovost?

Společnosti často potřebují stanovit měsíční množství jednotlivých produktů. Ve své nejjednodušší podobě se může stát, že se ve skladbě produktů zobrazí informace o tom, jak určit množství jednotlivých produktů, které by se měly vyrobit během měsíce za účelem maximalizace zisku. Produktová skladba musí obvykle dodržovat následující omezení:

• Produktová skladba nemůže využívat další zdroje, než je k dispozici.

• Pro každý produkt existuje omezená poptávka. V průběhu jednoho měsíce nemůžete vyrábět větší množství produktu, protože nadbytečná produkce je nevyužitá (například bezdůvodné léků).

Teď vyřešíme následující příklad problému se skladbou produktů. Řešení tohoto problému najdete v souboru Prodmix. xlsx, který je znázorněn na obrázku 27-1.

Řekněme, že pracujeme na drogové společnosti, která vyrábí šest různých produktů ve svém závodě. Výroba každého produktu vyžaduje práci a surovinu. Řádek 4 na obrázku 27-1 ukazuje hodiny práce potřebné k vytvoření křížku jednotlivých produktů a řádek 5 ukazuje libry materiálu potřebného k dosažení křížku každého produktu. Například vytvořením libry produktu 1 se vyžaduje šest hodin práce a 3,2 libra surovin. Pro každou drogu je cena za libru uvedená v řádku 6, jednotkové náklady na křížce jsou uváděny v řadě 7 a v řádku 9 je uvedený příspěvek na křížek. Například výrobek 2 prodává za $11,00 za libru, pořizovací cena za jednotku $5,70 za libru a přispívá $5,30 zisk za libru. Poptávka po jednotlivých drogách je uvedená v řadě 8. Třeba poptávka za produkt 3 je 1041 Libra. Tento měsíc, 4500 hodin práce a 1600 Libra surovin. Jak může tato společnost maximalizovat měsíční zisk?

Pokud jsme nevěděli, že Řešitel Excelu nic nedělá, můžeme tento problém vyřešit vytvořením listu za účelem sledování zisku a využití zdrojů spojených s produktem mix. Pak jsme použili zkušební verzi a chybu, která se hodí ke změně sortimentu produktů pro optimalizaci zisku bez použití více práce nebo materiálu, než je k dispozici, a bez vyprodukování léků, která překračují poptávka. Řešitel v tomto procesu používáme jenom na fázi zkušebního stavu a chyby. Řešitel je v podstatě optimalizačním jádrem, který flawlessly provádí zkušební a chybu.

Klíčem k řešení problému se skladbou produktů je efektivní výpočet používání zdrojů a zisku spojeného s případným produktem mix. Důležitým nástrojem, který můžeme použít k provedení tohoto výpočtu, je funkce skalární. Funkce skalární vynásobí odpovídající hodnoty v oblasti buněk a vrátí součet těchto hodnot. Každá oblast buněk použitá v skalární vyhodnocení musí mít stejné rozměry, což znamená, že můžete použít skalární se dvěma řádky nebo dvěma sloupci, ale ne s jedním sloupcem a jedním řádkem.

Jako příklad toho, jak můžeme použít funkci skalární v našem příkladu mix produktů, se snažíme vypočítat využití prostředků. Naše využití práce je počítáno společností

(Práce použitá na kříž od 1) * (práce 1 libry vyráběné) +
(práce použitá za libru 2) * (v drogách 2 vyráběné libry) +...
(Práce použitá za libru drog 6) * (vyráběná léků 6)

Společnost bychom mohli vypočítat práci pomocí práce efektivněji, jako je D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * i4. Podobně může být použití surovin počítané jako D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * i5. Zadávání těchto vzorců do listu na šest produktů je však časově náročné. PředStavte si, jak dlouho by mělo trvat, když spolupracujete se společností, kterou vyrobila, například produkty 50 ve svém závodě. Mnohem jednodušší způsob výpočtu spotřeby práce a surovin je zkopírovat z D14 na D15 vzorec skalární ($D $2: $I $2, D4: i4). Vzorec vypočte hodnotu D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * i4 (což je naše využití práce), ale mnohem snadněji ho zadáte! Všimněte si, že používám znak $ s oblastí D2: I2, takže když Kopíruji vzorec, který stále sbírá produkt z řádku 2. Vzorec v buňce D15 vypočítá spotřebu surovin.

Podobným způsobem závisí naše zisky

(Zisk drog 1 – libra) * (2 vyráběná léky 1) +
(zisk drog 2 – libra) * (2 vyrobené léky) +...
(Zisk drog 6 – libra) * (léky 6 vyrobených léků)

Zisk je v buňce D12 snadno vypočítáván pomocí vzorce skalární (D9: i9, $D $2: $I $2).

Teď můžeme zjistit tři součásti našeho modelu Řešitele mix produktů.

• Cílová buňkaNaším cílem je maximalizovat zisk (vypočtený v buňce D12).

• Měněné buňkyPočet Libra vyrobených z každého výrobku (uvedený v oblasti buněk D2: i2)

• Podmínkami. Máme 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é prostředky).

  • Nevyrábět více drog, než je poptávka. To znamená, že hodnoty v buňkách D2: I2 (v librách všech léků) musí být menší nebo rovny poptávce za každé léky (uvedené v buňkách D8: i8).

  • Nemůžeme vyrábět zápornou částku žádné léky.

Ukážem, jak zadat cílovou buňku, měnit buňky a omezení na Řešitel. Potom stačí kliknout na tlačítko řešení a najít ziskové směsi produktů s maximalizací zisku!

Chcete-li začít, klikněte na kartu data a ve skupině Analýza klikněte na příkaz Řešitel.

Zobrazí se dialogové okno Parametry Řešitele, jak ukazuje obrázek 27-2.

Klikněte na pole nastavit cílovou buňku a pak vyberte naši buňku zisk (D12 buňky). Klikněte na pole změnit buňky a nastavte ukazatel myši na oblast D2: I2, která obsahuje libry z každé léky. Mělo by se zobrazit obrázek 27-3.

Teď můžete do modelu přidat omezení. Klikněte na tlačítko Přidat. Zobrazí se dialogové okno Přidat omezení, které se zobrazuje na obrázku 27-4.

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

Teď jsme zajistili, že když Řešitel zkusí pro měněnou buňku použít jiné hodnoty, jsou dostupné jenom kombinace, které splňují obě D14< = F14 (pracovní práce je menší než nebo je rovno práci), a D15< = F15 (použitý surovina je menší než nebo rovno bude zvažována surovina, která je k dispozici). Kliknutím na Přidat zadejte omezení požadavků. Vyplňte dialogové okno Přidat omezení podle obrázku 27-6.

Přidáním těchto omezení zajistíte, že když Řešitel zkusí různé kombinace pro změny hodnot buněk, budou zváženy jenom kombinace, které splňují následující parametry:

• D2< = D8 (množství vyprodukovaných drog 1 je menší než nebo rovno poptávce 1)

• E2< = E8 (množství vyprodukovaných drog 2 je menší nebo rovno poptávce v drogách 2)

• F2< = F8 (vyrobené množství 3 drog je menší než nebo rovno poptávce 3)

• G2< = G8 (množství vyprodukovaných ve výši 4 drog je menší než nebo rovno poptávce 4)

• H2< = h8 (vyrobené množství 5 drog je menší nebo rovno poptávce v drogách 5)

• I2< = i8 (vydaná částka 6 drog je menší nebo rovna poptávce v drogách 6)

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

V dialogovém okně Možnosti Řešitele zadejte omezení, které 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íčko převzít lineární model a neZáporné pole neZáporné, jak je znázorněno na obrázku 27-8 na další stránce. Klikněte na OK.

Když zaŠkrtnete políčko neZáporné, může se v Řešiteli vycházet pouze z kombinací měněných buněk, ve 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 se skladbou produktů je speciální typ problému s řešitelem nazývaný lineární model. V podstatě je model Řešitele lineární za následujících podmínek:

• Cílová buňka je vypočítaná sečtením podmínek formuláře (Měněná buňka) * (konstanta).

• Každé omezení splňuje požadavky "lineární model". To znamená, že každé omezení je vyhodnoceno sečtením podmínek formuláře (Měněná buňka) * (Constant) a porovnáním součtů s konstantou.

Proč je tento Řešitel vyřešen lineárně? Naše cílová buňka (zisk) se vypočítává jako

(Zisk drog 1 – libra) * (2 vyráběná léky 1) +
(zisk drog 2 – libra) * (2 vyrobené léky) +...
(Zisk drog 6 – libra) * (léky 6 vyrobených léků)

Tento výpočet se řídí vzorcem, ve kterém je hodnota cílové buňky odvozená přidáním podmínek formuláře (Měněná buňka) * (konstanta).

Naše omezení práce je vyhodnoceno porovnáním hodnoty odvozené od (práce využité na křížCe drog 1) * (léky 1 libry vyráběné) + (práce použitá za libru 2) * (v drogách 2 vyráběné libry) +... (USed po librÁch léků 6) * (v lékůch 6 pěstovaných v librách) je k dispozici práce.

Proto je omezení práce vyhodnoceno sečtením podmínek formuláře (Měněná buňka) * (Constant) a porovnáním součtů s konstantou. Omezení práce a omezení materiálu splňují požadavek na lineární model.

Naše omezení požadavků mají formu

(Vyrobené léky 1) < = (poptávka 1 pro léky)
(léky 2 vyrobená) < = (léků 2 poptávka)
§
(léků 6 vyráběná) < = (poptávka 6 pro léky)

Každé omezení požadavku také splňuje lineární požadavek modelu, protože každý je vyhodnocen přidáním podmínek formuláře (Měněná buňka) * (Constant) a porovnáním součtů s konstantou.

S tím, že model míchání produktů je lineární model, proč bychom měli dělat?

• Pokud je model Řešitele lineární a Vy vyberete Lineární model, bude v Řešiteli zaručeno, že je optimální řešení pro model Řešitele. Pokud model Řešitele není lineární, řešitel může nebo nemusí najít optimální řešení.

• Pokud je model Řešitele lineární a Vy vyberete Lineární model, řešitel používá velmi efektivní algoritmus (metodu simplex) k nalezení optimálního řešení modelu. Pokud je model Řešitele lineární a nevybereme lineární model, řešitel používá velmi neefektivní algoritmus (metodu GRG2) a může mít potíže s hledáním optimálního řešení modelu.

Po kliknutí na tlačítko OK v dialogovém okně Možnosti Řešitele se vrátíte do dialogového okna hlavní řešitel, které je uvedeno výše na obrázku 27-7. Když kliknete na možnost vyřešit, řešitel vypočítá optimální řešení pro náš model míchání produktů (pokud existuje). Jak je uvedeno v kapitole 26, představuje optimální řešení pro model míchání produktů sadu měněných hodnot buněk (v librách všech léků), které maximalizují zisk ze sady všech vhodných řešení. Proveditelné řešení je zase sada měněných hodnot buněk splňujících všechna omezení. Měněné hodnoty buněk zobrazené na obrázku 27-9 jsou vhodné řešení, protože všechny úrovně výroby jsou nezáporné, výrobní úrovně nepřekračují poptávku a využití zdrojů nepřekračuje dostupné zdroje.

Měněné hodnoty buněk zobrazené na obrázku 27-10 na následující stránce představují neproveditelné řešení z následujících důvodů:

• Vyrábíme více léků 5, než je poptávka za produkt.

• Používáme více práce, než je to možné.

• Používáme více surovin, než je dostupných.

Po kliknutí na tlačítko vyřešit Řešitel rychle najde optimální řešení, které vidíte na obrázku 27-11. Chcete-li zachovat optimální hodnoty řešení v listu, vyberte možnost zachovat řešení řešitele.

Naše farmaceutická společnost může maximalizovat svůj měsíční zisk na úrovni $6 625,20 tím, že vyrábí 596,67 Libra drog 4, 1084 v drogách 5 a žádná z ostatních drog! Nemůžeme určit, zda můžeme dosáhnout maximálního zisku $6 625,20 jiným způsobem. Můžeme si být jistí, že s našimi omezenými zdroji a poptávkou nemůžete na více než $6 627,20 tento měsíc.