ellyzadg
Board Regular
- Joined
- May 9, 2016
- Messages
- 75
Hi Everyone,
I would like to ask the formula to be used for me to get the updated inventory per item in a given period. Below are the expected result.
Supply - Add inventory
Consume - Deduct in the inventory
A1. This shall be based per product and o given date range
A2. New Balance - Based on the revised count based on supply & consumption
Supply & Consumption Summary
Date From: 1/1/2022
Date To: 1/30/2022
These dates are dynamic based on user input
I would like to ask the formula to be used for me to get the updated inventory per item in a given period. Below are the expected result.
Supply - Add inventory
Consume - Deduct in the inventory
A1. This shall be based per product and o given date range
A2. New Balance - Based on the revised count based on supply & consumption
| Trans No. | Action Type | Action Date | Product | A1.Current Balance (Should be in Formula) | Quantity (Refers to the count being supplied or Consumed on a given date) | A2.New Balance (Should be in Formula) |
| 1 | Supply | 01/01/2022 | APPLE | 0 | 2 | 3 |
| 2 | Supply | 01/02/2022 | APPLE | 3 | 3 | 5 |
| 3 | Consume | 01/04/2022 | APPLE | 5 | 1 | 4 |
| 4 | Supply | 01/04/2022 | BANANA | 0 | 5 | 5 |
| 5 | Consume | 01/05/2022 | BANANA | 5 | 1 | 4 |
Supply & Consumption Summary
Date From: 1/1/2022
Date To: 1/30/2022
These dates are dynamic based on user input
| Product | A3. Supplied (formulated based on defined date range) | A4. Consumed (formulated based on defined date range) | Balance (A3 - A4) |
| APPLE | 5 | 1 | 4 |
| BANANA | 5 | 1 | 4 |
