Korišćenje programskog dodatka "Rešavač" za utvrđivanje optimalnog miksa proizvoda

U ovom članku se govori o korišćenju programskog dodatka "Rešavač", programskog dodatka programa Microsoft Excel koji možete da koristite za analizu šta-ako da biste utvrdili optimalni miks proizvoda.

Kako da odredim mesečni miks proizvoda koji povećava profitabilnost?

Kompanije često treba da odrede količinu svakog proizvoda za proizvodnju na mesečnom nivou. U svom najjednostavnijem obliku, problem miksa proizvoda podrazumeva kako odrediti količinu svakog proizvoda koji bi trebalo da bude proizveden tokom meseca kako bi se povećao profit. Proizvodi obično moraju da poštuju sledeća ograničenja:

• Proizvodni miks ne može da koristi više resursa nego što je dostupno.
• Postoji ograničena potražnja za svakim proizvodom. Ne možemo proizvesti više proizvoda tokom mesec dana nego što potražnja nalaže, jer se višak proizvodnje troši (na primer, kvarljivi lek).

Hajde da sada rešimo sledeći primer problema mešavine proizvoda. Rešenje ovog problema možete da pronađete u Prodmix.xlsx datoteka, prikazanom na slici 27-1.

Book Recimo da radimo za farmaceutsku kompaniju koja proizvodi šest različitih proizvoda u svojoj fabrici. Proizvodnja svakog proizvoda zahteva rad i sirovine. Red 4 na slici 27-1 pokazuje sate rada potrebne za proizvodnju pola kilograma svakog proizvoda, a red 5 pokazuje kilograma sirovine potrebne za proizvodnju pola kilograma svakog proizvoda. Na primer, proizvodnja funte proizvoda 1 zahteva šest sati rada i 3,2 kilograma sirovine. Za svaki lek, cena po funti je data u redu 6, jedinična cena po funti je data u redu 7, a doprinos profita po funti je dat u redu 9. Na primer, proizvod 2 se prodaje za 11,00 USD po funti, podrazumeva jediničnu cenu od 5,70 USD po funti i doprinosi profitu od 5,30 USD po funti. Mesečna potražnja za svaki lek je data u redu 8. Na primer, potražnja za proizvodom 3 je 1041 funti. Ovog meseca na raspolaganju je 4500 sati rada i 1600 kilograma sirovina. Kako ova kompanija može da maksimizira svoj mesečni profit?

Da ne znamo ništa o programu Excel Solver, rešili bismo ovaj problem tako što bismo napravili radni list za praćenje korišćenja profita i resursa povezanih sa kombinacijom proizvoda. Tada bismo koristili pokušaje i greške kako bismo promenili miks proizvoda kako bismo optimizirali profit bez upotrebe više rada ili sirovina nego što je dostupno, i bez proizvodnje bilo kakvog leka koji prelazi potražnju. Rešavač koristimo u ovom procesu samo u fazi probnih metoda i grešaka. U suštini, Rešavač je mašina za optimizaciju koja besprekorno izvršava pretragu proba i grešaka.

Ključ za rešavanje problema miksa proizvoda je efikasno izračunavanje upotrebe resursa i profita u vezi sa bilo kojim datim miksom proizvoda. Važna alatka koju možemo da koristimo za pravljenje ovog izračunavanja jeste funkcija SUMPRODUCT. Funkcija SUMPRODUCT množi odgovarajuće vrednosti u opsezima ćelija i vraća zbir tih vrednosti. Svaki opseg ćelija korišćen u SUMPRODUCT proceni mora imati iste dimenzije, što znači da možete koristiti SUMPRODUCT sa dva reda ili dve kolone, ali ne i sa jednom kolonom i jednim redom.

Kao primer načina na koji možemo da koristimo funkciju SUMPRODUCT u primeru sa proizvodima, hajde da pokušamo da izračunamo našu upotrebu resursa. Naše korišćenje rada se izračunava na osnovu

(Radna snaga koja se koristi po kilogramu leka 1) * (Lek 1 funti proizvedeno)+
(Rad se koristi po kilogramu leka 2) * (Lek 2 funti proizveden) + ...
(Radna snaga koja se koristi po kilogramu leka 6) * (Proizvedeni lek 6 funti)

