Uporaba reševalca za določanje optimalne kombinacije izdelkov

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

Podjetja morajo pogosto določiti količino posameznega izdelka, ki ga je treba proizvajati mesečno. V svoji najpreprostejši obliki se težava s mešanjem izdelkov nanaša na to, kako določiti količino posameznega izdelka, ki ga je treba proizvajati v mesecu, da povečate dobiček. Mešanica izdelkov mora običajno upoštevati te omejitve:

• Mešanica izdelkov ne more uporabiti več virov, kot jih je na voljo.

• Za vsak izdelek obstaja omejeno povpraševanje. Ne moremo proizvesti več izdelka med mesecem, kot zahteva narekuje, ker je presežna proizvodnja zapravljena (na primer pokvarljiva droga).

Zdaj razrešimo naslednji primer težave z mešanjem izdelkov. Rešitev za to težavo najdete v datoteki Prodmix. xlsx, ki je prikazana na sliki 27-1.

Recimo, da delamo za podjetje z zdravili, ki proizvede šest različnih izdelkov v obratu. Proizvodnja posameznega izdelka zahteva delo in surovine. Vrstica 4 na sliki 27-1 prikazuje ure dela, ki so potrebne za proizvodnjo funta posameznega izdelka, in vrstica 5 prikazuje funtov surovega materiala, ki je potreben za proizvodnjo funta posameznega izdelka. Na primer, ki proizvaja funt izdelka 1 zahteva šest ur dela in 3,2 funtov surovega materiala. Za vsako drogo je cena na funt navedena v vrstici 6, cena enote na funt je navedena v vrstici 7 in prispevek dobička na funt je v vrstici 9. Na primer, izdelek 2 prodaja za $11,00 na funt, povzroči stroške enote za $5,70 na funt in prispeva $5,30 dobiček na funt. Povpraševanje po mesecu za vsako zdravilo je navedeno v vrstici 8. Povpraševanje po izdelku 3 je na primer 1041 funtov. Ta mesec so na voljo 4500 ur dela in 1600 kilogramov surovin. Kako lahko to podjetje poveča mesečni dobiček?

Če ne bi nič vedeli o programu Excel reševalec, bi to težavo napadeli z ustvarjanjem delovnega lista za spremljanje dobička in uporabe virov, ki je povezano z mešanico izdelkov. Nato bi uporabili preskusno različico in napako, s katero bi lahko spreminjali mešanico izdelka, da bi optimizirali dobiček, ne da bi uporabili več dela ali surovine, kot je na voljo, in ne da bi izkoristili kakršno koli drogo V tem postopku uporabljamo reševalca le na stopnji poskusov in napak. Reševalec je v bistvu mehanizem optimizacije, ki brezhibno izvaja iskanje poskusov in napak.

Ključ za rešitev težave z mešanjem izdelkov je učinkovito izračunavanje uporabe virov in dobička, povezanega z morebitno mešanico izdelkov. Pomembno orodje, ki ga lahko uporabite za izračun funkcije SUMPRODUCT. Funkcija SUMPRODUCT Zmnoži ustrezne vrednosti v obsegih celic in vrne vsoto teh vrednosti. Vsak obseg celic, ki je uporabljen v oceni SUMPRODUCT, mora imeti enake dimenzije, kar pomeni, da lahko uporabite SUMPRODUCT z dvema vrsticama ali dvema stolpcema, vendar ne z enim stolpcem in eno vrstico.

Kot primer, kako lahko uporabite funkcijo SUMPRODUCT v našem primeru za mešanje izdelkov, poskusimo izračunati uporabo naših virov. Naša raba dela je izračunana s

(Delo se uporablja na funt drog 1) * (droga 1 £ proizvedena) +
(delo se uporablja na funt drog 2) * (droge 2 funtov proizvedena) +...
(Delo se uporablja na funt drog 6) * (droga 6 funtov proizvedenega)

