Excel Solver: Čo to je a kedy by ste ho mali používať

Tabuľkové nástroje Excelu, ako je hľadanie cieľov, ktoré menia jednu premennú, sú užitočné, ale väčšina problémov v podnikaní bohužiaľ nie je taká jednoduchá. Zvyčajne budete čeliť vzorcom s najmenej dvoma a niekedy aj desiatkami premenných. Problém bude mať často viac ako jedno riešenie a vašou výzvou bude použitie Excelu na nájdenie optimálneho riešenia (t. j. takého, ktoré maximalizuje zisk alebo minimalizuje náklady alebo zodpovedá iným kritériám). Na tieto väčšie výzvy potrebujete svalnatejší nástroj. Excel má len odpoveď: Riešiteľ. Excel Solver je sofistikovaný optimalizačný program, ktorý vám umožňuje nájsť riešenia zložitých problémov, ktoré by si inak vyžadovali matematickú analýzu na vysokej úrovni.

Pochopenie Excel Solver

Riešiteľ, podobne ako hľadanie cieľov , používa na vykonávanie svojich výpočtov iteračnú metódu. Použitie iterácie znamená, že Excel Solver skúša riešenie, analyzuje výsledky, skúša iné riešenie atď. Táto cyklická iterácia však nie je len dohadom zo strany Riešiteľa. To by bolo hlúpe. Nie, Excel Solver skúma, ako sa výsledky menia s každou novou iteráciou, a pomocou niektorých sofistikovaných matematických procesov (ktoré, našťastie, prebiehajú v pozadí a možno ich ignorovať), zvyčajne dokáže povedať, akým smerom by sa mal uberať k riešeniu.

Výhody Excel Solver

Áno, Goal Seek a Solver sú iteratívne, ale to neznamená, že sú rovnaké. V skutočnosti Excel Solver prináša do tabuľky množstvo výhod:

• Excel Solver umožňuje zadať viacero nastaviteľných buniek. Celkovo môžete použiť až 200 nastaviteľných buniek.
• Excel Solver vám umožňuje nastaviť obmedzenia pre nastaviteľné bunky. Riešiteľovi môžete napríklad povedať, aby našiel riešenie, ktoré nielen maximalizuje zisk, ale spĺňa aj určité podmienky, ako je dosiahnutie hrubej marže medzi 20 a 30 percentami alebo udržanie výdavkov pod 100 000 USD. O týchto podmienkach sa hovorí, že sú obmedzeniami riešenia.
• Excel Solver nehľadá len požadovaný výsledok ("cieľ" v Goal Seek), ale aj ten optimálny. Napríklad hľadanie optimálneho výsledku môže znamenať, že môžete nájsť maximálne alebo minimálne možné riešenie.
• Pre zložité problémy môže Riešiteľ vygenerovať viacero riešení . Tieto rôzne riešenia potom môžete uložiť v rôznych scenároch.

Kedy by ste mali použiť Excel Solver?

Dobre, poďme rovno: Excel Solver je výkonný nástroj, ktorý väčšina používateľov Excelu nepotrebuje. Bolo by prehnané napríklad používať Solver na výpočet čistého zisku vzhľadom na fixné príjmy a náklady. Mnohé problémy si však nevyžadujú nič menej ako prístup Riešiteľa. Tieto problémy pokrývajú mnoho rôznych oblastí a situácií, ale všetky majú spoločné nasledujúce charakteristiky:

• Majú jednu cieľovú bunku (nazývanú aj cieľová bunka ), ktorá obsahuje vzorec, ktorý chcete maximalizovať, minimalizovať alebo nastaviť na konkrétnu hodnotu. Tento vzorec môže byť výpočtom, ako sú celkové náklady na dopravu alebo čistý zisk.
• Vzorec objektívnej bunky obsahuje odkazy na jednu alebo viacero premenných buniek (nazývaných aj neznáme alebo meniace sa bunky ). Riešiteľ upraví tieto bunky, aby našiel optimálne riešenie pre objektívny vzorec bunky. Tieto variabilné bunky môžu zahŕňať položky, ako sú predané jednotky, náklady na dopravu alebo výdavky na reklamu.
• Voliteľne existuje jedna alebo viac buniek obmedzenia, ktoré musia spĺňať určité kritériá. Môžete napríklad požadovať, aby reklama predstavovala menej ako 10 percent celkových nákladov alebo aby zľava pre zákazníkov bola v rozsahu 40 až 60 percent.

