Korišćenje programskog dodatka "Rešavač" za budžetiranje velikog slova

Većina korporacija želi da izvrši projekte koji doprinose najvećoj neto sadašnjoj vrednosti (NPV), koji podležu ograničenim resursima (obično kapitalu i radu). Recimo da preduzeće za razvoj softvera pokušava da utvrdi koji od 20 softverskih projekata treba da se shvate. NPV (u milionima dolara) doprineo je svakom projektu, kao i glavnom gradu (u milionima dolara), a broj programera koji su potrebni tokom svake od sledeće tri godine dat je na radnom listu Osnovni model u datoteci Capbudget.xlsx, koja je prikazana na slici 30-1 na sledećoj stranici. Na primer, Project 2 daje 908 miliona USD. To zahteva 151 milion dolara tokom 1. godine, 269 miliona tokom 2. godine i 248 miliona tokom 3. godine. Projekat 2 zahteva 139 programera tokom 1. godine, 86 programera tokom 2. godine i 83 programere tokom 3. godine. Ćelije E4:G4 prikazuju glavni grad (u milionima dolara) dostupnim tokom svake od tri godine, a ćelije H4:J4 ukazuju na to koliko programera je dostupno. Na primer, tokom 1. godine do 2,5 milijarde dolara kapitala i dostupno je 900 programera.

Preduzeće mora da odluči da li treba da izvrši svaki projekat. Pretpostavimo da ne možemo da izvršimo deo softverskih projekata; ako dodelimo 0,5 potrebnih resursa, na primer, imamo neradni program koji će nam doneti prihod od 0 USD!

Trik u situacijama modelovanja u kojima radite ili ne radite nešto jeste korišćenje binarnih promenljivih ćelija. Binarna promenljivih ćelija uvek iznosi 0 ili 1. Kada binarna promenljivi ćelija koja odgovara projektu iznosi 1, mi radimo projekat. Ako binarna promenljivi ćelija koja odgovara projektu jednaka 0, ne radimo projekat. Programski dodatak "Rešavač" možete podesiti tako da koristi opseg binarnih promenljivih ćelija tako što ćete dodati ograničenje – izaberite promenljivih ćelija koje želite da koristite, a zatim odaberite stavku Bin sa liste u dijalogu Dodavanje ograničenja.

Sa ovom pozadinom smo spremni da rešimo problem sa izborom softverskog projekta. Kao i uvek uz model programskog dodatka "Rešavač", počinjemo identifikovanjem ciljne ćelije, promenljivih ćelija i ograničenja.

• Ciljna ćelija. Uvećavamo funkciju NPV koju generišu izabrani projekti.

• Menjanje ćelija. Potražimo 0 ili 1 binarnu promennu ćeliju za svaki projekat. Ove ćelije sam locirao u opsegu A6:A25 (i nazvan tačkom opsega). Na primer, 1 u ćeliji A6 ukazuje na to da preduzimamo Project 1; a 0 u ćeliji C6 ukazuje na to da ne preduzimamo Project 1.

• Ograničenja. Moramo da osiguramo da je za svaku godinu t (t=1, 2, 3), godina t kapitala koja se koristi manja ili jednaka godišnjem t kapitalu dostupna, a godina t koji se koristi manja ili jednaka godina t dostupan rad.

Kao što vidite, naš radni list mora da računa za svaki izbor projekata koje NPV, glavni grad koristi godišnje, kao i za programere koji se koriste svake godine. U ćeliji B2 koristim formulu SUMPRODUCT(doit,NPV) za izračunavanje ukupne NPV funkcije koju generišu izabrani projekti. (Ime opsega NPV upućuje na opseg C6:C25.) Za svaki projekat sa kolonom A broj 1, ova formula pokupi NPV projekta, a za svaki projekat sa oznakom 0 u koloni A ova formula ne pokupi NPV projekta. Stoga možemo da izračunamo NPV svih projekata, a ciljna ćelija je linearna jer se izračunava sabiranjem termina koji slede obrazac (promenljiva ćelija )*(konstanta). Na sličan način računam glavni grad koji se koristi svake godine i rad koji se koristi svake godine tako što kopiram iz E2 u F2:J2 formulu SUMPRODUCT(doit,E6:E25).

Sada popunjavam dijalog Parametri programskog dodatka "Rešavač" kao što je prikazano na slici 30-2.

Naš cilj je da uvećamo funkciju NPV izabranih projekata (ćelija B2). Naše promenlјene ćelije (opseg koji se zove tačka) su binarne promenljivih ćelija za svaki projekat. Ograničenje E2:J2<=E4:J4 obezbeđuje da tokom svake godine iskorišćeni glavni grad i radna snaga imaju manje ili jednako glavni grad i radnu snagu. Da biste dodali ograničenje koje binarne promene ćelija, kliknite na dugme Dodaj u dijalogu Parametri programskog dodatka "Rešavač", a zatim sa liste u sredini dijaloga izaberite stavku Bin. Dijalog Dodavanje ograničenja bi trebalo da se pojavi kao što je prikazano na slici 30-3.

Model je linearan zato što se ciljna ćelija izračunava kao zbir termina koji imaju obrazac (promenljivi ćelija)*( konstanta) i zato što se ograničenja upotrebe resursa izračunava poređenjem zbira (promenljivih ćelija )*(konstanti) sa konstantom.

