Použitie doplnku Riešiteľ na určenie optimálneho sortimentu produktov

Ako môžem určiť mesačný mix produktov, ktorý maximalizuje ziskovosť?

Spoločnosti často potrebujú určiť množstvo jednotlivých produktov, ktoré sa majú vyrábať na mesačnej báze. V najjednoduchšej podobe sa problém so zmiešaním produktov týka spôsobu stanovenia množstva jednotlivých produktov, ktoré sa majú vyrábať v priebehu mesiaca, aby sa maximalizovali zisky. Produktový mix musí zvyčajne dodržiavať tieto obmedzenia:

• Produkt mix nemôže používať viac zdrojov, než je k dispozícii.

• Pre každý produkt je obmedzený dopyt. Nemôžeme produkovať viac produktov v priebehu mesiaca, než je dopyt diktuje, pretože nadmerná produkcia je zbytočná (napríklad s drogami podliehajúcimi skaze).

Teraz vyriešime nasledujúci príklad problému s produktom mix. Riešenie tohto problému nájdete v súbore Prodmix. xlsx, ktorý je znázornený na obrázku 27-1.

Povedzme, že pracujeme na drogovej spoločnosti, ktorá vyrába šesť rôznych produktov v ich závode. Výroba jednotlivých produktov vyžaduje prácu a surovinu. Riadok 4 na obrázku 27-1 zobrazuje pracovnú dobu potrebnú na výrobu libry jednotlivých produktov a riadok 5 znázorňuje libry suroviny, ktoré sú potrebné na výrobu libry jednotlivých produktov. Napríklad výroba libry produktu 1 vyžaduje šesť hodín práce a 3,2 libier suroviny. Pre každý liek je uvedená cena za libru v riadku 6, jednotkové náklady na libru sú uvedené v riadku 7 a výška zisku na libru je uvedená v riadku 9. Produkt 2 sa napríklad predáva za $11,00 za libru, čím vznikne jednotkové náklady na $5,70 za libru a prispieva $5,30 zisk za libru. Dopyt mesiaca pre každý liek je uvedený v riadku 8. Dopyt po produktoch 3 je napríklad 1041 libier. Tento mesiac je k dispozícii 4500 hodín práce a 1600 kilogramov suroviny. Ako môže táto spoločnosť maximalizovať svoj mesačný zisk?

Ak by sme vedeli nič o doplnku Excel Riešiteľ, tento problém by sme napadol vytvorením hárka na sledovanie zisku a využitia zdrojov priradených k sortimentu produktov. Potom by sme použili skúšobnú a chybu na zmenu produktového mixu na optimalizáciu zisku bez použitia väčšieho množstva práce alebo suroviny, než je k dispozícii, a bez toho, aby ste museli produkovať akýkoľvek liek presahujúci dopyt. Riešiteľ používame v tomto procese len v štádiu skúšobnej verzie a chyby. Riešiteľ je v podstate vyhľadávací nástroj, ktorý dokonale vykoná vyhľadávanie skúšobnej verzie a chyby.

Kľúčom na vyriešenie problému so zmiešaním produktov je efektívne výpočet využitia zdrojov a zisku priradeného k danému sortimentu produktov. Dôležitým nástrojom, ktorý môžeme použiť na vykonanie tohto výpočtu je funkcia SUMPRODUCT. Funkcia SUMPRODUCT vynásobí zodpovedajúce hodnoty v rozsahoch buniek a vráti súčet týchto hodnôt. Každý rozsah buniek, ktorý sa používa v SUMPRODUCT hodnotení, musí mať rovnaké rozmery, čo znamená, že môžete použiť SUMPRODUCT s dvomi riadkami alebo dvoma stĺpcami, nie však s jedným stĺpcom a jedným riadkom.

Príkladom toho, ako môžeme použiť funkciu SUMPRODUCT v príklade našej zmesi produktov, sa poďme pokúsiť o výpočet využívania zdrojov. Používanie našich pracovných síl sa vypočítava podľa

