Kako lahko podjetje z Reševalcem določi, katere projekte naj se loti?
Vsako leto mora podjetje, kot je Eli Lilly, določiti, katera zdravila razviti; podjetje, kot je Microsoft, katere programske programe razvijati; podjetje, kot je Proctor & Gamble, katere nove potrošniške izdelke razviti. Funkcija reševalca v Excelu lahko pomaga podjetjem pri sprejemanju teh odločitev.
Kako lahko podjetje z Reševalcem določi, katere projekte naj se loti?
Večina korporacij želi izvajati projekte, ki prispevajo največjo neto sedanjo vrednost (NPV), ob upoštevanju omejenih virov (običajno kapitala in dela). Recimo, da podjetje za razvoj programske opreme poskuša določiti, katerega od 20 projektov programske opreme bi se moralo lotiti. NPV (v milijonih dolarjev), ki ga prispeva vsak projekt, kot tudi kapital (v milijonih dolarjev) in število programerjev, potrebnih v vsakem od naslednjih treh let, je navedeno na delovnem listu osnovnega modela v datoteki Capbudget.xlsx, ki je prikazan na sliki 30-1 na naslednji strani. Projekt 2 na primer prinese 908 milijonov EUR. Zahteva 151 milijonov dolarjev v 1. letu, 269 milijonov dolarjev v 2. letu in 248 milijonov dolarjev v 3. letu. Projekt 2 zahteva 139 programerjev v 1. letu, 86 programerjev v 2. letu in 83 programerjev v 3. letu. Celice E4:G4 prikazujejo kapital (v milijonih dolarjev), ki je na voljo v vsakem od treh let, celice H4:J4 pa število programerjev, ki so na voljo. Na primer, v 1. letu je na voljo do 2,5 milijarde dolarjev kapitala in 900 programerjev.
Podjetje se mora odločiti, ali bo izvedlo vsak projekt. Predpostavimo, da se ne moremo lotiti niti delčka projekta programske opreme; Če bi na primer dodelili 0,5 potrebnih virov, bi imeli nedelujoči program, ki bi nam prinesel 0 dolarjev prihodka!
Pri modeliranju situacij, v katerih nekaj naredite ali pa ne storite, je trik v uporabi binarnih celic, ki se spreminjajo. Binarna celica, ki se spreminja, je vedno enaka 0 ali 1. Če je binarna celica, ki se spreminja in ustreza projektu, enaka 1, izvedemo projekt. Če je binarna celica, ki se spreminja in ustreza projektu, enaka 0, projekta ne izvedemo. Reševalca nastavite tako, da uporablja obseg binarnih celic, ki se spreminjajo, tako, da dodate omejitev – izberite celice, ki se spreminjajo, jih želite uporabiti, in nato na seznamu v pogovornem oknu »Dodaj omejitev« izberite »Bin«.
Book 
S tem ozadjem smo pripravljeni rešiti problem izbire projekta programske opreme. Kot vedno pri modelu reševalca najprej določimo ciljno celico, celice, ki se spreminjajo, in omejitve.
- Ciljna celica. Maksimiramo NPV, ki ga ustvarijo izbrani projekti.
- Celice, ki se spreminjajo. Za vsak projekt poiščemo binarno celico, ki se spreminja v obliki 0 ali 1. Te celice sem našel v obsegu A6:A25 (in poimenoval obseg doit). Na primer, številka 1 v celici A6 označuje, da se lotimo projekta 1; 0 v celici C6 pomeni, da se ne lotimo Projekta 1.
- omejitve. Zagotoviti moramo, da je za vsako leto t (t = 1, 2, 3) leto t porabljenega kapitala manjši ali enak letu razpoložljivega kapitala t in da je leto t porabljenega dela manjše ali enako letu razpoložljive delovne sile.
Kot lahko vidite, mora naš delovni list za vsak izbor projektov izračunati NPV, kapital, uporabljen letno, in programerje, uporabljene vsako leto. V celici B2 uporabim formulo SUMPRODUCT(doit,NPV), da izračunam skupno NPV, ki je ustvarjen v izbranih projektih. (Ime obsega NPV se nanaša na obseg C6:C25.) Za vsak projekt z vrednostjo 1 v stolpcu A ta formula zazna NPV projekta, za vse projekte z vrednostjo 0 v stolpcu A pa ta formula ne zajame NPV projekta. Tako lahko izračunamo NPV vseh projektov, naša ciljna celica pa je linearna, saj je izračunana s seštevanjem členov, ki sledijo obliki (celica, ki se spreminja)*(konstanta). Na podoben način izračunam kapital, ki se porabi vsako leto, in delo, porabljeno vsako leto, tako da iz E2 v F2:J2 kopiram formulo SUMPRODUCT(doit,E6:E25).
Zdaj izpolnim pogovorno okno »Parametri reševalca«, kot je prikazano na sliki 30-2.
Book 
Naš cilj je maksimirati NPV izbranih projektov (celica B2). Naše spreminjajoče se celice (obseg z imenom doit) so binarne spreminjajoče se celice za vsak projekt. Omejitev E2:J2<=E4:J4 zagotavlja, da sta v vsakem letu porabljena kapital in delovna sila manjša ali enaka kapitalu in delu, ki sta na voljo. Če želim dodati omejitev, zaradi katere so celice, ki se spreminjajo, binarne, kliknem »Dodaj« v pogovornem oknu »Parametri reševalca« in nato na sredini pogovornega okna na seznamu izberem »Bin«. Pogovorno okno »Dodaj omejitev« bi moralo biti prikazano, kot je prikazano na sliki 30-3.
Book 
Naš model je linearen, ker je ciljna celica izračunana kot vsota členov z obliko (celica, ki se spreminja)*(konstanta) in ker so omejitve uporabe virov izračunane s primerjavo vsote (celice, ki se spreminjajo)*(konstante) s konstanto.
Ko izpolnite pogovorno okno »Parametri reševalca«, kliknite »Reši« in prikazali se bodo rezultati, prikazani na sliki 30-1. Podjetje lahko pridobi najvišjo NPV v višini 9,293 milijona dolarjev (9,293 milijarde dolarjev) z izbiro projektov 2, 3, 6–10, 14–16, 19 in 20.
Obravnavanje drugih omejitev
Včasih imajo modeli izbire projektov druge omejitve. Če na primer izberemo projekt 3, moramo izbrati tudi projekt 4. Ker naša trenutna optimalna rešitev izbere projekt 3, ne pa tudi projekta 4, vemo, da naša trenutna rešitev ne more ostati optimalna. Če želite odpraviti to težavo, preprosto dodajte omejitev, da je binarna celica, ki se spreminja za projekt 3, manjša ali enaka binarno spreminjajoči se celici za projekt 4.
Ta primer lahko najdete na delovnem listu Če 3, potem 4 v datoteki Capbudget.xlsx, ki je prikazan na sliki 30-4. Celica L9 se sklicuje na binarno vrednost, povezano s projektom 3, celica L12 pa na binarno vrednost, povezano s projektom 4. Če dodamo omejitev L9<=L12 in če izberemo projekt 3, je L9 enako 1 in naša omejitev prisili L12 (binarno vrednost projekta 4) na enako 1. Naša omejitev mora prav tako pustiti neomejeno binarno vrednost v spreminjajoči se celici Projekta 4, če ne izberemo Projekta 3. Če ne izberemo Projekta 3, je L9 enako 0 in naša omejitev omogoča, da je dvojiška vrednost Projekta 4 enaka 0 ali 1, kar hočemo. Nova optimalna rešitev je prikazana na sliki 30-4.
Book 
Nova optimalna rešitev je izračunana, če izbira projekta 3 pomeni, da moramo izbrati tudi projekt 4. Recimo, da lahko naredimo le štiri projekte spośród projektov od 1 do 10. (Glejte delovni list največ 4 od P1–P10 , prikazan na sliki 30-5.) V celici L8 izračunamo vsoto binarnih vrednosti, povezanih s projekti od 1 do 10, s formulo SUM(A6:A15). Nato dodamo omejitev L8<=L10, ki zagotovi, da so izbrani največ 4 od prvih 10 projektov. Nova optimalna rešitev je prikazana na sliki 30-5. NPV se je zmanjšala na 9,014 milijarde dolarjev.
Reševanje problemov binarnega in celoštevilskega programiranja
Modele linearnega reševalca, v katerih morajo biti nekatere ali vse celice, ki se spreminjajo, binarne ali celo število, je običajno težje rešiti kot linearne modele, v katerih so vse celice, ki se spreminjajo, dovoljene ulomki. Zato smo pogosto zadovoljni s skoraj optimalno rešitvijo problema binarnega ali celega števila. Če se vaš model reševalca izvaja dalj časa, boste morda želeli prilagoditi nastavitev tolerance v pogovornem oknu »Možnosti reševalca«. (Glejte sliko 30-6.) Na primer, nastavitev tolerance na 0,5 % pomeni, da se bo reševalec ustavil, ko bo prvič našel izvedljivo rešitev, ki je znotraj 0,5 odstotka od teoretične optimalne vrednosti ciljne celice (teoretična optimalna vrednost ciljne celice je najdena optimalna ciljna vrednost, ki je najdena, ko izpustite binarne omejitve in omejitve celega števila). Pogosto se soočamo z izbiro med iskanjem odgovora v 10 odstotkih od optimalnega v 10 minutah ali iskanjem optimalne rešitve v dveh tednih računalniškega časa! Privzeta vrednost tolerance je 0,05 %, kar pomeni, da se reševalec ustavi, ko najde vrednost ciljne celice znotraj 0,05 odstotkov od teoretične optimalne vrednosti ciljne celice.
Težave
- Podjetje obravnava devet projektov. V spodnji tabeli sta prikazana NPV, dodana za vsak posamezen projekt, in kapital, ki ga vsak projekt zahteva v naslednjih dveh letih. (Vse številke so v milijonih.) Projekt 1 bo na primer dodal 14 milijonov USD neto sedanja vrednost in zahteval izdatke v višini 12 milijonov EUR v 1. letu in 3 milijone USD v 2. letu. V 1. letu je za projekte na voljo 50 milijonov USD kapitala, med 2. letom pa 20 milijonov USD.
| NPV | Izdatki v 1. letu | Izdatki v 2. letu | |
|---|---|---|---|
| Project 1 | 14 | 12 | 3 |
| Project 2 | 17 | 54 | 7 |
| Project 3 | 17 | 6 | 6 |
| Project 4 | 15 | 6 | 2 |
| Projekt 5 | 40 | 30 | 35 |
| Project 6 | 12 | 6 | 6 |
| Project 7 | 14 | 48 | 4 |
| Project 8 | 10 | 36 | 3 |
| Projekt 9 | 12 | 18 | 3 |
- Če se ne moremo lotiti dela projekta, ampak se moramo lotiti celotnega ali nobenega projekta, kako lahko maksimiziramo NPV?
- Če se izvaja projekt 4, je treba na primer izvesti projekt 5. Kako lahko maksimiziramo NPV?
Založba poskuša določiti, katero od 36 knjig naj objavi letos. Datoteka Pressdata.xlsx vsebuje te informacije o vsaki knjigi:
- Predvideni prihodki in razvojni stroški (v tisočih dolarjih)
- Strani v vsaki knjigi
- Ali je knjiga namenjena občinstvu razvijalcev programske opreme (označeno z 1 v stolpcu E)
Založba lahko letos objavi knjige s skupno 8500 stranmi in mora objaviti vsaj štiri knjige, namenjene razvijalcem programske opreme. Kako lahko podjetje poveča svoj dobiček?
O članku
Ta članek je bil povzetek iz knjige Wayne L. Winston , Analiza podatkov in poslovno modeliranje programa Microsoft Office Excel 2007 .
To učno knjigo je razvil iz serije predstavitev Wayna Winstona, znanega statistika in poslovnega profesorja, ki je specializiran za ustvarjalne in praktične aplikacije Excela.