Sprendimo paieškos naudojimas optimaliam produktų deriniui nustatyti

Šiame straipsnyje aptariama, kaip naudoti sprendimo paieškos programą – "Microsoft Excel" priedo programą, kurią galite naudoti sąlyginei analizei, kad nustatytumėte optimalų produktų derinį.

Kaip nustatyti mėnesio produktų asortimentą, kuris maksimaliai padidina pelningumą?

Įmonėms dažnai reikia kas mėnesį nustatyti kiekvieno gaminamo produkto kiekį. Paprasčiausia forma produktų asortimento problema apima tai, kaip nustatyti kiekvieno produkto kiekį, kuris turėtų būti pagamintas per mėnesį, kad būtų maksimaliai padidintas pelnas. Produktų asortimentas paprastai turi atitikti šiuos apribojimus:

• Produktų asortimentas negali naudoti daugiau išteklių, nei yra prieinama.
• Kiekvieno produkto paklausa yra ribota. Per mėnesį negalime pagaminti daugiau produkto, nei diktuoja paklausa, nes perteklinė produkcija iššvaistoma (pavyzdžiui, greitai gendantis vaistas).

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

Book Tarkime, mes dirbame vaistų kompanijoje, kuri gamina šešis skirtingus produktus savo gamykloje. Kiekvieno produkto gamybai reikalinga darbo jėga ir žaliavos. 27-1 paveikslo 4 eilutėje parodytos darbo valandos, reikalingos kiekvienam produktui pagaminti, o 5 eilutėje - žaliavos svarai, reikalingi kiekvieno produkto svarui pagaminti. Pavyzdžiui, norint pagaminti svarą 1 produkto, reikia šešių valandų darbo ir 3,2 svaro žaliavos. Kiekvieno vaisto kaina už svarą pateikiama 6 eilutėje, vieneto kaina už svarą nurodyta 7 eilutėje, o pelno įnašas už svarą nurodomas 9 eilutėje. Pvz., 2 produktas parduodamas už 11,00 EUR už svarą, naudojant vieneto kainą 5,70 USD už svarą ir įnešant 5,30 USD pelno už svarą. Kiekvieno vaisto mėnesio poreikis nurodytas 8 eilutėje. Pavyzdžiui, 3 produkto paklausa yra 1041 svaras. Šį mėnesį yra 4500 darbo valandų ir 1600 svarų žaliavos. Kaip ši įmonė gali maksimaliai padidinti savo mėnesinį pelną?

Jei nieko nežinotume apie "Excel" sprendimo sprendimą, mes atakuotų šią problemą sukurdami darbalapį, skirtą stebėti pelną ir išteklių naudojimą, susijusį su produktų deriniu. Tada mes naudotume bandymus ir klaidas, kad galėtume keisti produktų asortimentą, kad optimizuotume pelną, nenaudodami daugiau darbo jėgos ar žaliavų, nei yra, ir negamindami jokio vaisto, viršijančio paklausą. Sprendimo paiešką šiame procese naudojame tik bandymų ir klaidų etape. Iš esmės "Solver" yra optimizavimo variklis, kuris nepriekaištingai atlieka bandymų ir klaidų paiešką.

Raktas į produktų derinio problemos sprendimą yra efektyviai apskaičiuoti išteklių naudojimą ir pelną, susijusį su bet kokiu produktų deriniu. Svarbus įrankis, kurį galime naudoti šiam skaičiavimui atlikti, yra funkcija SUMPRODUCT. Funkcija SUMPRODUCT sudaugina atitinkamas reikšmes langelių diapazonuose ir grąžina tų reikšmių sumą. Kiekvienas langelių diapazonas, naudojamas vertinant SUMPRODUCT, turi turėti tas pačias dimensijas, o tai reiškia, kad SUMPRODUCT galite naudoti su dviem eilutėmis arba dviem stulpeliais, bet ne su vienu stulpeliu ir viena eilute.

Pamėginkime apskaičiuoti, kaip galime naudoti funkciją SUMPRODUCT produktų derinio pavyzdyje. Mūsų darbo jėgos sąnaudos apskaičiuojamos pagal

(Darbo jėga, sunaudota vienam svarui narkotikų 1)*(Pagamintas vaistas 1 svaras)+
(Darbo jėga, sunaudota vienam svarui narkotikų 2)*(Narkotikai 2 svarai) + ...
(Darbo jėga sunaudota už svarą narkotikų 6) * (Narkotikų 6 svarai pagaminta)