Mogli bismo izračunati upotrebu rada na više dosadan način kao D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. Slično tome, upotreba sirovina može se izračunati kao D2 * D5 + E2 * E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. Međutim, unošenje ovih formula u radni list za šest proizvoda oduzima mnogo vremena. Zamislite koliko bi vam bilo potrebno da radite sa kompanijom koja je proizvela, na primer, 50 proizvoda u njihovoj fabrici. Mnogo lakši način za izračunavanje potrošnje rada i sirovina je da kopirate formulu SUMPRODUCT($D$2:$I$2,D4:I4). Ova formula izračunava D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (što je naša upotreba rada), ali je mnogo lakše uneti! Obratite pažnju na to da koristim znak $ sa opsegom D2:I2 tako da kada kopiram formulu i dalje hvatam mešavinu proizvoda iz reda 2. Formula u ćeliji D15 izračunava potrošnju sirovina.

Na sličan način, naš profit određuje

(Lek 1 profit po funti) * (Lek 1 funti proizveden) +
(Drug 2 profit po funti) * (Lek 2 funti proizveden) + ...
(Lek 6 profit po funti) * (Lek 6 funti proizveden)

Profit se lako izračunava u ćeliji D12 pomoću formule SUMPRODUCT(D9:I9,$D$2:$I$2)).

Sada možemo da identifikujemo tri komponente našeg modela programskog miksa Rešavača.

• Ciljna ćelija. Naš cilj je da uvećamo profit (izračunato u ćeliji D12).

• Promenljive ćelije. Broj funti proizvedenih za svaki proizvod (navedeno u opsegu ćelija D2:I2)

• Ograničenja. Imamo sledeća ograničenja:

  • Ne koristite više rada ili sirovina nego što je dostupno. To jest, vrednosti u ćelijama D14:D15 (korišćeni resursi) moraju biti manje ili jednake vrednostima u ćelijama F14:F15 (dostupni resursi).
  • Ne proizvodite više leka nego što je u potražnji. To jest, vrednosti u ćelijama D2: I2 (funte proizvedene od svakog leka) moraju biti manje ili jednake potražnji za svaki lek (navedene u ćelijama D8: I8).
  • Ne možemo proizvesti negativnu količinu bilo koje droge.

Pokazaću vam kako da unesete ciljnu ćeliju, promenu ćelija i ograničenja u Rešavač. Sve što treba da uradite je da kliknete na dugme Reši da biste pronašli miks proizvoda koji maksimizira profit!

Da biste započeli, izaberite karticu Podaci i u grupi "Analiza" izaberite stavku "Rešavač".

Pojaviće se dijalog "Parametri programskog dodatka Solver", kao što je prikazano na slici 27-2.

Book Izaberite polje "Postavljanje ciljne ćelije", a zatim izaberite ćeliju profita (ćelija D12). Kliknite na polje Promenom ćelija, a zatim postavite pokazivač na opseg D2: I2, koji sadrži kilograme proizvedene od svakog leka. Dijalog bi sada trebalo da izgleda Slika 27-3.

Book Sada smo spremni da modelu dodamo ograničenja. Kliknite na dugme "Dodaj". Videćete dijalog "Dodavanje ograničenja", koji je prikazan na slici 27-4.

Book Da biste dodali ograničenja upotrebe resursa, kliknite na polje "Referenca na ćeliju", a zatim izaberite opseg D14:D15. Izaberite <= sa srednje liste. Kliknite na polje "Ograničenje", a zatim izaberite opseg ćelija F14:F15. Dijalog "Dodavanje ograničenja" sada bi trebalo da izgleda kao na slici 27-5.

Book Sada smo se uverili da će se, kada programski dodatak Solver isproba različite vrednosti za promenljive ćelije, uzeti u obzir samo kombinacije koje zadovoljavaju D14< = F14 (upotrebljena radna snaga je manja ili jednaka raspoloživoj radnoj snazi) i D15< = F15 (upotrebljena sirovina je manja ili jednaka raspoloživoj sirovini). Kliknite na dugme "Dodaj" da biste uneli ograničenja potražnje. Popunite dijalog "Dodavanje ograničenja" kao što je prikazano na slici 27-6.

Book Dodavanje ovih ograničenja obezbeđuje da kada programski dodatak Solver pokuša različite kombinacije za promenljive vrednosti ćelija, u obzir se uzimaju samo kombinacije koje zadovoljavaju sledeće parametre:

