Utilizarea Rezolvitorului pentru a determina mixul optim de produse

Acest articol tratează utilizarea Rezolvitorului, un program de completare Microsoft Excel pe care îl puteți utiliza pentru analiza de tip "ce ar fi dacă", pentru a determina o combinație optimă de produse.

Cum pot determina mixul lunar de produse care maximizează profitabilitatea?

Companiile trebuie adesea să determine cantitatea fiecărui produs de produs lunar. În forma sa cea mai simplă, problema mixului de produse implică modul de determinare a cantității fiecărui produs care ar trebui să fie produs pe parcursul unei luni pentru a maximiza profiturile. Amestecul de produse trebuie să respecte de obicei 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 dintr-un produs într-o lună decât dictează solicitarea, deoarece producția în exces este irosită (de exemplu, un medicament perisabil).

Să rezolvăm acum următorul exemplu cu problema amestecului de produse. Găsiți soluția pentru această problemă în Prodmix.xlsx de fișier, afișată în Figura 27-1.

Să presupunem că lucrăm pentru o companie farmaceutică care produce șase produse diferite la fabrica lor. Producția fiecărui produs necesită forță de muncă și materie primă. Rândul 4 din Figura 27-1 arată orele de muncă necesare pentru a produce un kilogram din fiecare produs, iar rândul 5 arată kilogramele de materie primă necesare pentru a produce un kilogram din fiecare produs. De exemplu, producerea unui kilogram de Produsul 1 necesită șase ore de muncă și 3,2 kg de materie primă. Pentru fiecare medicament, prețul pe livră este dat în rândul 6, costul unitar pe livră este dat în rândul 7, iar contribuția la profit pe livră este dată în rândul 9. De exemplu, produsul 2 se vinde cu 11,00 lei pe livră, atrage un cost unitar de 5,70 lei pe livră și contribuie cu un profit de 5,30 lei pe livră. Cererea lunară pentru fiecare medicament este prezentată î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 kilograme de materie primă. Cum poate această companie să-și maximizeze profitul lunar?

Dacă nu am ști nimic despre Caracteristica de soluționare Excel, am ataca această problemă construind o foaie de lucru pentru a urmări profitul și utilizarea resurselor asociată combinației de produse. Apoi am folosi încercări și erori pentru a varia mixul de produse pentru a optimiza profitul fără a folosi mai multă forță de muncă sau materie primă decât este disponibilă și fără a produce niciun medicament în exces de cerere. Utilizăm Rezolvitorul în acest proces doar în etapa de încercare și eroare. În esență, Rezolvitor este un motor de optimizare care efectuează impecabil căutarea prin încercare și eroare.

O cheie pentru rezolvarea problemei mixului de produse este calcularea eficientă a utilizării resurselor și a profitului asociat cu orice mix 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 acelor 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 singură coloană și un rând.

Ca un exemplu despre cum putem utiliza funcția SUMPRODUCT în exemplul cu combinația de produse, să încercăm să calculăm utilizarea resurselor. Utilizarea forței de muncă este calculată prin

(Muncă folosită pe kilogram de medicament 1)*(Medicament produs 1 kilogram)+
(Muncă folosită pe kilogram de medicament 2)* (Medicament produs 2 kilograme) + ...
(Forță de muncă folosită pe kilogram de medicament 6)* (Medicament produs 6 kilograme)

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 materiei prime ar putea fi calculată ca D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Cu toate acestea, introducerea acestor formule într-o foaie de lucru pentru șase produse consumă mult timp. Imaginați-vă cât timp ar dura dacă ați lucra cu o companie care produce, de exemplu, 50 de produse la fabrica lor. O modalitate mult mai simplă de a calcula munca și utilizarea materiilor prime este să copiați din D14 în 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 noastră pentru muncă), 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ă capturez în continuare amestecul de produse din rândul 2. Formula din celula D15 calculează utilizarea materiei prime.

În mod similar, profitul nostru este determinat de

