Utilizarea Rezolvitorului pentru a determina amestecul optim de produse

Cum pot determina amestecul lunar de produse care maximizează rentabilitatea?

Adesea, companiile trebuie să determine cantitatea fiecărui produs pe care să-l producă lunar. În forma sa cea mai simplă, problema Mix de produs implică modul de determinare a volumului fiecărui produs care ar trebui să fie produs timp de o lună pentru a maximiza profiturile. Amestecul de produse trebuie, de obicei, să respecte următoarele restricții:

• Mix produs nu poate utiliza mai multe resurse decât sunt disponibile.

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

Să rezolvăm acum exemplul următor al problemei amestecării produsului. 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 firmă de droguri care produce șase produse diferite la uzina lor. Producția fiecărui produs necesită muncă și material brut. Rândul 4 din figura 27-1 Arată orele de lucru necesare pentru a produce o Liră din fiecare produs, iar rândul 5 afișează kilogramele de materii prime necesare pentru a produce o Liră din fiecare produs. De exemplu, producția unui kilogram de produs 1 necesită șase ore de muncă și 3,2 de lire sterline de materie primă. Pentru fiecare medicament, prețul per lira este dat în rândul 6, costul unitar per lira este dat în rândul 7, iar contribuția profitului per lira este dată în rândul 9. De exemplu, Product 2 se vinde pentru $11,00 per lira, injure un cost unitar de $5,70 per lira și contribuie cu $5,30 profit per lira. Cererea lunară pentru fiecare medicament este dată în rândul 8. De exemplu, cererea pentru Product 3 este 1041 de lire sterline. În această lună, sunt disponibile 4500 de ore de muncă și 1600 de lire sterline de materii prime. Cum poate această firmă să își maximizeze profitul lunar?

Dacă nu am știut nimic despre Excel Solver, am ataca această problemă prin construirea unei foi de lucru pentru a urmări profitul și utilizarea resurselor asociate cu amestecul de produse. Apoi ne-ar folosi de încercare și de eroare pentru a varia amestecul de produse pentru a optimiza profitul fără a utiliza mai multă muncă sau materii prime decât este disponibil și fără a produce niciun medicament care depășește cererea. Utilizăm Rezolvitorul în acest proces doar la etapa de evaluare și eroare. În esență, Rezolvitorul este un motor de optimizare care efectuează în mod impecabil căutarea de încercare și eroare.

O cheie pentru a rezolva problema amestecării produsului este să calculați eficient utilizarea resurselor și profitul asociat cu orice Mix de produs 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 intervalele de celule și returnează suma valorilor respective. 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 și cu o coloană și un rând.

Ca exemplu de modul în care putem utiliza funcția SUMPRODUCT în exemplul nostru mix de produse, să încercăm să calculăm utilizarea resurselor. Utilizarea muncii noastre este calculată de

(Lucru utilizat per lira de droguri 1) * (medicament 1 pounds produs) +
(lucru utilizat per lira de droguri 2) * (medicament 2 lire sterline produse) +...
(Lucru utilizat per lira de droguri 6) * (medicament 6 lire sterline produse)

