Estou montando uma planilha para controlar o custo médio de alguns itens de meu estoque. Nessa planilha faço os lançamentos da quantidade de itens que Comprei e da quantidade de itens que Vendi, de forma que a quantidade em estoque é =Qtd.Compra-Qtd.Venda.
Como o preço de custo varia conforme a época eu não posso simplesmente fazer uma média para saber o custo médio do item, pois preciso desconsiderar os itens que já foram vendidos no cálculo.
Exemplificando:
. Col. A - Col.B - Col.C
1. Item - Custo) - Qtd.Compra
2. Prego - 1,00 - 200
3. Prego - 1,50 - 100
4. Martelo - 5,00 - 20
5. Prego - 1,20 - 150
Sabendo que ainda tenho em estoque a quantia de 200 Pregos (ou seja, que já vendi 250 Pregos, logo sobraram apenas 200), preciso calcular o custo médio desses 200 Pregos que me sobraram em estoque, sendo que preciso desconsiderar neste cálculo o custo dos Pregos que já foram vendidos).
Neste caso tenho que me sobraram 200 pregos: 150 custando 1,20 + 50 custando 1,50, logo daria um custo médio de 1,275 por prego.
Gostaria de montar uma fórmula/macro que eu entraria com o Nome do Item (Prego) e Quantidade em Estoque (200) e ela me retornasse o custo médio com base nas ultimas compras do item que somassem as 200 unidades.