Kako preduzeće može da koristi rešavač da bi odredio koje projekte treba da iskoristi?

Preduzeće kao što je Eli Lilly svake godine mora da odredi koje preduzeće treba da razvije; preduzeće kao što je Microsoft, koje softverske programe da razvije; a company like Proctor & Gamble, which new consumer products to develop. Funkcija "Rešavač" u Excel može da pomogne preduzeću u donošenje ovih odluka.

Većina korporacija želi da poduzme projekte koji doprinose najvećoj neto sadašnjoj vrednosti (NPV), podložni ograničenim resursima (obično velikim i velikim slovom). Recimo da neko preduzeće za razvoj softvera pokušava da utvrdi koji od 20 softverskih projekata treba da se shvate. NPV (u milionima dolara) doprinosi svakom projektu, kao i glavnom (u milionima dolara) i broj programera potrebnih za svaku od sledeće tri godine dat je na radnom listu Osnovni model u datoteci Capbudget.xlsx, koja je prikazana na stranici Slika 30-1 na sledećoj stranici. Na primer, Project 2 dodelja 908 miliona USD. To zahteva 151 milion USD tokom 1. godine, 269 miliona USD tokom 2. godine i 248 miliona USD tokom 3. godine. Project 2 zahteva 139 programera tokom 1. godine, 86 programera tokom 2. godine i 83 programera tokom 3. godine. Ćelije E4:G4 pokazuju glavni grad (u milionima dolara) dostupan tokom svake od tri godine, a ćelije H4:J4 ukazuju na to koliko programera je dostupno. Na primer, tokom 1. godine je dostupno do 2,5 miliona usd, a dostupno je 900 programera.

Preduzeće mora da odluči da li treba da se poduhvati svakog projekta. Recimo da ne možemo da preuzmemo deo softverskog projekta; 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 nešto radite ili ne uradite jeste da koristite binarne promenljive ćelije. Binarna promenljiva ćelija uvek iznosi 0 ili 1. Kada binarna promenljiva ćelija koja odgovara projektu iznosi 1, mi radimo na projektu. Ako je binarna ćelija koja odgovara projektu jednaka 0, ne treba da radimo na projektu. Programski dodatak "Rešavač" podesite tako da koristi opseg binarnih ćelija tako što ćete dodati ograničenje – izaberite promenljive ćelije koje želite da koristite, a zatim odaberite stavku Bin sa liste u dijalogu Dodavanje ograničenja.

Book image

Sa ovom pozadinom, spremni smo da rešimo problem sa izborom softverskog projekta. Kao i uvek sa modelom rešavača, počinjemo identifikovanjem ciljne ćelije, promenljive ćelije i ograničenja.

  • Ciljna ćelija. Uvećavamo NPV generisano izabranim projektima.

  • Promenljive ćelije. Potražimo 0 ili 1 binarnu promenljivu ćeliju za svaki projekat. Ove ćelije sam locirao u opsegu A6:A25 (pod imenom "doit opsega"). Na primer, 1 u ćeliji A6 označava da smo Project 1; a 0 u ćeliji C6 označava da nećemo preduzeti Project 1.

  • Ograničenja. Moramo da obezbedimo da za svaku godinu t (t=1, 2, 3 ), iskorišćena godina t glavnica bude manja ili jednaka godini t glavnici, a da je iskorišćeni t-godina dostupan za manje ili jednako godini t.

Kao što možete da vidite, naš radni list mora da se nadograđuje za bilo koji izbor projekata, NPV, glavni grad koji se koristi godišnje i programeri koji se koriste svake godine. U ćeliji B2 koristim formulu SUMPRODUCT(doit,NPV) da bih zbir NPV generisao izabrani projekt. (Ime opsega NPV odnosi se na opseg C6:C25.) Za svaki projekat sa 1 u koloni A ova formula pokupi NPV projekta, a za svaki projekat sa 0 u koloni A ova formula ne pokupi NPV projekta. Stoga možemo da proučimo NPV svih projekata, a ciljna ćelija je linearna zato što je ona se komprimuje sabiranjem termina koji slede obrazac (promenljive ćelije)*(konstanta). Na sličan način, procjenio sam glavni grad koji se koristi svake godine i primerak koji se koristi svake godine tako što kopiram iz E2 u F2:J2 formulu SUMPRODUCT(doit,E6:E25).

Sada popunjavam dijalog Parametri programskog sandučeta "Rešavač" kao što je prikazano u okviru "Slika 30-2".

Book image

Cilj nam je da uvećamo NPV izabranih projekata (ćelija B2). Naše promenljive ćelije (opseg koji se zove doit) jesu binarne promenljive ćelije za svaki projekat. Ograničenje E2:J2<=E4:J4 obezbeđuje da se glavni grad i iskorišćeni godišnji glavni grad i iskorišćeni godišnji da su manji od ili jednaki glavnom i dostupnom tilinu. Da biste dodali ograničenje koje čini binarne promenljive ćelije, 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 u okviru "Slika 30-3".

Book image

Naš model je linearan jer se ciljna ćelija broji kao zbir termina koji imaju obrazac (promena ćelije)*(konstanta) i zato što se ograničenja korišćenja resursa odvraćaju poređenjem zbira (promenljivih ćelija)*(konstanti) sa konstantom.

Kada se popuni dijalog Parametri programskog sandučeta Solver, kliknite na dugme Reši i imamo rezultate prikazane ranije u 30-1. Preduzeće može da dobije maksimalni NPV od 9.293 miliona USD (9,293 usd) biranjem projekta 2, 3, 6–10, 14–16, 19 i 20.

