Az optimális szorzat-kombináció meghatározása a Solver használatával

Hogyan tudom megállapítani a nyereségességet maximalizáló havi szorzatot?

A cégeknek gyakran kell meghatározniuk az egyes termékek havi rendszerességgel számított mennyiségét. A termék -összekeverési probléma a legegyszerűbben azzal jár, hogy miként állapítható meg, hogy az egyes termékek hány hónapig legyenek létrehozva a nyereség maximalizálása érdekében. A termékek keverékének általában az alábbi korlátozásokat kell betartania:

• A Product mix nem tud további erőforrásokat használni, mint amennyit elérhető.

• Az egyes termékekhez korlátozott kereslet szükséges. Nem lehet több terméket gyártani egy hónap alatt, mint amennyit a kereslet diktál, mert a többlet-termelés kárba vész (például egy romlandó drog).

Most már megoldja a következő példát a szorzat-összekeverési problémáról. A probléma megoldásához keresse meg a Prodmix. xlsx fájlt, amely a 27-1 ábrán látható.

Tegyük fel, hogy egy olyan gyógyszergyártó cég dolgozunk, amely hat különböző terméket gyárt a növénye számára. Az egyes termékek előÁllításához munkamennyiség és alapanyag szükséges. A 4-ös sor az 27-1 ábrán az egyes termékek egy kilójának elkészítéséhez szükséges órákat jeleníti meg, az 5-ös sor pedig az egyes termékek egy kilójának elkészítéséhez szükséges pénzanyag kilóit mutatja. Ha például egy 1 kilós terméket hoz létre, a szüléshez hat óra szükséges, és a 3,2 kiló alapanyag. Minden egyes gyógyszer esetében az ár/font a 6-os sorba kerül, a 7., a kilogrammonkénti egységár pedig a 9-es sorból van megadva. Például a 2. termék a $11,00-ban egy kilón keresztül értékesíti az-os egységárat, és a $5,70-as hozamot is támogatja. A havi keresletet minden egyes gyógyszer esetében a 8-as sor adja meg. A 3-as szorzat értéke például 1041 font. Ebben a hónapban 4500-munkaidőt és 1600 kiló alapanyag áll rendelkezésre. Hogyan tudja ez a cég maximalizálni a havi nyereséget?

Ha semmit sem tudott az Excel Solverrel kapcsolatban, akkor a probléma megoldásához egy munkalapot kell létrehoznia a szorzattal társított profit és erőforrás kihasználtságának nyomon követéséhez. Ezután próbaverzióval és hibával is elvégezheti, hogy a termékek keveréke a profit optimalizálása érdekében ne használjon több munkamennyiséget vagy nyersanyagot, mint amennyit elérhető, és ne használjon semmilyen, a keresletet meghaladó drogot. Ebben a folyamatban csak a próbaverzió és a hiba szakaszában használjuk a Solvert. A Solver egy olyan optimalizálási motor, amely hibátlanul végrehajtja a próba-és hiba-keresést.

A termékek összekeverése probléma megoldásának kulcsa az adott szorzat-összetételhez társított erőforrás-kihasználtság és haszon hatékony kiszámítása. A számítások elvégzéséhez használható fontos eszköz a SZORZATÖSSZEG függvény. A SZORZATÖSSZEG függvény összeszorozza a megfelelő értékeket a cellák tartományában, és az értékek összegét adja eredményül. A SZORZATÖSSZEG minden egyes kiértékeléséhez használt cellának azonos méretűnek kell lennie, ami azt jelenti, hogy a SZORZATÖSSZEG két sorból vagy két oszlopból is használhatja, de nem egy oszloppal és egy sorral.

Példaként, hogy hogyan tudjuk használni a SZORZATÖSSZEG függvényt a termékek összekeverése során, próbálkozzon az erőforrás-használat kiszámításával. A munkamennyiség kihasználtsága a következőképpen számítható ki:

(Munkamennyiség 1 kiló drog 1) * (1) * (gyógyszeres 1 font) +
(kitermeltmunka egy kilóval a drog 2) * (gyógyszer 2 font )
(Munkamennyiség 1 kiló a drog 6) * (a gyógyszer 6 kilót termelt)

Több unalmas módon kiszámítható a munkamennyiség, mint a D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * i4. Hasonlóképpen, a nyersanyag-használatot D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + * "H5 + I2 * i5" értékre lehet számítani. Ezek a képletek azonban hat termék munkalapjára való beírása időigényes. Képzelje el, hogy mennyi ideig fog tartani, ha egy olyan céggel (például 50-termékekkel) dolgozott, amely a gyárban van. A munkamennyiség és a nyersanyag-használat kiszámításának sokkal egyszerűbb módja, ha a D14-ról D15 a képlet SZORZATÖSSZEG ($D $2: $I $2, D4: i4). A képlet kiszámítja a D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * i4 (ez a munkamennyiség), de sokkal könnyebben beírható! Figyelje meg, hogy a $ jelet a D2: i2 tartománnyal használom, hogy a képlet másolásakor továbbra is rögzítem a terméket a 2-es sorból. A D15 cellában lévő képlet a nyersanyag-felhasználást számítja ki.

