Planilha CUSTOS.XLS
Gerenciando Custos e Preços com o Excel
Por Adriano Leal Bruni e Rubens Famá
Atenção : os modelos aqui apresentados destinam-se exclusivamente
a dar suporte didático ao texto Gestão de Custos e Formação de Preços ,
publicado pela Editora Atlas. Embora os melhores esforços tenham sido
colocados na sua elaboração, os autores e a editora não dão nenhum tipo
de garantia, implícita ou explícita, sobre todo o material, incluindo modelos, textos
documentos e programas. Os autores e a editora não se responsabilizam por
quaisquer incidentes ou danos decorrentes da compra, da performance ou do uso
dos modelos, teorias e/ou exemplos apresentados no livro ou nesta planilha.
S.XLS
tinam-se exclusivamente
stos e Formação de Preços ,
hores esforços tenham sido
editora não dão nenhum tipo
o material, incluindo modelos, textos,
ora não se responsabilizam por
a compra, da performance ou do uso
ados no livro ou nesta planilha.
Planilha CUSTOS.XLS
Gerenciando Custos e Preços com o Excel
Por Adriano Leal Bruni e Rubens Famá
CUSTOS.XLS: Descrição dos modelos presentes na planilha
Planilha
Menu
Descrição
Total e Unitário
Materiais Diretos
Rateio
Ponto de Equilíbrio
Custo Volume Lucro
Padrão
Preços
Ciclo de Vida
Matemática Financeira
Regressão
Autores
nilha CUSTOS.XLS
do Custos e Preços com o Excel
o Leal Bruni e Rubens Famá
OS.XLS: Descrição dos modelos presentes na planilha
Descrição
Menu da planilha CUSTOS.XLS. Facilita a navegação e o uso dos modelos
apresentados.
Descreve os modelos presentes na planilha CUSTOS.XLS.
Permite construir mapas de composição de custos totais e unitários.
Facilita cálculos relativos à gestão de materiais, como a determinação do lote
econômico de compra e dos gráficos relativos aos custos de estocagem, de
pedidos e total.
Possibilita a execução de rateio de gastos indiretos a diferentes produtos,
empregando diversos critérios de rateio.
Permite analisar o ponto de equilíbrio contábil e as margens de segurança de
uma determinada situação.
Facilita o estudo das relações entre custos, volumes e lucros, analisando os
efeitos sobre gastos totais e unitários e lucros.
Possibilita análises comprarativas empregando o conceito de custo padrão.
Permite a composição rápida e fácil dos preços de venda, incluindo gastos,
impostos e lucros desejados.
Fornece os principais parâmetros financeiros, como o VPL e o VUL empregados
na análise do custeio do ciclo de vida.
Permite, de forma fácil, executar as principais operações da matemática
financeira.
Facilita os cálculos estatísticos necessários nas análises de regressão e
correlação.
Apresenta os autores do livro e da planilha.
Planilha CUSTOS.XLS
Gerenciando Custos e Preços com o Excel
Por Adriano Leal Bruni e Rubens Famá
CUSTOS.XLS: Modelo geral de análise de custos
Número de unidades =
Descrição Total
Receitas 15,000.00
Custos variáveis : Matéria-prima (2,000.00)
Embalagem (3,000.00)
Subtotal Custos Var (5,000.00)
Custos fixos : Aluguel da fábrica (1,800.00)
Subtotal Custos Fix (1,800.00)
Subtotal Custos (6,800.00)
Desp variáveis : Fretes de entrega (2,200.00)
Subtotal Desp Var (2,200.00)
Desp fixas : Aluguel do escritório (800.00)
Subtotal Desp fixas (800.00)
Subtotal Despesas (3,000.00)
Total Gastos (9,800.00)
Resultado 5,200.00
Cálculo da margem de contribuição
Descrição Total
Receitas 15,000.00
(-) Gastos variáveis (7,200.00)
(=) Margem de contribuição 7,800.00
500
Unitário
30.00
(4.00)
(6.00)
(10.00)
(3.60)
(3.60)
(13.60)
(4.40)
(4.40)
(1.60)
(1.60)
(6.00)
(19.60)
10.40
Unitário
30.00
(14.40)
15.60
Planilha CUSTOS.XLS
Gerenciando Custos e Preços com o Excel
Por Adriano Leal Bruni e Rubens Famá
CUSTOS.XLS: Análise de lotes econômicos de compra
Dados fornecidos
Variável Símbolo Valor Unidade
Custo do pedido Cp 15.00
Custo de manutenção Ce 0.50
Demanda D 24,000.00
Análise de custos de pedido e estocagem
Q Cte Ctp CT Cte - Ctp
374.17 93.54 962.14 1,055.68 (868.60)
Variável de decisão
Lote econômico: 1200.00 (Q, quantidade do pedido)
Fórmulas dos custos
Custo total de manutenção de estoques Cte = Ce x Q/2
Custo total dos pedidos Ctp = Cp x D/Q
Custo total CT = Cte + Ctp
Gráficos Step 150
Q Cte Ctp CT Cte - Ctp
150 37.50 2,400.00 2,437.50 (2,362.50) 3,000.00
300 75.00 1,200.00 1,275.00 (1,125.00)
450 112.50 800.00 912.50 (687.50) 2,500.00
600 150.00 600.00 750.00 (450.00)
2,000.00
750 187.50 480.00 667.50 (292.50)
900 225.00 400.00 625.00 (175.00)
1,500.00
1050 262.50 342.86 605.36 (80.36)
1200 300.00 300.00 600.00 - 1,000.00
1350 337.50 266.67 604.17 70.83
1500 375.00 240.00 615.00 135.00 500.00
1650 412.50 218.18 630.68 194.32
1800 450.00 200.00 650.00 250.00 -
1950 487.50 184.62 672.12 302.88
2100 525.00 171.43 696.43 353.57
Note no gráfico acima que, quando Ctp e C
Gráfico de perfil de demanda
Parâmetros iniciais Parâmetros do gráfico
Demanda 1500 Step 0.0625
Q 375 Ponto mínimo 0
Est Inicial 375
Est Seg 0
Perfil de estoque
400.0
350.0
300.0
Quantidades
250.0
200.0
150.0
100.0
50.0
-
- 0.20 0.40 0.60 0.80
Tempo
Tempo Saídas Compras Subtotal Estoque Seg Estoque
- 375.0 - 375.0
0.06 (93.8) - 281.3 - 281.3
0.13 (93.8) - 187.5 - 187.5
0.19 (93.8) - 93.8 - 93.8
0.25 (93.8) - - - -
0.25 - 375.0 375.0 - 375.0
0.31 (93.8) - 281.3 - 281.3
0.38 (93.8) - 187.5 - 187.5
0.44 (93.8) - 93.8 - 93.8
0.50 (93.8) - - - -
0.50 - 375.0 375.0 - 375.0
0.56 (93.8) - 281.3 - 281.3
0.63 (93.8) - 187.5 - 187.5
0.69 (93.8) - 93.8 - 93.8
0.75 (93.8) - - - -
0.75 - 375.0 375.0 - 375.0
0.81 (93.8) - 281.3 - 281.3
0.88 (93.8) - 187.5 - 187.5
0.94 (93.8) - 93.8 - 93.8
1.00 (93.8) - - - -
1.00 - 375.0 375.0 - 375.0
de compra
Uma empresa industrial emprega uma única matéria-prima. Sabendo que a demanda mensal deste material é constan
15.00
24,000 unidades, que seu custo unitário de pedido é igual a
0.50 , calcule o lote econêmico de compra.
e que seu custo unitário de estocagem é igual a
Uma empresa industrial emprega uma única matéria-prima. Sabendo que a demanda mensal deste material é constante e igu
1200
(868.60)
3,000.00
Cte Ctp CT
2,500.00
2,000.00
1,500.00
1,000.00
500.00
-
0 500 1000 1500 2000 2500
Note no gráfico acima que, quando Ctp e Cte se igualam, CT é mínimo.
Resultados
Estoque Médio (q) 187.50
Número de ressuprimentos 4
Intervalo 0.25
0.80 1.00 1.20
sal deste material é constante e igual a
este material é constante e igual a 24000 unidades, que seu custo unitário de pedido é igual a 15e que seu custo unitário de estocagem é ig
seu custo unitário de estocagem é igual a 0.5 , calcule o lote econêmico de compra.
Planilha CUSTOS.XLS
Gerenciando Custos e Preços com o Excel
Por Adriano Leal Bruni e Rubens Famá
CUSTOS.XLS: Rateio de custos fixos
Produto Unidades Produto
Descrição Alfa 40 Beta
Total Unitário Total
Receitas 15,000.00 375.00 15,000.00
Custos diretos Materiais diretos (2,000.00) (50.00) (3,000.00)
Mão-de-obra direta (3,000.00) (75.00) (3,000.00)
Subtotal Custos Diretos (5,000.00) (125.00) (6,000.00)
Custos Indiretos Manutenção fabril (227.27) (5.68) (272.73)
Salário supervisor (363.64) (9.09) (436.36)
Depreciação fabril (409.09) (10.23) (490.91)
Subtotal CIF (1,000.00) (25.00) (1,200.00)
Subtotal Custos (6,000.00) (150.00) (7,200.00)
Despesas diretas Fretes de entrega (1,400.00) (35.00) (800.00)
Comissões de vendas (1,200.00) (30.00) (1,100.00)
Subtotal Desp Var (2,600.00) (65.00) (1,900.00)
Depesas indiretas Aluguel do escritório (636.36) (15.91) (763.64)
Salário vendedores (409.09) (10.23) (490.91)
Subtotal Desp fixas (1,045.45) (26.14) (1,254.55)
Subtotal Despesas (3,645.45) (91.14) (3,154.55)
Total Gastos (9,645.45) (241.14) (10,354.55)
Resultado 5,354.55 133.86 4,645.45
Critério de Rateio (1 a 4) => 3 Alfa Beta Soma
Total (5,000.00) (6,000.00) (11,000.00)
Custos Diretos
Percentual 45% 55% 100%
Receitas 15,000.00 15,000.00 30,000.00
Gastos (9,645.45) (10,354.55) (20,000.00)
Resultado 5,354.55 4,645.45 10,000.00
Outro critério de rateio :
Horas máquina Total 500.00 400.00 900.00
Unidades
30 Soma
Unitário
500.00 30,000.00
(100.00) (5,000.00)
(100.00) (6,000.00)
(200.00) (11,000.00)
(9.09) (500.00)
(14.55) (800.00)
(16.36) (900.00)
(40.00) (2,200.00)
(240.00) (13,200.00)
(26.67) (2,200.00)
(36.67) (2,300.00)
0.00
(63.33) (4,500.00)
(25.45) (1,400.00)
(16.36) (900.00)
(41.82) (2,300.00)
(105.15) (6,800.00)
(345.15) (20,000.00)
154.85 10,000.00
Planilha CUSTOS.XLS
Gerenciando Custos e Preços com o Excel
Por Adriano Leal Bruni e Rubens Famá
CUSTOS.XLS: Análise do ponto de equilíbrio contábil
Descrição $
GF GV
Gastos Fixos Totais 10,000.00
120,000.00
Gasto Variável Unitário 8.00
Preço de Venda Unitário 10.00 100,000.00
Vendas atuais (qtde) 32,000
80,000.00
Intervalo do gráfico 500 60,000.00
Ponto de Equlíbrio (q) 5,000 40,000.00
Ponto de Equlíbrio ($) 50,000.00
20,000.00
Margem Segurança (Qtde) 27,000.00 -
Margem Segurança ($) 270,000.00 0 2000 4000 6000
Margem Segurança (%) 84%
Qtde. GF GV GT Receita
0 10,000.00 - 10,000.00 -
500 10,000.00 4,000.00 14,000.00 5,000.00
1000 10,000.00 8,000.00 18,000.00 10,000.00
1500 10,000.00 12,000.00 22,000.00 15,000.00
2000 10,000.00 16,000.00 26,000.00 20,000.00
2500 10,000.00 20,000.00 30,000.00 25,000.00
3000 10,000.00 24,000.00 34,000.00 30,000.00
3500 10,000.00 28,000.00 38,000.00 35,000.00
4000 10,000.00 32,000.00 42,000.00 40,000.00
4500 10,000.00 36,000.00 46,000.00 45,000.00
5000 10,000.00 40,000.00 50,000.00 50,000.00
5500 10,000.00 44,000.00 54,000.00 55,000.00
6000 10,000.00 48,000.00 58,000.00 60,000.00
6500 10,000.00 52,000.00 62,000.00 65,000.00
7000 10,000.00 56,000.00 66,000.00 70,000.00
7500 10,000.00 60,000.00 70,000.00 75,000.00
8000 10,000.00 64,000.00 74,000.00 80,000.00
8500 10,000.00 68,000.00 78,000.00 85,000.00
9000 10,000.00 72,000.00 82,000.00 90,000.00
9500 10,000.00 76,000.00 86,000.00 95,000.00
10000 10,000.00 80,000.00 90,000.00 100,000.00
GT Receita
8000 10000 12000
Planilha CUSTOS.XLS
Gerenciando Custos e Preços com o Excel
Por Adriano Leal Bruni e Rubens Famá
CUSTOS.XLS: Análise de custos, volumes e lucros.
Variação no Preço
DRE Simplificado Total Unitário Total
Qtde = 50,000 Qtde =
Receita 1,000,000.00 20.00 1,100,000.00
(-) Gastos Totais 944,000.00 18.88 1,009,000.00
(-) Gastos Fixos 294,000.00 5.88 294,000.00
(-) Gastos Variáveis 650,000.00 13.00 715,000.00
(=) Lucro Operacional (s/ Desp fin) 56,000.00 1.12 91,000.00
(-) Despesas financeiras - -
(=) Lucro Líquido 56,000.00 1.12 91,000.00
Grau de alavancagem operacional (GAo) 6.25
Grau de alavancagem financeira (GAf) 1.00
Grau de alavancagem combinada (GAc) 6.25
Ponto de Equilíbrio (q) 42,000.00 42,000
Ponto de Equilíbrio ($) 840,000.00 840,000.00
Margem Segurança (q) 8,000.00 13,000.00
Margem Segurança ($) 160,000.00 260,000.00
Margem Segurança (%) 16% 24%
Análise das variações em $ e %
DRE Simplificado Variação em $ Variação
Total Unitário Total
Receita 100,000.00 - 10.00%
(-) Gastos Totais 65,000.00 (0.53) 6.89%
(-) Gastos Fixos - (0.53) 0.00%
(-) Gastos Variáveis 65,000.00 - 10.00%
(=) Lucro Operacional (s/ Desp fin) 35,000.00 0.53 62.50%
(-) Despesas financeiras - - #DIV/0!
(=) Lucro Líquido 35,000.00 0.53 62.50%
1 1
Unitário Total Unitário
55,000 Qtde = 45,000
20.00 900,000.00 20.00
18.35 879,000.00 19.53
5.35 294,000.00 6.53
13.00 585,000.00 13.00
1.65 21,000.00 0.47
- - -
1.65 21,000.00 0.47
42,000
840,000.00
3,000.00
60,000.00
7%
em %
Unitário
0.00%
-2.83%
-9.09%
0.00%
47.73%
#DIV/0!
47.73%
Planilha CUSTOS.XLS
Gerenciando Custos e Preços com o Excel
Por Adriano Leal Bruni e Rubens Famá
CUSTOS.XLS: Análise de custo padrão versus real
Componentes Padrão Real
Q Preço ($) Custo ($) Q Preço ($) Custo ($) Q
Materiais 0.90 0.78 0.7020 0.8000 0.8000 0.6400 (0.1000)
MOD 0.09 8.00 0.7200 0.1200 7.0000 0.8400 0.0300
CIF 0.85 1.00 0.8500 0.9000 0.9500 0.8550 0.0500
- - -
- - -
- - -
- - -
Soma 1.4220 1.4800
Legenda : variações favoráveis em azul, defavoráveis em vermelho.
Diferença
Preço ($) Custo ($)
0.0200 (0.0620)
(1.0000) 0.1200
(0.0500) 0.0050
- -
- -
- -
- -
0.0580
Planilha CUSTOS.XLS
Gerenciando Custos e Preços com o Excel
Por Adriano Leal Bruni e Rubens Famá
CUSTOS.XLS: Formação de preços
Alíquota do ICMS 18% Formar preços com
Alíquota do IPI 4% lucro em % do preço
IPI integra a base de cálculo? (1=sim, 2=não) 1 sim de venda
Gasto dos produtos sem ICMS 800.00 Sem substituição
Base para cálculo da substituição 8,000.00
60%
Lucro a ser computado no valor dos produtos 3,690.67 Com substituição
60%
Sem substituição tributária
Valor dos produtos (gasto + lucro) 4,490.67
Valor dos produtos com ICMS (aplicação da fórmula) 5,524.94
Valor do IPI 221.00
Valor total da operação (preço) 5,745.94
Valor do ICMS 1,034.27
Lucro 3,690.67
Com substituição tributária
Valor dos produtos (gasto + lucro) 4,490.67
Valor dos produtos com ICMS Normal (fórmula) 5,524.94
Valor do IPI 221.00
Valor subtotal da operação 5,745.94
Valor do ICMS Normal 1,034.27
Valor do ICMS Substituído 405.73
Valor do ICMS Total 1,440.00
Valor total da operação (preço) 6,151.67
Lucro 3,690.67
Formar preços com
lucro em % do preço
de venda
Sem substituição
(0.04) Preço obtido:
5,745.94
Com substituição
0.00 Preço obtido:
6,151.67
Planilha CUSTOS.XLS
Gerenciando Custos e Preços com o Excel
Por Adriano Leal Bruni e Rubens Famá
CUSTOS.XLS: Análise de custos do ciclo de vida - VPL e VUL
Período Aquisição Analisada Parâmetros obtidos
N Uni Duni Tê Lêlê
K => 4.0% 4.0% 4.0% 4.0% VPL
0 (800.00) (500.00) (300.00) (850.00) VUL
1 600.00 300.00 100.00 700.00 TIR
2 400.00 400.00 400.00 400.00
3 200.00 200.00 500.00 200.00
4 50.00
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
VPL e VUL
Parâmetros obtidos
Uni Duni Tê Lêlê
324.54 378.82 610.47 370.70
116.95 104.36 219.98 133.58
29% 41% 70% 32%
Séries Não Uniformes
Período Fluxos Taxa 4%
0 -800
1 500 VPL 488.25
2 400
3 300 Fórmula : P8 =VPL(P6;M8:M11)+M7
4 200
Note que o investimento inicial
deve ser acrescentado fora da
fórmula do VPL.
Séries Não Uniformes
Data Fluxos Taxa 4%
1/1/1999 -600
1/5/1999 500 XVPL #NAME?
3/12/1999 400
5/15/1999 300 Fórmula : P21 =XVPL(P19;M20:M24;L20:L24)
6/16/1999 200
Séries Não Uniformes
Data Fluxos
1/1/1999 -550
1/5/1999 140 XTIR #NAME?
3/12/1999 150
5/15/1999 155 Fórmula : P32 =XTIR(M31:M35;L31:L35)
6/16/1999 160
Planilha CUSTOS.XLS
Gerenciando Custos e Preços com o Excel
Por Adriano Leal Bruni e Rubens Famá
CUSTOS.XLS: Aplicações genéricas com auxílio da matemática financ
Coloque um "?" no valor que deseja obter.
Cálculos Financeiros Básicos
Regime N I PV
3 3 3.0000% 400.00
Juros Compostos
Operações [f] [REG] 3 [n] 3 [i] 400 [PV] [g] [BEG] [PMT] Viso
na HP 12C :
Equivalência de Taxas
Taxa A Nper A Taxa B Nper B
8.0% 12 ? 1
151.82%
Contagem de dias
Início Fim Dias úteis Dias corridos
1/1/2001 6/5/2001
Lembre-se : os feriados devem estar abastecidos.
Último feriado fornecido = 12/25/2002 Ok
Primeiro feriado fornecido = 1/1/2001 Ok
Relação de feriados bancários (importante para a contagem de dia
1/1/2001 1/1/2002
2/26/2001 2/11/2002
2/27/2001 2/12/2002
4/13/2001 3/29/2002
4/21/2001 4/21/2002
5/1/2001 5/1/2002
6/14/2001 5/30/2002
9/7/2001 9/7/2002
10/12/2001 12/12/2002
11/2/2001 11/2/2002
11/15/2001 11/15/2002
12/25/2001 12/25/2002
N I PV
JS 1 #VALUE! #VALUE! #VALUE!
D 2 #VALUE! #VALUE! #VALUE!
JC 3 #VALUE! #VALUE! #VALUE!
l 3 c 4
Opção : 3 Juros Compostos
1 - Juros Simples
2 - Desconto Bancário
3 - Juros Compostos
Cálculos Financeiros Gerais (Não Mexa !!!)
2 3 4
1 (33.33) -33.33% -
2 #DIV/0! #DIV/0! -
3 #VALUE! #VALUE! #VALUE!
Cálculos na HP 12C - Não Mexa Nunca !!!
Juros N I PV
3 [n] 3 [i] 400 [PV]
N #VALUE!
I #VALUE!
PV #VALUE!
PMT [f] [REG] 3 [n] 3 [i] 400 [PV] [g] [BEG] [PMT] Visor => -137.2933
FV #VALUE!
xílio da matemática financeira
eja obter.
Juros Compostos
Juros Compostos
PMT FV TIPO
? 1
(137.29) Antec Tipo : 0 = postecipado, sem entrada
G] 3 [n] 3 [i] 400 [PV] [g] [BEG] [PMT] Visor => -137.2933 1 = antecipado, com entrada
para a contagem de dias úteis).
4
PMT FV TIPO
Não existe #VALUE!
Não existe #VALUE!
[i] 400 [PV] [g] [BEG] [PMT] Visor => -137.2933
[f] [REG] 3 [n] 3#VALUE!
fómula ==> [f] [REG] 3 [n] 3 [i] 400 [PV] [g] [BEG] [PMT] Visor => -137.2933
(Não Mexa !!!)
5 6
Não tem (436.00)
Não tem (439.56)
(137.29) #VALUE!
PMT FV TIPO
#VALUE! [g] [BEG]
Planilha CUSTOS.XLS
Gerenciando Custos e Preços com o Excel
Por Adriano Leal Bruni e Rubens Famá
CUSTOS.XLS: Análise de regressão aplicada
Variável Independ. (X) Variável Depend. (Y) Para ajuste linear : Y = a + b.X
Vendas Custos
10.00 5.40 a= 3.8200
25.00 8.00 b= 0.1660
20.00 7.00 R= 0.9910
15.00 6.50 R2 = 0.9820
Equação = Y = 3.82 + 0.166.X , valor de R2 igual a 0.982
Estimativas
Para X : Y estimado :
40 10.4600
Para Y : X estimado :
12 48.7063
Análise de regressão e correlação: gráficos.
y = 0.166x + 3.82
R² = 0.982 Diagrama de Dispersão
Modelo Linear
10.00
8.00
6.X , valor de R2 igual a 0.982 6.00
4.00
2.00
0.00
0.00 10.00 20.00
y = -0.001x2 + 0.201x + 3.545
R² = 0.9828 Diagrama de Dispersão
Modelo Polinomial
10.00
8.00
6.00
4.00
2.00
0.00
0.00 10.00 20.00
y = 2.0995x0.4113
R² = 0.9846 Diagrama de Dispersão
Modelo Potência
10.00
8.00
6.00
4.00
2.00
0.00
0.00 10.00 20.00
y = 2.7001ln(x) - 0.8523
ma de Dispersão R² = 0.9759 Diagrama de Dispersão
Modelo Logarítmico
10.00
8.00
6.00
4.00
2.00
0.00
30.00 0.00 10.00 20.00 30.00
y = 4.2942e0.0251x
ama de Dispersão R² = 0.9736 Diagrama de Dispersão
odelo Polinomial Modelo Exponencial
10.00
8.00
6.00
4.00
2.00
0.00
30.00 0.00 10.00 20.00 30.00
rama de Dispersão Diagrama de Dispersão
Modelo Potência Média Móvel (3p)
10.00
8.00
6.00
4.00
2.00
0.00
30.00 0.00 10.00 20.00 30.00
Planilha CUSTOS.XLS
Gerenciando Custos e Preços com o Excel
Por Adriano Leal Bruni e Rubens Famá
CUSTOS.XLS: Autores do livro e da planilha.
Adriano Leal Bruni, M.Sc. Rubens Famá, D.Sc.
E-Mail : albruni@hotmail.com E-Mail :
Home-Page : http://albruni.tripod.com
Rubens Famá, D.Sc.
rfama@usp.br
Planilha CUSTOS.XLS
Gerenciando Custos e Preços com o Excel
Por Adriano Leal Bruni e Rubens Famá
CUSTOS.XLS: Descrever