Na obrázku nižšie je napríklad znázornený dátový model pracovného hárka, ktorý je celý nastavený pre Excel Solver. Model zobrazuje výnosy (cena krát predané jednotky) a náklady pre dva produkty, zisk vytvorený každým produktom a celkový zisk. Otázka, na ktorú treba odpovedať, je táto: Koľko jednotiek každého produktu sa musí predať, aby ste získali celkový zisk 0 USD? Toto je v podnikaní známe ako analýza rovnováhy.

Cieľom tohto dátového modelu je nájsť bod zlomu (kde je celkový zisk 0 USD).

Znie to ako jednoduchá úloha hľadania cieľa, ale tento model má zložitý aspekt: ​​variabilné náklady. Za normálnych okolností sú variabilné náklady produktu jeho jednotkové náklady krát počet predaných jednotiek. Ak výroba produktu A stojí 10 USD a predáte 10 000 jednotiek, variabilné náklady na tento produkt sú 100 000 USD. V reálnom svete sú však takéto náklady často zmiešané medzi viacerými produktmi. Ak napríklad prevádzkujete spoločnú reklamnú kampaň na dva produkty, tieto náklady znášajú oba produkty. Preto tento model predpokladá, že náklady na jeden produkt súvisia s predanými jednotkami druhého produktu. Tu je napríklad vzorec, ktorý sa používa na výpočet nákladov na nafukovacie terče (bunka B8):

=B7 * B4 – C4

Inými slovami, variabilné náklady na nafukovacie terče sú znížené o jeden dolár za každú predanú jednotku leštičky na psy. Variabilné náklady posledne menovaného používajú podobný vzorec (v bunke C8):

=C7 * C4 – B4

Variabilné náklady súvisiace s viacerými produktmi stavia tento dátový model mimo rámec toho, čo dokáže Hľadanie cieľa, ale Riešiteľ je na túto výzvu pripravený. Tu sú špeciálne bunky v modeli, ktoré Solver použije:

• Objektívna bunka je C14; celkový zisk a cieľové riešenie pre tento vzorec je 0 (to znamená bod zvratu).
• Výmenné bunky sú B4 a C4, ktoré obsahujú počet predaných jednotiek pre každý produkt.
• Pre obmedzenia môžete pridať, že obe bunky zisku produktu (B12 a C12) by mali byť tiež 0.

Načítava sa doplnok Excel Solver

Add-in je softvér, ktorý pridáva jeden alebo viac funkcií Excelu. Inštaláciou doplnkov získate ďalšie funkcie Excelu, ktoré v predvolenom nastavení nie sú dostupné na páse s nástrojmi. Pribalený doplnkový softvér je súčasťou Excelu, ale nenainštaluje sa automaticky pri inštalácii Excelu . Niekoľko doplnkov sa štandardne dodáva s Excelom, vrátane Riešiteľa, ktorý vám umožňuje riešiť problémy s optimalizáciou.

Pribalené doplnky nainštalujete pomocou dialógového okna Možnosti programu Excel; nájdete ich v sekcii Doplnky. Po ich nainštalovaní sú doplnky ihneď k dispozícii. Zvyčajne sa zobrazujú na karte súvisiacej s ich funkciou. Napríklad Riešiteľ sa zobrazí na karte Údaje.

Ak chcete načítať doplnok Excel Solver, postupujte takto:

Vyberte Súbor → Možnosti.
Zobrazí sa dialógové okno Možnosti programu Excel.

Vyberte položku Doplnky.

V zozname Spravovať vyberte Doplnky Excelu a potom vyberte Prejsť.
Excel zobrazí dialógové okno Doplnky.

Začiarknite políčko Doplnok Riešiteľ

Kliknite na tlačidlo OK.
Excel pridá tlačidlo Riešiteľ do skupiny Analyzovať na karte Údaje.

Pozrite sa a zistite, ako používať Excel Solver .