Садржај
Формулисање проблема
Претпоставимо да компанија у којој радите има три складишта, одакле роба иде у пет ваших продавница разбацаних по Москви.
Свака продавница је у могућности да прода одређену количину робе која нам је позната. Свако од складишта има ограничен капацитет. Задатак је да се рационално изабере из ког складишта у које продавнице ће испоручити робу како би се укупни трошкови транспорта свели на минимум.
Пре него што започнете оптимизацију, биће потребно саставити једноставну табелу на Екцел листу – наш математички модел који описује ситуацију:
Подразумева се да:
- Светло жута табела (Ц4:Г6) описује трошкове испоруке једног артикла из сваког складишта до сваке продавнице.
- Љубичасте ћелије (Ц15:Г14) описују количину робе која је потребна за продају сваке продавнице.
- Црвене ћелије (Ј10:Ј13) приказују капацитет сваког складишта – максималну количину робе коју складиште може држати.
- Жуте (Ц13:Г13) и плаве (Х10:Х13) ћелије су збир редова и колона за зелене ћелије, респективно.
- Укупни трошкови испоруке (Ј18) се израчунавају као збир производа броја робе и њихових одговарајућих трошкова слања – за обрачун се овде користи функција СУМПРОДУЦТ (СУМПРОИЗВОД).
Дакле, наш задатак се своди на избор оптималних вредности зелених ћелија. И тако да укупан износ за линију (плаве ћелије) не прелази капацитет складишта (црвене ћелије), а да истовремено свака продавница добије количину робе коју треба да прода (износ за сваку продавницу у жуте ћелије треба да буду што ближе захтевима – љубичасте ћелије).
Решење
У математици су такви проблеми избора оптималне расподеле ресурса формулисани и описани дуго времена. И, наравно, начини за њихово решавање одавно су развијени не тупим набрајањем (које је веома дугачко), већ у веома малом броју итерација. Екцел пружа кориснику такву функционалност помоћу додатка. Сеарцх Солутионс (Решавач) са картице Датум (Датум):
Ако на картици Датум ваш Екцел нема такву команду – у реду је – то значи да додатак једноставно још није повезан. Да бисте га активирали, отворите га Фајл, А затим изаберите parametri - Адд-онс - O nama (Опције — Додаци — Иди на). У прозору који се отвори означите поље поред линије која нам је потребна Сеарцх Солутионс (Решавач).
Покренимо додатак:
У овом прозору морате да подесите следеће параметре:
- Оптимизујте циљну функцију (Постави тновац ћелија) – овде је потребно навести коначни главни циљ наше оптимизације, односно ружичасту кутију са укупним трошковима испоруке (Ј18). Циљна ћелија се може минимизирати (ако су трошкови, као у нашем случају), максимизирати (ако је, на пример, профит) или покушати да је доведе до задате вредности (на пример, тачно се уклопи у додељени буџет).
- Промена променљивих ћелија (By промена ћелије) – овде означавамо зелене ћелије (Ц10: Г12), мењајући вредности за које желимо да постигнемо наш резултат – минимални трошак испоруке.
- У складу са ограничењима (Наслов упита до la Ограничења) – списак ограничења која се морају узети у обзир приликом оптимизације. Да бисте додали ограничења на листу, кликните на дугме додати (Додати) и унесите услов у прозор који се појави. У нашем случају, ово ће бити ограничење потражње:
и ограничење максималног обима складишта:
Поред очигледних ограничења повезаних са физичким факторима (капацитет складишта и превозних средстава, буџетска и временска ограничења, итд.), понекад је потребно додати ограничења „посебна за Екцел“. Тако, на пример, Екцел може лако да организује да „оптимизујете“ трошкове испоруке тако што ћете понудити транспорт робе из продавница назад у складиште – трошкови ће постати негативни, односно остварићемо профит! 🙂
Да бисте спречили да се то догоди, најбоље је да оставите поље за потврду укључено. Нека неограничене варијабле нису негативне или чак понекад експлицитно региструју такве моменте у листи ограничења.
Након подешавања свих потребних параметара, прозор би требао изгледати овако:
У падајућој листи Изаберите метод решавања, потребно је да изаберете и одговарајући математички метод за решавање избора од три опције:
- Симплекс метода је једноставан и брз метод за решавање линеарних задатака, односно задатака где је излаз линеарно зависан од улаза.
- Општи метод сниженог градијента (ОГГ) – за нелинеарне проблеме, где постоје сложене нелинеарне зависности између улазних и излазних података (на пример, зависност продаје од трошкова оглашавања).
- Еволуциона потрага за решењем – релативно нова метода оптимизације заснована на принципима биолошке еволуције (здраво Дарвине). Ова метода ради много пута дуже од прва два, али може да реши скоро сваки проблем (нелинеарни, дискретни).
Наш задатак је јасно линеаран: испоручен 1 комад – потрошено 40 рубаља, испоручено 2 комада – потрошено 80 рубаља. итд., па је симплекс метода најбољи избор.
Сада када су подаци за прорачун унети, притисните дугме Пронађу решење (реши)да бисте започели оптимизацију. У тешким случајевима са много променљивих ћелија и ограничења, проналажење решења може потрајати дуго (посебно уз еволуциону методу), али наш задатак за Екцел неће представљати проблем – за неколико тренутака добићемо следеће резултате :
Обратите пажњу на то како је интересантно распоређен обим понуде по продавницама, а да притом не прелази капацитет наших магацина и задовољава све захтеве за потребан број робе за сваку продавницу.
Ако нам пронађено решење одговара, можемо га сачувати, или вратити на првобитне вредности и покушати поново са другим параметрима. Такође можете да сачувате изабрану комбинацију параметара као сценарио. На захтев корисника, Екцел може да изгради три типа Извештаји о проблему који се решава на посебним листовима: извештај о резултатима, извештај о математичкој стабилности решења и извештај о границама (ограничењима) решења, међутим, у већини случајева они су од интереса само за специјалисте .
Међутим, постоје ситуације у којима Екцел не може да пронађе одговарајуће решење. Такав случај је могуће симулирати ако у нашем примеру укажемо на потребе продавница у количини већој од укупног капацитета складишта. Затим, приликом извођења оптимизације, Екцел ће покушати да се приближи решењу што је више могуће, а затим ће приказати поруку да решење није могуће пронаћи. Ипак, чак иу овом случају имамо много корисних информација – посебно можемо видети „слабе карике“ наших пословних процеса и разумети области за побољшање.
Разматрани пример је, наравно, релативно једноставан, али се лако прилагођава за решавање много сложенијих проблема. На пример:
- Оптимизација расподеле финансијских средстава по ставци расхода у пословном плану или буџету пројекта. Ограничења ће, у овом случају, бити износ финансирања и тајминг пројекта, а циљ оптимизације је максимизирање профита и минимизирање трошкова пројекта.
- Оптимизација распореда запослених како би се минимизирао фонд зарада предузећа. Ограничења ће, у овом случају, бити жеље сваког запосленог према распореду запошљавања и захтевима кадровске табеле.
- Оптимизација инвестиционих улагања – потреба за правилном расподелом средстава између неколико банака, хартија од вредности или акција предузећа како би се, опет, максимизирао профит или (ако је важније) минимизирали ризици.
У сваком случају, додатак Сеарцх Солутионс (решавач) је веома моћан и леп Екцел алат и вредан ваше пажње, јер може помоћи у многим тешким ситуацијама са којима се морате суочити у савременом пословању.