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 ekosolventních 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). U každé simulace bylo vygenerováno 10.000 hodnot vyjadřující celkový zisk za budoucí tři roky za předpokladu nastavení dané kombinace spodního a horního limitu (testované kombinace limitů jsou uvedené v kapitole Výstupy modelu).

Kód

Zdrojové soubory simulace:

• File:Sim ink.xlsx (hlavní simulace)
• File:Sim ink output.xlsx (výstupní vygenerované hodnoty)