Optimalizace skladových zásob inkoustů

Název: Optimalizace skladových zásob inkoustů
Autor: Bc. Iveta Kleníková, Klei00 (talk)
Nástroj: Microsoft Excel 365
Metoda: Monte Carlo

Definice problému

Ilustrační obrázek inkoustových lahví CMYK

Společnost B je českým dodavatelem UV inkoustů a dalšího spotřebního materiálu do velkoformátových tiskáren. Tyto produkty firma každý měsíc naskladňuje, aby dokázala rychle reagovat na poptávku zákazníků, a ti nemuseli čekat, než zboží dorazí ze zahraničí, kde se vyrábí. Každý měsíc je tedy třeba kontrolovat stav skladových zásob inkoustů a v případě nízkého stavu inkousty ze zahraničí objednat. Systém skladového hospodářtsví umožňuje ke každému produktu nastavit spodní a horní limit zásob. V případě, že je na začátku měsíce stav nižší než spodní limit, objedná se právě tolik kusů daného produktu, aby se dosáhlo horního limitu.

Inkousty se prodávají v jednolitrových lahvích ve čtyřech barvách (CMYK). Každý inkoust má z výroby roční expiraci. Ovšem na sklad se inkousty dostávají ve valné většině případů už pár měsíců staré. Ideální je prodat inkoust maximálně 6 měsíců starý, jelikož starší inkousty už zákazníci nechtějí kupovat z důvodu, že by je nestihli do data expirace vypotřebovat. Inkousty staré 7 měsíců se ještě dají prodat, ovšem pouze s určitou slevou. Starší inkousty se již neprodají a je potřeba je za určitý poplatek ekologicky zlikvidovat. Inkousty se ze skladu prodávají metodou FIFO (časovou jednotkou je měsíc).

Cílem simulace je nalézt optimální limity skladových zásob tak, aby firma zbytečně nenaskladňovala velké množství inkoustů, které se nakonec neprodají a budou muset být zlikvidovány. Zároveň je vhodné, aby poptávka byla ideálně plně uspokojena. Jako podklad simulace budou použita reálná interní data o prodejích, nákupních cenách a expiracích naskladněných inkoustů dané společnosti.

Metoda

Jelikož cílem této simulace je provést kalkulaci skladových limitů na základě pravděpodobné poptávky po inkoustech (dle historických dat), je zde použita metoda Monte Carlo. Tato metoda je vhodná pro výpočet nejvíce pravděpodobných náhodných hodnot dané veličiny za pomocí generování velkého počtu pseudonáhodných čísel. Metoda je prováděna v nástroji Microsoft Excel 365.

Popis modelu

Tento model představuje skladové hospodářství UV inkoustů společnosti B, která je českým dodavatelem spotřebního materiálu pro signmaking. Model řeší pouze čtyři produkty, kterými jsou základní barvy UV inkoustů CMYK (Cyan, Magenta, Yellow a Black).

Na začátku každého měsíce se kontroluje, zda aktuální stav zásob daného inkoustu neklesl pod spodní limit. Pokud ano, objedná se právě tolik balení inkoustů (po 10 ks), aby zásoby dosáhly horního limitu. Za jednu objednávku jedné barvy inkoustů se platí jednorázová doprava ve výši 1.500 Kč. Zboží obvykle dorazí do třech týdnů od jeho objednání, proto se v tomto modelu pro zjednodušení počítá s dostupností objednaných inkoustů až v následujícím měsíci. V době naskladnění inkoustů bývá jejich stáří 2-3 měsíce, přičemž inkousty staré do 6 měsíců včetně lze ještě prodat za jejich plnou prodejní cenu. Inkousty staré 7 měsíců lze prodat pouze se slevou 15 %, jelikož tyto inkousty už zákazníci většinou nestíhají do konce jejich expirace celé spotřebovat. Starší inkousty už nejsou prodejné (z již zmíněného důvodu blížící se expirace a rizika jejich sražení) a je třeba je ekologicky zlikvidovat za poplatek 9,20 Kč za litr. Inkousty jsou ze skladu prodávány metodou FIFO (časovou jednotkou je měsíc). Dále jsou v modelu započítány měsíční fixní náklady ve výši 40.000 Kč (mzda skladníka, nájem prostor, energie), které jsou rovnoměrně rozděleny mezi jednotlivé inkousty (každé barvě inkoustu tedy náleží fixní náklady ve výši 10.000 Kč).