(Drog 1 profit pe livră)*(Drog 1 kilogram produs) +
(Drog 2 profit pe livră)*(Drog 2 kilograme produse) + ...
(Medicament 6 profit pe livră)* (Medicament 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 mix de produse.

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

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

• Restricții. Avem următoarele restricții:

  • Nu utilizați mai multă forță de muncă sau materie primă decât cea disponibilă. Adică, valorile din celulele D14:D15 (resursele utilizate) trebuie să fie mai mici sau egale cu valorile din celulele F14:F15 (resursele disponibile).
  • Nu produceți mai mult dintr-un medicament decât este solicitat. Adică, valorile din celulele D2:I2 (kilograme produse din fiecare medicament) trebuie să fie mai mici sau egale cu cererea pentru fiecare medicament (enumerate în celulele D8:I8).
  • Nu putem produce o cantitate negativă din niciun medicament.

Vă voi arăta cum să introduceți celula țintă, celulele modificatoare și restricțiile în Rezolvitor. Apoi tot ce trebuie să faceți este să faceți clic pe butonul Rezolvare pentru a găsi un mix de produse care maximizează 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). Faceți clic pe caseta Prin modificarea celulelor, apoi indicați spre zona D2:I2, care conține kilogramele produse din fiecare medicament. 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 de restricții, apoi selectați zona de celule F14:F15. Caseta de dialog Adăugare restricție ar trebui să arate acum ca 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 atât D14<=F14 (forța de muncă utilizată este mai mică sau egală cu forța de muncă disponibilă), cât și D15<=F15 (materia primă utilizată este mai mică sau egală cu materia primă disponibilă). 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 modificabile, vor fi luate în considerare doar combinațiile care satisfac următorii parametri:

• D2<=D8 (cantitatea produsă de Medicamentul 1 este mai mică sau egală cu cererea pentru Medicamentul 1)
• E2< = E8 (cantitatea produsă de Medicament 2 este mai mică sau egală cu cererea pentru Medicamentul 2)
• F2<=F8 (cantitatea produsă de Medicamentul 3 este mai mică sau egală cu cererea pentru Medicamentul 3)
• G2< = G8 (cantitatea produsă de medicamentul 4 este mai mică sau egală cu cererea pentru medicamentul 4)
• H2< = H8 (cantitatea produsă de medicamentul 5 este mai mică sau egală cu cererea pentru medicamentul 5)
• I2< = I8 (cantitatea produsă de medicamentul 6 produs este mai mică sau egală cu cererea pentru medicamentul 6)

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

Introducem restricția conform căreia celulele modificabile trebuie să nu 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 Presupunere model liniar și caseta Presupunere non-negativ, așa cum se arată în Figura 27-8 de pe pagina următoare. Faceți clic pe OK.

Bifarea casetei Se presupune că nu este negativ asigură că Rezolvitorul ia în considerare doar combinațiile de celule modificabile în care fiecare celulă modificabilă presupune o valoare pozitivă. Am bifat caseta Presupunere model liniar, deoarece problema amestecului de produse este un tip special de problemă Rezolvitor, denumit model liniar. În esență, un model Rezolvitor este liniar în următoarele condiții:

• Celula-țintă este calculată prin adunarea termenilor formei (celulă modificabilă)*(constantă).
• Fiecare restricție satisface "cerința modelului liniar". Aceasta înseamnă că fiecare restricție este evaluată prin adunarea termenilor formei (celulă modificabilă)*(constantă) și compararea sumelor cu o constantă.

De ce problema din Rezolvitor este liniară? Celula noastră țintă (profit) este calculată ca

(Drog 1 profit pe livră)*(Drog 1 kilogram produs) +
(Drog 2 profit pe livră)*(Drog 2 kilograme produse) + ...
(Medicament 6 profit pe livră)* (Medicament 6 lire produse)

Acest calcul urmează un model în care valoarea celulei-țintă este derivată prin adunarea termenilor de formă (celulă modificabilă)*(constantă).

Constrângerea noastră de muncă este evaluată prin compararea valorii derivate din (Forța de muncă utilizată pe kilogram de Medicament 1)* (Medicament 1 kilogram produs) + (Forța de muncă utilizată pe kilogram de Medicament 2)* (Medicament 2 lire produse)+ ... (Forță de muncă folosită pe kilogram de Medicament 6) * (Medicament 6 lire produse) la forța de muncă disponibilă.

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

