Hogyan határozhatja meg egy cég a Solverrel, hogy mely projekteket hajtsa végre?
Minden évben egy olyan cégnek, mint az Eli Lilly, meg kell határoznia, hogy mely gyógyszereket kell kifejlesztenie; egy olyan cég, mint a Microsoft, mely szoftverprogramokat kell fejleszteni; egy olyan cég, mint a Proctor & Gamble, amelyek új fogyasztói termékeket fejlesztenek. Az Excel Solver funkciója segíthet a vállalatoknak e döntések meghozatalában.
Hogyan határozhatja meg egy cég a Solverrel, hogy mely projekteket hajtsa végre?
A legtöbb vállalat olyan projekteket akar végrehajtani, amelyek a legnagyobb nettó jelenértékkel (NPV) járulnak hozzá, korlátozott erőforrások (általában tőke és munkaerő) mellett. Tegyük fel, hogy egy szoftverfejlesztő cég megpróbálja meghatározni, hogy a 20 szoftverprojekt közül melyiket végezze el. Az egyes projektek által biztosított nettó jelenérték (millió dollárban), valamint a tőke (millió dollárban) és a következő három évben szükséges programozók száma az alapmodell munkalapon van megadva a Capbudget.xlsx fájlban, amelyet a következő oldal 30-1. ábrája mutat. A Project 2 például 908 millió dollár bevételt hoz. 151 millió dollárra van szükség az 1. évben, 269 millió dollárra a 2. évben és 248 millió dollárra a 3. évben. A 2. projekthez 139 programozóra van szükség az 1. évben, 86 programozóra a 2. és 83 programozóra a 3. évben. Az E4:G4 cellák a három év során rendelkezésre álló tőkét mutatják (millió dollárban), a H4:J4 cellák pedig a rendelkezésre álló programozók számát. Például az 1. évben akár 2,5 milliárd dollár tőke és 900 programozó áll rendelkezésre.
A vállalatnak el kell döntenie, hogy vállalja-e az egyes projekteket. Tegyük fel, hogy egy szoftverprojekt töredékét nem tudjuk vállalni; Ha például a szükséges erőforrások 0,5-ét különítjük el, akkor egy nem működő programunk lenne, amely 0 dolláros bevételt hozna!
Az olyan helyzetek modellezésekor, amikor valamit vagy teszünk, vagy nem teszünk valamit, a bináris változó cellák használata a trükk. 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 végrehajtjuk a projektet. Ha egy projektnek megfelelő bináris változócella értéke 0, akkor nem hajtjuk végre a projektet. A Solver bináris változó cellák tartományának használatára úgy állítható be, hogy feltétel hozzáadásával – jelölje ki a használni kívánt változó cellákat, majd válassza a Bin elemet a Korlátozó feltétel hozzáadása párbeszédpanelen megjelenő listából.
Ennek hátterében készen állunk a szoftverprojektek kiválasztásának problémájának megoldására. Mint mindig a Solver-modellek esetében, itt is a célcella, a változó cellák és a megkötések azonosításával kezdjük.
- Célcella. A kiválasztott projektek által generált nettó jelenértéket maximalizáljuk.
- Változó cellák. Minden projekthez 0 vagy 1 bináris változó cellát keresünk. Ezeket a cellákat az A6:A25 tartományban találtam meg (és a doit nevet adtam a tartománynak). Például az A6 cellában az 1 érték azt jelzi, hogy az 1. projektet hajtjuk végre; a C6 cellában lévő 0 érték azt jelzi, hogy nem vállaljuk az 1. projektet.
- Kényszerek. Biztosítanunk kell, hogy minden t év esetében (t=1, 2, 3) a felhasznált t év tőkéje kevesebb vagy egyenlő legyen, mint a t év rendelkezésre álló tőkéje, és a t év felhasznált munkaerője kevesebb vagy egyenlő, mint a rendelkezésre álló munkaerő.
Mint látható, munkalapunknak ki kell számítania a projektek bármely kiválasztott részéhez az NMÉ-t, az évente felhasznált tőkét és az évente használt programozókat. A B2 cellában a SZORZATÖSSZEG(doit;NMÉ) képlettel számítom ki a kijelölt projektek által generált teljes nettó mai értéket. (A tartománynév NPV a C6:C25 tartományra utal.) Ez a képlet minden olyan projektnél, amelynek A oszlopában 1 érték található, a képlet kiszámítja a projekt nettó jelenértékét, míg minden olyan projektnél, ahol az A oszlopban 0 érték található, ez a képlet nem veszi fel a projekt nettó jelenértékét. Így minden projekt esetében ki tudjuk számítani az NMÉ-t, és a célcella lineáris, mivel az alakot követő ( változó cella)*(állandó)) kifejezések összegzésével történik. Hasonló módon számítom ki az évente felhasznált tőkét és az évente felhasznált munkát úgy, hogy E2-ből F2:J2-be másolom a SZORZATÖ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.
Célunk a kijelölt projektek nettó jelenértékének maximalizálása (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 biztosítja, hogy minden évben a felhasznált tőke és munkaerő kevesebb vagy egyenlő legyen a rendelkezésre álló tőkével és munkaerővel. A változó cellákat binárissá tevő kényszer hozzáadásához a Solver paraméterek párbeszédpanelén a Hozzáadás gombra kattintok, majd a párbeszédpanel közepén lévő listában a Bin elemet választom. A Kényszer hozzáadása párbeszédpanelnek a 30-3. ábrán látható módon kell megjelennie.
Modellünk lineáris, mivel a célcella a ( változó cella)*(állandó) formátumú tagok összegeként történik, és mivel az erőforrás-használati korlátok kiszámítása a (változó cellák)*(állandók) összegének egy állandóval való összehasonlításával történik.
Miután kitöltötte a Solver paraméterei párbeszédpanelt, kattintson a Megoldás gombra. Ekkor megjelennek a 30-1. ábrán látható eredmények. A vállalat legfeljebb 9,293 millió dolláros (9,293 milliárd dollár) nettó jelenértéket érhet el a 2., 3., 6–10., 14–16., 19. és 20. projekt választásával.
Egyéb kényszerek kezelése
A projektkiválasztási modelleknek időnként más korlátai is lehetnek. Tegyük fel például, hogy ha a 3. projektet választjuk, akkor a 4. projektet is ki kell választanunk. Mivel a jelenlegi optimális megoldásunk a 3. projektet választja, a 4-et nem, tudjuk, hogy a jelenlegi megoldásunk nem maradhat optimális. A probléma megoldásához egyszerűen vegye fel azt a korlátozást, hogy a 3. projekt bináris változócellája kisebb vagy egyenlő a 4. projekt bináris változócellájánál.
Ezt a példát a fájl Capbudget.xlsx "Ha 3, akkor 4 " munkalapján találja, amely a 30-4. ábrán látható. Az L9 cella a 3. projekthez tartozó bináris értékre, az L12 cella pedig a 4. projekthez tartozó bináris értékre hivatkozik. Ha az L9<=L12 korlátot hozzáadjuk, ha a 3. projektet választjuk, L9 egyenlő 1-gyel, a korlát pedig 1-et kényszerít L12-t (a 4. projekt binárisát) 1-re. Ha nem a 3. projektet választjuk, akkor a korlátnak a 4. projekt változó cellájában lévő bináris értéket is korlátozás nélkül kell hagynia. Ha nem választjuk ki a 3. projektet, L9 egyenlő 0-val, és a korlátunk megengedi, hogy a 4. projekt bináris fájlja 0 vagy 1 legyen, amit akarunk. Az új optimális megoldást a 30-4. ábra mutatja.
Az új optimális megoldás kiszámítása abban az esetben történik, ha a 3. projekt kiválasztása azt jelenti, hogy a 4. projektet is ki kell választani. Most tegyük fel, hogy az 1–10. projektek közül csak négy projektet tudunk végrehajtani. (Lásd a P1–P10 munkalap legfeljebb 4 . számát, a 30-5. ábrán látható.) Az L8 cellában a SZUM(A6:A15) képlettel kiszámítjuk az 1–10. projektekhez tartozó bináris értékek összegét. Ezután hozzáadjuk az L8<=L10 korlátot, amely biztosítja, hogy az első 10 projektből legfeljebb 4 legyen kiválasztva. Az új optimális megoldást a 30-5. ábra mutatja. Az NMÉ 9,014 milliárd dollárra csökkent.
Bináris és egész számok programozási problémáinak megoldása
Azok a lineáris Megoldó modellek, amelyekben egy vagy az összes változó cellának binárisnak vagy egész számnak kell lennie, általában nehezebben megoldhatók, mint a lineáris modellek, amelyekben minden változó cella lehet tört. Emiatt gyakran megelégszünk egy bináris vagy egész szám programozási probléma közel optimális megoldásával. Ha a Solver modell hosszú ideig fut, érdemes lehet módosítani a Tolerancia beállítást a Solver beállításai párbeszédpanelen. (Lásd a 30-6. ábrát.) A 0,5%-os Tolerancia beállítás például azt jelenti, hogy a Solver leáll, amikor az első alkalommal olyan megvalósítható megoldást talál, amely az elméleti optimális célcellaérték 0,5%-án belül van (az elméleti optimális célcellaérték az az optimális célérték, amelyet a bináris és egész korlátok kihagyása esetén találunk). Gyakran azzal kell szembesülnünk, hogy választunk az optimális 10 százalékán belül 10 perc alatt, vagy két hét számítógépes idő alatt találjuk meg az optimális megoldást! Az alapértelmezett toleranciaérték 0,05%, ami azt jelenti, hogy a Solver leáll, ha az elméleti optimális célcellaértékhez képest 0,05%-on belül lévő célcellaértéket talál.
Problémák
- Egy vállalatnak kilenc projektje van mérlegelésben. Az egyes projektek által hozzáadott nettó jelenérték és az egyes projektek által a következő két évben igényelt tőke az alábbi táblázatban látható. (Minden szám millió.) Például az 1. projekt nettó jelenértéke 14 millió dollárral nő meg, és az 1. évben 12 millió dolláros, a második évben pedig 3 millió dolláros kiadást igényel. Az 1. évben 50 millió dollár tőke áll rendelkezésre a projektekre, és 20 millió dollár a 2. évben.
| NMÉ | 1. évi kiadások | 2. év kiadásai | |
|---|---|---|---|
| Project 1 | 14 | 12 | 3 |
| Project 2 | 17 | 54 | 7 |
| Project 3 | 17 | 6 | 6 |
| 4. projekt | 15 | 6 | 2 |
| Project 5 | 40 | 30 | 35 |
| 6. projekt | 12 | 6 | 6 |
| Project 7 | 14 | 48 | 4 |
| Project 8 | 10 | 36 | 3 |
| Project 9 | 12 | 18 | 3 |
- Ha egy projekt töredékét nem tudjuk vállalni, de egy projekt egészét vagy egyiket sem, hogyan maximalizálhatjuk az NMÉ-t?
- Tegyük fel, hogy a 4. projekt megvalósításához az 5. projektnek is végre kell hajtania. Hogyan maximalizálhatjuk az NMÉ-t?
Egy kiadó megpróbálja meghatározni, hogy a 36 könyv közül melyiket jelenítse meg idén. A fájl Pressdata.xlsx a következő információkat tartalmazza az egyes könyvekről:
- Várható bevétel és fejlesztési költségek (ezer dollárban)
- Oldalak az egyes könyvekben
- Hogy a könyv szoftverfejlesztőkből álló közönségnek szól-e (ezt az E oszlopban 1-es jelöli)?
Egy kiadó idén összesen 8500 oldalas könyveket adhat ki, és legalább négy szoftverfejlesztőknek szóló könyvet kell kiadnia. Hogyan maximalizálhatja a vállalat a profitját?
A cikk névjegye
Ez a cikk Wayne L. Winston Microsoft Office Excel 2007 Data Analysis and Business Modeling (Adatelemzés és üzleti modellezés) című cikke alapján készült.
Ez a tanteremszerű könyv Wayne Winston, az Excel kreatív, gyakorlati alkalmazásaira szakosodott statisztikus és üzleti professzor előadássorozata alapján készült.