Výstupem modelu jsou spodní a horní limity jednotlivých inkoustů, které jsou kalkulovány na základě zadaných vstupních parametrů a určených pravidel modelu.

Vstupní parametry

Model řeší následující čtyři UV inkousty, u kterých jsou dané nákupní a prodejní ceny (dle interních dat společnosti):

Kód inkoustu	Objem	Barva	Nákupní cena	Prodejní cena
SUV-10CY	1 litr	Cyan	756,50 Kč	1644,00 Kč
SUV-10MG	1 litr	Magenta	756,00 Kč	1619,00 Kč
SUV-10YE	1 litr	Yellow	756,50 Kč	1614,00 Kč
SUV-10BK	1 litr	Black	754,50 Kč	1659,00 Kč

Ná základě interních dat o prodejích těchto inkoustů v minulých 5 letech bylo určeno pravděpodobnostní rozdělení poptávky po inkoustech v jednotlivých měsících. Poptávky mají normální rozdělení a následující střední hodnoty a směrodatné odchylky:

Měsíc	SUV-10CY		SUV-10MG		SUV-10YE		SUV-10BK
	Průměr	Sm.odchylka	Průměr	Sm.odchylka	Průměr	Sm.odchylka	Průměr	Sm.odchylka
1	13,6	7,6	27,6	11,2	27,4	12,3	17,8	9,2
2	12,6	0,8	30,2	4,1	32,8	5,2	15,6	3,1
3	18,6	7,9	39,2	14,4	33,4	14,2	21,4	9,1
4	16,0	9,5	34,8	18,1	37,0	17,5	17,4	10,1
5	16,4	1,6	32,6	5,6	36,8	9,7	16,8	6,2
6	13,0	4,9	29,8	9,1	28,2	8,6	18,0	6,7
7	18,6	4,5	29,2	5,2	30,4	2,4	22,8	4,6
8	21,6	2,7	37,6	6,2	42,4	4,8	25,8	2,1
9	18,2	5,9	32,8	12,2	33,6	15,7	19,8	9,6
10	21,2	6,0	35,6	4,5	39,2	6,9	22,0	6,2
11	15,0	3,9	34,2	5,6	40,4	11,7	19,2	5,0
12	15,2	2,8	33,2	16,0	28,4	6,5	18,2	5,1

Ostatními vstupními variabilními parametry modelu jsou:

Parametr	Hodnota
Fixní měsíční náklady	40.000 Kč
Cena za likvidaci	9,20 Kč/litr
Ks v balení	10
Cena za dopravu	1.500 Kč
Sleva	15 %

Co se expirace (stáří v době naskladnění) jednotlivých inkoustů týká, jedná se o Poissonovo rozdělení se střední hodnotou 2,49 měsíce (nejčastěji se příjímá na sklad inkoust 2 či 3 měsíce starý). Rozdělení pro jednotlivé barvy inkoustů znázorňuje graf níže.

Vzhledem k tomu, že Microsoft Excel neposkytuje žádnou inverzní funkci pro Poissonovo rozdělení, jsou náhodná stáří inkoustů generována pomocí tabulky vytvořené na základě interních dat o expiracích inkoustů. Tato tabulka obsahuje kumulativní pravděpodobnosti stáří jednotlivých inkoustů. Místo neexistující funkce POISSON.INV() je tedy zde použita funkce POZVYHLEDAT(NÁHČÍSLO();sloupec tabulky dané barvy;1)+1. Jednička je na konci přičtena z důvodu, že je potřeba převést index řádku (který funkce POZVYHLEDAT() vrací) na stáří inkoustu, které začíná na 2 měsících (tj. první řádek tabulky představuje pravděpodobnost stáří 2 měsíce, druhý řádek stáří 3 měsíce atd.).

