comparing Sales Forecast with Stock and calculating requirements

[illusionek]

Hello!

This is going to be my first VBA project and I am looking for some advise on how to approach it. I have only a basic knowledge of VBA and at the moment I really struggle not with the code but with making an initial design.

I do not expect to get a ready to use code as I appreciate below is a complex problem. However I would be grateful for general tips how to design it and and ideally what steps I have to follow for example: "use for each loop to get data from sreadsheet X to Y and then use Workseet Funcion Z to get calculation" etc.

Thank you in advance for all help.




Please see my problem below:


Stock spreadsheet has all the items I am trying to sell with 'sell by date' after which I cannot sell this item. Then in Sales Forecast I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.

I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with 'sale by date' 23/09 and so on.

So based on the attached example, I can see that on 16/09 I will consume only 5 cases from 'sell by date' 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.

Ideally I would like also to include the logic that if Item is out of date it would move to the next 'sell by date'.

So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10

For Item 2 I can see that units with 'Sell by date' 30/09 will be consumed on 25/09 and I will start taking stock from next 'sell by date' which is 14/10.

I hope all this makes sense and someone would be able to help me out.

I attached both spreadsheets plus an illustration of a solution I am looking for, which at the moment is done manually.

[GeneralDisarray]

So, if I look at each row in the Stock.xlsx - you would just like to know how much of each quantity was 'leftover' (i.e., not sold prior to expiring)?

Leftovers = (Qty for Stock row) - (Total QTY sold for this period)

The period would be sales that occurred before the expiration date, but after any previous expiration date for the same item (on some previous row).

I think I have a simple, non VBA way to deliver that... if that is indeed what you want (your numbers described above don't seem to match your attached files).


I sorted the tables you posted for comparison:

SALES TABLE
Product	Qty	Sell by date
Item 1	100	16-09-2014
Item 1	50	23-09-2014
Item 1	100	01-01-2014
Item 2	11	30-09-2014
Item 2	500	14-10-2014

Compared to:

STOCK TABLE
Product	Qty	Sell by date
Item 1	100	16-09-2014
Item 1	50	23-09-2014
Item 1	100	01-10-2014
Item 2	11	30-09-2014
Item 2	500	14-10-2014

[GeneralDisarray]

Anyway, this seems helpful:

SORT the Stocks table by ITEM, then by SELL-BY-DATE (oldest to newest). Now you can use an array formula link to the other workbook to get your 'qty not sold by sell by date, not including sales from earlier periods'...

Please Login or Register  to view this content.

Enter that as an "array formula" in your Stocks.xlsx D column (meaning you enter the formula and press cntrl shift enter altogether, not just enter!)

Leftovers are all zero based on the attached files.

STOCK TABLE
Product	Qty	Sell by date	Leftovers
Item 1	100	16-09-2014	0
Item 1	50	23-09-2014	0
Item 1	100	01-10-2014	0
Item 2	11	30-09-2014	0
Item 2	500	14-10-2014	0

[illusionek]

Hello

First of all thanks a lot for looking into my issue. I need 'leftover' quantity indeed but I need to know it by date (column D in Solution spreadsheet), so sadly I cannot use your solution.

I have also double checked and my figures seem fine and they match the spreadsheet. Perhaps you would like to look at PivotedVersion tab in Solution spreadsheet as it is much easier to read.

Many thanks!

[GeneralDisarray]

Ok, somehow I mixed up the tables - I see your numbers now. So, in your solution the dates 9/16/2014 and 9/29/2014 - do those come from the min/max sales dates.... OR ... should it come from min(stock sell by date) and max(sales date)?

[illusionek]

Hi

The second option is correct, it should be based on stock sell by date and max sales date.

Thanks a lot!