solution for assignment 2

Week3-ROP-SOLUTION.xlsx

ROP example

Reorder Point with Safety Stock	Compute the necessary items below to find WHEN to order!
But what if demand isn't constant?
But what if delivery isn't reliable??
Daily demand (average)	DD =	85.81	units/day	Historical Data
Standard deviation of Demand =	9.453	units/day	Daily Demand (units)	Length of each replenishment cycle (days)
90	12
Length of replenishment cycle (average) =	RC =	12.89	days	85	12
Standard deviation of Replenishment Cycle =	1.269	days	75	11
60	13
Service level target =	96%	78	15
z =	1.7506860713	83	14	HINT: To convert Service Level to z-score, use =NORM.S.INV(probability)
95	13
Reorder Point without Variability =	1,106.03	units	93	12
+	95	14	When demand is uncertain (always) and replenishment cycle is uncertain (always).
Safety Stock (to buffer from variability) =	SS =	199.7291088935	units	94
=	92
Reorder Point (in the real world!) =	ROP =	1,306.00	units	83
(round to the nearest unit)	90
85
Unit Cost of items in inventory =	I =	$ 15.00	per unit	95
Inventory carrying cost rate =	C =	32%	per year	80
How much does it cost to hold just the Safety Stock for one year?	$ 958.70	per year
Extra: How much more would it cost to increase Safety Stock to reach a 97% Service Level? To reach 99% Service Level?

𝜎�

𝑅𝐶��

𝑆𝑆��=𝑧�

ඥ

𝜎�

𝐷𝐷��

∗𝑅𝐶��+ 𝜎�

𝑅𝐶��

∗𝐷𝐷 