Optimalizace počtu provozoven rychlého občerstvení nového podniku ve městech ČR (Monte Carlo)

Zadání: Jeden z faktorů hodnocení předmětu 4IT495 Simulace Systému je zpracování simulace a její dokumentace na těchto stránkách.

Název simulace: Optimalizace počtu provozoven rychleho občerstveni nového podniku v městech ČR
Autor: Nguyen Van Thanh
Typ modelu: Monte Carlo
Modelovací nástroj: MS Excel 2013

Definice problému

Jsme nový podnik rychlého občerstvení a chceme se prosadit v největších městech České republiky, konkr. Praha, Brno, Plzeň a Ostrava. Sledujeme jaký je stav a vývoj průmyslu rychlého občerstvení v ČR poslední rok, tj. 2017. K dispozici máme veřejné průzkumy a statistiky, které nám slouží jako ovlivňující faktory našeho zkoumání.Výsledky práce slouží jako predikce pro následující rok, tj. 2018 a tyto výsledky jsou interprotovány v měsíčním časovém úseku.

Práce je strukturována na 1 hlavní statistickou část 00 Info, ve které jsou získány veškeré fixní faktory ovlivňující naší práci. Dále nejdůležitější část architektury a funkce práce 03 Logika, ve které jsou zpracovány všechny vzorce. K přehlednosti jsou doplněny 4 další pomocné xls.listy, se kterými lze zkontrolovat správnost a přesnost jednotlivých náhodných veličin formou Monte Carlo.

Cíl simulace

Pomocí simulace Monte Carlo zkoumáme, jaký je počet optimálního rozpoložení provozen v těchto městech ideální, tak abychom docílili kontinuálního nejvyššího zisku. Dále máme k dispozici předem nastavený formulář, který může sloužit k manuálnímu zadávání počtu provozoven ve vybraných městech viz dále.

Metoda a vstupní data

Nejprve byla potřeba zjistit demografické údaje ve vybraných městech, dále z veřejných průzkůmu ohledně průmyslu rychlého občerstvení ve formě dotazníků, veřejných státních průzkumech a akademických prací. K tomu všemu se dále zjišťovaly dynamické složky, které byly zkoumány náklady na provoz rychlého občerstvení v náhodném rozdělení, rozsah jednotlivých složek byly analyzovány na veřejných portálech poskytující nabídky prací a nájmů nebytových prostor.

Statistické údaje

Obyvatelstvo

1. Praha - 1 294 513  ||  2. Brno - 379 527  ||  3. Plzeň - 170 936  ||  4. Ostrava - 290 450 

Návštěvnost

1. Alespoň jednou měsíčně navštíví fast food - 37%
2. Alespoň jednou týdně navštíví fast food - 8%
3. Průměrná útrata strávníka je - 188 Kč
4. Jídlo je snězeno na místě - 40% lidí
5. Časové rozdělení návštěvnosti
 - ráno - 5%  |  - odpoledne - 22%  |  - odpoledne - 41%  |  - večer/v noci - 32%

Provozovna

1. Průměrné místo na sezení - 39 míst
2. Průměrné náklady na energie - 25%
3. Počet zaměstnanců na jedné provozovně - 11 
  - 2x obsluha, 2x uklízečka, 1x vedoucí, 2x zástupce vedoucího, 4x kuchař
  - Na částečný úvazek pracuje - 1/6~1/4 zaměstnanců v provozovně (Náhodná složka - Randombetween)
4. Průměrná marže z tržeb - 19%
  - tzn. vypočítává se tržby očištěny od náklady na suroviny

Konkurence

1. Počítáme, s tím že máme 7 hlavní konkurentů v průmyslu, se kterou se bude vést boj o návštěvníky
2. Koeficient konkurenta je 1, tzn. že každý si je rovný (v našm případě, máme 1/8 návštěvnosti)

Výpočty dynamických složek jsou vypočteny na listech 05 MC Gener. nájmu a 06 MC Gener. mzdy

List 00 Info

Dynamické údaje

List 03 Logika

Praha

1. Průměrná mzda zaměstnanců v řetězcích rychlého občerstvení - 95~120 Kč/h (log-normálním rozdělení)
  1. Plný úvazek - 80*hodinová sazba
  2. Částečný úvazek - 160*hodinová sazba

