Hello,
I am attempting to build a visual involving simulated scrapped inventory value. I have raw material expiration date information and consumption patterns, what i would like to do is simulate how much raw material will be scrapped based on the current consumption pattern. Below is the sample data.
Table 1 contains expiry date information of the raw material of different batches and its value plus stock on hand.
Table 1:
Operative Stock Days = (Stock on Hand / Average monthly consumpion) * 30 days
| Part No | Expiry Date | Batch No. | Value | Unit Cost | Stock on Hand | Operative Stock Days |
| A | 3/1/2021 | 1 | $ 22,500.00 | 1.5 | 15000 | 225 |
| A | 7/2/2021 | 2 | $ 22,650.00 | 1.51 | 15000 | 225 |
| A | 11/2/2020 | 3 | $ 14,900.00 | 1.49 | 10000 | 150 |
Table 2 contains the following information:
| Part No | Stock On Hand | Average Monthly Consumption (L12M) |
| A | 40000 | 2000 |
What i would like is an output of Table 3:
Highlighted in red indicates my desired output
| Part No | Expiry Date | Batch No. | Value | Unit Cost | Stock on Hand | Operative Stock Days | Date of consumption | Days to expiry | Material Remaining | Scrapped Inventory Value |
| A | 11/2/2020 | 3 | $ 14,900.00 | 1.49 | 10000 | 150 | 7/20/2020 | 105.00 | 3000.00 | $ 4,470.00 |
| A | 3/1/2021 | 1 | $ 22,500.00 | 1.5 | 15000 | 225 | 11/3/2020 | 118 | 7133.33 | $ 10,700.00 |
| A | 7/2/2021 | 2 | $ 22,650.00 | 1.51 | 15000 | 225 | 3/2/2021 | 122 | 6866.67 | $ 10,368.67 |
Important parameters to consider:
1. First in first out applies. Oldest batch with earliest expiry date will be consumed first.
2. For the date of consumption, the batch with the earliest expiry date is always today's date. Therefore it is important to identify the first batch for each part number.
3. The second batch with the next expiry date in line will theoretically start consuming when the first batch has been consumed completely (operative stock days becomes zero) or it has expired (the day after expiry date). This will apply until all batches has been consumed, or expired.
4. Assume that the average monthly consumption does not change.
How to arrive at material remaining:
Since we quantify the consumption of raw materials in days, and we are always looking at days to expiry, therefore i thought it is easier to calculate material remaining by days.
Operative Stock Days = (Stock on Hand / Average monthly consumpion) * 30 days
Operative Stock Days - Days to Expiry = Days Remaining.
Material remaining = (Days Remaining / 30) * average monthly consumption
Scrap inventory value = Material remaining * unit cost.
The way i figured is:
The formula needs to first identify which is the first batch, and assume today's date. Then it needs to calculate whether based on the operative stock days, will it be completely consumed or will there be any material left for expiry. If the material is completely consumed, then the second batch (assuming there is one) will start consuming on the day after the first batch is consumed. If there is material left for expiry from the first batch, then the second batch will start consuming on the day after the first batch is expired.
This is the logic i have come up with. I would appreciate if anyone can show me how to visualize this, or come up with a better solution.
Thanks!
