Supply Chain Challenge - Calculating Cumulative Inbound Receipts

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.



 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top