Hogyan állapíthatja meg egy cég a Solver segítségével, hogy mely projekteket kell elvégeznie?

Minden évben egy cégnek, például Lillynek, meg kell határoznia, hogy melyik céget fejleszti; egy vállalatot, például a Microsoftot, amely fejleszthet szoftverprogramokat; cég, például Pro az &, amely új fejleszthet fogyasztói termékeket tartalmaz. A vállalati Solver Excel segíthet a vállalatnak e döntések meghozatalában.

A legtöbb nagyvállalat olyan projekteket kíván elvégezni, amelyek a legnagyobb nettó jelenértéket (NMÉ) szeretnék kivállalni, a korlátozott erőforrásoknak (általában tőke- és munkaigényeknek) is kitéve. Tegyük fel, hogy egy szoftverfejlesztő cég megpróbálja eldönteni, hogy a 20 szoftverprojektből melyiket kell elvégeznie. Az NMÉ (több millió forintban) hozzájárult az egyes projektek és a főváros (több millió forint) által hozzájárult ahhoz, hogy a következő három év során hány programozóra van szükség az Capbudget.xlsx fájl Egyszerű modell munkalapján, amely a következő oldalon a 30–1. ábrán látható. Például 2 Project 908 millió forintot. Ehhez 151 millió dollár szükséges az 1. évben, 269 millió dollár a 2. év során, és 248 millió dollár a 3. év során. Project 2., 139 programozóra van szükség az 1. év során, 86 programozóra a 2. évben, és 83 programozóra a 3. évben. Az E4:G4 cellák a három év alatt elérhető fővárost (több millió forintban), a H4:J4 cellák pedig azt jelzik, hogy hány programozó áll rendelkezésre. Például az 1. év során legfeljebb 2,5 usdnyi tőketőke és 900 programozó áll rendelkezésre.

A vállalatnak el kell döntenie, hogy az egyes projekteket kell-e elvégeznie. Tegyük fel, hogy a szoftverprojektek töredékét nem lehet elvégezni; ha kiosztunk 0,5-öt a szükséges erőforrásokból, például egy nem munkamentes program lenne, amely 0 USD bevételt eredményezne!

Az olyan helyzetek modellezésében, ahol vagy nem, az a bináris változó cellák használata. A bináris változó cella értéke mindig 0 vagy 1. Ha egy projektnek megfelelő bináris változó cella értéke 1, akkor a projekt a következő lesz. Ha egy projektnek megfelelő bináris változó cella egyenlő 0-nak, akkor nem a projektről van szó. Beállíthatja, hogy a Solver bináris változócellák tartományát használja egy megkötés megkötésével – jelölje ki a használni kívánt változó cellákat, majd válassza a Bin lehetőséget a Kényszer hozzáadása párbeszédpanel listájából.

Book image

Ebben a háttérben készen áll a szoftverprojekt-kijelölési probléma megoldására. Mint minden más Solver-modell esetében, most is a célcella, a változó cellák és a kényszerek azonosításával kezdjük.

  • Célcella. Teljes méretűre növeljük a kijelölt projektek által generált NMÉ-t.

  • Cellák módosítása Minden projekthez 0 vagy 1 bináris változó cellát keresünk. Ezek a cellák az A6:A25 tartományban találhatók (és a tartomány neve doit). Az A6 cellában lévő 1 érték például azt jelzi, hogy 1 Project vállalunk; a C6 cellában a 0 érték azt jelzi, hogy nem 1-esre Project vállalunk.

  • Kényszerek. Meg kell győződnünk arról, hogy minden t év (t=1, 2, 3), a Felhasznált év t tőke értéke nem kisebb, mint év t capital available, és a "T" év munka, amely nem kisebb, mint az Év t munka.

