Kako preduzeće može da koristi Rešavač kako bi odredilo koje projekte treba da preduzme?
Svake godine, kompanija kao što je Eli Lilli treba da utvrdi koje lekove da razviju; kompanija kao što je Microsoft, koje softverske programe razvijati; kompanija kao što je Proctor & Gamble, koji novi potrošački proizvodi da se razviju. Funkcija "Rešavač" u programu Excel može da pomogne preduzeću u donošenju ovih odluka.
Kako preduzeće može da koristi Rešavač kako bi odredilo koje projekte treba da preduzme?
Većina korporacija želi da preduzme projekte koji doprinose najvećoj neto sadašnjoj vrednosti (NPV), podložni ograničenim resursima (obično kapital i rad). Recimo da kompanija za razvoj softvera pokušava da odredi koji od 20 softverskih projekata treba da preduzme. NPV (u milionima dolara) doprineo svaki projekat, kao i kapital (u milionima dolara) i broj programera potrebnih tokom svake od naredne tri godine je dat na Osnovnom modelu radnog lista u fajlu Capbudget.xlsx, koji je prikazan na slici 30-1 na sledećoj stranici. Na primer, projekat 2 daje 908 miliona dolara. To zahteva $151 miliona tokom godine 1, $269 miliona tokom godine 2, i $248 miliona tokom godine 3. Projekat 2 zahteva 139 programera tokom prve godine, 86 programera tokom druge godine i 83 programera tokom treće godine. Ćelije E4:G4 prikazuju kapital (u milionima dolara) dostupan tokom svake od tri godine, a ćelije H4:J4 pokazuju koliko je programera dostupno. Na primer, tokom prve godine dostupno je do 2,5 milijardi dolara kapitala i 900 programera.
Kompanija mora odlučiti da li treba da preduzme svaki projekat. Pretpostavimo da ne možemo da preduzmemo delić softverskog projekta; Ako izdvojimo 0,5 potrebnih resursa, na primer, imali bismo neradni program koji bi nam doneo prihod od 0 dolara!
Trik u modeliranju situacija u kojima nešto radite ili ne radite je da koristite binarne promenljive ćelije. Binarna promenljiva ćelija je uvek jednaka 0 ili 1. Kada binarna promenljiva ćelija koja odgovara projektu iznosi 1, mi radimo projekat. Ako je binarna promenljiva ćelija koja odgovara projektu jednaka 0, nećemo raditi projekat. Rešavač podešavate tako da koristi opseg binarnih promenljivih ćelija dodavanjem ograničenja – izaberite promenljive ćelije koje želite da koristite, a zatim odaberite Bin sa liste u dijalogu "Dodavanje ograničenja".
Book 
Sa ovim iskustvom, spremni smo da rešimo problem izbora softverskog projekta. Kao i uvek sa modelom programskog dodatka "Rešavač", počinjemo identifikovanjem ciljne ćelije, promenljivih ćelija i ograničenja.
- Ciljna ćelija. Maksimalno povećavamo NPV koji generišu izabrani projekti.
- Promenljive ćelije. Tražimo 0 ili 1 binarnu promenljivu ćeliju za svaki projekat. Locirao sam ove ćelije u opsegu A6: A25 (i nazvao opseg doit). Na primer, broj 1 u ćeliji A6 ukazuje na to da preduzimamo Projekat 1; 0 u ćeliji C6 ukazuje na to da ne preduzimamo Projekat 1.
- Ograničenja. Moramo da obezbedimo da za svaku godinu t (t = 1, 2, 3), godina t kapital koji se koristi je manji ili jednak godini t kapitala na raspolaganju, i godina t rada koja se koristi je manja ili jednaka godini t rada na raspolaganju.
Kao što vidite, naš radni list mora da izračuna NPV za svaki izbor projekata, kapital koji se koristi godišnje i programere koji se koriste svake godine. U ćeliji B2 koristim formulu SUMPRODUCT(doit,NPV) kako bih izračunao ukupan NPV koji generišu izabrani projekti. (Ime opsega NPV upućuje na opseg C6:C25.) Za svaki projekat čiji je broj 1 u koloni A ova formula uzima NPV vrednosti projekta, a za svaki projekat sa 0 u koloni A ova formula ne preuzima NPV vrednosti projekta. Zbog toga smo u mogućnosti da izračunamo NPV svih projekata, a ciljna ćelija je linearna jer se računa sabiranjem članova koji slede oblik (promenljiva ćelija)*(konstanta). Na sličan način, izračunavam kapital koji se koristi svake godine i rad koji se koristi svake godine kopiranjem formule SUMPRODUCT(doit,E6:E25) iz E2 u F2:J2.
Sada ću popuniti dijalog "Parametri rešavača" kao što je prikazano na slici 30-2.
Book 
Naš cilj je da maksimiziramo NPV izabranih projekata (ćelija B2). Promenljive ćelije (opseg koji se zove doit) su binarne promenljive ćelije za svaki projekat. Ograničenje E2:J2<=E4:J4 obezbeđuje da tokom svake godine upotrijebljeni kapital i radna snaga budu manji ili jednaki kapitalu i radnoj snazi koji su na raspolaganju. Da bih dodala ograničenje koje promenljive ćelije čini binarnim, kliknula ću na dugme "Dodaj" u dijalogu "Parametri programskog dodatka" i izabrati opciju Bin sa liste u sredini dijaloga. Dijalog "Dodavanje ograničenja" trebalo bi da se pojavi kao što je prikazano na slici 30-3.
Book 
Model je linearan zato što se ciljna ćelija računa kao zbir termina koji imaju oblik (promenljiva ćelija)*(konstanta) i zato što se ograničenja upotrebe resursa izračunavaju poređenjem zbira (promenljivih ćelija)*(konstanti) sa konstantom.
Uz popunjen dijalog "Parametri rešavača", kliknite na dugme "Reši" i imamo rezultate prikazane ranije na slici 30-1. Kompanija može da dobije maksimalni NPV od $9,293 miliona ($9.293 milijarde) izborom projekata 2, 3, 6–10, 14–16, 19, i 20.
Rukovanje drugim ograničenjima
Ponekad modeli za izbor projekata imaju druga ograničenja. Na primer, pretpostavimo da ako izaberemo Projekat 3, moramo da izaberemo i Projekat 4. Znamo da trenutno optimalno rešenje ne može ostati optimalno jer naše trenutno optimalno rešenje bira Project 3, ali ne Project 4. Da biste rešili ovaj problem, jednostavno dodajte ograničenje da je binarna promenljiva ćelija za Project 3 manja ili jednaka binarnoj promenljivoj ćeliji za Project 4.
Ovaj primer možete da pronađete na radnom listu Ako 3 onda 4 u Capbudget.xlsx datoteke, koja je prikazana na slici 30-4. Ćelija L9 upućuje na binarnu vrednost u vezi sa 3. projektom, a ćeliju L12 na binarnu vrednost povezanu sa 4-projektom. Dodavanjem ograničenja L9<=L12, ako odaberemo Projekat 3, L9 je jednako 1 i naše ograničenje primorava L12 (binarni projekat 4) da bude jednak 1. Naše ograničenje takođe mora da ostavi neograničenu binarnu vrednost u promenljivoj ćeliji projekta 4 ako ne izaberemo Projekat 3. Ako ne izaberemo Projekat 3, L9 je jednako 0, a naše ograničenje dozvoljava da binarni Projekat 4 bude jednak 0 ili 1, što je ono što želimo. Novo optimalno rešenje je prikazano na slici 30-4.
Book 
Novo optimalno rešenje se izračunava ako izbor projekta 3 znači da moramo izabrati i projekat 4. Sada pretpostavimo da možemo da uradimo samo četiri projekta od projekta 1 do 10. (Pogledajte najviše 4 radnog lista P1–P10, prikazano na slici 30-5.) U ćeliji L8 izračunavamo zbir binarnih vrednosti povezanih sa projektima od 1 do 10 pomoću formule SUM(A6:A15). Zatim dodajemo ograničenje L8<=L10, koje obezbeđuje da su izabrana najviše 4 od prvih 10 projekata. Novo optimalno rešenje je prikazano na slici 30-5. NPV je pao na 9,014 milijardi dolara.
Rešavanje problema programiranja binarnih i celih brojeva
Obično je teže rešiti modele linearnog rešavača u kojima se zahteva da neke ili sve promenljive ćelije budu binarne ili cele brojeve nego linearne modele u kojima je svim promenljivim ćelijama dozvoljeno da budu razlomci. Iz tog razloga, često smo zadovoljni sa skoro optimalnim rešenjem problema binarnog ili celobrojnog programiranja. Ako vaš model programskog dodatka Solver radi duže vreme, možda ćete želeti da razmotrite prilagođavanje postavke tolerancije u dijalogu "Opcije programskog dodatka Solver". (Pogledajte sliku 30-6.) Na primer, postavka tolerancije od 0,5% znači da će se rešavač zaustaviti kada prvi put pronađe izvodljivo rešenje koje je unutar 0,5 procenata od teoretske optimalne vrednosti ciljne ćelije (teoretska optimalna vrednost ciljne ćelije je optimalna ciljna vrednost koja se dobije kada se binarna ograničenja i ograničenja celog broja izostave). Često smo suočeni sa izborom između pronalaženja odgovora u roku od 10 odsto optimalnog za 10 minuta ili pronalaženja optimalnog rešenja za dve nedelje vremena na računaru! Podrazumevana vrednost tolerancije je 0,05%, što znači da se Rešavač zaustavlja kada pronađe vrednost ciljne ćelije unutar 0,05 procenata od teoretske optimalne vrednosti ciljne ćelije.
Problemi
- Kompanija ima devet projekata u razmatranju. NPV vrednost koju dodaje svaki projekat i kapital potreban za svaki projekat tokom sledeće dve godine prikazani su u sledećoj tabeli. (Svi brojevi su u milionima.) Na primer, projekat 1 će dodati 14 miliona USD u NPV i zahtevati troškove od 12 miliona USD tokom 1. godine i 3 miliona USD tokom 2. godine. Tokom 1. godine, 50 miliona dolara kapitala je dostupno za projekte, a 20 miliona dolara je dostupno tokom 2. godine.
| NPV | Rashodi za prvu godinu | Rashodi za 2. godinu | |
|---|---|---|---|
| 1. projekat | 14 | 12 | 3 |
| Project 2 | 17 | 54 | 7 |
| Project 3 | 17 | 6 | 6 |
| Project 4 | 15 | 6 | 2 |
| Project 5 | 40 | 30 | 35 |
| Project 6 | 12 | 6 | 6 |
| Project 7 | 14 | 48 | 4 |
| Project 8 | 10 | 36 | 3 |
| Project 9 | 12 | 18 | 3 |
- Ako ne možemo da preduzmemo deo projekta, ali moramo da preduzmemo ceo projekat ili nijedan projekat, kako možemo da maksimiziramo NPV?
- Pretpostavimo da ako se izvrši projekat 4, mora da se preduzme projekat 5. Kako možemo da uvećamo NPV?
Izdavačka kuća pokušava da utvrdi koju od 36 knjiga treba da objavi ove godine. Datoteka Pressdata.xlsx pruža sledeće informacije o svakoj knjizi:
- Projektovani prihodi i troškovi razvoja (u hiljadama dolara)
- Stranice u svakoj knjizi
- Da li je knjiga usmerena ka publici programera (označeno sa 1 u koloni E)
Izdavačka kuća može objaviti knjige u ukupnom iznosu do 8500 stranica ove godine i mora objaviti najmanje četiri knjige usmerene ka programerima softvera. Kako kompanija može maksimizirati svoj profit?
O članku
Ovaj članak je prilagođen biblioteci Vejna L. Vinstona ( Analiza podataka i poslovno modeliranje programa Microsoft Office Excel 2007) autora Vejna L. Vinstona.
Ova knjiga u stilu učionice razvijena je iz niza prezentacija Vejna Vinstona, poznatog statističara i profesora biznisa koji je specijalizovan za kreativne, praktične primene Ekcel-a.