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) (5,000.00) (1,800.00) Subtotal Custos Var Custos fixos : Aluguel da fábrica 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 14.00 2.00 10,000.00 Unidade 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 Cte = Ce x Q/2 Ctp = Cp x D/Q CT = Cte + Ctp 150 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 Ctp 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 Step 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 1 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 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 - 1500 375 375 0 Step Ponto mínimo 0.06 0 Perfil de estoque Quantidades 0.10 0.20 0.30 0.40 0.50 Tempo 0.60 0.70 0.80 0.90 1.00 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.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 CT 1500 1750 2000 2250 Note no gráfico acima que, quando Ctp e Cte se igualam, CT é mínimo. Resultados Estoque Médio (q) Número de ressuprimentos Intervalo 187.50 4 0.25 0.60 0.70 0.80 0.90 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 Descrição Receitas Custos diretos Subtotal Custos Indiretos 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) (2,600.00) (636.36) (409.09) (1,045.45) (3,645.45) (9,645.45) 5,354.55 3 Total Percentual (65.00) (15.91) (10.23) (26.14) (91.14) (241.14) 133.86 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) (1,900.00) (763.64) (490.91) (1,254.55) (3,154.55) (10,354.55) 4,645.45 Materiais diretos Mão-de-obra direta Custos Diretos Manutenção fabril Salário supervisor Depreciação fabril Subtotal CIF Subtotal Custos Despesas diretas Fretes de entrega Comissões de vendas Subtotal Desp Var Depesas indiretas Aluguel do escritório Salário vendedores Subtotal Desp fixas Subtotal Despesas Total Gastos Resultado Critério de Rateio (1 a 4) => Custos Diretos Receitas Gastos Resultado Outro critério de rateio : Horas máquina 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 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 Gastos Fixos Totais Gasto Variável Unitário Preço de Venda Unitário Vendas atuais (qtde) Intervalo do gráfico Ponto de Equlíbrio (q) Ponto de Equlíbrio ($) Margem Segurança (Qtde) Margem Segurança ($) Margem Segurança (%) $ 10,000.00 8.00 10.00 32,000 500 5,000 50,000.00 27,000.00 270,000.00 84% 100,000.00 90,000.00 80,000.00 70,000.00 60,000.00 50,000.00 40,000.00 30,000.00 20,000.00 10,000.00 0 1000 2000 3000 4000 5000 6000 7000 8000 9 GF GV GT Receita Qtde. 0 500 1000 1500 2000 2500 3000 3500 4000 4500 5000 5500 6000 6500 7000 7500 8000 8500 9000 9500 10000 GF 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 GV 4,000.00 8,000.00 12,000.00 16,000.00 20,000.00 24,000.00 28,000.00 32,000.00 36,000.00 40,000.00 44,000.00 48,000.00 52,000.00 56,000.00 60,000.00 64,000.00 68,000.00 72,000.00 76,000.00 80,000.00 GT 10,000.00 14,000.00 18,000.00 22,000.00 26,000.00 30,000.00 34,000.00 38,000.00 42,000.00 46,000.00 50,000.00 54,000.00 58,000.00 62,000.00 66,000.00 70,000.00 74,000.00 78,000.00 82,000.00 86,000.00 90,000.00 Receita 5,000.00 10,000.00 15,000.00 20,000.00 25,000.00 30,000.00 35,000.00 40,000.00 45,000.00 50,000.00 55,000.00 60,000.00 65,000.00 70,000.00 75,000.00 80,000.00 85,000.00 90,000.00 95,000.00 100,000.00 4000 5000 6000 7000 8000 9000 10000 GT Receita 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 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 42,000.00 840,000.00 8,000.00 160,000.00 16% 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 6.25 1.00 6.25 42,000 840,000.00 13,000.00 260,000.00 24% 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 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 1 Unitário 45,000 20.00 19.53 6.53 13.00 0.47 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 Q Materiais MOD CIF 0.90 0.09 0.85 Padrão Preço ($) 0.78 8.00 1.00 Custo ($) 0.7020 0.7200 0.8500 Soma 1.4220 Q 0.8000 0.1200 0.9000 Real Preço ($) 0.8000 7.0000 0.9500 Custo ($) 0.6400 0.8400 0.8550 1.4800 Q (0.1000) 0.0300 0.0500 - Legenda : variações favoráveis em azul, defavoráveis em vermelho. Diferença Preço ($) 0.0200 (1.0000) (0.0500) Custo ($) (0.0620) 0.1200 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 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 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 18% 4% 1 800.00 8,000.00 3,690.67 Formar preços com lucro em % do preço de venda Sem substituição 60% Com substituição 60% 4,490.67 5,524.94 221.00 5,745.94 1,034.27 3,690.67 sim 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 3 Juros Compostos N 3 I 3.0000% PV 400.00 Operações na HP 12C : [f] [REG] 3 [n] 3 [i] 400 [PV] [g] [BEG] [PMT] Viso Equivalência de Taxas Taxa A Nper A Taxa B Nper B 8.0% 12 ? 151.82% 1 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 = Primeiro feriado fornecido = 12/25/2002 1/1/2001 Ok 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 [/] 1 [-] 3400 [/] 1 [-] 30,000,000 0,000,0 0 [ENTER] [/] [ENTER] 0.03 [ENTER]3 [x] 1 0 Visor => [/] Visor => 2 1 [ENTER] 400 [ENTER]00[ENTER] 1 [/] [/] [-] 3=> 0,000,= 1 [ENTER] 400[/] [-] 0.03 [ENTER] [/] Visor [ENTER] 0 Visor 0.03 [EN 3 [f] [REG] 3 [i][f] [REG] 30[n] 400 [PV] 0 [PMT]0[g] [BEG]0[i] 400 [PV] [PMT] [g] 3 [n] 3 [n] Visor => . [f] [REG] [BEG] [i] [PMT] [g] [ 3c 4 Opção : 3 1 - Juros Simples Juros Compostos 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] N I PV PMT FV 3 [i] 400 [PV] - PV [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 [f] [REG] 3 [n] 400 [PV] 0 [PMT] [g] [BEG] [i] Visor => 0 [f] [REG] 3 [n] 3 [i] 0 [PMT] [g] [BEG] [PV] Visor => 0 [f] [REG] 3 [n] 3 [i] 400 [PV] [g] [BEG] [PMT] Visor => -137.2933 [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 ? (137.29) FV TIPO 1 Antec Tipo : 0 = postecipado, sem entrada 1 = antecipado, com entrada G] 3 [n] 3 [i] 400 [PV] [g] [BEG] [PMT] Visor => -137.2933 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] [i] 400 [PV] [g][i] 0 [PMT] 400 [PV] => -137.2933 Visor => 0 3 [REG] 3 [n] 3 [BEG] [PMT] Visor [g] [BEG] [FV] fómula ==> [f] [REG] 3 [n] 3 [i] 400 [PV] [g] [BEG] [PMT] Visor => -137.2933 (Não Mexa !!!) 5 Não tem Não tem (137.29) 6 (436.00) (439.56) (437.09) PMT 0 [PMT] 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) Vendas 10.00 25.00 20.00 15.00 Variável Depend. (Y) Custos 5.40 8.00 7.00 6.50 Para ajuste linear : Y = a + b.X a= b= R= R2 = Equação = Estimativas Para X : 40 Y estimado : 10.4600 3.8200 0.1660 0.9910 0.9820 Y = 3.82 + 0.166.X , valor de R2 igual a 0.982 Para Y : 12 X estimado : 48.7063 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 6.X , valor de R2 igual a 0.982 15.00 17.50 20.00 22.50 25.00 8.00 7.50 7.00 6.50 6.00 5.50 5.00 10.00 12.50 Diagrama de Dispersão Modelo Polinomial 15.00 17.50 20.00 22.50 25.00 8.00 7.50 7.00 6.50 6.00 5.50 5.00 10.00 12.50 Diagrama de Dispersão Modelo Potência 15.00 17.50 20.00 22.50 25.00 são 8.00 7.50 7.00 6.50 6.00 5.50 22.50 25.00 5.00 10.00 12.50 Diagrama de Dispersão Modelo Logarítmico 0.00 15.00 17.50 20.00 22.50 25.00 são l 8.00 7.50 7.00 6.50 6.00 5.50 22.50 25.00 5.00 10.00 12.50 Diagrama de Dispersão Modelo Exponencial 0.00 15.00 17.50 20.00 22.50 25.00 são 8.00 7.50 7.00 6.50 6.00 5.50 22.50 25.00 5.00 10.00 12.50 Diagrama de Dispersão Média Móvel (3p) 0.00 15.00 17.50 20.00 22.50 25.00 Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Adriano Leal Bruni e Rubens Famá CUSTOS.XLS: Descrever