Uporaba reševalca za določanje optimalne kombinacije izdelkov

Opomba: Najnovejšo vsebino pomoči v vašem jeziku vam želimo zagotoviti v najkrajšem možnem času. Ta stran je bila prevedena z avtomatizacijo in lahko vsebuje slovnične napake ali nepravilnosti. Naš namen je, da bi bila vsebina za vas uporabna. Ali nam lahko na dnu te strani sporočite, ali so bile informacije za vas uporabne? Tukaj je angleški članek za preprosto referenco.

V tem članku je opisano uporaba reševalca, Microsoft Excelov program za dodajanje, ki ga lahko uporabite za analizo» kaj-če «, da določite optimalno mešanico 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.

Book image

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.

Opomba:  Kot je razloženo v poglavju 26, je» uvod v optimizacijo s programom Excel reševalec «nameščen tako, da kliknete gumb» Microsoft Office «, nato Excelove možnosti, nato pa dodatki. Na seznamu upravljanje kliknite Excelovi dodatki, potrdite polje reševalec in nato kliknite v redu.

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

Book image

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.

Book image

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

Book image

Č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.

Book image

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.

Book image

Č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.

Book image

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«.

Book image

Č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.

Book image

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.

Book image

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.

Book image

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.

PredPostavimo, da mora biti zahteva za vsak izdelek izpolnjena. (Glejte v datoteki Prodmix. xlsx ni izvedljivega delovnega lista rešitev.) Nato moramo spremeniti omejitve povpraševanja iz D2: I2< = D8: I8 to D2: I2> = D8: I8. To naredite tako, da odprete reševalca, izberite D2: I2< = D8: I8 CONSTRAINT in nato kliknite Spremeni. Prikaže se pogovorno okno spremeni omejitev, ki je prikazano na sliki 27-12.

Book image

Izberite > = in nato kliknite v redu. Zdaj smo zagotovili, da bo Reševalec premislil o spreminjanju le vrednosti celic, ki ustrezajo vsem zahtevam. Ko kliknete rešitev, 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, vendar pa z omejenimi viri ne moremo izpolnjevati povpraševanja za vse izdelke. Reševalec nam preprosto sporoča, da če želimo zadostiti povpraševanju po posameznem izdelku, moramo dodati več dela, več surovin ali več obojega.

Poglejmo, kaj se zgodi, če dovolimo neomejene zahteve za posamezen izdelek in dovolimo, da se izvedejo negativne količine posamezne droge. (To težavo reševalca lahko vidite na nastavljenih vrednostih, ne konvergirajo delovnega lista v datoteki Prodmix. xlsx.) Če želite poiskati optimalno rešitev za to stanje, odprite reševalca, kliknite gumb možnosti in počistite polje predpostavi, da ni negativno. 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šitev, vrne reševalec sporočilo» nastavitev vrednosti celic ne konvergira. « To sporočilo pomeni, da če je ciljna celica maksimirana (kot v našem primeru), obstajajo izvedljive rešitve s samovoljnimi velikimi vrednostmi ciljnih celic. (Če je ciljna celica minimizirana, sporočilo» nastavitev vrednosti celic se ne konvergira «pomeni, da obstajajo izvedljive rešitve s samovoljno majhnimi vrednostmi ciljnih celic.) V našem primeru lahko s tem, ko dopuščamo negativno proizvodnjo zdravila, v bistvu ustvarjamo vire, ki jih je mogoče uporabiti za ustvarjanje samovoljnih velikih količin drugih drog. Zaradi naše neomejene zahteve nam to omogoča, da izkoristimo neomejene dobičke. V pravem položaju ne moremo ustvariti neskončnega zneska denarja. Skratka, če vidite, da» nastavitev vrednosti ne konvergira «, ima vaš model napako.

  1. Recimo, da lahko naše podjetje za droge kupi do 500 ur dela pri $1, ki je več na uro kot tekoči stroški dela. Kako lahko povečamo dobiček?

  2. V proizvodni napravi čipa štirje tehniki (A, B, C in D) ustvarijo tri izdelke (izdelke 1, 2 in 3). Ta mesec lahko izdelovalec čipa proda 80 enot izdelkov 1, 50 enot izdelka 2 in največ 50 enot izdelka 3. Tehnik A lahko naredi le izdelke 1 in 3. Tehnik B lahko naredi le izdelke 1 in 2. Tehnik C lahko naredi le izdelek 3. Tehnik D lahko naredi le izdelek 2. Za vsako proizvedeno enoto izdelki prispevajo naslednji dobiček: izdelek 1; $6; Izdelek 2; $7; in izdelek 3, $10. Čas (v urah) vsak tehnik mora proizvesti izdelek na naslednji način:

    Izdelek

    Tehnik A

    Tehnik B

    Tehnik C

    Tehnik D

    1

    2

    2,5

    Ne morete narediti

    Ne morete narediti

    2

    Ne morete narediti

    3

    Ne morete narediti

    3,5

    3

    3

    Ne morete narediti

    4

    Ne morete narediti

  3. Vsak tehnik lahko deluje do 120 ur na mesec. Kako lahko izdelovalec čipa poveča mesečni dobiček? PredPostavimo, da je mogoče proizvesti delno število enot.

  4. V računalnikovem proizvodnem obratu so na miški, tipkovnicah in igralnih palicah za video igre. Dobiček na enoto, poraba na enoto, mesečna zahteva in strojna raba na enoto so navedeni v tej tabeli:

    Miši

    Tipkovnice

    Igralne palice

    Dobiček/enota

    $8

    $11

    $9

    Uporaba delovne sile/enote

    .2 uri

    .3 ure

    .24 Hour

    Čas stroja/enota

    .04 Hour

    .055 Hour

    .04 Hour

    Mesečno povpraševanje

    15.000

    27.000

    11.000

  5. Vsak mesec je na voljo skupno število delovnih ur v 13.000 in 3000 ur časa stroja. Kako lahko proizvajalec poveča svoj mesečni prispevek dobička iz obrata?

  6. RazRešite naš primer zdravila ob predpostavki, da morajo biti izpolnjene minimalne zahteve za 200 enot za vsako zdravilo.

  7. Jason naredi diamantne zapestnice, ogrlice in uhane. Želi delati največ 160 ur na mesec. Ima 800 unč diamantov. Dobiček, čas dela in unčo diamantov, potrebnih za proizvodnjo posameznega izdelka, so navedeni spodaj. Kako lahko Jason poveča dobiček, če je povpraševanje po posameznem izdelku neomejeno?

    Izdelek

    Dobiček enote

    Delovne ure na enoto

    Unče diamantov na enoto

    Zapestnico

    300 EUR

    .35

    1,2

    Ogrlico

    200 €

    .15

    .75

    Uhani

    100 €

    .05

    0,5

Razširite poznavanje Officea
Oglejte si izobraževanje
Prvi dobite nove funkcije
Pridružite se programu Office Insider

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije.

Zahvaljujemo se vam za povratne informacije. Videti je, da bi vam prišla prav pomoč enega od naših Officeovih agentov za podporo.

×