Upotreba alata za rješavanje za određivanje optimalnog miksa proizvoda

U ovom se članku opisuje korištenje alata za rješavanje, dodatka programa Microsoft Excel koji možete koristiti za analizu "što ako" da biste odredili optimalnu kombinaciju proizvoda.

Kako odrediti mjesečni miks proizvoda koji maksimizira profitabilnost?

Tvrtke često moraju odrediti količinu svakog proizvoda za proizvodnju na mjesečnoj bazi. U svom najjednostavnijem obliku, problem miksa proizvoda uključuje kako odrediti količinu svakog proizvoda koju treba proizvesti tijekom mjeseca dana kako bi se povećala dobit. Asortiman proizvoda obično mora ispunjavati sljedeća ograničenja:

• Proizvodima se ne može koristiti više resursa nego što je dostupno.
• Potražnja za svakim proizvodom je ograničena. Ne možemo proizvesti više proizvoda tijekom mjeseca dana nego što to nalaže potražnja, jer se višak proizvodnje troši (na primjer, kvarljiva droga).

Riješimo sada sljedeći primjer problema mješavine proizvoda. Rješenje ovog problema možete pronaći u Prodmix.xlsx datoteke, prikazanoj na slici 27-1.

Recimo da radimo za farmaceutsku tvrtku koja proizvodi šest različitih proizvoda u njihovoj tvornici. Proizvodnja svakog proizvoda zahtijeva rad i sirovine. Redak 4 na slici 27-1 prikazuje sate rada potrebne za proizvodnju funte svakog proizvoda, a redak 5 prikazuje funte sirovine potrebne za proizvodnju funte svakog proizvoda. Na primjer, za proizvodnju funte proizvoda 1 potrebno je šest sati rada i 3,2 kilograma sirovine. Za svaki lijek cijena po funti navedena je u retku 6, jedinični trošak po funti dat je u retku 7, a doprinos dobiti po funti naveden je u retku 9. Na primjer, Proizvod 2 prodaje se za 11,00 USD po funti, stvara jedinični trošak od 5,70 USD po funti i doprinosi dobiti od 5,30 USD po funti. Mjesečna potražnja za svakom lijekom navedena je u retku 8. Na primjer, potražnja za proizvodom 3 iznosi 1041 funtu. Ovog mjeseca dostupno je 4500 sati rada i 1600 kilograma sirovine. Kako ova tvrtka može maksimizirati svoju mjesečnu dobit?

Da ne znamo ništa o alatu za rješavanje programa Excel, riješili bismo taj problem sastavljanjem radnog lista radi praćenja dobiti i korištenja resursa povezanog s asortimanom proizvoda. Zatim bismo koristili pokušaje i pogreške kako bismo promijenili asortiman proizvoda kako bismo optimizirali profit bez korištenja više radne snage ili sirovina nego što je dostupno i bez proizvodnje bilo kojeg lijeka koji bi premašio potražnju. Alat za rješavanje u tom procesu koristimo samo u fazi pokušaja i pogrešaka. U osnovi, Alat za rješavanje je optimizirani mehanizam koji besprijekorno izvodi traženje pokušaja i pogrešaka.

Ključ rješavanja problema mješavine proizvoda jest učinkovito računanje korištenja resursa i dobiti u vezi s bilo kojom vrstom proizvoda. Važan alat koji možemo koristiti za izračun je funkcija SUMPRODUCT. Funkcija SUMPRODUCT množi odgovarajuće vrijednosti u rasponima ćelija i vraća zbroj tih vrijednosti. Svaki raspon ćelija korišten u procjeni funkcije SUMPRODUCT mora biti iste dimenzije, što znači da funkciju SUMPRODUCT možete koristiti s dva retka ili dva stupca, ali ne i s jednim stupcem i jednim retkom.

Za primjer korištenja funkcije SUMPRODUCT u primjeru miksa proizvoda pokušajmo izračunati korištenje resursa. Naša upotreba radne snage izračunava se prema