Amint láthatja, munkalapunknak ki kell számítania a projektek kiválasztását az NMÉ, az éves tőke és az egyes évben használt programozók közül. A B2 cellában a SZUMÖSSZEG (doit,NMÉ) képletet használom a kijelölt projektek által generált teljes NMÉ kiszámítására. (Az NMÉ tartománynév a C6:C25 tartományra utal.) Minden olyan projekt esetében, amely az A oszlopban 1-et tartalmaz, ez a képlet felveszi a projekt NMÉ-jét, és minden olyan projekthez, amely az A oszlopban 0-val rendelkezik, a képlet nem számítja ki a projekt NMÉ-jét. Ezért minden projekt NMÉ-ját ki tudjuk számítani, és a célcella lineáris, mivel az űrlapot követő kifejezések összegzésével (változó cella)*(állandó)) számítja ki. Hasonlóképpen, kiszámolom az évente használt tőke- és az egyes évben használt munkadíjat úgy, hogy az E2-ről az F2:J2-re másolom a SZUMÖSSZEG(doit;E6:E25) képletet.

Most kitöltöm a Solver paraméterei párbeszédpanelt a 30–2. ábrán látható módon.

Book image

Célunk, hogy maximalizáljuk a kijelölt projektek nettó jelenértékét (B2 cella). A változó cellák (a Doit nevű tartomány) az egyes projektek bináris változó cellái. Az E2:J2<=E4:J4 kényszer gondoskodik arról, hogy minden évben a használt tőke- és munkamunkák ne kisebbek, mint a rendelkezésre álló tőke- és munkamunkák. A változó cellák bináris megjelenítésére szolgáló kényszer hozzáadásához a Solver paraméterei párbeszédpanelEn kattintson a Hozzáadás gombra, majd a párbeszédpanel közepén lévő listában válassza a Rekesz lehetőséget. A Kényszer hozzáadása párbeszédpanelnek a 30–3. ábra szerint kell megjelennie.

Book image

A modell lineáris, mivel a célcella azon kifejezések összegeként számítható ki, amelyek formát tartalmaznak (változó cella)*(állandó), és mivel az erőforrás-használati korlátozások kiszámítása a (változó cellák )*(állandók) összegének összehasonlításával történt.

Ha ki van töltve a Solver paraméterei párbeszédpanel, kattintson a Megoldás gombra, és megjelennek az eredmények a 30–1. ábrán. A vállalat a 2-es, 3-as, 6–10-es, 14–16-os, 19-es és 20-as projektre választva kaphat maximális, 9293 millió USD értéket.

A projektkijelölési modelleknek néha más korlátai is vannak. Tegyük fel például, hogy ha a 3. Project, akkor a 4. Project is ki kell választania. Mivel a jelenlegi optimális megoldásunk a 3 Project 3-as, de a 4-es Project nem, tudjuk, hogy a jelenlegi megoldás nem maradhat optimális. A probléma megoldásához egyszerűen adja hozzá azt a megkötést, hogy a Project 3 bináris változó cellája kisebb vagy egyenlő a 4 bináris változó Project értéknél.

Ez a példa a Ha 3, majd 4 munkalapon található Capbudget.xlsx, amely a 30–4. ábrán látható. Az L9 cella a 3- Project L12-hez kapcsolódó bináris értékre, az L12 cellára pedig a 4-es számhoz tartozó bináris Project hivatkozik. Az L9<=L12 megkötés hozzáadásával, ha a Project 3,L9 egyenlő 1, és az L12 (az Project 4 bináris) kényszer értéke 1 lesz. Megkötésünknek azt is meg kell hagynia, hogy a bináris érték a 4 változó cellában Project, ha nem a 3. értéket Project ki. Ha nem a 3. Project, az L9 értéke 0 lesz, és a megkötésünk lehetővé teszi, hogy a Project 4 bináris értéke 0 vagy 1 lesz, amit mi szeretnénk. Az új optimális megoldás a 30–4. ábrán látható.

Book image

Egy új optimális megoldás kiszámítása akkor történik meg, ha a Project 3 érték kiválasztásával azt jelenti, hogy a 4-es Project is ki kell választanunk. Most tegyük fel, hogy csak négy projektre van lehetőség az 1–10. projektek között. (Lásd a P1–P10 munkalap 4., a 30–5. ábrán látható módon.) Az L8 cellában az 1–10. projektekhez társított bináris értékek összegét a SZUM(A6:A15) képlettel számítjuk ki. Ezután hozzáadjuk az L8<=L10, amely biztosítja, hogy az első 10 projektből legalább 4 ki van jelölve. Az új optimális megoldás a 30–5. ábrán látható. Az NMÉ 9,014 usd-re csökkent.

