Korištenje rješavača da biste odredili optimalan miks proizvoda

Kako utvrditi mjesečni mix proizvoda koji Maksimizira dobiti od klijenata?

Tvrtke često je potrebno da biste odredili količinu svaki proizvod, čime se dobiva mjesec. U najjednostavniji obrascu, problem kombinirati proizvoda uključuje upute za određivanje iznosa svaki proizvod koji treba proizvesti tijekom mjeseca Maksimiziranje dobiti. Mix proizvoda obično moraju biti sljedeća ograničenja:

• Mix proizvoda nije moguće koristiti od su dostupni dodatni resursi.

• Postoji ograničeni zahtjev za svaki proizvod. Ne možemo ne daju više proizvoda tijekom mjeseca od određuje zahtjev, jer je suvišno radni je utrošeno (na primjer, pokvarljive zloupotrebu).

Pogledajmo sada riješiti u sljedećem primjeru mix problema proizvoda. Rješenja tog problema možete pronaći u datoteci Prodmix.xlsx, prikazan na slici 27-1.

Recimo da radimo za zloupotrebu tvrtku koja daje šest različite proizvode na njihove biljke. Proizvodne svaki proizvod potreban je truda i sirovine. Retka 4 u slika 27-1 prikazuje sati truda potrebna za stvaranje znak za funtu svakog proizvoda, a redak 5 prikazuje funtama sirovine koja su potrebna za stvaranje znak za funtu svakog proizvoda. Ako, na primjer, proizvodnje znak za funtu proizvoda 1 zahtijeva šest sati truda i 3.2 funtama sirovine. Za svaki zloupotrebu cijenu po znak za funtu izražen je u redak 6, jedinični trošak po znak za funtu izražen je u redak 7 i dobiti udjela po znak za funtu izražen je u retku 9. Ako, na primjer, proizvod 2 prodaje za $11.00 po znak za funtu, uključuje jedinični trošak od $5.70 po znak za funtu i doprinosa $5.30 dobiti po znak za funtu. U mjesecu zahtjev za svaki zloupotrebu izražen je u retku 8. Na primjer, zahtjev za proizvod 3 je 1041 funtama. Ovog mjeseca 4500 sati truda i 1600 funtama sirovine dostupni su. Kako mogu ovu tvrtku Maksimiziranje njegov mjesečni dobiti?

Ako ne možemo znao ništa o dodatak Rješavatelj programa Excel, ne možemo bi izgradnje radnog lista da biste pratili dobiti i korištenju resursa pridružene mix proizvoda napada taj problem. Zatim koristit ćemo pogreške i probne verzije za razlikovanje mix proizvoda da biste optimizirali dobiti bez korištenja više truda ili sirovine nego što je dostupno i bez proizvodnje sve zloupotrebu iznad zahtjev. U ovom postupku samo fazi probnu verziju i pogreške koristimo alata za rješavanje. Zapravo, alata za rješavanje je programa optimizacije tražilica koja flawlessly izvodi pretraživanje probnu verziju i pogreške.

Ključ za rješavanje problema s mix proizvoda nalazi se izračunati učinkovito korištenje resursa i dobiti pridružene mix sve navedene proizvoda. Važno alat koji koristimo da bi ovaj izračuni je SUMPRODUCT (funkcija). Funkcije SUMPRODUCT Množi odgovarajuće vrijednosti u rasponima ćelija i vraća zbroj tih vrijednosti. Svaki raspon ćelija u SUMPRODUCT procjenu mora imati iste dimenzije koji podrazumijeva da koristite funkcije SUMPRODUCT s dva retka ili dva stupca, ali ne i s jedan stupac i jedan redak.

Kao primjer kako ćemo pomoću funkcije SUMPRODUCT u naš proizvod kombinirati primjer, Pokušajmo naš korištenje resursa za izračun. Korištenje naše truda izračunava

*(Drug 1 pounds produced) (rad po funta zloupotrebu 1) +
(rad po funta zloupotrebu 2) * (zloupotrebu 2 funtama proizvodi) +...
(Rad po funta zloupotrebu 6) * (zloupotrebu 6 funtama proizvodi)

Ne možemo nije moguće izračunati truda korištenje više zamoran način kao D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. Isto tako, korištenje sirovine nije moguće izračunati kao D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. Unos formule u radni list za proizvode šest je dugotrajan. Zamislite koliko će trajati ako kojim ste radili u tvrtki koje se proizvesti, na primjer, 50 proizvoda njihove biljke. Na koliko jednostavniji način za izračunavanje truda i korištenje sirovine je možete kopirati iz D14 D15 formula SUMPRODUCT($D$2:$I$2,D4:I4). Ova formula izračunava D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (koji je naš korištenje truda), ali je mnogo lakše unesite! Obratite pozornost na to da koristiti znak $ s rasponom D2:I2 tako da pri kopiranju formula mogu i dalje snimiti mix proizvoda iz retka 2. Formula u ćeliji D15 formula izračunava sirovine korištenje.

Na sličan način naš dobiti ovisi o