Mes galėtume apskaičiuoti darbo jėgos naudojimą labiau varginančiu būdu kaip D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. Panašiai žaliavų sunaudojimas gali būti apskaičiuojamas kaip D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Tačiau šių formulių įvedimas į šešių produktų darbalapį užima daug laiko. Įsivaizduokite, kiek laiko tai užtruktų, jei dirbtumėte su įmone, kuri pagamino, pavyzdžiui, 50 produktų savo gamykloje. Daug paprasčiau apskaičiuoti darbo jėgos ir žaliavų sunaudojimą yra kopijuoti 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 yra mūsų darbo sąnaudos), bet daug lengviau įvesti! Atkreipkite dėmesį, kad naudoju $ ženklą su diapazonu D2:I2, kad kopijuodamas formulę vis tiek užfiksuočiau produktų mišinį iš 2 eilutės. Formulė langelyje D15 apskaičiuoja žaliavų naudojimą.

Panašiai mūsų pelną lemia

(Narkotikas 1 pelnas už svarą)*(Narkotikas 1 svaras) +
(Narkotikas 2 pelnas už svarą) * (Narkotikas 2 svarai pagaminti) + ...
(Narkotikų 6 pelnas už svarą)*(Narkotikai 6 svarai)

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

Dabar galime išskirti tris mūsų produktų derinio sprendimo modelio komponentus.

• Tikslinis langelis. Mūsų tikslas – maksimizuoti pelną (skaičiuojama langelyje D12).

• Kintantys langeliai. Kiekvieno produkto pagamintų svarų skaičius (nurodytas langelių diapazone D2:I2)

• Apribojimai. Turime šiuos apribojimus:

  • Nenaudokite daugiau darbo jėgos ar žaliavų, nei turite. Tai yra reikšmės langeliuose D14:D15 (naudojami ištekliai) turi būti mažesnės arba lygios reikšmėms langeliuose F14:F15 (turimi ištekliai).
  • Negaminkite daugiau vaisto, nei yra paklausa. Tai yra, langeliuose D2:I2 esančios vertės (kiekvieno vaisto pagaminti svarai) turi būti mažesnės arba lygios kiekvieno vaisto poreikiui (išvardyti langeliuose D8:I8).
  • Mes negalime pagaminti neigiamo kiekio jokio vaisto.

Parodysiu, kaip įvesti tikslinį langelį, keisti langelius ir apribojimus į sprendimo priemonę. Tada viskas, ką jums reikia padaryti, tai spustelėti mygtuką Išspręsti, kad rastumėte pelną maksimizuojantį produktų derinį!

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

Pasirodys dialogo langas Sprendimo paieškos parametrai, kaip parodyta 27-2 paveiksle.

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

Book Dabar esame pasirengę į modelį įtraukti apribojimų. Spustelėkite mygtuką Pridėti. Pamatysite dialogo langą Pridėti apribojimą, parodytą 27-4 paveiksle.

Book Norėdami įtraukti išteklių naudojimo apribojimus, spustelėkite lauką Langelio nuoroda, tada pažymėkite diapazoną D14:D15. Pasirinkite <= iš vidurinio sąrašo. Spustelėkite lauką Apribojimas, tada pažymėkite langelių diapazoną F14:F15. Dialogo langas Pridėti apribojimą dabar turėtų atrodyti kaip 27-5 pav.

Book Dabar užtikrinome, kad sprendėjui bandant keisti skirtingas reikšmes besikeičiančiuose langeliuose, bus atsižvelgiama tik į tas kombinacijas, kurios tenkina tiek D14<=F14 (sunaudota darbo jėga yra mažesnė arba lygi turimam darbo kiekiui), tiek D15<=F15 (panaudota žaliava yra mažesnė arba lygi turimai žaliavai). Spustelėkite Įtraukti, kad įvestumėte poreikio apribojimus. Užpildykite dialogo langą Pridėti apribojimą, kaip parodyta 27-6 paveiksle.

Book Įtraukus šiuos apribojimus užtikrinama, kad sprendimo paieškai bandant skirtingus kombinacijas su besikeičiančiomis langelių reikšmėmis, bus atsižvelgiama tik į tuos derinius, kurie atitinka šiuos parametrus:

• D2<=D8 (1 vaisto pagamintas kiekis yra mažesnis arba lygus 1 vaisto poreikiui)
• E2<=E8 (pagaminto vaisto 2 kiekis yra mažesnis arba lygus 2 vaisto poreikiui)
• F2<=F8 (pagaminto 3 vaisto kiekis yra mažesnis arba lygus 3 vaisto paklausai)
• G2<=G8 (pagaminto vaisto 4 kiekis yra mažesnis arba lygus 4 vaisto poreikiui)
• H2< = H8 (pagamintas 5 vaisto kiekis yra mažesnis arba lygus 5 vaisto paklausai)
• I2< = I8 (pagaminto vaisto 6 kiekis yra mažesnis arba lygus 6 vaisto paklausai)