2. Nájem nebytových prostor (restaurace) - 7k~350k kč/měsíc 
  1. Nízký extrém - 7k~30k kč/měsíc (náhodná složka - random)
  2. Vysoký extrém - 150k~350k kč/měsíc (náhodná složka - random)
  3. Standarní interval - 31k~149k kč/měsíc (normálové rozložení)
  - 70% procent, že se vybere standardní nájem a zbylých 30% je výběr extrému 50% 

3. Variabilní složka návštěvnosti (Exponenciální rozdělení)
  - tzn. je měsíci existuje kladná či záporná odchylka návštěvnosti
  - Výpočet je sestaven na základě matematcikého poměru mezi Prahou a městem s nejnižší počtem obyvatel (Plzeň), protože odchylka s rostoucí velikostí města roste, 
lambda u náhodného rozdělení je 4

Brno

1. Průměrná mzda zaměstnanců v řetězcích rychlého občerstvení - 80~115 Kč/h (log-normálním rozdělení)
  1. Plný úvazek - 80*hodinová sazba
  2. Částečný úvazek - 160*hodinová sazba

2. Nájem nebytových prostor (restaurace) - 7k~105k kč/měsíc 
  1. Nízký extrém - 7k~10k kč/měsíc (náhodná složka - random)
  2. Vysoký extrém - 60k~105k kč/měsíc (náhodná složka - random)
  3. Standarní interval - 11k~59k kč/měsíc (normálové rozložení)
  - 70% procent, že se vybere standardní nájem a zbylých 30% je výběr extrému 50% 

3. Variabilní složka návštěvnosti (Exponenciální rozdělení)
  - tzn. je měsíci existuje kladná či záporná odchylka návštěvnosti
  - Výpočet je sestaven na základě matematcikého poměru mezi Brnem a městem s nejnižší počtem obyvatel (Plzeň), protože odchylka s rostoucí velikostí města roste,
 lambda u náhodného rozdělení je 3

Plzeň

1. Průměrná mzda zaměstnanců v řetězcích rychlého občerstvení - 110~135 Kč/h (log-normálním rozdělení)
  1. Plný úvazek - 80*hodinová sazba
  2. Částečný úvazek - 160*hodinová sazba

2. Nájem nebytových prostor (restaurace) - 3k~70k kč/měsíc 
  1. Nízký extrém - 3k~13k kč/měsíc (náhodná složka - random)
  2. Vysoký extrém - 50k~70k kč/měsíc (náhodná složka - random)
  3. Standarní interval - 14k~49k kč/měsíc (normálové rozložení)
  - 70% procent, že se vybere standardní nájem a zbylých 30% je výběr extrému 50% 

3. Variabilní složka návštěvnosti (Exponenciální rozdělení)
  - tzn. je měsíci existuje kladná či záporná odchylka návštěvnosti
  - Výpočet je sestaven na základě matematcikého poměru mezi Plzní a městem s nejnižší počtem obyvatel (Plzeň), protože odchylka s rostoucí velikostí města roste, 
lambda u náhodného rozdělení je 1

Ostrava

1. Průměrná mzda zaměstnanců v řetězcích rychlého občerstvení - 80~110 Kč/h (log-normálním rozdělení)
  1. Plný úvazek - 80*hodinová sazba
  2. Částečný úvazek - 160*hodinová sazba

2. Nájem nebytových prostor (restaurace) - 6k~70k kč/měsíc 
  1. Nízký extrém - 6k~10k kč/měsíc (náhodná složka - random)
  2. Vysoký extrém - 50k~70k kč/měsíc (náhodná složka - random)
  3. Standarní interval - 11k~49k kč/měsíc (normálové rozložení)
  - 70% procent, že se vybere standardní nájem a zbylých 30% je výběr extrému 50% 

3. Variabilní složka návštěvnosti (Exponenciální rozdělení)
  - tzn. je měsíci existuje kladná či záporná odchylka návštěvnosti
  - Výpočet je sestaven na základě matematcikého poměru mezi Ostravou a městem s nejnižší počtem obyvatel (Plzeň), protože odchylka s rostoucí velikostí města roste, 
lambda u náhodného rozdělení je 2

Zdroje:

Model

List 03 Logika

Náklady

Máme k dispozici několik variant rozložení počtu provozoven do vybraných měst, pro tento příklad jsou zvoleny intervaly 1-5, 6-10, 11-15, 16-20. Hodnota každého intervalu se počítá náhodnou metodou random. Aktuální hodnotu v dané měření si vybere právě jedno náhodně generované číslo z jednoho ze 4 intervalů, každý má 25% šanci.