Kada popunite dijalog Parametri programskog dodatka Solver, kliknite na dugme Reši i rezultati su prikazani ranije u slikama 30-1. Preduzeće može da dobije maksimalnih 9.293 miliona USD (9,293 milijarde USD) izborom stavki Projekti 2, 3, 6–10, 14–16, 19 i 20.

Ponekad modeli za izbor projekta imaju druga ograničenja. Na primer, pretpostavimo da ako izaberemo Projekat 3, moramo da izaberemo i Projekat 4. Pošto naše trenutno optimalno rešenje bira Project 3, ali ne i Project 4, znamo da trenutno rešenje ne može ostati optimalno. Da biste rešili ovaj problem, jednostavno dodajte ograničenje da je binarna promenna ćelija za Project 3 manja ili jednaka binarnim promenljivim ćelijama za Project 4.

Ovaj primer možete da pronađete na radnom listu Ako 3 onda 4 u Capbudget.xlsx, koja je prikazana u slici 30-4. Ćelija L9 upućuje na binarnu vrednost povezanu sa projektom 3, a ćeliju L12 na binarnu vrednost povezanu sa projektom 4. Ako dodamo ograničenje L9<=L12, ako odaberemo Project 3, L9 jednako 1, a naše snage ograničenja L12 (binarni projekat 4) da binarne vrednosti 1. Naše ograničenje takođe mora da ostavi binarnu vrednost u promenlјivoj ćeliji projekta 4 neograničeno ako ne izaberemo Project 3. Ako ne izaberemo Project 3, L9 jednako 0, a naše ograničenje omogućava binarnom programu Project 4 da bude jednak 0 ili 1, što je ono što mi želimo. Novo optimalno rešenje prikazano je u slici 30-4.

Novo optimalno rešenje se izračunava ako izaberete Project 3 znači da moramo da izaberemo i Project 4. Pretpostavimo da možemo da uradimo samo četiri projekta od 1. do 10. projekta. (Pogledajte radni list Najviše 4 od P1–P10 , prikazan na slici 30-5.) U ćeliji L8 računamo zbir binarnih vrednosti povezanih sa projektima od 1 do 10 pomoću formule SUM(A6:A15). Zatim dodajemo ograničenje L8<=L10, što osigurava da je najviše 4 od prvih 10 projekata izabrano. Novo optimalno rešenje prikazano je u slici 30-5. NPV je pao na 9,014 milijardi dolara.

Linearni modeli programskog dodatka "Rešavač" u kojima su neke ili sve promenljivih ćelija obavezni da budu binarni ili ceo broj obično se teže rešavaju od linearnih modela u kojima je svim promenljivim ćelijama dozvoljeno da budu razlomci. Iz ovog razloga često smo zadovoljni blizu optimalnim rešenjem za binarni ili celobrojni programski problem. Ako se solver model dugo izvršava, možda bi trebalo da razmotrite podeš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 izvedljivo rešenje koje se nalazi na 0,5 procenata od teoretske optimalne ciljne vrednosti ćelije (teoretska optimalna ciljna vrednost je optimalna ciljna vrednost koja se nalazi kada se izostavi binarna i celobrojna ograničenja). Često se suočimo sa izborom između pronalaženja odgovora u roku od 10 procenata optimalnog za 10 minuta ili pronalaženja optimalnog rešenja za dve nedelje vremena računara! Podrazumevana vrednost tolerancije je 0,05%, što znači da se "Rešavač" zaustavlja kada pronađe vrednost ciljne ćelije u okviru 0,05 procenata od teoretske optimalne ciljne ćelije.

1. Kompanija ima devet projekata koje razmatramo. NPV dodat po svakom projektu i glavni grad koji je neophodan za svaki projekat u naredne dve godine prikazan je u sledećoj tabeli. (Svi brojevi su u milionima.) Na primer, Project 1 će dodati 14 miliona USD u NPV i zahtevati rashode od 12 miliona dolara tokom 1. godine i 3 miliona tokom 2. godine. Tokom 1. godine, 50 miliona dolara kapitala dostupno je za projekte, a tokom 2. godine dostupno je 20 miliona.

	NPV	1. godina rashoda	Rashod za 2. godinu
Project 1	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 izvršimo deo projekta, ali moramo da izvršimo ceo ili nijedan projekat, kako možemo da uvećamo funkciju NPV?

• Recimo da ako se Projekat 4 izvrši, projekat 5 mora da se izvrši. Kako da uvećamo funkciju NPV?

• Kompanija za objavljivanje pokušava da utvrdi koja od 36 knjiga bi trebalo da bude objavljena ove godine. Datoteka Pressdata.xlsx pruža sledeće informacije o svakoj knjizi:

  • Projektovani troškovi prihoda i razvoja (u hiljadama dolara)

  • Stranice u svakoj knjizi

  • Da li je knjiga prilagođena publici programera softvera (na šta ukazuje 1 u koloni E)

    Kompanija za objavljivanje može da objavljuje knjige sa ukupno do 8500 stranica ove godine i mora da objavi najmanje četiri knjige prilagođene programerima softvera. Kako preduzeće može da uveća profit?

Ovaj članak je prilagođen programu Microsoft Office Excel 2007 Data Analysis and Business Modeling by Wayne L. Winston.

Ovu knjigu u učionici je razvio Vejn Vinston, poznati statistički i poslovni profesor koji je specijalizovao kreativne, praktične aplikacije programa Excel.