Restricțiile noastre legate de cerere iau forma

(Medicamentul 1 produs)<=(Cerere de medicament 1)
(Medicamentul 2 produs)<=(Cerere de medicament 2)
§
(Medicamentul 6 produs)<=(Cererea de droguri 6)

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

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

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

După ce facem clic pe OK în caseta de dialog Opțiuni Rezolvitor, revenim la caseta de dialog principală Rezolvitor, prezentată mai devreme în Figura 27-7. Când facem clic pe Rezolvare, Rezolvitor calculează o soluție optimă (dacă există) pentru modelul nostru de mix de produse. După cum am spus în capitolul 26, o soluție optimă pentru modelul de amestec de produse ar fi un set de valori celulare în schimbare (kilograme produse din fiecare medicament) care maximizează profitul față de setul tuturor soluțiilor fezabile. Din nou, o soluție fezabilă este un set de valori de celulă modificabile care satisfac toate restricțiile. Valorile celulelor în schimbare prezentate în Figura 27-9 sunt o soluție fezabilă, deoarece toate nivelurile de producție sunt pozitive, nivelurile de producție nu depășesc cererea, iar utilizarea resurselor nu depășește resursele disponibile.

Valorile celulelor modificabile afișate în Figura 27-10 de pe pagina următoare reprezintă o soluție imposibil de făcut din următoarele motive:

• Producem mai mult Drug 5 decât cererea pentru el.
• Folosim mai multă forță de muncă decât cea 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ăstrare soluție Rezolvitor pentru a păstra valorile optime ale soluției în foaia de lucru.

Compania noastră farmaceutică își poate maximiza profitul lunar la un nivel de 6.625,20 USD prin producerea de 596,67 lire sterline de Drug 4, 1084 de kilograme de Drug 5 și niciunul 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 și cererea noastră limitată, nu există nicio modalitate de a câștiga mai mult de 6.627,20 USD luna aceasta.

Are un model de Rezolvitor întotdeauna o soluție?

Să presupunem că cererea pentru fiecare produs trebuie satisfăcută. (Consultați foaia de lucru "Nicio soluție fezabilă " din fișier Prodmix.xlsx.) Apoi trebuie să modificăm restricțiile legate de cerere de la D2:I2<=D8:I8 la D2:I2>=D8:I8. Pentru a face acest lucru, deschideți Rezolvitorul, selectați restricția D2:I2<=D8:I8, apoi faceți clic pe Modificare. Apare caseta de dialog Modificare restricție, afișată în Figura 27-12.

Selectați >=, apoi faceți clic pe OK. Acum ne-am asigurat că Rezolvitor va lua în considerare modificarea numai a valorilor celulelor care îndeplinesc toate cerințele. Atunci când faceți clic pe Rezolvare, veți vedea mesajul "Rezolvitorul nu a putut găsi o soluție fezabilă". Acest mesaj nu înseamnă că am făcut o greșeală în modelul nostru, ci mai degrabă că, cu resursele noastre limitate, nu putem satisface cererea pentru toate produsele. Rezolvitorul ne spune pur și simplu că, dacă dorim să satisfacem cererea pentru fiecare produs, trebuie să adăugăm mai multă forță de muncă, mai multe materii prime sau mai multe din ambele.

Ce înseamnă dacă un model Rezolvitor dă rezultatul Setului de valori nu converge?