• D2< = D8 (količina proizvedena od leka 1 je manja ili jednaka potražnji za lekom 1)
• E2< = E8 (količina proizvedenog leka 2 je manja ili jednaka potražnji za lekom 2)
• F2< = F8 (količina proizvedena od leka 3 je manja ili jednaka potražnji za lekom 3)
• G2< = G8 (količina proizvedena od leka 4 je manja ili jednaka potražnji za lekom 4)
• H2< = H8 (količina proizvedena od leka 5 je manja ili jednaka potražnji za lekom 5)
• I2< = I8 (količina proizvedena od leka 6 je manja ili jednaka potražnji za lekom 6)

Kliknite na dugme "U redu" u dijalogu "Dodavanje ograničenja". Prozor programskog dodatka "Rešavač" trebalo bi da izgleda kao na slici 27-7.

Book U dijalog "Opcije programskog dodatka Solver" unosimo ograničenje prema kojem promenljive ćelije moraju biti nenegativne. Kliknite na dugme "Opcije" u dijalogu "Parametri programskog dodatka Solver". Potvrdite polje "Pretpostavi linearni model" i polje "Pretpostavi da nije negativno", kao što je prikazano na slici 27-8 na sledećoj stranici. Kliknite na dugme „U redu“.

Book Ako potvrdite izbor u polju za potvrdu "Pretpostavi da nije negativno", obezbeđuje da rešavač uzima u obzir samo kombinacije promenljivih ćelija u kojima svaka promenljiva ćelija pretpostavlja nenegativnu vrednost. Potvrdili smo izbor u polju "Pretpostavi linearni model" zato što je problem mešavine proizvoda poseban tip problema Solver koji se naziva linearni model. U osnovi, model programskog dodatka Solver je linearan u sledećim uslovima:

• Ciljna ćelija se izračunava sabiranjem uslova obrasca (promenljiva ćelija)*(konstanta).
• Svako ograničenje ispunjava "zahteve linearnog modela". To znači da se svako ograničenje procenjuje sabiranjem uslova obrasca (promenljiva ćelija)*(konstanta) i poređenjem zbirova sa konstantom.

Zašto je ovaj problem sa programskim dodatkom Solver linearan? Naša ciljna ćelija (profit) se računa kao

(Lek 1 profit po funti) * (Lek 1 funti proizveden) +
(Drug 2 profit po funti) * (Lek 2 funti proizveden) + ...
(Lek 6 profit po funti) * (Lek 6 funti proizveden)

Ovo izračunavanje prati obrazac u kojem se vrednost ciljne ćelije izvodi sabiranjem uslova oblika (promenljiva ćelija)*(konstanta).

Naše ograničenje rada se procenjuje upoređivanjem vrednosti izvedene iz (Rad korišćen po kilogramu leka 1) * (Lek 1 funti proizveden) + (Rad korišćen po kilogramu leka 2) * (Lek 2 funti proizveden) + ... (Radna snaga koja se koristi po kilogramu leka 6) * (Lek 6 funti proizveden) na raspoloživu radnu snagu.

Prema tome, ograničenje rada se procenjuje sabiranjem uslova obrasca (promenljiva ćelija)*(konstanta) i poređenjem zbirova sa konstantom. I ograničenje rada i ograničenje sirovina zadovoljavaju uslov linearnog modela.

Naša ograničenja potražnje poprimaju oblik

(Proizveden lek 1)<=(Potražnja za lekom 1)
(Proizveden lek 2)<=(Potražnja za lekom 2)
§
(Proizveden lek 6)<=(Potražnja za lekom 6)

Svako ograničenje potražnje takođe ispunjava zahteve linearnog modela, zato što se svako izračunava sabiranjem uslova obrasca (promenljiva ćelija)*(konstanta) i poređenjem zbirova sa konstantom.

Pošto smo pokazali da je naš model miksa linearan model, zašto bi nas bilo briga?

• Ako je model programskog dodatka "Rešavač" linearan i izaberemo opciju "Pretpostavimo linearni model", on će garantovano pronaći optimalno rešenje za model rešavača. Ako model programskog dodatka "Rešavač" nije linearan, "Rešavač" može, ali i ne mora da pronađe optimalno rešenje.
• Ako je model programskog dodatka "Rešavač" linearan i izaberemo opciju "Pretpostavimo linearni model", "Rešavač" koristi veoma efikasan algoritam (simpleks metod) za pronalaženje optimalnog rešenja za model. Ako je model programskog dodatka "Rešavač" linearan, a ne izaberemo opciju "Pretpostavimo linearni model", "Rešavač" koristi veoma neefikasan algoritam (metod GRG2) i može imati poteškoća u pronalaženju optimalnog rešenja modela.

