Toimitusoptimointi

Ongelman muotoilu

Oletetaan, että yrityksellä, jossa työskentelet, on kolme varastoa, joista tavarat menevät viiteen myymäläänne hajallaan Moskovassa.

Jokainen myymälä pystyy myymään tietyn määrän meille tunnettuja tavaroita. Jokaisen varaston kapasiteetti on rajoitettu. Tehtävänä on rationaalisesti valita, mistä varastosta mihin liikkeisiin tavarat toimitetaan, jotta kokonaiskuljetuskustannukset saadaan minimoitua.

Ennen optimoinnin aloittamista on tarpeen koota Excel-arkille yksinkertainen taulukko – tilannetta kuvaava matemaattinen mallimme:

Ymmärretään, että:

• Vaaleankeltainen taulukko (C4:G6) kuvaa yhden tuotteen toimituskulut kustakin varastosta kuhunkin kauppaan.
• Purppurat solut (C15:G14) kuvaavat kunkin myymälän myytäväksi tarvittavan tavaramäärän.
• Punaiset solut (J10:J13) näyttävät kunkin varaston kapasiteetin – tavaran enimmäismäärän, jonka varastoon mahtuu.
• Keltaiset (C13:G13) ja siniset (H10:H13) solut ovat vihreiden solujen rivi- ja sarakesummat.
• Kokonaistoimituskulut (J18) lasketaan tavaroiden määrän ja niitä vastaavien toimituskulujen tuotteiden summana – laskennassa käytetään funktiota tässä SUMPRODUCT (SUMPRODUCT).
  

Siten tehtävämme rajoittuu vihreiden solujen optimaalisten arvojen valintaan. Ja jotta rivin kokonaismäärä (siniset solut) ei ylitä varaston kapasiteettia (punasolut), ja samalla jokainen myymälä saa sen myytäväksi tarvittavan määrän tavaraa (määrä jokaiselle myymälälle keltaisten solujen tulee olla mahdollisimman lähellä vaatimuksia – violetit solut).

Ratkaisu

Matematiikassa tällaisia ​​resurssien optimaalisen jakautumisen valinnan ongelmia on muotoiltu ja kuvattu pitkään. Ja tietysti tapoja niiden ratkaisemiseksi on jo pitkään kehitetty ei tylppä luettelemalla (joka on hyvin pitkä), vaan hyvin pienessä määrässä iteraatioita. Excel tarjoaa käyttäjälle tällaisen toiminnon apuohjelman avulla. Hakuratkaisut (Ratkaisija) välilehdeltä Päiväys (Päivämäärä):

Jos välilehdellä Päiväys Excelissäsi ei ole tällaista komentoa – se on ok – se tarkoittaa, että apuohjelmaa ei yksinkertaisesti ole vielä yhdistetty. Aktivoi se avaamalla fileeValitse parametrit - Lisäosat - Meistä (Asetukset — Lisäosat — Siirry). Valitse avautuvassa ikkunassa tarvitsemamme rivin vieressä oleva valintaruutu Hakuratkaisut (Ratkaisija).

Suoritetaan lisäosa:

Tässä ikkunassa sinun on asetettava seuraavat parametrit:

• Optimoi kohdetoiminto (Aseta traha solu) – Tässä on ilmoitettava optimointimme lopullinen päätavoite eli vaaleanpunainen laatikko kokonaistoimituskuluineen (J18). Kohdesolu voidaan minimoida (jos se on kustannuksia, kuten meidän tapauksessamme), maksimoida (jos se on esimerkiksi voitto) tai yrittää saada se tiettyyn arvoon (esimerkiksi mahtumaan tarkasti varattuun budjettiin).
• Muuttuvien solujen vaihtaminen (By muuttuviin solut) – Tässä ilmoitamme vihreät solut (C10: G12), joiden arvoja vaihtelemalla haluamme saavuttaa tuloksemme – toimituskulut minimiin.
• Rajoitusten mukainen (Aihe että Ishayoiden opettaman Rajoitukset) – luettelo rajoituksista, jotka on otettava huomioon optimoinnissa. Lisää rajoituksia luetteloon napsauttamalla painiketta Lisää (Lisätä) ja kirjoita ehto näkyviin tulevaan ikkunaan. Meidän tapauksessamme tämä on kysynnän rajoitus:

   

  ja varastojen enimmäismäärän rajoitus:

Fyysisiin tekijöihin liittyvien ilmeisten rajoitusten (varastojen ja kuljetusvälineiden kapasiteetti, budjetti- ja aikarajoitukset jne.) lisäksi on joskus tarpeen lisätä rajoituksia "erityisesti Excelille". Joten esimerkiksi Excel pystyy helposti "optimoimaan" toimituskulut tarjoamalla tavaran kuljetuksen myymälöistä takaisin varastoon – kustannukset tulevat negatiivisiksi, eli saamme voittoa! 🙂

