Uporaba reševalca za določanje optimalne mešanice izdelkov

V tem članku je opisana uporaba reševalca, dodatka za Microsoft Excel, ki ga lahko uporabite za analizo kaj-če, da določite optimalno mešanico izdelkov.

Kako lahko določim mesečno ponudbo izdelkov, ki povečuje dobičkonosnost?

Podjetja morajo pogosto določiti količino vsakega izdelka za proizvodnjo na mesečni ravni. V svoji najpreprostejši obliki problem mešanice izdelkov vključuje, kako določiti količino vsakega izdelka, ki ga je treba proizvesti v enem mesecu, da bi povečali dobiček. Proizvodni program mora običajno izpolnjevati naslednje omejitve:

• Paleta izdelkov ne more porabiti več virov, kot jih je na voljo.
• Povpraševanje po vsakem izdelku je omejeno. V enem mesecu ne moremo proizvesti več izdelka, kot narekuje povpraševanje, ker je presežek proizvodnje zapravljen (na primer pokvarljivo zdravilo).

Zdaj pa rešimo naslednji primer problema mešanice izdelkov. Rešitev za to težavo najdete v Prodmix.xlsx datoteke, ki je prikazana na sliki 27-1.

Recimo, da delamo za farmacevtsko podjetje, ki v svoji tovarni proizvaja šest različnih izdelkov. Proizvodnja vsakega izdelka zahteva delo in surovine. Vrstica 4 na sliki 27-1 prikazuje ure dela, potrebne za proizvodnjo funta vsakega izdelka, vrstica 5 pa funte surovine, potrebne za proizvodnjo funta vsakega izdelka. Na primer, proizvodnja funta izdelka 1 zahteva šest ur dela in 3, 2 kilograma surovine. Za vsako zdravilo je cena na funt navedena v vrstici 6, stroški na enoto na funt so navedeni v vrstici 7, prispevek dobička na funt pa je naveden v vrstici 9. Na primer, izdelek 2 se prodaja za 11,00 USD na funt, povzroči stroške na enoto v višini 5,70 USD na funt in prispeva 5,30 USD dobička na funt. Mesečno povpraševanje po vsakem zdravilu je navedeno v vrstici 8. Na primer, povpraševanje po izdelku 3 je 1041 funtov. Ta mesec je na voljo 4500 ur dela in 1600 kilogramov surovin. Kako lahko to podjetje poveča svoj mesečni dobiček?

Če ne bi vedeli ničesar o programu Excel Solver, bi to težavo obravnavali tako, da bi ustvarili delovni list za sledenje dobičku in porabi virov, povezanih s kombinacijo izdelkov. Nato bi uporabili poskuse in napake za spreminjanje proizvodne mešanice, da bi optimizirali dobiček, ne da bi porabili več delovne sile ali surovin, kot je na voljo, in brez proizvodnje zdravil, ki presega povpraševanje. Solver v tem postopku uporabljamo samo na stopnji poskusov in napak. V bistvu je Solver optimiziralni mehanizem, ki brezhibno izvaja iskanje poskusov in napak.

Ključ do reševanja problema mešanice izdelkov je učinkovito izračunavanje porabe virov in dobička, povezanega s katero koli ponudbo izdelkov. Pomembno orodje, ki ga lahko uporabimo za ta izračun, je funkcija SUMPRODUCT. Funkcija SUMPRODUCT pomnoži ustrezne vrednosti v obsegih celic in vrne vsoto teh vrednosti. Vsak obseg celic, uporabljen pri ocenjevanju funkcije SUMPRODUCT, mora imeti enake razsežnosti, kar pomeni, da lahko uporabite funkcijo SUMPRODUCT z dvema vrsticama ali dvema stolpcema, ne pa z enim stolpcem in eno vrstico.

Kot primer, kako lahko uporabimo funkcijo SUMPRODUCT v primeru mešanice izdelkov, poskusimo izračunati porabo virov. Naša poraba delovne sile se izračuna z