(Radna snaga koja se koristi po kilogramu lijeka 1)*(Proizvedeni lijek 1 funta)+
(Radni snag koji se koristi po kilogramu lijeka 2)*(Proizvedeni lijek 2 funte) + ...
(Upotrijebljeni rad po kilogramu lijeka 6)*(Proizvedeni lijek 6 funti)

Mogli bismo računati potrošnju radne snage na zamorniji 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. No unos tih formula na radni list za šest proizvoda oduzima mnogo vremena. Zamislite koliko bi vremena trajalo da radite s tvrtkom koja proizvodi, na primjer, 50 proizvoda u njihovoj tvornici. Mnogo jednostavniji način izračuna potrošnje rada i sirovina jest kopirati iz D14 u D15 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 radna pošta), ali je mnogo lakše unijeti! Obratite pozornost na to da koristim znak $ uz raspon D2:I2 da bih prilikom kopiranja formule i dalje uhvatio mješavinu proizvoda iz retka 2. Formula u ćeliji D15 izračunava potrošnju sirovina.

Na sličan način, naša dobit je određena

(Lijek 1 dobit po funti)*(Proizvedeni lijek 1 funta) +
(Lijek 2 dobit po funti)*(Proizvedeni lijek 2 funte) + ...
(Lijek 6 dobit po funti)*(Proizvedeni lijek 6 funti)

Dobit se jednostavno izračunava u ćeliji D12 pomoću formule SUMPRODUCT(D9:I9;$D$2:$I$2)).

Sada možemo prepoznati tri komponente našeg modela alata za rješavanje mise proizvoda.

• Odredišna ćelija. Cilj nam je maksimizirati dobit (izračunato u ćeliji D12).

• Ćelija koje se mijenjaju. Broj funti proizvedenih za svaki proizvod (naveden u rasponu ćelija D2:I2)

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

  • Nemojte koristiti više radne snage ili sirovina nego što je dostupno. Odnosno, vrijednosti u ćelijama D14:D15 (upotrijebljeni resursi) moraju biti manje ili jednake vrijednostima u ćelijama F14:F15 (dostupni resursi).
  • Nemojte proizvoditi više lijeka nego što je potrebno. Odnosno, vrijednosti u stanicama D2:I2 (funte proizvedene od svakog lijeka) moraju biti manje ili jednake potražnji za svakim lijekom (navedenim u stanicama D8:I8).
  • Ne možemo proizvesti negativnu količinu bilo kojeg lijeka.

Pokazat ću vam kako unijeti ciljnu ćeliju, promijeniti ćelije i ograničenja u alat za rješavanje. Zatim sve što trebate učiniti je kliknuti gumb Riješi kako biste pronašli miks proizvoda koji maksimizira profit!

Da biste počeli, kliknite karticu Podaci pa u grupi Analiza kliknite Alat za rješavanje.

Prikazat će se dijaloški okvir Parametri alata za rješavanje kao što je prikazano na slici 27-2.

Kliknite okvir Postavi ciljnu ćeliju pa odaberite ćeliju s dobitkom (ćelija D12). Kliknite okvir Promjenom ćelija, a zatim pokažite na raspon D2:I2, koji sadrži funte proizvedene od svakog lijeka. Dijaloški okvir sada bi trebao izgledati kao Slika 27-3.

Sada smo spremni za dodavanje ograničenja u model. Kliknite gumb Dodaj. Vidjet ćete dijaloški okvir Dodavanje ograničenja, prikazan na slici 27-4.

Da biste dodali ograničenja korištenja resursa, kliknite okvir Reference ćelije, a zatim odaberite raspon D14:D15. Na srednjem popisu odaberite <=. Kliknite okvir Ograničenje, a zatim odaberite raspon ćelija F14:F15. Dijaloški okvir Dodavanje ograničenja sada bi trebao izgledati kao na slici 27-5.

Sada smo osigurali da kada Alat za rješavanje pokuša različite vrijednosti za ćelije koje se mijenjaju, uzet će se u obzir samo kombinacije koje zadovoljavaju D14<=F14 (upotrijebljeni rad je manji ili jednak raspoloživom radu) i D15<=F15 (upotrijebljena sirovina je manja ili jednaka dostupnoj sirovini). Kliknite Dodaj da biste unijeli ograničenja potražnje. Ispunite dijaloški okvir Dodavanje ograničenja kao što je prikazano na slici 27-6.

