I am struggling to come up with a solution for cashflow projection of procurement items based on few weighted steps.
|Item #||Item Description||Price||Plan / Actual||Issue PO||Start Manufacturing||Inspection||Shipment||Delivery|
| || || ||Step Weight>>||20%||30%||20%||10%||30% (or 20%)|
| || || ||Forecast|| || || || ||31-Oct-22|
| || || ||Actual||25-Jun-22||30-Jul-22||01-Oct-22|| || |
| || || ||Forecast|| || ||01-May-23||15-Jun-23||15-Aug-23|
| || || ||Actual|| ||10-Oct-22|| || || |
| || || ||Forecast||01-Jan-23||10-Mar-23||20-Oct-23||15-Nov-23||15-Dec-23|
| || || ||Actual|| || || || || |
I am trying to calculate, for a specific date (say end of each month), how much is fund required, based on the dates and their weitages above.
Also, not every step has any date (for example, shipping is not required for Item-1), so the delivery payment weight will be varied (in this case balance 20% payment payment will be required for items required shipping, but for Item-1, there is no shipping, so payment on delivery will be balance 30%).
Result I am trying to get is, at the end of each month, need to calculate plan / forecast / actual fund required / disbursed.
Thank you in advance for your help.