Excel modeling question

[kc1985]

Hi all,

Attachment: https://drive.google.com/file/d/0B6JPJC8HktchYk1kbjNHRHpZb1U/view?usp=sharing

In the attachment above, I've made a simple logistics model.

• Column C shows the daily shipments on each date
• The shipment capacity is 20000 (cell C6)
• The shipments follow FIFO i.e.
  • For the 45k shipments of Dec 2nd, 20k will get shipped on 2nd, another 20k on 3rd and 5k on 4th
  • The 30k shipments of Dec 3rd will then start getting shipped on 4th
• Once the items are shipped, they get delivered as Row 4 i.e. 1% of total shipped items are delivered same day, 4% on the shipped day + 1, 10% on the shipped day + 2 etc.
• Cell I9 onwards shows when the items will be delivered
  • For example - of the 45k items (from 2nd Dec),
    • 200 will be delivered on 2nd Dec (20k got shipped and 1% are delivered same day)
    • 1000 will be delivered on 3rd Dec (4% of 20k which got shipped on 2nd + 1% of 20k that got shipped on 3rd(
    • and so on...

I'm struggling with an elegant formula for Cell I9 onwards - right now its a mess of hard coded values etc.

Any help would be appreciated on how I can approach this - another waterfall model etc.?

Thanks

 

[kc1985]

Not sure on how to change the title / or edit the post -

Title: Help for "Simple waterfall modeling - logistics model"

 

[kc1985]

Bump!