Guru do Excel e Power BI

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.

Boa tarde

Veja se o exemplo enviado, atende a sua necessidade.
Até

Foxtri

Boa noite Foxtri.

Obrigado pelo apoio, mas confesso que não entendo da programação das macros, tentei dar uma olhada no código para adaptar a minha planilha mas infelizmente não consegui.

Estou enviando em anexo o arquivo com as minhas Planilhas (uma de Resumo e outra de Movimentação), se você puder me ajudar fazendo favor ficarei muito grato, vou tentar especificar:

Na aba Resumo eu gostaria de calcular o Custo Médio (na coluna I), a fórmula/macro terá que ler qual o tipo de Item (na coluna G) e Quantidade em Estoque (na coluna H), então na aba Movimentação terá que identificar o Item (coluna A) e calcular o Custo Médio (utilizando o Custo Unitário da coluna D) apenas dos últimos itens comprados que somados resultam na mesma quantidade que tem em estoque.

Vou tentar exemplificar utilizando os dados da Planilha em anexo:

O Item Alicate (coluna G) tem apenas 12 unidades em estoque (coluna H), para calcular o custo médio (coluna I) destas 12 unidades preciso utilizar os dados presentes na aba (planilha) Movimentação.
Na aba Movimentação é possível identificar que foram feitas 2 compras de Alicates, uma primeira compra de 99 unidades a 59.70 e posteriormente uma segunda compra de 3 unidades a 67.70, porém não posso fazer uma média simples, visto que algumas unidades do Alicate já foram vendidas, para ser mais exato foram vendidas 90 unidades a 60.70.
Assim sendo, eu teria que identificar a quantidade atual em estoque (coluna H da aba Resumo) e realizar uma média de custo dos últimos itens que foram comprados onde a soma destes itens seja igual a quantidade em estoque: neste caso como tenho 12 unidades em estoque seria preciso identificar o custo médio das ultimas 12 unidades de Alicate que foram compradas, ou seja: 3 unidades a 67.70 + 9 unidades a 59.70, o que daria um custo médio de 61.70.

Não sei se consegui ser claro e objetivo na explicação, mas o grande ponto é o seguinte: preciso calcular a média de custo utilizando apenas as ultimas entradas do produto, desconsiderando os custos mais antigos referente aos itens que já foram vendidos.

Se possível não modifique a disposição dos dados nas Planilhas, sei que há varias células em branco, mas por favor não as delete pois futuramente vou incrementar outros dados e já deixei o local reservado para eles.

Obrigado pela ajuda.

Foxtri escreveu: ↑19 Jan 2021 às 13:57 Boa tarde

Veja se o exemplo enviado, atende a sua necessidade.
Até

Foxtri

Agradeço da ajuda, mas infelizmente não consegui aplicar na minha planilha. Na mensagem anterior enviei a planilha que utilizo, se puder me ajudar com ela ficarei grato. Obrigado