This is a perennial question for retailers, for which there are a number of solutions in existence:

How can you calculate the "forward cover" of a product knowing its current inventory and armed with forward sales estimates.

eg.

• current inventory 100 units (cell A1)
• weekly forward sales estimates: 25, 30, 10, 40, 90... (in range A2:AX)

Here the answer would be 3.875 weeks (3 full weeks plus 0.875 of week 4)

I have a UDF to do this already. I also have some slightly complicated array functions to do this, eg.

=MATCH(TRUE,SUBTOTAL(9,OFFSET(A2:A13,,,ROW(A2:A13)-ROW(A2) 1))>A1,0)-1 (A1-SUM(A2:INDEX(A2:A13,MATCH(TRUE,SUBTOTAL(9,OFFSET(A2:A13,,,ROW(A2:A13)-ROW(A2) 1))>A1,0)-1)))/INDEX(A2:A13,MATCH(TRUE,SUBTOTAL(9,OFFSET(A2:A13,,,ROW(A2:A13)-ROW(A2) 1))>A1,0)-1 1)

I was wondering if there is a neater way with these 'new-fangled' array functions which have been available for the last few years in later versions of Excel?

CodePudding user response：

Here is another possible solution, although it requires the LET() function which is only available to newer version of excel (2021, 365 and later I believe).

The solution would be the following formula:

=LET(
    sales,A2:A50,
    inventory,A1,
    cum_sum,MMULT(SEQUENCE(1,ROWS(sales),1,0),(ROW(sales)<=TRANSPOSE(ROW(sales)))*sales),
    week_full,MATCH(TRUE,inventory<cum_sum,0) - 1,
    week_frac,(inventory - INDEX(cum_sum,week_full)) / INDEX(sales,week_full   1),
    week_full   week_frac
)

Explanation

Given inventory and the forward looking sales estimates, the formula calculates the running total (i.e. cumulated sum) of the sales estimates as shown in the table here below

The formula goes on to get the number of full weeks of 'forward cover' by finding the the value for the cumulated sum that exceeds the inventory minus one (here 4 - 1 = 3).

Lastly, for the value of the week fraction covered in the last week, the formula calculates inventory minus sum of sales estimates of all previous weeks divided by sales estimate of final week of cover (i.e. (100 - 65) / 40 = 0.875).

Edit

After simplifying the formula you used with the LET() function, I noticed it's doing exactly the same calculation with the only difference of how the cumulated sum is being calculated. Here's your formula using LET():

=LET(
    sales,A2:A50,
    inventory,A1,
    cum_sum,SUBTOTAL(9,OFFSET(sales,,,SEQUENCE(ROWS(sales)))),
    week_full,MATCH(TRUE,cum_sum>inventory,0)-1,
    week_frac,(inventory - INDEX(cum_sum,week_full)) / INDEX(sales,week_full 1),
    week_full   week_frac
)

CodePudding user response：

=LET(inv,A1,
     sales,A2:A6,
     cs,SCAN(0,sales,LAMBDA(x,y,x y)),
     m,XMATCH(A1,cs,1)-1,
m (inv-
   IF(m=0,
          0,
          INDEX(cs,m)))
   /INDEX(sales,m 1))

SCAN() is perfect for creating a cumulative sum.

It can be referenced inside XMATCH because of the use of LET.

Here m returns the number of full weeks and the final calculation is the number of full weeks (inv- cumulative sum up to the full week)/sales of the following week.