Please show formulas used in excel to complete the below chart. P
| Product | KL-2000-10 | ||||||||
| safety stock | 0 | ||||||||
| min order qty | 0 | ||||||||
| Lead Time/days | 2 | ||||||||
| Order Quantity | 200 | DAILY PERIODS | |||||||
| On Hand Inventory/units | 225 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
| Gross Requirements | 150 | 140 | 170 | 185 | 160 | 170 | 190 | 205 | |
| DRP In Transit Receipts | 200 | ||||||||
| Projected Available Balance | 75 | ||||||||
| Net Requirements | |||||||||
| DRP Planned Order Receipt | |||||||||
| DRP Planned Order Release | |||||||||
Steps for MRP below
| repeat these steps for every week | ||
| step 1 | IOH= | PIOH of last week |
| step 2 | Requirement= | GR-IOH-SR |
| step 3 | NR= | is equal to requirement (if requirement>0) OR 0 (if requirement is negative) |
| step 4 | PR= | in L4L PR=NR |
| step 5 | projected inventory on hand PIOH= | IOH+PR-GR |
| step 6 | POR= | PR of the preceding week |
Answer:
| Period | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | |
| Gross requirements GR | 150 | 140 | 170 | 185 | 160 | 170 | 190 | 205 | |
| Scheduled receipts SR | 0 | 200 | 0 | 0 | 0 | 0 | 0 | 0 | |
| Projected Available balance= Projected inventory on hand PIOH | 75 | 135 | 165 | 180 | 20 | 50 | 60 | 55 | |
| IOH= 225 | inventory on hand IOH | 225 | 75 | 135 | 165 | 180 | 20 | 50 | 60 |
| Requirement | -75 | -135 | 35 | 20 | -20 | 150 | 140 | 145 | |
| Net requirements NR | 0 | 0 | 35 | 20 | 0 | 150 | 140 | 145 | |
| Q = 200 | Planned order receipts (PR) | 0 | 0 | 200 | 200 | 0 | 200 | 200 | 200 |
| Lead Time LT=2 | Planned order releases POR | 200 | 200 | 0 | 200 | 200 | 200 | 0 | 0 |
Please show formulas used in excel to complete the below chart. P Product KL-2000-10 safety stock...