Filetype Excel XLSX | Posted on 08 Aug 2022 | 3 years ago
Authentication
digital resources
256x Filetype XLSX File size 0.02 MB Source: excelwithwayne.com
Sheet 1: cycle service level
| Cycle Inventory | ||||||||
| Given reorder point determine Cycle Service Level | Safety Stock=Reorder Point-Mean Leadtime Demand | |||||||
| weekly demand | ||||||||
| mean | 2500 | Place order | ||||||
| sigma | 500 | arrives | variance for 2 weeks = 2*var for 1 week | |||||
| reorderpoint | 6000 | inventory depleted | stdev for2 weeks = sqrt(2)*stdev for 1 week | |||||
| leadtime | 2 | reorder order arrives | ||||||
| safety stock | 1000 | =B7-B11 | ||||||
| Leadtime demand | ||||||||
| leadtime mean | 5000 | =B8*B5 | Stdev Leadtime=Sqrt(N)*st dev for 1 week | |||||
| leadtime sigma | 707.106781186548 | =SQRT(B8)*B6 | ||||||
| Chance in stock during cycle | 0.921350396474857 | =NORM.DIST(B7,B11,B12,1) | ||||||
| 92% of cycles we do not run out |
| Reorder Point for | We want 90% Service Level | ||||
| Given Cycle Service Level | |||||
| determine reorder point and safety stock | Use NORM.INV function | ||||
| weekly demand | |||||
| mean | 2500 | ||||
| sigma | 500 | ||||
| reorderpoint | 5906.19380243682 | =NORM.INV(0.9,B11,B12) | ?? | ||
| leadtime | 2 | ||||
| safety stock | 906.193802436824 | ||||
| Leadtime demand | |||||
| leadtime mean | 5000 | ||||
| leadtime sigma | 707.106781186548 |
| Fill Rate | ||||
| Given reorder point | ||||
| determine fraction of all demand met on time | ||||
| weekly demand | ||||
| mean | 2500 | |||
| sigma | 500 | |||
| reorderpoint | 6000 | |||
| leadtime | 2 | |||
| safety stock | 1000 | Mean Shortage Per Cycle | ||
| Leadtime demand | SS*(1-Prob(Standard Normal <=SS/LeadtimeSigma)+LeadtimeSigma*StandardNormal Density(at SS/Leadtime Sigma) | |||
| leadtime mean | 5000 | |||
| leadtime sigma | 707.106781186548 | |||
| Order Quantity | 10000 | FROM EOQ | ||
| Expected Shortage per Cycle | 25.1272708300061 | =-B9*(1-NORM.DIST(B9/B12,0,1,1))+B12*NORM.DIST(B9/B12,0,1,0) | ||
| Fill Rate | 0.997487272916999 | =1-B14/B13 |
no reviews yet
Please Login to review.
