Stock Balance with Certain Expiry Date: Will It Be Enough for Remaining Months?

Hello,

My 1st day and I need your expert support on how to write a formula for Stock Balance with Certain Expiry Date - whether it can cover for remaining months required quantity (Consumption for remaining months.)

[ If we look only the Balance Quantity on "F9", it would cover as requirement under Forecast "C9" Quantity. But, having expiry date on the product, we would not able to cover "C9" Quantity and required 800 more on "G15". My request is to create1 or 2 formulas only for all remaining 6 months (I will apply for 12 months), calculating for each month forecast with Balance Quantity with Exp date. So that we will know whether Balance Quantity will cover all months and if not cover, required quantity will show up after calculation. ]


I have attached file for your support.

Thanking in advance.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here I have done it for you today.)

This proposal employs a helper column (G3:G8) which may be hidden for aesthetic purposes.
1. Paste the following into cell G3 and then drag the fill handle down to cell G8: =SUM(F3:F$8)-SUM(C$3:C3)
2. Paste the following into cell B15 and then drag the fill handle over to cell G15: =IF(INDEX($G3:$G8,COLUMNS($B16:B16))>0,"OK",-INDEX($C3:$C8,MATCH(B12,$B3:$B8,0)))
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.