Az optimális termékmix meghatározása a Solver segítségével

Ez a cikk azt ismerteti, hogy hogyan használható a Solver nevű Microsoft Excel-bővítmény, amellyel lehetőségelemzésekhez határozható meg az optimális termékösszetétel.

Hogyan határozhatom meg a havi termékmixet, amely maximalizálja a jövedelmezőséget?

A vállalatoknak gyakran meg kell határozniuk az egyes termékek havi alapon előállítandó mennyiségét. A legegyszerűbb formájában a termékmix problémája magában foglalja azt, hogy hogyan lehet meghatározni az egyes termékek mennyiségét, amelyet egy hónap alatt elő kell állítani a profit maximalizálása érdekében. A termékmixnek általában meg kell felelnie a következő követelményeknek:

• A termékmix nem használhat a rendelkezésre állónál több erőforrást.
• Az egyes termékekre korlátozott a kereslet. Egy hónap alatt nem tudunk többet előállítani egy termékből, mint amennyit a kereslet diktál, mert a többlettermelés elpazarolódik (például egy romlandó gyógyszer).

Most pedig oldjuk meg a termékmix problémájának következő példáját. A probléma megoldását a 27-1. ábra Prodmix.xlsx fájlban találja.

Tegyük fel, hogy egy gyógyszergyárnál dolgozunk, amely hat különböző terméket gyárt az üzemében. Az egyes termékek előállításához munkaerőre és nyersanyagra van szükség. A 27-1. ábra 4. sora az egyes termékek egy fontjának előállításához szükséges munkaórákat mutatja, az 5. sor pedig az egyes termékek egy fontjának előállításához szükséges nyersanyag fontját. Például egy font 1. termék előállításához 6 óra munka és 3,2 font nyersanyag szükséges. Minden gyógyszer esetében a fontonkénti árat a 6. sorban, a fontonkénti egységköltséget a 7. sorban, a fontonkénti haszonhozzájárulást pedig a 9. sorban adjuk meg. A 2. termék például 11,00 forint fontonkénti áronként, 5,70 forintos egységköltséggel jár és 5,30 forint nyereséggel járul hozzá fontonként. Az egyes gyógyszerek iránti havi keresletet a 8. sorban adjuk meg. Például a 3. termék iránti kereslet 1041 font. Ebben a hónapban 4500 óra munka és 1600 font nyersanyag áll rendelkezésre. Hogyan maximalizálhatja ez a cég havi nyereségét?

Ha semmit sem tudnánk az Excel Solver alkalmazásról, a probléma ellen létrehoznánk egy munkalapot a termékmixhez kapcsolódó nyereség és erőforrás-felhasználás nyomon követésére. Ezután próba és hiba segítségével variálnánk a termékmixet a profit optimalizálása érdekében anélkül, hogy a rendelkezésre álló munkaerőt vagy nyersanyagot használnánk fel, és anélkül, hogy a keresletet meghaladó gyógyszert állítanánk elő. Ebben a folyamatban csak a próba és hiba fázisában használjuk a Solvert. A Solver lényegében egy optimalizáló motor, amely hibátlanul végzi a próba- és hibaalapú keresést.

A termékmix-probléma megoldásának egyik kulcsa az adott termékösszetételhez tartozó erőforrás-felhasználás és nyereség hatékony kiszámítása. A számítás elvégzéséhez fontos eszköz a SZORZATÖSSZEG függvény. A SZORZATÖSSZEG függvény összeszorozza a cellatartományok megfelelő értékeit, és kiszámítja ezen értékek összegét. A SZORZATÖSSZEG kiértékelése során használt cellatartományoknak azonos méretűnek kell lenniük, ami azt jelenti, hogy a SZORZATÖSSZEG függvény két sorral vagy két oszloppal használható, egy oszloppal és egy sorral nem.

Példaként arra, hogy hogyan használhatjuk a SZORZATÖSSZEG függvényt a termékmix-példánkban, próbáljuk meg kiszámítani az erőforrás-felhasználást. A munkaerő-felhasználás kiszámítása

(1. font gyógyszerre felhasznált munkaerő)*(1 kg előállított gyógyszer)+
(2. font gyógyszerre felhasznált munkaerő)*(2 font előállított gyógyszer) + ...
(6 font gyógyszerre felhasznált munkaerő)*(6 font előállított gyógyszer)