(Práca použitá na libru drogy 1) * (drogy 1 kg vyrobený) +
(práca použitá na libru drogy 2) * (drogy 2 kg produkoval) +...
(Práca použitá na libru drogy 6) * (vyrobená droga 6 libier)

Dalo by sa vypočítať používanie práce v podobe nudné módy ako D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * i4. Používanie suroviny sa tiež môže vypočítať ako D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * i5. Zadávanie týchto vzorcov v hárku pre šesť produktov však je časovo náročné. PredStavte si, ako dlho bude trvať, ak by ste pracovali s firmou, ktorá vyrábala napríklad produkty 50 v ich zariadení. Oveľa jednoduchší spôsob výpočtu práce a používania suroviny je skopírovať z D14 na D15 vzorca SUMPRODUCT ($D $2: $I $2; D4: i4). Tento vzorec vypočíta D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * i4 (ktorý je naším pracovným využitím), ale je oveľa jednoduchšie zadať! Všimnite si, že používam znak $ s rozsahom D2: i2, aby sa po skopírovaní vzorca aj naďalej zaznamenával mix produktov z riadka 2. Vzorec v bunke D15 vypočíta používanie suroviny.

V podobnom duchu je náš zisk určený

(Droga 1 zisk za libru) * (drogy 1 kg vyrobený) +
(droga 2 zisk za libru) * (drogy 2 kg produkoval) +...
(Droga 6 zisk za libru) * (vyrobená droga 6 libier)

Zisk sa jednoducho vypočítava v bunke D12 so vzorcom SUMPRODUCT (D9: i9; $D $2: $I $2).

Teraz môžeme identifikovať tri súčasti modelu Riešiteľa produktu mix.

• Cieľová bunka.Naším cieľom je maximalizovať zisk (vypočítaný v bunke D12).

• Zmena buniek.Počet kilogramov vyrobených z jednotlivých produktov (uvedených v rozsahu buniek D2: I2)

• Obmedzenia. Máme tieto obmedzenia:

  • Nepoužívajte viac práce alebo suroviny, než je k dispozícii. Čiže hodnoty v bunkách D14: D15 (použité zdroje) musia byť menšie alebo rovné hodnotám v bunkách F14: F15 (dostupné zdroje).

  • Neprodukujú viac drog, než je dopyt. To znamená, že hodnoty v bunkách D2: I2 (kilogramy vyrobené z každej drogy) musia byť menšie alebo rovné dopytu po jednotlivých drogách (uvedených v bunkách D8: i8).

  • Nemôžeme produkovať záporné množstvo žiadneho lieku.

Ukážem vám, ako zadať cieľovú bunku, zmeniť bunky a obmedzenia v Riešiteľovi. Potom všetko, čo potrebujete urobiť, je kliknúť na tlačidlo riešiť a nájsť zisk – Maximalizujte produktový mix.

Ak chcete začať, kliknite na kartu údaje a v skupine Analýza kliknite na položku Riešiteľ.

Zobrazí sa dialógové okno Parametre doplnku Riešiteľ, ako je znázornené na obrázku 27-2.

Kliknite na pole nastaviť cieľovú bunku a potom vyberte položku zisková bunka (D12 buniek). Kliknite na pole zmeniť bunky a potom ukážte na rozsah D2: i2, ktorý obsahuje libry vyrobené z každej drogy. Dialógové okno by teraz malo vyzerať ako obrázok 27-3.

Teraz sme pripravení pridať do modelu obmedzenia. Kliknite na tlačidlo Pridať. Zobrazí sa dialógové okno Pridanie obmedzenia zobrazené na obrázku 27-4.

Ak chcete pridať obmedzenia využitia zdrojov, kliknite na pole odkaz na bunku a potom vyberte rozsah D14: D15. V strednom zozname vyberte položku < =. Kliknite na pole obmedzenie a potom vyberte rozsah buniek F14: F15. Dialógové okno Pridať obmedzenie by teraz malo vyzerať ako obrázok 27-5.

