Formulácia problému
Predpokladajme, že spoločnosť, v ktorej pracujete, má tri sklady, odkiaľ ide tovar do piatich vašich obchodov roztrúsených po celej Moskve.
Každý obchod je schopný predať určité množstvo nám známeho tovaru. Každý zo skladov má obmedzenú kapacitu. Úlohou je racionálne vybrať, z ktorého skladu do akých predajní tovar dodať, aby sa minimalizovali celkové náklady na dopravu.
Pred začatím optimalizácie bude potrebné zostaviť jednoduchú tabuľku na hárku Excel – náš matematický model popisujúci situáciu:
Rozumie sa, že:
- Svetložltá tabuľka (C4:G6) popisuje náklady na prepravu jednej položky z každého skladu do každého obchodu.
- Fialové bunky (C15:G14) popisujú množstvo tovaru potrebného na predaj v každom obchode.
- Červené bunky (J10:J13) zobrazujú kapacitu každého skladu – maximálne množstvo tovaru, ktoré sklad pojme.
- Žlté (C13:G13) a modré (H10:H13) bunky predstavujú súčty riadkov a stĺpcov pre zelené bunky.
- Celkové náklady na dopravu (J18) sa vypočítajú ako súčet produktov počtu tovaru a im zodpovedajúcich nákladov na dopravu – pre výpočet slúži funkcia tu SUMPRODUCT (SUMA PRODUKT).
Naša úloha sa teda obmedzuje na výber optimálnych hodnôt zelených buniek. A to tak, aby celková suma za riadok (modré bunky) neprekročila kapacitu skladu (červené bunky) a zároveň každá predajňa dostala také množstvo tovaru, ktoré potrebuje predať (suma za každú predajňu v žlté bunky by mali byť čo najbližšie k požiadavkám – fialové bunky).
Riešenie
V matematike sa takéto problémy výberu optimálneho rozdelenia zdrojov formulovali a popisovali už dávno. A, samozrejme, spôsoby ich riešenia sa už dávno nevyvíjajú tupým vymenovaním (ktoré je veľmi dlhé), ale vo veľmi malom počte opakovaní. Excel poskytuje používateľovi takúto funkčnosť pomocou doplnku. Vyhľadávacie riešenia (riešiteľ) z karty dátum (Dátum):
Ak na karte dátum váš Excel takýto príkaz nemá – to je v poriadku – to znamená, že doplnok ešte nie je pripojený. Ak ju chcete aktivovať, otvorte rezeňA potom vyberte parametre - Add-ons - O nás (Možnosti — Doplnky — Prejsť na). V okne, ktoré sa otvorí, začiarknite políčko vedľa riadku, ktorý potrebujeme Vyhľadávacie riešenia (riešiteľ).
Spustíme doplnok:
V tomto okne musíte nastaviť nasledujúce parametre:
- Optimalizovať cieľovú funkciu (Nastavte tpeniaze bunka) – tu je potrebné uviesť konečný hlavný cieľ našej optimalizácie, teda ružový rámček s celkovou cenou dopravy (J18). Cieľovú bunku je možné minimalizovať (ak ide o výdavky, ako v našom prípade), maximalizovať (ak ide napr. o zisk) alebo sa ju pokúsiť priblížiť na danú hodnotu (napr. presne zapadnúť do prideleného rozpočtu).
- Zmena premenných buniek (By meniace sa bunky) – tu označujeme zelené bunky (C10: G12), ktorých obmenou hodnôt chceme dosiahnuť náš výsledok – minimálne náklady na doručenie.
- V súlade s obmedzeniami (Vaša FIRMA na the,en Obmedzenia) – zoznam obmedzení, ktoré treba brať do úvahy pri optimalizácii. Ak chcete do zoznamu pridať obmedzenia, kliknite na tlačidlo pridať (Pridať) a v zobrazenom okne zadajte podmienku. V našom prípade to bude obmedzenie dopytu:
a limit na maximálny objem skladov:
Okrem zjavných obmedzení spojených s fyzikálnymi faktormi (kapacita skladov a dopravných prostriedkov, rozpočtová a časová tieseň a pod.) je niekedy potrebné pridať obmedzenia „špeciálne pre Excel“. Excel vám teda napríklad ľahko zariadi „optimalizáciu“ nákladov na doručenie tým, že ponúkne odvoz tovaru z predajní späť do skladu – náklady sa stanú zápornými, čiže budeme v zisku! 🙂
Aby ste tomu zabránili, je najlepšie ponechať začiarkavacie políčko zapnuté. Urobte z neobmedzených premenných nezáporné alebo dokonca niekedy takéto momenty výslovne zapísať do zoznamu obmedzení.
Po nastavení všetkých potrebných parametrov by okno malo vyzerať takto:
V rozbaľovacom zozname Vyberte metódu riešenia musíte navyše vybrať vhodnú matematickú metódu riešenia a vybrať si z troch možností:
- Simplexná metóda je jednoduchá a rýchla metóda na riešenie lineárnych úloh, teda úloh, kde výstup je lineárne závislý od vstupu.
- Všeobecná downgradovaná metóda prechodu (OGG) – pri nelineárnych problémoch, kde existujú zložité nelineárne závislosti medzi vstupnými a výstupnými údajmi (napríklad závislosť tržieb od nákladov na reklamu).
- Evolučné hľadanie riešenia – relatívne nová optimalizačná metóda založená na princípoch biologickej evolúcie (ahoj Darwin). Táto metóda funguje mnohonásobne dlhšie ako prvé dve, ale dokáže vyriešiť takmer každý problém (nelineárny, diskrétny).
Naša úloha je jasne lineárna: dodaný 1 kus – vynaložené 40 rubľov, doručené 2 kusy – vynaložené 80 rubľov. atď., takže simplexná metóda je najlepšou voľbou.
Teraz, keď sú zadané údaje pre výpočet, stlačte tlačidlo nájsť riešenie (vyriešiť)na spustenie optimalizácie. V závažných prípadoch s množstvom meniacich sa buniek a obmedzení môže hľadanie riešenia trvať dlho (najmä pri evolučnej metóde), ale naša úloha pre Excel nebude problém – za pár okamihov dostaneme nasledujúce výsledky :
Venujte pozornosť tomu, ako zaujímavo boli rozložené objemy dodávok medzi predajne, pričom neprekročili kapacitu našich skladov a uspokojili všetky požiadavky na požadovaný počet tovaru pre každú predajňu.
Ak nám nájdené riešenie vyhovuje, môžeme ho uložiť, alebo sa vrátiť k pôvodným hodnotám a skúsiť to znova s inými parametrami. Vybratú kombináciu parametrov môžete uložiť aj ako Scenár. Na žiadosť používateľa môže Excel zostaviť tri typy Správy o riešenom probléme na samostatných listoch: správa o výsledkoch, správa o matematickej stabilite riešenia a správa o limitoch (obmedzeniach) riešenia, vo väčšine prípadov však zaujímajú len špecialistov .
Existujú však situácie, keď Excel nedokáže nájsť vhodné riešenie. Takýto prípad je možné nasimulovať, ak v našom príklade uvedieme požiadavky predajní v množstve väčšom ako je celková kapacita skladov. Potom sa pri vykonávaní optimalizácie Excel pokúsi priblížiť k riešeniu čo najbližšie a potom zobrazí správu, že riešenie nemožno nájsť. Napriek tomu aj v tomto prípade máme množstvo užitočných informácií – najmä vidíme „slabé odkazy“ našich obchodných procesov a chápeme oblasti, v ktorých je potrebné zlepšiť.
Uvažovaný príklad je, samozrejme, relatívne jednoduchý, ale dá sa ľahko upraviť na riešenie oveľa zložitejších problémov. Napríklad:
- Optimalizácia rozdelenia finančných zdrojov podľa výdavkovej položky v podnikateľskom pláne alebo rozpočte projektu. Obmedzeniami v tomto prípade bude výška financovania a načasovanie projektu a cieľom optimalizácie je maximalizácia zisku a minimalizácia nákladov na projekt.
- Optimalizácia plánovania zamestnancov s cieľom minimalizovať mzdový fond podniku. Obmedzenia v tomto prípade budú želaním každého zamestnanca podľa harmonogramu zamestnania a požiadaviek personálnej tabuľky.
- Optimalizácia investičných investícií – potreba správne rozdeliť finančné prostriedky medzi viaceré banky, cenné papiere alebo akcie podnikov s cieľom opäť maximalizovať zisk alebo (ak je to dôležitejšie) minimalizovať riziká.
V každom prípade doplnok Vyhľadávacie riešenia (riešiteľ) je veľmi výkonný a krásny nástroj Excel a zaslúži si vašu pozornosť, pretože môže pomôcť v mnohých zložitých situáciách, ktorým musíte v modernom podnikaní čeliť.