Graf hustoty pravděpodobnosti stáří inkoustů v době naskladnění

Kumulativní pravděpodobnost stáří inkoustů

Výstupy modelu

Výstupy tohoto modelu jsou spodní a horní limity jednotlivých barev inkoustů. Na základě průměrné měsíční poptávky po inkoustech jsou zvoleny následující rozsahy spodních a horních limitů, které budou testovány:

Kód inkoustu	Průměrná měsíční poptávka	Rozsah spodního limitu	Rozsah horního limitu
SUV-10CY	16,7 ks	10 - 60 ks	60 - 110 ks
SUV-10MG	33,1 ks	60 - 110 ks	120 - 170 ks
SUV-10YE	34,2 ks	60 - 110 ks	120 - 170 ks
SUV-10BK	19,6 ks	10 - 60 ks	60 - 110 ks

U spodního i horního limitu je vždy testováno 6 hodnot (s krokem 10), z čehož vyplývá, že u každé barvy inkoustu je dohromady testováno 36 kombinací spodního a horního limitu.

Výsledky

Vzhledem k tomu, že poptávky po jednotlivých barvách inkoustu jsou na sobě nezávislé, a tedy i limity se určují u každé barvy zvlášť, byla provedena simulace každé barvy odděleně. Všechny čtyři simulace simulují skladové hospodářství a s tím související příjmy a výdaje v následujících třech letech (2022-2024). Každá simulace zahrnovala 10.000 iterací, aby výsledek byl co nejpřesnější. Jako veličina určující optimální nastavení dané kombinace spodního a horního limitu (testované kombinace limitů jsou uvedené v kapitole Výstupy modelu) byl zvolen kumulativní čistý zisk za celé simulované období.

Na základě zprůměrování dílčích výsledků jednotlivých iterací byly vytvořeny tabulky nejpravděpodobnějších zisků za 3 roky pro dané kombinace limitů (viz níže). Nejvyšší zisky jsou znázorněny zeleně, nejnižší červeně.

Zisk při daných kombinacích limitů inkoustu Cyan

Limity inkoustu Cyan

Bylo zjištěno, že pro inkoust barvy Cyan je nejoptimálnější spodní limit 40 ks a horní limit 70 ks. Při těchto limitech by čistý zisk za 3 roky dosahoval s 90% pravděpodobností 92.207,6 Kč až 207.144,2 Kč. Průměrný čistý zisk za 3 roky by byl 154.189,2 Kč.

Zisk při daných kombinacích limitů inkoustu Magenta

Limity inkoustu Magenta

Co se inkoustu barvy Magenta týká, bylo zjištěno, že nejoptimálnější spodní limit je 80 ks a nejoptimálnější horní limit 160 ks. Při těchto limitech by čistý zisk za 3 roky dosahoval s 90% pravděpodobností 561.106,3 Kč až 787.074,4 Kč. Průměrný čistý zisk za 3 roky by byl 680.111,1 Kč.

Zisk při daných kombinacích limitů inkoustu Yellow

Limity inkoustu Yellow

U inkoustu barvy Yellow bylo zjištěno, že nejoptimálnější spodní limit je 80 ks a nejoptimálnější horní limit 140 ks. Při těchto limitech by čistý zisk za 3 roky dosahoval s 90% pravděpodobností 584.201,8 Kč až 784.254,9 Kč. Průměrný čistý zisk za 3 roky by byl 689.745,2 Kč.

Zisk při daných kombinacích limitů inkoustu Black

Limity inkoustu Black

Posledním simulovaným inkoustem byl inkoust barvy Black. U tohoto inkoustu bylo zjištěno, že nejoptimálnější spodní limit je 40 ks a nejoptimálnější horní limit 80 ks. Při těchto limitech by čistý zisk za 3 roky dosahoval s 90% pravděpodobností 177.647,2 Kč až 319.692 Kč. Průměrný čistý zisk za 3 roky by byl 255.024,2 Kč.

