Kako lahko podjetje uporabi Solver, da določi, katere projekte naj izvede?
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, katerega nove potrošniške izdelke je treba razviti. Funkcija reševalca v Excelu lahko podjetju pomaga pri sprejemanju teh odločitev.
Kako lahko podjetje uporabi Solver, da določi, katere projekte naj izvede?
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 ugotoviti, katerega od 20 projektov programske opreme bi moralo izvesti. NPV (v milijonih dolarjev), ki jih prispeva vsak projekt, kot tudi kapital (v milijonih dolarjev) in število programerjev, potrebnih v vsakem od naslednjih treh let, so navedeni na delovnem listu Osnovni model v datoteki Capbudget.xlsx, ki je prikazan na sliki 30-1 na naslednji strani. Na primer, projekt 2 prinaša 908 milijonov dolarjev. 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. letniku. Celice E4:G4 prikazujejo kapital (v milijonih dolarjev), ki je na voljo v vsakem od treh let, celice H4:J4 pa kažejo, koliko programerjev je 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č program, ki bi nam prinesel 0 dolarjev prihodkov!
Trik pri modeliranju situacij, v katerih nekaj naredite ali ne storite, je uporaba binarnih spreminjajočih se celic. Binarna spreminjajoča se celica je vedno enaka 0 ali 1. Ko je binarna spreminjajoča se celica, ki ustreza projektu, enaka 1, naredimo projekt. Če je binarna spreminjajoča se celica, ki ustreza projektu, enaka 0, projekta ne naredimo. Reševalec nastavite tako, da uporablja obseg dvojiških spreminjajočih se celic tako, da dodate omejitev – izberite spreminjajoče se celice, ki jih želite uporabiti, in nato na seznamu v pogovornem oknu Dodaj omejitev izberite Koš.
S tem ozadjem smo pripravljeni rešiti težavo pri izbiri projektov programske opreme. Kot vedno pri modelu reševalca začnemo z identifikacijo ciljne celice, spreminjajočih se celic in omejitev.
- Ciljna celica. Maksimiramo neto sedanjo vrednost, ki jo ustvarjajo izbrani projekti.
- Spreminjanje celic. Za vsak projekt iščemo binarno spreminjajočo se celico 0 ali 1. Te celice sem našel v območju A6: A25 (in poimenoval obseg doit). Na primer, 1 v celici A6 pomeni, 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 uporabljenega kapitala manjše ali enako letu t razpoložljivega kapitala in leto t uporabljene delovne sile je manjše ali enako letu t razpoložljive delovne sile.
Kot lahko vidite, mora naš delovni list za vsak izbor projektov izračunati NPV, kapital, ki se porabi letno, in programerje, ki se uporabljajo vsako leto. V celici B2 uporabljam formulo SUMPRODUCT(doit,NPV) za izračun skupnega NPV, ki ga ustvarijo izbrani projekti. (Ime obsega NPV se nanaša na območje C6:C25.) Za vsak projekt z 1 v stolpcu A ta formula prevzame NPV projekta, za vsak projekt z 0 v stolpcu A pa ta formula ne prevzame neto vrednosti projekta. Zato lahko izračunamo NPV vseh projektov in naša ciljna celica je linearna, ker se izračuna s seštevanjem členov, ki sledijo obliki (spreminjajoča se celica)*(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.
Naš cilj je maksimirati NPV izbranih projektov (celica B2). Naše spreminjajoče se celice (obseg imenovan doit) so binarne spreminjajoče se celice za vsak projekt. Omejitev E2:J2<=E4:J4 zagotavlja, da sta v vsakem letu uporabljeni kapital in delo manjša ali enaka razpoložljivemu kapitalu in delu. Če želite dodati omejitev, zaradi katere so spreminjajoče se celice binarne, v pogovornem oknu Parametri reševalca kliknem Dodaj in nato na seznamu na sredini pogovornega okna izberem Koš. Pogovorno okno Dodaj omejitev bi moralo biti prikazano, kot je prikazano na sliki 30-3.
Naš model je linearen, ker je ciljna celica izračunana kot vsota členov, ki imajo obliko (spreminjajoča se celica)*(konstanta) in ker se omejitve porabe virov izračunajo s primerjavo vsote (spreminjajočih se celic)*(konstant) s konstanto.
Ko je pogovorno okno Parametri reševalca izpolnjeno, kliknite Reši in imamo rezultate, ki so prikazani prej na sliki 30-1. Podjetje lahko pridobi najvišjo NPV v višini 9.293 milijonov dolarjev (9,293 milijarde dolarjev) z izbiro projektov 2, 3, 6–10, 14–16, 19 in 20.
Ravnanje z drugimi omejitvami
Včasih imajo modeli izbire projektov druge omejitve. Recimo, da če izberemo Project 3, moramo izbrati tudi Project 4. Ker naša trenutna optimalna rešitev izbere Project 3, ne pa Project 4, vemo, da naša trenutna rešitev ne more ostati optimalna. Če želite rešiti to težavo, preprosto dodajte omejitev, da je binarna spreminjajoča se celica za Projekt 3 manjša ali enaka binarni spreminjajoči se celici za Projekt 4.
Ta primer lahko najdete na delovnem listu Če 3, potem 4 v datoteki Capbudget.xlsx, ki je prikazana na sliki 30-4. Celica L9 se nanaša na dvojiško vrednost, povezano s projektom 3, celica L12 pa na dvojiško vrednost, povezano s projektom 4. Če dodamo omejitev L9<=L12, če izberemo Projekt 3, je L9 enako 1 in naša omejitev prisili L12 (dvojiški projekt 4), da je enak 1. Naša omejitev mora tudi pustiti dvojiško vrednost v spreminjajoči se celici projekta 4 neomejeno, če ne izberemo projekta 3. Če ne izberemo projekta 3, je L9 enako 0 in naša omejitev omogoča, da je dvojiška datoteka projekta 4 enaka 0 ali 1, kar želimo. Nova optimalna rešitev je prikazana na sliki 30-4.
Izračunamo novo optimalno rešitev, če izbira Projekta 3 pomeni, da moramo izbrati tudi Projekt 4. Zdaj pa predpostavimo, da lahko naredimo samo štiri projekte med projekti od 1 do 10. (Glej Največ 4 od P1-P10 delovni list, prikazan na sliki 30-5.) V celici L8 izračunamo vsoto dvojiških vrednosti, povezanih s projekti od 1 do 10, s formulo SUM(A6:A15). Nato dodamo omejitev L8<=L10, ki zagotavlja, da so izbrani največ 4 od prvih 10 projektov. Nova optimalna rešitev je prikazana na sliki 30-5. NPV je padel na 9,014 milijarde dolarjev.
Reševanje problemov binarnega in celoštevilskega programiranja
Linearne modele reševalca, v katerih morajo biti nekatere ali vse spreminjajoče se celice binarne ali celoštevilske, je običajno težje rešiti kot linearne modele, v katerih so vse spreminjajoče se celice dovoljene ulomki. Zaradi tega smo pogosto zadovoljni s skoraj optimalno rešitvijo problema binarnega ali celoštevilskega programiranja. Če se vaš model reševalca izvaja dlje časa, boste morda želeli prilagoditi nastavitev Toleranca v pogovornem oknu Možnosti reševalca. (Glej sliko 30-6.) Na primer, nastavitev tolerance 0,5% pomeni, da se bo reševalec ustavil, ko prvič najde izvedljivo rešitev, ki je znotraj 0,5 odstotka teoretične optimalne vrednosti ciljne celice (teoretična optimalna vrednost ciljne celice je optimalna ciljna vrednost, ki jo najdemo, ko so izpuščene binarne in celoštevilske omejitve). Pogosto se soočamo z izbiro med iskanjem odgovora v 10 odstotkih 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 v območju 0,05 odstotka teoretične optimalne vrednosti ciljne celice.
Težave
- Podjetje obravnava devet projektov. Neto sedanja vrednost, dodana za vsak projekt, in kapital, ki ga vsak projekt potrebuje v naslednjih dveh letih, je prikazana v naslednji tabeli. (Vse številke so v milijonih.) Na primer, projekt 1 bo dodal 14 milijonov dolarjev neto vrednosti in zahteval izdatke v višini 12 milijonov dolarjev v 1. letu in 3 milijone dolarjev v 2. letu. V 1. letu je za projekte na voljo 50 milijonov dolarjev kapitala, v 2. letu pa 20 milijonov dolarjev.
| NPV | Odhodki za 1. leto | Odhodki za 2. leto | |
|---|---|---|---|
| Projekt 1 | 14 | 12 | 3 |
| Projekt 2 | 17 | 54 | 7 |
| Projekt 3 | 17 | 6 | 6 |
| Projekt 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.