Supply Chain Challenge - Cumulative Inbound Receiving Plan

[colindarling]

I have attached an example scenario for this question; 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 (our example only covers 20 days; formula only covers 3 days.) Instead of writing the formula the way we have in CELL I18, is there a way to write the formula more efficiently?

 

[NARAYANK991]

Hi ,

See if this works. Enter the following formula in I16 and copy down :

=SUMPRODUCT(C$16:C16,H$7:H7)

Narayan

 

[colindarling]

I appreciate the prompt reply, SUMPRODUCT as it is written does not quite work.

I've written the challenge a second way to add some more context.

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, 16. Day 2 requests will ship on Day 3, and will arrive for processing on Days 12, 13, 14, 15, 16,17.

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.

I updated the file to add sumproduct, perhaps you will still help me?

 

[NARAYANK991]

Hi ,

Can you enter the actual outputs that should be the result of the formula in cells I16 downwards for at least 20 cells , so that we get an idea of what is sought to be achieved ?

Narayan

 

[colindarling]

Please see attached file. I hope this helps!

 

[bosco_yip]

Please see attached file. I hope this helps!

Maybe,

In F2, formula copied down :

=IF(ROWS($1:1)<=H$6+1,SUMPRODUCT(D$6:D6*OFFSET(I$6,H$6,,-ROWS($1:1))),"")

Regards
Bosco

 

[colindarling]

This is so close! This builds my formula up to row 41 (THANK YOU!).

I've added columns K & L, sorry that this problem keeps evolving. Boxes that were shipped out will ALL eventually come back (between day 1-40 after their ship date). So now that you have helped me build the formula up, how do I continue to paste it down (pretend no more boxes are ever shipped). Eventually, the formula will return all boxes and the two cumulative numbers will reconcile.

We are so close! Thank you for your help!

 

[colindarling]

I finished a full "MANUAL" version of the formula. You will see on row 66, the formula (Column C) reaches its maximum size, but then starts to move down the boxes shipped column (column B) and eventually, all boxes return to the processing site.

 

[bosco_yip]

I finished a full "MANUAL" version of the formula. You will see on row 66, the formula (Column C) reaches its maximum size, but then starts to move down the boxes shipped column (column B) and eventually, all boxes return to the processing site.

1] Formula result put in Col G, in G6 formula copied down :

=IF(A6="","",SUMPRODUCT(OFFSET(B6,,,-MIN(ROWS($1:1),M$6+1))*OFFSET(N$6,M$6,,-MIN(ROWS($1:1),M$6+1))))

2] See attached file

Regards
Bosco

 

[Peter Bartholomew]

I think that what is being described is an example of a 'convolution' (in mathematics or image processing) or a 'spread' (insurance). In discrete terms the function is formed by multiplying a row vector by a column vector to get a rectangular array of products. The catch is that the terms of the convolution are given by the sum of terms across a diagonal array within the resulting matrix.

Although that is easy to set up using INDEX (the difference between the row and column indices being a parameter), spreadsheet formulas are resistant to summing the resulting values. The attached worksheet uses coercion to present the calculation to the INDEX function in a manner that causes it to return an array that may be summed.

ps The spreadsheet is several years old and the main layout is the transpose of that used in this question.