Hasonló módon a nyereséget a következőhöz hasonló módon határozzuk meg:

(Drug 1 profit/font) * (1 kilós kábítószer-előállítás) +
(Drug 2 profit per font) * (gyógyszer 2 font) +.. .
(6 gyógyszeres haszon) * (a gyógyszer 6 font )

A D12 a képlet SZORZATÖSSZEG (D9: i9, $D $2: $I $2)képlettel egyszerűen kiszámítható a profit.

Most már megadhatja a Product mix Solver modell három összetevőjét.

• Célcella.Célunk az, hogy maximalizálja a profitot (a cella D12 kiszámítva).

• Cellák módosításaAz egyes termékekből előállított fontok száma (a D2: i2 cellatartomány listájában)

• Korlátozásokkal. Az alábbi feltételekkel rendelkezünk:

  • Ne használjon több munkamennyiséget vagy nyersanyagot, mint amennyit elérhető. Azaz a cellák D14: D15 (a felhasznált erőforrások) értéke nem lehet kisebb, mint a cellák f14: F15 (a rendelkezésre álló erőforrások).

  • Ne készítsen több gyógyszert, mint amennyi a keresletben van. Vagyis a D2: i2 cellákban szereplő értékek (az egyes gyógyszerekből előállított font) értéke nem lehet kisebb, mint az egyes gyógyszerek iránti kereslet (a következő cellákban D8: i8).

  • Nem lehet negatív összeget kitermelni bármely drogból.

Bemutatjuk, hogy miként adhatók meg a célcella, módosíthatja a cellákat és a kényszereket a Solverben. Ezután mindössze annyit kell tennie, hogy a megoldás gombra kattintva megkeresi a nyereség maximalizálása terméket.

A kezdéshez kattintson az adatlapra, és az elemzés csoportban kattintson a Solver gombra.

Ekkor megjelenik a Solver paraméterei párbeszédpanel, az 27-2 ábra szerint.

Jelölje be a célcella jelölőnégyzetet, majd válassza ki a profit (cella D12) cellát. Jelölje be a cellák módosításával jelölőnégyzetet, majd mutasson a D2: i2 értékre, amely az egyes drogok által termelt kilókat tartalmazza. Ekkor a párbeszédpanelnek meg kell néznie az 27-3 ábrát.

Most már készen áll arra, hogy kényszereket vegyen fel a modellbe. Kattintson a Hozzáadás gombra. Megjelenik a kényszer hozzáadása párbeszédpanel a 27-4 ábrán látható módon.

Az erőforrás-használati kényszerek hozzáadásához kattintson a cellahivatkozást tartalmazó mezőre, majd válassza ki a tartomány D14: D15. Válassza a < = elemet a középső listából. Kattintson a kényszer mezőbe, majd jelölje ki a f14: F15. A kényszer hozzáadása párbeszédpanel ekkor úgy néz ki, mint a 27-5 ábra.

Most már biztos lehet benne, hogy amikor a Solver a változó cellák különböző értékeit kísérli meg használni, csak a D14< = f14 (az alkalmazott munkamennyiség kisebb vagy egyenlő) és a D15< = F15 (a felhasznált nyersanyag kisebb vagy egyenlő). a rendelkezésre álló nyersanyagok is megtekinthetők. Kattintson a Hozzáadás gombra az igény korlátainak megadásához. Töltse ki a kényszer hozzáadása párbeszédpanelt az 27-6 ábra szerint.

Ezekkel a korlátozásokkal biztosíthatja, hogy a Solver a változó cellák értékeinek eltérő kombinációját adja meg, csak az alábbi paramétereket teljesítő kombinációk lesznek figyelembe véve:

• D2< = D8 (a kábítószer 1-es verzióban termelt mennyiség kisebb vagy egyenlő a kábítószer 1)

• E2< = E8 (a drog 2 előállításának összege kisebb vagy egyenlő, mint a drog 2)

• F2< = F8 (a gyógyszer 3 termékből előállított mennyiség kisebb vagy egyenlő a kábítószer-3 iránti igénnyel)

• G2< = G8 (a drog 4 termékhez gyártott mennyiség kisebb vagy egyenlő a kábítószer-4-előfizetéssel)

• H2< = H8 (a drog 5 termékből előállított mennyiség kisebb vagy egyenlő a kábítószer 5 igényével)

• I2< = i8 (a gyógyszer 6 termékből előállított mennyiség kisebb, mint a kábítószer-6 kereslete)

Kattintson a kényszer hozzáadása párbeszédpanel OK gombjára. A Solver ablaka az 27-7 ábra szerint fog kinézni.

Azt a kényszert adja meg, hogy a cellák módosítása nem negatív a Solver beállításai párbeszédpanelen. A Solver paraméterei párbeszédpanelen kattintson a beállítások gombra. Jelölje be a lineáris modell feltételezése jelölőnégyzetet, és a nem negatív jelölőnégyzetet, amint az 27-8 ábra a következő oldalon látható. Kattintson az OK gombra.

