Sprendimo paieškos naudojimas optimaliam produktų deriniui nustatyti

Kaip nustatyti mėnesio produktų derinį, kuris maksimizuoja pelningumą?

Įmonės dažnai turi nustatyti kiekvieno produkto kiekį kas mėnesį. Iš esmės produktų derinio problema susijusi su tuo, kaip nustatyti kiekvieno produkto kiekį, kuris turėtų būti pagamintas per mėnesį, kad maksimaliai padidintumėte pelną. Produktų derinys paprastai turi atitikti šiuos apribojimus:

• Produktų derinys negali naudoti daugiau išteklių, nei yra.

• Kiekvieno produkto paklausa yra ribota. Negalime gaminti daugiau produkto per mėnesį, nei nurodo paklausa, nes perteklinė gamyba iššvaistyti (pvz., greitai gendantis vaistas).

Dabar išspręskime šį produktų derinio problemos pavyzdį. Šios problemos sprendimą galite rasti failo Prodmix.xlsx, parodytame 27-1 pav.

Tarkime, kad mes dirbame narkotikų kompanijai, kuri gamina šešis skirtingus produktus jų augaluose. Kiekvieno produkto gamybai reikia darbo jėgos ir žaliavų. 4 eilutėje 27-1 paveiksle pavaizduotos darbo valandos, reikalingos kiekvieno produkto svarui pagaminti, o 5 eilutėje rodomi žaliavų svarai, reikalingi kiekvieno produkto svarui pagaminti. Pavyzdžiui, norint gaminti 1 produkto svarą reikia šešių valandų darbo ir 3,2 svarų žaliavų. Kiekvieno vaisto kaina už svarą yra nurodyta 6 eilutėje, vieneto kaina už svarą nurodoma 7 eilutėje, o pelno įnašas už svarą nurodytas 9 eilutėje. Pavyzdžiui, 2 produktas parduoda už 11,00 EUR už svarą, jo vieneto kaina yra 5,70 EUR už svarą ir prisideda prie 5,30 EUR pelno už svarą. Kiekvieno vaisto mėnesio paklausa pateikta 8 eilutėje. Pavyzdžiui, 3 produkto paklausa yra 1041 svaro. Šį mėnesį yra 4500 valandų darbo ir 1600 svarų žaliavų. Kaip ši įmonė gali padidinti savo mėnesinį pelną?

Jei nieko nežinojome apie "Excel" sprendimo paiešką, užpulsime šią problemą kurdami darbalapį pelnui ir išteklių naudojimui, susietam su produktų deriniu, stebėti. Tada mes turėtume naudoti bandomąją versiją ir klaidą, kad galėtume keisti produktų derinį, kad optimizuotume pelną nenaudodami daugiau darbo vietų ar žaliavų, nei yra prieinama, ir negaminant jokių vaistų, kurie viršija paklausą. Sprendimo paiešką šiame procese naudojame tik bandomosios versijos ir klaidų etape. Iš esmės sprendimo paieška yra optimizavimo modulis, kuris be trūkumų atlieka bandomosios versijos ir klaidų iešką.

Produkto derinio problemos sprendimo raktas yra efektyviai apskaičiuoti išteklių naudojimą ir pelną, susietą su bet kokiu produktų deriniu. Svarbus įrankis, kurį galime naudoti, kad šis skaičiavimas būtų SUMPRODUCT funkcija. Funkcija SUMPRODUCT sudaugina atitinkamas langelių diapazonų reikšmes ir grąžina tų reikšmių sumą. Kiekvieno langelių diapazono, naudojamo atliekant SUMPRODUCT vertinimą, matmenys turi būti tokie patys, o tai reiškia, kad galite naudoti SUMPRODUCT su dviem eilutėmis arba dviem stulpeliais, bet ne su vienu stulpeliu ir viena eilute.

Kaip pavyzdį galime naudoti funkciją SUMPRODUCT mūsų produktų derinio pavyzdyje, pabandykime apskaičiuoti išteklių naudojimą. Mūsų darbo naudojimas apskaičiuojamas pagal