Dodavanjem tih ograničenja jamčite da će Rješavatelj pokušati različite kombinacije za promjenjive vrijednosti ćelija uzeti u obzir samo kombinacije koje zadovoljavaju sljedeće parametre:

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

Kliknite U redu u dijaloškom okviru Dodavanje ograničenja. Prozor alata za rješavanje trebao bi izgledati kao na slici 27-7.

U dijaloški okvir Mogućnosti alata za rješavanje unijeli smo ograničenje prema kojem ćelije koje se mijenjaju ne moraju biti negativne. Kliknite gumb Mogućnosti u dijaloškom okviru Parametri alata za rješavanje. Potvrdite okvire Pretpostavi linearni model i Pretpostavi da nije negativno, kao što je prikazano na slici 27-8 na sljedećoj stranici. Kliknite U redu.

Potvrđivanjem okvira Pretpostavi da nije negativno rješavatelj uzima u obzir samo kombinacije ćelija s promjenama od kojih svaka mijenjajuća ćelija poprima ne-negativnu vrijednost. Potvrdili smo okvir Pretpostavi linearni model jer je problem mješavine proizvoda posebna vrsta problema alata za rješavanje pod nazivom linearni model. Model alata za rješavanje u osnovi je linearan u sljedećim uvjetima:

• Ciljna ćelija izračunava se zbrajanjem izraza oblika (promjenjiva ćelija)*(konstanta).
• Svako ograničenje zadovoljava "preduvjet linearnog modela". To znači da se svako ograničenje procjenjuje zbrajanjem izraza oblika (promjenjiva ćelija)*(konstanta) i usporedbom zbrojeva s konstantom.

Zašto je ovaj problem alata za rješavanje linearan? Naša ciljna ćelija (dobit) računa se kao

(Lijek 1 dobit po funti)*(Proizvedeni lijek 1 funta) +
(Lijek 2 dobit po funti)*(Proizvedeni lijek 2 funte) + ...
(Lijek 6 dobit po funti)*(Proizvedeni lijek 6 funti)

Ovaj izračun slijedi obrazac u kojem se vrijednost ciljne ćelije dobiva zbrajanjem izraza oblika (promjenjiva ćelija)*(konstanta).

Naše ograničenje rada procjenjuje se usporedbom vrijednosti izvedene iz (Radni snag upotrijebljen po funti lijeka 1)*(Proizvedeni lijek 1 funta) + (Radni snag koji se koristi po funti lijeka 2)*(Proizvedeni lijek 2 funte)+ ... (Radna snaga koja se koristi po kilogramu lijeka 6)*(Proizvedeni lijek 6 funti) na raspoloživu radnu snagu.

Stoga se ograničenje rada procjenjuje zbrajanjem izraza oblika (promjenjiva ćelija)*(konstanta) i usporedbom zbrojeva s konstantom. I ograničenje rada i ograničenje sirovina zadovoljavaju zahtjeve linearnog modela.

Naša ograničenja potražnje imaju oblik

(Proizveden lijek 1)<=(Potražnja za lijekom 1)
(Proizveden lijek 2)<=(Potražnja za lijekom 2)
§
(Proizveden lijek 6)<=(Potražnja za lijekom 6)

Svako ograničenje potražnje zadovoljava i preduvjet linearnog modela jer se svako od njih procjenjuje zbrajanjem izraza oblika (promjenjiva ćelija)*(konstanta) i usporedbom zbrojeva s konstantom.

Nakon što smo pokazali da je naš model miksa proizvoda linearni model, zašto bi nas bilo briga?

