Safety and Cycle Stock Calculation.ipynb


{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First part of script consists of raw data processing. Scroll down till text in **bold**."
   ]
  },
    {
   "cell_type": "code",
   "execution_count": 311,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import glob, os\n",
    "from scipy.stats import norm "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 312,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.width', 150)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 313,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = r'C:\\Users\\Folder'\n",
    "all_files = glob.glob(path + \"/*.txt\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 314,
   "metadata": {},
   "outputs": [],
   "source": [
    "col_specification = [(0,2),(2,15),(15,28),(28,30),\n",
    "                     (30,34),(34,36),(36,38),(38,42),(42,52),(52,62)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 315,
   "metadata": {},
   "outputs": [],
   "source": [
    "li = []\n",
    "\n",
    "for filename in all_files:\n",
    "    df = pd.read_fwf(filename, header=None,colspecs=col_specification,float_format='%.f')\n",
    "    li.append(df)\n",
    "\n",
    "df = pd.concat(li, axis=0, ignore_index=True)\n",
    "\n",
    "df = df.dropna() # Script drops N/A values. They may appear if file has blank lines.\n",
    "\n",
    "df.columns = ['Movement_Code','GLN_Code','EAN_Code','Site_Item_Type',\n",
    "                  'Case_Count','Movement_Day','Movement_Month','Movement_Year',\n",
    "                    'Quantity','Base_Unit_Code']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 316,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[['GLN_Code','EAN_Code','Base_Unit_Code','Case_Count']] = \\\n",
    "df[['GLN_Code','EAN_Code','Base_Unit_Code','Case_Count']].astype(str).replace('\\.0', '', regex=True)\n",
    "\n",
    "df['Quantity'] = df['Quantity'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 317,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Movement_Day'] = df['Movement_Day'].astype(str).replace('\\.0', '', regex=True)\n",
    "df['Movement_Day'] = df['Movement_Day'].str.zfill(2)\n",
    "df['Movement_Month'] = df['Movement_Month'].astype(str).replace('\\.0', '', regex=True)\n",
    "df['Movement_Month'] = df['Movement_Month'].str.zfill(2)\n",
    "df['Movement_Year'] = df['Movement_Year'].apply(str).replace('\\.0', '', regex=True)\n",
    "df['Movement_Date'] = df[\"Movement_Day\"] + \"-\" + df[\"Movement_Month\"] + \"-\" + df[\"Movement_Year\"]\n",
    "df['Movement_Date'] = df['Movement_Date'].apply(str).replace('\\.0', '', regex=True)\n",
    "df['Movement_Date'] = pd.to_datetime(df.Movement_Date,format = '%d-%m-%Y', dayfirst=True)\n",
    "df = df.drop(['Movement_Day','Movement_Month','Movement_Year'], axis=1) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 318,
   "metadata": {},
   "outputs": [],
   "source": [
    "if 5 in df['Movement_Code']:\n",
    "    df['Movement_Code'] = df['Movement_Code'].replace([1,4,3,5],\n",
    "                                                      ['Stock','Transit','Shipped','OOS'])\n",
    "else: \n",
    "    df['Movement_Code'] = df['Movement_Code'].replace([1,4,3],\n",
    "                                                      ['Stock','Transit','Shipped'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 319,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Site_Item_Type'] = df['Site_Item_Type'].replace([0,1],\n",
    "                                                    ['Standard','Promo'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 320,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[['Movement_Code','GLN_Code','EAN_Code','Site_Item_Type',\n",
    "                  'Case_Count','Movement_Date','Quantity','Base_Unit_Code']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 321,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfPivot = pd.pivot_table(df,aggfunc='sum',values='Quantity',\n",
    "                         index=['Movement_Date','EAN_Code','Base_Unit_Code'],\n",
    "                         columns='Movement_Code').reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 322,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfPivot = dfPivot.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 323,
   "metadata": {},
   "outputs": [],
   "source": [
    "Movement_Codes_Order = ['Movement_Week','Movement_Date','EAN_Code','Base_Unit_Code',\n",
    "                        'Stock','Transit','Shipped','OOS','Demand']\n",
    "\n",
    "dfPivot = dfPivot.reindex(Movement_Codes_Order,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 324,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfPivot['Movement_Week'] = 'cw'+dfPivot['Movement_Date'].dt.strftime('%V')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 325,
   "metadata": {},
   "outputs": [],
   "source": [
    "if 'OOS' in df['Movement_Code'].unique():\n",
    "    dfPivot['OOS'] = dfPivot['OOS'].fillna(0)\n",
    "    dfPivot['Demand'] = dfPivot['Shipped'] + dfPivot['OOS']\n",
    "else:\n",
    "    dfPivot['OOS'] = dfPivot['OOS'].fillna(0)\n",
    "    dfPivot['Demand'] = dfPivot['Shipped']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 326,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfPivot = dfPivot.rename_axis(\"Line_ID\",axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 327,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfPivot.iloc[:, 4:] = dfPivot.iloc[:, 4:].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 328,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfPivot = dfPivot.sort_values(by=['Movement_Date'],ascending=False,inplace=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 329,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfPivot.reset_index(drop=True,inplace=True)\n",
    "dfPivot.sort_index(ascending=False,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "--- **Safety Stock Calculation script starts here** ---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Product chosen for this script is one of supplier's topsellers. It's is rarely included in promo activities."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 330,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfPivot = dfPivot[dfPivot['Base_Unit_Code']=='1234567890']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We choose calendar week as a period. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 331,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfOneProduct = dfPivot.groupby('Movement_Week',as_index=False)[['Stock','Shipped','OOS','Demand']].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aggregated quantities can be observed in the following table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 332,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Line_ID Movement_Week  Demand\n",
      "11               cw44   41607\n",
      "12               cw45   37393\n",
      "13               cw46   62100\n",
      "14               cw47   60111\n",
      "15               cw48   94296\n",
      "16               cw49   75029\n",
      "17               cw50   69098\n",
      "18               cw51    9144\n",
      "19               cw52   27278\n",
      "20               cw53    9279\n"
     ]
    }
   ],
   "source": [
    "print(dfOneProduct[['Movement_Week','Demand']].tail(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Z* is a demand threshold. Below we will calculate the probability for an occurence of demand quantity to be below *Z* value within a chosen period."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 333,
   "metadata": {},
   "outputs": [],
   "source": [
    "z = 50000 # Random value chosen as a threshold. Just a bit above of the mean value."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*MU* is a mean value for product's demand. In our case it's calculated based on data since August 16th."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 334,
   "metadata": {},
   "outputs": [],
   "source": [
    "mu = np.round(dfOneProduct['Demand'].mean(),decimals=2) # mean value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Sigma* is standard deviation value for product's demand."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 335,
   "metadata": {},
   "outputs": [],
   "source": [
    "sigma = np.round((np.std(dfOneProduct['Demand'],ddof=1)),decimals=2) # standard deviation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 336,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      " z: 50000 \n",
      " mu: 47933.9 \n",
      " sigma: 21003.95\n"
     ]
    }
   ],
   "source": [
    "print('\\n','z:',z,'\\n','mu:',mu,'\\n','sigma:',sigma)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below we compute the CDF of a normal distribution - *Alpha*. It's calculated based on inputs above only.\n",
    "\n",
    "Later we will create a table with an extended row of CDF levels."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 337,
   "metadata": {},
   "outputs": [],
   "source": [
    "alpha = np.round(norm.cdf(z,mu,sigma),decimals=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 338,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "alpha:  0.54\n"
     ]
    }
   ],
   "source": [
    "print('alpha: ',alpha)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Calculation of the inverse CDF of a normal distribution.** \n",
    "\n",
    "It will give us the threshold *z* that will achieve a probability *α* for an occurrence of the distribution *𝒳* to be below this threshold *z*. \n",
    "\n",
    "Here the use the same *alpha* value as in the example above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 339,
   "metadata": {},
   "outputs": [],
   "source": [
    "z1 = norm.ppf(alpha,mu,sigma)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 340,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Inverse CDF:  50043.0\n"
     ]
    }
   ],
   "source": [
    "print('Inverse CDF: ', np.round(z1,decimals=0))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can calculate the relationship between several service levels and required stock levels. Here we use variable *alpha1*. It represents an array of CDF values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 341,
   "metadata": {},
   "outputs": [],
   "source": [
    "alpha1 = np.array([0.5,0.6,0.7,0.8,0.9,0.95,0.965,0.975,0.985,0.99])\n",
    "inv = norm.ppf(alpha1,mu,sigma)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 342,
   "metadata": {},
   "outputs": [],
   "source": [
    "table1 = {'Inventory level, pcs': inv.astype(int),\n",
    "         'Cycle service level, %': alpha1*100}\n",
    "\n",
    "df1 = pd.DataFrame(table1)\n",
    "df1.set_index('Cycle service level, %', inplace=True)\n",
    "dfT1 = df1.transpose()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Table 1* below shows the relationship between the inventory level at the beginning of a period (1 week) and the expected cycle service level."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 343,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cycle service level, %   50.0   60.0   70.0   80.0   90.0   95.0   96.5   97.5   98.5   99.0\n",
      "Inventory level, pcs    47933  53255  58948  65611  74851  82482  85991  89100  93514  96796\n"
     ]
    }
   ],
   "source": [
    "print(dfT1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Calculation of a Service level factor.**\n",
    "\n",
    "Service level factor is the ratio that multiplies the demand deviation in order to compute the required safety stock and to obtain a desired service level."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 344,
   "metadata": {},
   "outputs": [],
   "source": [
    "table2 = {'Service level factor': np.round(norm.ppf(alpha1),decimals=2),\n",
    "          'Cycle service level, %': alpha1*100}\n",
    "\n",
    "df2 = pd.DataFrame(table2)\n",
    "df2.set_index('Cycle service level, %', inplace=True)\n",
    "dfT2 = df2.transpose()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Table 2* below shows the relationship between the desired cycle service level and its service level factor. Impact of service level factor becomes bigger as you approach 100% service level. A higher cycle service level implies bigger extra costs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 345,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cycle service level, %  50.0  60.0  70.0  80.0  90.0  95.0  96.5  97.5  98.5  99.0\n",
      "Service level factor     0.0  0.25  0.52  0.84  1.28  1.64  1.81  1.96  2.17  2.33\n"
     ]
    }
   ],
   "source": [
    "print(dfT2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Cycle and Safety stocks' calculation.**\n",
    "\n",
    "Value for cycle stock is taken from Table 1 and corresponds to 95% cycle service level."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 346,
   "metadata": {},
   "outputs": [],
   "source": [
    "InventoryLevel = dfT1.loc[['Inventory level, pcs'],95].values[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we want to calculate a safety stock in order to perform with a 95% service level, then we should multiply Service level factor (*1.64*) by Demand deviation (*sigma*)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 347,
   "metadata": {},
   "outputs": [],
   "source": [
    "# SafetyStock = (1.64*sigma).astype(int) # This code also works, but it implies \n",
    "                                         # a manual search for necessary service level factor.\n",
    "\n",
    "SLFactor = dfT2.loc[['Service level factor'],95].values[0]  # 95 can be replaced with a desired service level.\n",
    "\n",
    "SafetyStock = (SLFactor*sigma).astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cycle Stock equals to Inventory level minus Safety Stock. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 348,
   "metadata": {},
   "outputs": [],
   "source": [
    "CycleStock = InventoryLevel - SafetyStock"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All three types of stock are displayed below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 349,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                           \n",
      "Safety stock, pcs     34446\n",
      "Cycle stock, pcs      48036\n",
      "Inventory level, pcs  82482\n"
     ]
    }
   ],
   "source": [
    "table3 = {'Safety stock, pcs': SafetyStock,\n",
    "          'Cycle stock, pcs': CycleStock,\n",
    "          'Inventory level, pcs': InventoryLevel}\n",
    "\n",
    "df3 = pd.DataFrame(table3,index=[''])\n",
    "dfT3 = df3.transpose()\n",
    "print(dfT3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Summary:**\n",
    "\n",
    "Inventory level calculated above is likely to be sufficient for performing on a 95% service level.\n",
    "\n",
    "According to inputs, we calculate replenishment based on weekly demand. In this particular case, we don't take Lead time into account.\n",
    "\n",
    "A more complex approach to stock calculation will be presented in the next document."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}