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

Poznámka: Radi by sme vám čo najrýchlejšie poskytovali najaktuálnejší obsah Pomocníka vo vašom vlastnom jazyku. Táto stránka bola preložená automaticky a môže obsahovať gramatické chyby alebo nepresnosti. Naším cieľom je, aby bol tento obsah pre vás užitočný. Dali by ste nám v dolnej časti tejto stránky vedieť, či boli pre vás tieto informácie užitočné? Tu nájdete anglický článok pre jednoduchú referenciu.

V tomto článku sa popisuje používanie doplnku Riešiteľ, doplnku programu Microsoft Excel, ktorý môžete použiť na analýzu hypotéz, 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.

Book image

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

Poznámka:  Ako je vysvetlené v kapitole 26, "Úvodné informácie o optimalizácii pomocou doplnku Excel Riešiteľ," Riešiteľ je nainštalovaný kliknutím na tlačidlo Microsoft Office, potom na položku Možnosti programu Excel a následne na položku Doplnky. V zozname Spravovať kliknite na položku Doplnky programu Excel, začiarknite políčko doplnok Riešiteľ a potom kliknite na tlačidlo OK.

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

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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

Book image

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.

Predpokladajme, že dopyt po jednotlivých produktoch musí byť splnený. (Pozrite si hárok bez možnéHo riešenia v súbore Prodmix. xlsx.) Potom musíme zmeniť obmedzenia dopytu z D2: I2< = D8: i8D2: I2> = D8: i8. Ak to chcete urobiť, otvorte Riešiteľ, vyberte položku D2: I2< = D8: i8 a potom kliknite na položku zmeniť. Zobrazí sa dialógové okno Zmena obmedzenia zobrazené na obrázku 27-12.

Book image

Vyberte položku > = a potom kliknite na tlačidlo OK. Teraz sme zaistili, že Riešiteľ zváži meniace sa len hodnoty buniek, ktoré spĺňajú všetky požiadavky. Po kliknutí na položku vyriešiť sa zobrazí hlásenie Riešiteľ sa nepodarilo nájsť možné riešenie. Toto hlásenie neznamená, že sme urobili chybu v našom modeli, ale naopak, že s obmedzenými zdrojmi nemôžeme uspokojiť dopyt po všetkých produktoch. Riešiteľ nám jednoducho oznámi, že ak chceme uspokojiť dopyt po jednotlivých produktoch, musíme pridať viac práce, viac surovín alebo viac.

Pozrime sa, čo sa stane, ak povolíme neobmedzený dopyt po jednotlivých produktoch a dovolíme, aby sa vyprodukovali negatívne množstvá každej drogy. (Tento problém s riešiteľom sa zobrazí v časti nastaviť hodnoty nepribližujú hárok v súbore Prodmix. xlsx.) Ak chcete nájsť optimálne riešenie tejto situácie, otvorte Riešiteľ, kliknite na tlačidlo Možnosti a zrušte začiarknutie políčka predpokladať, že nie je záporné. V dialógovom okne Parametre doplnku Riešiteľ vyberte obmedzenie dopytu D2: I2< = D8: i8 a potom kliknutím na položku Odstrániť odstráňte obmedzenie. Po kliknutí na položku riešiť sa v Riešiteľovi vráti hlásenie "Nastavenie hodnôt buniek nie je konvergentné". Toto hlásenie znamená, že ak má byť cieľová bunka maximalizovaná (ako v našom príklade), existujú uskutočniteľné riešenia s ľubovoľne veľkými hodnotami cieľovej bunky. (Ak sa má cieľová bunka minimalizovať, hlásenie "Nastavenie hodnôt buniek nie je konvergentné" znamená, že sú možné riešenia s ľubovoľne malými hodnotami cieľovej bunky.) V našej situácii tým, že povolíte negatívnu výrobu drogy, sme v skutočnosti "vytvoriť" zdroje, ktoré možno použiť na výrobu svojvoľne veľkého množstva iných drog. Vďaka nášmu neobmedzenému dopytu nám to umožňuje neobmedzený zisk. V skutočnej situácii nemôžeme urobiť nekonečné množstvo peňazí. V skratke, ak sa zobrazí hlásenie nastaviť hodnoty sa nePribližujú, model má chybu.

  1. Predpokladajme, že naša Drogová spoločnosť si môže zakúpiť až 500 hodín práce na $1 viac za hodinu než súčasné mzdové náklady. Ako môžeme maximalizovať zisk?

  2. Štyria technici (a, B, C a D) vyrábajú na čipe výrobný závod tri produkty (produkty 1, 2 a 3). Tento mesiac môže výrobca čipu predávať 80 jednotiek produktov 1, 50 jednotiek produktu 2 a najviac 50 jednotiek produktu 3. Technik A môže robiť len produkty 1 a 3. Technik B môže vykonávať len produkty 1 a 2. Technik C môže urobiť len produkt 3. Technik D môže urobiť len produkt 2. Pre každú vyrábanú jednotku produkty prispejú nasledujúcim ziskom: produkt 1, $6; Produkt 2, $7; a produkt 3, $10. Čas (v hodinách), ktorý každý technik potrebuje na výrobu produktu, je nasledujúci:

    Produkt

    Technik A

    Technik B

    Technik C

    Technik D

    1

    2

    2,5

    Nie je možné vykonať

    Nie je možné vykonať

    2

    Nie je možné vykonať

    3

    Nie je možné vykonať

    3,5

    3

    3

    Nie je možné vykonať

    4

    Nie je možné vykonať

  3. Každý technik môže pracovať až do 120 hodín mesačne. Ako môže výrobca čipu maximalizovať svoj mesačný zisk? Predpokladajme, že je možné vytvoriť frakčný počet jednotiek.

  4. Počítačová výrobná továreň vyrába myši, klávesnice a joysticky pre videohry. V tejto tabuľke je uvedený zisk za jednotku, použitie za jednotku práce, mesačný dopyt a spotreba za jednotku času.

    Myši

    Klávesnice

    Joysticky

    Zisk/jednotka

    $8

    $11

    $9

    Používanie práce/jednotka

    .2 hodina

    .3 hodina

    .24 hodín

    Časový úsek počítača/jednotka

    .04 hour

    .055 hour

    .04 hour

    Mesačný dopyt

    15 000

    27 000

    11 000

  5. Každý mesiac je k dispozícii celkový počet pracovných hodín 13 000 a 3000 hodín času stroja. Ako môže výrobca maximalizovať svoj mesačný zisk zo závodu?

  6. Vyriešiť náš príklad drogy za predpokladu, že musí byť splnená minimálna požiadavka 200 jednotiek pre každý liek.

  7. Jason robí diamantové náramky, náhrdelníky a náušnice. Chce pracovať maximálne 160 hodín mesačne. Má 800 uncí diamantov. Nižšie je uvedený zisk, pracovná doba a unca diamantov potrebných na výrobu jednotlivých produktov. Ak je dopyt po jednotlivých produktoch neobmedzený, ako môže Jason maximalizovať svoj zisk?

    Produkt

    Jednotka Profit

    Pracovné hodiny na jednotku

    Unca diamantov na jednotku

    Náramok

    300 EUR

    .35

    1,2

    Náhrdelník

    200 EUR

    .15

    .75

    Náušnice

    100 EUR

    0,05

    0,5

Rozšírte svoje zručnosti práce s balíkom Office
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×