Risinātāja izmantošana, lai noteiktu optimālo produktu kombināciju

Šajā rakstā ir aplūkota Microsoft Excel pievienojumprogrammas Risinātājs lietošana, ko varat izmantot iespēju analīzei, lai noteiktu optimālu produktu kombināciju.

Kā noteikt ikmēneša produktu kombināciju, kas maksimāli palielina rentabilitāti?

Uzņēmumiem bieži ir jānosaka katra produkta daudzums, kas jāražo katru mēnesi. Vienkāršākajā formā produktu sastāva problēma ir saistīta ar to, kā noteikt katra produkta daudzumu, kas jāsaražo mēneša laikā, lai maksimāli palielinātu peļņu. Produktu klāstā parasti jāievēro šādi ierobežojumi:

• Produktu kombinācija nevar izmantot vairāk resursu, nekā ir pieejams.
• Pieprasījums pēc katra produkta ir ierobežots. Mēneša laikā mēs nevaram saražot vairāk produkta, nekā nosaka pieprasījums, jo produkcijas pārpalikums tiek izšķērdēts (piemēram, ātrbojīga narkotika).

Tagad atrisināsim tālāk minēto produktu kombinācijas problēmas piemēru. Šīs problēmas risinājumu varat atrast failu Prodmix.xlsx, kas parādīts 27-1. attēlā.

Pieņemsim, ka mēs strādājam zāļu kompānijā, kas savā rūpnīcā ražo sešus dažādus produktus. Katra produkta ražošanai ir nepieciešams darbaspēks un izejvielas. 4. rindā 27-1. attēlā parādītas darba stundas, kas nepieciešamas, lai saražotu mārciņu katra produkta, un 5. rindā parādītas mārciņas izejvielu, kas nepieciešamas, lai saražotu mārciņu katra produkta. Piemēram, mārciņas 1. produkta ražošanai nepieciešamas sešas stundas darba un 3,2 mārciņas izejvielu. Katrai narkotikai cena par mārciņu ir norādīta 6. rindā, vienības izmaksas par mārciņu ir norādītas 7. rindā, un peļņas ieguldījums par mārciņu ir norādīts 9. rindā. Piemēram, 2. produkts tiek pārdots par 11,00 USD par mārciņu, vienības izmaksas ir 5,70 USD par mārciņu un iemaksā 5,30 USD peļņu par mārciņu. Mēneša pieprasījums pēc katras zāles ir norādīts 8. rindā. Piemēram, pieprasījums pēc 3. produkta ir 1041 mārciņa. Šomēnes ir pieejamas 4500 darba stundas un 1600 mārciņas izejvielu. Kā šis uzņēmums var maksimāli palielināt savu ikmēneša peļņu?

Ja mēs neko nezinātu par Excel risinātāju, mēs novērstu šo problēmu, izveidojot darblapu, lai sekotu ar produktu kombināciju saistītajai peļņai un resursu lietojumam. Tad mēs izmantotu izmēģinājumus un kļūdas, lai mainītu produktu klāstu, lai optimizētu peļņu, neizmantojot vairāk darbaspēka vai izejvielu, nekā ir pieejams, un neražojot zāles, kas pārsniedz pieprasījumu. Risinātāju šajā procesā izmantojam tikai izmēģinājumu un kļūdu posmā. Būtībā Risinātājs ir optimizācijas programma, kas nevainojami veic izmēģinājumu un kļūdu meklēšanu.

Produktu kombinācijas problēmas risināšanas atslēga ir efektīvi aprēķināt resursu lietojumu un peļņu, kas saistīta ar jebkuru konkrētu produktu kombināciju. Svarīgs rīks, ko varam izmantot šo aprēķinu veikšanai, ir funkcija SUMPRODUCT. Funkcija SUMPRODUCT reizina atbilstošās vērtības šūnu diapazonos un atgriež šo vērtību summu. Visiem SUMPRODUCT novērtēšanā izmantotajiem šūnu diapazoniem jābūt vienādām dimensijām, kas nozīmē, ka SUMPRODUCT varat izmantot ar divām rindām vai divām kolonnām, bet ne ar vienu kolonnu un vienu rindu.