Dialogo lange Pridėti apribojimą spustelėkite Gerai. Sprendimo paieškos langas turėtų atrodyti kaip 27-7 pav.

Book Dialogo lange Sprendimo priemonės parinktys įvedame apribojimą, kad besikeičiantys langeliai turi būti neneigiami. Spustelėkite mygtuką Parinktys dialogo lange Sprendimo paieškos parametrai. Pažymėkite langelius Prisiimti tiesinį modelį ir Daryti prielaidą, kad neneigiamas, kaip parodyta kito puslapio 27-8 paveiksle. Spustelėkite Gerai.

Book Pažymėjus žymės langelį Daryti prielaidą, kad nėra neigiamų, užtikrinama, kad sprendimo paieškos priemonė atsižvelgia tik į besikeičiančių langelių kombinacijas, kuriose kiekvienas besikeičiantis langelis įgauna ne neigiamą reikšmę. Pažymėjome langelį Tarkime, linijinis modelis, nes produktų derinio problema yra specialus sprendėjo problemos tipas, vadinamas linijiniu modeliu. Iš esmės sprendimo paieškos modelis yra linijinis esant šioms sąlygoms:

• Paskirties langelis apskaičiuojamas sudedant formos (kintančio langelio)*(konstantos) sąlygas.
• Kiekvienas apribojimas atitinka "linijinio modelio reikalavimus". Tai reiškia, kad kiekvienas apribojimas yra įvertinamas sudedant formos sąlygas (kintantis langelis)*(konstanta) ir lyginant sumas su konstanta.

Kodėl sprendimo paieškos problema linijinė? Mūsų tikslinis langelis (pelnas) apskaičiuojamas kaip

(Narkotikas 1 pelnas už svarą)*(Narkotikas 1 svaras) +
(Narkotikas 2 pelnas už svarą) * (Narkotikas 2 svarai pagaminti) + ...
(Narkotikų 6 pelnas už svarą)*(Narkotikai 6 svarai)

Skaičiuojant tikslinio langelio reikšmė gaunama sudedant formos (kintančio langelio)*(konstantos) terminus.

Mūsų darbo jėgos apribojimas vertinamas lyginant vertę, gautą iš (Darbo sunaudotas vienam svarui narkotikų 1) * (Narkotikų 1 svarų pagamintas) + (Darbo jėga sunaudota už svarą narkotikų 2) * (Narkotikų 2 svarai pagaminta) + ... (Darbo jėga sunaudota už svarą narkotikų 6) * (Narkotikų 6 svarai pagaminta) į turimą darbo jėgą.

Todėl darbo jėgos apribojimas vertinamas sudedant formos sąlygas (kintantis langelis)*(konstanta) ir lyginant sumas su konstanta. Tiek darbo jėgos apribojimas, tiek žaliavų apribojimas atitinka linijinio modelio reikalavimus.

Mūsų paklausos apribojimai pasireiškia

(Pagamintas 1 vaistas)<= (1 narkotikų paklausa)
(Narkotikas 2 pagamintas)<=(2 narkotikų paklausa)
§
(Narkotikas 6 pagamintas)<= (Narkotikų 6 paklausa)

Kiekvienas poreikio apribojimas taip pat tenkina linijinio modelio reikalavimus, nes kiekvienas yra įvertinamas sudedant formos sąlygas (kintantis langelis)*(konstanta) ir lyginant sumas su konstanta.

Įrodžius, kad mūsų produktų asortimento modelis yra linijinis, kodėl mums turėtų rūpėti?

• Jei sprendimo paieškos modelis yra linijinis ir mes pasirenkame Taikyti linijinį modelį, sprendimo paieška garantuotai ras optimalų sprendimo paieškos modelio sprendimą. Jei sprendimo paieškos modelis nėra linijinis, sprendimo paieška gali rasti optimalų sprendimą arba ne.
• Jei sprendimo paieškos modelis yra linijinis ir mes pasirenkame Tarkime, linijinis modelis, sprendimo paieška naudoja labai efektyvų algoritmą (simplekso metodą), kad rastų optimalų modelio sprendimą. Jei sprendimo paieškos modelis yra linijinis ir mes nepasirenkame daryti prielaidą, kad linijinis modelis, sprendimo paieška naudoja labai neefektyvų algoritmą (GRG2 metodas) ir gali būti sunku rasti optimalų modelio sprendimą.

