topic Re: Calculo de Margem in Brasil

Calculo de Margem

Fri, 14 Apr 2017 14:47:57 GMT

Voltando aqui para pedir ajuda a comunidade.

Tenho duas tabelas, uma com as compras das loja e outra com as vendas das lojas, e queria uma luz em como calcular a margem dos produtos vendidos (Valor Venda - Valor Custo)/Valor Venda

As tabelas seriam assim

TABELA VENDA PARA AS LOJAS

Data	NF	Destino	Produto	Quant	Valor Unitario Custo	Valor Total Custo
26-Jan-17	27019	Loja 1	BE047	4	60,00	240,00
26-Jan-17	27019	Loja 1	CL001	5	50,00	250,00
26-Jan-17	27019	Loja 1	BE023	4	30,00	120,00
26-Jan-17	27019	Loja 1	CL212	3	80,00	240,00
26-Jan-17	27019	Loja 1	CL012	2	75,00	150,00
26-Jan-17	27019	Loja 1	ML012	7	90,00	630,00
01-Feb-17	27095	Loja 2	BE047	5	60,00	300,00
01-Feb-17	27095	Loja 2	CL001	5	5,00	25,00
01-Feb-17	27095	Loja 2	CL012	2	75,00	150,00

TABELA VENDA DAS LOJAS

Filial	Data Venda	NF	Produto	Quant	Valor Unitario Venda	Valor Total Venda
Loja 2	09-mar-17	4454	CL012	1	168,00	168,00
Loja 2	10-mar-17	4456	BE047	1	128,00	128,00
Loja 2	10-mar-17	4456	CL001	1	105,00	105,00
Loja 1	01-mar-17	14704	BE047	1	128,00	128,00
Loja 1	01-mar-17	14704	CL001	1	105,00	105,00
Loja 1	01-mar-17	14704	CL012	1	168,00	168,00
Loja 1	28-mar-17	14825	CL001	4	105,00	420,00
Loja 1	28-mar-17	14825	ML012	4	199,00	796,00

Se alguem puder ajudar agradeco.

Muito obrigado

Luis

Re: Calculo de Margem

Anonymous — Fri, 14 Apr 2017 15:01:21 GMT

Why not join both the tables and use normal expression (Value Sale - Value Cost) / Value Sale??

Re: Calculo de Margem

Fri, 14 Apr 2017 15:12:59 GMT

The tables are already linked by "product" (Produto)

The problem is I just want the margin from the produts sold.

To pick the "Value Sale" is easy, hard is to get the respective (same products and same quantities) "Value cost"

Can you help me?

Re: Calculo de Margem

Anonymous — Fri, 14 Apr 2017 15:25:23 GMT

When you join with product, you get the Value Sale of same products I didn't get you why you need same quantities??

Can you please explain what you are trying to calculate??

Re: Calculo de Margem

Fri, 14 Apr 2017 17:14:07 GMT

Shiva,

Tks for your help

Let me try to explain better

Another example

Cost Table

Product	Quant	Value Cost	Total Value Cost
Prod 10	10	100,00	1.000,00
Prod 20	5	75,00	375,00

Sold Table

Product	Quant Sold	Value Sale	Total Value Sale
Prod 10	3	300,00	900,00
Prod 20	1	150,00	150,00

The correct margin for the sold products is

Total Value Sale = 1.050 (3 x 300 + 1 x 150)

Total Value Cost = 375 (3 x 100 + 1 x 75)

Margin = 0,6429 or 64,29%

I tried to do that in 2 ways at quik sense and get different number as bellow

(Sum([Value Sale])-Sum([Value Cost]))/Sum([Value Sale]) - I get 0,6111 or 61,11%

(Sum([Total Value Sale])-Sum([Total Value Cost]))/Sum([Total Value Sale]) - I get -0,3095 or -30,95%

Hope you can help me.

Cheers