Hi,
I would like to ask for help regarding a project I have.
Base data:
To order from our supplier, we have to make a minimum order of 1000 USD.
I calculate our order needs with the following formula:
Minimum stock - (Current stock - (Average sales*Delivery time from supplier))
Example:
Minimum stock = 150
Stock = 100 pc
Average sale/day = 5
Delivery time in days = 10
Calculation for delivery cycles:
First delivery cycle: 150-(100-(5*10)) =
Second delivery cycle: 150-(100-(5*10*2))
.. etc
Our corrected stock on 'T' day is 50, so I have to order 100 pc of supplies, but if it is not enough in value, then
I jump to the next colum (cycle)
Example table:
| Item name | Minimum stock | Stock | Average sales | Delivery time | Cycle1 (order pc.) | Cycle2 (order pc.) | Cycle1 value | Cycle2 value |
| XY1 | 150 | 100 | 5 | 10 | 100 | 150 | 250usd | 500usd |
| XY2 | 150 | 100 | 5 | 10 | 100 | 150 | 250usd | 500usd |
I need to compare my TOTAL order values with the minimum of what our supplier requests (1000 USD). To do this, I sum the columns that contain the order values seperately and check if it is >= to 1000 USD. If so, then I want to have those order pc values that are in regards with the USD values. In this case, Cycle 2.
Result:
| Item name | Order PC | Order USD |
| XY1 | 150 | 500 |
| XY2 | 150 | 500 |
I have all the colums calculated but I struggle with the second part.
Please help me if you have a solution for this.