Ponekad modeli za izbor projekta imaju druga ograničenja. Na primer, pretpostavimo da ako izaberemo Project 3, takođe moramo da izaberemo Project 4. Pošto naše trenutno optimalno rešenje bira Project 3, a ne 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 promena ćelija za Project 3 manja od binarne promenljive ćelije Project 4.

Ovaj primer možete da pronađete na radnom listu Ako je 3, zatim 4, u datoteci Capbudget.xlsx, koja je prikazana u 30-4. Ćelija L9 upućuje na binarnu vrednost povezanu sa ćelijom Project 3, a ćelija L12 na binarnu vrednost povezanu sa Project 4. Dodavanjem ograničenja L9<=L12, ako izaberemo Project 3, L9 jednako 1, a naše ograničenje primorava L12 (Project 4 binarna) da bude jednaka 1. Naše ograničenje takođe mora da ostavi binarnu vrednost u promenljivoj ćelije Project 4 neograničene ako ne izaberemo Project 3. Ako ne izaberemo Project 3, L9 jednako 0, a naše ograničenje omogućava da Project 4 binarna bude jednaka 0 ili 1, što je ono što želimo. Novo optimalno rešenje prikazano je na slikama 30-4.

Book image

Izračunava se novo optimalno rešenje ako se izabere Project 3 znači da takođe moramo da izaberemo Project 4. Pretpostavimo da sada možemo da uradimo samo četiri projekta između 1. projekta od 1 do 10. (Pogledajte radni list Od najviše 4 od P1 do P10, prikazan na slikama 30-5.) U ćeliji L8 zbir binarnih vrednosti povezanih sa projektima od 1 do 10 ćemo sa formulom SUM(A6:A15). Zatim dodajemo ograničenje L8<=L10,što obezbeđuje da se, u većini razloga, izabere 4 od prvih 10 projekata. Novo optimalno rešenje prikazano je na slikama 30-5. NPV je zaokupalo do 9,014 USD više od sada.

Book image

Linearni modeli rešavača u kojima su neke ili sve promenljive ćelije neophodne za binarne ili ceo broj obično se teže rešavaju od linearnih modela u kojima je svim promenljivim ćelijama dozvoljeno da budu razlomci. Iz tog razloga često smo zadovoljni skoro optimalnim rešenjem za binarni ili problem sa programerima sa svim brojevima. Ako se model rešavača dugo pokreće, možda bi trebalo da razmotrite prilagođavanje postavke tolerancije u dijalogu Opcije rešavača. (Pogledajte Figure 30-6.) Na primer, postavka tolerancije od 0,5% znači da će rešavač zaustaviti kada prvi put pronađe izvodljivo rešenje koje je na 0,5 procenata od teoretičkog optimalne ciljne ćelije (optimalna vrednost ciljne ćelije je optimalna ciljna vrednost koja se nalazi kada se izostave binarna i celi broj ograničenja). Često smo suočeni sa izborom da pronađemo odgovor u roku od 10 procenata optimalnog za 10 minuta ili pronalaženja optimalnog rešenja za dve sedmice vremena računara! Podrazumevana vrednost tolerancije je 0,05%, što znači da se "Rešavač" zaustavlja kada pronađe vrednost ciljne ćelije na 0,05 procenata od teoretičke optimalne ciljne ćelije.

Book image

  1. 1. Preduzeće ima devet projekata koji se uzimaju u obzir. NPV koji dodaje svaki projekat i glavni grad koji je neophodan za svaki projekat u sledeće dve godine prikazuje se u sledećoj tabeli. (Svi brojevi su u milionima.) Na primer, Project 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 za projekate je dostupno 50 miliona USD, a tokom 2. godine je dostupno 20 miliona.

NPV

1. godina

2. godina

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 poduhvatmo deo projekta, ali moramo da ga iskoristimo na ceo projekat ili nijedan projekat, kako možemo da uvećamo NPV?

  • Pretpostavimo da Project 4 bude u toku, Project 5 mora biti u toku. Kako možemo da uvećamo NPV?

  • Preduzeće za objavljivanje pokušava da utvrdi koje od 36 knjiga treba da objavi ove godine. Datoteka u Pressdata.xlsx pruža sledeće informacije o svakoj knjizi:

    • Predviđeni prihod i troškovi razvoja (u hiljadama dolara)

    • Stranice u svakoj knjizi

    • Da li je knjiga zupčanik prema publici programera softvera (označeno brojem 1 u koloni E)

      Preduzeće za objavljivanje može da objavi knjige sa do 8500 stranica ove godine i mora da objavi najmanje četiri knjige opremene projektantima softvera. Kako preduzeće može da uveća profit?

Ovaj članak se prilagođava iz Microsoft kancelarija Excel analiza podataka i modelinga poslovanja iz 2007. godine od Veljka L. Winston.

Ovu knjigu u stilu učionice razvila je grupa prezentacija od Vene Winston, poznatog statističkog i poslovnog profesora koji je specijalizovao kreativne, praktične aplikacije Excel.

Potrebna vam je dodatna pomoć?

Unapredite veštine
Istražite obuku
Prvi nabavite nove funkcije
Pridružite se Microsoft insajdere

Da li su vam ove informacije koristile?

Koliko ste zadovoljni kvalitetom prevoda?

Šta je uticalo na vaše iskustvo?

Imate li dodatne povratne informacije? (Opcionalno)

Hvala vam na povratnim informacijama!

×