(Delovna sila, porabljena na kilogram zdravila 1)*(Proizvedeno zdravilo 1 funt)+
(Delovna sila, porabljena na kilogram zdravila 2) * (Proizvedeno zdravilo 2 funta) + ...
(Porabljena delovna sila na kilogram zdravila 6) * (Proizvedeno zdravilo 6 funtov)

Porabo dela bi lahko izračunali na bolj dolgočasen način kot D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. Podobno bi lahko porabo surovin izračunali kot D2 * D5 + E2 * E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. Vendar pa je vnos teh formul na delovni list za šest izdelkov dolgotrajen. Predstavljajte si, koliko časa bi trajalo, če bi sodelovali s podjetjem, ki je na primer proizvedlo 50 izdelkov v njihovi tovarni. Veliko lažji način za izračun porabe dela in surovin je kopiranje iz D14 v D15 formule SUMPRODUCT($D$2:$I$2,D4:I4). Ta formula izračuna D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (kar je naša poraba dela), vendar jo je veliko lažje vnesti! Opazite, da uporabljam znak $ z obsegom D2:I2, tako da ko kopiram formulo, še vedno zajamem mešanico izdelkov iz vrstice 2. Formula v celici D15 izračuna porabo surovin.

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

(Zdravilo 1 dobiček na funt) * (Proizvedeno zdravilo 1 funt) +
(Zdravilo 2 dobiček na funt) * (Proizvedeno zdravilo 2 funta) + ...
(Zdravilo 6 dobiček na funt) * (Proizvedeno zdravilo 6 funtov)

Dobiček se enostavno izračuna v celici D12 s formulo SUMPRODUCT(D9: I9, $D 2 $: $I 2 $).

Zdaj lahko identificiramo tri komponente našega modela reševalca.

• Ciljna celica. Naš cilj je povečati dobiček (izračunano v celici D12).

• Spreminjanje celic. Število proizvedenih funtov vsakega izdelka (navedeno v območju celic D2:I2)

• Omejitve. Imamo naslednje omejitve:

  • Ne uporabljajte več delovne sile 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 proizvajajte več zdravila, kot je povpraševanje. To pomeni, da morajo biti vrednosti v celicah D2: I2 (funti, proizvedeni iz vsakega zdravila) manjše ali enake povpraševanju po vsakem zdravilu (navedenem v celicah D8: I8).
  • Ne moremo proizvesti negativne količine nobenega zdravila.

Pokazal vam bom, kako vnesti ciljno celico, spremeniti celice in omejitve v reševalca. Nato morate le klikniti gumb Reši, da najdete mešanico izdelkov, ki povečuje dobiček!

Če želite začeti, kliknite zavihek Podatki in nato 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 profitno celico (celica D12). Kliknite polje S spreminjanjem celic in nato pokažite na obseg D2: I2, ki vsebuje kilograme, proizvedene iz vsakega zdravila. Pogovorno okno bi moralo zdaj videti sliko 27-3.

Zdaj smo pripravljeni dodati omejitve v model. Kliknite gumb Dodaj. Prikazano bo pogovorno okno Dodaj omejitev, ki je prikazano na sliki 27-4.

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

Zdaj smo zagotovili, da ko Solver poskuša različne vrednosti za spreminjajoče se celice, se upoštevajo samo kombinacije, ki izpolnjujejo D14< = F14 (uporabljena delovna sila je manjša ali enaka razpoložljivi sili) in D15< = F15 (uporabljena surovina je manjša ali enaka razpoložljivi surovini). Kliknite Dodaj, da vnesete omejitve povpraševanja. Izpolnite pogovorno okno Dodaj omejitev, kot je prikazano na sliki 27-6.

Če dodate te omejitve, zagotovite, da se pri poskusu reševanja različnih kombinacij za spreminjajoče se vrednosti celic upoštevajo le kombinacije, ki ustrezajo tem parametrom:

• D2< = D8 (proizvedena količina zdravila 1 je manjša ali enaka povpraševanju po zdravilu 1)
• E2< = E8 (količina proizvedenega zdravila 2 je manjša ali enaka povpraševanju po zdravilu 2)
• F2< = F8 (proizvedena količina proizvedenega zdravila 3 je manjša ali enaka povpraševanju po zdravilu 3)
• G2< = G8 (proizvedena količina proizvedenega zdravila 4 je manjša ali enaka povpraševanju po zdravilu 4)
• H2< = H8 (proizvedena količina proizvedenega zdravila 5 je manjša ali enaka povpraševanju po zdravilu 5)
• I2< = I8 (proizvedena količina proizvedenega zdravila 6 je manjša ali enaka povpraševanju po zdravilu 6)

Kliknite V redu v pogovornem oknu Dodaj omejitev. Okno reševalca bi moralo biti videti kot na sliki 27-7.

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

Če potrdite polje Predpostavimo nenegativno, zagotovite, da reševalec upošteva le kombinacije spreminjajočih se celic, v katerih vsaka spreminjajoča se celica prevzame nenegativno vrednost. Potrdili smo polje Predpostavimo linearni model, ker je problem mešanice izdelkov posebna vrsta problema reševalca, imenovana linearni model. V bistvu je model reševalca linearen pod naslednjimi pogoji:

• Ciljna celica se izračuna tako, da seštejemo izraze oblike (spreminjajoča se celica)*(konstanta).
• Vsaka omejitev izpolnjuje »zahtevo linearnega modela«. To pomeni, da se vsaka omejitev ovrednoti tako, da seštejemo izraze oblike (spreminjajoča se celica)*(konstanta) in primerjamo vsote s konstanto.

Zakaj je ta težava reševalca linearna? Naša ciljna celica (dobiček) se izračuna kot

(Zdravilo 1 dobiček na funt) * (Proizvedeno zdravilo 1 funt) +
(Zdravilo 2 dobiček na funt) * (Proizvedeno zdravilo 2 funta) + ...
(Zdravilo 6 dobiček na funt) * (Proizvedeno zdravilo 6 funtov)

Ta izračun sledi vzorcu, v katerem je vrednost ciljne celice izpeljana s seštevanjem izrazov oblike (spreminjajoča se celica)*(konstanta).

Naša delovna omejitev se ocenjuje s primerjavo vrednosti, ki izhaja iz (porabljena delovna sila na kilogram zdravila 1) * (proizvedeno zdravilo 1 funt) + (porabljeno delo na kilogram zdravila 2) * (proizvedeno zdravilo 2 funta) + ... (Delovna sila, porabljena na kilogram zdravila 6) * (Proizvedeno zdravilo 6 funtov) na razpoložljivo delo.

Zato se delovna omejitev oceni tako, da seštejemo izraze oblike (spreminjajoča se celica)*(konstanta) in primerjamo vsote s konstanto. Tako omejitev dela kot omejitev surovin izpolnjujeta zahteve linearnega modela.

Naše omejitve povpraševanja imajo obliko

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

Vsaka omejitev povpraševanja izpolnjuje tudi zahtevo linearnega modela, ker se vsaka ovrednoti tako, da seštejemo izraze oblike (spreminjajoča se celica)*(konstanta) in primerjamo vsote s konstanto.

Ko smo pokazali, da je naš model mešanice izdelkov linearni model, zakaj bi nas to zanimalo?

