Uporaba reševalca za določanje optimalne izdelka

Kako lahko določim mesečno mešanico izdelkov, ki povečuje donosnost?

Podjetja morajo pogosto mesečno določiti količino posameznega izdelka za proizvodnjo. V svoji najpreprostejši obliki, izdelek mix problem vključuje, kako določiti količino posameznega izdelka, ki naj bi se proizvedla v mesecu za maksimiziranje dobička. Mešanica izdelkov običajno upošteva te omejitve:

• V mešanici izdelkov ni mogoče uporabiti več virov, kot so na voljo.

• Povpraševanje po posameznem izdelku je omejeno. V enem mesecu ne moremo proizvajati več izdelka, kot narekuje povpraševanje, saj se odvečna proizvodnja zapravlja (na primer, kable drog).

Zdaj bomo odpravili spodnji primer težave z mešanico izdelkov. Rešitev za to težavo najdete v pogovornem oknu z Prodmix.xlsx, prikazano na sliki 27-1.

Denimo, da delamo za družbo, ki proizvaja šest različnih proizvodov v njihovi rastlini. Proizvodnja vsakega izdelka zahteva delo in surovino. Vrstica 4 na sliki 27-1 prikazuje ure dela, potrebne za proizvodnjo funt posameznega izdelka, in vrstica 5 kaže funtov surovin, potrebnih za proizvodnjo funta posameznega izdelka. Za proizvodnjo funta izdelka 1 potrebujete na primer šest ur dela in 3,2 kilograma surovine. Za vsako zdravilo se cena na funt poda v vrstici 6, strošek na enoto na funt je poda v vrstici 7, prispevek za dobiček na funt pa je poda v vrstici 9. Izdelek 2 na primer proda za 11,00 USD na funt, strošek na enoto pa je 5,70 USD na funt in prispeva 5,30 USD dobička na funt. Povpraševanje po posameznih drog v mesecu se poda v vrstici 8. Povpraševanje po izdelku 3 je na primer 1041 funtov. Ta mesec, 4500 ur dela in 1600 funtov surovin so na voljo. Kako lahko to podjetje maksimizira svoj mesečni dobiček?

Če ne vemo nič o Excelovem reševalniku, bi to težavo napadli tako, da bi gradnji delovnega lista spremljali dobiček in uporabo virov, povezano z mešanico izdelkov. Potem bi uporabili poskus in napake, da se razlikujejo izdelek mix optimizirati dobiček brez uporabe več dela ali surovine, kot je na voljo, in brez proizvodnje drog, ki presegajo povpraševanje. Reševalec v tem postopku uporabljamo le v fazi preskušanja in napake. Reševalec je mehanizem za optimizacijo, ki brezhibno izvaja iskanje po preskusih in napakah.

Ključno pri reševanju težave s kombinacijo izdelkov je učinkovito računanje uporabe virov in dobička, povezanega s katero koli kombinacijo izdelkov. Pomembno orodje, s katerem lahko ustvarimo ta izračun, je SUMPRODUCT računanje. Funkcija SUMPRODUCT pomnoži ustrezne vrednosti v obsegih celic in vrne vsoto teh vrednosti. Vsak obseg celic, uporabljen v vrednotenju SUMPRODUCT, mora imeti enake mere, kar pomeni, da lahko uporabite SUMPRODUCT z dvema vrsticama ali dvema stolpcema, vendar ne z enim stolpcem in eno vrstico.

Kot primer uporabe funkcije SUMPRODUCT v našem primeru izdelka poskusite izračunati porabo virov. Našo porabo dela izračuna

(Dela, ki se uporabljajo na funt drog 1)*(Drug 1 funt proizvaja)+
(Dela, ki se uporabljajo na funt drog 2)*(Drug 2 kilogramov proizvaja) + ...
(Dela, ki se uporabljajo na funt drog 6)*(Drug 6 kilogramov proizvaja)

Porabo dela bi lahko izračunali na bolj utrudljiv način, kot je D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. Podobno lahko uporabo surovin izračunamo kot D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Vendar pa je vnašanje teh formul na delovni list za šest izdelkov zamudno. Predstavljajte si, kako dolgo bi trajalo, če bi delali s podjetjem, ki je v njihovi tovarni izdelalo na primer 50 izdelkov. Veliko lažji način za izračun dela in uporabe surovin je kopiranje formule iz D14 v D15 SUMPRODUCT($D$2:$I$2,D4:I4). Ta formula izračuna D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (ki je naša uporaba dela), vendar je veliko lažje odpreti. Opazili boste, da uporabim znak $ z obsegom D2:I2, tako da, ko kopiram formulo, še vedno zajamem mešanico izdelkov iz 2. vrstice. Formula v celici D15 izračuna uporabo surovin.

Na podoben način je naš dobiček določen z