Pošto kliknemo na dugme "U redu" u dijalogu "Opcije programskog dodatka Solver", vraćamo se na glavni dijalog programskog dodatka Solver, koji je prikazan ranije na slici 27-7. Kada kliknemo na dugme "Reši", "Rešavač" izračunava optimalno rešenje (ako ono postoji) za naš model mešanja proizvoda. Kao što sam naveo u poglavlju 26, optimalno rešenje za model miksa proizvoda bi bio skup promena vrednosti ćelija (funti proizvedenih od svakog leka) koji maksimizira profit u odnosu na skup svih izvodljivih rešenja. Izvodljivo rešenje je skup promenljivih vrednosti ćelija koje zadovoljavaju sva ograničenja. Promenljive vrednosti ćelija prikazane na slici 27-9 su izvodljivo rešenje, jer svi nivoi proizvodnje su ne-negativni, nivoi proizvodnje ne prelaze potražnju, a korišćenje resursa ne prelazi raspoložive resurse.

Book Promenljive vrednosti ćelija prikazane na slici 27-10 na sledećoj stranici predstavljaju neizvodljivo rešenje iz sledećih razloga:

• Proizvodimo više leka 5 nego potražnja za njim.
• Koristimo više radne snage nego što je dostupno.
• Koristimo više sirovina nego što je dostupno.

Book Kad kliknete na dugme "Reši", rešavač brzo pronalazi optimalno rešenje prikazano na slici 27-11. Morate da izaberete stavku "Zadrži rešenje rešavača" da biste zadržali vrednosti optimalnog rešenja u radnom listu.

Book Naša farmaceutska kompanija može maksimizirati svoj mesečni profit na nivou od $6,625.20 proizvodnjom 596.67 funti leka 4, 1084 funti leka 5, i nijedan od drugih lekova! Ne možemo da utvrdimo da li možemo da postignemo maksimalnu zaradu od 6.625,20 USD na druge načine. Sve što možemo biti sigurni je da sa našim ograničenim resursima i potražnjom, ne postoji način da zaradimo više od 6,627.20 dolara ovog meseca.

Da li model programskog dodatka "Rešavač" uvek ima rešenje?

Pretpostavimo da potražnja za svakim proizvodom mora biti zadovoljena. (Pogledajte radni list " Nije moguće rešenje " u Prodmix.xlsx datoteke.) Zatim moramo da promenimo ograničenja potražnje sa D2:I2<=D8:I8 na D2:I2>=D8:I8. Da biste to uradili, otvorite Rešavač, izaberite ograničenje D2:I2<=D8:I8, a zatim kliknite na dugme "Promeni". Pojavljuje se dijalog "Promena ograničenja", prikazan na slici 27-12.

Book Izaberite >=, a zatim kliknite na dugme "U redu". Sada smo se uverili da će programski dodatak Solver razmotriti promenu samo vrednosti ćelija koje zadovoljavaju sve zahteve. Kada kliknete na dugme Reši, videćete poruku "Rešavač nije mogao da pronađe izvodljivo rešenje". Ova poruka ne znači da smo pogrešili u modelu, već da sa našim ograničenim resursima ne možemo da zadovoljimo potražnju za svim proizvodima. Rešavač nam jednostavno govori da ako želimo da zadovoljimo potražnju za svakim proizvodom, moramo dodati više radne snage, više sirovina ili više i jednog i drugog.

Šta znači ako model programskog dodatka Solver daje rezultat: Postavljene vrednosti se ne konvergiraju?

