On Hand Inventory

[TG2812]

Hi,

The objective is to come up with the formula in F4 that give you the number of months in takes to deplete the current inventory (F3) based on your current sales forecast (G2:02) (I put the expected results the formula should obtain)

I included the expected results in row 4. Note that the result may be negative (too many forecasted sales and not enough inventory).

Thanks in advance for your feedback.



[TABLE="class: cms_table_cms_table"]
<tbody>[TR]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]
[TH="align: center"]H[/TH]
[TH="align: center"]I[/TH]
[TH="align: center"]J[/TH]
[TH="align: center"]K[/TH]
[TH="align: center"]L[/TH]
[TH="align: center"]M[/TH]
[TH="align: center"]N[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Model[/TD]
[TD]Region[/TD]
[TD]Configuration[/TD]
[TD][/TD]
[TD="align: right"]Jan-17[/TD]
[TD="align: right"]Feb-17[/TD]
[TD="align: right"]Mar-17[/TD]
[TD="align: right"]Apr-17[/TD]
[TD="align: right"]May-17[/TD]
[TD="align: right"]Jun-17[/TD]
[TD="align: right"]Jul-17[/TD]
[TD="align: right"]Aug-17[/TD]
[TD="align: right"]Sep-17[/TD]
[TD="align: right"]Oct-17[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Model 1[/TD]
[TD]Region 1[/TD]
[TD]Configuration 1[/TD]
[TD]My sales forecast[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Model 1[/TD]
[TD]Region 1[/TD]
[TD]Configuration 1[/TD]
[TD]My inventory[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Model 1[/TD]
[TD]Region 1[/TD]
[TD]Configuration 1[/TD]
[TD]My Inventory cover (in mths)[/TD]
[TD="align: right"]8.67[/TD]
[TD="align: right"]7.33[/TD]
[TD="align: right"]5.67[/TD]
[TD="align: right"]5.33[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]0.67[/TD]
[/TR]
</tbody>[/TABLE]


Anybody has an idea ?

Thanks in advance for your responses, time and help.

 

[Domenic]

I think some of your calculations are incorrect. Assuming that your data starts at A1 with the column header Model, try...

E4, copied across:

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(E2:$N2,,,,COLUMN(E2:$N2)-COLUMN(E2)+1))<=E3))+
LOOKUP(0,SUBTOTAL(9,OFFSET(E2:$N2,,,,COLUMN(E2:$N2)-COLUMN(E2)+1))-
E2:$N2-E3,(E3-(SUBTOTAL(9,OFFSET(E2:$N2,,,,COLUMN(E2:$N2)-
COLUMN(E2)+1))-E2:$N2))/E2:$N2)

Hope this helps!

 

[TG2812]

The above formula is terrific! Any idea how to make it deal with negative number and potential errors?

 

[Domenic]

Do you mean negative inventory? Is that possible? If so, I don't know what you would expect as a result in that case. However, you can use the following amended formula, which will return the text value "N/A" when inventory exceeds the total projected sales...

=IF(E3<SUM(E2:$N2),SUMPRODUCT(--(SUBTOTAL(9,OFFSET(E2:$N2,,,,COLUMN(E2:$N2)-COLUMN(E2)+1))<=E3))+LOOKUP(0,SUBTOTAL(9,OFFSET(E2:$N2,,,,COLUMN(E2:$N2)-COLUMN(E2)+1))-E2:$N2-E3,(E3-(SUBTOTAL(9,OFFSET(E2:$N2,,,,COLUMN(E2:$N2)-COLUMN(E2)+1))-E2:$N2))/E2:$N2),IF(E3=SUM(E2:$N2),COUNT(E2:$N2),"N/A"))

 

[TG2812]

Thanks for the reply. However, I think I was not clear enough in my explanation:

When i get to the last cell of the range, the formula returns #DIV/0! error as there is not more sales forecast..any idea how to overcome this issue?

 

[Domenic]

Oh, I see. In that case, I think the amended formula that I tried posted and which didn't display properly should help. The formula should return "N/A" when there are no sales forecasted or inventory exceeds forecasted sales. Here's the formula...

E4, copied across:

=IF(E3 < SUM(E2:$N2),SUMPRODUCT(--(SUBTOTAL(9,OFFSET(E2:$N2,,,,COLUMN(E2:$N2)-COLUMN(E2)+1))<=E3))+LOOKUP(0,SUBTOTAL(9,OFFSET(E2:$N2,,,,COLUMN(E2:$N2)-COLUMN(E2)+1))-E2:$N2-E3,(E3-(SUBTOTAL(9,OFFSET(E2:$N2,,,,COLUMN(E2:$N2)-COLUMN(E2)+1))-E2:$N2))/E2:$N2),IF(E3=SUM(E2:$N2),COUNT(E2:$N2),"N/A"))

<sum(e2:$n2),sumproduct(--(subtotal(9,offset(e2:$n2,,,,column(e2:$n2)-column(e2)+1))<=e3))+lookup(0,subtotal(9,offset(e2:$n2,,,,column(e2:$n2)-column(e2)+1))-e2:$n2-e3,(e3-(subtotal(9,offset(e2:$n2,,,,column(e2:$n2)-column(e2)+1))-e2:$n2)) e2:$n2),if(e3="SUM(E2:$N2),COUNT(E2:$N2),"N/A"))[/CODE]</html"></sum(e2:$n2),sumproduct(--(subtotal(9,offset(e2:$n2,,,,column(e2:$n2)-column(e2)+1))<=e3))+lookup(0,subtotal(9,offset(e2:$n2,,,,column(e2:$n2)-column(e2)+1))-e2:$n2-e3,(e3-(subtotal(9,offset(e2:$n2,,,,column(e2:$n2)-column(e2)+1))-e2:$n2))>