Utilizarea Rezolvitorului pentru a determina combinația optimă de produse

Cum pot determina combinația lunară de produse care maximizează profitabilitatea?

Companiile trebuie adesea să determine cantitatea din fiecare produs pentru a se produce lunar. În forma sa cea mai simplă, problema amestec de produse implică modul de a determina cantitatea de fiecare produs care ar trebui să fie produse într-o lună pentru a maximiza profiturile. Combinația de produse trebuie, de obicei, să respecte următoarele restricții:

• Combinația de produse nu poate utiliza mai multe resurse decât sunt disponibile.

• Există o cerere limitată pentru fiecare produs. Nu putem produce mai mult de un produs în timpul unei luni decât cererea dictează, deoarece producția în exces este pierdută (de exemplu, un medicament perisabil).

Să rezolvăm acum următorul exemplu de problemă cu combinația de produse. Puteți găsi soluția la această problemă în fișierul Prodmix.xlsx, afișat în Figura 27-1.

Să presupunem că lucrăm pentru o companie de droguri care produce șase produse diferite în fabrica lor. Producția fiecărui produs necesită muncă și materie primă. Rândul 4 din figura 27-1 arată orele de lucru necesare pentru a produce o jumătate de kilogram din fiecare produs, iar rândul 5 afișează livrele de materie primă necesare pentru a produce o livră din fiecare produs. De exemplu, producerea unei livre de Produs 1 necesită șase ore de muncă și 3,2 kg de materie primă. Pentru fiecare medicament, prețul per livră este dat în rândul 6, costul unitar pe livră este dat în rândul 7, iar contribuția profitului per liră este dată în rândul 9. De exemplu, Produsul 2 se vinde cu 11,00 lei per liră, suportă un cost unitar de 5,70 lei per liră și contribuie cu 5,30 lei profit per liră. Cererea lunii pentru fiecare medicament este dat în rândul 8. De exemplu, cererea pentru Produsul 3 este de 1041 de lire sterline. Luna aceasta sunt disponibile 4500 de ore de muncă și 1600 de lire sterline de materie primă. Cum poate această firmă să-și maximizeze profitul lunar?

Dacă nu am fi știut nimic despre Rezolvitorul Excel, am fi atacat această problemă construind o foaie de lucru pentru a urmări profitul și utilizarea resurselor asociate cu combinația de produse. Apoi, ne-ar folosi de încercare și de eroare pentru a varia se amestecă de produs pentru a optimiza profitul fără a utiliza mai multă muncă sau materii prime decât este disponibil, și fără a produce nici un medicament în exces de cerere. Utilizăm Rezolvitorul în acest proces doar în etapa de încercare și eroare. În esență, Rezolvitorul este un motor de optimizare care efectuează fără probleme căutarea de încercări și erori.

O cheie pentru rezolvarea problemei mixului de produse este să calculați eficient utilizarea resurselor și profitul asociat cu orice combinație de produse dată. Un instrument important pe care îl putem utiliza pentru a face acest calcul este funcția SUMPRODUCT. Funcția SUMPRODUCT înmulțește valorile corespondente din zonele de celule și returnează suma acestor valori. Fiecare zonă de celule utilizată într-o evaluare SUMPRODUCT trebuie să aibă aceleași dimensiuni, ceea ce înseamnă că puteți utiliza SUMPRODUCT cu două rânduri sau două coloane, dar nu cu o coloană și un rând.

Ca exemplu de utilizare a funcției SUMPRODUCT în combinația noastră de produse, să încercăm să calculăm utilizarea resurselor. Utilizarea muncii noastre este calculată prin

(Forța de muncă utilizată pe jumătate de kilogram de droguri 1)*(Droguri 1 livre produse)+
(Forța de muncă utilizată pe livră de droguri 2)*(Droguri 2 livre produse) + ...
(Forța de muncă utilizată pe jumătate de kilogram de droguri 6)*(Droguri 6 livre produse)

Am putea calcula utilizarea forței de muncă într-o manieră mai obositoare ca D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. În mod similar, utilizarea materiilor prime poate fi calculată ca D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Totuși, introducerea acestor formule într-o foaie de lucru pentru șase produse consumă mult timp. Imaginați-vă cât ar dura dacă ați lucra cu o firmă care a produs, de exemplu, 50 de produse la uzina lor. O modalitate mult mai ușoară de a calcula munca și utilizarea materiilor prime este să copiați de la D14 la D15 formula SUMPRODUCT($D$2:$I$2,D4:I4). Această formulă calculează D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (care este utilizarea muncii), dar este mult mai ușor de introdus! Observați că utilizez semnul $ cu zona D2:I2, astfel încât, atunci când copiez formula, să captez totuși combinația de produse din rândul 2. Formula din celula D15 calculează utilizarea materiilor prime.

Într-o manieră similară, profitul nostru este determinat de