Teraz sme zaistili, že keď Riešiteľ vyskúša odlišné hodnoty pre meniace sa bunky, iba kombinácie, ktoré vyhovujú obom D14< = F14 (práca použitá je menšia alebo rovná pracovnej k dispozícii) a D15< = F15 (použitá surovina je menšia alebo rovná k dispozícii) sa bude uvažovať. Ak chcete zadať obmedzenia dopytu, kliknite na položku Pridať. Vyplňte dialógové okno Pridanie obmedzenia, ako je znázornené na obrázku 27-6.

Pridaním týchto obmedzení sa zabezpečí, že keď Riešiteľ vyskúša odlišné kombinácie pre meniace sa hodnoty buniek, budú sa zvažovať iba kombinácie, ktoré spĺňajú nasledujúce parametre:

• D2< = D8 (suma vyrobená z drogy 1 je menšia alebo rovná dopytu po drogách 1)

• E2< = E8 (množstvo vyrobeného lieku 2 je menšie alebo rovné dopytu po drogách 2)

• F2< = F8 (suma vyrobená z vyprodukovaného lieku 3 je menšia alebo rovná dopytu po drogách 3)

• G2< = G8 (suma vyrobená z vyprodukovaného lieku 4 je menšia alebo rovná dopytu po drogách 4)

• H2< = H8 (suma vyrobená z vykonanej drogy 5 je menšia alebo rovná dopytu po drogách 5)

• I2< = i8 (suma vyrobená z vyprodukovaného lieku 6 je menšia alebo rovná dopytu po drogách 6)

Kliknite na tlačidlo OK v dialógovom okne Pridať obmedzenie. Okno Riešiteľ by malo vyzerať ako obrázok 27-7.

Do dialógového okna Riešiteľ – možnosti zadáte obmedzenie, ktoré mení bunky. Kliknite na tlačidlo Možnosti v dialógovom okne Parametre doplnku Riešiteľ. Začiarknite políčko lineárny model a pole Predpokladajme, že nie je záporné, ako je znázornené na obrázku 27-8 na ďalšej strane. Kliknite na tlačidlo OK.

Kontrola poľa Predpokladajme, že nie je záporná, zabezpečí, že Riešiteľ považuje len kombinácie zmien buniek, v ktorých každá zmena bunky prevezme nezápornú hodnotu. Skontrolovali sme okno Predpokladajme lineárny model, pretože problém so zmiešaním produktov je špeciálny typ problému s riešiteľom, ktorý sa nazýva lineárny model. Model Riešiteľa je v podstate lineárny za týchto podmienok:

• Cieľová bunka sa vypočíta spočítaním podmienok formulára (zmena bunky) * (konštanta).

• Každé obmedzenie vyhovuje požiadavkám lineárneho modelu. To znamená, že každé obmedzenie sa vyhodnotí spočítaním podmienok formulára (zmena bunky) * (konštanta) a porovnaním súm na konštantu.

Prečo je tento problém s riešiteľom lineárny? Naša cieľová bunka (zisk) sa vypočíta ako

(Droga 1 zisk za libru) * (drogy 1 kg vyrobený) +
(droga 2 zisk za libru) * (drogy 2 kg produkoval) +...
(Droga 6 zisk za libru) * (vyrobená droga 6 libier)

Tento výpočet nasleduje po vzore, v ktorom je hodnota cieľovej bunky odvodená spočítaním výrazov formulára (zmena bunky) * (konštanta).

Naše pracovné obmedzenia sa hodnotia porovnaním hodnoty odvodenej od (práce použitej na libru drogy 1) * (vyrobenéHo lieku 1 kg) + (práca použitá na libru drogy 2) * (vyrobená droga 2 kg) +... (Práca v USAEd na libru drogy 6) * (liek 6 libier vyrobený) k dispozícii na prácu.

