sadržaj
Formulacija problema
Pretpostavimo da kompanija u kojoj radite ima tri skladišta, odakle roba ide u pet vaših prodavnica raštrkanih po Moskvi.
Svaka prodavnica je u mogućnosti da proda određenu količinu robe koja nam je poznata. Svako od skladišta ima ograničen kapacitet. Zadatak je racionalno izabrati iz kojeg skladišta u koje prodavnice isporučiti robu kako bi se ukupni troškovi transporta sveli na minimum.
Prije početka optimizacije bit će potrebno sastaviti jednostavnu tabelu na Excel listu – naš matematički model koji opisuje situaciju:
Podrazumijeva se da:
- Svjetložuta tabela (C4:G6) opisuje troškove dostave jednog artikla iz svakog skladišta do svake trgovine.
- Ljubičaste ćelije (C15:G14) opisuju količinu robe potrebnu za svaku prodavnicu za prodaju.
- Crvene ćelije (J10:J13) prikazuju kapacitet svakog skladišta – maksimalnu količinu robe koju skladište može držati.
- Žute (C13:G13) i plave (H10:H13) ćelije su zbroj redova i kolona za zelene ćelije, respektivno.
- Ukupni trošak dostave (J18) izračunava se kao zbir proizvoda broja robe i odgovarajućih troškova dostave – za izračun se ovdje koristi funkcija SUMPRODUCT (SUMPROIZVOD).
Dakle, naš zadatak se svodi na odabir optimalnih vrijednosti zelenih ćelija. I tako da ukupan iznos za liniju (plave ćelije) ne prelazi kapacitet skladišta (crvene ćelije), a da istovremeno svaka prodavnica dobije količinu robe koju treba da proda (iznos za svaku prodavnicu u žute ćelije treba da budu što bliže zahtevima – ljubičaste ćelije).
rastvor
U matematici su takvi problemi izbora optimalne raspodjele resursa formulirani i opisani dugo vremena. I, naravno, načini za njihovo rješavanje odavno su razvijeni ne tupim nabrajanjem (koje je jako dugo), već u vrlo malom broju iteracija. Excel pruža korisniku takvu funkcionalnost pomoću dodatka. Search Solutions (Rješač) sa kartice podaci (Datum):
Ako je na kartici podaci vaš Excel nema takvu komandu – u redu je – to znači da dodatak jednostavno još nije povezan. Da biste ga aktivirali otvorite fajl, a zatim izaberite parametri - Dodaci - Oko (Opcije — Dodaci — Idi na). U prozoru koji se otvori označite polje pored linije koja nam je potrebna Search Solutions (Rješač).
Pokrenimo dodatak:
U ovom prozoru morate podesiti sljedeće parametre:
- Optimizirajte ciljnu funkciju (Postavite tnovac ćelija) – ovdje je potrebno navesti konačni glavni cilj naše optimizacije, odnosno ružičastu kutiju sa ukupnim troškovima dostave (J18). Ciljna ćelija se može minimizirati (ako su troškovi, kao u našem slučaju), maksimizirati (ako je, na primjer, profit) ili pokušati je dovesti do zadane vrijednosti (na primjer, tačno se uklopiti u dodijeljeni budžet).
- Promjena varijabilnih ćelija (By promjena ćelije) – ovdje označavamo zelene ćelije (C10: G12), mijenjajući vrijednosti za koje želimo postići naš rezultat – minimalni trošak isporuke.
- U skladu sa ograničenjima (Naslov to u ograničenja) – lista ograničenja koja se moraju uzeti u obzir prilikom optimizacije. Da biste dodali ograničenja na listu, kliknite na dugme dodati (Dodati) i unesite uslov u prozor koji se pojavi. U našem slučaju, ovo će biti ograničenje potražnje:
i ograničenje maksimalnog obima skladišta:
Pored očiglednih ograničenja povezanih sa fizičkim faktorima (kapacitet skladišta i transportnih sredstava, budžetska i vremenska ograničenja, itd.), ponekad je potrebno dodati ograničenja „posebna za Excel“. Tako, na primjer, Excel može lako organizirati da “optimizirate” troškove dostave tako što ćete ponuditi transport robe iz trgovina natrag u skladište – troškovi će postati negativni, odnosno ostvarit ćemo profit! 🙂
Kako biste spriječili da se to dogodi, najbolje je ostaviti potvrdni okvir uključenim. Neka neograničene varijable nisu negativne ili čak ponekad eksplicitno registruju takve trenutke na listi ograničenja.
Nakon postavljanja svih potrebnih parametara, prozor bi trebao izgledati ovako:
U padajućoj listi Odaberi metodu rešavanja, potrebno je dodatno izabrati odgovarajuću matematičku metodu za rešavanje izbora od tri opcije:
- Simpleks metoda je jednostavna i brza metoda za rješavanje linearnih problema, odnosno zadataka kod kojih je izlaz linearno ovisan o ulazu.
- Opća metoda sniženog gradijenta (OGG) – za nelinearne probleme, gdje postoje složene nelinearne ovisnosti između ulaznih i izlaznih podataka (na primjer, ovisnost prodaje o troškovima oglašavanja).
- Evolucijska potraga za rješenjem – relativno nova metoda optimizacije zasnovana na principima biološke evolucije (zdravo Darwin). Ova metoda radi mnogo puta duže od prve dvije, ali može riješiti gotovo svaki problem (nelinearni, diskretni).
Naš zadatak je jasno linearan: isporučen 1 komad – potrošeno 40 rubalja, isporučeno 2 komada – potrošeno 80 rubalja. itd., tako da je simpleks metoda najbolji izbor.
Sada kada su podaci za proračun uneti, pritisnite dugme Pronađite rješenje (riješi)za početak optimizacije. U teškim slučajevima sa puno promjenjivih ćelija i ograničenja, pronalaženje rješenja može potrajati dugo (posebno uz evolucijsku metodu), ali naš zadatak za Excel neće predstavljati problem – za nekoliko trenutaka ćemo dobiti sljedeće rezultate :
Obratite pažnju na to kako je interesantno raspoređen obim ponude po prodavnicama, a da pritom ne prelazi kapacitet naših skladišta i zadovoljava sve zahteve za potreban broj robe za svaku prodavnicu.
Ako nam nađeno rješenje odgovara, možemo ga sačuvati, ili vratiti na izvorne vrijednosti i pokušati ponovo s drugim parametrima. Također možete sačuvati odabranu kombinaciju parametara kao scenario. Na zahtjev korisnika, Excel može izgraditi tri tipa Izvještaji o problemu koji se rješava na posebnim listovima: izvještaj o rezultatima, izvještaj o matematičkoj stabilnosti rješenja i izvještaj o granicama (ograničenjima) rješenja, međutim, u većini slučajeva oni su od interesa samo za stručnjake .
Međutim, postoje situacije u kojima Excel ne može pronaći odgovarajuće rješenje. Takav slučaj je moguće simulirati ako u našem primjeru navedemo zahtjeve skladišta u količini većoj od ukupnog kapaciteta skladišta. Zatim, prilikom izvođenja optimizacije, Excel će pokušati da se približi rješenju što je više moguće, a zatim će prikazati poruku da rješenje nije moguće pronaći. Ipak, čak iu ovom slučaju imamo mnogo korisnih informacija – posebno možemo uočiti „slabe karike“ naših poslovnih procesa i razumjeti područja za poboljšanje.
Razmatrani primjer je, naravno, relativno jednostavan, ali se lako prilagođava za rješavanje mnogo složenijih problema. Na primjer:
- Optimizacija raspodjele finansijskih sredstava prema stavci rashoda u poslovnom planu ili budžetu projekta. Ograničenja će u ovom slučaju biti iznos finansiranja i tajming projekta, a cilj optimizacije je maksimiziranje profita i minimiziranje troškova projekta.
- Optimizacija rasporeda zaposlenih kako bi se minimizirao platni fond preduzeća. Ograničenja će, u ovom slučaju, biti želje svakog zaposlenog prema rasporedu zapošljavanja i zahtjevima kadrovskog rasporeda.
- Optimizacija investicionih ulaganja – potreba za pravilnom raspodjelom sredstava između nekoliko banaka, vrijednosnih papira ili dionica preduzeća kako bi se, opet, maksimizirao profit ili (ako je važnije) minimizirali rizici.
U svakom slučaju, dodatak Search Solutions (rješavač) je vrlo moćan i lijep Excel alat i vrijedan vaše pažnje, jer može pomoći u mnogim teškim situacijama sa kojima se morate suočiti u modernom poslovanju.