A nem negatív megjelenítés jelölőnégyzet bejelölésével biztosíthatja, hogy a Solver csak a változó cellák kombinációit veszi figyelembe, amelyben az egyes változó cellák nem negatív értéket feltételeznek. Bejelöltük a lineáris modell jelölőnégyzetet, mert a termék-összekeverési probléma a Solver speciális típusa, amelyet lineáris modellneknevezünk. A Solver-modellek az alábbi feltételek szerint lineárisak:

• A célcella értékét úgy számítja ki a program, hogy összeadja az űrlap feltételeit (változó cella) * (állandó).

• Minden korlátozás megfelel a "lineáris modell követelményének". Ez azt jelenti, hogy minden korlátozás kiértékelésének eredménye az űrlap (változó cella) * (állandó) és az összegek állandóval való összehasonlítása.

Miért lineáris ez a Solver probléma? A célcella (haszon) kiszámítása a következő képlet szerint történik

(Drug 1 profit/font) * (1 kilós kábítószer-előállítás) +
(Drug 2 profit per font) * (gyógyszer 2 font) +.. .
(6 gyógyszeres haszon) * (a gyógyszer 6 font )

Ez a számítás a célcella értékének egy olyan mintáját követi, amely az űrlap (változó cella) * (állandó)feltételeit foglalja össze.

A munkamennyiség-megkötés kiértékelése az eredményül kapott (a gyógyszer 1 kilón alapuló munkaMennyiség) * (a gyógyszer 1 kilós) * (a gyógyszer 2 font) (Labor US a gyógyszer 6 (gyógyszer 6) * ( a gyógyszer 6 font) a rendelkezésre álló munkához.

Ezért a munkakényszer kiértékelése az űrlap (változó cella) * (állandó) feltételeit, valamint az összegek állandóval való összehasonlítását adja meg. A munkakényszer és a nyersanyag-megkötés egyaránt megfelel a lineáris modell követelményeinek.

Keresleti kényszerek az űrlapon

(Drug 1 termelt) < = (Drug 1 Demand)
(Drug 2 Produced) < = (Drug 2 Demand)
§
(Drug 6 Produced) < = (Drug 6 Demand)

Minden igény szerinti megkötés megfelel a lineáris modell követelményeinek is, mivel az egyes adatok kiértékelése az űrlap (változó cella) * (állandó) és az összegek állandóval való összevetésével történik.

Miután kimutatta, hogy a szorzat mix modell lineáris modell, miért érdemes figyelni?

• Ha egy Solver-modell lineáris, és a lineáris modell kiválasztása beállítást választja, a Solver garantáltan optimális megoldást keres a Solver modellre. Ha egy Solver-modell nem lineáris, a Solver lehet, hogy nem találja az optimális megoldást.

• Ha egy Solver-modell lineáris, és a lineáris modell bekapcsolása beállítást választja, a Solver nagyon hatékony algoritmust használ (a simplex módszer) a modell optimális megoldásának megkereséséhez. Ha egy Solver-modell lineáris, és nem jelöli ki a lineáris modellt, a Solver nagyon hatékony algoritmust használ (az GRG2 módszer), és nehézséget okozhat a modell optimális megoldásának megtalálása.

Miután a Solver beállításai párbeszédpanelen az OK gombra kattintott, visszatérjünk a fő Solver párbeszédpanelre, amely a 27-7 ábra korábbi részén látható. Ha a megoldás gombra kattint, a Solver kiszámítja az optimális megoldást (ha létezik) a szorzat mix modellhez. Amint azt a 26. fejezetben kifejtettem, az optimális megoldás a termék-összevonási modellnek az összes lehetséges megoldás esetében maximalizálja a nyereséget. A kivitelezhető megoldás a minden korlátozást kielégítő cella értékek halmaza. Az 27-9 ábra által megjelenített cellák értékei kivitelezhetők, mert az összes termelési szint nem negatív, a termelés szintje nem haladja meg a keresletet, és az Erőforrás kihasználtsága nem haladja meg a rendelkezésre álló erőforrásokat.

A következő oldal 27-10-as ábrán látható változó cellák értékei az alábbi okok miatt nem kivitelezhető megoldást jelentenek:

• Több mint 5%-ot gyártunk a drog iránt.

• A rendelkezésre állónál több munkamennyiséget használunk.

• A rendelkezésre állónál több nyersanyagot használunk.

A megoldás gombra kattintás után a Solver gyorsan megtalálhatja az optimális megoldást az 27-11 ábra segítségével. Ha meg szeretné őrizni az optimális megoldási értékeket a munkalapon, válassza a Solver-oldat megőrzése lehetőséget.

A gyógyszergyárak maximalizálhatja a havi nyereséget az $6 625,20 szintjén, ha 596,67 kilót termel a drogokból 4, 1084 kilót a kábítószer 5, és nem a többi drogot sem! Nem lehet megállapítani, hogy az $6 625,20 maximális profitját más módon tudjuk-e elérni. Biztos lehet benne, hogy a korlátozott erőforrásokkal és a kereslettel nem lehet több mint $6 627,20 ebben a hónapban.