Am putea calcula utilizarea forței de muncă într-un mod mai plictisitor ca D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + i2 * i4. În mod similar, utilizarea materiilor 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ă timp. Imaginați-vă cât timp va dura dacă lucrați cu o firmă care a produs, de exemplu, produse 50 la uzina lor. O modalitate mult mai simplă de a calcula lucrul și utilizarea materiilor prime este să copiați din D14 în D15 formulei SUMPRODUCT ($D $2: $I $2, D4: i4). Această formulă calculează D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + i2 * i4 (care este modul nostru de utilizare a 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ă capturez încă amestecul de produse din rândul 2. Formula din celula D15 calculează utilizarea materiilor prime.

Într-un mod similar, profitul nostru este determinat de

(Consum de droguri 1 per lira) * (medicament 1 pounds produs) +
(medicament 2 profit per lira) * (medicament 2 lire sterline produse) +...
(Consum de droguri 6 per lira) * (medicament 6 lire sterline produse)

Profitul este calculat ușor în celula D12 cu formula SUMPRODUCT (D9: i9, $D $2: $I $2).

Acum putem identifica cele trei componente ale modelului nostru de Solver Mix Product.

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

• Se modifică celulele.Numărul de lire sterline produse din fiecare produs (listat în zona de celule D2: i2)

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

  • Nu utilizați mai multă muncă sau materii prime 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 produceți mai mult medicament decât este necesar. Mai exact, valorile din celulele D2: i2 (lire sterline produse din fiecare medicament) trebuie să fie mai mici sau egale cu cererea pentru fiecare medicament (listate în celulele D8: i8).

  • Nu putem genera o cantitate negativă de medicament.

Vă vom arăta cum să introduceți celula țintă, 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 un mix de produs 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 stabilire celulă țintă, apoi selectați celula noastră de profit (celula D12). Faceți clic pe caseta modificând celulele, apoi indicați spre zona D2: i2, care conține lire sterline produse pentru 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 de 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 figura 27-5.

Ne-am asigurat acum că, atunci când Rezolvitorul încearcă valori diferite pentru celulele în schimbare, numai combinațiile care satisfac D14< = F14 (munca utilizată este mai mică sau egală cu forța de muncă disponibilă) și D15< = F15 (materia primă utilizată este mai mică sau egală cu materia primă disponibilă) va fi luată în considerare. Faceți clic pe Adăugare pentru a introduce restricțiile de solicitare. 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 în schimbare, se vor examina numai combinații care satisfac următorii parametri:

• D2< = D8 (cantitatea produsă din medicamentul 1 este mai mică sau egală cu solicitarea pentru droguri 1)

• E2< = E8,2 (cantitatea de produse din medicamentul 2 este mai mică sau egală cu solicitarea pentru droguri 2)

• F2< = F8 (cantitatea produsă din medicamentul 3 efectuat este mai mică sau egală cu solicitarea pentru medicamentul 3)

• G2< = G8 (cantitatea produsă din medicamentul 4 efectuat este mai mică sau egală cu solicitarea pentru droguri 4)

• H2< = H8 (cantitatea produsă din medicamentul 5 efectuat este mai mică sau egală cu solicitarea pentru medicamentul 5)

• I2< = i8 (cantitatea produsă din medicamentul 6 efectuat este mai mică sau egală cu solicitarea pentru droguri 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 că modificarea celulelor trebuie să fie non-negativă în caseta de dialog Opțiuni Rezolvitor. Faceți clic pe butonul Opțiuni din caseta de dialog Parametri Rezolvitor. Bifați caseta se presupune model liniar și caseta asumare non-negativă, așa cum se arată în figura 27-8 pe pagina următoare. Faceți clic pe OK.

Verificarea casetei asumare non-negative asigură faptul că Rezolvitorul consideră doar combinații de celule care modifică în care fiecare celulă în schimbare își asumă o valoare non-negativă. Am verificat caseta se presupune model liniar, deoarece problema de amestec a produsului este un tip special de problemă de Rezolvitor numită model liniar. În esență, un model Rezolvitor este liniar în următoarele condiții:

• Celula țintă este calculată adăugând împreună termenii formularului (modificare celulă) * (constantă).

• Fiecare restricție satisface "cerința de model liniar". Acest lucru înseamnă că fiecare restricție este evaluată prin adăugarea împreună a termenilor formularului (modificarea celulei) * (constantă) și compararea sumelor la o constantă.

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

(Consum de droguri 1 per lira) * (medicament 1 pounds produs) +
(medicament 2 profit per lira) * (medicament 2 lire sterline produse) +...
(Consum de droguri 6 per lira) * (medicament 6 lire sterline produse)

Acest calcul urmează un model în care valoarea celulei țintă este derivată prin adăugarea termenilor formularului (modificare celulă) * (constantă).

Restricția noastră de muncă este evaluată prin compararea valorii derivate din (lucru utilizat per lira de droguri 1) * (medicament 1 pounds produs) + (lucru utilizat per lira de droguri 2) * (medicament 2 lire sterline produse) +... (Forță de muncăEd per lira de droguri 6) * (medicament 6 lire sterline produse) la munca disponibilă.

Prin urmare, restricția de muncă este evaluată prin adăugarea împreună a termenilor formularului (modificarea celulei) * (constantă) și compararea sumelor la o constantă. Atât restricția la locul de muncă, cât și constrângerea materiilor prime satisfac cerințele modelului liniar.

Restricțiile noastre de solicitare preiau formularul

(Medicament 1 produs) < = (droguri 1 cerere)
(medicament 2 produs) < = (medicament 2 cerere)
§
(medicament 6 produs) < = (medicament 6 cerere)

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

Deoarece s-a demonstrat că modelul nostru mix de produse este un model liniar, de ce ar trebui să ne pese?

• Dacă un model Rezolvitor este liniar și selectăm presupunem model liniar, Rezolvitorul este garantat pentru a găsi soluția optimă pentru modelul Rezolvitorului. Dacă un model Rezolvitor nu este liniar, Rezolvitorul poate sau nu să găsească soluția optimă.

• Dacă un model Rezolvitor este liniar și selectăm presupunem model 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 se selectează asumare model liniar, Rezolvitorul utilizează un algoritm foarte ineficient (metoda GRG2) și poate avea dificultăți în găsirea soluției optime a modelului.

După ce faceți clic pe OK în caseta de dialog Opțiuni Rezolvitor, reveniți la caseta de dialog Rezolvitor principal, afișată anterior în figura 27-7. Atunci când faceți clic pe rezolvare, Rezolvitorul calculează o soluție optimă (dacă există) pentru modelul nostru de amestec de produse. Așa cum am precizat în capitolul 26, o soluție optimă pentru modelul Mix de produs ar fi un set de valori de celule în schimbare (lire sterline produse din fiecare medicament) care maximizează profitul din setul de soluții fezabile. Din nou, o soluție fezabilă este un set de valori de celule în schimbare care satisfac toate restricțiile. Valorile de celule care se modifică afișate în figura 27-9 sunt o soluție fezabilă, deoarece toate nivelurile de producție sunt non-negative, nivelurile de producție nu depășesc solicitarea, iar utilizarea resurselor nu depășește resursele disponibile.

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

• Producem mai mult din medicamentul 5 decât solicitarea.

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

• Utilizăm mai mult material brut decât ceea ce este 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 ale soluției în foaia de lucru.

Compania noastră de droguri poate maximiza profitul său lunar la un nivel de $6.625,20, producând 596,67 de lire sterline de droguri 4, 1084 kg de droguri 5, și niciunul dintre celelalte medicamente! Nu putem determina dacă putem obține profitul maxim de $6.625,20 în alte moduri. Tot ce putem fi siguri este faptul că, cu resursele și cererile noastre limitate, nu există nicio modalitate de a face mai mult de $6.627,20 în această lună.