Book image

Azok a lineáris Solver-modellek, amelyekben néhány vagy az összes változó cellának binárisnak vagy egésznek kell lennie, általában nehezebben oldható meg, mint az olyan lineáris modellek, amelyekben minden változó cella lehet tört. Ennek érdekében gyakran vagyunk elégedettek a bináris vagy egész programozási probléma szinte optimális megoldásával. Ha a Solver modell hosszú ideig fut, érdemes lehet módosítani a Tűrés beállítást a Solver beállításai párbeszédpanelen. (Lásd: 30–6. ábra.) A 0,5%-os tűréshatár például azt jelenti, hogy a Solver az első olyan megoldás megtaláláskor leáll, amely az optimális optimalizálás célcellás értékének 0,5%-on belül van (a optimalizálás szerinti optimális célcella értéke az optimális célérték, amely akkor található, ha a bináris és egész korlát elhagyható). Gyakran előfordul, hogy választási lehetőség áll előttünk, hogy 10 perc alatt az optimális válasz 10%-án belül, vagy két hét múlva optimális megoldást találjunk a számítógép előtt! Az alapértelmezett Tűrés érték 0,05%, ami azt jelenti, hogy a Solver leáll, ha a célcella értékének 0,05%-on belülre van beállítva az optimális célcella értékének 0,05%-on belül.

Book image

  1. Egy vállalatnak kilenc projektje van. Az egyes projektekhez hozzáadott NMÉ és az egyes projektekhez a következő két év során szükséges tőkeérték az alábbi táblázatban látható. (Minden szám milliókban van.) Például 1 Project 14 millió USD nettó jelenértékhez fog hozzá adni, és 12 millió USD kiadást igényel az 1. évben, és 3 millió USD-t a 2. év során. Az 1. év során 50 millió USD tőke áll rendelkezésre a projektekhez, és 20 millió Forint érhető el a 2. évben.

NMÉ

1. év kiadás

2. év kiadás

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

  • Ha nem tudjuk elvégezni egy projekt töredékét, de a projekt egészét vagy egészét végre kell vállalnunk, hogyan maximalizálható az NMÉ?

  • Tegyük fel, Project 4-esre van kötelezettséget vállalva, Project 5-öt kell elvégezni. Hogyan maximalizálható az NMÉ?

  • Egy közzétételi vállalat azt próbálja megállapítani, hogy az idén 36 könyvből melyiket kell közzétennie. A Pressdata.xlsx az alábbi információkat adja meg az egyes könyvekkel kapcsolatban:

    • Tervezett bevétel és fejlesztési költségek (több ezer forintban)

    • Pages in each book

    • A könyv a szoftverfejlesztők közönsége felé irányul-e (ezt az E oszlopban az 1-es érték jelzi)

      Egy közzétételi vállalat akár 8500 lapot is képes közzétenni ebben az évben, és legalább négy, szoftverfejlesztőknek készült könyvet kell közzétennie. Hogyan maximalizálhatja a vállalat a nyereséget?

Ez a cikk a 2007 Microsoft Office Excel-es Data Analysis and Business Modeling (A 2007-es adatelemzés és üzleti modellezés) szövegből jött ki.

Ezt az osztálytermi stílusú könyvet a 2010-es Éva, egy jól ismert statisztikus és üzleti tanár fejleszti, és kreatív, gyakorlati alkalmazásokra szakosítja a Excel.

További segítségre van szüksége?

Ismeretek bővítése
Oktatóanyagok megismerése
Új szolgáltatások listájának lekérése
Csatlakozás Microsoft Office Insiderekhez

Hasznos volt az információ?

Mennyire elégedett a fordítás minőségével?
Mi befolyásolta a felhasználói élményét?

Köszönjük a visszajelzését!

×