(Zloupotrebu 1 dobiti po funte) * (1 zloupotrebu funtama proizvodi) +
(2 zloupotrebu dobiti po funte) * (zloupotrebu 2 funtama proizvodi) +...
(6 zloupotrebu dobiti po funte) * (zloupotrebu 6 funtama proizvodi)

Dobiti jednostavno izračunati u ćeliji D12 s formulom SUMPRODUCT(D9:I9,$D$2:$I$2).

Sada možete utvrđujemo tri komponente naših proizvoda mix modela alata za rješavanje.

• Ciljna ćelija.Naš je cilj maksimiziranja zarade (izračunati u ćeliji D12).

• Ćelije koje se mijenjaju.Broj funtama proizvodi svakog proizvoda (navedene u rasponu ćelija D2:I2)

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

  • Nemojte koristiti više truda ili sirovine nego što je dostupno. To je vrijednosti u ćelijama D14:D15 (resursa koji se koriste) mora biti manja od ili jednaka vrijednosti u F14:F15 ćelije (dostupnim resursima).

  • Neće proizvesti od zloupotrebu nego što je u zahtjev. To je vrijednosti u ćelijama D2:I2 (funtama proizvodi od svakog zloupotrebu) mora biti manja od ili jednaka na zahtjev za svaki zloupotrebu (navedene u ćelijama D8:I8).

  • Ne možemo ne može proizvesti negativan iznos sve zloupotrebu.

Li vam pokazati kako unijeti ciljne ćelije promjenom ćelija i ograničenjima u alat za rješavanje. Sve što trebate napraviti pa je kliknite gumb riješi da biste pronašli proizvoda različiti dobiti maksimiziranja!

Da biste započeli, kliknite karticu podaci pa u grupi analiza kliknite alat za rješavanje.

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

Kliknite okvir Postavi ciljnu ćeliju, a zatim odaberite naš dobiti ćelije (ćelija D12). Kliknite okvir promjenom ćelija, a zatim pokažite na raspon D2:I2 koja sadrži funtama proizvodi od svakog zloupotrebu. Dijaloški okvir trebala izgledati slika 27-3.

Ne možemo sada ste spremni za dodavanje ograničenja modelu. 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 referenca ćelije, a zatim odaberite raspon D14:D15. Odaberite < = srednjem popisa. Kliknite okvir ograničenja, a zatim odaberite raspon ćelija F14:F15. Dijaloški okvir Dodavanje ograničenja trebala izgledati kao što je slika 27-5.

Ne možemo su sada ensured da da kada alata za rješavanje pokuša različite vrijednosti za promjenu ćelije, samo kombinacije koji zadovoljavaju oba D14< = F14 (truda koristi je manja od ili jednaka truda dostupna) i D15< = F15 (sirovine koristi manja od ili jednako će se smatrati sirovine dostupna). Kliknite Dodaj da biste unijeli ograničenja zahtjev. Ispunite dijaloški okvir Dodavanje ograničenja kao što je prikazano na slici 27-6.

Dodavanje tih ograničenja osigurava da kad alata za rješavanje pokuša raznih kombinacija za promjenom vrijednosti ćelija, samo kombinacije koji zadovoljavaju sljedećih parametara smatrat će se:

• D2< = D8 (iznos proizvodi zloupotrebu 1 manja od ili jednako zahtjev za zloupotrebu 1)

• E2< = E8 (količinu proizvodi zloupotrebu 2 manja od ili jednako zahtjev za zloupotrebu 2)

• F2< = F8 (iznos proizvodi zloupotrebu 3 stvoren je manja od ili jednaka na zahtjev za zloupotrebu 3)

• G2< = G8 (iznos proizvodi zloupotrebu 4 stvoren je manja od ili jednaka na zahtjev za zloupotrebu 4)

• H2< = H8 (iznos proizvodi od 5 zloupotrebu stvoren je manja od ili jednaka na zahtjev za zloupotrebu 5)

• I2< = I8 (iznos proizvodi zloupotrebu 6 stvoren je manja od ili jednaka na zahtjev za zloupotrebu 6)

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

Ne možemo unesite ograničenja koja promjenjivim ćelijama mora biti koji nije negativan u dijaloškom okviru Mogućnosti alata za rješavanje. Kliknite gumb mogućnosti u dijaloškom okviru parametri alata za rješavanje. Potvrdite okvire pretpostavlja linearni Model i pretpostavlja da korisnici koji nisu negativan, kao što je prikazano na slici 27-8 na sljedećoj stranici. Kliknite U redu.

Potvrdite okvir pretpostavlja da nije negativan osigurava alata za rješavanje obzir uzima samo kombinacije promjenjivim ćelijama u kojem svaku ćeliju promjenom pretpostavlja koji nije negativan vrijednost. Ne možemo potvrđen okvir pretpostavlja linearni Model jer je proizvod kombinirati problem posebna je vrsta alata za rješavanje problema s nazivom linearni model. Zapravo, modela rješavača su linearni u sljedećim okolnostima:

• Ciljna ćelija se izračunati zbrajanjem uvjeta u obrascu (Promjena cell)*(constant).

