Jak může firma pomocí Řešitele určit, které projekty by měla realizovat?
Společnost jako Eli Lilly musí každý rok určit, které léky vyvinout; společnost jako Microsoft, které softwarové programy vyvíjet; společnost jako Proctor & Gamble, které vyvíjejí nové spotřebitelské produkty. Funkce Řešitel v Excelu může společnosti s rozhodováním pomoct.
Jak může firma pomocí Řešitele určit, které projekty by měla realizovat?
Většina korporací chce realizovat projekty, které přispívají největší čistou současnou hodnotou (NPV), a to v závislosti na omezených zdrojích (obvykle kapitálu a pracovní síle). Řekněme, že společnost zabývající se vývojem softwaru se snaží určit, který z 20 softwarových projektů by měla provést. NPV (v milionech dolarů), kterým každý projekt přispěl, stejně jako kapitál (v milionech dolarů) a počet programátorů potřebných během každého z příštích tří let je uveden na listu základního modelu v souboru Capbudget.xlsx, který je znázorněn na obrázku 30-1 na další stránce. Například projekt 2 vynáší 908 milionů Kč. Vyžaduje 151 milionů dolarů během roku 1, 269 milionů dolarů během roku 2 a 248 milionů dolarů během roku 3. Projekt 2 vyžaduje 139 programátorů během 1. ročníku, 86 programátorů během 2. ročníku a 83 programátorů během 3. ročníku. Buňky E4:G4 zobrazují kapitál (v milionech dolarů), který je k dispozici během každého ze tří let, a buňky H4:J4 udávají, kolik programátorů je k dispozici. Například během roku 1 je k dispozici kapitál až 2,5 miliardy dolarů a 900 programátorů.
Společnost se musí rozhodnout, zda má každý projekt realizovat. Předpokládejme, že nemůžeme provést zlomek softwarového projektu; Pokud bychom například vyčlenili 0,5 potřebných zdrojů, měli bychom nefunkční program, který by nám přinesl 0 dolarů!
Trik při modelování situací, ve kterých něco uděláte nebo neuděláte, spočívá v použití buněk s binárními změnami. Binární měněná buňka se vždy rovná 0 nebo 1. Pokud binární měněná buňka, která odpovídá projektu, se rovná 1, uděláme projekt. Pokud binárně měněná buňka odpovídající projektu odpovídá 0, projekt se neuskuteční. Řešitele nastavíte tak, aby používal oblast binárně měněných buněk přidáním omezující podmínky – vyberte měněné buňky, které chcete použít, a pak ze seznamu v dialogovém okně Přidat omezení zvolte Třída.
S tímto pozadím jsme připraveni vyřešit problém výběru softwarového projektu. Jako vždy u modelů v Řešiteli začneme identifikací cílové buňky, měněných buněk a omezení.
- Cílová buňka. Maximalizujeme NPV generovanou vybranými projekty.
- Měněné buňky. Pro každý projekt hledáme binární měněnou buňku 0 nebo 1. Tyto buňky jsem umístil do oblasti A6:A25 (a pojmenoval jsem oblast doit). Například hodnota 1 v buňce A6 označuje, že provádíme Projekt 1; Hodnota 0 v buňce C6 označuje, že se nerealizujeme Projektem 1.
- Omezení. Musíme zajistit, aby pro každý rok t (t=1, 2, 3) byl použitý kapitál v roce t menší nebo roven dostupnému kapitálu v roce t a rok t použité práce byl menší nebo roven roku t dostupné práce.
Jak vidíte, náš list musí pro jakýkoli výběr projektů počítat s čistá, roční kapitálem a každoročně používanými programátory. V buňce B2 použiji vzorec SOUČIN.SKALÁRNÍ(doit;NPV) k výpočtu celkové funkce ČISTÁ.SOUČHODNOTA, kterou vygenerují vybrané projekty. (Název oblasti ČISTÁ.SOUČHODNOTA odkazuje na oblast C6:C25.) Pro každý projekt, který má ve sloupci A hodnotu 1, tento vzorec vypočítá čistou současnou hodnotu projektu. Pro každý projekt s hodnotou 0 ve sloupci A vzorec nevypočítá funkci ČISTÁ.SOUČHODNOTA projektu. Proto jsme schopni vypočítat funkci ČISTÁ.SOUČHODNOTA všech projektů a naše cílová buňka je lineární, protože se počítá sečtením členů, které následují za tvarem (měnící se buňka)*(konstanta). Podobným způsobem vypočítám kapitál použitý každý rok a práci použitou každý rok tak, že zkopíruji z E2 do F2:J2 vzorec SOUČIN.SKALÁRNÍ(doit;E6:E25).
Nyní vyplním dialogové okno Parametry řešitele, jak je znázorněno na obrázku 30-2.
Naším cílem je maximalizovat NPV vybraných projektů (buňka B2). Naše měněné buňky (oblast s názvem doit) jsou binární měněné buňky pro každý projekt. Omezení E2:J2<=E4:J4 zajišťuje, že během každého roku bude použitý kapitál a práce menší nebo rovna dostupnému kapitálu a práci. Pokud chci přidat omezení, které způsobí, že měněné buňky budou binární, kliknu v dialogovém okně Parametry Řešitele na tlačítko Přidat a uprostřed dialogového okna vyberu ze seznamu položku Třída. Dialogové okno Přidat vazbu by se mělo zobrazit, jak je znázorněno na obrázku 30-3.
Náš model je lineární, protože cílová buňka se počítá jako součet termínů, které mají tvar (měnící se buňka)*(konstanta) a protože omezení využití prostředků se počítají porovnáním součtu (měněných buněk)*(konstant) s konstantou.
Když je dialogové okno Parametry Řešitele vyplněné, klikněte na Řešit a máme výsledky zobrazené dříve na obrázku 30-1. Společnost může získat maximální čistou současnou současnou hodnotu ve výši 9 293 milionů USD (9,293 miliardy USD) výběrem projektů 2, 3, 6–10, 14–16, 19 a 20.
Zacházení s jinými omezeními
Modely výběru projektů mají někdy jiná omezení. Předpokládejme například, že pokud vybereme Projekt 3, musíme vybrat také Projekt 4. Protože naše současné optimální řešení vybírá Projekt 3, ale ne Projekt 4, víme, že naše současné řešení nemůže zůstat optimální. Tento problém vyřešíte jednoduše přidáním podmínky, že měněná buňka v Projektu 3 je menší nebo rovna měněné buňce v Projektu 4.
Tento příklad najdete na listu If 3 then 4 v Capbudget.xlsx souborů, který je znázorněn na obrázku 30-4. Buňka L9 odkazuje na binární hodnotu související s Projektem 3 a buňka L12 na binární hodnotu související s Projektem 4. Když přidáte omezení L9<=L12, pokud zvolíme Projekt 3, L9 se rovná 1 a naše omezení vynutí, aby se L12 (binární soubor Projekt 4) rovnal 1. Naše omezení musí také ponechat binární hodnotu v měněné buňce Projektu 4 neomezenou, pokud nevybereme Projekt 3. Pokud nevybereme Projekt 3, L9 se rovná 0 a naše omezení umožňuje, aby se binární číslo Projektu 4 rovnalo 0 nebo 1, což je to, co chceme. Nové optimální řešení je znázorněno na obrázku 30-4.
Nové optimální řešení je vypočítáno, pokud výběr Projektu 3 znamená, že musíme také vybrat Projekt 4. Nyní předpokládejme, že z Projektů 1 až 10 můžeme udělat pouze čtyři projekty. (Viz list Nanejvýš 4 z P1–P10, znázorněný na obrázku 30-5.) V buňce L8 vypočítáme součet binárních hodnot přidružených k projektům 1 až 10 pomocí vzorce SUMA(A6:A15). Poté přidáme omezení L8<=L10, které zajistí, že budou vybrány maximálně 4 z prvních 10 projektů. Nové optimální řešení je znázorněno na obrázku 30-5. NPV klesla na 9,014 miliardy dolarů.
Řešení úloh binárního a celočíselného programování
Lineární modely řešiče, ve kterých musí být některé nebo všechny měněné buňky binární nebo celočíselné, se obvykle řeší obtížněji než lineární modely, ve kterých mohou být všechny měnící se buňky zlomky. Z tohoto důvodu se často spokojíme s téměř optimálním řešením problému binárního nebo celočíselného programování. Pokud je model Řešitele spuštěný dlouhou dobu, zvažte úpravu nastavení Tolerance v dialogovém okně Možnosti Řešitele. (Viz obrázek 30-6.) Například nastavení tolerance 0,5 % znamená, že Řešitel poprvé najde přijatelné řešení, které je v rozmezí 0,5 procenta od teoretické optimální hodnoty cílové buňky (teoretická optimální hodnota cílové buňky je optimální cílová hodnota zjištěná při vynechání binárních a celočíselných omezení). Často stojíme před volbou mezi nalezením odpovědi do 10 procent optima za 10 minut nebo nalezením optimálního řešení za dva týdny času u počítače! Výchozí hodnota tolerance je 0,05 %, což znamená, že Řešitel se zastaví, když najde cílovou buňku s hodnotou menší než 0,05 procenta teoretické optimální cílové buňky.
Problémy
- Společnost zvažuje devět projektů. Čistá současná hodnota přidaná každým projektem a kapitál požadovaný každým projektem během příštích dvou let jsou uvedeny v následující tabulce. (Všechna čísla jsou v milionech.) Projekt 1 například přidá 14 milionů Kč v čisté současné hodnotě a bude vyžadovat výdaje ve výši 12 milionů Kč během roku 1 a 3 miliony USD během roku 2. Během roku 1 je k dispozici kapitál 50 milionů USD pro projekty a 20 milionů USD je k dispozici během roku 2.
| NPV | Výdaje za 1. rok | Výdaje za rok 2 | |
|---|---|---|---|
| Projekt 1 | 14 | 12 | 3 |
| Projekt 2 | 17 | 54 | 7 |
| Projekt 3 | 17 | 6 | 6 |
| Projekt 4 | 15 | 6 | 2 |
| Projekt 5 | 40 | 30 | 35 |
| Projekt 6 | 12 | 6 | 6 |
| Projekt 7 | 14 | 48 | 4 |
| Projekt 8 | 10 | 36 | 3 |
| Projekt 9 | 12 | 18 | 3 |
- Pokud nemůžeme provést zlomek projektu, ale musíme provést buď celý nebo žádný projekt, jak můžeme maximalizovat ČISTÁ.SOUČHODNOTA?
- Předpokládejme, že pokud je proveden Projekt 4, musí být proveden Projekt 5. Jak můžeme maximalizovat funkci ČISTÁ.SOUČHODNOTA?
Vydavatelská společnost se snaží určit, kterou z 36 knih by měla letos vydat. SouborPressdata.xlsx poskytuje následující informace o každé knize:
- Předpokládané výnosy a náklady na vývoj (v tisících dolarů)
- Počet stránek v každé knize
- Zda je kniha určena pro vývojáře softwaru (označeno číslem 1 ve sloupci E)
Vydavatelská společnost může letos vydat knihy v celkovém rozsahu až 8500 stran a musí vydat alespoň čtyři knihy zaměřené na vývojáře softwaru. Jak může společnost maximalizovat svůj zisk?
O článku
Tento článek byl převzat z knihy systém Microsoft Office Excel 2007 Data Analysis and Business Modeling od Wayna L. Winstona.
Tato učební kniha vznikla na základě série prezentací Wayna Winstona, známého statistika a profesora obchodu, který se specializuje na kreativní a praktické aplikace Excelu.