(Drug 1 dobiček na funt)*(Drug 1 kilogram proizveden) +
(Drug 2 dobiček na funt)*(Drug 2 kilogramov proizvedena) + ...
(Drug 6 dobiček na funt)*(Drug 6 kilogramov proizvedena)

Dobiček je preprosto izračunan v celici D12 s formulo SUMPRODUCT(D9:I9,$D$2:$I$2).

Zdaj lahko prepoznamo tri komponente našega modela mešanega reševalca.

• Ciljna celica. Naš cilj je maksimizirati dobiček (izračunan v celici D12).

• Celice, ki se spreminjajo. Število funtov, izdelanih za vsak izdelek (navedeno v obsegu celic D2:I2)

• Omejitve. Imamo te omejitve:

  • Ne uporabljajte več dela ali surovin, kot je na voljo. To pomeni, da morajo biti vrednosti v celicah D14:D15 (uporabljeni viri) manjše ali enake vrednostim v celicah F14:F15 (razpoložljivi viri).

  • Ne proizvajajo več drog, kot je v povpraševanju. To pomeni, da morajo biti vrednosti v celicah D2:I2 (funtov, izdelanih za vsako zdravilo) manjše ali enako povpraševanju po vsaki drogi (navedene v celicah D8:I8).

  • Ne moremo ustvariti negativne količine drog.

Pokazal vam bom, kako vnesete ciljno celico, spremenite celice in omejitve v reševalca. Nato morate le še klikniti gumb »Reši«, da poiščete mešanico izdelkov z maksimiranjem dobička!

Če želite začeti, kliknite zavihek Podatki in v skupini Analiza kliknite Reševalec.

Prikaže se pogovorno okno Parametri reševalca, kot je prikazano na sliki 27-2.

Kliknite polje Nastavi ciljno celico in nato izberite našo celico z dobičkom (celica D12). Kliknite s spreminjanjem celic polje in nato pokažite na obseg D2: I2, ki vsebuje funtov, izdelanih iz vsakega zdravila. Pogovorno okno bi moralo zdaj videti sliko 27-3.

Zdaj smo pripravljeni, da modelu dodamo omejitve. Kliknite gumb Dodaj. Videli boste pogovorno okno Dodaj omejitev, prikazano na sliki 27-4.

Če želite dodati omejitve uporabe virov, kliknite polje Sklic na celico in nato izberite obseg D14:D15. Na <izberite <= . Kliknite polje Omejitev in izberite obseg celic F14:F15. Pogovorno okno Dodaj omejitev bi moralo biti videti kot slika 27-5.

Ko reševalec poskusi različne vrednosti za celice, ki se spreminjajo, smo zdaj zagotovili, da bodo obravnavane le kombinacije, ki izpolnjujejo oba D14<=F14 (uporabljeno delo je manjše ali enako delu, ki je na voljo) in D15<=F15 (uporabljena surovina je manjša ali enaka razpoložljivi surovini). Kliknite Dodaj, da vnesete omejitve zahtev. Izpolnite pogovorno okno Dodaj omejitev, kot je prikazano na sliki 27-6.

Dodajanje teh omejitev zagotavlja, da bodo obravnavane le kombinacije, ki izpolnjujejo te parametre, ko reševalec poskusi različne kombinacije za vrednosti celice, ki se spreminjajo:

• D2<=D8 (količina proizvedenega zdravila 1 je manjša ali enaka povpraševanju po drogi 1)

• E2<=E8 (količina proizvedenega zdravila 2 je manjša ali enaka povpraševanju po drogi 2)

• F2<=F8 (količina proizvedenega zdravila 3 je manjša ali enaka povpraševanju po drogi 3)

• G2<=G8 (količina proizvedenega zdravila 4 je manjša ali enaka povpraševanju po drogi 4)

• H2<=H8 (količina proizvedenega zdravila 5 je manjša ali enaka povpraševanju po drogi 5)

• I2<=I8 (količina proizvedenega zdravila 6 je manjša ali enaka povpraševanju po drogi 6)

V pogovornem oknu Dodaj omejitev kliknite V redu. Okno reševalca bi moralo biti videti kot slika 27-7.

V pogovorno okno Možnosti reševalca smo vnesli omejitev, da mora biti spreminjanje celic nenegativno. Kliknite gumb Možnosti v pogovornem oknu Parametri reševalca. Potrdite polje Predpostavi linearni model in Predpostavi, da ni negativni, kot je prikazano na sliki 27-8 na naslednji strani. Kliknite »V redu«.

Če potrdite polje Predpostavi, da ni negativno, zagotovite, da Reševalec upošteva le kombinacije spreminjanja celic, v katerih vsaka celica, ki se spreminja, predvideva nenegacialno vrednost. Preverili smo polje Predpostavi linearni model, ker je težava z mešanico izdelkov posebna vrsta težave z reševalnikom, ki se imenuje linearni model. V bistvu je model reševalca linearen pod temi pogoji:

• Ciljna celica se izračuna tako, da se sešteje pogoje obrazca (spreminjajo celice)*(konstanta).

• Vsaka omejitev ustreza zahtevi »linearnega modela«. To pomeni, da je vsaka omejitev ovrednotena tako, da se sešteje pogoje obrazca (spreminjanje celice )*( konstanta) in primerja vsote s konstanto.

Zakaj je težava z reševalnikom linearna? Naša ciljna celica (dobiček) je izračunana kot

(Drug 1 dobiček na funt)*(Drug 1 kilogram proizveden) +
(Drug 2 dobiček na funt)*(Drug 2 kilogramov proizvedena) + ...
(Drug 6 dobiček na funt)*(Drug 6 kilogramov proizvedena)

To računanje sledi vzorcu, v katerem je vrednost ciljne celice izpeljana tako, da se sešteje izraze v obrazcu (spreminja celica )*(konstanta).

Naša omejitev dela se vrednoti s primerjavo vrednosti, izpeljanih iz (Labor uporablja na funt drug 1)*(Drug 1 funt proizvaja) + (Dela, ki se uporabljajo na funt drug 2)*(Drug 2 kilogramov proizvaja)+ ... (Delajte named na funt drug 6)*(Drug 6 kilogramov proizvaja) za delo na voljo.

Zato se omejitev dela ovrednoti tako, da se sešteje pogoje obrazca (spreminjanje celice )*(konstanta) in primerja vsote s konstanto. Omejitev dela in omejena surovina izpolnjujeta zahtevo linearnega modela.

Naše omejitve zahtev so v obliki

(Proizvedeno je zdravilo 1)<=(Povpraševanje po drogah 1)
(Proizvedeno je zdravilo 2)<=(Povpraševanje po drogah 2)
§
(Proizvedeno je zdravilo 6)<=(Povpraševanje po drogah 6)

Vsaka omejitev zahteve ustreza tudi zahtevi linearnega modela, ker je vsaka ovrednotena tako, da se sešteje pogoje obrazca (spreminjajoč se celica )*( konstanta) in primerja vsote s konstanto.

Zakaj bi nam bilo mar, če je model mešanja izdelkov linearen?

• Če je model reševalca linearen in izberemo Predpostavimo linearni model, bo reševalec zagotovo našel optimalno rešitev za model reševalca. Če model reševalca ni linearen, bo reševalec morda našel optimalno rešitev ali ne.

• Če je model reševalca linearen in izberete Predpostavi linearni model, reševalec za iskanje optimalne rešitve modela uporablja zelo učinkovit algoritem (način simpleksa). Če je model reševalca linearen in ne izberemo možnosti Predpostavi linearni model, reševalec uporablja zelo neučinkovit algoritem (metodo GRG2) in ima lahko težave pri iskanju optimalne rešitve modela.

Ko v pogovornem oknu Možnosti reševalca kliknete V redu, se vrnemo v glavno pogovorno okno Reševalec, ki je prikazano na sliki 27-7. Ko kliknete Reši, Reševalec izračuna optimalno rešitev (če obstaja) za model mešanega izdelka. Kot sem navedel v poglavju 26, optimalno rešitev za izdelek mix model bi bil nabor spreminjajočih se vrednosti celic (funtov, izdelanih iz vsake drog), ki maksimira dobiček v naboru vseh izvedljivih rešitev. Tudi izvedljiva rešitev je nabor spreminjanih vrednosti celic, ki izpolnjujejo vse omejitve. Spreminjanje vrednosti celic, prikazanih na sliki 27–9, je izvedljiva rešitev, saj so vse ravni proizvodnje nenegativne, ravni proizvodnje ne presegajo povpraševanja in uporaba virov ne presega razpoložljivih virov.

Spreminjanje vrednosti celic, prikazanih na sliki 27–10 na naslednji strani, predstavlja rešitev, ki jo je mogoče odstraniti iz teh razlogov:

• Proizvedemo več drog 5, kot je povpraševanje po njem.

• Uporabljamo več dela, kot je na voljo.

• Uporabljamo več surovin, kot jih je na voljo.

Ko kliknete Reši, reševalec hitro najde optimalno rešitev, prikazano na sliki 27-11. Izbrati morate Možnost Obdrži rešitev reševalca, da ohranite optimalne vrednosti rešitev na delovnem listu.

Naše zdravilo podjetje lahko poveča svoj mesečni dobiček na ravni $ 6.625.20 s proizvodnjo 596,67 kilogramov drug 4, 1084 kilogramov drog 5, in nobena druga zdravila! Ne moremo ugotoviti, ali lahko dosežemo najvišji dobiček v višini 6.625,20 $ na druge načine. Prepričani smo lahko le, da z omejenimi viri in povpraševanjem ta mesec ne moremo narediti več kot 6.627,20 $.