(Darbas naudojamas už svarą narkotikų 1)*(Narkotikų 1 svarų pagaminti)+
(Darbas naudojamas už svarą narkotikų 2)*(Narkotikų 2 svarų pagaminti) + ...
(Darbas naudojamas už svarą narkotikų 6)*(Narkotikų 6 svarų pagaminti)

Mes galėtų apskaičiuoti darbo naudojimo varginantis mados D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. Taip pat žaliavų naudojimas galėtų būti apskaičiuojamas kaip D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Tačiau šių formulių įvedimas į darbalapį šešiems produktams užima daug laiko. Įsivaizduokite, kiek užtruks, jei dirbtumėte su įmone, kuri pagamino, pvz., 50 produktų jų augale. Daug lengviau apskaičiuoti darbą ir žaliavų naudojimą yra nukopijuoti iš D14 į D15 formulę SUMPRODUCT($D$2:$I$2,D4:I4). Ši formulė apskaičiuoja D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (tai mūsų darbo naudojimas), tačiau ją daug lengviau įvesti! Atkreipkite dėmesį, kad aš naudoju $ ženklą su diapazono D2:I2, kad kai aš nukopijuoti formulę aš vis dar užfiksuoti produkto mišinys iš eilutės 2. Formulė langelyje D15 apskaičiuoja žaliavų naudojimą.

Panašiu būdu mūsų pelną nustato

(1 narkotiko pelnas už svarą)*(pagamintas narkotikų 1 svaras) +
(Narkotikų 2 pelnas už svarą)* (Narkotikų 2 svarų pagaminti) + ...
(Narkotikai 6 pelnas už svarą)*(Pagamintas narkotikų 6 svarai)

Pelnas lengvai apskaičiuojamas langelyje D12 naudojant formulę SUMPRODUCT(D9:I9,$D$2:$I$2).

Dabar galime nustatyti tris produkto mišinio sprendimo paieškos modelio komponentus.

• Paskirties langelis. Mūsų tikslas yra maksimizuoti pelną (apskaičiuojama langelyje D12).

• Keičiami langeliai. Pagamintų kiekvieno produkto svarų skaičius (nurodytas langelių diapazone D2:I2)

• Apribojimus. Turime tokius apribojimus:

  • Nenaudokite daugiau darbo ar žaliavų, nei yra. T. y. langelių D14:D15 reikšmės (naudojami ištekliai) turi būti mažesnės arba lygios langelių F14:F15 reikšmėms (turimi ištekliai).

  • Negamina daugiau narkotikų, nei yra paklausa. Tai yra, vertės ląstelėse D2:I2 (kiekvieno vaisto pagaminti svarai) turi būti mažesnės arba lygios kiekvieno vaisto paklausai (išvardytai langeliuose D8:I8).

  • Negalime gaminti neigiamo jokio narkotiko kiekio.

Parodysiu, kaip įvesti paskirties langelį, keisti langelius ir apribojimus sprendimo paieškai. Tada jums tereikia spustelėti mygtuką Spręsti, kad rastumėte pelną maksimizuojantį produktų rinkinį!

Norėdami pradėti, spustelėkite skirtuką Duomenys ir grupėje Analizė spustelėkite Sprendimo paieška.

Bus rodomas dialogo langas Sprendimo paieškos parametrai, kaip parodyta 27–2 pav.

Spustelėkite lauką Nustatyti tikslinį langelį ir pasirinkite mūsų pelno langelį (langelį D12). Spustelėkite lauką Pagal keičiamus langelius, tada nukreipkite žymiklį į diapazoną D2:I2, kuriame yra kiekvieno vaisto pagaminti svarai. Dialogo langas dabar turėtų atrodyti 27–3 pav.

Dabar esame pasirengę įtraukti į modelį apribojimų. Spustelėkite mygtuką Įtraukti. Pamatysite dialogo langą Įtraukti apribojimą, rodomą 27–4 pav.

Norėdami įtraukti išteklių naudojimo apribojimus, spustelėkite langelį Langelio nuoroda ir pažymėkite diapazoną D14:D15. Viduriniajame sąraše pasirinkite <=. Spustelėkite lauką Apribojimas, tada pasirinkite langelių diapazoną F14:F15. Dialogo langas Apribojimo įtraukimas dabar turėtų atrodyti kaip 27–5 pav.

