Calculate the Weeks of Supply
The following formula calculates the weeks of supply, where A3 contains the starting inventory, and C3:L3 contains the forecast of unit sales (for simplicity, the forecast extends only to ten weeks)...
=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(C3:L3,,,,COLUMN(C3:L3)-COLUMN(C3)+1))<=A3))+
LOOKUP(0,SUBTOTAL(9,OFFSET(C3:L3,,,,COLUMN(C3:L3)-COLUMN(C3)+1))-
C3:L3-A3,(A3-(SUBTOTAL(9,OFFSET(C3:L3,,,,COLUMN(C3:L3)-
COLUMN(C3)+1))-C3:L3))/C3:L3)
Based on the sample data, the formula returns 6.667, rounded to three decimal places.
Sample Workbook: Download