Okay i have a question... Right now i am making my life quite horrible, because of it. Probably you all can relate to these "Excel challenges".
I am trying to create a small ERP system, but i am running in some issues with the stock list. Let me explain it a bit further:
Sheet 1: Product list: The product list has basic information about the products, like SKU, Name, Complete Stock, Average buying price
Sheet 2: Import orders: This sheet i wanna fill in all import orders on date, with Date, ordername. SKU, Bought stock, Buying Price, Total buying price
Sheet 3: Sales: A list with all the orders we have, these will be deducted from the stock on the second sheet so that we can keep the average buying price as correct as possible.
My issue happens on the second sheet. I would love to let the stock go to zero per order. So let's say:
We have 3 orders of Product 1:
1 - 500 pcs - 20 dollar - 10.000 dollar
2 - 800 pcs - 15 dollar - 12.000 dollar
3 - 150 pcs - 21 dollar - 3.150 dollar
In the beginning the average price will be 17,34 (1+2+3/total stock) but when the batch 1 is sold out the average price will change to 15,94 (2+3/total stock). But the big question is how can i deduct the sold amount from order 1 and from than continue to order 2 and so on. So when there are 500 pieces sold it will become 0 and than the rest of the sold pieces go to order 2 till that one becomes 0 and so on.
I am trying to create a small ERP system, but i am running in some issues with the stock list. Let me explain it a bit further:
Sheet 1: Product list: The product list has basic information about the products, like SKU, Name, Complete Stock, Average buying price
Sheet 2: Import orders: This sheet i wanna fill in all import orders on date, with Date, ordername. SKU, Bought stock, Buying Price, Total buying price
Sheet 3: Sales: A list with all the orders we have, these will be deducted from the stock on the second sheet so that we can keep the average buying price as correct as possible.
My issue happens on the second sheet. I would love to let the stock go to zero per order. So let's say:
We have 3 orders of Product 1:
1 - 500 pcs - 20 dollar - 10.000 dollar
2 - 800 pcs - 15 dollar - 12.000 dollar
3 - 150 pcs - 21 dollar - 3.150 dollar
In the beginning the average price will be 17,34 (1+2+3/total stock) but when the batch 1 is sold out the average price will change to 15,94 (2+3/total stock). But the big question is how can i deduct the sold amount from order 1 and from than continue to order 2 and so on. So when there are 500 pieces sold it will become 0 and than the rest of the sold pieces go to order 2 till that one becomes 0 and so on.
| Stock calculation issue.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | |||||||||
| 2 | Import orders | ||||||||
| 3 | Product name | SKU | Stock | Sold | Total Stock | Buying price | Total buying price | ||
| 4 | Product 1 | 16068 | 500 | 520 | -20 | 20 | Sold out | ||
| 5 | Product 2 | 16069 | 150 | 0 | 150 | 17 | € 2.550,00 | ||
| 6 | Product 1 | 16068 | 800 | 0 | 800 | 15 | € 12.000,00 | ||
| 7 | Product 1 | 16068 | 150 | 0 | 150 | 21 | € 3.150,00 | ||
| 8 | Product 3 | 16070 | 225 | 0 | 225 | 16 | € 3.600,00 | ||
| 9 | |||||||||
| 10 | |||||||||
| 11 | Sold orders | SKU | Sold | ||||||
| 12 | Product 1 | 16068 | 520 | ||||||
| 13 | Product 2 | 16069 | 0 | ||||||
| 14 | Product 3 | 16070 | 0 | ||||||
| 15 | |||||||||
| 16 | |||||||||
| 17 | Product list | ||||||||
| 18 | Product name | SKU | Stock | Total buying price | Average price | ||||
| 19 | Product 1 | 16068 | 410 | € 15.150,00 | € 36,95 | ||||
| 20 | Product 2 | 16069 | 150 | € 2.550,00 | € 17,00 | ||||
| 21 | Product 3 | 16070 | 225 | € 3.600,00 | € 16,00 | ||||
| 22 | |||||||||
Product list | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D4:D8 | D4 | =C12 |
| E4:E8 | E4 | =[@Stock]-[@Sold] |
| G4:G8 | G4 | =IF([@[Total Stock]]>0,[@[Total Stock]]*[@[Buying price]],"Sold out") |
| C19 | C19 | =(SUMIF(Table1[SKU],$B$19,Table1[Total Stock]))-D4 |
| D19:D21 | D19 | =SUMIF(Table1[SKU],B19,Table1[Total buying price]) |
| E19:E21 | E19 | =D19/C19 |
| C20 | C20 | =(SUMIF(Table1[SKU],$B$20,Table1[Total Stock]))-D5 |
| C21 | C21 | =(SUMIF(Table1[SKU],$B$21,Table1[Total Stock]))-D6 |