• Svako ograničenje zadovoljava "linearni model obavezne." To znači da svako ograničenje spada zbrajanjem uvjeta u obrascu (Promjena cell)*(constant) i Usporedba zbrojeve za konstante.

Zašto je alat za rješavanje problema linearni? Naš ciljne ćelije (dobit) izračunat će se kao

(Zloupotrebu 1 dobiti po funte) * (1 zloupotrebu funtama proizvodi) +
(2 zloupotrebu dobiti po funte) * (zloupotrebu 2 funtama proizvodi) +...
(6 zloupotrebu dobiti po funte) * (zloupotrebu 6 funtama proizvodi)

Ovaj izračuni slijedi uzorak vrijednosti ciljne ćelije u kojoj se dobiva zbrajanjem uvjete obrasca (Promjena cell)*(constant).

Naš ograničenja truda procjenjuje usporedbom vrijednost izvedene iz (rad po znak za funtu zloupotrebu 1) * (1 zloupotrebu funtama proizvodi) + (rad po znak za funtu zloupotrebu 2) *(Drug 2 pounds produced) +... (Labor namUre po znak za funtu zloupotrebu 6) * (zloupotrebu 6 funtama proizvodi) za dostupna truda.

Stoga procjenjuje ograničenje truda zbrajanjem uvjeta u obrascu (Promjena cell)*(constant) i Usporedba zbrojeve za konstante. Ograničenje truda i ograničenja sirovine ispunjavaju obavezne linearni model.

Naš ograničenja zahtjev se pojavljuju u obliku

_LT_ (zloupotrebu 1 proizvodi) = (zahtjev za zloupotrebu 1)
< (proizvodi zloupotrebu 2) = (zahtjev za zloupotrebu 2)
§
< (proizvodi zloupotrebu 6) = (zloupotrebu 6 zahtjev)

Svaki zahtjev ograničenja i zadovoljava obavezne linearni model jer se svaki procjenjuje zbrajanjem uvjeta u obrascu (Promjena cell)*(constant) i Usporedba zbrojeve za konstante.

Imate prikazano je linearni model na našem modelu mix proizvoda, zašto treba smo važnih?

• Ako je modela rješavača linearni, a ne možemo odaberite pretpostavlja linearni Model, alata za rješavanje uvijek je da biste pronašli optimalnih rješenja rješavača model. Ako je modela rješavača linearni, alata za rješavanje možda ili ne pronađete optimalnih rješenja.

• Ako model alata za rješavanje linearni, a ne možemo odaberite pretpostavlja linearni Model, alata za rješavanje koristi vrlo učinkovitog algoritam (jednostavni metoda) da biste pronašli u model optimalnih rješenja. Ako model alata za rješavanje linearni, a ne možemo odaberite pretpostavlja linearni Model, alata za rješavanje koristi vrlo Neučinkovit algoritam (GRG2 metoda), a možda imaju poteškoća pronalaženja optimalnih rješenja u model.

Nakon što kliknete u redu u dijaloškom okviru Mogućnosti alata za rješavanje, ne možemo vratite se u glavnom alata za rješavanje dijaloškom okviru prikazuju ranije u slici 27-7. Kada smo kliknite riješi, alata za rješavanje izračunava optimalnih rješenja (Ako postoji) za naše model mix proizvoda. Kao što je li naveden u 26 poglavlja, optimalnih rješenja mix model proizvoda bi skup promjena vrijednosti ćelija (funtama proizvodi od svakog zloupotrebu) koji Maksimizira dobiti skup svih izvedivo rješenja. Ponovno izvedivo rješenje je skup promjena vrijednosti ćelija koje zadovoljavaju sva ograničenja. Promjenom vrijednosti u ćelijama prikazan na slici 27-9 su izvedivo rješenje jer su sve razine proizvodnje koji nije negativan, razine proizvodne premašuju zahtjev i korištenje resursa nije veća od dostupnih resursa.

Promjena vrijednosti ćelija u slici 27 10 prikazano na sljedećoj stranici predstavljaju šifriraju rješenja iz sljedećih razloga:

• Ne možemo proizvesti od 5 zloupotrebu od zahtjev za njega.

• Koristimo više truda nego što je dostupno.

• Koristimo više sirovinom nego što je dostupno.

Nakon što kliknete riješi, alata za rješavanje brzo pronalazi optimalnih rješenja prikazan na slici 27-11. Morate odaberite Zadrži rješenje alata za rješavanje da biste sačuvali vrijednosti optimalnih rješenja na radnom listu.

Našu tvrtke zloupotrebu možete Maksimiziranje njegov mjesečni dobiti na razini $6,625.20 po proizvodnje 596.67 funtama zloupotrebu 4, 1084 funtama zloupotrebu 5 i nijedan drugi droga! Ne možemo ne može odrediti ako smo možete postići Maksimalna stabala $6,625.20 na druge načine. Sve možemo biti sigurni je li s našim ograničeni resurse i zahtjev ne postoji način da bi više od $6,627.20 ovog mjeseca.