Kraus Lucie, xkral52 - Rozvozové časy - Monte Carlo

Tato stránka je slouží jako Výzkumná zpráva simulace "Rozvozové časy" k semestrálnímu projektu pro předmět 4IT495 Simulace systémů (LS 2013/2014) na VŠE v Praze.

Zadání

• Název simulace: Rozvozové časy
• Předmět: 4IT495 Simulace systémů (LS 2013/2014)
• Autor: Lucie Kraus
• Typ modelu: Monte Carlo
• Provedeno v: MS Excel 2013

Definice problému

Jsme v situaci, kdy máme údaje z eshopu poskytující suroviny k vaření, který sám rozváží svým kurýrem po Praze objednávky. Zákazník si může vybrat během objednání rozvozový čas (interval 2 hodiny). Nabízené dny k analýze jsou pondělí až sobota (o neděli není z managementu eshopu zájem) a rozvozové časy jsou intervaly dvou hodin: 8-10, 10-12, 12-14, 14-16, 16-18, 18-20, 20-22.

Cílem situace je zobrazit, které rozvozové časy jsou neztrátové a které jsou a o kolik.

Použitá výchozí data, pravděpodobnosti a pravděpodobnostní rozdělení vycházejí z reálných dat eshopu a dále se s nimi v simulaci pracuje.

Metoda

Tato simulace byla ideální pro metodu Monte Carlo (práce s čísly, nutnost nasimulovat každý den a každý rozvozový čas desetitisíckrát a více). Vypracovaná v prostředí MS Excel 2013.

Model

Listy modelu:

• Start - první list modelu s editovatelnými parametry modelu
• Mezivýpočty - list s přehledem a výpočtem dalších parametrů nutný pro další postup (pro standardního uživatele nejsou určeny k editaci)
• Výpočet obj - vygenerování přibližného počtu objednávek pro každý den
• Výpočet den obj - vygenerování počtu objednávek pro každý rozvozový čas (seskupeno podle dní)
• Výpočet náročnosti - výpočet náročnosti každého rozvozového času
• Výpočet nákladů - celkový výpočet všech nákladů
• Výstup - přehled matematických výpočtů
• Výsledek - grafické a zjednodušené znázornění výsledků

Výchozí data

V modelu se pracuje s několika proměnnými, které jsou dány informacemi extrahovanými přímo z reality (data z eshopu).

Počet objednávek pro jeden rozvozový den

Níže zveřejněný graf zobrazuje dlouhodobý vývoj počtu objednávek pro každý rozvozový den. Z této reality lze vyčíst mnoho informací:

- v pondělí si velice málo lidí nechává přivést zboží (začátek týdne, stres, není čas vařit)

- vrchol objednacích termínů je středa a pátek

- obliba sobotního termínu před obědem a večeří

Graf ale není žádným pravděpodobnostním rozdělením (viz čtvrteční propad), tj tyto informace jsou poskytnuty jako základ pro výpočet střední hodnoty normálního rozdělení přímo pro každý den.

Vzdálenost ("Náročnost")

Vzdálenost je potřeba pro výpočet variabilních nákladů jako benzin a hodiny práce. Graf z reality ukazuje, že takový vývoj má pravděpodobností rozdělení Beta. V modelu lze náročnost (vzdálenost) objednávek upravovat, například kdyby se eshop rozšířil i mimo Prahu do okolních vesnic atd, případně naopak zúžil svůj prostor.

Poptávka po rozvozových časech

Graf zobrazuje současný vývoj poptávky po rozvozovém čase. Pracovní dny mají poptávku po rozvozových časech prakticky identickou, zatímco sobota má úplně jiné rozdělení (díky kterému nelze použít pravděpodobnostní rozdělení).

Editovatelné parametry

• Vzdálenost: Komplikovanost tras zadaná na stupnici 1 - 7 (jedna - velice jednoduchá, 7 - velice komplikovaná)
• Akce sleva na dopravu: Zadává se v % (akce 20% sleva = 80 Kč dopravné, 100% sleva = Kč dopravné, 0% sleva = 100 Kč dopravné)
• Průměrné náklady na 10 minut v autě (mezi 10 - 20 Kč)
• Náklady na 1h práce řidiče (0 - 200 Kč)
• Maximální nákladnost jedné objednávky: maximální přípustná nákladnost jedné objednávky po odečtení dopravného atd.

Jak model počítá

1. Nastaví se editovatelné parametry v Listu Start

2. Na základě ceny dopravy pro uživatele se vypočítají hodnoty pro normální rozdělení pro přibližný výpočet počtu objednávek za jeden daný den. Střední hodnota se vypočítá:

=ZAOKROUHLIT(((100-[cena_dopravy])/100*[parametr_násobku_střední_hodnoty]+[parametr_přičtení])*[výchozí_hodnota_při_ceně_100_za_dopravu];1)

Směrodatná odchylka:

=ZAOKROUHLIT(((100-[cena_dopravy])/100*[parametr_násobku_směrodatné_odchylky]+[parametr_přičtení])*[výchozí_odchylka_při_ceně_100_za_dopravu];1)

3. V list Výpočet obj se vypočte počet objednávek pro každý den 29999x.

=NORM.INV(NÁHČÍSLO();[střední_hodnota_z_kroku_2];[směrodatná_odchylka_z_kroku_2])

