4
In a stock control scenario where 1 product can be supplied by 1 or more suppliers and 1 supplier can contain 1 or more products, what to do on the following occasion:
Product "X" has 2 items in stock provided by supplier "A" and has cost price at R$ 23.00 each and sale price of R$ 50.00. The store owner not to let the stock end, will and buy more 100 units of the same product at supplier "B" with cost price to R $ 13.00 each.
In this scenario the profit on each sale of this product provided by supplier A is R$ 27.00 and acquired by supplier B has a profit of R$ 37.00 on each sale.
My question is:
I cannot simply go in the product "X" and change the price to the value of the supplier "B" because this product supplied by the supplier "A" is not yet exhausted and doing so would generate inaccurate reports. Is there any way around this without having this duplicate product in the system? Is that my model is not the most appropriate for this kind of situation?
EDIT1:
Following @Motta’s help I removed the cost price from the product table and decided to insert in product table_has_supplier, but this way every time I make a sale I will have to make a sort of sum in the stock column of the supply table?
My previously raised difficulty remains, that is, if a customer buys 5 units of product "X" to R $ 50.00 each unit and I have in stock 2 units of this product supplied by supplier "A" with a cost price of R$ 23,00 and 100 units of that product supplied by the supplier "B" with a cost price of R$ 13,00 each, how would be the process to calculate the profit of that sale? and How would be represented this distinction of cost values of the same product in the table "sale"?
The sale and purchase price has to be in separate tables and with effective date because these values change. Sale because it is linked only to the product because I believe that the sale price is and even and purchase linked to product_fornacecor.
– Motta
@Motta Effective date? But how to know exactly when the product supplied by supplier "A" will finish the stock to then release the product from supplier "B" for sale?
– Vinicius.Silva
Duration of the price
– Motta
The accountant is the one who defines the low stock FIFO, LIFO, FEFO, PEPS or UEPS.
– Motta
@Motta sorry but I still could not visualize how entering a price effective date I would have this cost value control to calculate profit, anyway I added a new modeling following one of your tips, but I still don’t get the desired result. I wonder if I could evaluate this new model I’ve inserted?
– Vinicius.Silva
https://pt.linkedin.com/pulse/saiba-o-que-%C3%A9-fifo-lifo-fefo-Peps-ueps-sua-rela%C3%A7% based on this you lower the corresponding "stock" and verify the profit , the counter defines which form of "low" of the stock.
– Motta
I believe that there is one more important issue to consider, whether the cost of the product can vary from supplier to supplier, the selling price should not be different either, that is, even if the cost of the product in a particular supplier is higher, the selling price will be the same, I believe I can improve that in your modeling as well...
– Raphael Godoi