Použitie Riešiteľa na tvorbu kapitálového rozpočtu

Ako môže spoločnosť pomocou Riešiteľa určiť, ktoré projekty by mala realizovať?

Spoločnosť ako Eli Lilly musí každý rok určiť, ktoré lieky vyvinúť; spoločnosť ako Microsoft, ktorú softvérové programy vyvíjať; spoločnosť ako Proctor & Gamble, ktorú nové spotrebiteľské produkty vyvinúť. Funkcia Riešiteľ v Exceli môže spoločnosti pomôcť pri takýchto rozhodnutiach.

Ako môže spoločnosť pomocou Riešiteľa určiť, ktoré projekty by mala realizovať?

Väčšina spoločností chce realizovať projekty, ktoré prispievajú najväčšou čistou súčasnou hodnotou (NPV), s obmedzenými zdrojmi (zvyčajne kapitál a práca). Povedzme, že spoločnosť zaoberajúca sa vývojom softvéru sa pokúša určiť, ktorý z 20 softvérových projektov by mala realizovať. NPV (v miliónoch dolárov) prispievaný každým projektom, ako aj kapitál (v miliónoch dolárov) a počet programátorov potrebných počas každého z nasledujúcich troch rokov sú uvedené v pracovnom hárku základného modelu v súbore Capbudget.xlsx, ktorý je znázornený na obrázku 30-1 na ďalšej strane. Napríklad Project 2 prináša 908 miliónov dolárov. Vyžaduje 151 miliónov dolárov počas 1. roka, 269 miliónov dolárov počas 2. roka a 248 miliónov dolárov počas 3. roka. Projekt 2 vyžaduje 139 programátorov počas 1. ročníka, 86 programátorov počas 2. ročníka a 83 programátorov počas 3. ročníka. Bunky E4:G4 zobrazujú kapitál (v miliónoch eur) dostupný počas každého z troch rokov a bunky H4:J4 označujú počet programátorov, ktorí sú k dispozícii. Napríklad počas prvého roka je k dispozícii kapitál vo výške až 2,5 miliardy dolárov a 900 programátorov.

Spoločnosť sa musí rozhodnúť, či sa má uskutočňovať každý projekt. Predpokladajme, že nemôžeme podniknúť zlomok softvérového projektu; Ak by sme napríklad vyčlenili 0,5 potrebných zdrojov, mali by sme nefungujúci program, ktorý by nám priniesol výnos 0 $!

Trik pri modelovaní, keď niečo robíte alebo nerobíte, spočíva v použití buniek meniacich sa na základe binárnej hodnoty. Binárne meniaca sa bunka sa vždy rovná 0 alebo 1. Keď sa binárne meniaca sa bunka, ktorá zodpovedá projektu, rovná hodnote 1, vykonáme projekt. Ak sa binárna meniaca sa bunka, ktorá zodpovedá projektu, rovná 0, projekt sa nevykoná. Riešiteľ môžete nastaviť tak, aby používal rozsah buniek meniacich sa na binárne základe pridaním obmedzenia. Vyberte menené bunky, ktoré chcete použiť, a potom vyberte položku Bin v zozname v dialógovom okne Pridať obmedzenie.

Book S týmto pozadím sme pripravení vyriešiť problém výberu softvérového projektu. Ako vždy v modeli Riešiteľa, začneme identifikáciou našej cieľovej bunky, meniacich sa buniek a obmedzení.

• Cieľová bunka. Maximalizujeme NPV generované vybranými projektmi.
• Menené bunky. Pre každý projekt hľadáme meniacu sa bunku 0 alebo 1 binárnej sústavy. Tieto bunky som lokalizoval v rozsahu A6:A25 (a pomenoval som rozsah doit). Napríklad hodnota 1 v bunke A6 označuje, že realizujeme projekt 1. 0 v bunke C6 znamená, že nevykonávame projekt 1.
• Obmedzenia. Musíme zabezpečiť, aby pre každý rok t (t = 1, 2, 3) bol rok použitého kapitálu nižší alebo rovný ako rok t dostupného kapitálu a rok t použitej práce menší alebo rovný roku t dostupnej práce.

