Používanie Riešiteľa na vytváranie veľkých a veľkých rozpočtov

Väčšina korporácií chce vykonávať projekty, ktoré prispievajú k najväčšej čistej súčasnej hodnote (NPV), s výhradou obmedzených zdrojov (zvyčajne kapitál a práca). Povedzme, že spoločnosť na vývoj softvéru sa snaží zistiť, ktorý z 20 softvérových projektov by sa mal realizovať. NPV (v miliónoch dolárov) prispel každý projekt, rovnako ako kapitál (v miliónoch dolárov) a počet programátorov potrebné počas každého z nasledujúcich troch rokov je uvedený na hárku Základný model 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. To si vyžaduje 151 miliónov dolárov v priebehu roka 1, 269 miliónov dolárov počas roka 2, a 248 miliónov dolárov počas roka 3. Projekt 2 vyžaduje 139 programátorov počas roka 1, 86 programátorov počas roka 2 a 83 programátorov počas roka 3. Bunky E4:G4 zobrazujú kapitál (v miliónoch dolárov) dostupný počas každého z troch rokov a bunky H4:J4 označujú počet dostupných programátorov. Napríklad počas roka 1 až 2,5 miliardy dolárov kapitálu a 900 programátorov sú k dispozícii.

Spoločnosť sa musí rozhodnúť, či má realizovať každý projekt. Predpokladajme, že nemôžeme vykonať zlomok softvérového projektu. ak by sme vyhradili 0,5 z potrebných zdrojov, napríklad by sme mali nepracovný program, ktorý by nám priniesol 0 dolárov príjmy!

Trik v situáciách modelovania, v ktorých buď urobíte alebo niečo neurobíte, je použitie binárnych meniacich sa buniek. Bunka, ktorá mení binárne údaje, sa vždy rovná 0 alebo 1. Keď sa bunka s binárnou zmenou, ktorá zodpovedá projektu, rovná 1, vykoná sa projekt. Ak sa bunka, ktorá mení binárne údaje zodpovedajúce projektu, rovná 0, projekt sa nevytýli. Riešiteľa nastavíte tak, aby používal rozsah binárnych meniacich sa buniek pridaním obmedzenia. Vyberte menené bunky, ktoré chcete použiť, a potom v zozname v dialógovom okne Pridanie obmedzenia vyberte položku Priehradka.

Na tomto pozadí sme pripravení vyriešiť problém s výberom softvérového projektu. Ako vždy pri modeli Riešiteľa, začneme identifikovaním cieľovej bunky, meniacich sa buniek a obmedzení.

• Cieľová bunka. Maximalizujeme NPV generované vybratými projektmi.

• Mení sa bunka. Pre každý projekt hľadáme bunku s dvojkovým číslom 0 alebo 1. Tieto bunky som našiel v rozsahu A6:A25 (a pomenoval rozsah doit). Napríklad číslo 1 v bunke A6 označuje, že vykonávame Projekt 1. Číslo 0 v bunke C6 označuje, že projekt 1 nevykonávame.

• Obmedzenia. Musíme zabezpečiť, aby pre každý rok t (t = 1, 2, 3), rok t použitý kapitál je menší alebo rovný rok t kapitálu k dispozícii, a Rok t práce je menšia alebo rovnaká ako rok t práce k dispozícii.

Ako vidíte, náš hárok musí vypočítať pre každý výber projektov NPV, kapitál používaný ročne a programátorov používaných každý rok. V bunke B2 použijem vzorec SUMPRODUCT(doit,NPV) na výpočet celkového počtu NPV vygenerovaných vybratými projektmi. (Názov rozsahu NPV odkazuje na rozsah C6:C25.) Pre každý projekt s 1 stĺpcom A tento vzorec vyberie NPV projektu a pre každý projekt s 0 v stĺpci A tento vzorec nezachytí 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číta sčítavaním výrazov, ktoré nasledujú za formulárom (meniaca sa bunka)*(konštanta). Podobným spôsobom vypočítam každý rok použitý kapitál a prácu, ktorá sa používa každý rok skopírovaním vzorca SUMPRODUCT(doit,E6:E25) z E2 do F2:J2.

Teraz vypĺňam dialógové okno Parametre doplnku Riešiteľ, ako je znázornené na obrázku 30-2.

Naším cieľom je maximalizovať NPV vybratých projektov (bunka B2). Naše meniace sa bunky (rozsah s názvom doit) sú binárne meniace sa bunky pre každý projekt. Obmedzenie E2:J2<=E4:J4 zabezpečuje, že počas každého roka je použité hlavné mesto a práca menšie alebo rovné základnému imania a dostupnej práci. Ak chcete pridať obmedzenie, ktoré zmení bunky na binárne, v dialógovom okne Parametre doplnku Riešiteľ kliknem na položku Pridať a potom v zozname uprostred dialógového okna vyberiem položku Priehradka. Dialógové okno Pridať obmedzenie by sa malo zobraziť tak, ako je to znázornené na obrázku 30-3.