• Ako je model Rješavatelja linearan i odaberemo Pretpostavimo linearni model, Rješavatelj će sigurno pronaći optimalno rješenje za model Rješavatelja. Ako model Alata za rješavanje nije linearan, Alat za rješavanje može, ali i ne mora pronaći optimalno rješenje.
• Ako je model Rješavatelja linearan i odaberemo Pretpostavi linearni model, Rješavatelj koristi vrlo učinkovit algoritam (simpleks metodu) da bi pronašao optimalno rješenje modela. Ako je model Rješavatelja linearan i ne odaberemo mogućnost Pretpostavi linearni model, Rješavatelj koristi vrlo neučinkovit algoritam (GRG2 metodu) i može imati poteškoća s pronalaženjem optimalnog rješenja za model.

Nakon klika na U redu u dijaloškom okviru Mogućnosti alata za rješavanje vratit ćemo se na glavni dijaloški okvir Alata za rješavanje, prikazan ranije na slici 27-7. Kada kliknemo Riješi, Rješavatelj izračunava optimalno rješenje (ako postoji) za naš model mješavine proizvoda. Kao što sam naveo u poglavlju 26, optimalno rješenje za model mješavine proizvoda bio bi skup promjenjivih vrijednosti stanica (funte proizvedene od svakog lijeka) koji maksimizira dobit u odnosu na skup svih izvedivih rješenja. Ponovno, izvedivo rješenje je skup promjenjivih vrijednosti ćelija koje zadovoljavaju sva ograničenja. Promjenjive vrijednosti ćelija prikazane na slici 27-9 izvedivo su rješenje jer su sve razine proizvodnje nenegativne, razine proizvodnje ne premašuju potražnju, a korištenje resursa ne premašuje raspoložive resurse.

Promjena vrijednosti ćelija prikazana na slici 27-10 na sljedećoj stranici predstavlja neizvedivo rješenje iz sljedećih razloga:

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

Nakon klika na Riješi, Rješavatelj brzo pronalazi optimalno rješenje prikazano na slikama 27-11. Morate odabrati Zadrži rješenje alata za rješavanje da biste sačuvali optimalne vrijednosti rješenja na radnom listu.

Naša farmaceutska tvrtka može maksimizirati svoju mjesečnu dobit na razini od 6.625,20 dolara proizvodeći 596,67 funti lijeka 4, 1084 funte lijeka 5 i nijednog drugog lijeka! Ne možemo odrediti možemo li postići maksimalnu dobit od 6.625,20 USD na druge načine. Sve u što možemo biti sigurni je da s našim ograničenim resursima i potražnjom ne postoji način da ovog mjeseca zaradimo više od 6.627,20 USD.

Ima li model alata za rješavanje uvijek rješenje?

Pretpostavimo da potražnja za svakim proizvodom mora biti zadovoljena. (Pogledajte radni list Rješenje koje nije izvedivo u datoteci Prodmix.xlsx.) Zatim moramo promijeniti ograničenja potražnje iz D2:I2<=D8:I8 u D2:I2>=D8:I8. Da biste to učinili, otvorite alat za rješavanje, odaberite ograničenje D2:I2<=D8:I8, a zatim kliknite Promijeni. Pojavit će se dijaloški okvir Promjena ograničenja, prikazan na slici 27-12.

Odaberite >=, a zatim kliknite U redu. Sada smo osigurali da će Alat za rješavanje razmotriti promjenu samo vrijednosti ćelija koje zadovoljavaju sve zahtjeve. Kada kliknete Riješi, prikazat će vam se poruka "Alat za rješavanje nije mogao pronaći izvedivo rješenje". Ova poruka ne znači da smo pogriješili u našem modelu, već da s našim ograničenim resursima ne možemo zadovoljiti potražnju za svim proizvodima. Solver nam jednostavno govori da ako želimo zadovoljiti potražnju za svakim proizvodom, moramo dodati više radne snage, više sirovina ili više oboje.

Što znači ako model alata za rješavanje daje rezultat Vrijednosti skupa ne konvergiraju?

