Utilizarea Rezolvitorului pentru bugetarea capitalului

Majoritatea societăților doresc să întreprindă proiecte care contribuie la cea mai mare valoare netă actualizată (NPV), cu resurse limitate (de obicei capital și muncă). Să presupunem că o companie de dezvoltare software încearcă să determine care dintre cele 20 de proiecte software pe care ar trebui să le întreprindă. NPV (în milioane de dolari) a contribuit de fiecare proiect, precum și de capital (în milioane de dolari), iar numărul de programatori necesari în următorii trei ani este dat în foaia de lucru Model de bază din Capbudget.xlsx de fișiere, care este afișată în Figura 30-1 de pe pagina următoare. De exemplu, Project 2 are un venit de 908 milioane lei. Aceasta necesită 151 milioane dolari în timpul Anului 1, 269 milioane dolari în timpul Anului 2, și 248 milioane dolari în timpul Anului 3. Proiectul 2 necesită 139 programatori în timpul Anului 1, 86 programatori în timpul Anului 2 și 83 programatori în timpul Anului 3. Celulele E4:G4 arată capitalul (în milioane de dolari) disponibil în fiecare dintre cei trei ani, iar celulele H4:J4 indică numărul de programatori disponibili. De exemplu, în timpul Anului 1 până la 2,5 miliarde dolari în capital și 900 programatori sunt disponibile.

Compania trebuie să decidă dacă trebuie să întreprindă fiecare proiect. Să presupunem că nu putem întreprinde o fracțiune a unui proiect de software; dacă alocăm 0,5 din resursele necesare, de exemplu, am avea un program nelucrător care ne-ar aduce 0 dolari venituri!

Trucul în modelarea situațiilor în care faceți sau nu faceți ceva este să utilizați celule binare modificabile. O celulă modificatoare binară este întotdeauna egală cu 0 sau 1. Atunci când o celulă de schimbare binară care corespunde unui proiect este egală cu 1, facem proiectul. Dacă o celulă de schimbare binară care corespunde unui proiect este egală cu 0, nu facem proiectul. Ați configurat Rezolvitorul să utilizeze o zonă de celule binare modificabile adăugând o restricție - selectați celulele modificabile pe care doriți să le utilizați, apoi alegeți Bin din listă în caseta de dialog Adăugare restricție.

Cu acest fundal, suntem gata să rezolvăm problema de selectare a proiectului software. Ca întotdeauna cu un model Rezolvitor, începem prin a identifica celula țintă, celulele modificabile și restricțiile.

• Celula țintă. Maximizăm valoarea NPV generată de proiectele selectate.

• Celule modificabile. Căutăm o celulă de schimbare binară 0 sau 1 pentru fiecare proiect. Am localizat aceste celule în zona A6:A25 (și am denumit zona doit). De exemplu, un 1 din celula A6 indică faptul că întreprindem Proiectul 1; un 0 în celula C6 indică faptul că nu întreprindem Proiectul 1.

• Constrângeri. Trebuie să ne asigurăm că, pentru fiecare An t (t=1, 2, 3), Capital de an utilizat este mai mic sau egal cu Anul t capital disponibil, iar munca din Anul t este mai mică sau egală cu numărul de ani de muncă disponibil.

După cum puteți vedea, foaia noastră de lucru trebuie să calculeze pentru orice selecție de proiecte NPV, capitalul utilizat anual și programatorii utilizați în fiecare an. În celula B2, utilizez formula SUMPRODUCT(doit,NPV) pentru a calcula valoarea NPV totală generată de proiectele selectate. (Numele zonei NPV se referă la zona C6:C25.) Pentru fiecare proiect cu un 1 în coloana A, această formulă preia NPV-ul proiectului, iar pentru fiecare proiect cu un 0 în coloana A, această formulă nu preia NPV-ul proiectului. Prin urmare, putem calcula valoarea NPV a tuturor proiectelor, iar celula noastră țintă este liniară, deoarece se calculează însumând termenii care urmează formularului (celulă modificantă)*(constantă). Într-un mod similar, calculez capitalul utilizat în fiecare an și munca utilizată în fiecare an copiind de la E2 la F2:J2 formula SUMPRODUCT(doit,E6:E25).

Acum completez caseta de dialog Parametri Rezolvitor, așa cum se arată în Figura 30-2.

Obiectivul nostru este să maximizăm NPV pentru proiectele selectate (celula B2). Celulele noastre modificabile (zona denumită doit) sunt celulele modificabile binare pentru fiecare proiect. Restricția E2:J2<=E4:J4 asigură că, pe parcursul fiecărui an, capitalul și munca utilizate sunt mai mici sau egale cu capitalul și munca disponibile. Pentru a adăuga constrângerea care face binare celulele modificabile, fac clic pe Adăugare în caseta de dialog Parametri Rezolvitor, apoi selectez Bin din lista din mijlocul casetei de dialog. Caseta de dialog Adăugare restricție ar trebui să apară așa cum se arată în Figura 30-3.

Modelul nostru este liniar, deoarece celula țintă este calculată ca sumă de termeni care au forma (celulă modificabilă)*(constantă) și deoarece restricțiile de utilizare a resurselor sunt calculate prin compararea sumei (celulelor modificabile)*(constantelor) cu o constantă.

Cu caseta de dialog Parametri Rezolvitor completată, faceți clic pe Rezolvare și avem rezultatele afișate anterior în Figura 30-1. Compania poate obține un NPV maxim de 9293 milioane dolari (9.293 miliarde dolari), alegând Proiecte 2, 3, 6-10, 14-16, 19, și 20.

