Kako tvrtka može pomoću alata za rješavanje odrediti koje projekte treba poduzeti?
Svake godine tvrtka poput Eli Lillyja mora odrediti koje lijekove razviti; tvrtka poput Microsofta, koje softverske programe razvijati; tvrtka kao što je Proctor & Gamble, koji će razviti nove potrošačke proizvode. Značajka alata za rješavanje u programu Excel može pomoći tvrtkama u donošenju takvih odluka.
Kako tvrtka može pomoću alata za rješavanje odrediti koje projekte treba poduzeti?
Većina korporacija želi poduzeti projekte koji doprinose najvećoj neto sadašnjoj vrijednosti (NPV), podložno ograničenim resursima (obično kapital i rad). Recimo da tvrtka za razvoj softvera pokušava odrediti koji bi od 20 softverskih projekata trebala poduzeti. NPV (u milijunima dolara) koji je doprinio svaki projekt, kao i kapital (u milijunima dolara) i broj programera potrebnih tijekom svake od sljedeće tri godine navedeni su na radnom listu Osnovnog modela u datoteci Capbudget.xlsx, koja je prikazana na slici 30-1 na sljedećoj stranici. Na primjer, Project 2 donosi 908 milijuna USD. Potreban je 151 milijun dolara tijekom 1. godine, 269 milijuna dolara tijekom druge godine i 248 milijuna dolara tijekom 3. godine. Projekt 2 zahtijeva 139 programera tijekom 1. godine, 86 programera tijekom druge godine i 83 programera tijekom 3. godine. Ćelije E4:G4 pokazuju kapital (u milijunima dolara) dostupan tijekom svake od tri godine, a ćelije H4:J4 upućuju na to koliko je programera dostupno. Na primjer, tijekom prve godine dostupno je do 2,5 milijardi dolara kapitala i 900 programera.
Tvrtka mora odlučiti treba li poduzeti svaki projekt. Pretpostavimo da ne možemo poduzeti ni djelić softverskog projekta; Ako dodijelimo 0,5 potrebnih resursa, na primjer, imali bismo neaktivni program koji bi nam donio 0 USD prihoda!
Trik u modeliranju situacija u kojima nešto morate učiniti ili ne učiniti jest korištenje binarnih mijenjajućih ćelija. Binarna ćelija s promjenom uvijek je jednaka 0 ili 1. Kada je binarna ćelija koja se mijenja i koja odgovara projektu jednaka 1, mi radimo projekt. Ako je binarna ćelija s promjenom koja odgovara projektu jednaka 0, projekt ne radimo. Solver ste postavili tako da koristi raspon binarnih ćelija s promjenom dodavanjem ograničenja – odaberite ćelije s promjenama koje želite koristiti, a zatim odaberite Bin s popisa u dijaloškom okviru Dodavanje ograničenja.
U tom smo kontekstu spremni riješiti problem odabira softverskog projekta. Kao i uvijek s modelom alata za rješavanje, počinjemo utvrđivanjem ciljne ćelije, ćelija koje se mijenjaju i ograničenja.
- Odredišna ćelija. Maksimiziramo NPV koji generiraju odabrani projekti.
- Ćelija koje se mijenjaju. Tražimo 0 ili 1 binarnu ćeliju s promjenom za svaki projekt. Te ćelije pronašao sam u rasponu A6:A25 (i rasponu nazvao doit). 1 u ćeliji A6, primjerice, označava da pokrećemo Project 1; 0 u ćeliji C6 označava da ne preuzimamo Project 1.
- Ograničenja. Moramo osigurati da za svaku godinu t (t=1, 2, 3) Godina t upotrijebljenog kapitala je manji ili jednak Godina t raspoloživog kapitala, a Godina t utrošenog rada je manja ili jednaka Godini t raspoloživog rada.
Kao što vidite, naš radni list mora za svaki odabir projekata računati NPV, kapital koji se godišnje koristi i programere koji se koriste svake godine. U ćeliji B2 koristim formulu SUMPRODUCT(doit;NPV) da bih izračunao ukupnu NPV koju generiraju odabrani projekti. (Naziv raspona NPV odnosi se na raspon C6:C25.) Za svaki projekt s brojem 1 u stupcu A ova formula preuzima NPV vrijednosti, a za svaki projekt s vrijednošću 0 u stupcu A ta formula ne preuzima NPV vrijednosti. Stoga možemo računati NPV vrijednosti svih projekata, a ciljna je ćelija linearna jer se izračunava zbrajanjem članova koji slijede oblik (promjenjiva ćelija)*(konstanta). Na sličan način računam kapital koji se koristi svake godine i rad koji se troši svake godine kopiranjem formule SUMPRODUCT(doit,E6:E25), iz E2 u F2:J2.
Sada ću ispuniti dijaloški okvir Parametri alata za rješavanje kao što je prikazano na slici 30-2.
Cilj nam je maksimizirati NPV odabranih projekata (ćelija B2). Naše promjenjive ćelije (raspon pod nazivom doit) binarne su promjenjive ćelije za svaki projekt. Ograničenje E2:J2<=E4:J4 osigurava da tijekom svake godine upotrijebljeni kapital i rad budu manji od ili jednak raspoloživom kapitalu i radnoj snazi. Da bih dodala ograničenje zbog kojeg su ćelije koje se mijenjaju binarne, kliknut ću Dodaj u dijaloškom okviru Parametri alata za rješavanje, a zatim na popisu u sredini dijaloškog okvira odabrati Bin. Dijaloški okvir Dodavanje ograničenja trebao bi izgledati kao na slici 30-3.
Model je linearan jer se ciljna ćelija izračunava kao zbroj članova koji imaju oblik (promjenjiva ćelija)*(konstanta) i zato što se ograničenja korištenja resursa izračunavaju usporedbom zbroja (promjenjivih ćelija)*(konstanti) s konstantom.
Popunite dijaloški okvir Parametri alata za rješavanje, kliknite Riješi i vidjet ćete rezultate prikazane ranije na slici 30-1. Tvrtka može dobiti maksimalnu NPV od 9,293 milijuna USD (9,293 milijarde USD) odabirom projekata 2, 3, 6-10, 14-16, 19 i 20.
Rukovanje ostalim ograničenjima
Ponekad modeli odabira projekta imaju druga ograničenja. Pretpostavimo da, primjerice, ako odaberemo Project 3, moramo odabrati i Project 4. Budući da naše trenutno optimalno rješenje odabire Project 3, ali ne i Project 4, znamo da naše trenutačno rješenje ne može ostati optimalno. Da biste riješili taj problem, jednostavno dodajte ograničenje prema kojem je binarna promjenjiva ćelija za Project 3 manja ili jednaka binarnoj promjenjivoj ćeliji za Project 4.
Ovaj primjer možete pronaći na radnom listu Ako 3 onda 4 u Capbudget.xlsx datoteka, što je prikazano na slikama 30-4. Ćelija L9 odnosi se na binarnu vrijednost vezanu uz Projekt 3, a ćelija L12 na binarnu vrijednost koja se odnosi na Projekt 4. Ako odaberemo ograničenje L9<=L12, odaberemo li Projekt 3, L9 je jednak 1, a naše ograničenje prisiljava L12 (binarni sustav Projekt 4) da bude jednak 1. Ako ne odaberemo Projekt 3, naše ograničenje mora ostaviti neograničenu binarnu vrijednost u promjenjivoj ćeliji Projekta 4. Ako ne odaberemo Projekt 3, L9 je jednak 0, a naše ograničenje omogućuje da binarni sustav Projekt 4 bude jednak 0 ili 1, a to je ono što želimo. Novo optimalno rješenje prikazano je na slici 30-4.
Novo optimalno rješenje izračunava se ako odabir Projekta 3 znači da moramo odabrati i Projekt 4. Pretpostavimo da od projekta od 1 do 10 možemo napraviti samo četiri projekta. (Pogledajte radni list Najviše 4 od P1 – P10 , prikazan na slikama 30-5.) U ćeliji L8 izračunavamo zbroj binarnih vrijednosti povezanih s projektima od 1 do 10 pomoću formule SUM(A6:A15). Zatim se dodaje ograničenje L8<=L10, što osigurava da su odabrana najviše 4 od prvih 10 projekata. Novo optimalno rješenje prikazano je na slici 30-5. NPV je pao na 9,014 milijardi dolara.
Rješavanje problema binarnog i cjelobrojnog programiranja
Modele linearnog rješavatelja u kojima neke ili sve ćelije koje se mijenjaju moraju biti binarne ili cjelobrojne obično je teže riješiti nego linearne modele u kojima sve ćelije koje se mijenjaju smiju biti razlomci. Iz tog razloga često smo zadovoljni gotovo optimalnim rješenjem problema binarnog ili cjelobrojnog programiranja. Ako se model alata za rješavanje koristi dulje vrijeme, razmislite o prilagodbi postavke tolerancije u dijaloškom okviru Mogućnosti alata za rješavanje. (Pogledajte sliku 30-6.) Postavka tolerancije od 0,5%, primjerice, znači da će se Rješavatelj zaustaviti kada prvi put pronađe izvedivo rješenje koje je unutar 0,5% od teoretske optimalne vrijednosti ciljne ćelije (teoretska optimalna ciljna vrijednost ćelije je optimalna ciljna vrijednost pronađena kada se binarna i cjelobrojna ograničenja izostavi). Često smo suočeni s izborom između pronalaženja odgovora unutar 10 posto optimalnog za 10 minuta ili pronalaženja optimalnog rješenja u dva tjedna računalnog vremena! Zadana vrijednost tolerancije je 0,05%, što znači da se Rješavatelj zaustavlja kada pronađe vrijednost ciljne ćelije unutar 0,05% od teoretske optimalne vrijednosti ciljne ćelije.
Problemi
- Tvrtka razmatra devet projekata. NPV dodana za svaki projekt i kapital potreban za svaki projekt tijekom sljedeće dvije godine prikazani su u sljedećoj tablici. (Svi brojevi su u milijunima.) U okviru projekta 1, primjerice, povećat će se 14 milijuna USD NPV-a i izdatci u iznosu od 12 milijuna USD tijekom prve godine te 3 milijuna USD tijekom druge godine. Tijekom 1. godine za projekte dostupno je 50 milijuna dolara kapitala, a tijekom druge godine 20 milijuna dolara.
| NPV (net present value, neto trenutna vrijednost) | Izdaci za 1. godinu | Rashodi 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 poduzeti djelić projekta, ali moramo poduzeti cijeli ili nijedan projekt, kako možemo maksimizirati NPV?
- Pretpostavimo da ako se provede Projekt 4, mora se pokrenuti Projekt 5. Kako maksimizirati NPV?
Izdavačka kuća pokušava odrediti koju bi od 36 knjiga trebala objaviti ove godine. Datoteka Pressdata.xlsx sadrži sljedeće podatke o svakoj knjizi:
- Predviđeni prihodi i troškovi razvoja (u tisućama dolara)
- Stranice u svakoj knjizi
- Je li knjiga namijenjena publici softverskih programera (označeno brojem 1 u stupcu E)
Izdavačka kuća ove godine može objaviti knjige do 8500 stranica i mora objaviti najmanje četiri knjige namijenjene programerima softvera. Kako tvrtka može maksimizirati svoju dobit?
O članku
Ovaj je članak prilagođen iz knjige Analiza podataka i poslovno modeliranje programa Microsoft Office Excel 2007 autora Waynea L. Winstona.
Ova knjiga u stilu učionice razvijena je iz niza prezentacija Waynea Winstona, poznatog statističara i profesora poslovanja koji se specijalizirao za kreativne, praktične primjene Excela.