• Če je model reševalca linearen in izberemo Prevzem linearni model, bo reševalec zagotovo našel optimalno rešitev za model reševalca. Če model reševalca ni linearen, lahko Solver najde optimalno rešitev ali pa ne.
• Če je model reševalca linearen in izberemo Predej linearni model, Solver uporabi zelo učinkovit algoritem (simpleks metodo), da poišče optimalno rešitev modela. Če je model reševalca linearen in ne izberemo možnosti Predpostavi linearni model, Solver uporablja zelo neučinkovit algoritem (metoda GRG2) in ima morda 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ševalca, ki je prikazano prej na sliki 27-7. Ko kliknemo Reši, Solver izračuna optimalno rešitev (če obstaja) za naš model palete izdelkov. Kot sem navedel v poglavju 26, bi bila optimalna rešitev za model mešanice izdelkov niz spreminjajočih se celičnih vrednosti (funtov, proizvedenih iz vsakega zdravila), ki povečuje dobiček nad nizom vseh izvedljivih rešitev. Še enkrat, izvedljiva rešitev je niz spreminjajočih se vrednosti celic, ki izpolnjujejo vse omejitve. Spreminjajoče se vrednosti celic, prikazane na sliki 27-9, so izvedljiva rešitev, ker so vse ravni proizvodnje nenegativne, ravni proizvodnje ne presegajo povpraševanja in uporaba virov ne presega razpoložljivih virov.

Spreminjajoče se vrednosti celic, prikazane na sliki 27-10 na naslednji strani, predstavljajo neizvedljivo rešitev iz naslednjih razlogov:

• Proizvajamo več zdravila 5, kot je povpraševanje po njem.
• Porabimo več delovne sile, kot je na voljo.
• Uporabljamo več surovin, kot je na voljo.

Po kliku na Reši reševalec hitro najde optimalno rešitev, prikazano na sliki 27-11. Izbrati morate Ohrani rešitev reševalca, da ohranite optimalne vrednosti rešitve na delovnem listu.

Naše farmacevtsko podjetje lahko poveča svoj mesečni dobiček na ravni 6.625,20 USD s proizvodnjo 596,67 funtov zdravila 4, 1084 funtov zdravila 5 in nobenega drugega zdravila! Ne moremo določiti, ali lahko dosežemo največji dobiček v višini 6.625,20 USD na druge načine. Vse, kar smo lahko prepričani, je, da z našimi omejenimi viri in povpraševanjem ta mesec ni mogoče zaslužiti več kot 6.627,20 dolarjev.

Ali ima model reševalca vedno rešitev?

Recimo, da je treba zadovoljiti povpraševanje po vsakem izdelku. (Oglejte si delovni list »Ni izvedljive rešitve « v datoteki Prodmix.xlsx.) Nato moramo spremeniti omejitve povpraševanja iz D2: I2< = D8: I8 v D2: I2> = D8: I8. To naredite tako, da odprete reševalca, izberete omejitev D2:I2<=D8:I8 in nato kliknete Spremeni. Prikaže se pogovorno okno Spremeni omejitev, prikazano na sliki 27-12.

Izberite >= in kliknite V redu. Zdaj smo zagotovili, da bo Solver razmislil o spremembi samo vrednosti celic, ki ustrezajo vsem zahtevam. Ko kliknete Reši, se prikaže sporočilo »Reševalec ni mogel najti izvedljive rešitve«. To sporočilo ne pomeni, da smo naredili napako v našem modelu, temveč da z našimi omejenimi viri ne moremo zadovoljiti povpraševanja po vseh izdelkih. Solver nam preprosto pravi, da če želimo zadovoljiti povpraševanje po vsakem izdelku, moramo dodati več delovne sile, več surovin ali več obojega.

Kaj pomeni, če model reševalca da rezultat Vrednosti nabora ne konvergujejo?