Dialogo lange Sprendimo paieškos parinktys spustelėję Gerai, grįšime į pagrindinį sprendimo paieškos dialogo langą, parodytą anksčiau 27-7 paveiksle. Kai spustelėjame Spręsti, sprendimo paieška apskaičiuoja optimalų sprendimą (jei toks yra) mūsų produktų derinio modeliui. Kaip minėjau 26 skyriuje, optimalus produktų derinio modelio sprendimas būtų kintančių ląstelių verčių rinkinys (kiekvieno vaisto pagaminti svarai), kuris maksimaliai padidina pelną per visų įmanomų sprendimų rinkinį. Vėlgi, įmanomas sprendimas yra besikeičiančių langelių reikšmių rinkinys, tenkinantis visus apribojimus. 27-9 paveiksle parodytos kintančios langelių vertės yra įmanomas sprendimas, nes visi gamybos lygiai nėra neigiami, gamybos lygiai neviršija paklausos, o išteklių naudojimas neviršija turimų išteklių.

Book Kitame puslapyje esančiame 27-10 paveiksle parodytos kintančios langelių reikšmės yra neįmanomas sprendimas dėl šių priežasčių:

• Mes gaminame daugiau narkotikų 5 nei jo paklausa.
• Mes naudojame daugiau darbo jėgos, nei yra prieinama.
• Mes naudojame daugiau žaliavų, nei yra prieinama.

Book Spustelėjus Sprendimo paieška, sprendimo paieška greitai randa optimalų sprendimą, parodytą 27-11 paveiksle. Norėdami darbalapyje išsaugoti optimalias sprendimo reikšmes, turite pasirinkti Išsaugoti sprendimo paieškos sprendimą.

Book Mūsų vaistų kompanija gali maksimaliai padidinti savo mėnesinį pelną 6 625,20 USD lygyje, gamindama 596,67 svarų narkotikų 4, 1084 svarus narkotikų 5 ir nė vieno iš kitų vaistų! Negalime nustatyti, ar galime pasiekti maksimalų 6 625,20 USD pelną kitais būdais. Viskas, kuo galime būti tikri, yra tai, kad dėl mūsų ribotų išteklių ir paklausos šį mėnesį neįmanoma uždirbti daugiau nei 6 627,20 USD.

Ar sprendimo paieškos modelis visada turi sprendimą?

Tarkime, kad kiekvieno produkto paklausa turi būti patenkinta. (Žr. failo Prodmix.xlsx darbalapį Nėra galimo sprendimo .) Tada turime pakeisti paklausos apribojimus iš D2:I2<=D8:I8 į D2:I2>=D8:I8. Norėdami tai padaryti, atidarykite sprendimo paieškos priemonę, pasirinkite apribojimą D2:I2<=D8:I8 ir spustelėkite Keisti. Rodomas dialogo langas Keisti apribojimus, pavaizduotas 27-12 paveiksle.

Book Pasirinkite >=, tada spustelėkite Gerai. Dabar užtikrinome, kad sprendimo paieška apsvarstys galimybę keisti tik visus reikalavimus atitinkančias langelių reikšmes. Spustelėję Sprendimo paiešką, matysite pranešimą "Sprendimo paieškai nepavyko rasti tinkamo sprendimo". Ši žinia nereiškia, kad padarėme klaidą savo modelyje, o tai, kad turėdami ribotus išteklius negalime patenkinti visų produktų paklausos. "Solver" tiesiog mums sako, kad jei norime patenkinti kiekvieno produkto paklausą, turime pridėti daugiau darbo jėgos, daugiau žaliavų arba daugiau abiejų.

Ką reiškia, kad sprendimo paieškos modelis pateikia rezultatą, kurį pateikia Rinkinio reikšmės nesikonverguoja?