Uporabo delovne sile lahko izračunate na bolj dolgočasen način kot D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + i2 * i4. Podobno se lahko uporaba surovin izračuna kot D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + i2 * i5. Vendar pa vnos teh formul na delovnem listu za šest izdelkov je dolgotrajen. Predstavljajte si, kako dolgo bo trajalo, če ste delali z družbo, ki je izdelala, na primer, 50 izdelkov v obratu. Veliko lažji način izračunavanja dela in uporabe 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 (ki je naša uporaba dela), vendar je veliko lažje vnesti! Obvestilo, da uporabljam znak» $ «z obsegom D2: i2 tako, da ko kopiram formulo, še vedno zajamem mešanico izdelka iz vrstice 2. Formula v celici D15 izračuna uporabo surovin.

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

(Droga 1 dobiček na funt) * (droga 1 £ proizvedena) +
(droga 2 dobiček na funt) * (droge 2 funtov proizvedena) +...
(Droga 6 dobička na funt) * (proizvedena droga 6 funtov)

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

Zdaj lahko identificiramo tri komponente modela reševalca izdelka.

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

• Spreminjanje celic.Število kilogramov, proizvedenih na posameznem izdelku (na seznamu v obsegu celic D2: i2)

• Omejitve. Imamo te omejitve:

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

  • Ne priPravite več drog, kot je v povpraševanju. To pomeni, da morajo biti vrednosti v celicah D2: i2 (funtov, proizvedene za vsako drogo) manjše ali enake povpraševanju po posameznih drogah (navedene v celicah D8: I8).

  • Ne moremo ustvariti negativne količine katerekoli droge.

Pokazal vam bom, kako vnesete ciljno celico, spreminjajoče se celice in omejitve v reševalca. Vse, kar morate storiti, je, da kliknete gumb» reši «, da poiščete izdelek, ki bi lahko pridobil dobiček.

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

Prikazal se bo pogovorno okno Parametri reševalca, kot je prikazano na sliki 27-2.

Kliknite polje Nastavi ciljno celico in nato izberite našo dobiček celic (celica D12). Kliknite polje s spreminjanjem celic in nato Pokaži na obseg D2: i2, ki vsebuje kilograme, izdelane iz posamezne droge. Pogovorno okno bi moralo zdaj iskati sliko 27-3.

Zdaj smo pripravljeni dodati omejitve modelu. Kliknite gumb Dodaj. Videli boste 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 slika 27-5.

Zdaj smo zagotovili, da pri Reševalcu poskušajo različne vrednosti za spreminjajoče se celice, le kombinacije, ki ustrezajo obema D14< = F14 (uporabljeno delo je manjše ali enako kot delo, ki je na voljo) in D15< = F15 (uporabljena surovina je manjša ali enaka razpoložljiva surovina) bo upoštevana. 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 ko reševalec poskusi različne kombinacije za spreminjajoče se vrednosti celic, bodo upoštevane le kombinacije, ki izpolnjujejo te parametre:

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

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

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

• G2< = G8 (količina, izdelana iz droge 4, je manjša ali enaka povpraševanju po drogi 4)

• H2< = H8 (količina, proizvedena za drogo 5, je manjša ali enaka povpraševanju po drogah 5)

