Sprendimo paieškos naudojimas norint nustatyti optimalų produktų derinį

Kaip nustatyti mėnesio produktų derinys, kurį maksimizuoja pelningumo?

Įmonės dažnai turi nustatyti kiekvieno produkto gauti kas mėnesį. Paprasčiausias formoje produkto sumaišykite problema apima kaip nustatyti, kokio dydžio kiekvieną produktą, kuris turėtų būti pateikta per mėnesį maksimaliai padidinti pelną. Produktų derinys, paprastai turi atitikti šiuos apribojimus:

• Produktų derinys negalima naudoti daugiau nei galima išteklių.

• Yra tik pagal poreikį, kiekvieno produkto. Mes negali pateikti daugiau produkto per mėnesį nei pagal poreikį diktuoja, nes perteklinio gamybos yra prarandama (pavyzdžiui, gendančių narkotikų).

Dabar galime išspręsti toliau pateiktame pavyzdyje produkto mix problemos. Išspręsti šią problemą galite rasti failo Prodmix.xlsx, rodomas 27-1 pav.

Tarkime, kad dirbame narkotikų įmonėje, pagal kurią apskaičiuojamas šešių skirtingų produktų jų įmonėje. Kiekvieno produkto gamybos reikia darbo ir žalias medžiagą. Eilutė 4 27-1 iliustracija rodo valandas, darbo, daržovės ir vaisiai svaro kiekvieno produkto ir 5 eilutėje rodomas žalias medžiaga, reikalinga gauti svaro kiekvieno produkto svarų. Pvz., gamybos produkto 1 svaro reikia šešių valandų darbo ir 3.2 svarų žalias medžiaga. Kiekvienai vaistų 6 eilutėje pateikiamas svaro kainą, vieneto kaina svaro pateikiamas 7 eilutėje ir 9 eilutėje pateikiamas pelno įmokos už svaro. Pvz., produkto 2 už 11,00 $ svaro, prisiima vieneto savikaina, $5.70 per svaro ir padeda $5.30 pelno per svaro. Mėnesio pagal poreikį kiekvienai vaistų pateikiamas 8 eilutėje. Pvz., pagal poreikį produkto 3 yra 1041 svarų. Šį mėnesį, 4500 valandų darbo ir 1 600 svarų žalias medžiaga yra pasiekiamos. Kaip ši bendrovė padidinti savo mėnesio pelno?

Jei nieko apie "Excel" sprendimo paieškos žinojo, mes ataka šią problemą sukurti darbalapio, kurį norite stebėti pelno ir išteklių naudojimą, susijusias su produktų derinys. Tada mes naudojame bandymų ir klaidų keisti produktų derinys optimizuoti pelno nenaudojant daugiau darbo ar žalias medžiagos nei galima ir be gamybos bet narkotikų viršija pagal poreikį. Mes naudojame sprendimo paieškos šį procesą tik etape bandymų ir klaidų. Iš esmės, sprendimo paieškos yra optimizavimo modulis, kuris nepriekaištingai atlieka bandymų ir klaidų ieškoti.

Klavišą, kad produkto mix problemos sprendimas yra efektyviai apskaičiuoti išteklių naudojimą ir pelno, susietą su bet kurio konkretaus produkto derinys. Svarbi priemonė, vandeniui žymėti galima naudoti, kad šis skaičiavimas yra funkcija SUMPRODUCT. Funkcija SUMPRODUCT Sudaugina atitinkamas reikšmes, langelių diapazoną ir grąžina tų reikšmių sumą. Kiekvieno langelių diapazonas, naudojamas SUMPRODUCT vertinimo turi turėti tas pačias dimensijas, reiškia, kad galite naudoti SUMPRODUCT dviejų eilučių ir dviejų stulpelių, bet ne vienas stulpelis ir viena eilutė.

Kaip pavyzdys, kaip mes naudojame funkcija SUMPRODUCT mūsų produktas derinys pavyzdys, išbandykime apskaičiuoti išteklių naudojimą. Apskaičiuoja mūsų darbo naudojimas