Kā piemēru tam, kā varam izmantot funkciju SUMPRODUCT mūsu produktu klāsta piemērā, mēģināsim aprēķināt mūsu resursu lietojumu. Mūsu darbaspēka patēriņu aprēķina

(Izmantotais darbaspēks uz vienu mārciņu narkotiku 1)*(Saražotās zāles 1 mārciņa)+
(Izmantotais darbaspēks uz vienu mārciņu narkotiku 2)*(Saražotās zāles 2 mārciņas) + ...
(Izmantotais darbaspēks uz vienu mārciņu narkotiku 6)*(Saražotās zāles 6 mārciņas)

Mēs varētu aprēķināt darbaspēka patēriņu garlaicīgākā veidā kā D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. Līdzīgi izejvielu patēriņu varētu aprēķināt kā D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Tomēr šo formulu ievadīšana darblapā sešiem produktiem ir laikietilpīga. Iedomājieties, cik ilgs laiks būtu nepieciešams, ja jūs strādātu ar uzņēmumu, kas savā rūpnīcā ražo, piemēram, 50 produktus. Daudz vienkāršāks veids, kā aprēķināt darbaspēka un izejvielu lietojumu, ir kopēt no D14 uz D15 formulu SUMPRODUCT($D$2:$I$2,D4:I4)). Šī formula aprēķina D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (kas ir mūsu darbaspēka lietojums), bet to ir daudz vieglāk ievadīt! Ievērojiet, ka es izmantoju $ zīmi ar diapazonu D2:I2, lai, kopējot formulu, es joprojām tvertu produktu kombināciju no 2. rindas. Formula šūnā D15 aprēķina izejvielu lietojumu.

Līdzīgā veidā mūsu peļņu nosaka

(Zāles 1 peļņa par mārciņu)*(Saražotās zāles 1 mārciņa) +
(Narkotikas 2 peļņa par mārciņu)*(Saražotās zāles 2 mārciņas) + ...
(Narkotiku 6 peļņa par mārciņu)*(Saražotās zāles 6 mārciņas)

Peļņu var viegli aprēķināt šūnā D12, izmantojot formulu SUMPRODUCT(D9:I9,$D 2 EUR:$I 2 EUR).

Tagad varam identificēt trīs mūsu produktu kombinācijas risinātāja modeļa komponentus.

• Mērķa šūna. Mūsu mērķis ir maksimāli palielināt peļņu (aprēķināta šūnā D12).

• Mainīgās šūnas. Katra produkta saražoto mārciņu skaits (uzskaitīts šūnu diapazonā D2:I2)

• Ierobežojumi. Pastāv šādi ierobežojumi:

  • Neizmantojiet vairāk darbaspēka vai izejvielu, nekā ir pieejams. Proti, vērtībām šūnās D14:D15 (izmantotie resursi) ir jābūt mazākām vai vienādām ar vērtībām šūnās F14:F15 (pieejamie resursi).
  • Neražojiet vairāk narkotiku, nekā ir pieprasīts. Tas nozīmē, ka šūnās D2:I2 (katras zāles saražotās mārciņas) vērtībām jābūt mazākām vai vienādām ar katras zāles pieprasījumu (uzskaitītas šūnās D8:I8).
  • Mēs nevaram saražot negatīvu daudzumu nevienas zāles.

Es jums parādīšu, kā ievadīt mērķa šūnu, mainīt šūnas un ierobežojumus risinātājā. Tad viss, kas jums jādara, ir noklikšķināt uz pogas Atrisināt, lai atrastu peļņu maksimāli palielinošu produktu kombināciju!

Lai sāktu, noklikšķiniet uz cilnes Dati un grupā Analīze noklikšķiniet uz Risinātājs.

Tiks parādīts dialoglodziņš Risinātāja parametri, kā parādīts 27-2. attēlā.

