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

Poznámka: Snažíme se pro vás co nejrychleji zajistit aktuální obsah nápovědy ve vašem jazyce. Tato stránka byla přeložena automaticky a může obsahovat gramatické chyby nebo nepřesnosti. Naším cílem je to, aby pro vás byl její obsah užitečný. Mohli byste nám prosím dát ve spodní části této stránky vědět, jestli vám informace v článku pomohly? Pokud byste se rádi podívali na jeho anglickou verzi, najdete ji tady.

Tento článek popisuje použití doplňku Řešitel, což je doplněk aplikace Microsoft Excel, který můžete použít pro citlivostní analýzu 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.

Book image

Ř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.

Poznámka:  Jak je vysvětleno v kapitole 26, "předvolba optimalizace s řešitelem aplikace Excel" Řešitel je nainstalován kliknutím na tlačítko Microsoft Office a potom na možnosti aplikace Excel a pak na doplňky. V seznamu Spravovat klikněte na doplňky Excelu, zaškrtněte políčko doplňku Řešitel a klikněte na OK.

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

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Předpokládejme, že musí být splněny požadavky na jednotlivé produkty. (V souboru Prodmix. xlsx se nejedná o neProveditelné řešení Je třeba změnit naše omezení požadavků z D2: I2< = D8: i8 to D2: I2> = D8: i8. Otevřete Řešitel, vyberte omezení D2: I2< = D8: i8 a klikněte na změnit. Zobrazí se dialogové okno změnit omezení zobrazené na obrázku 27-12.

Book image

Vyberte > = a pak klikněte na OK. Teď jsme zajistili, že Řešitel bude počítat jenom hodnoty buněk, které splňují všechny požadavky. Když kliknete na vyřešit, zobrazí se zpráva Řešitel nenašel vhodné řešení. Tato zpráva neznamená, že jsme v našem modelu udělali chybu, ale přesto u nich nemůžeme splnit poptávku pro všechny produkty. Řešitel vám jednoduše oznámí, že pokud chceme vyhovět poptávce za každý produkt, potřebujeme přidat další práci, další suroviny nebo další.

Podívejme se, co se stane, když pro každý produkt povolíme neomezené poptávky a zakazujeme pro každou léků negativní množství. (Tento problém Řešitele najdete v listu nastavit hodnoty nekonvergovat v souboru Prodmix. xlsx.) Pokud chcete najít optimální řešení této situace, otevřete Řešitel, klikněte na tlačítko Možnosti a zrušte zaškrtnutí políčka neZáporné. V dialogovém okně Parametry Řešitele vyberte omezení požadavku D2: I2< = D8: i8 a kliknutím na odstranit omezení odeberte. Když kliknete na tlačítko vyřešit, vrátí Řešitel zprávu "nastavení hodnot buněk neKonverguje." Tato zpráva znamená, že pokud se má cílová buňka maximalizovat (jako v našem příkladu), existují možná řešení s libovolně velkými hodnotami cílových buněk. (Pokud má být cílová buňka minimalizovaná, zpráva "nastavení hodnot buněk neKonverguje" znamená, že existují možná řešení s libovolně malými hodnotami cílových buněk.) V naší situaci tím, že umožníte negativní produkci drog, jsme v platnosti "vytvářením" zdrojů, které se dají použít pro libovolně velké množství jiných drog. Díky naší neomezené poptávce to nám umožňuje provádět neomezené zisky. V reálných situacích nemůžeme neprovádět žádné peníze. Pokud se zobrazí zpráva "nastavené hodnoty neKonvergují", má váš model chybu.

  1. Předpokládejme, že naše farmaceutická společnost může zakoupit až 500 hodin práce na $1 více za hodinu než aktuální pracovní náklady. Jak můžeme maximalizovat zisk?

  2. Ve spalovacím závodě tvoří čtyři technici (A, B, C a D) tři produkty (produkty 1, 2 a 3). Tento měsíc může výrobce čipu prodávat 80 jednotek produktu 1, 50 jednotek produktu 2 a maximálně 50 jednotek produktu 3. Technika A může dělat jenom produkty 1 a 3. Technika B může provádět pouze produkty 1 a 2. Technika C může dělat jenom produkt 3. Technika D může dělat jenom produkt 2. Pro každou vyrobenou jednotku produkty přispívají následujícím ziskem: produkt 1, $6; Produkt 2, $7; a produktem 3, $10. Čas (v hodinách), který potřebuje každý technik vyrábět produkt, je následující:

    Produkt

    Technika A

    Technika B

    Technika C

    Technika D

    1

    2

    2,5

    Nejde

    Nejde

    2

    Nejde

    3

    Nejde

    3,5

    3

    3

    Nejde

    4

    Nejde

  3. Každý technik může pracovat až s 120 hodin měsíčně. Jak může výrobce čipu maximalizovat měsíční zisk? Předpokládat, že je možné vyrobit zlomek počtu jednotek.

  4. Počítačová továrna vyrábí ovladače myši, klávesnice a herní zařízení pro videohry. Náklady na jednotlivé jednotky, využití práce za jednotku, měsíční poptávku a využití počítače za jednotku jsou uvedené v následující tabulce:

    Myš

    Poskytuje

    Pákové ovladače

    Zisk/kus

    $8

    $11

    $9

    Spotřeba práce/jednotka

    .2 hodiny

    .3 hodiny

    .24 hodin

    Čas počítače/jednotka

    .04 hodina

    .055 hodina

    .04 hodina

    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 počítače. Jak může výrobce maximalizovat měsíční příspěvek ze závodu?

  6. Vyřešení našeho příkladu léků za předpokladu, že musí být splněny minimální požadavky na 200 pro každou drogu.

  7. Jason vytvoří Diamond Bracelets, necklaces a Earrings. Chce pracovat maximálně 160 hodin měsíčně. Má 800 unce. Zisk, pracovní doba a unce diamantů potřebných k výrobě každého produktu jsou uvedeny níže. Pokud je poptávka za každý produkt neomezená, může Jason maximalizovat zisk?

    Produkt

    Zisk za kus

    Počet pracovních hodin za kus

    Unce na jednotku

    Bracelet

    300 Kč

    .35

    1,2

    Necklace

    2 000 Kč

    .15

    .75

    Earrings

    100 Kč

    0,05

    0,5

Rozšiřte své dovednosti s Office
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor. Vypadá to, že bude užitečné, když vás spojíme s některým z našich agentů z podpory Office.

×