A munkaerő-felhasználást unalmasabb módon is kiszámíthatnánk, mint D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. Hasonlóképpen, a nyersanyagfelhasználás kiszámítható D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Ezeknek a képleteknek a munkalapra való beírása hat termék esetében időigényes lehet. Képzelje el, mennyi ideig tartana, ha egy olyan céggel dolgozna együtt, amely például 50 terméket gyárt az üzemében. A munkaerő és a nyersanyag-felhasználás kiszámításának jóval egyszerűbb módja, ha a D14 cellából a D15 cellába másolja a SZORZATÖSSZEG($D 2 $2:$I$2;D4:I4) képletet. Ez a képlet a D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 értéket számítja ki (ami a munkafelhasználásunk), de sokkal egyszerűbb beírni! Figyelje meg, hogy a dollárjelet a D2:I2 tartományban használom, hogy a képlet másolásakor továbbra is a 2. sor termékmixét rögzítsem. A D15 cellában lévő képlet kiszámítja a nyersanyag-felhasználást.

Hasonló módon a nyereségünket a

(Gyógyszer 1 nyereség fontonként)*(Gyógyszer 1 font előállított) +
(Gyógyszer 2 nyereség fontonként)*(Gyógyszer 2 font előállított) + ...
(Gyógyszer 6 nyereség fontonként)*(Gyógyszer 6 font előállított)

A nyereség egyszerűen kiszámítható a D12 cellában a SZORZATÖSSZEG(D9:I9;$D 2 Ft:$I 2 Ft) képlettel.

Most már azonosíthatjuk a termékmix Solver modelljének három összetevőjét.

• Célcella. Célunk a haszon maximalizálása (a D12 cellában számítva).

• Változó cellák. Az egyes termékekből (a D2:I2 cellatartományban felsorolt) termékek kitermelésének mennyisége (a D2:I2 cellatartományban felsorolva)

• Kényszerek. A következő korlátozások érvényesek a rendszerünkre:

  • Ne használjon több munkaerőt vagy nyersanyagot, mint amennyi rendelkezésre áll. Ez azt jelenti, hogy a D14:D15 cellák értékei (a felhasznált erőforrások) nem lehetnek nagyobbak, mint az F14:F15 cella értékei (a rendelkezésre álló erőforrások).
  • Ne termeljen több gyógyszert, mint amennyire szükség van. Ez azt jelenti, hogy a D2:I2 cellákban lévő értékeknek (az egyes gyógyszerekből kiállított fontoknak) kisebbnek vagy egyenlőnek kell lenniük az egyes gyógyszerek iránti keresletnél (a D8:I8 cellákban felsorolva).
  • Nem tudunk negatív mennyiségű gyógyszert előállítani.

Megmutatom, hogyan írhatja be a célcellát, hogyan módosíthatja a cellákat, és hogyan állíthatja be a korlátozásokat a Solverbe. Ezután csak annyit kell tennie, hogy a Megoldás gombra kattint, hogy megtalálja a profitmaximalizáló termékmixet!

A kezdéshez kattintson az Adatok fülre, majd az Elemzés csoportban a Solvor gombra.

Ekkor megjelenik a Solver paraméterek párbeszédpanelje, a 27-2. ábrán szemléltetett módon.

Kattintson a Célcella beállítása mezőre, majd válassza ki a profitcellánkat (D12 cella). Kattintson a Cellák módosításával mezőre, majd mutasson a D2:I2 tartományra, amely az egyes gyógyszerekből előállított fontokat tartalmazza. A párbeszédpanelnek most a 27-3. ábrán kell lennie.

Most már készen állunk arra, hogy korlátozásokat vegyünk fel a modellbe. Kattintson a Hozzáadás gombra. Megjelenik a 27-4. ábrán látható "Kényszer hozzáadása" párbeszédpanel.

Az erőforrás-használati korlátok hozzáadásához kattintson a Cellahivatkozás mezőre, majd jelölje ki a D14:D15 tartományt. Válassza az <= lehetőséget a középső listából. Kattintson a Kényszer mezőre, majd jelölje ki az F14:F15 cellatartományt. A Kényszer hozzáadása párbeszédpanel ekkor a 27-5. ábrához hasonlóan néz ki.

Mostantól gondoskodtunk arról, hogy amikor a Solver a változó cellákban más értékeket ad meg, csak olyan kombinációkat vegyen figyelembe, amelyek a D14<=F14 (a felhasznált munkaerő kevesebb vagy egyenlő, mint a rendelkezésre álló munkaerő) és a D15<=F15 (a felhasznált nyersanyag egyenlő a rendelkezésre álló nyersanyaggal) paramétereket egyaránt kielégítik. Kattintson a Hozzáadás gombra az igénykorlátozások megadásához. Töltse ki a Kényszer hozzáadása párbeszédpanelt a 27-6. ábrán látható módon.