Uneori, modelele de selectare a proiectelor au alte restricții. De exemplu, să presupunem că, dacă selectăm Project 3, trebuie să selectăm și Project 4. Deoarece soluția noastră optimă curentă selectează Project 3, dar nu Project 4, știm că soluția noastră curentă nu poate rămâne optimă. Pentru a rezolva această problemă, adăugați pur și simplu restricția conform căreia celula de schimbare binară pentru Project 3 este mai mică sau egală cu celula de schimbare binară pentru Project 4.

Puteți găsi acest exemplu în foaia de lucru If 3 then 4 din Capbudget.xlsx de fișier, care este afișată în Figura 30-4. Celula L9 se referă la valoarea binară asociată cu Project 3 și la celula L12 la valoarea binară asociată cu Project 4. Adăugând restricția L9<=L12, dacă alegem Project 3, L9 este egal cu 1 și constrângerea noastră forțează L12 (binarul Project 4) să egaleze 1. De asemenea, constrângerea noastră trebuie să lase valoarea binară în celula de modificare din Project 4 nerestricționat dacă nu selectăm Project 3. Dacă nu selectăm Project 3, L9 este egal cu 0 și restricția noastră permite ca fișierul binar Project 4 să fie egal cu 0 sau 1, ceea ce dorim. Noua soluție optimă este prezentată în Figura 30-4.

O nouă soluție optimă este calculată dacă selectarea Project 3 înseamnă că trebuie să selectăm și Project 4. Acum să presupunem că putem face doar patru proiecte din proiectele 1-10. (Consultați foaia de lucru Cel mult 4 de la P1-P10 , afișată în Figura 30-5.) În celula L8, vom calcula suma valorilor binare asociate cu proiecte de la 1 la 10 cu formula SUM(A6:A15). Apoi adăugăm restricția L8<=L10, care asigură că sunt selectate cel mult 4 din primele 10 proiecte. Noua soluție optimă este prezentată în Figura 30-5. NPV a scăzut la 9,014 miliarde de dolari.

Modelele liniare rezolvitor în care unele celule modificabile sau toate celulele modificabile trebuie să fie binare sau întregi sunt de obicei mai greu de rezolvat decât modelele liniare în care toate celulele modificabile pot fi fracții. Din acest motiv, de multe ori suntem mulțumiți de o soluție aproape optimă la o problemă de programare binară sau întreagă. Dacă modelul Rezolvitor rulează mult timp, se recomandă să luați în considerare ajustarea setării Toleranță din caseta de dialog Opțiuni Rezolvitor. (Vezi Figura 30-6.) De exemplu, o setare Toleranță de 0,5% înseamnă că Rezolvitorul se va opri prima dată când găsește o soluție fezabilă care se încadrează în 0,5% din valoarea optimă teoretică a celulei țintă (valoarea teoretică optimă a celulei țintă este valoarea țintă optimă găsită atunci când sunt omise restricțiile binare și întregi). Adesea ne confruntăm cu o alegere între găsirea unui răspuns în 10 procente din optime în 10 minute sau găsirea unei soluții optime în două săptămâni de timp la computer! Valoarea implicită Toleranță este 0,05%, ceea ce înseamnă că Rezolvitorul se oprește atunci când găsește o valoare de celulă țintă în limita a 0,05% din valoarea teoretică optimă a celulei țintă.

1. O companie are în vedere nouă proiecte. NPV adăugată de fiecare proiect și capitalul necesar pentru fiecare proiect în următorii doi ani se afișează în tabelul următor. (Toate numerele sunt în milioane.) De exemplu, Proiectul 1 va aduna 14 milioane lei în NPV și va necesita cheltuieli de 12 milioane LEI în timpul Anului 1 și 3 milioane USD în anul 2. Pe parcursul anului 1, 50 milioane dolari în capital este disponibil pentru proiecte, și 20 milioane dolari este disponibil în timpul Anului 2.

	NPV	Cheltuieli din anul 1	Cheltuieli din anul 2
Proiect 1	14	12	3
Proiect 2	17	54	7
Project 3	17	6	6
Proiect 4	15	6	2
Proiect 5	40	30	35
Proiect 6	12	6	6
Proiectul 7	14	48	4
Proiect 8	10	36	3
Proiect 9	12	18	3

• Dacă nu putem întreprinde o fracțiune a unui proiect, dar trebuie să întreprindem fie întregul proiect, fie niciunul, cum putem maximiza NPV?

• Să presupunem că, dacă se realizează Proiectul 4, proiectul 5 trebuie să fie efectuat. Cum putem maximiza NPV?

• O companie de publicare încearcă să determine care din 36 de cărți ar trebui să publice în acest an. Fișierul Pressdata.xlsx oferă următoarele informații despre fiecare carte:

  • Venituri proiectate și costuri de dezvoltare (în mii de dolari)

  • Pagini din fiecare carte

  • Dacă cartea este orientată spre o audiență de dezvoltatori de software (indicat de un 1 în coloana E)

    O companie de publicare poate publica cărți care totalizează până la 8500 de pagini în acest an și trebuie să publice cel puțin patru cărți orientate către dezvoltatorii de software. Cum poate firma să își maximizeze profitul?

Acest articol a fost adaptat de la Microsoft Office Excel 2007 Data Analysis and Business Modeling de Wayne L. Winston.

Această carte în stil clasă a fost dezvoltată dintr-o serie de prezentări ale lui Wayne Winston, un statistician cunoscut și profesor de afaceri specializat în aplicații creative și practice din Excel.