Hi all,
I am stuck on building a solution to weekly calculate the stock age by FIFO (first-in-first-out) method for all our products (amount, days per date).
Example input data (note Stock minus Sell could be different from stock amount in the next row, because of depreciation or theft) :
| Article | Date | Purchase | Stock | Sell |
| 94082 | 14-3-2020 | 300 | 300 | 0 |
| 94082 | 21-3-2020 | | 300 | 0 |
| 94082 | 28-3-2020 | | 293 | 7 |
| 94082 | 4-4-2020 | | 285 | 8 |
| 94082 | 11-4-2020 | | 286 | -1 |
| 94082 | 18-4-2020 | | 277 | 9 |
| 94082 | 25-4-2020 | | 265 | 11 |
| 94082 | 2-5-2020 | | 263 | 2 |
| 94082 | 9-5-2020 | | 254 | 9 |
| 94082 | 16-5-2020 | | 237 | 17 |
| 94082 | 23-5-2020 | | 212 | 25 |
| 94082 | 30-5-2020 | | 186 | 26 |
| 94082 | 6-6-2020 | | 171 | 15 |
| 94082 | 13-6-2020 | | 152 | 18 |
| 94082 | 20-6-2020 | 353 | 491 | 14 |
| 94082 | 27-6-2020 | | 458 | 33 |
| 94082 | 4-7-2020 | | 440 | 19 |
| 94082 | 11-7-2020 | | 431 | 10 |
| 94082 | 18-7-2020 | | 414 | 17 |
| 94082 | 25-7-2020 | | 399 | 15 |
| 94082 | 1-8-2020 | | 374 | 25 |
| 94082 | 8-8-2020 | | 348 | 26 |
| 94082 | 15-8-2020 | | 323 | 25 |
| 94082 | 22-8-2020 | | 300 | 24 |
| 94082 | 29-8-2020 | | 292 | 8 |
| 94082 | 5-9-2020 | | 287 | 5 |
| 94082 | 12-9-2020 | | 286 | 1 |
| 94082 | 19-9-2020 | | 283 | 4 |
| 94082 | 26-9-2020 | | 285 | -2 |
To calculate the stock age amount and days, we have to take the Stock per certain date and look back at the last purchases (loop back) to define the stock age amount and days.
This could result in extra rows per date. So for example date 1-8-2020. Stock = 374. Look for last purchase (353 on 20-6-2020), so 353 have the age of 42 days (datediff between
Date And Last Purchase Date). For the remaining 21 we have to look back again for the next purchase (300 on 14-3-2020) and so on....
So the remaining 21 have the age of 140 days.
| | | | | | | RESULT |
| Article | Date | Purchase | Stock | Sell | | StockAge Amount | StockAge Days | Date |
| 94082 | 14-3-2020 | 300 | 300 | 0 | | | | |
| 94082 | 21-3-2020 | | 300 | 0 | | | | |
| 94082 | 28-3-2020 | | 293 | 7 | | | | |
| 94082 | 4-4-2020 | | 285 | 8 | | | | |
| 94082 | 11-4-2020 | | 286 | -1 | | | | |
| 94082 | 18-4-2020 | | 277 | 9 | | | | |
| 94082 | 25-4-2020 | | 265 | 11 | | | | |
| 94082 | 2-5-2020 | | 263 | 2 | | | | |
| 94082 | 9-5-2020 | | 254 | 9 | >> | 254 | 56 | 9-5-2020 |
| 94082 | 16-5-2020 | | 237 | 17 | | | | |
| 94082 | 23-5-2020 | | 212 | 25 | | | | |
| 94082 | 30-5-2020 | | 186 | 26 | | | | |
| 94082 | 6-6-2020 | | 171 | 15 | | | | |
| 94082 | 13-6-2020 | | 152 | 18 | | | | |
| 94082 | 20-6-2020 | 353 | 491 | 14 | >> | 353 | 0 | 20-6-2020 |
| 94082 | 27-6-2020 | | 458 | 33 | | 138 | 98 | 20-6-2020 |
| 94082 | 4-7-2020 | | 440 | 19 | | | | |
| 94082 | 11-7-2020 | | 431 | 10 | | | | |
| 94082 | 18-7-2020 | | 414 | 17 | | | | |
| 94082 | 25-7-2020 | | 399 | 15 | | | | |
| 94082 | 1-8-2020 | | 374 | 25 | >> | 353 | 42 | 1-8-2020 |
| 94082 | 8-8-2020 | | 348 | 26 | | 21 | 140 | 1-8-2020 |
| 94082 | 15-8-2020 | | 323 | 25 | | | | |
| 94082 | 22-8-2020 | | 300 | 24 | | | | |
| 94082 | 29-8-2020 | | 292 | 8 | >> | 292 | 70 | 29-8-2020 |
| 94082 | 5-9-2020 | | 287 | 5 | | | | |
| 94082 | 12-9-2020 | | 286 | 1 | | | | |
| 94082 | 19-9-2020 | | 283 | 4 | | | | |
| 94082 | 26-9-2020 | | 285 | -2 | | | | |
I am not that experienced in using macro's (especially not iterative macro's), so I hope someone can help me how to start solving this.
Thanks in advance!
Kind regards,
Johann