E korlátozások felvételével biztosítható, hogy amikor a Solver különböző kombinációkat próbál használni a változó cellaértékekhez, csak az alábbi paramétereknek megfelelő kombinációkat veszi figyelembe a program:

• D2<=D8 (az 1. gyógyszer termelt mennyisége kisebb vagy egyenlő, mint az 1. gyógyszer iránti kereslet)
• E2<=E8 (a 2. gyógyszer előállításának mennyisége kisebb vagy egyenlő, mint a 2. gyógyszer iránti kereslet)
• F2<=F8 (a 3. gyógyszer előállítása kevesebb vagy egyenlő, mint a 3. gyógyszer iránti kereslet)
• G2<=G8 (a 4. előállított gyógyszer mennyisége kisebb vagy egyenlő, mint a 4. gyógyszer iránti kereslet)
• H2<=H8 (az előállított 5. gyógyszer termelt mennyisége kisebb vagy egyenlő, mint az 5. gyógyszer iránti kereslet)
• I2<=I8 (a 6. gyógyszer termelt mennyisége kisebb vagy egyenlő, mint a 6. gyógyszer iránti kereslet)

Kattintson az OK gombra a Kényszer hozzáadása párbeszédpanelen. A Solver ablakának a 27-7. ábrához hasonlóan kell kinéznie.

A Solver beállításai párbeszédpanelen beírjuk azt a korlátozást, hogy a változó celláknak nem lehetnek negatívak. A Solver paraméterei párbeszédpanelen kattintson a Beállítások gombra. Jelölje be a Lineáris modell feltételezése és a Nem negatív modell feltevése mezőt, ahogy a következő oldalon a 27-8. ábrán látható. Kattintson az OK gombra.

A Nem negatív érték feltételezése jelölőnégyzet bejelölésével biztosítható, hogy a Solver csak a változó cellák kombinációját vegye figyelembe, amelyekben minden változó cella nem negatív értéket feltételez. Bejelöltük a Lineáris modell feltételezése jelölőnégyzetet, mert a termékmix probléma a Solver-probléma egy speciális típusa, a lineáris modell. A Solver modell alapvetően lineáris a következő feltételek teljesülése esetén:

• A célcella kiszámítása az űrlap feltételeinek összeadásával történik (változó cella)*(állandó).
• Mindegyik kényszer megfelel a "lineáris modell" követelményének. Ez azt jelenti, hogy minden egyes korlátot úgy értékel ki, hogy összeadja az űrlap feltételeit (változó cella)*(állandó), és összehasonlítja az összegeket egy állandóval.

Miért lineáris ez a Solver-probléma? A célcella (nyereség) kiszámítása a következő:

(Gyógyszer 1 nyereség fontonként)*(Gyógyszer 1 font előállított) +
(Gyógyszer 2 nyereség fontonként)*(Gyógyszer 2 font előállított) + ...
(Gyógyszer 6 nyereség fontonként)*(Gyógyszer 6 font előállított)

Ez a számítás olyan mintát követ, amelyben a célcella értékét az űrlap ( változó cella)*(állandó)) kifejezések összeadásával határozzuk meg.

Munkaerő-kényszerünket úgy értékeljük, hogy összehasonlítjuk a (1. font gyógyszerre felhasznált munkaerő)*(Előállított gyógyszer 1 font) + (2. font gyógyszerre felhasznált munkaerő)*(2 font előállított gyógyszer)+ ... (6. font gyógyszerre felhasznált munkaerő)*(6 font előállított gyógyszer) a rendelkezésre álló munkaerőre.

Ezért a munkakényszer kiértékelése úgy történik, hogy összeadja az űrlap feltételeit (változó cella)*(állandó), majd összehasonlítja az összegeket egy állandóval. Mind a munkaerő-, mind a nyersanyagkényszer kielégíti a lineáris modell követelményét.

A keresletkorlátok a következő formát öltik

(1. előállított gyógyszer)<=(Kábítószer 1 igény)
(2. előállított gyógyszer)<=(2. kábítószer iránti kereslet)
§
(6. előállított gyógyszer)<=(Kábítószer 6 kereslet)

Az egyes igénykorlátok eleget tesznek a lineáris modell követelményeinek is, mivel mindegyik kiértékelése az űrlap feltételeinek összeadásával történik (változó cella)*(állandó), és az összegeket egy állandóval hasonlítja össze.

Miután bebizonyosodott, hogy a termékmix-modellünk lineáris modell, miért is érdekelne minket?

