Excel Solver: Co to je a kdy byste jej měli používat

Excelové tabulkové nástroje jako Goal Seek, které mění jednu proměnnou, jsou užitečné, ale bohužel většina problémů v podnikání není tak snadná. Obvykle budete čelit vzorcům s nejméně dvěma a někdy desítkami proměnných. Problém bude mít často více než jedno řešení a vaším úkolem bude použití Excelu k nalezení optimálního řešení (tj. takového, které maximalizuje zisk nebo minimalizuje náklady nebo odpovídá jiným kritériím). Pro tyto větší výzvy potřebujete svalnatější nástroj. Excel má jen odpověď: Řešitel. Excel Solver je sofistikovaný optimalizační program, který vám umožní najít řešení složitých problémů, které by jinak vyžadovaly matematickou analýzu na vysoké úrovni.

Porozumění Excel Solveru

Solver, stejně jako Goal Seek , používá k provádění svých výpočtů iterativní metodu. Použití iterace znamená, že Excel Solver zkouší řešení, analyzuje výsledky, zkouší jiné řešení a tak dále. Tato cyklická iterace však není jen dohadem ze strany Řešitele. To by bylo hloupé. Ne, Excel Solver zkoumá, jak se výsledky mění s každou novou iterací, a pomocí některých sofistikovaných matematických procesů (které naštěstí probíhají na pozadí a lze je ignorovat) obvykle dokáže říct, jakým směrem by se měl k řešení ubírat.

Výhody Excel Solveru

Ano, Goal Seek a Solver jsou iterativní, ale to je nerovná. Ve skutečnosti Excel Solver přináší do tabulky řadu výhod:

• Excel Solver umožňuje zadat více nastavitelných buněk. Celkem můžete použít až 200 nastavitelných článků.
• Excel Solver umožňuje nastavit omezení pro nastavitelné buňky. Například můžete Solveru říct, aby našel řešení, které nejen maximalizuje zisk, ale také splní určité podmínky, jako je dosažení hrubé marže mezi 20 a 30 procenty nebo udržení nákladů pod 100 000 $. Tyto podmínky jsou považovány za omezení řešení.
• Excel Solver hledá nejen požadovaný výsledek ("cíl" v Goal Seek), ale také optimální. Například hledání optimálního výsledku může znamenat, že můžete najít řešení, které je maximum nebo minimum možné.
• U složitých problémů může Řešitel generovat více řešení . Tato různá řešení pak můžete uložit v různých scénářích.

Kdy byste měli používat Excel Solver?

Dobře, pojďme rovnou sem: Excel Solver je mocný nástroj, který většina uživatelů Excelu nepotřebuje. Bylo by přehnané například používat Solver k výpočtu čistého zisku vzhledem k fixním tržbám a nákladům. Mnoho problémů však nevyžaduje nic menšího než přístup Řešitel. Tyto problémy pokrývají mnoho různých oblastí a situací, ale všechny mají společné následující charakteristiky:

• Mají jedinou cílovou buňku (nazývanou také cílová buňka ), která obsahuje vzorec, který chcete maximalizovat, minimalizovat nebo nastavit na konkrétní hodnotu. Tento vzorec může být výpočtem, jako jsou celkové náklady na dopravu nebo čistý zisk.
• Objektivní vzorec buňky obsahuje odkazy na jednu nebo více proměnných buněk (nazývaných také neznámé nebo měnící se buňky ). Řešitel upraví tyto buňky, aby našel optimální řešení pro vzorec cílové buňky. Tyto proměnné buňky mohou zahrnovat položky, jako jsou prodané jednotky, náklady na dopravu nebo náklady na reklamu.
• Volitelně existuje jedna nebo více buněk omezení, které musí splňovat určitá kritéria. Můžete například požadovat, aby reklama činila méně než 10 procent celkových nákladů nebo aby sleva pro zákazníky byla v rozmezí 40 až 60 procent.

Obrázek níže například ukazuje datový model listu, který je celý nastaven pro Excel Solver. Model ukazuje výnosy (cena krát prodané jednotky) a náklady pro dva produkty, zisk vytvořený každým produktem a celkový zisk. Otázka, kterou je třeba zodpovědět, zní takto: Kolik jednotek každého produktu se musí prodat, abyste získali celkový zisk 0 USD? V podnikání se tomu říká analýza rovnováhy.

Cílem tohoto datového modelu je najít bod zvratu (kde je celkový zisk 0 USD).

Zní to jako přímočarý úkol Hledání cílů, ale tento model má záludný aspekt: ​​variabilní náklady. Normálně jsou variabilní náklady produktu jeho jednotkové náklady krát počet prodaných jednotek. Pokud výroba produktu A stojí 10 USD a prodáte 10 000 jednotek, variabilní náklady na tento produkt jsou 100 000 USD. V reálném světě se však tyto náklady často směšují mezi více produkty. Pokud například provozujete společnou reklamní kampaň na dva produkty, tyto náklady ponesou oba produkty. Proto tento model předpokládá, že náklady na jeden produkt souvisí s prodanými jednotkami druhého produktu. Zde je například vzorec používaný k výpočtu nákladů na nafukovací terč (buňka B8):

=B7 * B4 – C4

Jinými slovy, variabilní náklady na nafukovací terč jsou sníženy o jeden dolar za každou prodanou jednotku Dog Polisher. Variabilní náklady posledně jmenovaného používají podobný vzorec (v buňce C8):

=C7 * C4 – B4

Variabilní náklady související s více produkty staví tento datový model mimo rámec toho, co může Goal Seek udělat, ale Řešitel se s touto výzvou vyrovná. Zde jsou speciální buňky v modelu, které Řešitel použije:

• Objektivní buňka je C14; celkový zisk a cílové řešení pro tento vzorec je 0 (tj. bod zvratu).
• Měnící buňky jsou B4 a C4, které obsahují počet prodaných jednotek pro každý produkt.
• Pro omezení můžete přidat, že obě buňky zisku produktu (B12 a C12) by měly být také 0.

Načítání doplňku Řešitel aplikace Excel

Add-in je software, který přidává jeden nebo více funkcí Excelu. Instalací doplňků získáte další funkce aplikace Excel, které ve výchozím nastavení nejsou na pásu karet dostupné. Přiložený doplňkový software je součástí Excelu, ale není automaticky instalován při instalaci Excelu . S Excelem je standardně dodáváno několik doplňků, včetně Solveru, který umožňuje řešit problémy s optimalizací.

Přibalené doplňky nainstalujete pomocí dialogového okna Možnosti aplikace Excel; najdete je v sekci Doplňky. Po jejich instalaci jsou doplňky ihned k dispozici. Obvykle se objevují na kartě související s jejich funkcí. Například Řešitel se zobrazí na kartě Data.

Při načítání doplňku Řešitel Excelu postupujte podle následujících kroků:

Vyberte Soubor → Možnosti.
Zobrazí se dialogové okno Možnosti aplikace Excel.

Vyberte Doplňky.

V seznamu Spravovat vyberte Doplňky aplikace Excel a poté vyberte Přejít.
Excel zobrazí dialogové okno Doplňky.

Zaškrtněte políčko Doplněk Řešitel

Klepněte na tlačítko OK.
Excel přidá tlačítko Řešitel do skupiny Analyzovat na kartě Data.

Podívejte se a zjistěte, jak používat Excel Solver .