Jotta näin ei tapahdu, on parasta jättää valintaruutu käyttöön. Tee rajattomista muuttujista ei-negatiivisia tai jopa joskus nimenomaisesti rekisteröidä tällaiset hetket rajoitusluetteloon.

Kun kaikki tarvittavat parametrit on asetettu, ikkunan pitäisi näyttää tältä:

Avattavasta Valitse ratkaisumenetelmä -luettelosta sinun on lisäksi valittava sopiva matemaattinen menetelmä kolmen vaihtoehdon ratkaisemiseksi:

• Yksinkertainen menetelmä on yksinkertainen ja nopea menetelmä lineaaristen ongelmien ratkaisemiseen, eli tehtäviin, joissa lähtö on lineaarisesti riippuvainen tulosta.
• Yleinen alennettu gradienttimenetelmä (OGG) – epälineaarisiin ongelmiin, joissa syöte- ja tulostietojen välillä on monimutkaisia ​​epälineaarisia riippuvuuksia (esimerkiksi myynnin riippuvuus mainoskuluista).
• Evoluutiota etsimään ratkaisua – suhteellisen uusi optimointimenetelmä, joka perustuu biologisen evoluution periaatteisiin (hei Darwin). Tämä menetelmä toimii monta kertaa pidempään kuin kaksi ensimmäistä, mutta voi ratkaista melkein minkä tahansa ongelman (epälineaarinen, diskreetti).
  

Tehtävämme on selkeästi lineaarinen: toimitettu 1 kpl - käytetty 40 ruplaa, toimitettu 2 kpl - käytetty 80 ruplaa. jne., joten simpleksimenetelmä on paras valinta.

Nyt kun laskennan tiedot on syötetty, paina -painiketta Löytää ratkaisu (Ratkaista)aloittaaksesi optimoinnin. Vaikeissa tapauksissa, joissa on paljon vaihtuvia soluja ja rajoituksia, ratkaisun löytäminen voi viedä kauan (etenkin evolutionaarisella menetelmällä), mutta tehtävämme Excelille ei ole ongelma – parin hetken kuluttua saamme seuraavat tulokset :

Kiinnitä huomiota siihen, kuinka mielenkiintoisesti toimitusmäärät jakautuivat myymälöiden kesken, ylittämättä varastojemme kapasiteettia ja tyydyttämällä kaikki pyynnöt tarvittavasta tavaramäärästä jokaiseen myymälään.

Jos löydetty ratkaisu sopii meille, voimme tallentaa sen tai palata alkuperäisiin arvoihin ja yrittää uudelleen muilla parametreilla. Voit myös tallentaa valitun parametriyhdistelmän nimellä skenaario. Käyttäjän pyynnöstä Excel voi rakentaa kolme tyyppiä Raportit ratkaistavasta ongelmasta erillisillä arkeilla: raportti tuloksista, raportti ratkaisun matemaattisesta stabiilisuudesta ja raportti ratkaisun rajoista (rajoituksista), mutta useimmissa tapauksissa ne kiinnostavat vain asiantuntijoita .

On kuitenkin tilanteita, joissa Excel ei löydä sopivaa ratkaisua. Tällainen tapaus on mahdollista simuloida, jos osoitamme esimerkissämme myymälöiden vaatimukset varastojen kokonaiskapasiteettia suuremmassa määrässä. Sitten optimointia tehdessään Excel yrittää päästä mahdollisimman lähelle ratkaisua ja näyttää sitten viestin, että ratkaisua ei löydy. Siitä huolimatta meillä on tässäkin tapauksessa paljon hyödyllistä tietoa – erityisesti voimme nähdä liiketoimintaprosessiemme "heikot lenkit" ja ymmärtää parannettavat alueet.

Tarkasteltu esimerkki on tietysti suhteellisen yksinkertainen, mutta skaalautuu helposti ratkaisemaan paljon monimutkaisempia ongelmia. Esimerkiksi:

• Rahoitusresurssien jaon optimointi hankkeen liiketoimintasuunnitelman tai budjetin menoerän mukaan. Rajoitukset ovat tässä tapauksessa rahoituksen määrä ja projektin ajoitus, ja optimoinnin tavoitteena on maksimoida tuotot ja minimoida projektin kustannukset.
• Työntekijöiden aikataulujen optimointi yrityksen palkkarahaston minimoimiseksi. Rajoitukset ovat tässä tapauksessa jokaisen työntekijän toiveita työaikataulun ja henkilöstötaulukon vaatimusten mukaisesti.
• Investointien optimointi – tarve jakaa varoja oikein useiden pankkien välillä, arvopapereita tai yritysten osakkeita jälleen kerran voittojen maksimoimiseksi tai (jos tärkeämpää) riskien minimoimiseksi.

Joka tapauksessa lisäosa Hakuratkaisut (Ratkaisija) on erittäin tehokas ja kaunis Excel-työkalu ja huomiosi arvoinen, sillä se voi auttaa monissa vaikeissa tilanteissa, joita joudut kohtaamaan nykyaikaisessa liiketoiminnassa.