Náš model je lineárny, pretože cieľová bunka sa vypočíta ako súčet výrazov, ktoré majú tvar (meniaca sa bunka)*(konštanta) a pretože obmedzenia využitia zdrojov sa vypočítajú 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 výsledky sú zobrazené vyššie na obrázku 30-1. Spoločnosť môže získať maximálnu npv 9,293 milióna dolárov (9,293 miliardy dolárov) výberom projektov 2, 3, 6-10, 14-16, 19 a 20.

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

Tento príklad môžete nájsť v hárku If 3, potom 4 v Capbudget.xlsx súboru, ktorý je znázornený na obrázku 30-4. Bunka L9 odkazuje na binárnu hodnotu súvisiacu s Projectom 3 a bunku L12 na binárnu hodnotu súvisiacu s Projectom 4. Pridaním obmedzenia L9<=L12, ak vyberieme Project 3, L9 sa rovná 1 a naše obmedzenie vynúti L12 (binárny projekt 4) na hodnotu 1. Ak nevyberieme Project 3, naše obmedzenie musí ponechať binárnu hodnotu v mennej bunke Projectu 4 neobmedzenú. Ak nevyberieme Project 3, L9 sa rovná 0 a naše obmedzenie umožňuje, aby sa binárne číslo projektu 4 rovndalo 0 alebo 1, čo je to, čo chceme. Nové optimálne riešenie je znázornené na obrázku 30-4.

Nové optimálne riešenie sa vypočíta, ak výber Projectu 3 znamená, že musíme vybrať aj Project 4. Predpokladajme, že môžeme vykonávať iba štyri projekty z projektov 1 až 10. (Pozri hárok s najviac 4 Z P1 –P10 , ktorý je znázornený na obrázku 30-5.) V bunke L8 vypočítame súčet binárnych hodnôt priradených k projektom 1 až 10 pomocou vzorca SUM(A6:A15). Potom pridáme obmedzenie L8<=L10, ktoré zabezpečí, že sa vyberú najviac 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.

Lineárne modely Riešiteľa, v ktorých niektoré alebo všetky meniace sa bunky musia byť binárne alebo celé číslo, sa zvyčajne ťažšie riešia ako lineárne modely, v ktorých môžu byť všetky meniace sa bunky zlomkami. Z tohto dôvodu sme často spokojní s takmer optimálnym riešením problému s binárnym alebo celočíselným programovaním. Ak je váš model Riešiteľ spustený dlho, možno budete chcieť zvážiť úpravu nastavenia Tolerancia v dialógovom okne Možnosti doplnku Riešiteľ. (Pozri obrázok 30-6.) Napríklad nastavenie tolerancie 0,5 % znamená, že Riešiteľ sa zastaví pri prvom nájdení realizovateľného riešenia, ktoré je v rozsahu 0,5 percenta teoretickej optimálnej hodnoty cieľovej bunky (teoretická optimálna hodnota cieľovej bunky je optimálna cieľová hodnota zistená pri vynechaní obmedzení binárneho a celého čísla). Často sme konfrontovaní s možnosťou výberu medzi hľadaním odpovede do 10 percent optimálneho za 10 minút, alebo nájsť optimálne riešenie za dva týždne času počítača! Predvolená hodnota tolerancie je 0,05 %, čo znamená, že Riešiteľ sa zastaví, keď nájde hodnotu cieľovej bunky v rozsahu 0,05 percenta teoretickej optimálnej hodnoty cieľovej bunky.

1. Spoločnosť má deväť projektov. NpV pridané jednotlivými projektmi a kapitál, ktorý vyžaduje každý projekt počas nasledujúcich dvoch rokov, je uvedený v nasledujúcej tabuľke. (Všetky čísla sú v miliónoch.) Napríklad Projekt 1 pridá 14 miliónov DOLÁROV v NPV a bude vyžadovať výdavky vo výške 12 miliónov DOLÁROV počas roka 1 a 3 milióny dolárov počas roka 2. Počas roka 1, 50 miliónov dolárov kapitálu je k dispozícii pre projekty, a 20 miliónov dolárov je k dispozícii počas roka 2.

	NPV	Výdavky za rok 1	Výdavky za rok 2
Projekt 1	14	12	3
Projekt 2	17	54	7
Project 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

• Ak nemôžeme vykonať zlomok projektu, ale musíme vykonať buď celý alebo žiadny projekt, ako môžeme maximalizovať NPV?

• Predpokladajme, že ak sa uskutoční Projekt 4, musí sa uskutočniť Project 5. Ako môžeme maximalizovať NPV?

• Vydavateľstvo sa snaží zistiť, ktorá z 36 kníh by mala vydať v tomto roku. Súbor Pressdata.xlsx poskytuje nasledujúce 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

  • Určuje, či je kniha zameraná na cieľovú skupinu vývojárov softvéru (označená číslom 1 v stĺpci E)

    Vydavateľstvo môže v tomto roku publikovať knihy s celkovou celkovou 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?

Tento článok upravil Wayne L. Winston z programu Microsoft Office Excel 2007 Data Analysis and Business Modeling .

Táto kniha v štýle triedy 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.