(Droguri 1 profit per livră)*(Droguri 1 livră produsă) +
(Droguri 2 profit pe livră)*(Droguri 2 lire produse) + ...
(Droguri 6 profit pe livră)*(Droguri 6 lire produse)

Profitul se calculează cu ușurință în celula D12 cu formula SUMPRODUCT(D9:I9,$D$2:$I$2).

Acum putem identifica cele trei componente ale modelului rezolvitor din gama noastră de produse.

• Celula țintă. Obiectivul nostru este să maximizăm profitul (calculat în celula D12).

• Celule modificabile. Numărul de livre produse pentru fiecare produs (listat în zona de celule D2:I2)

• Constrângeri. Avem următoarele restricții:

  • Nu folosiți mai multă muncă sau materie primă decât este disponibilă. Mai exact, valorile din celulele D14:D15 (resursele utilizate) trebuie să fie mai mici sau egale cu valorile din celulele F14:F15 (resursele disponibile).

  • Nu produc mai mult de un medicament decât este în cerere. Aceasta înseamnă că valorile din celulele D2:I2 (livre produse din fiecare medicament) trebuie să fie mai mici sau egale cu cererea pentru fiecare medicament (listate în celulele D8:I8).

  • Nu putem produce o cantitate negativă de droguri.

Vă voi arăta cum să introduceți celula țintă, cum să modificați celulele și restricțiile în Rezolvitor. Apoi tot ce trebuie să faceți este să faceți clic pe butonul Rezolvare pentru a găsi o combinație de produse care să maximizeze profitul!

Pentru a începe, faceți clic pe fila Date, apoi, în grupul Analiză, faceți clic pe Rezolvitor.

Va apărea caseta de dialog Parametri Rezolvitor, așa cum se arată în Figura 27-2.

Faceți clic pe caseta Setare celulă țintă, apoi selectați celula noastră de profit (celula D12). Click the By Changing Cells box and then point to the range D2:I2, which contains the livre produced of each drug. Caseta de dialog ar trebui să arate acum figura 27-3.

Acum suntem gata să adăugăm restricții la model. Faceți clic pe butonul Adăugare. Veți vedea caseta de dialog Adăugare restricție, afișată în Figura 27-4.

Pentru a adăuga restricțiile de utilizare a resurselor, faceți clic pe caseta Referință celulă, apoi selectați zona D14:D15. Selectați <= din lista din mijloc. Faceți clic pe caseta Restricție, apoi selectați zona de celule F14:F15. Caseta de dialog Adăugare restricție ar trebui să arate acum ca în Figura 27-5.

Acum ne-am asigurat că, atunci când Rezolvitorul încearcă valori diferite pentru celulele modificabile, vor fi luate în considerare doar combinațiile care satisfac D14<=F14 (munca utilizată este mai mică sau egală cu munca disponibilă) și D15<=F15 (materiile prime utilizate sunt mai mici sau egale cu materiile prime disponibile). Faceți clic pe Adăugare pentru a introduce restricțiile de cerere. Completați caseta de dialog Adăugare restricție, așa cum se arată în Figura 27-6.

Adăugarea acestor restricții asigură faptul că, atunci când Rezolvitorul încearcă combinații diferite pentru valorile celulelor modificatorii, vor fi luate în considerare numai combinațiile care satisfac următorii parametri:

• D2<=D8 (cantitatea produsă de droguri 1 este mai mică sau egală cu cererea de droguri 1)

• E2<=E8 (cantitatea de droguri produsă 2 este mai mică sau egală cu cererea de droguri 2)

• F2<=F8 (cantitatea produsă de droguri 3 făcute este mai mică sau egală cu cererea de droguri 3)

• G2<=G8 (cantitatea produsă de droguri 4 făcute este mai mică sau egală cu cererea de droguri 4)

• H2<=H8 (cantitatea produsă de droguri 5 făcute este mai mică sau egală cu cererea de droguri 5)

• I2<=I8 (cantitatea produsă de droguri 6 făcute este mai mică sau egală cu cererea de droguri 6)

Faceți clic pe OK în caseta de dialog Adăugare restricție. Fereastra Rezolvitor ar trebui să arate ca în Figura 27-7.

Introducem restricția potrivit căreia celulele modificabile trebuie să fie negative în caseta de dialog Opțiuni Rezolvitor. Faceți clic pe butonul Opțiuni din caseta de dialog Parametri Rezolvitor. Bifați caseta Presupune un model liniar și caseta Presupune non-negativ, așa cum se arată în Figura 27-8 de pe pagina următoare. Faceți clic pe OK.

Bifarea casetei Presupunere non-negativă asigură faptul că Rezolvitorul ia în considerare numai combinațiile de celule modificabile în care fiecare celulă modificară presupune o valoare non-negativă. Am bifat caseta Presupune modelul liniar, deoarece problema combinației de produse este un tip special de problemă cu Rezolvitorul, numit model liniar. În esență, un model Rezolvitor este liniar în următoarele condiții:

• Celula țintă se calculează prin adunarea termenilor formularului (celulă modificantă)*(constantă).

• Fiecare restricție satisface "cerința de model liniar". Aceasta înseamnă că fiecare restricție este evaluată prin adunarea termenilor formularului (celulă modificabilă)*(constantă) și compararea sumelor cu o constantă.

De ce este liniară această problemă cu Rezolvitorul? Celula noastră țintă (profitul) se calculează ca

(Droguri 1 profit per livră)*(Droguri 1 livră produsă) +
(Droguri 2 profit pe livră)*(Droguri 2 lire produse) + ...
(Droguri 6 profit pe livră)*(Droguri 6 lire produse)

Această calcul urmează un model în care valoarea celulei țintă este derivată prin adunarea termenilor formularului (celulă modificantă)*(constantă).

Constrângerea noastră de muncă este evaluată prin compararea valorii derivate din (Munca utilizată pe livră de droguri 1)*(Droguri 1 livre produse) + (Munca utilizată pe jumătate de kilogram de droguri 2)*(Droguri 2 livre produse)+ ... (Muncii need pe jumătate de kilogram de droguri 6)*(Droguri 6 lire sterline produse) la munca disponibilă.

Prin urmare, constrângerea de muncă este evaluată prin adunarea termenilor formularului (celulă modificabilă)*(constantă) și compararea sumelor cu o constantă. Atât constrângerea forței de muncă, cât și constrângerea materiei prime satisfac cerința modelului liniar.

Constrângerile noastre de cerere iau forma

(Droguri 1 produse)<=(Droguri 1 Cerere)
(Droguri 2 produse)<=(Droguri 2 Cerere)
§
(Droguri 6 produse)<=(Consumul de droguri 6)

Fiecare restricție de cerere satisface, de asemenea, cerința de model liniar, deoarece fiecare este evaluat prin adunarea termenilor formularului (celulă modificabilă)*(constantă) și compararea sumelor cu o constantă.

După ce am arătat că modelul nostru de mixare de produse este un model liniar, de ce ar trebui să ne pese?

• Dacă un model Rezolvitor este liniar și selectăm Presupune modelul liniar, Rezolvitorului i se garantează că va găsi soluția optimă pentru modelul Rezolvitor. Dacă un model Rezolvitor nu este liniar, Rezolvitorul poate găsi sau nu soluția optimă.

• Dacă un model Rezolvitor este liniar și selectăm Presupune modelul liniar, Rezolvitorul utilizează un algoritm foarte eficient (metoda simplex) pentru a găsi soluția optimă a modelului. Dacă un model Rezolvitor este liniar și nu selectăm Presupune modelul liniar, Rezolvitorul utilizează un algoritm foarte ineficient (metoda GRG2) și poate întâmpina dificultăți în găsirea soluției optime a modelului.

După ce faceți clic pe OK în caseta de dialog Opțiuni Rezolvitor, revenim la caseta de dialog principală Rezolvitor, afișată mai devreme în Figura 27-7. Atunci când facem clic pe Rezolvare, Rezolvitorul calculează o soluție optimă (dacă există una) pentru modelul nostru de combinație de produse. După cum am menționat în capitolul 26, o soluție optimă pentru modelul de amestec de produse ar fi un set de valori de celule în schimbare (livre produse de fiecare medicament), care maximizează profitul peste setul de toate soluțiile fezabile. Din nou, o soluție fezabilă este un set de valori de celule modificabile care satisfac toate restricțiile. Valorile celulelor modificabile prezentate în figura 27-9 sunt o soluție fezabilă, deoarece toate nivelurile de producție sunt negative, nivelurile de producție nu depășesc cererea, iar utilizarea resurselor nu depășește resursele disponibile.

Valorile din celulele modificabile afișate în figura 27-10 de pe pagina următoare reprezintă o soluție infeasibilă din următoarele motive:

• Producem mai mult drog decât cererea pentru el.

• Folosim mai multă muncă decât ceea ce este disponibil.

• Folosim mai multă materie primă decât cea disponibilă.

După ce faceți clic pe Rezolvare, Rezolvitorul găsește rapid soluția optimă afișată în Figura 27-11. Trebuie să selectați Păstrați soluția Rezolvitor pentru a păstra valorile optime de soluții din foaia de lucru.

Compania noastra de droguri poate maximiza profitul său lunar la un nivel de $6,625.20 prin producerea 596.67 kg de droguri 4, 1084 de lire sterline de droguri 5, și nici unul dintre celelalte medicamente! Nu putem determina dacă putem obține profitul maxim de 6.625,20 USD în alte moduri. Tot ce putem fi siguri este că, cu resursele noastre limitate și cererea, nu există nici o modalitate de a face mai mult de 6627.20 dolari în această lună.