Pogledajmo što će se dogoditi ako dopustimo neograničenu potražnju za svakim proizvodom i dopustimo proizvodnju negativnih količina svakog lijeka. (Ovaj problem s alatom za rješavanje možete vidjeti na radnom listu Postavljanje vrijednosti Ne konvergiraju u datoteci Prodmix.xlsx.) Da biste pronašli optimalno rješenje za tu situaciju, otvorite alat za rješavanje, kliknite gumb Mogućnosti i poništite okvir Pretpostavi da nije negativno. U dijaloškom okviru Parametri alata za rješavanje odaberite ograničenje potražnje D2:I2<=D8:I8, a zatim kliknite Izbriši da biste uklonili ograničenje. Kada kliknete Riješi, Rješavatelj vraća poruku "Postavljene vrijednosti ćelija ne konvergiraju." Ova poruka znači da, ako želite maksimizirati ciljnu ćeliju (kao u našem primjeru), postoje izvediva rješenja s proizvoljno velikim vrijednostima ciljnih ćelija. (Ako je ciljnu ćeliju potrebno minimizirati, poruka "Postavljene vrijednosti ćelija ne konvergiraju" znači da postoje izvediva rješenja s proizvoljno malim vrijednostima ciljnih ćelija.) U našoj situaciji, dopuštajući negativnu proizvodnju lijeka, zapravo "stvaramo" resurse koji se mogu koristiti za proizvodnju proizvoljno velikih količina drugih lijekova. S obzirom na našu neograničenu potražnju, to nam omogućuje neograničenu zaradu. U stvarnoj situaciji ne možemo zaraditi beskonačnu količinu novca. Ukratko, ako vidite "Postavke ne konvergiraju", vaš model sadrži pogrešku.

Problemi

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

2. U tvornici za proizvodnju čipova četiri tehničara (A, B, C i D) proizvode tri proizvoda (Proizvodi 1, 2 i 3). Ovaj mjesec proizvođač čipova može prodati 80 jedinica proizvoda 1, 50 jedinica proizvoda 2 i najviše 50 jedinica proizvoda 3. Tehničar A može izraditi samo proizvode 1 i 3. Tehničar B može izraditi samo proizvode 1 i 2. Tehničar C može izraditi samo proizvod 3. Tehničar D može izraditi samo Proizvod 2. Za svaku proizvedenu jedinicu proizvodi doprinose sljedećoj dobiti: Proizvod 1, 6 USD; Proizvod 2, 7 USD; i Proizvod 3, 10 USD. Vrijeme (u satima) koje je svakom tehničaru potrebno za proizvodnju proizvoda je sljedeće:

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

3. Svaki tehničar može raditi do 120 sati mjesečno. Kako proizvođač čipova može maksimizirati svoju mjesečnu dobit? Pretpostavimo da se može proizvesti decimalni broj jedinica.

4. Tvornica za proizvodnju računala proizvodi miševe, tipkovnice i igraće palice za videoigre. Jedinična dobit, potrošnja po jedinici rada, mjesečna potražnja i potrošnja strojnog vremena po jedinici prikazani su u sljedećoj tablici:

   	Miš	Tipkovnice	Igraće palice
   Dobit/jedinica	$8	$11	9 kn
   Potrošnja radne snage/jedinica	0,2 sata	0,3 sata	24 sata
   Vrijeme stroja/jedinica	0,04 sata	0,055 h	0,04 sata
   Mjesečna potražnja	15 000	27,000	11,000

5. Svakog mjeseca na raspolaganju je ukupno 13.000 radnih sati i 3000 sati strojnog vremena. Kako proizvođač može maksimizirati svoj mjesečni doprinos dobiti od biljke?

6. Riješite naš primjer lijeka pod pretpostavkom da se mora zadovoljiti minimalna potražnja od 200 jedinica za svaki lijek.

7. Jason izrađuje dijamantne narukvice, ogrlice i naušnice. Želi raditi najviše 160 sati mjesečno. Ima 800 unci dijamanata. Dobit, radno vrijeme i unce dijamanata potrebne za proizvodnju svakog proizvoda navedeni su u nastavku. Ako je potražnja za svakim proizvodom neograničena, kako Jason može maksimizirati svoj profit?

   Proizvod	Jedinična dobit	Radnih sati po jedinici	Unci dijamanata po jedinici
   Narukvica	1500,00 kn	.35	1,2
   Ogrlica	1000 kn	.15	.75
   Naušnice	500 kn	0,05	.5