Formulacija problema
Pretpostavimo da tvrtka u kojoj radite ima tri skladišta, odakle roba odlazi u pet vaših trgovina raštrkanih diljem Moskve.
Svaka trgovina je u mogućnosti prodati određenu količinu nama poznate robe. Svako od skladišta ima ograničeni kapacitet. Zadatak je racionalno odabrati iz kojeg skladišta u koje trgovine robu isporučiti kako bi se ukupni troškovi transporta sveli na minimum.
Prije početka optimizacije bit će potrebno sastaviti jednostavnu tablicu na Excel listu – naš matematički model koji opisuje situaciju:
Podrazumijeva se da:
- Svijetložuta tablica (C4:G6) opisuje trošak otpreme jednog artikla iz svakog skladišta u svaku trgovinu.
- Ljubičaste ćelije (C15:G14) opisuju količinu robe potrebnu za prodaju svake trgovine.
- Crvene ćelije (J10:J13) prikazuju kapacitet svakog skladišta – maksimalnu količinu robe koju skladište može primiti.
- Žute (C13:G13) i plave (H10:H13) ćelije zbrojevi su redova i stupaca za zelene ćelije.
- Ukupni trošak dostave (J18) izračunava se kao zbroj proizvoda broja robe i njihovih odgovarajućih troškova dostave – za izračun se ovdje koristi funkcija SUMPRODUCT (SUMPROIZVOD).
Stoga se naš zadatak svodi na odabir optimalnih vrijednosti zelenih stanica. I to tako da ukupan iznos za liniju (plave ćelije) ne premaši kapacitet skladišta (crvene ćelije), a istovremeno svaka trgovina dobije količinu robe koju treba prodati (količina za svaku trgovinu u žute ćelije trebaju biti što bliže zahtjevima – ljubičaste ćelije).
Riješenje
U matematici su takvi problemi izbora optimalne raspodjele resursa već dugo formulirani i opisani. I, naravno, načini za njihovo rješavanje odavno su razvijeni ne tupim nabrajanjem (koje je jako dugo), već u vrlo malom broju ponavljanja. Excel korisniku pruža takvu funkcionalnost pomoću dodatka. Rješenja za pretraživanje (Rješavač) s kartice Datum (Datum):
Ako je na kartici Datum vaš Excel nema takvu naredbu – u redu je – to znači da dodatak jednostavno još nije spojen. Za aktiviranje otvorite file, A zatim odaberite Parametri - Dodatke - O nama (Opcije — Dodaci — Idi na). U prozoru koji se otvori označite okvir pored linije koja nam je potrebna Rješenja za pretraživanje (Rješavač).
Pokrenimo dodatak:
U ovom prozoru morate postaviti sljedeće parametre:
- Optimizirajte ciljnu funkciju (Postavite tnovac stanica) – ovdje je potrebno naznačiti konačni glavni cilj naše optimizacije, tj. ružičastu kutiju s ukupnim troškom dostave (J18). Ciljnu ćeliju je moguće minimizirati (ako se radi o troškovima, kao u našem slučaju), maksimizirati (ako je npr. dobit) ili je pokušati dovesti na zadanu vrijednost (npr. točno uklopiti u dodijeljeni proračun).
- Promjena varijabilnih ćelija (By promjena Stanice) – ovdje označavamo zelene ćelije (C10: G12), čijim variranjem vrijednosti želimo postići naš rezultat – minimalni trošak dostave.
- U skladu s ograničenjima (Predmet do o Ograničenja) – popis ograničenja koja se moraju uzeti u obzir pri optimizaciji. Za dodavanje ograničenja na popis kliknite gumb dodati (Dodati) i unesite uvjet u prozor koji se pojavi. U našem slučaju, ovo će biti ograničenje potražnje:
i ograničenje najvećeg volumena skladišta:
Osim očitih ograničenja povezanih s fizičkim čimbenicima (kapacitet skladišta i prijevoznih sredstava, proračunska i vremenska ograničenja itd.), ponekad je potrebno dodati ograničenja “posebna za Excel”. Tako vam npr. Excel lako može organizirati da “optimizirate” trošak dostave tako što ćete ponuditi transport robe iz dućana natrag u skladište – troškovi će postati negativni, tj. mi ćemo profitirati! 🙂
Kako biste spriječili da se to dogodi, najbolje je ostaviti potvrdni okvir uključen. Učinite neograničen broj varijabli nenegativnim ili čak ponekad eksplicitno registrirati takve trenutke na popisu ograničenja.
Nakon postavljanja svih potrebnih parametara, prozor bi trebao izgledati ovako:
Na padajućem popisu Odaberite metodu rješavanja dodatno trebate odabrati odgovarajuću matematičku metodu za rješavanje izbora od tri opcije:
- Simpleks metoda je jednostavna i brza metoda za rješavanje linearnih problema, tj. problema kod kojih je izlaz linearno ovisan o ulazu.
- Opća metoda smanjenog 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 koja se temelji na principima biološke evolucije (pozdrav Darwine). Ova metoda radi mnogo puta duže od prve dvije, ali može riješiti gotovo svaki problem (nelinearan, diskretan).
Naš je zadatak jasno linearan: isporučeno 1 komad – potrošeno 40 rubalja, isporučeno 2 komada – potrošeno 80 rubalja. itd., pa je simpleks metoda najbolji izbor.
Sada kada su podaci za izračun uneseni, pritisnite tipku Pronađite rješenje (Riješiti)za početak optimizacije. U teškim slučajevima s puno mijenjanja ćelija i ograničenja, pronalaženje rješenja može potrajati dugo (pogotovo kod evolucijske metode), ali naš zadatak za Excel neće biti problem – za nekoliko trenutaka dobit ćemo sljedeće rezultate :
Obratite pažnju na to koliko su količine ponude bile zanimljivo raspoređene po trgovinama, a da pritom ne premašimo kapacitet naših skladišta i zadovoljimo sve zahtjeve za potrebnim brojem robe za svaku trgovinu.
Ako nam pronađeno rješenje odgovara, možemo ga spremiti ili se vratiti na izvorne vrijednosti i pokušati ponovno s drugim parametrima. Također možete spremiti odabranu kombinaciju parametara kao scenario. Na zahtjev korisnika, Excel može izgraditi tri vrste Izvješća o problemu koji se rješava na posebnim listovima: izvješće o rezultatima, izvješće o matematičkoj stabilnosti rješenja i izvješće 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 moguće je simulirati ako u našem primjeru navedemo potrebe trgovina u iznosu većem od ukupnog kapaciteta skladišta. Zatim, prilikom izvođenja optimizacije, Excel će se pokušati što više približiti rješenju, a zatim prikazati poruku da se rješenje ne može pronaći. Unatoč tome, čak iu ovom slučaju imamo puno korisnih informacija – posebice možemo vidjeti „slabe karike“ naših poslovnih procesa i razumjeti područja za poboljšanje.
Razmatrani primjer je, naravno, relativno jednostavan, ali se lako mjeri za rješavanje mnogo složenijih problema. Na primjer:
- Optimizacija raspodjele financijskih sredstava po stavci rashoda u poslovnom planu ili proračunu projekta. Ograničenja će, u ovom slučaju, biti iznos financiranja i vrijeme projekta, a cilj optimizacije je maksimiziranje dobiti i minimiziranje troškova projekta.
- Optimizacija rasporeda zaposlenika kako bi se minimizirao fond plaća poduzeća. Ograničenja će u ovom slučaju biti želje svakog zaposlenika prema rasporedu zapošljavanja i zahtjevima rasporeda osoblja.
- Optimizacija investicijskih ulaganja – potreba za pravilnom raspodjelom sredstava između nekoliko banaka, vrijednosnih papira ili dionica poduzeća kako bi se, opet, maksimizirali profiti ili (ako je još važnije) minimizirali rizici.
U svakom slučaju dodatak Rješenja za pretraživanje (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 s kojima se morate suočiti u modernom poslovanju.