(Naudojama per narkotikų 1 svaro darbo) *(Drug 1 pounds produced) +
(darbo naudoti svaro narkotikų 2) * (narkotikų 2 svarų pateikė) +...
(Darbo naudoti svaro narkotikų 6) * (narkotikų 6 svarų pateikė)

Mes gali apskaičiuoti darbo naudojimo daugiau nuobodus būdu kaip D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. Taip pat, kaip reikia apskaičiuoti žalias medžiagos naudojimo D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. Tačiau šių formulių įvedimas į darbalapį šešių produktų yra daug laiko. Įsivaizduokite, kiek tai būtų jei dirbote su įmonės, pvz., pateikė 50 produktai jų įmonėje. Daug lengviau apskaičiuoti darbo ir žalias medžiagos naudojimo būdas yra kopijuoti D14 į D15 SUMPRODUCT($D$2:$I$2,D4:I4)formulę. Ši formulė apskaičiuoja D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (kuris yra mūsų darbo naudojimas), tačiau yra daug lengviau įvesti! Atkreipkite dėmesį, kad naudojant $ ženklas D2:I2 diapazoną, kad kopijuojant formulę vis dar galiu užfiksuoti produktų derinys iš 2 eilutės. D15 langelyje formulė apskaičiuoja žalias medžiagos naudojimas.

Panašiai, nustatomas mūsų pelno

(Narkotikų 1 pelno per svaro) * (1 narkotikų svarų pagaminta) +
(narkotikų 2 pelno per svaro) * (narkotikų 2 svarų pateikė) +...
(Narkotikų 6 pelno per svaro) * (narkotikų 6 svarų pateikė)

Pelno lengvai apskaičiuojamas D12 langelį su formule, SUMPRODUCT(D9:I9,$D$2:$I$2).

Dabar galite nurodomi tris komponentus mūsų produktų derinį sprendimo paieškos modelį.

• Paskirties langeliu.Mūsų tikslas – padidinti pelną (apskaičiuojama D12 langelyje).

• Kintantys langeliai.Svarų skaičių pateikė kiekvieno produkto (išvardyti langelių diapazone D2:I2)

• Apribojimų. Mes šiuos apribojimus:

  • Nenaudokite daugiau darbo ir žalias medžiagos, nei galima. Tai yra reikšmių langelių D14:D15 (ištekliai) turi būti mažesnė arba lygi reikšmes langeliuose F14:F15 (išteklius).

  • Sukelti daugiau nei pagal poreikį narkotikų. Tai yra reikšmių langelių D2:I2 (svarų pateikė kiekvieną narkotikų) turi būti mažesnė arba lygi reikalavimo kiekvienai vaistų (išvardyti langelių D8:I8).

  • Mes negali pateikti neigiamas bet narkotikų kiekį.

Aš bus rodoma, kaip įvesti paskirties langeliu, keisti langelių ir apribojimų į sprendimo paiešką. Tada viskas, ką reikia daryti yra, spustelėkite mygtuką spręsti, Norėdami rasti produkto pramaišiui kaip padidinti pelną!

Norėdami pradėti, spustelėkite skirtuką duomenys ir grupėje analizė spustelėkite sprendimo priemonė.

Dialogo lange Sprendimo paieškos parametrai bus rodomas, kaip parodyta paveikslėlyje 27 2.

Spustelėkite nustatyti paskirties langelį ir pasirinkite mūsų pelno langelį (langelis D12). Spustelėkite lauke pagal keičiamus langelius ir nukreipkite žymiklį į D2:I2, kuriame yra kiekvieno narkotikų svarų pateikė diapazoną. Dialogo lange dabar turi atrodyti 27 3 pav.

Mes dabar esate pasirengę apribojimų įtraukimas į modelį. Spustelėkite mygtuką įtraukti. Matysite dialogo langą Pridėti apribojimą, rodomas 27-4 pav.

Norėdami įtraukti išteklių naudojimo apribojimų, spustelėkite langelio nuoroda, ir pasirinkite D14:D15 diapazoną. Pasirinkite < = iš antrojo sąrašo. Spustelėkite lauke apribojimas, ir tada pasirinkite langelių diapazoną, F14:F15. Dialogo lange Pridėti apribojimą dabar turi atrodyti kaip skaičius 27-5.

Mes dabar užtikrinti, kad kai sprendimo paieškos bando skirtingas reikšmes pakeisti langeliai, tik derinių, kurie atitinka abi D14< = F14 (naudojamas darbo yra mažesnė arba lygi darbo galimų) ir D15< = F15 (žaliava yra mažiau nei arba lygu laikomas žalias medžiagos). Spustelėkite įtraukti ir įveskite pagal poreikį apribojimų. Užpildyti dialogo lange Pridėti apribojimą, kaip parodyta 27-6 pav.

Įtraukti šie apribojimai užtikrina, kai sprendimo paieškos bando įvairiai keisti langelių reikšmes, tik derinių, kurie atitinka šiuos parametrus bus laikoma:

• D2< = D8 (pateikė narkotikų 1 suma yra mažesnė arba lygi reikalavimo narkotikų 1)

• E2< = E8 (pateikė narkotikų 2 suma mažesnė arba lygi reikalavimo narkotikų 2)

• F2< = F8 (pateikė narkotikų 3 padarytus suma yra mažesnė arba lygi reikalavimo narkotikų 3)

• G2< = G8 (pateikė narkotikų 4 padarytus suma yra mažesnė arba lygi reikalavimo narkotikų 4)

• H2< = H8 (pateikė narkotikų 5 padarytus suma yra mažesnė arba lygi reikalavimo narkotikų 5)

• I2< = I8 (pateikė narkotikų 6 padarytus suma yra mažesnė arba lygi reikalavimo narkotikų 6)

Spustelėkite gerai dialogo lange Pridėti apribojimą. Sprendimo paieškos langas turėtų atrodyti kaip skaičius 27-7.

Mes įvesti apribojimą, kintantys langeliai turi būti neneigiamas dialogo lange Sprendimo paieškos parinktys. Spustelėkite mygtuką parinktys, esantį dialogo lange Sprendimo paieškos parametrai. Patikrinkite Tarkime linijinio modelio langelius ir Tarkime ne neigiamas, kaip parodyta figūra 27-8 kitame puslapyje. Spustelėkite Gerai.

Pažymėdami lauką Tarkime ne neigiamas užtikrina, kad sprendimo paieškos mano tik deriniai kintantys langeliai, kuriuose keisti kiekvieną langelį numanoma neneigiamas reikšmę. Mes tikrinamas langą Tarkime linijinio modelio, nes produkto sumaišykite problema yra specialaus tipo vadinamas linijinio modeliosprendimo paieškos problema. Iš esmės, sprendimo paieškos pavyzdys yra linijinis esant šioms sąlygoms:

• Paskirties langeliu apskaičiuojamas įtraukiant kartu sąlygų formos (keitimo cell)*(constant).

• Kiekvieną apribojimą atitinka "linijinio modelio reikalavimas." Tai reiškia, kad kiekvieno apribojimą vertinamas kartu įtraukiant formos sąlygas (keisti cell)*(constant) ir lyginimas į konstantos.

Kodėl šios problemos sprendimo paieškos yra linijinės? Mūsų paskirties langeliu (pelno) yra apskaičiuojama kaip

(Narkotikų 1 pelno per svaro) * (1 narkotikų svarų pagaminta) +
(narkotikų 2 pelno per svaro) * (narkotikų 2 svarų pateikė) +...
(Narkotikų 6 pelno per svaro) * (narkotikų 6 svarų pateikė)

Šio skaičiavimo seka, kurioje paskirties langelio reikšmė gaunama sudėjus sąlygos formos trafaretą (keitimo cell)*(constant).

Mūsų darbo apribojimą vertinamas palyginant vertė iš (darbo naudoti narkotikų 1 svaro) * (1 narkotikų svarų pagaminta) + (darbo naudoti svaro narkotikų 2) *(Drug 2 pounds produced) +... (Darbo su mumisde per svaro narkotikų 6) * (narkotikų 6 svarų pateikė) galimi darbą.

Todėl darbo apribojimą vertinamas kartu įtraukiant formos sąlygas (keisti cell)*(constant) ir lyginimas į konstantos. Darbo apribojimą ir žalias medžiagos apribojimą atitikti linijinio modelio reikalavimus.

Būti mūsų pagal poreikį apribojimų

(Narkotikų 1 pateikė) < (pagal poreikį narkotikų 1) =
< (pateikė narkotikų 2) = (pagal poreikį narkotikų 2)
§
(narkotikų 6 pateikė) < = (6 narkotikų pagal poreikį)

Kiekvienam pagal poreikį apribojimą taip pat atitinka linijinio modelio reikalavimą, nes kiekvienas vertinamas kartu įtraukiant formos sąlygas (keisti cell)*(constant) ir lyginimas į konstantos.

Rodo, mix modelio yra linijinio modelio, kodėl mes turėtume rūpintis?

• Jei sprendimo pavyzdys yra tiesinė ir mes pasirinkite Tarkime linijinis modelis, sprendimo paieškos garantuoja, kad rasti optimalių sprendimų sprendimo paieškos modelį. Jei sprendimo paieškos modelyje nėra linijinė, sprendimo paieškos gali arba negali rasti optimalių sprendimų.

• Jei sprendimo pavyzdys yra tiesinė ir mes pasirinkite Tarkime linijinis modelis, sprendimo paieškos naudoja labai efektyvus algoritmą (simpleksas metodas) rasti modelio optimalių sprendimų. Jei sprendimo pavyzdys yra linijinė ir mes nežymėkite Tarkime linijinio modelio, sprendimo paieškos naudoja labai neefektyvi algoritmą (GRG2 metodas) ir gali būti sunku rasti modelio optimalių sprendimų.

Spustelėjus Gerai dialogo lange Sprendimo paieškos parinktys, mes pateikti pagrindinio sprendimo paieškos dialogo langas, rodomas anksčiau, 27-7 pav. Spustelėjus mygtuką spręsti sprendimo paieškos apskaičiuoja optimalų sprendimą (jei toks yra) mix modelio. Kaip nurodyta skyriuje 26, optimalų sprendimą produkto mix modelį būtų keičiant langelių reikšmes (svarų pateikė kiekvieną narkotikų) rinkinys, kuris maksimizuoja pelną per visus tai įmanoma sprendimų nustatymas. Dar kartą, sprendimas yra rinkinys keičiant langelių reikšmes, kurios atitinka visus apribojimus. Keisti langelių reikšmes, rodomas 27-9 paveikslėlis yra sprendimas, nes visi gamybos lygiai yra neigiamas, gamybos lygis neviršytų pagal poreikį ir išteklių naudojimo neviršija išteklius.

Keisti langelių reikšmes, 27-10 paveikslėlis rodomas kitame puslapyje pateikti pasiekiamumo sprendimas dėl šių priežasčių:

• Negalime pateikti daugiau narkotikų 5 nei pagal poreikį, jį.

• Mes naudojame daugiau nei kas yra darbo.

• Mes naudojame daugiau žalias medžiagos nei kas yra.

Spustelėjus mygtuką spręsti sprendimo paieškos greitai randa optimalų sprendimą, rodomas 27-11 pav. Jums reikia pasirinkti Išsaugoti sprendimo paieškos sprendimą Išsaugoti optimalių sprendimų reikšmes darbalapyje.

Mūsų narkotikų įmonės galite padidinti jo mėnesio pelno lygio $6,625.20 gamybos 596.67 svarų narkotikų 4, 1084 narkotikų 5 svarų ir nė vienas iš kitų narkotikų! Negalite nustatyti, jei galite pasiekti maksimalus pelnas iš $6,625.20 ir kitais būdais. Visi galime būti tikri, kad yra su ribotų išteklių ir pagal poreikį, nėra būdo, kaip atlikti daugiau nei $6,627.20 šį mėnesį.