Utilizarea Rezolvitorului pentru a determina amestecul optim de produse

Notă: Dorim să vă oferim cel mai recent conținut de ajutor, cât mai rapid posibil, în limba dvs. Această pagină a fost tradusă automatizat și poate conține erori gramaticale sau inexactități. Scopul nostru este ca acest conținut să vă fie util. Vă rugăm să ne spuneți dacă informațiile v-au fost utile, în partea de jos a acestei pagini. Aici se află articolul în limba engleză, ca să îl puteți consulta cu ușurință.

Acest articol discută despre utilizarea Rezolvitorului, un program de completare Microsoft Excel pe care îl puteți utiliza pentru analiza IF, pentru a determina un mix de produs optim.

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.

Book image

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.

Notă:  Așa cum se explică în capitolul 26, "o introducere în optimizarea cu Excel Solver", Rezolvitor este instalat făcând clic pe butonul Microsoft Office, apoi pe Opțiuni Excel, urmat de programele de completare. În lista gestionare, faceți clic pe programe de completare Excel, bifați caseta de completare Rezolvitor, apoi faceți clic pe OK.

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

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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.

Book image

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ă.

Să presupunem că solicitarea pentru fiecare produs trebuie să fie îndeplinită. (Consultați foaia de lucru fără soluție fezabilă din fișierul Prodmix. xlsx.) Apoi, trebuie să modificăm restricțiile cerute 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. Se afișează caseta de dialog modificare restricție, afișată în figura 27-12.

Book image

Selectați > =, apoi faceți clic pe OK. Ne-am asigurat acum că Rezolvitorul va lua în considerare modificarea numai a valorilor celulelor care satisfac toate cerințele. Atunci când faceți clic pe rezolvare, veți vedea mesajul "Rezolvitorul nu a găsit 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 îndeplini solicitarea pentru toate produsele. Rezolvitorul ne spune pur și simplu că, dacă dorim să îndeplinim cererea pentru fiecare produs, trebuie să adăugăm mai multă muncă, mai multe materii prime sau mai multe.

Să vedem ce se întâmplă dacă permitem o cerere nelimitată pentru fiecare produs și permitem să se producă cantități negative pentru fiecare medicament. (Puteți vedea această problemă de Rezolvitor pe valorile setate nu converg foaia de lucru în fișierul Prodmix. xlsx.) Pentru a găsi soluția optimă pentru această situație, deschideți Rezolvitor, faceți clic pe butonul Opțiuni și debifați caseta asumare non-negativă. În caseta de dialog Parametri Rezolvitor, selectați restricția de solicitare D2: I2< = D8: i8, apoi faceți clic pe Ștergere pentru a elimina restricția. Atunci când faceți clic pe rezolvare, Rezolvitor returnează mesajul "Setați valorile celulelor nu converg." Acest mesaj înseamnă că, dacă celula țintă va fi maximizată (la fel ca în exemplul nostru), există soluții fezabile cu valori de celule țintă în mod arbitrar mari. (În cazul în care celula țintă va fi minimizată, mesajul "setarea valorilor celulelor nu converg" înseamnă că există soluții fezabile cu valori mici de celule țintă în mod arbitrar.) In situatia noastra, permitand productia negativa a unui medicament, intram in vigoare "creeaza" resurse care pot fi utilizate pentru a produce cantitati considerabile de alte medicamente. Dată fiind solicitarea noastră nelimitată, aceasta ne permite să facem profituri nelimitate. Într-o situație reală, nu putem face o sumă infinită de bani. Pe scurt, dacă vedeți "setarea valorilor nu converg", modelul dumneavoastră are o eroare.

  1. Să presupunem că firma noastră de droguri poate achiziționa până la 500 de ore de muncă la $1 pe oră mai mult decât costurile curente ale muncii. Cum putem maximiza profitul?

  2. La o fabrică de producție de cipuri, patru tehnicieni (A, B, C și D) produc trei produse (produsele 1, 2 și 3). În această lună, producătorul cipului poate vinde 80 de unități de produs 1, 50 unități de produs 2 și cel mult 50 unități de produs 3. Tehnicianul A poate face doar produsele 1 și 3. Tehnicianul B poate face numai produsele 1 și 2. Tehnicianul C poate face doar produsul 3. Tehnicianul D poate face doar produsul 2. Pentru fiecare unitate produsă, produsele contribuie la profitul următor: produsul 1, $6; Produs 2, $7; și produsul 3, $10. Timpul (în ore) fiecărui tehnician trebuie să fabrice 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 un producător cip să își maximizeze profitul lunar? Să presupunem că se pot produce un număr fracționat de unități.

  4. O fabrică de producție de computere produce șoareci, tastaturi și joystick-ul jocurilor video. Profitul per unitate, utilizarea forței de muncă per unitate, solicitarea lunară și utilizarea automată a mașinilor sunt prezentate în tabelul următor:

    Șoareci

    Tastaturi

    Joystick

    Profit/unitate

    $8

    $11

    $9

    Utilizare/unitate de muncă

    .2 ore

    .3 ore

    .24 de ore

    Timpul/unitatea mașinii

    .04 oră

    .055 oră

    .04 oră

    Cerere lunară

    15.000

    27.000

    11.000

  5. În fiecare lună, sunt disponibile un total de 13.000 de ore de lucru și 3000 de ore de timp pentru mașini. Cum își poate maximiza producătorul contribuția lunară a profitului din fabrică?

  6. Soluționați exemplul nostru de medicament, presupunând că trebuie îndeplinite o cerere minimă de unități 200 pentru fiecare medicament.

  7. Jason face brățări de diamant, coliere și cercei. El vrea să lucreze un maxim de 160 de ore pe lună. El are 800 uncii de diamante. Profitul, timpul de muncă și uncii de diamante necesare pentru a produce fiecare produs sunt prezentate mai jos. Dacă solicitarea pentru fiecare produs este nelimitată, cum poate Jason să își maximizeze profitul?

    Produs

    Profit unitar

    Orele de muncă per unitate

    Uncii de diamante per unitate

    Brățară

    300 lei

    .35

    1,2

    Colier

    200 lei

    .15

    .75

    Cercei

    100 lei

    .05

    0,5

Extindeți-vă competențele Office
Explorați instruirea
Fiți primul care obține noile caracteristici
Alăturați-vă utilizatorilor Office Insider

Au fost utile aceste informații?

Vă mulțumim pentru feedback!

Vă mulțumim pentru feedback! Se pare că ar fi util să luați legătura cu unul dintre agenții noștri de asistență Office.

×