• Ha a Solver modell lineáris, és a Lineáris modell feltételezése lehetőséget választja, akkor a Solver garantáltan megtalálja az optimális megoldást a Solver modellhez. Ha a Solver modell nem lineáris, a Solver megtalálhatja az optimális megoldást.
• Ha a Solver modell lineáris, és a Lineáris modell feltételezése lehetőséget választja, a Solver egy nagyon hatékony algoritmus (egyoldalas módszer) segítségével keresi meg a modellhez optimális megoldást. Ha a Solver modell lineáris, és nem a Lineáris modell feltételezése lehetőséget választjuk, a Solver nagyon nem hatékony algoritmust (GRG2-módszert) használ, és nehézségekbe ütközhet a modellhez tartozó optimális megoldás megtalálása.

A Solver beállításai párbeszédpanelen az OK gombra kattintva visszatérünk a Solver 27-7. ábrán látható fő párbeszédpanelre. A Megoldás gombra kattintva a Solver kiszámítja a termékmix-modellünkhöz optimális megoldást (ha van ilyen). Amint azt a 26. fejezetben említettem, a termékkeverék modell optimális megoldása a változó sejtértékek (az egyes gyógyszerekből előállított fontok) halmaza lenne, amely maximalizálja a profitot az összes lehetséges megoldás halmazához képest. Szintén megvalósítható megoldás a változó cellaértékek csoportja, amely megfelel minden megkötésnek. A 27-9. ábrán látható változó cellaértékek megvalósítható megoldást jelentenek, mivel az összes termelési szint nem negatív, a termelési szintek nem haladják meg a keresletet, és az erőforrás-felhasználás sem haladja meg a rendelkezésre álló erőforrásokat.

A következő oldalon a 27-10. ábrán látható változó cellaértékek megvalósíthatatlan megoldást jelentenek a következő okok miatt:

• Többet termelünk az 5. gyógyszerből, mint amennyi igény van rá.
• Több munkaerőt használunk, mint amennyi rendelkezésre áll.
• Több nyersanyagot használunk, mint amennyi rendelkezésre áll.

A Megoldás gombra kattintva a Solver gyorsan megtalálja a 27-11. ábrán látható optimális megoldást. Ha meg szeretné őrizni a megoldás optimális értékeit a munkalapon, válassza a Solver megoldásának megtartása lehetőséget.

Gyógyszercégünk havi nyereségét 6,625,20 dolláros szinten maximalizálhatja 596,67 font 4. gyógyszer, 1084 font 5. gyógyszer és egyik másik gyógyszer előállításával sem! Nem tudjuk eldönteni, hogy más módon el tudjuk-e érni a maximális 6,625,20 dolláros nyereséget. Csak abban lehetünk biztosak, hogy korlátozott erőforrásainkkal és keresletünkkel ebben a hónapban nincs mód 6,627,20 dollárnál többet keresni.

A Solver-modellek mindig tartalmaznak megoldást?

Tegyük fel, hogy minden termék iránti keresletet ki kell elégíteni. (Lásd a fájl "Nem megvalósítható megoldás" munkalapját a Prodmix.xlsx.) Ezután a keresleti korlátokat D2:I2<=D8:I8-rólD2:I2>=D8:I8-ra kell módosítanunk. Ehhez nyissa meg a Solver alkalmazást, válassza a D2:I2<=D8:I8 korlátot, majd kattintson a Módosítás gombra. Megjelenik a 27-12. ábrán látható Kényszer módosítása párbeszédpanel.

Válassza a >= jelet, majd kattintson az OK gombra. Most már meggyőződtünk arról, hogy a Solver csak azokat a cellaértékeket módosítja, amelyek minden igényt kielégítenek. Amikor a Megoldás gombra kattint, megjelenik "A Solver nem talált megvalósítható megoldást" üzenet. Ez az üzenet nem azt jelenti, hogy hibát követtünk el a modellünkben, hanem azt, hogy korlátozott erőforrásaink miatt nem tudnánk minden termék iránti keresletet kielégíteni. A Solver egyszerűen azt mondja nekünk, hogy ha minden termék iránti keresletet ki akarunk elégíteni, több munkaerőt, több nyersanyagot vagy mindkettőt többet kell hozzáadnunk.

Mit jelent az, ha egy Solver-modell olyan eredményt ad, amelynek a halmazértékei nem konvergálnak?

