Log In
Register
Home
January 25, 2021
Offline
Hello. I am trying to come up with a way to take my current quantity on hand and reduce it based on sales plan but the sales plan needs to be applied to the batch the expires first then to the next one to expire, and etc.
Tabs "Sls Plan" and I"nvlevel" are simply the raw data i have.
The tab "InvlevelPT" is the raw data in a pivot table. Bringing in EAN/UPC, expiration date, and unit quantity. Then to the right I did lookup to bring in sales plan for that EAN/UPC #.
So what I am trying to do is this on the "invlevelpt" tab is I want to find a way to apply the sales forecast starting in column E row 6 for material 1(50 units). I would want to apply that to the date that expires first. Which is 01/31/205. So in this example for the item that expires on 01/31/2025 I currently have 158 units(cell C8) so for the Nov sales forecast units (50 units) I would want to apply 50 units to 01/31/2025. Then Dec take sales of 60 and apply to balance of the 01/31/2025. Then Jan apply 48 units to 01/31/2025 then use the balance 22 units to the next date that expires next which would be 05/31/2025.
Starting in row 35 i have mocked up a basic idea but not locked into that type of format. It doesn't need to be this format. It can look like anything i just need to apply sales forecast to each material (1 & 2) in this example until it gets to zero to ensure it is sold before it expires. Thank you to anyone that can help
Trusted Members
October 17, 2018
Offline
What version of Excel are you using?
January 20, 2019
Offline
I started looking at this in Power Query. It is not an easy solution and is probably beyond my capability. But I have a few questions.
Are you going to have more that 2 Items?
I assume that Item 1 and Item 2 are separate although they have the same inventory in the table you submitted.
Will the number of Item1's and Item 2's (and so on) vary in the tale, i.e., Item 1 has 10 entries and Item 2 has 8.
What do you want to do if you have deducted all sales for a product and have inventory left. Will the remaining inventory simply be carried over without deductions for that item.
An Excel formula guru may be able to come up with something, but I think using Power Query would be more efficient especially if you have a lot of data.
I'll try to give it a shot if you can answer the question.
Cedric
1 Guest(s)