Ako vidíte, náš pracovný hárok musí pre akýkoľvek výber projektov vypočítať NPV, kapitál použitý ročne a programátorov použitých každý rok. V bunke B2 použijem vzorec SUMPRODUCT(doit;NPV) na výpočet celkovej hodnoty NPV vytvorenej vybratými projektmi. (Názov rozsahu NPV odkazuje na rozsah C6:C25.) Pre každý projekt s hodnotou 1 v stĺpci A tento vzorec preberá hodnotu NPV projektu a pre každý projekt s hodnotou 0 v stĺpci A tento vzorec nezachytáva hodnotu NPV projektu. Preto sme schopní vypočítať NPV všetkých projektov a naša cieľová bunka je lineárna, pretože sa vypočítava sčítavaním členov, ktoré nasledujú po tvare (meniaca sa bunka)*(konštanta). Podobným spôsobom vypočítam kapitál použitý každý rok a prácu použitú každý rok skopírovaním vzorca z bunky E2 do bunky F2:J2 vzorca SUMPRODUCT(doit;E6:E25).

Teraz vyplním dialógové okno parametrov doplnku Riešiteľ, ako je znázornené na obrázku 30-2.

Book Naším cieľom je maximalizovať NPV vybraných projektov (bunka B2). Meniace sa bunky (rozsah s názvom doit) sú binárne meniace sa bunky každého projektu. Obmedzenie E2:J2<=E4:J4 zabezpečí, že v priebehu každého roka bude použitý kapitál a práca nižší alebo rovnaký ako dostupný kapitál a práca. Ak chcem pridať obmedzenie, ktoré spôsobí, že meniace sa bunky sú binárne, kliknem v dialógovom okne Parametre doplnku Riešiteľ na položku Pridať a v zozname v strede dialógového okna potom vyberiem položku Kôš. Dialógové okno Pridať obmedzenie by sa malo zobraziť tak, ako je to znázornené na obrázku 30-3.

Book Náš model je lineárny, pretože cieľová bunka sa počíta ako súčet podmienok, ktoré majú tvar (meniaca sa bunka)*(konštanta), a pretože obmedzenia použitia zdrojov sa vypočítavajú porovnaním súčtu (meniacich sa buniek)*(konštánt) s konštantou.

Po vyplnení dialógového okna Parametre doplnku Riešiteľ kliknite na položku Riešiť a zobrazia sa výsledky znázornené vyššie na obrázku 30-1. Spoločnosť môže získať maximálnu NPV vo výške 9 293 miliónov USD (9,293 miliardy USD) výberom projektov 2, 3, 6 – 10, 14 – 16, 19 a 20.

Spracovanie iných obmedzení

Modely výberu projektu majú niekedy iné obmedzenia. Predpokladajme napríklad, že ak vyberieme Projekt 3, musíme vybrať aj Projekt 4. Keďže naše aktuálne optimálne riešenie vyberá Project 3, ale nie Project 4, vieme, že naše súčasné riešenie nemôže zostať optimálne. Ak chcete vyriešiť tento problém, jednoducho pridajte obmedzenie, že binárna meniaca sa bunka Projektu 3 je menšia alebo rovnaká ako bunka meniaca sa binárne pre Projekt 4.

Tento príklad nájdete v hárku Ak 3, potom 4 v súbore Capbudget.xlsx, ktorý je znázornený na obrázku 30-4. Bunka L9 odkazuje na binárnu hodnotu súvisiacu s Projektom 3 a bunka L12 na binárnu hodnotu súvisiacu s Projektom 4. Ak pridáme obmedzenie L9<=L12, ak vyberieme Projekt 3, L9 sa rovná 1 a naše obmedzenie prinúti L12 (binárny súbor Projektu 4) rovnať sa 1. Naše obmedzenie musí tiež ponechať binárnu hodnotu v meniacej sa bunke Projektu 4 bez obmedzenia, ak nevyberieme Projekt 3. Ak nevyberieme Projekt 3, L9 sa rovná 0 a naše obmedzenie povolí, aby sa binárny súbor Projektu 4 rovnal 0 alebo 1, čo požadujeme. Nové optimálne riešenie je znázornené na obrázku 30-4.

