I need to calculate weeks of supply with a dynamic wos calculation. Using inventory from wk 21 (281,061), project that into shipments of the future weeks (wk 22, 23, 24 etc). In the first case, 281,061 covers 143,179 leavng a balance of 137,882. That balance then covers week 23 shipments of 114,629, leaving a balance of 23,253. That balance covers .169 of week 24 shipments (137,433). the answer for weeks of supply of my first inventory number of (281,061) should be 2.169. How do I write this given that in some cases, I may need to divide the first month, in other cases,after the second, in others after the third, etc, etc? Please HELP!!
Ship Invoice Prod Inv DOS
Week 21 129,230 281,061
Week 22 143,179 119,401 106,629 244,511
Week 23 114,629 108,588 117,813 247,695
Week 24 137,433 128,794 140,111 250,373
Week 25 135,212 134,335 159,597 274,758
Week 26 137,889 108,670 143,902 280,771
Week 27 133,547 104,278 95,723 242,947
Week 28 101,955 125,136 139,340 280,332
Week 29 135,157 133,540 173,838 319,013
Week 30 198,935 123,848 119,226 239,304
Week 31 260,513 164,958 172,347 151,138
Week 32 168,814 299,092 153,292 135,616
Bookmarks