Noklikšķiniet uz lodziņa Iestatīt mērķa šūnu un pēc tam atlasiet mūsu peļņas šūnu (šūna D12). Noklikšķiniet uz lodziņa Mainot šūnas un pēc tam norādiet uz diapazonu D2:I2, kurā ir katras zāles saražotās mārciņas. Tagad dialoglodziņam jāizskatās kā 27-3. attēls.

Tagad esam gatavi pievienot modelim ierobežojumus. Noklikšķiniet uz pogas Pievienot. Tiks parādīts dialoglodziņš Ierobežojuma pievienošana, kas parādīts 27-4. attēlā.

Lai pievienotu resursu lietojuma ierobežojumus, noklikšķiniet uz lodziņa Šūnas atsauce un pēc tam atlasiet diapazonu D14:D15. Vidējā sarakstā atlasiet <=. Noklikšķiniet uz lodziņa Ierobežojums un pēc tam atlasiet šūnu diapazonu F14:F15. Dialoglodziņam Ierobežojuma pievienošana tagad jāizskatās kā 27-5. attēls.

Tagad esam pārliecinājušies, ka, kad Risinātājs izmēģina dažādas vērtības mainīgajām šūnām, tiks ņemtas vērā tikai tādas kombinācijas, kas apmierina gan D14<=F14 (izmantotais darbaspēks ir mazāks vai vienāds ar pieejamo darbaspēku), gan D15<=F15 (izmantotā izejviela ir mazāka vai vienāda ar pieejamo izejvielu). Noklikšķiniet uz Pievienot, lai ievadītu pieprasījuma ierobežojumus. Aizpildiet dialoglodziņu Ierobežojuma pievienošana, kā parādīts 27-6. attēlā.

Pievienojot šos ierobežojumus, tiek nodrošināts, ka, kad risinātājs mēģina izmantot dažādas kombinācijas mainīgajām šūnu vērtībām, tiek ņemtas vērā tikai tās kombinācijas, kas atbilst šādiem parametriem:

• D2<=D8 (1. narkotikas saražotais daudzums ir mazāks vai vienāds ar 1. narkotikas pieprasījumu)
• E2<=E8 (saražotās 2. zāles daudzums ir mazāks vai vienāds ar 2. zāļu pieprasījumu)
• F2<=F8 (saražotais 3. narkotikas daudzums ir mazāks vai vienāds ar 3. zāļu pieprasījumu)
• G2<=G8 (saražotais 4. zāļu daudzums ir mazāks vai vienāds ar 4. zāļu pieprasījumu)
• H2< = H8 (saražotais 5. narkotikas daudzums ir mazāks vai vienāds ar 5. zāļu pieprasījumu)
• I2<=I8 (saražotās 6. zāles saražotais daudzums ir mazāks vai vienāds ar 6. narkotikas pieprasījumu)

Dialoglodziņā Ierobežojuma pievienošana noklikšķiniet uz Labi. Risinātāja logam jāizskatās kā 27-7. attēlā.

Risinātāja opciju dialoglodziņā ievadām ierobežojumu, ka mainīgajās šūnās nedrīkst būt negatīvas. Dialoglodziņā Risinātāja parametri noklikšķiniet uz pogas Opcijas. Atzīmējiet izvēles rūtiņas Pieņemt lineāru modeli un Pieņemt, ka nav negatīvs, kā parādīts 27-8. attēlā nākamajā lapā. Noklikšķiniet uz Labi.

Atzīmējot rūtiņu Pieņemt, ka nav negatīvs, risinātājs ņem vērā tikai tādas mainīgo šūnu kombinācijas, kurās katra mainīgā šūna pieņem vērtību, kas nav negatīva. Mēs atzīmējām izvēles rūtiņu Pieņemt lineāro modeli, jo produktu kombinācijas problēma ir īpaša veida risinātāja problēma, ko sauc par lineāro modeli. Būtībā risinātāja modelis ir lineārs šādos apstākļos:

• Mērķa šūna tiek aprēķināta, saskaitot formas terminus (mainīgā šūna)*(konstante).
• Katrs ierobežojums atbilst "lineārā modeļa prasībai". Tas nozīmē, ka katrs ierobežojums tiek novērtēts, saskaitot kopā formas (mainīgā šūna)*(konstante) nosacījumus un salīdzinot summas ar konstanti.

Kāpēc šī risinātāja problēma ir lineāra? Mūsu mērķa šūna (peļņa) tiek aprēķināta kā

(Zāles 1 peļņa par mārciņu)*(Saražotās zāles 1 mārciņa) +
(Narkotikas 2 peļņa par mārciņu)*(Saražotās zāles 2 mārciņas) + ...
(Narkotiku 6 peļņa par mārciņu)*(Saražotās zāles 6 mārciņas)

Šis aprēķins notiek pēc parauga, kurā mērķa šūnas vērtība tiek iegūta, saskaitot kopā formas terminus (mainīgā šūna)*(konstante).

Mūsu darbaspēka ierobežojums tiek novērtēts, salīdzinot vērtību, kas iegūta no (Izmantotais darbaspēks uz 1. zāļu mārciņu) * (Narkotika 1 mārciņa saražota) + (Darbaspēks uz vienu mārciņu narkotiku 2) * (Zāles 2 mārciņas saražotas) + ... (Darbaspēks, kas izmantots uz mārciņu narkotiku 6) * (Zāles 6 mārciņas saražots) uz pieejamo darbaspēku.

Tāpēc darbaspēka ierobežojums tiek novērtēts, saskaitot kopā formas (mainīgā šūna)*(konstante) nosacījumus un salīdzinot summas ar konstanti. Gan darbaspēka, gan izejvielu ierobežojums atbilst lineārā modeļa prasībām.

Mūsu pieprasījuma ierobežojumi izpaužas šādi

(Ražota 1. narkotika)<=(1. narkotiku pieprasījums)
(Saražotās narkotikas 2)<=(2. narkotiku pieprasījums)
§
(Ražotās< zāles 6)=(Narkotiku 6 pieprasījums)

Katrs pieprasījuma ierobežojums atbilst arī lineārā modeļa prasībām, jo katrs tiek novērtēts, saskaitot kopā formas nosacījumus (mainīgā šūna)*(konstante) un salīdzinot summas ar konstanti.

Parādījuši, ka mūsu produktu klāsta modelis ir lineārs modelis, kāpēc mums vajadzētu rūpēties?

• Ja risinātāja modelis ir lineārs un mēs atlasām Pieņemt lineāru modeli, risinātājs garantēti atradīs optimālu risinājumu risinātāja modelim. Ja risinātāja modelis nav lineārs, risinātājs var vai arī atrast optimālo risinājumu.
• Ja risinātāja modelis ir lineārs un mēs atlasām Pieņemt lineāru modeli, risinātājs izmanto ļoti efektīvu algoritmu (simpleksa metode), lai atrastu modeļa optimālo risinājumu. Ja risinātāja modelis ir lineārs un mēs neatlasām Pieņemt lineāro modeli, risinātājs izmanto ļoti neefektīvu algoritmu (GRG2 metode) un var būt grūtības atrast modeļa optimālo risinājumu.

Pēc tam, kad dialoglodziņā Risinātāja opcijas noklikšķinājām uz Labi, mēs atgriežamies pie galvenā risinātāja dialoglodziņa, kas parādīts iepriekš 27-7. attēlā. Noklikšķinot uz Risināt, risinātājs aprēķina optimālo risinājumu (ja tāds ir) mūsu produktu kombinācijas modelim. Kā es teicu 26. nodaļā, optimāls risinājums produktu maisījuma modelim būtu mainīgu šūnu vērtību kopums (katras zāles saražotās mārciņas), kas maksimāli palielina peļņu pār visu iespējamo risinājumu kopumu. Atkal īstenojams risinājums ir mainīgu šūnu vērtību kopums, kas atbilst visiem ierobežojumiem. Mainīgās šūnu vērtības, kas parādītas 27-9. attēlā, ir īstenojams risinājums, jo visi ražošanas līmeņi nav negatīvi, ražošanas līmeņi nepārsniedz pieprasījumu un resursu patēriņš nepārsniedz pieejamos resursus.

Mainīgās šūnu vērtības, kas parādītas 27-10. attēlā nākamajā lapā, ir neīstenojams risinājums šādu iemeslu dēļ:

• Mēs ražojam vairāk narkotiku 5 nekā pieprasījums pēc tā.
• Mēs izmantojam vairāk darbaspēka, nekā ir pieejams.
• Mēs izmantojam vairāk izejvielu, nekā ir pieejams.

Noklikšķinot uz Risināt, risinātājs ātri atrod optimālo risinājumu, kas parādīts 27-11. attēlā. Lai darblapā saglabātu optimālās risinājuma vērtības, ir jāatlasa Paturēt risinātāja risinājumu.

Mūsu zāļu uzņēmums var maksimāli palielināt savu ikmēneša peļņu 6,625.20 USD līmenī, ražojot 596.67 mārciņas narkotiku 4, 1084 mārciņas narkotiku 5 un nevienu no pārējām zālēm! Mēs nevaram noteikt, vai varam sasniegt maksimālo peļņu $6,625.20 citos veidos. Viss, par ko mēs varam būt pārliecināti, ir tas, ka ar mūsu ierobežotajiem resursiem un pieprasījumu šomēnes nav iespējams nopelnīt vairāk par 6,627.20 USD.

Vai risinātāja modelī vienmēr ir risinājums?

Pieņemsim, ka ir jāapmierina pieprasījums pēc katra produkta. (Skatiet faila Prodmix.xlsx darblapu Bez iespējamiem risinājumiem .) Pēc tam mums ir jāmaina pieprasījuma ierobežojumi no D2:I2<=D8:I8 uz D2:I2>=D8:I8. Lai to izdarītu, atveriet risinātāju, atlasiet ierobežojumu D2:I2<=D8:I8 un noklikšķiniet uz Mainīt. Tiek parādīts dialoglodziņš Ierobežojuma maiņa, kas parādīts 27-12. attēlā.

Atlasiet >= un pēc tam noklikšķiniet uz Labi. Tagad esam pārliecinājušies, ka risinātājs apsvērs iespēju mainīt tikai tās šūnu vērtības, kas atbilst visām prasībām. Noklikšķinot uz Risināt, tiek rādīts ziņojums "Risinātājs nevarēja atrast īstenojamu risinājumu". Šis vēstījums nenozīmē, ka mēs kļūdījāmies savā modelī, bet drīzāk to, ka ar mūsu ierobežotajiem resursiem mēs nevaram apmierināt pieprasījumu pēc visiem produktiem. Risinātājs vienkārši mums saka, ka, ja mēs vēlamies apmierināt pieprasījumu pēc katra produkta, mums ir jāpievieno vairāk darbaspēka, vairāk izejvielu vai vairāk no abiem.

Ko nozīmē tas, ka risinātāja modelis dod rezultātu, kuras kopas vērtības nesaplūst?

Redzēsim, kas notiks, ja mēs pieļaujam neierobežotu pieprasījumu pēc katra produkta un mēs ļaujam ražot negatīvu daudzumu no katras zāles. (Šī risinātāja problēma ir redzama failu Prodmix.xlsx darblapā Iestatīt vērtības Nesaplūst .) Lai atrastu optimālu risinājumu šādai situācijai, atveriet Risinātājs, noklikšķiniet uz pogas Opcijas un notīriet rūtiņu Pieņemt, ka nav negatīvs. Dialoglodziņā Risinātāja parametri atlasiet pieprasījuma ierobežojumu D2:I2<=D8:I8 un pēc tam noklikšķiniet uz Dzēst, lai noņemtu ierobežojumu. Kad noklikšķināt uz Risināt, Risinātājs atgriež ziņojumu "Šūnu vērtību iestatīšana nesaplūst." Šis ziņojums nozīmē, ka, lai maksimāli palielinātu mērķa šūnu (kā mūsu piemērā), ir iespējami risinājumi ar patvaļīgi lielām mērķa šūnas vērtībām. (Ja mērķa šūna ir jāsamazina, ziņojums "Šūnu vērtību iestatīšana nesaplūst" nozīmē, ka ir iespējami risinājumi ar patvaļīgi mazām mērķa šūnas vērtībām.) Mūsu situācijā, pieļaujot negatīvu zāļu ražošanu, mēs faktiski "radām" resursus, kurus var izmantot, lai patvaļīgi ražotu lielu daudzumu citu narkotiku. Ņemot vērā mūsu neierobežoto pieprasījumu, tas ļauj mums gūt neierobežotu peļņu. Reālā situācijā mēs nevaram nopelnīt bezgalīgu naudas summu. Īsāk sakot, ja redzat "Iestatītās vērtības nesaplūst", modelī ir kļūda.