Dabar užtikrinome, kad sprendimo paieškai bandant skirtingas kintančių langelių reikšmes, bus svarstomi tik tie deriniai, kurie atitinka D14<=F14 (darbo vietoje naudojama mažiau arba lygu darbui) ir D15<=F15 (naudojama žaliava yra mažesnė arba lygi turimai žaliavai). Spustelėkite Įtraukti, kad įvestumėte poreikio apribojimus. Užpildykite dialogo langą Apribojimo įtraukimas, kaip parodyta 27–6 pav.

Įtraukus šiuos apribojimus užtikrinama, kad sprendimo paieškos funkcijai bandant skirtingus langelių reikšmių derinius, bus svarstomi tik tie deriniai, kurie atitinka šiuos parametrus:

• D2<=D8 (1 narkotiko pagaminamas kiekis yra mažesnis arba lygus 1 narkotiko paklausai)

• E2<=E8 (2 narkotiko pagaminimo kiekis yra mažesnis arba lygus 2-ojo vaisto paklausai)

• F2<=F8 (pagaminto 3-ojo narkotiko kiekis yra mažesnis arba lygus 3-io vaisto paklausai)

• G2<=G8 (pagaminto 4-ojo narkotiko kiekis yra mažesnis arba lygus 4-ojo vaisto paklausai)

• H2<=H8 (pagaminto narkotiko 5 kiekis yra mažesnis arba lygus 5-ojo vaisto paklausai)

• I2<=I8 (pagamintas 6 narkotiko kiekis yra mažesnis arba lygus 6 narkotikų paklausai)

Dialogo lange Apribojimo įtraukimas spustelėkite Gerai. Sprendimo paieškos langas turėtų atrodyti kaip 27–7 pav.

Dialogo lange Sprendimo paieškos parinktys įvedame apribojimą, kad keičiami langeliai turi būti neigiami. Dialogo lange Sprendimo paieškos parametrai spustelėkite mygtuką Parinktys. Pažymėkite žymės langelį Taikyti tiesinį modelį ir Manyti, kad tai nėra neigiamas, kaip parodyta 27–8 pav. kitame puslapyje. Spustelėkite Gerai.

Pažymėjus lauką Laikyti ne neigiamais užtikrinama, kad sprendimo paieška atsižvelgia tik į keičiamų langelių derinius, kuriuose kiekvienas kintantis langelis daro prielaidą, kad reikšmė nėra neigiama. Patikrinome lauką Numanomas linijinis modelis, nes produktų derinio problema yra specialus sprendimo paieškos problemos tipas, vadinamas linijiniu modeliu. Sprendimo paieškos modelis iš esmės yra tiesinis esant šioms sąlygoms:

• Paskirties langelis apskaičiuojamas sudedant formos sąlygas (keičiant langelį)*(konstanta).)

• Kiekvienas apribojimas atitinka "linijinio modelio reikalavimą". Tai reiškia, kad kiekvienas apribojimas įvertinamas įtraukiant kartu formos sąlygas (keičiant langelį)*(konstanta) ir lyginant sumas su konstanta.

Kodėl ši sprendimo paieškos problema yra tiesinė? Mūsų tikslinis langelis (pelnas) skaičiuojamas kaip

(1 narkotiko pelnas už svarą)*(pagamintas narkotikų 1 svaras) +
(Narkotikų 2 pelnas už svarą)* (Narkotikų 2 svarų pagaminti) + ...
(Narkotikai 6 pelnas už svarą)*(Pagamintas narkotikų 6 svarai)

Šis skaičiavimas atitinka modelį, pagal kurį išvedama paskirties langelio reikšmė, įtraukiant kartu formos sąlygas (keičiant langelį)*(konstanta).

