Cum poate o firmă să utilizeze Rezolvitorul pentru a determina ce proiecte ar trebui să întreprindă?
În fiecare an, o companie precum Eli Lilly trebuie să determine ce medicamente să dezvolte; o companie precum Microsoft, ce programe software să dezvolte; o companie precum Proctor & Gamble, care noi produse de consum să dezvolte. Caracteristica Rezolvitor din Excel poate ajuta o firmă să ia aceste decizii.
Cum poate o firmă să utilizeze Rezolvitorul pentru a determina ce proiecte ar trebui să întreprindă?
Cele mai multe corporații doresc să întreprindă proiecte care contribuie cu cea mai mare valoare netă actualizată (NPV), sub rezerva resurselor limitate (de obicei capital și forță de muncă). Să presupunem că o companie de dezvoltare software încearcă să determine care dintre cele 20 de proiecte software ar trebui să întreprindă. NPV (în milioane de dolari) contribuit de fiecare proiect, precum și capitalul (în milioane de dolari) și numărul de programatori necesari în fiecare dintre următorii trei ani sunt dați în foaia de lucru a modelului de bază din Capbudget.xlsx de dosar, care este prezentată în Figura 30-1 de pe pagina următoare. De exemplu, Proiectul 2 returnează 908 milioane de lei. Este nevoie de 151 de milioane de dolari în anul 1, 269 de milioane de dolari în anul 2 și 248 de milioane de dolari în anul 3. Proiectul 2 necesită 139 de programatori în anul 1, 86 de programatori în anul 2 și 83 de programatori în anul 3. Celulele E4:G4 arată capitalul (în milioane de dolari) disponibil pentru fiecare dintre cei trei ani, iar celulele H4:J4 arată câți programatori sunt disponibili. De exemplu, în anul 1 sunt disponibili până la 2,5 miliarde de dolari în capital și 900 de programatori.
Compania trebuie să decidă dacă ar trebui să întreprindă fiecare proiect. Să presupunem că nu putem întreprinde o fracțiune dintr-un proiect software; Dacă alocăm 0,5 din resursele necesare, de exemplu, am avea un program nefuncțional care ne-ar aduce venituri de 0 USD!
Trucul în modelarea situațiilor în care faceți sau nu faceți ceva este să utilizați celule binare modificabile. O celulă binară modificabilă este întotdeauna egală cu 0 sau 1. Când o celulă binară modificabilă care corespunde unui proiect este egală cu 1, noi facem proiectul. Dacă o celulă binară modificabilă care corespunde unui proiect este egală cu 0, noi 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 lista din caseta de dialog Adăugare restricție.
Cu acest background, suntem gata să rezolvăm problema selecției proiectelor software. Ca întotdeauna în cazul unui model de Rezolvitor, începem prin a identifica celula țintă, celulele modificabile și restricțiile.
- Celula țintă. Maximizăm VAN generat de proiectele selectate.
- Celule modificabile. Căutăm o celulă binară schimbătoare 0 sau 1 pentru fiecare proiect. Am găsit aceste celule în zona A6:A25 (și am denumit zona doit). De exemplu, un 1 în celula A6 indică faptul că efectuăm Proiectul 1; un 0 în celula C6 indică faptul că nu ne desfășurăm Proiectul 1.
- Restricții. Trebuie să ne asigurăm că pentru fiecare an t (t = 1, 2, 3), anul t capital utilizat este mai mic sau egal cu anul t capital disponibil, iar anul t forța de muncă utilizată este mai mică sau egală cu anul t de muncă disponibilă.
După cum puteți vedea, foaia noastră de lucru trebuie să calculeze pentru orice selecție de proiecte VAN, 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 care are un 1 în coloana A, această formulă alege valoarea NPV a proiectului, iar pentru fiecare proiect care are un 0 în coloana A, această formulă nu alege valoarea NPV a proiectului. Prin urmare, putem calcula valoarea NPV a tuturor proiectelor, iar celula-țintă este liniară, deoarece se calculează prin însumarea termenilor care urmează forma (celulă modificabilă)*(constantă). În mod similar, calculez capitalul utilizat în fiecare an și forța de muncă 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 de a maximiza valoarea NPV a proiectelor selectate (celula B2). Celulele noastre modificabile (zona numită doit) sunt celulele binare modificabile pentru fiecare proiect. Constrângerea E2:J2<=E4:J4 asigură că în fiecare an capitalul și forța de muncă utilizate sunt mai mici sau egale cu capitalul și forța de muncă disponibile. Pentru a adăuga restricția care face celulele modificabile binare, fac clic pe Adăugare în caseta de dialog Parametri Rezolvitor și selectez Compartimentul 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 suma termenilor care au forma (celulă modificabilă)*(constantă) și deoarece restricțiile de utilizare a resurselor sunt calculate prin compararea sumei (celulelor modificabile)*(constante) cu o constantă.
Cu caseta de dialog Parametri Rezolvitor completată, faceți clic pe Rezolvare și avem rezultatele arătate mai devreme în Figura 30-1. Compania poate obține un NPV maxim de 9.293 milioane USD (9,293 miliarde USD), alegând Proiectele 2, 3, 6-10, 14-16, 19 și 20.
Gestionarea altor restricții
Uneori, modelele de selecție de proiecte au alte restricții. De exemplu, să presupunem că, dacă selectăm Proiectul 3, trebuie să selectăm și Proiectul 4. Deoarece soluția noastră optimă actuală selectează Proiectul 3, dar nu și Proiectul 4, știm că soluția noastră actuală nu poate rămâne optimă. Pentru a rezolva această problemă, adăugați pur și simplu restricția conform căreia celula binară modificantă pentru Proiectul 3 este mai mică sau egală cu celula binară modificantă pentru Proiectul 4.
Puteți găsi acest exemplu în foaia de lucru Dacă 3 atunci 4 din Capbudget.xlsx de fișier, care este afișată în Figura 30-4. Celula L9 se referă la valoarea binară asociată cu Proiectul 3, iar celula L12 la valoarea binară asociată cu Proiectul 4. Adunând restricția L9<=L12, dacă alegem Proiectul 3, L9 este egal cu 1 și constrângerea noastră forțează L12 (binarul Proiectului 4) la valoarea 1. De asemenea, restricția noastră trebuie să lase valoarea binară din celula modificantă a Proiectului 4 nerestricționată dacă nu selectăm Proiectul 3. Dacă nu selectăm Proiectul 3, L9 este egal cu 0 și restricția noastră permite binarului Proiectului 4 să fie egal cu 0 sau 1, ceea ce dorim. Noua soluție optimă este prezentată în Figura 30-4.
Se calculează o nouă soluție optimă dacă selectarea Proiectului 3 înseamnă că trebuie să selectăm și Proiectul 4. Acum, să presupunem că putem face doar patru proiecte din proiectele de la 1 la 10. (Vedeți foaia de lucru Cel mult 4 din P1-P10, afișată în Figura 30-5.) În celula L8, calculăm 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 USD.
Rezolvarea problemelor de programare în limbile binare și întregi
Modelele liniare din Rezolvitor, în care unele celule modificate sau toate 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, suntem adesea mulțumiți de o soluție aproape optimă la o problemă de programare binară sau de numere întregi. Dacă modelul dvs. de Rezolvitor rulează mult timp, se recomandă să luați în considerare ajustarea setării de toleranță în caseta de dialog Opțiuni Rezolvitor. (Vezi Figura 30-6.) De exemplu, o setare de toleranță de 0,5 % înseamnă că Rezolvitor se va opri prima dată când găsește o soluție fezabilă care se află în limita a 0,5 procente din valoarea optimă teoretică a celulei-țintă (valoarea optimă teoretică a celulei-țintă este valoarea țintă optimă găsită atunci când se omit restricțiile pentru binar și întreg). Adesea ne confruntăm cu o alegere între a găsi un răspuns în termen de 10% din optim în 10 minute sau a găsi o soluție optimă în două săptămâni de timp de computer! Valoarea de toleranță implicită este 0,05%, ceea ce înseamnă că Rezolvitorul se oprește atunci când găsește o valoare de celulă țintă la 0,05 procente din valoarea optimă teoretică a celulei-țintă.
Probleme
- O companie are nouă proiecte în curs de examinare. VAN adăugat de fiecare proiect și capitalul necesar fiecărui proiect în următorii doi ani sunt prezentate în tabelul următor. (Toate numerele sunt în milioane.) De exemplu, Proiectul 1 va adăuga 14 milioane de lei în VAN și va necesita cheltuieli de 12 milioane de lei în anul 1 și 3 milioane de lei în anul 2. În anul 1, 50 de milioane de dolari în capital sunt disponibili pentru proiecte, iar 20 de milioane de dolari sunt disponibili în anul 2.
| NPV | Cheltuieli pentru anul 1 | Cheltuieli pentru anul 2 | |
|---|---|---|---|
| Proiectul 1 | 14 | 12 | 3 |
| Proiectul 2 | 17 | 54 | 7 |
| Proiectul 3 | 17 | 6 | 6 |
| Proiectul 4 | 15 | 6 | 2 |
| Proiectul 5 | 40 | 30 | 35 |
| Proiectul 6 | 12 | 6 | 6 |
| Proiectul 7 | 14 | 48 | 4 |
| Proiectul 8 | 10 | 36 | 3 |
| Project 9 | 12 | 18 | 3 |
- Dacă nu putem întreprinde o fracțiune dintr-un proiect, dar trebuie să întreprindem fie tot proiectul, fie nimic, cum putem maximiza funcția NPV?
- Să presupunem că, dacă se întreprinde Proiectul 4, trebuie întreprins Proiectul 5. Cum putem maximiza funcția NPV?
O editură încearcă să determine care dintre cele 36 de cărți ar trebui să publice anul acesta. Fișierul Pressdata.xlsx oferă următoarele informații despre fiecare carte:
- Venituri preconizate și costuri de dezvoltare (în mii de dolari)
- Pagini în fiecare carte
- Dacă cartea se adresează unui public de dezvoltatori de software (indicat prin 1 în coloana E)
O editură 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 compania să-și maximizeze profitul?
Despre articol
Acest articol a fost adaptat după Analiza datelor și modelarea de afaceri Microsoft Office Excel 2007 de Wayne L. Winston.
Această carte în stil de clasă a fost dezvoltată dintr-o serie de prezentări de către Wayne Winston, un statistician și profesor de afaceri bine cunoscut, specializat în aplicații creative și practice ale Excel.