Da vidimo šta će se desiti ako dozvolimo neograničenu potražnju za svakim proizvodom i dozvolimo da se proizvedu negativne količine svakog leka. (Možete da vidite ovaj problem programskog dodatka Solver na radnom listu "Postavi vrednosti ne konvergiraju" u datoteci Prodmix.xlsx.) Da biste pronašli optimalno rešenje za ovu situaciju, otvorite Rešavač, kliknite na dugme "Opcije" i opozovite izbor u polju "Pretpostavi da nije negativno". U dijalogu "Parametri programskog dodatka Solver" izaberite ograničenje potražnje D2:I2<=D8:I8, a zatim kliknite na dugme "Izbriši" da biste uklonili ograničenje. Kada kliknete na dugme "Reši", Solver daje poruku "Postavljene vrednosti ćelija ne konvergiraju". Ova poruka znači da ako ciljna ćelija treba da bude uvećana (kao u našem primeru), postoje izvodljiva rešenja sa proizvoljno velikim vrednostima ciljne ćelije. (Ako ciljna ćelija treba da bude umanjena, poruka "Podesite vrednosti ćelija da se ne konvergiraju" znači da postoje izvodljiva rešenja sa proizvoljno malim vrednostima ciljne ćelije.) U našoj situaciji, omogućavajući negativnu proizvodnju leka, mi u stvari "stvaramo" resurse koji se mogu koristiti za proizvodnju proizvoljno velikih količina drugih lekova. S obzirom na našu neograničenu potražnju, to nam omogućava neograničen profit. U stvarnoj situaciji, ne možemo zaraditi beskonačnu količinu novca. Ukratko, ako vidite poruku "Postavljene vrednosti ne konvergiraju", model ima grešku.

Problemi

1. Pretpostavimo da naša farmaceutska kompanija može kupiti do 500 sati rada na $ 1 više po satu od trenutnih troškova rada. Kako možemo maksimizirati profit?

2. U fabrici za proizvodnju čipova, četiri tehničara (A, B, C i D) proizvode tri proizvoda (Proizvodi 1, 2 i 3). Ovog meseca proizvođač čipova može da proda 80 jedinica proizvoda 1, 50 jedinica proizvoda 2 i najviše 50 jedinica proizvoda 3. Tehničar A može da napravi samo proizvode 1 i 3. Tehničar B može napraviti samo proizvode 1 i 2. Tehničar C može da napravi samo proizvod 3. Tehničar D može da napravi samo proizvod 2. Za svaku proizvedenu jedinicu, proizvodi doprinose sledećem profitu: Proizvod 1, 6 dolara; Proizvod 2, 7 dolara; i proizvod 3, 10 dolara. Vreme (u satima) svaki tehničar treba da proizvede proizvod je sledeće:

   Proizvod	Tehničar A	Tehničar B	Tehničar C	Tehničar D
   1	2	2,5	Nije moguće uraditi	Nije moguće uraditi
   2	Nije moguće uraditi	3	Nije moguće uraditi	3,5
   3	3	Nije moguće uraditi	4	Nije moguće uraditi

3. Svaki tehničar može da radi do 120 sati mesečno. Kako proizvođač čipova može maksimizirati svoj mesečni profit? Pretpostavimo da može da se proizvede broj jedinica u procentima.

4. Fabrika za proizvodnju računara proizvodi miševe, tastature i džojstike za video igre. Dobit po jedinici, upotreba radne snage po jedinici, mesečna potražnja i upotreba vremena mašine po jedinici su date u sledećoj tabeli:

   	Miševi	Tastature	Džojstiki
   Profit/jedinica	$8	Dolar 11	$9
   Potrošnja radne snage / jedinica	.2 sat	.3 sat	.24 sata
   Vreme mašine / jedinica	0,04 sat	.055 sat	0,04 sat
   Mesečna potražnja	15.000	27,000	11,000

5. Svakog meseca na raspolaganju je ukupno 13.000 radnih sati i 3000 sati mašinskog vremena. Kako proizvođač može maksimizirati svoj mesečni doprinos profitu od biljke?

6. Rešite naš primer droge pod pretpostavkom da mora biti zadovoljena minimalna potražnja od 200 jedinica za svaki lek.

7. Jason pravi dijamantske narukvice, ogrlice i minđuše. Želi da radi maksimalno 160 sati mesečno. Ima 800 unci dijamanata. Profit, radno vreme i unce dijamanata potrebnih za proizvodnju svakog proizvoda su dati u nastavku. Ako je potražnja za svakim proizvodom neograničena, kako Džejson može maksimizirati svoj profit?

   Proizvod	Jedinična dobit	Radni sati po jedinici	unci dijamanata po jedinici
   Narukvica	300 din	.35	1,2
   Ogrlica	200 din.	.15	.75
   Minđuše	100 RSD	0,05	.5