Zhodnocení

Jak lze vidět, optimální limity u inkoustů barev Magenta a Yellow jsou přibližně dvojnásobné v porovnání s optimálními limity u inkoustů barev Cyan a Black. Je to způsobeno tím, že poptávka po inkoustech barev Magenta a Yellow je téměř dvojnásobná (jak lze vidět v tabulce prodejů v kapitole Vstupní parametry). Při tisku barevných motivů se totiž nejvíce barev spektra míchá právě z těchto dvou základních barev, a tak zákazníkům nejčastěji dochází.

Také si lze všimnout, že optimální spodní limity vychází přibližně 2,5 krát vyšší než je průměrná měsíční poptávka. Tím vzniká snaha zabránit situaci, kdy by na skladě bylo méně zásob, než na dva měsíce dopředu. Mít zásoby pouze na měsíc dopředu je velmi málo, jelikož poptávka může mít výkyvy a snadno by se tak mohlo stát, že by měsíční zásoby ve výši průměrné měsíční poptávky neuspokojily měsíční poptávku. Naopak vyšší spodní limity by způsobily to, že by se zboží nakupovalo v moment, kdy je na skladě ještě dostatek zásob. Naskladněným inkoustům by tak zbytečně ubíhala lhůta trvanlivosti, inkousty by se nestíhaly prodat a zbytečně by se likvidovaly.

Co se horních limitů týká, tak ty představují téměř pětinásobek průměrné měsíční poptávky. Tím je docíleno toho, že se zásoby doplní přesně do takové výše, aby při průměrné měsíční poptávce a průměrném stáří naskladněných inkoustů ve výši 2 měsíců bylo zboží prodáno během oněch 5 měsíců, kdy se ještě inkousty dají prodat za plnou prodejní cenu. Vyšší horní limity by mohly způsobit to, že by se takové množství inkoustů nestihlo prodat a muselo by být zlikvidováno. Nižší horní limity by naopak mohly způsobit příliš časté objednávání zboží a častější placení vysoké ceny za dopravu zboží ze zahraničí.

Závěr

Cílem simulace bylo nalézt optimální limity skladových zásob tak, aby firma zbytečně nenaskladňovala velké množství inkoustů, které se nakonec neprodají, a aby byla zároveň uspokojena poptávka. Cíl se podařilo naplnit - byly zjištěny optimální limity všech čtyř barev inkoustů a pravděpodobný zisk při nastavení těchto limitů.

V porovnání s aktuálně nastavenými limity skladových zásob společnosti B jsou zjištěné optimální limity mírně vyšší. Navíc také firma nerozlišuje jednotlivé barvy inkoustů a má nastavené všechny limity stejné, a to 30 ks (spodní limit) a 90 ks (horní limit). Při nastavení těchto hodnot do vytvořeného modelu vychází, že v současnosti má firma v průměru o 10 % (Cyan, Black), resp. o 30 % (Magenta, Yellow) nižší zisky, než by mohla mít při nastavení optimálních limitů zjištěných na základě této simulace. Tento pokles zisku je způsoben tím, že firma občas nemá dostatek inkoustů na skladě, aby dokázala plně uspokojit měsíční poptávku. Přichází tak zbytečně o prodeje.

Kvůli určitému zjednodušení modelu jsou procenta snížení zisků trochu zkreslená (minimálně oněch 30 % určitě). Ve skutečnosti objednané inkousty přijdou na firmu do 2-3 týdnů, ne až za celý měsíc. To zajišťuje dřívější dostupnost inkoustů, a tudíž i lepší uspokojení poptávky. Za těchto okolností mohou být limity nižší, jelikož se na inkousty nečeká tak dlouho. Nicméně i tak firmě doporučuji navýšit spodní a horní limity minimálně u barev Magenta a Yellow, které mají vyšší poptávku, a mělo by se jich na skladu držet větší množství.

Kód

Zdrojové soubory simulace:

• sim_ink.xlsx (hlavní simulace)
• sim_ink_output.xlsx (výstupní vygenerované hodnoty)