Problēmas

1. Pieņemsim, ka mūsu zāļu kompānija var iegādāties līdz 500 stundām darbaspēka par 1 USD vairāk stundā nekā pašreizējās darbaspēka izmaksas. Kā mēs varam maksimāli palielināt peļņu?

2. Mikroshēmu ražošanas rūpnīcā četri tehniķi (A, B, C un D) ražo trīs produktus (1., 2. un 3. produktu). Šomēnes mikroshēmu ražotājs var pārdot 80 1. produkta vienības, 50 2. produkta vienības un ne vairāk kā 50 3. produkta vienības. Tehniķis A var izgatavot tikai 1. un 3. produktu. Tehniķis B var izgatavot tikai 1. un 2. produktu. Tehniķis C var izgatavot tikai 3. produktu. Tehniķis D var izgatavot tikai 2. produktu. Par katru saražoto vienību produkti dod šādu peļņu: 1. produkts, 6 USD; 2. produkts, 7 USD; un 3. produkts, 10 USD. Laiks (stundās), kas katram tehniķim nepieciešams, lai ražotu produktu, ir šāds:

   Produkts	Tehniķis A	Tehniķis B	Tehniķis C	Tehniķis D
   1	2	2,5	Nevar to izdarīt	Nevar to izdarīt
   2	Nevar to izdarīt	3	Nevar to izdarīt	3,5
   3	3	Nevar to izdarīt	4	Nevar to izdarīt

3. Katrs tehniķis var strādāt līdz 120 stundām mēnesī. Kā mikroshēmu ražotājs var maksimāli palielināt savu ikmēneša peļņu? Pieņemsim, ka var saražot vienību skaitu daļskaitā.

4. Datoru ražošanas rūpnīca ražo peles, tastatūras un videospēļu kursorsviras. Peļņa uz vienu vienību, darbaspēka lietojums uz vienu vienību, ikmēneša pieprasījums un mašīnas laika lietojums uz vienu vienību ir norādīti šajā tabulā:

   	Peles	Tastatūras	Kursorsviras
   Peļņa/vienība	8 ASV dolāri	$11	9 ASV dolāri
   Darbaspēka patēriņš/vienība	.2 stundas	.3 stunda	.24 stunda
   Mašīnas laiks/vienība	.04 stunda	.055 stunda	.04 stunda
   Ikmēneša pieprasījums	15 000	27,000	11,000

5. Katru mēnesi ir pieejamas kopumā 13,000 darba stundas un 3000 stundas mašīnas laika. Kā ražotājs var maksimāli palielināt savu ikmēneša peļņas iemaksu no rūpnīcas?

6. Atrisināt mūsu narkotiku piemēru, pieņemot, ka ir jāapmierina minimālais pieprasījums 200 vienības katrai narkotikai.

7. Džeisons izgatavo dimanta rokassprādzes, kaklarotas un auskarus. Viņš vēlas strādāt ne vairāk kā 160 stundas mēnesī. Viņam ir 800 unces dimantu. Peļņa, darba laiks un dimantu unces, kas nepieciešamas katra produkta ražošanai, ir norādīti zemāk. Ja pieprasījums pēc katra produkta ir neierobežots, kā Džeisons var maksimāli palielināt savu peļņu?

   Produkts	Vienības peļņa	Darba stundas vienībā	Dimantu unces vienībā
   Aproce	300 €	.35	1.2
   Kaklarota	200 €	.15	.75
   Auskari	100 €	0.05	.5