Ošetření pro extrémní hodnoty, které vygenerují číslo menší než 0:

=KDYŽ([výsledek_náhodného_rozdělení]<0;0;[výsledek_náhodného_rozdělení])

4. V listu Výpočet den obj dochází k rozdělení předběžného počtu objednávek za daný den do příslušných rozvozových časů

=ZAOKROUHLIT([předpokládaný_počet_objednávek_z_kroku_3]*([procento_pravděpodobnosti_daného_času]/100)*(1+(NÁHČÍSLO()*(3+3)-3)/10);0)

Část vzorce (1+(NÁHČÍSLO()*(3+3)-3)/10) generuje čísla jako 1,3 nebo -1,2, resp. hodnoty mezi -1,3 a 1,3. Tato část je tu kvůli náhodné odchylce od dané pravděpodobnosti rozvozového času (z listu Mezičas), aby výsledek nebyl přesně daný procentem a měl jistou odchylku.

5. Na základě počtu objednávek se vypočítají hodnoty pro beta rozdělení pro výpočet náročnosti rozvozu pro každý den.

Střední hodnota:

=[výchozí_střední_hodnota]+[hodnota_parametru_vzdálenost]

Směrodatná odchylka:

=KDYŽ([hodnota_parametru_vzdálenost];[výchozí_hodnota_odchylky]-2;[výchozí_hodnota_odchylky]-1)

6. Vypočte se náročnost objednávek pro každý rozvozový čas každého dne:

=ZAOKROUHLIT((BETA.INV(NÁHČÍSLO();[vypočtená_alfa];[vypočtená_beta];[min];[max])*100)/(((100-[cena_dopravy])/100)/[procentuální_snížení_díky_počtu_objednávek]+1);0)

Část vzorce (((100-[cena_dopravy])/100)/[procentuální_snížení_díky_počtu_objednávek]+1) ovlivňuje vygenerovanou beta náročnost. Dělí vygenerovaný výsledek číslem 1 až 1,33 (tj. snižuje výsledek až o 33%). Tento výpočet je zde kvůli faktu, že když je více objednávek (= cena za dopravu je nižší), cesty mezi objednávkami musí být logicky kratší.

7. Poté se v listu Výpočet nákladů spočítají náklady pro daný čas

=([vypočtená_náročnost_času_z_bodu_6]/10)*[průměrné_náklady_na_10_minut]*[počet_obj_daného_času]+([hodinové_náklady_na_řidiče]*2)

8. V listu Výstup se už provádí pouze finální výpočty nákladovosti.

Nejdříve se spočítají příjmy z objednávek, které si můžeme odečíst z nákladů

=KDYŽ([průměrný_počet_objednávek_daného_času]=0;0;[průměrný_počet_objednávek_daného_času]*[cena_za_dopravu])

A poté se vypočtou zbylé náklady

=ZAOKROUHLIT(KDYŽ([průměrný_počet_objednávek_daného_času]=0;0;(([průměrné_náklady_na_objednávky]-[příjmy_z_ceny_dopravy])-([průměrný_počet_objednávek_daného_času]*[maximální_přípustné_náklady_na_obj]));0)

9. Finální pohled na výsledky barvami ovlivněný podmíněným formátováním.

Pokud je výsledná hodnota nula nebo záporná, stane se z ní kladná (je to zisk) a naopak.

=KDYŽ(Výstup![průměrný_počet_objednávek_daného_času]=0;"no obj";Výstup![výsledné_náklady_času]*(-1))

Výsledky

Výstupem je přehledná tabulka zobrazujicí rozvozové časy (zelené pozadí buňky), které nejsou prodělečné.

S výsledky simulace jde dále pracovat, avšak už záleží na v budoucnu stanoveném business modelu eshopu. Způsoby dalších interpretací výsledků jsou vlastně tři:

- eshop bude jezdit pouze v časech, které jsou ve výsledné tabulce zelené (tj. v nich nemá ztrátu)

- eshop sečte zisk a rozhodne se v případě "přebytečného" zisku dotovat další rozvozový čas, ten nejméně ztrátový

- eshop sečte zisk a rozhodne se v případě "přebytečného" zisku dotovat další rozvozový čas, avšak takový, který by se mu hodil pro zjednodušení administrativy. Tj v případě výsledku níže zobrazených:

Tabulka zobrazuje zeleně neprodělečné časy. Pokud bychom chtěli přijmout jeden ztrátový čas o hodnotě 50 (číslo určené vedením eshopu, ne modelem), máme na výběr celkem 6 možností (pondělí 10-12 a 18-20; úterý 18-20; pátek 8-10 a 20-22; sobota 18-20). Pokud se rozhodujeme pro zjednodušení administrativy, je nejchytřejší přijmnout ještě čas v pondělí 10-12, protože všechny ostatní dny v týdnu nejsou ztrátové a administrace eshopu bude jednodušší.

Zde se rozhodneme například rozdělit hodnotu 60.

Pokud se rozhodneme o co nejvíc termínů, výsledek bude takový (můžeme si vybrat ze soboty 10-12 nebo čtvrtka 18-20):

Pokud se ale rozhodneme o výsledek vhodnější pro administrativu (a výklad zákazníkům v objednacím systému), zvolíme takovýto výběr i když zbývá hodnota 5.