Lássuk, mi történik, ha korlátlan keresletet engedünk meg minden termékre, és lehetővé tesszük, hogy minden gyógyszerből negatív mennyiséget állítsanak elő. (Ez a Solverrel kapcsolatos probléma a fájl Prodmix.xlsx Set Values Do Converge (Értékek beállítása nem konvergencia ) munkalapján látható.) Az optimális megoldás megtalálásához nyissa meg a Solver alkalmazást, kattintson a Beállítások gombra, és törölje a jelet a Nem negatív tételek feltételezése jelölőnégyzetből. A Solver paraméterei párbeszédpanelen jelölje ki a D2:I2<=D8:I8 igénykorlátozást, majd a Törlés gombra kattintva távolítsa el a korlátot. Amikor a Megoldás gombra kattint, a Solver a következőhöz hasonló üzenetet jeleníti meg: "A Cellaértékek beállítása nem konvergál". Ez az üzenet azt jelenti, hogy ha a célcellát teljes méretűre akarjuk állítani (mint a példában), akkor tetszőlegesen nagy célcella-értékkel is vannak megvalósítható megoldások. (Ha a célcellát kis méretűre szeretné állítani, a "Cellaérték beállítása ne konvergáljon" üzenet azt jelenti, hogy tetszőlegesen kis célcella-értékkel is vannak megvalósítható megoldások.) A mi helyzetünkben egy gyógyszer negatív termelésének megengedésével valójában "létrehozunk" erőforrásokat, amelyek önkényesen nagy mennyiségű más drog előállítására használhatók. Korlátlan keresletünk miatt ez lehetővé teszi számunkra, hogy korlátlan nyereséget érjünk el. Valós helyzetben nem tudunk végtelen mennyiségű pénzt keresni. Röviden: ha "Az értékbeállítás nem konvergál" üzenet jelenik meg, a modellben hiba található.

Problémák

1. Tegyük fel, hogy gyógyszergyárunk akár 500 órányi munkaerőt is vásárolhat, óránként 1 dollárral többért, mint a jelenlegi munkaerőköltség. Hogyan maximalizálhatjuk a profitot?

2. Egy forgácsgyártó üzemben négy technikus (A, B, C és D) három terméket gyárt (1., 2. és 3. termék). Ebben a hónapban a processzorlapkagyártó 80 darabban adhatja el az 1. terméket, 50 darabban a 2. terméket, és legfeljebb 50 darabban a 3. terméket. A technikus csak az 1. és a 3. terméket tudja előállítani. B technikus csak az 1. és 2. terméket tudja előállítani. A C technikus csak a 3. terméket készítheti. D technikus csak a 2. terméket készítheti. Minden egyes előállított egység után a termékek a következő nyereséghez járulnak hozzá: 1. termék, 6 dollár; 2. termék, 7 dollár; és a 3. termék, 10 dollár. Az egyes technikusoknak a termék gyártásához szükséges idő (órákban) a következő:

   Termék	A technikus	"B" technikus	C technikus	D technikus
   1	2	2,5	Nem lehet elvégezni	Nem lehet elvégezni
   2	Nem lehet elvégezni	3	Nem lehet elvégezni	3,5
   3	3	Nem lehet elvégezni	4	Nem lehet elvégezni

3. Minden technikus havonta legfeljebb 120 órát dolgozhat. Hogyan maximalizálhatja a chipgyártó havi nyereségét? Tételezzük fel, hogy az egységek egy része állítható elő.

4. Egy számítógépgyártó üzem egereket, billentyűzeteket és videojáték-botkormányokat gyárt. Az egységenkénti nyereséget, az egységnyi munkafelhasználást, a havi igényt és az egységnyi gépidő-felhasználást az alábbi táblázat tartalmazza:

   	Egerek	Billentyűzetek	Botkormányok
   Nyereség/egység	8 dollár	11 Ft	9 USD
   Munkaerő-felhasználás/egység	.2 óra	.3 óra	.24 óra
   Gép idő/erőforrás	.04 óra	.055 óra	.04 óra
   Havi igény	15 000	27,000	11,000

5. Havonta összesen 13 000 munkaóra és 3000 óra gépi idő áll rendelkezésre. Hogyan maximalizálhatja a gyártó az üzemből származó havi nyereséget?

6. Oldja meg a kábítószer-példánkat, feltételezve, hogy minden gyógyszer esetén legalább 200 egység igényt kell kielégíteni.

7. Jason gyémánt karkötőket, nyakláncokat és fülbevalókat készít. Legfeljebb 160 órát szeretne dolgozni havonta. 800 uncia gyémántja van. Az alábbiakban adjuk meg az egyes termékek előállításához szükséges nyereséget, munkaidőt és unciás gyémántokat. Ha az egyes termékek iránti kereslet korlátlan, hogyan maximalizálhatja Jason a nyereségét?

   Termék	Egységnyereség	Munkaórák száma egységenként	Uncia gyémánt egységenként
   Karkötő	30 000 Ft	.35	1.2
   Nyaklánc	20 000 Ft	.15	.75
   Fülbevalók	10 000 Ft	,05	.5