colindarling
New Member
- Joined
- Jun 5, 2018
- Messages
- 1
I hope someone can help me.
ABC Company is responsible for processing and fulfilling repair claims for a luxury wristwatch manufacturer. ABC Company receives customer repair requests daily (7 days per week). The next business day, a box is sent from ABC Company to the customer with a return shipping label. The customer ships their box back to ABC Company (to process the repair claim and fix their watch). ABC Company expects wristwatches from all over the country and they arrive at their facility based on a predictable, "box arrival" distribution.
For example: On Day 1, 100 repairs requests are made. On Day 2, 100 boxes are shipped out, and beginning on Day 11, boxes begin to arrive at the processing facility - 10% of the boxes they expect back.
On Day 2, 100 additional repair requests are made. On Day 3, 100 additional boxes are shipped out. On Day 12, boxes from both Day 2 & Day 3 shipments begin to arrive at the processing facility (accumulating) - 15% from Day 2 shipments; 10% from Day 3 shipments = 25 boxes total.
We would like to be able to create a formula to calculate the rolling or cumulative receiving for 60 days out.
The receiving distribution is 10%, 15%, 25%, 20%, 20%, 10% beginning 9 days from shipment Date. For example: Day 1 requests will ship on Day 2, and will arrive for processing on Days 11, 12, 13, 14, 15. Day 2 requests will ship on Day 3, and will arrive for processing on Days 12, 13, 14, 15, 16.
In our real-world example, requests are shipped next day, but take up to 60 days to return. So when we build the distribution out (and the formula out) it gets pretty nuts.
ABC Company is responsible for processing and fulfilling repair claims for a luxury wristwatch manufacturer. ABC Company receives customer repair requests daily (7 days per week). The next business day, a box is sent from ABC Company to the customer with a return shipping label. The customer ships their box back to ABC Company (to process the repair claim and fix their watch). ABC Company expects wristwatches from all over the country and they arrive at their facility based on a predictable, "box arrival" distribution.
For example: On Day 1, 100 repairs requests are made. On Day 2, 100 boxes are shipped out, and beginning on Day 11, boxes begin to arrive at the processing facility - 10% of the boxes they expect back.
On Day 2, 100 additional repair requests are made. On Day 3, 100 additional boxes are shipped out. On Day 12, boxes from both Day 2 & Day 3 shipments begin to arrive at the processing facility (accumulating) - 15% from Day 2 shipments; 10% from Day 3 shipments = 25 boxes total.
We would like to be able to create a formula to calculate the rolling or cumulative receiving for 60 days out.
The receiving distribution is 10%, 15%, 25%, 20%, 20%, 10% beginning 9 days from shipment Date. For example: Day 1 requests will ship on Day 2, and will arrive for processing on Days 11, 12, 13, 14, 15. Day 2 requests will ship on Day 3, and will arrive for processing on Days 12, 13, 14, 15, 16.
In our real-world example, requests are shipped next day, but take up to 60 days to return. So when we build the distribution out (and the formula out) it gets pretty nuts.