Preto je obmedzenie pracovných síl vyhodnotené spočítaním podmienok formulára (zmena bunky) * (konštanta) a porovnaním súm na konštantu. Obmedzenie pracovnej sily a obmedzenie suroviny vyhovujú požiadavkám lineárneho modelu.

Naše obmedzenia dopytu majú formu

(Vyprodukovaný liek 1) < = (dopyt s drogami 1)
(produkovaná droga 2) < = (dopyt s drogami 2)
§
(vyprodukovaný liek 6) < = (dopyt s drogami 6)

Každé obmedzenie dopytu spĺňa aj požiadavky lineárneho modelu, pretože každá z nich je hodnotená sčítaním podmienok formulára (zmena bunky) * (konštanta) a porovnaním súm na konštantu.

Keď sa ukázalo, že náš model Product mix je lineárny model, prečo by sme sa mali starať?

• Ak je model Riešiteľa lineárny a vyberáme lineárny model, Riešiteľ je zaručený, že nájde optimálne riešenie pre model Riešiteľ. Ak model Riešiteľa nie je lineárny, Riešiteľ môže alebo nemusí nájsť optimálne riešenie.

• Ak je model Riešiteľa lineárny a vyberáme lineárny model, Riešiteľ použije veľmi účinný algoritmus (jednostranná metóda) na nájdenie optimálneho riešenia modelu. Ak je model Riešiteľa lineárny a nie je možné vybrať položku prevziať lineárny model, Riešiteľ použije veľmi neúčinný algoritmus (GRG2 metóda) a môže mať problémy s vyhľadaním optimálneho riešenia modelu.

Po kliknutí na tlačidlo OK v dialógovom okne Možnosti doplnku Riešiteľ sa vrátite do dialógového okna hlavný riešiteľ, ktorý je zobrazený skôr na obrázku 27-7. Po kliknutí na položku riešiť sa v Riešiteľovi vypočíta optimálne riešenie (ak existuje) pre model sortimentu produktov. Ako som už uviedol v kapitole 26, optimálne riešenie modelu modelovania produktov by bolo množina zmien hodnôt buniek (libier vyrobených z každej drogy), ktorá maximalizuje zisk v rámci množiny všetkých uskutočniteľných riešení. Uskutočniteľné riešenie je opäť množina zmien hodnôt buniek, ktoré vyhovujú všetkým obmedzeniam. Meniace sa hodnoty buniek znázornené na obrázku 27-9 sú prijateľné riešenie, pretože všetky úrovne výroby nie sú negatívne, úrovne výroby nepresiahnu dopyt a používanie zdrojov nepresahuje dostupné zdroje.

Meniace sa hodnoty buniek zobrazené na obrázku 27-10 na ďalšej strane predstavujú neuskutočniteľné riešenie z nasledujúcich dôvodov:

• Vyprodukujeme viac drog 5 ako dopyt po ňom.

• Používame viac práce, než to, čo je k dispozícii.

• Používame viac surovín, než čo je k dispozícii.

Po kliknutí na položku vyriešiť Riešiteľ rýchlo nájde optimálne riešenie zobrazené na obrázku 27-11. Ak chcete zachovať optimálne hodnoty riešenia v hárku, je potrebné vybrať možnosť ponechať riešenie doplnku Riešiteľ.

Naša farmaceutická spoločnosť môže maximalizovať svoj mesačný zisk na úrovni $6 625,20 výrobou 596,67 libier drog 4, 1084 libier drog 5, a žiadny z ostatných liekov! Nemôžeme zistiť, či môžeme dosiahnuť maximálny zisk $6 625,20 inými spôsobmi. Všetko, čo môžeme si byť istí, že vďaka našim obmedzeným zdrojom a dopytu neexistuje spôsob, ako v tomto mesiaci urobiť viac než $6 627,20.