Mūsų darbo jėgos apribojimas yra vertinamas lyginant iš vertės (Darbo naudojamas už svaro narkotikų 1)*(Narkotikų 1 svarų pagaminti) + (Darbo naudojamas už svaro narkotikų 2)*(Narkotikų 2 svarų pagaminti)+ ( (Darbas mused už svarą narkotikų 6)*(Narkotikų 6 svarų pagaminti) į darbo prieinama.

Todėl darbo jėgos apribojimas įvertinamas sudedant formos sąlygas (keičiant langelį)*(konstanta) ir lyginant sumas su konstanta. Tiek darbo jėgos apribojimas, tiek žaliavų apribojimas atitinka tiesinio modelio reikalavimą.

Mūsų poreikio apribojimai yra

(Pagamintas 1 narkotikas)<=(1 narkotiko paklausa)
(Pagamintas 2 narkotikas)<=(2 narkotiko paklausa)
§
(Pagamintas 6 narkotikas)<=(6 narkotiko paklausa)

Kiekvienas poreikio apribojimas taip pat atitinka linijinio modelio reikalavimą, nes kiekvienas iš jų įvertinamas sudedant formos sąlygas (keičiant langelį)*(konstanta) ir lyginant sumas su konstanta.

Atsižvelgiant į tai, kad mūsų produktų mišinio modelis yra tiesinis modelis, kodėl turėtume rūpintis?

• Jei sprendimo paieškos modelis yra tiesinis ir mes pasirenkame Numanomas linijinis modelis, sprendimo paieška garantuojama, kad būtų rastas optimalus sprendimo paieškos modelio sprendimas. Jei sprendimo paieškos modelis nėra tiesinis, sprendimo paieška gali arba neranda optimalaus sprendimo.

• Jei sprendimo paieškos modelis yra tiesinis ir mes pasirenkame Taikyti linijinį modelį, sprendimo paieška naudoja labai veiksmingą algoritmą (paprastą metodą), kad rastų optimalų modelio sprendimą. Jei sprendimo paieškos modelis yra tiesinis ir mes nepasirinkome "Assume Linear Model", sprendimo paieška naudoja labai neefektyvų algoritmą (GRG2 metodą) ir gali būti sunku rasti optimalų modelio sprendimą.

Spustelėję Gerai dialogo lange Sprendimo paieškos parinktys, grįžtame į pagrindinį dialogo langą Sprendimo paieška, parodytas anksčiau 27-7 pav. Kai spustelėjame Sprendimo paieška, sprendimo paieška apskaičiuoja optimalų mūsų produktų derinio modelio sprendimą (jei toks yra). Kaip minėjau 26 skyriuje, optimalus produkto mišinio modelio sprendimas būtų kintančių langelių reikšmių (kiekvieno narkotiko pagamintų svarų) rinkinys, kuris maksimaliai padidina pelną per visų įvykdomų sprendimų rinkinį. Vėlgi, įvykdomas sprendimas yra langelių reikšmių keitimo rinkinys, atitinkantis visus apribojimus. 27–9 paveiksle pavaizduotos kintančios langelių reikšmės yra įvykdomas sprendimas, nes visi gamybos lygiai nėra neigiami, gamybos lygiai neviršija paklausos, o išteklių naudojimas neviršija turimų išteklių.

Keičiamos langelių reikšmės, parodytos 27–10 paveikslėlyje kitame puslapyje, nurodo įvykdomą sprendimą dėl šių priežasčių:

• Mes gaminame daugiau narkotikų 5 nei paklausa už jį.

• Mes naudojame daugiau darbo jėgos, nei yra.

• Mes naudojame daugiau žaliavų, nei yra.

Spustelėjus Sprendimo paieška greitai randa optimalų sprendimą, parodytą 27–11 pav. Turite pasirinkti Palikti sprendimo paieškos sprendimą, kad išsaugotumėte optimalias sprendimo reikšmes darbalapyje.

Mūsų narkotikų bendrovė gali maksimaliai padidinti savo mėnesio pelną 6.625.20 lygį, gaminant 596.67 svarų narkotikų 4, 1084 svarų narkotikų 5, ir nė vienas iš kitų narkotikų! Negalime nustatyti, ar galime pasiekti maksimalų 6 625,20 EUR pelną kitais būdais. Visi mes galime būti tikri, kad mūsų ribotų išteklių ir paklausos, nėra būdas padaryti daugiau nei $ 6,627,20 šį mėnesį.