Formulace problému
Předpokládejme, že firma, kde pracujete, má tři sklady, odkud jde zboží do pěti vašich obchodů rozesetých po celé Moskvě.
Každý obchod je schopen prodat určité množství nám známého zboží. Každý ze skladů má omezenou kapacitu. Úkolem je racionálně vybrat, ze kterého skladu do kterých prodejen zboží dodat, aby se minimalizovaly celkové náklady na dopravu.
Před zahájením optimalizace bude nutné sestavit jednoduchou tabulku na listu Excel – náš matematický model popisující situaci:
Rozumí se, že:
- Světle žlutá tabulka (C4:G6) popisuje náklady na dopravu jedné položky z každého skladu do každého obchodu.
- Fialové buňky (C15:G14) popisují množství zboží, které má každý obchod prodat.
- Červené buňky (J10:J13) zobrazují kapacitu každého skladu – maximální množství zboží, které sklad pojme.
- Žluté (C13:G13) a modré (H10:H13) buňky představují řádkové a sloupcové součty pro zelené buňky.
- Celková cena dopravy (J18) se vypočítá jako součet součinů počtu zboží a jim odpovídajících nákladů na dopravu – pro výpočet slouží funkce zde SUMPRODUCT (SUMPRODUKT).
Náš úkol je tedy redukován na výběr optimálních hodnot zelených buněk. A to tak, aby celková částka za řádek (modré buňky) nepřesáhla kapacitu skladu (červené buňky) a zároveň každá prodejna dostala takové množství zboží, které potřebuje prodat (částka za každou prodejnu v žluté buňky by měly být co nejblíže požadavkům – fialové buňky).
Řešení
V matematice jsou takové problémy volby optimálního rozdělení zdrojů formulovány a popisovány již dlouho. A samozřejmě způsoby, jak je řešit, se už dávno nevyvíjejí tupým výčtem (který je velmi dlouhý), ale ve velmi malém počtu iterací. Excel poskytuje uživateli takovou funkcionalitu pomocí doplňku. Hledání řešení (řešitel) ze záložky Data (Datum):
Pokud na kartě Data váš Excel takový příkaz nemá – to je v pořádku – to znamená, že doplněk ještě není připojen. Chcete-li jej aktivovat, otevřete Soubor, Poté vyberte parametry - Doplňky - O Nás (Možnosti — Doplňky — Přejít na). V okně, které se otevře, zaškrtněte políčko vedle řádku, který potřebujeme Hledání řešení (řešitel).
Spustíme doplněk:
V tomto okně musíte nastavit následující parametry:
- Optimalizovat cílovou funkci (Nastavte tpeníze buňka) – zde je nutné uvést konečný hlavní cíl naší optimalizace, tedy růžové pole s celkovými náklady na dopravu (J18). Cílovou buňku lze minimalizovat (pokud se jedná o výdaje, jako v našem případě), maximalizovat (pokud jde např. o zisk) nebo se ji pokusit přivést na danou hodnotu (např. přesně zapadnout do přiděleného rozpočtu).
- Změna proměnných buněk (By měnící se buňky) – zde označujeme zelené buňky (C10: G12), jejichž obměnou hodnot chceme dosáhnout našeho výsledku – minimálních nákladů na doručení.
- V souladu s omezeními (Předmět na ο omezení) – seznam omezení, která je třeba vzít v úvahu při optimalizaci. Chcete-li přidat omezení do seznamu, klikněte na tlačítko přidat (Přidat) a do zobrazeného okna zadejte podmínku. V našem případě to bude omezení poptávky:
a limit na maximální objem skladů:
Kromě zřejmých omezení spojených s fyzickými faktory (kapacita skladů a dopravních prostředků, rozpočtová a časová omezení atd.) je někdy nutné přidat omezení „speciální pro Excel“. Excel vám tedy například snadno zařídí „optimalizaci“ nákladů na doručení nabídkou odvozu zboží z prodejen zpět do skladu – náklady se dostanou do záporu, tedy budeme v zisku! 🙂
Abyste tomu zabránili, je nejlepší ponechat zaškrtávací políčko zaškrtnuté. Udělejte z neomezených proměnných nezáporné nebo dokonce někdy takové momenty výslovně zapsat do seznamu omezení.
Po nastavení všech potřebných parametrů by okno mělo vypadat takto:
V rozevíracím seznamu Vyberte metodu řešení musíte navíc vybrat vhodnou matematickou metodu řešení a vybrat si ze tří možností:
- Simplexní metoda je jednoduchá a rychlá metoda pro řešení lineárních úloh, tedy úloh, kde výstup je lineárně závislý na vstupu.
- Obecná metoda downgradovaného gradientu (OGG) – pro nelineární problémy, kde existují složité nelineární závislosti mezi vstupními a výstupními daty (například závislost tržeb na reklamních nákladech).
- Evoluční hledání řešení – relativně nová optimalizační metoda založená na principech biologické evoluce (ahoj Darwine). Tato metoda funguje mnohonásobně déle než první dvě, ale dokáže vyřešit téměř jakýkoli problém (nelineární, diskrétní).
Náš úkol je jasně lineární: dodán 1 kus – utraceno 40 rublů, dodáno 2 kusy – utraceno 80 rublů. atd., takže simplexní metoda je nejlepší volbou.
Nyní, když jsou zadány údaje pro výpočet, stiskněte tlačítko Najít řešení (Řešit)pro zahájení optimalizace. V závažných případech s mnoha měnícími se buňkami a omezeními může nalezení řešení trvat dlouho (zejména s evoluční metodou), ale náš úkol pro Excel nebude problém – za pár okamžiků dostaneme následující výsledky :
Věnujte pozornost tomu, jak zajímavě byly rozloženy objemy dodávek mezi prodejny, přičemž nepřekračovaly kapacitu našich skladů a uspokojily všechny požadavky na požadovaný počet zboží pro každou prodejnu.
Pokud nám nalezené řešení vyhovuje, můžeme jej uložit, nebo se vrátit k původním hodnotám a zkusit to znovu s jinými parametry. Vybranou kombinaci parametrů můžete také uložit jako Scénář. Na žádost uživatele může Excel sestavit tři typy zprávy k řešenému problému na samostatných listech: zpráva o výsledcích, zpráva o matematické stabilitě řešení a zpráva o limitech (omezeních) řešení, ve většině případů však zajímají pouze specialisty .
Existují však situace, kdy Excel nemůže najít vhodné řešení. Takový případ je možné simulovat, pokud v našem příkladu uvedeme požadavky prodejen v množství větším, než je celková kapacita skladů. Poté se při provádění optimalizace Excel pokusí přiblížit řešení co nejblíže a poté zobrazí zprávu, že řešení nelze nalézt. Přesto i v tomto případě máme mnoho užitečných informací – především vidíme „slabá místa“ našich obchodních procesů a chápeme oblasti, které je třeba zlepšit.
Uvažovaný příklad je samozřejmě relativně jednoduchý, ale lze jej snadno škálovat pro řešení mnohem složitějších problémů. Například:
- Optimalizace distribuce finančních zdrojů podle výdajové položky v podnikatelském plánu nebo rozpočtu projektu. Omezením v tomto případě bude výše financování a načasování projektu a cílem optimalizace je maximalizace zisku a minimalizace nákladů na projekt.
- Optimalizace plánování zaměstnanců s cílem minimalizovat mzdový fond podniku. Omezení v tomto případě bude na přání každého zaměstnance podle pracovního plánu a požadavků personálního stolu.
- Optimalizace investičních investic – potřeba správně rozdělovat finanční prostředky mezi několik bank, cenné papíry nebo akcie podniků s cílem opět maximalizovat zisk nebo (je-li důležitější) minimalizovat rizika.
V každém případě doplněk Hledání řešení (řešitel) je velmi výkonný a krásný nástroj Excel a zaslouží si vaši pozornost, protože může pomoci v mnoha obtížných situacích, kterým musíte v moderním podnikání čelit.