Calculating "Weeks of Supply" in Excel

June 30th, 2006

In retail there is an important measure called "Weeks of Supply", which essentially determines how long the current inventory will last based on a future sales forecast.

Calculating Weeks of Supply in Excel is difficult since you have to determine how many cells (weeks) would be added up from the sales forecast before you exceed the current inventory. There aren't any formulas that count cells one at a time until a limit is reached, then return the distance to that cell. There may be a simpler solution to this, but here's what I came up with.

First, I created a new row called "Cumulative Sales". This is just the sales accumulated by week.

Then I entered my Weeks of Supply formula.

=SUMPRODUCT(MAX((COLUMN(W$59:$AK59)-COLUMN())*((W$59:$AK59-V$59)<V$56)))

With a generic range:

=SUMPRODUCT(MAX((COLUMN(future_cum_sales_range)-COLUMN())*((future_cum_sales_range-current_week_sales)<current_week_inventory)))

Explanation: