Contabilidade Gerencial - CUST Gerenciamento de Custos e Preços. Por Vinícius Lima Martins
[email protected] ATENÇÃO: Esta planilha foi desenvolvida para o fim de pesquisas e estudos não possuindo quaisquer direitos autorais, pede-se entretando ao copiá-la divulgar a fonte e o autor.
erencial - CUSTOS
a o fim de pesquisas e estudos ede-se entretando ao copiá-la
Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Adriano Leal Bruni e Rubens Famá
CUSTOS.XLS: 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
ilha 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 Descrição Receitas Custos variáveis :
Matéria-prima Embalagem
Número de unidades = Total 15,000.00 (2,000.00) (3,000.00)
Subtotal Custos Var Custos fixos : Aluguel da fábrica
(5,000.00) (1,800.00)
Subtotal Custos Fix Subtotal Custos Desp variáveis :
Fretes de entrega
(1,800.00) (6,800.00) (2,200.00)
Subtotal Desp Var Desp fixas :
Aluguel do escritório
(2,200.00) (800.00)
Subtotal Desp fixas Subtotal Despesas Total Gastos Resultado Cálculo da margem de contribuição Descrição Receitas (-) Gastos variáveis (=) Margem de contribuição
(800.00) (3,000.00) (9,800.00) 5,200.00
Total 15,000.00 (7,200.00) 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 Custo do pedido Custo de manutenção Demanda
Símbolo Cp Ce D
Valor
Unidade
14.00 2.00 10,000.00
Análise de custos de pedido e estocagem Q 374.17
Cte 374.17
Ctp 374.17
CT 748.33
Cte - Ctp 0.00
Variável de decisão Lote econômico:
374.17
(Q, quantidade do pedido)
Fórmulas dos custos Custo total de manutenção de estoques Custo total dos pedidos Custo total
Gráficos Q 150 300 450 600 750 900 1050 1200 1350 1500 1650 1800 1950 2100
Cte 150.00 300.00 450.00 600.00 750.00 900.00 1,050.00 1,200.00 1,350.00 1,500.00 1,650.00 1,800.00 1,950.00 2,100.00
Cte = Ce x Q/2 Ctp = Cp x D/Q CT = Cte + Ctp
Step
150
Ctp 933.33 466.67 311.11 233.33 186.67 155.56 133.33 116.67 103.70 93.33 84.85 77.78 71.79 66.67
CT 1,083.33 766.67 761.11 833.33 936.67 1,055.56 1,183.33 1,316.67 1,453.70 1,593.33 1,734.85 1,877.78 2,021.79 2,166.67
Cte - Ctp (783.33) (166.67) 138.89 366.67 563.33 744.44 916.67 1,083.33 1,246.30 1,406.67 1,565.15 1,722.22 1,878.21 2,033.33
2,250.00 2,000.00 1,750.00 1,500.00 1,250.00 1,000.00 750.00 500.00 250.00 0
250
500
750 Cte
1000
1
Ctp
C
Note no gráfico acima que, quando Ctp e Ct
Gráfico de perfil de demanda Parâmetros iniciais
Parâmetros do gráfico
Demanda Q Est Inicial Est Seg
1500 375 375 0
Step Ponto mínimo
0.06 0
Quantidades
Perfil de estoque 375.0 350.0 325.0 300.0 275.0 250.0 225.0 200.0 175.0 150.0 125.0 100.0 75.0 50.0 25.0 -
0.10
0.20
0.30
0.40
0.50
0.60
Tempo
Tempo 0.06 0.13 0.19 0.25 0.25 0.31 0.38 0.44 0.50 0.50 0.56
Saídas (93.8) (93.8) (93.8) (93.8) (93.8) (93.8) (93.8) (93.8) (93.8)
Compras 375.0 375.0 -
Subtotal Estoque Seg 375.0 281.3 187.5 93.8 375.0 281.3 187.5 93.8 375.0 281.3 -
Estoque 375.0 281.3 187.5 93.8 375.0 281.3 187.5 93.8 375.0 281.3
0.70
0.80
0.90
1.00
0.63 0.69 0.75 0.75 0.81 0.88 0.94 1.00 1.00
(93.8) (93.8) (93.8) (93.8) (93.8) (93.8) (93.8) -
375.0 375.0
187.5 93.8 375.0 281.3 187.5 93.8 375.0
-
187.5 93.8 375.0 281.3 187.5 93.8 375.0
de compra
0.00
2,250.00 2,000.00 1,750.00 1,500.00 1,250.00 1,000.00 750.00 500.00 250.00 0
250
500
750 Cte
1000 Ctp
1250
1500
1750
2000
CT
Note no gráfico acima que, quando Ctp e Cte se igualam, CT é mínimo.
Resultados
2250
Estoque Médio (q) Número de ressuprimentos Intervalo
0.60
0.70
0.80
0.90
187.50 4 0.25
1.00
Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Adriano Leal Bruni e Rubens Famá
CUSTOS.XLS: Rateio de custos fixos
Fretes de entrega Comissões de vendas
Produto Unidades Alfa 40 Total Unitário 15,000.00 375.00 (2,000.00) (50.00) (3,000.00) (75.00) (5,000.00) (125.00) (227.27) (5.68) (363.64) (9.09) (409.09) (10.23) (1,000.00) (25.00) (6,000.00) (150.00) (1,400.00) (35.00) (1,200.00) (30.00)
Aluguel do escritório Salário vendedores
(2,600.00) (636.36) (409.09)
(65.00) (15.91) (10.23)
(1,900.00) (763.64) (490.91)
(1,045.45) (3,645.45) (9,645.45) 5,354.55
(26.14) (91.14) (241.14) 133.86
(1,254.55) (3,154.55) (10,354.55) 4,645.45
Descrição Receitas Custos diretos Subtotal Custos Indiretos
Subtotal CIF Subtotal Custos Despesas diretas
Subtotal Desp Var Depesas indiretas
Materiais diretos Mão-de-obra direta Custos Diretos Manutenção fabril Salário supervisor Depreciação fabril
Subtotal Desp fixas Subtotal Despesas Total Gastos Resultado Critério de Rateio (1 a 4) => Custos Diretos
3 Total Percentual
Receitas Gastos Resultado Outro critério de rateio : Horas máquina
Total
Alfa Beta Soma (5,000.00) (6,000.00) (11,000.00) 45% 55% 100% 15,000.00 15,000.00 30,000.00 (9,645.45) (10,354.55) (20,000.00) 5,354.55 4,645.45 10,000.00
500.00
400.00
900.00
Produto Beta Total 15,000.00 (3,000.00) (3,000.00) (6,000.00) (272.73) (436.36) (490.91) (1,200.00) (7,200.00) (800.00) (1,100.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 Gastos Fixos Totais Gasto Variável Unitário Preço de Venda Unitário Vendas atuais (qtde)
$ 10,000.00 8.00 10.00 32,000
100,000.00 90,000.00 80,000.00 70,000.00 60,000.00 50,000.00
Intervalo do gráfico
500
40,000.00 30,000.00
Ponto de Equlíbrio (q) Ponto de Equlíbrio ($)
5,000 50,000.00
Margem Segurança (Qtde) Margem Segurança ($) Margem Segurança (%)
27,000.00 270,000.00 84%
20,000.00 10,000.00 -
Qtde.
GF 0
10,000.00
0
1000
2000
GF
GV
GT -
10,000.00
3000
GV
4000
5000
GT
6000
7000
Receita
Receita -
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
8000
9
4000
5000
GT
6000
7000
Receita
8000
9000 10000
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. DRE Simplificado Receita (-) Gastos Totais (-) Gastos Fixos (-) Gastos Variáveis (=) Lucro Operacional (s/ Desp fin) (-) Despesas financeiras (=) Lucro Líquido
Total Qtde = 1,000,000.00 944,000.00 294,000.00 650,000.00 56,000.00 56,000.00
Variação no Preço Unitário Total 50,000 Qtde = 20.00 1,100,000.00 18.88 1,009,000.00 5.88 294,000.00 13.00 715,000.00 1.12 91,000.00 1.12 91,000.00
Grau de alavancagem operacional (GAo) Grau de alavancagem financeira (GAf) Grau de alavancagem combinada (GAc) Ponto de Equilíbrio (q) Ponto de Equilíbrio ($) Margem Segurança (q) Margem Segurança ($) Margem Segurança (%) Análise das variações em $ e % DRE Simplificado Receita (-) Gastos Totais (-) Gastos Fixos (-) Gastos Variáveis (=) Lucro Operacional (s/ Desp fin) (-) Despesas financeiras (=) Lucro Líquido
6.25 1.00 6.25 42,000.00 840,000.00 8,000.00 160,000.00 16%
Variação em $ Total Unitário 100,000.00 65,000.00 (0.53) (0.53) 65,000.00 35,000.00 0.53 35,000.00 0.53
42,000 840,000.00 13,000.00 260,000.00 24%
Variação Total 10.00% 6.89% 0.00% 10.00% 62.50% #DIV/0! 62.50%
1 Unitário 55,000 20.00 18.35 5.35 13.00 1.65 1.65
Total Qtde = 900,000.00 879,000.00 294,000.00 585,000.00 21,000.00 21,000.00
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%
1 Unitário 45,000 20.00 19.53 6.53 13.00 0.47 0.47
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 Q
Real Preço ($)
Custo ($)
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
Preço ($)
Custo ($)
Q
Q
-
-
-
-
-
-
-
-
-
-
-
-
1.4220
Legenda : variações favoráveis em azul, defavoráveis em vermelho.
1.4800
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 Alíquota do IPI IPI integra a base de cálculo? (1=sim, 2=não) Gasto dos produtos sem ICMS Base para cálculo da substituição Lucro a ser computado no valor dos produtos
18% 4% 1 800.00 8,000.00 3,690.67
sim
Formar preços com lucro em % do preço de venda Sem substituição 60% Com substituição 60%
Sem substituição tributária Valor dos produtos (gasto + lucro) Valor dos produtos com ICMS (aplicação da fórmula) Valor do IPI Valor total da operação (preço) Valor do ICMS Lucro
4,490.67 5,524.94 221.00 5,745.94 1,034.27 3,690.67
Com substituição tributária Valor dos produtos (gasto + lucro) Valor dos produtos com ICMS Normal (fórmula) Valor do IPI Valor subtotal da operação Valor do ICMS Normal Valor do ICMS Substituído Valor do ICMS Total Valor total da operação (preço) Lucro
4,490.67 5,524.94 221.00 5,745.94 1,034.27 405.73 1,440.00 6,151.67 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 N K => 0 1 2 3 4 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
Uni 4.0% (800.00) 600.00 400.00 200.00
Aquisição Analisada Duni Tê 4.0% 4.0% (500.00) (300.00) 300.00 100.00 400.00 400.00 200.00 500.00 50.00
Parâmetros obtidos Lêlê 4.0% (850.00) 700.00 400.00 200.00
VPL VUL TIR
VPL e VUL Parâmetros obtidos Uni 324.54 116.95 29%
Duni 378.82 104.36 41%
Tê 610.47 219.98 70%
Lêlê 370.70 133.58 32%
Séries Não Uniformes
Período 0 1 2 3 4
Fluxos -800 500 400 300 200
Taxa VPL
4% 488.25
Fórmula : P8 =VPL(P6;M8:M11)+M7 Note que o investimento inicial deve ser acrescentado fora da fórmula do VPL.
Séries Não Uniformes Data 1/1/1999 1/5/1999 3/12/1999 5/15/1999 6/16/1999
Fluxos -600 500 400 300 200
Taxa XVPL
4% #ADDIN?
Fórmula : P21 =XVPL(P19;M20:M24;L20:L24)
Séries Não Uniformes Data 1/1/1999 1/5/1999 3/12/1999 5/15/1999 6/16/1999
Fluxos -550 140 150 155 160
XTIR
#ADDIN?
Fórmula : P32 =XTIR(M31:M35;L31:L35)
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 finance
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
? 151.82%
1
Início
Fim
Dias úteis
Dias corridos
1/1/2001
6/5/2001
Contagem de dias
Lembre-se : os feriados devem estar abastecidos. Último feriado fornecido = Primeiro feriado fornecido =
12/25/2002
Ok
1/1/2001
Ok
Relação de feriados bancários (importante para a contagem de dias 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
JS D JC l
N I PV 1 0 [ENTER] 400 0 [ENTER] [/] 1 [-] 3400 [/] 0 [ENTER] Visor [/] 1 [-] =>30,000,000 0.03 [/] Visor [ENTER]3 => 0,000,0 [x] 1 2 1 [ENTER] 400 1 [ENTER] [ENTER]400 00[ENTER] [/][ENTER] [-] 0.031 [/] [ENTER] 0 [/] Visor [-] 3=> [/] 0.03 0,000, Visor [EN= 3 [f] [REG] 3 [i][f]400 [REG] [PV]30[n] [PMT] [f]400 [REG] [PV] [g] 3[BEG] 0 [n][PMT] 3 [n] [i] 0Visor [g] [PMT] [BEG] =>[g] 0[i].[ 3c 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 #NUM! -100.00% Cálculos na HP 12C - Não Mexa Nunca !!! Juros N I 3 [n]
3 [i]
-
PV
400 [PV]
N
[f] [REG] 3 [i] 400 [PV] 0 [PMT] [g] [BEG] [n] Visor => 0 . A HP aproxima o cálculo de n para o inteiro superior. S
I
[f] [REG] 3 [n] 400 [PV] 0 [PMT] [g] [BEG] [i] Visor => 0
PV
[f] [REG] 3 [n] 3 [i] 0 [PMT] [g] [BEG] [PV] Visor => 0
PMT
[f] [REG] 3 [n] 3 [i] 400 [PV] [g] [BEG] [PMT] Visor => -137.2933
FV
[f] [REG] 3 [n] 3 [i] 0 [PMT] 400 [PV] [g] [BEG] [FV] Visor => 0
xílio da matemática financeira
eja obter. Juros Compostos
Juros Compostos
PMT
FV
TIPO
? (137.29)
1 Antec Tipo : 0 = postecipado, sem entrada
G] 3 [n] 3 [i] 400 [PV] [g] [BEG] [PMT] Visor => -137.2933
1 = antecipado, com entrada
e para a contagem de dias úteis).
4
PMT FV TIPO Não existe 400 [ENTER] 0.03 [ENTER] 3 [x] 1 [+] [x] Visor => 0,000,000 Não existe 400 [ENTER] 1 [ENTER] 0.03 [ENTER] 3 [x] [-] [/] Visor => 0,000, [f] [REG] 3 [n] [f] 3 [i] [REG] 400 [PV] 3 [n] [g] 3 [i][BEG] 0 [PMT] [PMT] 400Visor [PV] => [g] -137.2933 [BEG] [FV] Visor => 0 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)
PMT 0 [PMT]
(437.09)
FV
TIPO [g] [BEG]
HP aproxima o cálculo de n para o inteiro superior. Seu valor com quatro casas é igual a 0
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)
Vendas
Custos
10.00
5.40
25.00
8.00
20.00
7.00
15.00
6.50
Para ajuste linear : Y = a + b.X a= b= R= R2 = Equação =
3.8200 0.1660 0.9910 0.9820 Y = 3.82 + 0.166.X , valor de R2 igual a 0.982
Estimativas Para X : 40
Para Y : 12
Y estimado : 10.4600
X estimado : 48.7063
6.X , valor de R2 igual a 0.982
Análise de regressão e correlação: gráficos. Diagrama de Dispersão Modelo Linear
8.00 7.50 7.00 6.50 6.00 5.50 5.00 10.00
12.50
15.00
17.50
20.00
22.50
25.00
22.50
25.00
22.50
25.00
Diagrama de Dispersão Modelo Polinomial
8.00 7.50 7.00 6.50 6.00 5.50 5.00 10.00
12.50
15.00
17.50
20.00
Diagrama de Dispersão Modelo Potência
8.00 7.50 7.00 6.50 6.00 5.50 5.00 10.00
12.50
15.00
17.50
20.00
são
0.00
8.00 7.50 7.00 6.50 6.00 5.50 22.50
25.00
são l
0.00
Diagrama de Dispersão Modelo Logarítmico
5.00 10.00
12.50
17.50
20.00
22.50
25.00
22.50
25.00
22.50
25.00
Diagrama de Dispersão Modelo Exponencial
8.00 7.50 7.00 6.50 6.00 5.50 22.50
25.00
5.00 10.00
12.50
são
0.00
15.00
15.00
17.50
20.00
Diagrama de Dispersão Média Móvel (3p)
8.00 7.50 7.00 6.50 6.00 5.50 22.50
25.00
5.00 10.00
12.50
15.00
17.50
20.00
Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Adriano Leal Bruni e Rubens Famá
CUSTOS.XLS: Descrever