Poglejmo, kaj se zgodi, če dovolimo neomejeno povpraševanje po vsakem izdelku in dovolimo proizvodnjo negativnih količin vsakega zdravila. (To težavo reševalca si lahko ogledate na delovnem listu Nastavi vrednosti ne konvergira v datoteki Prodmix.xlsx.) Če želite poiskati najboljšo rešitev za to situacijo, odprite reševalca, kliknite gumb Možnosti in počistite polje Predpostavi nenegativno. V pogovornem oknu Parametri reševalca izberite omejitev povpraševanja D2:I2<=D8:I8 in nato kliknite Izbriši, da odstranite omejitev. Ko kliknete Reši, Solver vrne sporočilo »Nastavi vrednosti celic ne konverguje«. To sporočilo pomeni, da če želimo povečati ciljno celico (kot v našem primeru), obstajajo izvedljive rešitve s poljubno velikimi vrednostmi ciljnih celic. (Če je treba ciljno celico zmanjšati, sporočilo »Nastavljene vrednosti celic se ne zbližujejo« pomeni, da obstajajo izvedljive rešitve s poljubno majhnimi vrednostmi ciljnih celic.) V naši situaciji z omogočanjem negativne proizvodnje droge dejansko "ustvarjamo" vire, ki jih lahko uporabimo za proizvodnjo poljubno velikih količin drugih zdravil. Glede na naše neomejeno povpraševanje nam to omogoča neomejen dobiček. V resnični situaciji ne moremo zaslužiti neskončne količine denarja. Skratka, če vidite »Nastavljene vrednosti se ne zbližujejo«, ima vaš model napako.

Težave

1. Recimo, da lahko naše farmacevtsko podjetje kupi do 500 ur dela za 1 dolar več na uro od trenutnih stroškov dela. Kako lahko povečamo dobiček?

2. V obratu za proizvodnjo čipov štirje tehniki (A, B, C in D) proizvajajo tri izdelke (izdelki 1, 2 in 3). Ta mesec lahko proizvajalec čipov proda 80 enot izdelka 1, 50 enot izdelka 2 in največ 50 enot izdelka 3. Tehnik A lahko izdela samo izdelke 1 in 3. Tehnik B lahko izdela samo izdelke 1 in 2. Tehnik C lahko izdela samo izdelek 3. Tehnik D lahko izdela samo izdelek 2. Za vsako proizvedeno enoto izdelki prispevajo naslednji dobiček: Izdelek 1, 6 USD; Izdelek 2, 7 dolarjev; in izdelek 3, 10 dolarjev. Čas (v urah), ki ga vsak tehnik potrebuje za izdelavo izdelka, je naslednji:

   Izdelek	Tehnik A	Tehnik B	Tehnik C	Tehnik D
   1	2	2,5	Ne more storiti	Ne more storiti
   2	Ne more storiti	3	Ne more storiti	3,5
   3	3	Ne more storiti	4	Ne more storiti

3. Vsak tehnik lahko dela do 120 ur na mesec. Kako lahko proizvajalec čipov poveča svoj mesečni dobiček? Predpostavimo, da je mogoče proizvesti delno število enot.

4. Tovarna za proizvodnjo računalnikov proizvaja miške, tipkovnice in igralne palice za video igre. Dobiček na enoto, poraba dela na enoto, mesečno povpraševanje in poraba strojnega časa na enoto so navedeni v naslednji tabeli:

   	Miška	Tipkovnice	Igralne palice
   Dobiček/enota	8 dolarjev	11 dolarjev	9 dolarjev
   Poraba delovne sile/enota	2 uri	.3 ure	.24 ur
   Čas stroja/enota	0,04 ure	0,055 ure	0,04 ure
   Mesečno povpraševanje	15.000	27,000	11,000

5. Vsak mesec je na voljo skupaj 13.000 delovnih ur in 3000 ur strojnega časa. Kako lahko proizvajalec poveča svoj mesečni prispevek k dobičku iz obrata?

6. Razrešite naš primer droge ob predpostavki, da je treba zadovoljiti minimalno povpraševanje 200 enot za vsako zdravilo.

7. Jason izdeluje diamantne zapestnice, ogrlice in uhane. Želi delati največ 160 ur na mesec. Ima 800 unč diamantov. Dobiček, delovni čas in unče diamantov, potrebnih za proizvodnjo vsakega izdelka, so navedeni spodaj. Če je povpraševanje po vsakem izdelku neomejeno, kako lahko Jason poveča svoj dobiček?

   Izdelek	Dobiček enote	Delovne ure na enoto	Unča diamantov na enoto
   Zapestnica	300 EUR	.35	1,2
   Ogrlica	200 €	.15	.75
   Uhani	100 €	.05	.5