• I2< = I8 (količina, proizvedena za drogo 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 vnesemo omejitev, ki jih spreminjanje celic ne sme biti negativno. Kliknite gumb» možnosti «v pogovornem oknu» Parametri reševalca «. Potrdite polje predpostavi linearni model in preVzemite neNegativno polje, kot je prikazano na sliki 27-8 na naslednji strani. Kliknite »V redu«.

Če želite preveriti, ali je polje» predpostavi neNegativno «zagotovljeno, da reševalec upošteva le kombinacije spreminjajočih se celic, v katerih vsaka spreminjajoča se celica predpostavlja nenegativno vrednost. PoTrdili smo polje predpostavi linearni model, ker je težava za mešanje izdelkov posebna vrsta težave reševalca, ki se imenuje linearni model. Model reševalca je v glavnem linearen pod naslednjimi pogoji:

• Ciljna celica je izračunana tako, da sešteje pogoje obrazca (spremenljiva celica) * (konstanta).

• Vsaka omejitev izpolnjuje zahteve» linearni model «. To pomeni, da je vsaka omejitev ovrednotena tako, da seštejete pogoje obrazca (spremenljiva celica) * (konstanta) in primerjate vsote s konstanto.

Zakaj je ta težava v Reševalcu linearna? Naša ciljna celica (dobiček) je izračunana kot

(Droga 1 dobiček na funt) * (droga 1 £ proizvedena) +
(droga 2 dobiček na funt) * (droge 2 funtov proizvedena) +...
(Droga 6 dobička na funt) * (proizvedena droga 6 funtov)

Ta izračun sledi vzorcu, v katerem je vrednost ciljne celice izpeljana tako, da sešteje pogoje obrazca (spremenljiva celica) * (konstanta).

Naša omejitev dela je ovrednotena s primerjavo vrednosti, ki izhaja iz (delo se uporablja na funt drog 1) * (droga 1 £ proizvedena) + (delo se uporablja na funt drog 2) * (drog 2 funtov proizvedena) +... (Delo ZDAEd na funt zdravila 6) * (droga 6 funtov proizvedena) na delo, ki je na voljo.

Zato je omejitev dela ovrednotena tako, da seštejete pogoje obrazca (spremenljiva celica) * (konstanta) in primerjate vsote s konstanto. Omejitev delovne sile in omejitev surovine ustrezata zahtevi linearnega modela.

Naše omejitve povpraševanja so v obliki

(Droga 1 proizvedena) < = (droga 1)
(droga 2 proizvedena) < = (zahteva za drogo 2)
§
(zdravilo 6 proizvedeno) < = (zahteva za drogo 6)

Vsako omejitev povpraševanja tudi izpolnjuje zahteve linearnega modela, ker je vsaka ovrednotena tako, da sešteje pogoje obrazca (spremenljiva celica) * (konstanta) in primerja vsote s konstanto.

Če je prikazano, da je naš model za mešanje izdelkov linearni model, zakaj bi nas to zanimalo?

• Če je model reševalca linearen in izberete predpostavi linearni model, je reševalec zagotovljen, da poišče optimalno rešitev za model reševalca. Če model reševalca ni linearen, lahko reševalec poišče optimalno rešitev ali pa ne.

• Če je model reševalca linearen in izberete predpostavi linearni model, Reševalec uporabi zelo učinkovit algoritem (metoda simplex), da poišče optimalno rešitev modela. Če je model reševalca linearen in ne izberemo linearnega modela, Reševalec uporablja zelo neučinkovit algoritem (metoda GRG2) in morda težave z iskanjem optimalne rešitve modela.

Ko kliknete v redu v pogovornem oknu Možnosti reševalca, se vrnemo v pogovorno okno glavni reševalec, ki je prikazano na sliki 27-7. Ko kliknete rešitev, Reševalec izračuna optimalno rešitev (če obstaja) za naš model za mešanje izdelkov. Kot sem navedla v poglavju 26, bi bila optimalna rešitev modela za mešanje izdelkov niz spreminjajočih se vrednosti celic (funtov, proizvedenih za vsako drogo), ki poveča dobiček nad naborom vseh izvedljivih rešitev. Znova je možna rešitev nabor spreminjajočih se vrednosti celic, ki izpolnjujejo vse omejitve. Spreminjajoče se vrednosti celic, prikazane na sliki 27-9, so izvedljive rešitve, 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 zaradi teh razlogov:

• Izdelujemo več drog 5, kot ga zahtevamo.

• Uporabljamo več dela, kot je na voljo.

• Uporabljamo več surovin od tistega, kar je na voljo.

Ko kliknete rešitev, Reševalec hitro poišče optimalno rešitev, prikazano na sliki 27-11. Če želite ohraniti optimalno vrednost rešitev na delovnem listu, morate izbrati možnost ohrani rešitev reševalca.

Naše podjetje za droge lahko poveča svoj mesečni dobiček na ravni $6.625,20, tako da proizvede 596,67 funtov drog 4, 1084 funtov drog 5, in nobena druga droga! Ne moremo ugotoviti, ali lahko dosežemo največji dobiček $6.625,20 na druge načine. Vse, kar smo lahko prepričani, je, da z našimi omejenimi viri in povpraševanjem ne morete narediti več kot $6.627,20 ta mesec.