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.



 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Watch MrExcel Video

Forum statistics

Threads
1,127,234
Messages
5,623,535
Members
415,976
Latest member
tuananh09x

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
Top