# Supply Chain Challenge - Calculating Cumulative Inbound Receipts

#### colindarling

##### New Member
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.

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

Replies
9
Views
113
Replies
0
Views
278
Replies
0
Views
55
Replies
1
Views
70
Replies
6
Views
111

1,127,422
Messages
5,624,701
Members
416,042
Latest member
Oden

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back