Pažiūrėkime, kas atsitiks, jei leisime neribotą kiekvieno produkto paklausą ir leisime gaminti neigiamus kiekvieno vaisto kiekius. (Šią sprendimo paieškos problemą galite matyti failo Prodmix.xlsx darbalapyje Nustatyti reikšmes nekonverguoti .) Norėdami rasti optimalų sprendimą šiai situacijai, atidarykite sprendimo paieškos priemonę, spustelėkite mygtuką Parinktys ir išvalykite langelį Daryti prielaidą, kad neneigiamas. Dialogo lange Sprendimo paieškos parametrai pasirinkite poreikio apribojimą D2:I2<=D8:I8 ir spustelėkite Naikinti, kad pašalintumėte šį apribojimą. Spustelėjus Sprendimo paieška, sprendimo paieška pateikia pranešimą "Nustatyti langelių reikšmes nesikonverguoja". Šis pranešimas reiškia, kad jei tikslinė ląstelė turi būti maksimaliai padidinta (kaip mūsų pavyzdyje), yra įmanomų sprendimų su savavališkai didelėmis tikslinių langelių reikšmėmis. (Jei norima minimizuoti tikslinį langelį, pranešimas "Nustatyti langelio reikšmes nesikonverguoja" reiškia, kad yra galimų sprendimų su savavališkai mažomis tikslinių langelių reikšmėmis.) Mūsų situacijoje, leisdami neigiamai gaminti narkotiką, mes iš tikrųjų "sukuriame" išteklius, kurie gali būti panaudoti savavališkai dideliems kiekiams kitų vaistų gaminti. Atsižvelgiant į mūsų neribotą paklausą, tai leidžia mums uždirbti neribotą pelną. Realioje situacijoje negalime uždirbti begalinės pinigų sumos. Trumpai tariant, jei matote "Set Values Do Not Convergge" (Nustatyti reikšmės nesikonverguoja), jūsų modelyje yra klaida.

Problemos

1. Tarkime, kad mūsų vaistų kompanija gali įsigyti iki 500 valandų darbo už 1 USD daugiau per valandą nei dabartinės darbo sąnaudos. Kaip galime maksimaliai padidinti pelną?

2. Lustų gamybos įmonėje keturi technikai (A, B, C ir D) gamina tris produktus (1, 2 ir 3 produktus). Šį mėnesį lustų gamintojas gali parduoti 80 vienetų 1 produkto, 50 vienetų 2 produkto ir daugiausiai 50 vienetų 3 produkto. Technikas A gali gaminti tik 1 ir 3 gaminius. Technikas B gali gaminti tik 1 ir 2 gaminius. Technikas C gali pagaminti tik 3 gaminį. Technikas D gali pagaminti tik 2 gaminį. Už kiekvieną pagamintą vienetą produktai prisideda prie tokio pelno: 1 produktas, 6 USD; 2 produktas, 7 USD; ir 3 produktas, 10 USD. Laikas (valandomis), kurio reikia kiekvienam technikui pagaminti gaminį, yra toks:

   Produktas	Technikas A	Technikas B	Technikas C	Technikas D
   1	2	2,5	Negalima	Negalima
   2	Negalima	3	Negalima	3.5
   3	3	Negalima	4	Negalima

3. Kiekvienas technikas gali dirbti iki 120 valandų per mėnesį. Kaip lustų gamintojas gali maksimaliai padidinti savo mėnesinį pelną? Tarkime, kad galima pagaminti dalinį vienetų skaičių.

4. Kompiuterių gamybos įmonė gamina peles, klaviatūras ir vaizdo žaidimų vairalazdes. Vieneto pelnas, darbo jėgos sunaudojimas vienetui, mėnesio paklausa ir mašinos laiko sunaudojimas vienetui pateikti šioje lentelėje:

   	Pelės	Klaviatūros	Vairalazdės
   Pelnas/vienetas	8 doleriai	11 dolerių	9 doleriai
   Darbo sąnaudos / vienetas	.2 val.	.3 valanda	.24 valanda
   Mašinos laikas/vienetas	.04 valanda	0,055 valanda	.04 valanda
   Mėnesio poreikis	15 000	27,000	11,000

5. Kiekvieną mėnesį iš viso galima naudoti 13 000 darbo valandų ir 3000 valandų mašinos laiko. Kaip gamintojas gali maksimaliai padidinti savo mėnesinį pelno įnašą iš gamyklos?

6. Išspręskite mūsų narkotikų pavyzdį, darant prielaidą, kad turi būti patenkintas minimalus 200 vienetų kiekvieno vaisto poreikis.

7. Jasonas gamina deimantines apyrankes, karolius ir auskarus. Jis nori dirbti ne daugiau kaip 160 valandų per mėnesį. Jis turi 800 uncijų deimantų. Toliau pateikiamas pelnas, darbo laikas ir deimantų uncijos, reikalingos kiekvienam produktui pagaminti. Jei kiekvieno produkto paklausa yra neribota, kaip Jasonas gali maksimaliai padidinti savo pelną?

   Produktas	Vieneto pelnas	Darbo valandos vienetui	Deimantų uncijos vienete
   Apyrankė	300 EUR	.35	1.2
   Vėrinys	$200	.15	.75
   Auskarai	$100	.05	.5