Hi
I try to calculated availability of material and I'm not sure how to achieve that.
I have Bill of material for products and production plan, on hand balance for material.
Basically I want to show when material will not be on the stock.
Here are sample data:
Bill of material
| Product | Material | quantity |
| A | M1 | 1 |
| A | M2 | 1 |
| A | M3 | 0,5 |
| A | M4 | 2 |
| A | M2 | 0,5 |
| B | M8 | 1 |
| B | M2 | 1 |
| B | M5 | 0,5 |
| B | M4 | 2 |
| B | M3 | 0,5 |
| C | M1 | 2 |
| C | M2 | 0,5 |
| C | M6 | 1 |
| C | M3 | 1 |
| C | M5 | 1 |
Plan
| Product | Planned quantity | Plan |
| A | 100 | 1 |
| C | 200 | 2 |
| B | 500 | 3 |
Stock of material
| Material | On hand |
| M1 | 500 |
| M2 | 200 |
| M3 | 600 |
| M4 | 1000 |
| M5 | 200 |
| M6 | 8000 |
| M8 | 200 |
Output
| Plan | Product | Material | BOM Qty | Planned qty | total qty | Onhand-total |
| 1 | A | M1 | 1 | 100 | 100 | 400 |
| 1 | A | M2 | 1 | 100 | 100 | 100 |
| 1 | A | M3 | 0,5 | 100 | 50 | 550 |
| 1 | A | M4 | 2 | 100 | 200 | 800 |
| 1 | A | M2 | 0,5 | 100 | 50 | 50 |
| 2 | C | M1 | 2 | 200 | 400 | 0 |
| 2 | C | M2 | 0,5 | 200 | 100 | -50 |
| 2 | C | M6 | 1 | 200 | 200 | 7800 |
| 2 | C | M3 | 1 | 200 | 200 | 350 |
| 2 | C | M5 | 1 | 200 | 200 | 0 |
| 3 | B | M8 | 1 | 500 | 500 | -300 |
| 3 | B | M2 | 1 | 500 | 500 | -550 |
| 3 | B | M5 | 0,5 | 500 | 250 | -250 |
| 3 | B | M4 | 2 | 500 | 1000 | -200 |
| 3 | B | M3 | 0,5 | 500 | 250 | 100 |
What formula could help me to see negative balance on stock?