Book Nové optimálne riešenie sa vypočíta, ak výber Projektu 3 znamená, že musíme vybrať aj Projekt 4. Teraz predpokladajme, že môžeme urobiť iba štyri projekty spomedzi projektov 1 až 10. (Pozri hárok Najviac 4 P1 – P10 zobrazený na obrázku 30-5.) V bunke L8 vypočítame súčet binárnych hodnôt súvisiacich s projektmi 1 až 10 pomocou vzorca SUM(A6:A15). Potom pridáme obmedzenie L8<=L10, ktoré zabezpečí, že budú vybraté maximálne 4 z prvých 10 projektov. Nové optimálne riešenie je znázornené na obrázku 30-5. NPV klesla na 9,014 miliardy dolárov.

Riešenie problémov binárneho a celočíselného programovania

Modely lineárneho riešiteľa, v ktorých musia byť niektoré alebo všetky meniace sa bunky binárne alebo celočíselné, sú zvyčajne ťažšie riešiteľné ako lineárne modely, v ktorých môžu byť všetky menené bunky zlomkami. Z tohto dôvodu sme často spokojní s takmer optimálnym riešením binárneho alebo celočíselného programovacieho problému. Ak je váš model doplnku Riešiteľ spustený dlho, zvážte úpravu nastavenia tolerancie v dialógovom okne Riešiteľ – možnosti. (Pozri obrázok 30-6.) Nastavenie tolerancie na úrovni 0,5 % napríklad znamená, že Riešiteľ sa zastaví, keď prvýkrát nájde uskutočniteľné riešenie, ktoré je v rozsahu 0,5 % od teoretickej optimálnej cieľovej hodnoty bunky (teoretická optimálna cieľová hodnota bunky je optimálna cieľová hodnota zistená pri vynechaní binárnych a celočíselných obmedzení). Často stojíme pred voľbou medzi nájdením optimálnej odpovede do 10 percent za 10 minút alebo nájdením optimálneho riešenia za dva týždne počítačového času! Predvolená hodnota tolerancie je 0,05 %, čo znamená, že Riešiteľ sa zastaví, keď nájde hodnotu cieľovej bunky v rozsahu 0,05 % od teoretickej optimálnej hodnoty cieľovej bunky.

Problémy

1. Spoločnosť má deväť zvažovaných projektov. V nasledujúcej tabuľke je uvedené NPV pridané jednotlivými projektmi a kapitál požadovaný každým projektom počas nasledujúcich dvoch rokov. (Všetky čísla sú v miliónoch.) Napríklad Project 1 pridá 14 miliónov USD v NPV a bude vyžadovať výdavky vo výške 12 miliónov USD počas 1. roka a 3 milióny USD počas 2. roka. Počas 1. roka je na projekty k dispozícii kapitál vo výške 50 miliónov USD a 20 miliónov USD počas 2. roka.

	NPV	Výdavky za 1. rok	Výdavky na 2. rok
Projekt 1	14	12	3
Project 2	17	54	7
Project 3	17	6	6
Projekt 4	15	6	2
Projekt 5	40	30	35
Project 6	12	6	6
Project 7	14	48	4
Project 8	10	36	3
Project 9	12	18	3

• Ak nemôžeme realizovať zlomok projektu, ale musíme realizovať celý alebo žiadny projekt, ako môžeme maximalizovať NPV?
• Predpokladajme, že keď sa realizuje Project 4, musí sa realizovať Project 5. Ako môžeme maximalizovať NPV?

• Vydavateľstvo sa snaží určiť, ktorú z 36 kníh by malo tento rok vydať. Súbor Pressdata.xlsx poskytuje tieto informácie o každej knihe:

  • Predpokladané výnosy a náklady na vývoj (v tisícoch dolárov)
  • Strany v každej knihe
  • Či je kniha zameraná na cieľovú skupinu softvérových vývojárov (označená číslom 1 v stĺpci E)
    Vydavateľstvo môže tento rok vydať knihy s celkovou veľkosťou až 8 500 strán a musí vydať aspoň štyri knihy zamerané na vývojárov softvéru. Ako môže spoločnosť maximalizovať svoj zisk?

O článku

Tento článok vychádza z knihy Wayne L. Winston, ktorý vytvoril dokument Microsoft Office Excel 2007 Data Analysis and Business Modeling .

Táto kniha v triednom štýle bola vyvinutá zo série prezentácií Wayna Winstona, známeho štatistika a profesora obchodu, ktorý sa špecializuje na kreatívne a praktické aplikácie Excelu.