Să vedem ce se întâmplă dacă permitem cererea nelimitată pentru fiecare produs și permitem producerea de cantități negative din fiecare medicament. (Puteți vedea această problemă din Rezolvitor în foaia de lucru Setare valori nu converg din fișier Prodmix.xlsx.) Pentru a găsi soluția optimă pentru această situație, deschideți Rezolvitorul, faceți clic pe butonul Opțiuni și debifați caseta Se presupune că nu este negativ. În caseta de dialog Parametri Rezolvitor, selectați restricția de cerere D2:I2<=D8:I8, apoi faceți clic pe Ștergere pentru a elimina restricția. Când faceți clic pe Rezolvare, Rezolvitorul returnează mesajul "Setarea valorilor celulelor nu converge". Acest mesaj înseamnă că, dacă celula-țintă trebuie maximizată (ca în exemplul nostru), există soluții fezabile cu valori arbitrar de mari ale celulelor-țintă. (Dacă celula-țintă trebuie minimizată, mesajul "Setare valori celulă nu converge" înseamnă că există soluții fezabile cu valori arbitrar de mici ale celulelor-țintă.) În situația noastră, permițând producția negativă a unui medicament, de fapt "creăm" resurse care pot fi folosite pentru a produce cantități arbitrar mari de alte droguri. Având în vedere cererea noastră nelimitată, acest lucru ne permite să obținem profituri nelimitate. Într-o situație reală, nu putem câștiga o sumă infinită de bani. Pe scurt, dacă vedeți "Setarea valorilor nu converg", modelul dvs. are o eroare.

Probleme

1. Să presupunem că compania noastră farmaceutică poate cumpăra până la 500 de ore de muncă cu 1 dolar mai mult pe oră decât costurile actuale ale forței de muncă. Cum putem maximiza profitul?

2. La o fabrică de fabrică de cipuri, patru tehnicieni (A, B, C și D) produc trei produse (produsele 1, 2 și 3). Luna aceasta, producătorul cipurilor poate vinde 80 de unități din Produsul 1, 50 de unități din Produsul 2 și cel mult 50 de unități din Produsul 3. Tehnicianul A poate fabrica numai produsele 1 și 3. Tehnicianul B poate fabrica numai produsele 1 și 2. Tehnicianul C poate face numai Produsul 3. Tehnicianul D poate face numai Produsul 2. Pentru fiecare unitate produsă, produsele contribuie cu următorul profit: Produsul 1, 6 USD; Produsul 2, 7 USD; și Produsul 3, 10 USD. Timpul (în ore) de care are nevoie fiecare tehnician pentru a fabrica un produs este următorul:

   Produs	Tehnician A	Tehnician B	Tehnician C	Tehnician D
   1	2	2,5	Nu se poate face	Nu se poate face
   2	Nu se poate face	3	Nu se poate face	3,5
   3	3	Nu se poate face	4	Nu se poate face

3. Fiecare tehnician poate lucra până la 120 de ore pe lună. Cum poate producătorul de cipuri să-și maximizeze profitul lunar? Să presupunem că se poate produce un număr fracționar de unități.

4. O fabrică producătoare de computere produce mouse-uri, tastaturi și joystickuri pentru jocuri video. Profitul pe unitate, utilizarea forței de muncă pe unitate, cererea lunară și utilizarea pe unitate a timpului de utilizare a mașinilor sunt prezentate în tabelul următor:

   	Mouse-uri	Tastaturi	Joystickuri
   Profit/unitate	8 USD	11 USD	9 USD
   Utilizarea forței de muncă/unitate	0,2 oră	0,3 oră	0,24 oră
   Timp/unitate mașină	0,04 oră	0,055 oră	0,04 oră
   Cerere lunară	15.000	27,000	11,000

5. În fiecare lună, sunt disponibile un total de 13.000 de ore de muncă și 3.000 de ore de lucru pentru mașină. Cum poate producătorul să-și maximizeze contribuția la profitul lunar din fabrică?

6. Rezolvați exemplul nostru de medicament presupunând că trebuie îndeplinită o cerere minimă de 200 de unități pentru fiecare medicament.

7. Jason face brățări, coliere și cercei cu diamante. Vrea să lucreze maximum 160 de ore pe lună. Are 800 de uncii de diamante. Profitul, timpul de muncă și unciile de diamante necesare pentru a produce fiecare produs sunt date mai jos. Dacă cererea pentru fiecare produs este nelimitată, cum își poate maximiza Jason profitul?

   Produs	Profit unitar	Ore de muncă pe unitate	Uncii de romb pe unitate
   Brățară	300 lei	.35	1,2
   Colier	200 lei	.15	.75
   Cercei	100 lei	.05	.5