Výpočet interavalu: =IF(RAND()<0.5,IF(RAND()<0.5,interval_1,interval_2),(IF(RAND()<0.5,interval_3,interval_4)))

Generovaná hodnota se posléze vypočítá ve všech xls.listech práce a vygeneruje nám následující údaje:

1. Nakumulovanou hodnotu nájmu, např. vygeneruje se nám 11 provozoven pro dané období a pro každou provozovnu máme konkrétní náhodně generovaný nájem (neznamená, že se vygeneruje jeden fixní nájem pro všechny. (Výpočet je obsažený v listu 04 Kumulace nájmu a vyhledán pomocí funkce =LOOKUP)
   
   Výpočet celkových nákkladů na nájem=LOOKUP(pocet_provozoven_v_obdobi,'04 MC Kumulace nájmu'!najdi_cislo_provozovny_obsahu,'04 MC Kumulace nájmu'rozsah_hodnot_kumulovanych hodnot)*(1+procento_nakladu_na_energii)
   
2. U celkových mzdách nefunguje princip jako u nájmu, ale vybere se průměrná hodnota, která se vynásobí podle počtu zaměstnanců v provozovnách
3. Celkové náklady se vypočítají jako součet bodu 1. a 2.
4. Denní rozpoložení návštěvnosti:
   - zjišťuje se celkový počet míst v provozovnách ve městech
   - průměrné hodnoty v jednotlivých časových úsecích (měsíční a denní údaj), data jsou získána z listu 00 Info
   - je vypočtena obsazenost provozovny očistěna o návštěvníky, kteří si kupují jídlo s sebou, v případě obsazenosti vyšší než 100% (v excelu vyznačené červeně), se návštěvníci mimo interval počítají jako ztrátoví a jdou do celkových nákladů v listu 01 Souhrn

Simulace Monte Carlo - obecný model pozn. Generují se náhodné počty provozoven

Obecný model simulace

Souhrnný výsledek měření

List 01 Souhrn

V této sekci jsou zobrazeny nejdůležitejší údaje měření za měřený měsíc:
- Celková návštěvnost
- Z toho kolik bylo ztrátových kvůli nedostatku míst na sezení
- Doplňující info, kolik nebylo využito míst
- Celková tržba
- Celkový zisk očištěný o všechny náklady(tj. marže - podnikové náklady) K dispozici je také aktuální přehled probíhaného měření, ve kterém jsou údaje pro dané rozpoložení počtu provozoven v jednotlivých městech a jaké je je jejich čistý zisk + tyto údaje lze srovnat s aktuálním celkovým rizikem podniku a nebo aktuálním celkovým průměrem zisku po simulaci MC.

Závěr

Cílem měření bylo zjistit, jaká kombinace počtu provozoven v jednotlivých městech byla nejvíce zisková, viz v následující tabulce. (Odevzdané zadání jsem lehce zformuloval, neboť jsem nemohl vyhovující data pro zjištění poptávky/nabídky/konkurence bude fixní, proto jsem práci zpracoval, tak že bude pracovat zejména s náhodně generovanými hodnotami v nákladech podniku a návštěvnosti zákazníků.

Po simulaci pomocí Monte Carlo, byly zjištěny tyto údaje.

Výsledek měření

Počet provozoven	Praha	Brno	Plzeň	Ostrava	Celkový výsledek hospodaření
Maximalizace	1	19	9	12	4 745 570 Kč
Minimalizace	20	20	12	14	-2 303 151 Kč

Pro zajímavost sem byla vložena i u kombinace rozpoložení provozoven, kdy se o jedná o nejhorší možný výsledek hospodaření.

Interpretace výsledku Je zajímavé, že k docílení maximalního zisku je potřeba, aby v Praze byl, co nejmenší počet provozoven. Důvody mohou následující: Příliš velká fluktuace návštěvnosti ve městě, vysoká míra obsazenosti míst na sezení během dne a následek mnoho ztrátových zákazníků nebo vysoké nájmy a jejich kolísání za prostory.

Další rozšíření a možnosti Práci lze rozšířit z designové hlediska a vylepšit tak, aby bylo přehlednější a více intuitivní. Dále lze doplnit některá funkce a vzorce pro zakomponování dalších položek, která mohou ovlivnit výsledek měření.

Ke stažení

Soubor