Baixe o app para aproveitar ainda mais
Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original
Menu Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Gleison Glienke 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. Descrição Total e Unit Rateio Mat. Diretos Ponto Eq Custo, volume Padrão Ciclo de vida Preços Mat. Financ. Regressão Autores Descrição Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Gleison Glienke CUSTOS.XLS: Descrição dos modelos presentes na planilha Planilha Descrição Menu Menu da planilha CUSTOS.XLS. Facilita a navegação e o uso dos modelos apresentados. Descrição Descreve os modelos presentes na planilha CUSTOS.XLS. Total e Unitário Permite construir mapas de composição de custos totais e unitários. Materiais Diretos 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. Rateio Possibilita a execução de rateio de gastos indiretos a diferentes produtos, empregando diversos critérios de rateio. Ponto de Equilíbrio Permite analisar o ponto de equilíbrio contábil e as margens de segurança de uma determinada situação. Custo Volume Lucro Facilita o estudo das relações entre custos, volumes e lucros, analisando os efeitos sobre gastos totais e unitários e lucros. Padrão Possibilita análises comprarativas empregando o conceito de custo padrão. Preços Permite a composição rápida e fácil dos preços de venda, incluindo gastos, impostos e lucros desejados. Ciclo de Vida Fornece os principais parâmetros financeiros, como o VPL e o VUL empregados na análise do custeio do ciclo de vida. Matemática Financeira Permite, de forma fácil, executar as principais operações da matemática financeira. Regressão Facilita os cálculos estatísticos necessários nas análises de regressão e correlação. Autores Apresenta os autores do livro e da planilha. Menu Total e unitário Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Gleison Glienke CUSTOS.XLS: Modelo geral de análise de custos Número de unidades = 500 Descrição Total Unitário Receitas 15,000.00 30.00 Custos variáveis : Matéria-prima (2,000.00) (4.00) Embalagem (3,000.00) (6.00) 0.00 Subtotal Custos Var (5,000.00) (10.00) Custos fixos : Aluguel da fábrica (1,800.00) (3.60) 0.00 0.00 Subtotal Custos Fix (1,800.00) (3.60) Subtotal Custos (6,800.00) (13.60) Desp variáveis : Fretes de entrega (2,200.00) (4.40) 0.00 0.00 Subtotal Desp Var (2,200.00) (4.40) Desp fixas : Aluguel do escritório (800.00) (1.60) 0.00 0.00 Subtotal Desp fixas (800.00) (1.60) Subtotal Despesas (3,000.00) (6.00) Total Gastos (9,800.00) (19.60) Resultado 5,200.00 10.40 Cálculo da margem de contribuição Descrição Total Unitário Receitas 15,000.00 30.00 (-) Gastos variáveis (7,200.00) (14.40) (=) Margem de contribuição 7,800.00 15.60 Menu Limpar Materiais diretos Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Gleison Glienke CUSTOS.XLS: Análise de lotes econômicos de compra Dados fornecidos Variável Símbolo Valor Unidade Uma empresa industrial emprega uma única matéria-prima. Sabendo que a demanda mensal deste material é constante e igual a Custo do pedido Cp * 15.00 * 24,000 unidades, que seu custo unitário de pedido é igual a * 15.00 Custo de manutenção Ce * 0.50 e que seu custo unitário de estocagem é igual a * 0.50 , calcule o lote econêmico de compra. Demanda D * 24,000.00 Uma empresa industrial emprega uma única matéria-prima. Sabendo que a demanda mensal deste material é constante e igual a 24000 unidades, que seu custo unitário de pedido é igual a 15e que seu custo unitário de estocagem é igual a 0,5 , calcule o lote econêmico de compra. 1200 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) * (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) 300 * 75.00 * 1,200.00 * 1,275.00 * (1,125.00) 450 * 112.50 * 800.00 * 912.50 * (687.50) 600 * 150.00 * 600.00 * 750.00 * (450.00) 750 * 187.50 * 480.00 * 667.50 * (292.50) 900 * 225.00 * 400.00 * 625.00 * (175.00) 1050 * 262.50 * 342.86 * 605.36 * (80.36) 1200 * 300.00 * 300.00 * 600.00 0.0 1350 * 337.50 * 266.67 * 604.17 * 70.83 1500 * 375.00 * 240.00 * 615.00 * 135.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 Cte se igualam, CT é mínimo. Gráfico de perfil de demanda Parâmetros iniciais Parâmetros do gráfico Resultados Demanda 1500 Step 0.0625 Estoque Médio (q) * 187.50 Q 375 Ponto mínimo 0 Número de ressuprimentos 4 Est Inicial 375 Intervalo 0.25 Est Seg 0 Tempo Saídas Compras Subtotal Estoque Seg Estoque 0.0 * 375.0 0.0 * 375.0 * 0.06 * (93.8) 0.0 * 281.3 0.0 * 281.3 * 0.13 * (93.8) 0.0 * 187.5 0.0 * 187.5 * 0.19 * (93.8) 0.0 * 93.8 0.0 * 93.8 * 0.25 * (93.8) 0.0 0.0 0.0 0.0 * 0.25 0.0 * 375.0 * 375.0 0.0 * 375.0 * 0.31 * (93.8) 0.0 * 281.3 0.0 * 281.3 * 0.38 * (93.8) 0.0 * 187.5 0.0 * 187.5 * 0.44 * (93.8) 0.0 * 93.8 0.0 * 93.8 * 0.50 * (93.8) 0.0 0.0 0.0 0.0 * 0.50 0.0 * 375.0 * 375.0 0.0 * 375.0 * 0.56 * (93.8) 0.0 * 281.3 0.0 * 281.3 * 0.63 * (93.8) 0.0 * 187.5 0.0 * 187.5 * 0.69 * (93.8) 0.0 * 93.8 0.0 * 93.8 * 0.75 * (93.8) 0.0 0.0 0.0 0.0 * 0.75 0.0 * 375.0 * 375.0 0.0 * 375.0 * 0.81 * (93.8) 0.0 * 281.3 0.0 * 281.3 * 0.88 * (93.8) 0.0 * 187.5 0.0 * 187.5 * 0.94 * (93.8) 0.0 * 93.8 0.0 * 93.8 * 1.00 * (93.8) 0.0 0.0 0.0 0.0 * 1.00 0.0 * 375.0 * 375.0 0.0 * 375.0 Materiais diretos Cte Ctp CT Rateio Estoque Tempo Quantidades Perfil de estoque Ponto de Equilíbrio Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Gleison Glienke CUSTOS.XLS: Rateio de custos fixos Descrição Produto Unidades Produto Unidades Soma Alfa 40 Beta 30 Total Unitário Total Unitário Receitas 15,000.00 375.00 15,000.00 500.00 30,000.00 Custos diretos Materiais diretos (2,000.00) (50.00) (3,000.00) (100.00) (5,000.00) Mão-de-obra direta (3,000.00) (75.00) (3,000.00) (100.00) (6,000.00) Subtotal Custos Diretos (5,000.00) (125.00) (6,000.00) (200.00) (11,000.00) Custos Indiretos Manutenção fabril (227.27) (5.68) (272.73) (9.09) (500.00) Salário supervisor (363.64) (9.09) (436.36) (14.55) (800.00) Depreciação fabril (409.09) (10.23) (490.91) (16.36) (900.00) Subtotal CIF (1,000.00) (25.00) (1,200.00) (40.00) (2,200.00) Subtotal Custos (6,000.00) (150.00) (7,200.00) (240.00) (13,200.00) Despesas diretas Fretes de entrega (1,400.00) (35.00) (800.00) (26.67) (2,200.00) Comissões de vendas (1,200.00) (30.00) (1,100.00) (36.67) (2,300.00) 0.00 0.00 0.00 Subtotal Desp Var (2,600.00) (65.00) (1,900.00) (63.33) (4,500.00) Depesas indiretas Aluguel do escritório (636.36) (15.91) (763.64) (25.45) (1,400.00) Salário vendedores (409.09) (10.23) (490.91) (16.36) (900.00) 0.00 0.00 0.00 0.00 Subtotal Desp fixas (1,045.45) (26.14) (1,254.55) (41.82) (2,300.00) Subtotal Despesas (3,645.45) (91.14) (3,154.55) (105.15) (6,800.00) Total Gastos (9,645.45) (241.14) (10,354.55) (345.15) (20,000.00) Resultado 5,354.55 133.86 4,645.45 154.85 10,000.00 Critério de Rateio (1 a 4) => 3 Alfa Beta Soma Custos Diretos Total * (5,000.00) * (6,000.00) * (11,000.00) 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 Menu Achar o LEC com Atingir Meta Menu Custo Volume Lucro Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Gleison Glienke CUSTOS.XLS: Análise do ponto de equilíbrio contábil Descrição $ Gastos Fixos Totais * 10,000.00 Gasto Variável Unitário * 8.00 Preço de Venda Unitário * 10.00 Vendas atuais (qtde) * 32,000 Intervalo do gráfico 500 Ponto de Equlíbrio (q) * 5,000 Ponto de Equlíbrio ($) * 50,000.00 Margem Segurança (Qtde) * 27,000.00 Margem Segurança ($) * 270,000.00 Margem Segurança (%) 84% Qtde. GF GV GT Receita 0 * 10,000.00 0.0 * 10,000.00 0.0 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 Custo Volume Lucro GF GV GT Receita Padrão Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Gleison Glienke CUSTOS.XLS: Análise de custos, volumes e lucros. Variação no Preço 1 1 DRE Simplificado Total Unitário Total Unitário Total Unitário Qtde = 50,000 Qtde = 55,000 Qtde = 45,000 Receita * 1,000,000.00 * 20.00 * 1,100,000.00 * 20.00 * 900,000.00 * 20.00 (-) Gastos Totais * 944,000.00 * 18.88 * 1,009,000.00 * 18.35 * 879,000.00 * 19.53 (-) Gastos Fixos * 294,000.00 * 5.88 * 294,000.00 * 5.35 * 294,000.00 * 6.53 (-) Gastos Variáveis * 650,000.00 * 13.00 * 715,000.00 * 13.00 * 585,000.00 * 13.00 (=) Lucro Operacional (s/ Desp fin) * 56,000.00 * 1.12 * 91,000.00 * 1.65 * 21,000.00 * 0.47 (-) Despesas financeiras 0.0 0.0 0.0 0.0 0.0 (=) Lucro Líquido * 56,000.00 * 1.12 * 91,000.00 * 1.65 * 21,000.00 * 0.47 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 * 42,000 Ponto de Equilíbrio ($) * 840,000.00 * 840,000.00 * 840,000.00 Margem Segurança (q) * 8,000.00 * 13,000.00 * 3,000.00 Margem Segurança ($) * 160,000.00 * 260,000.00 * 60,000.00 Margem Segurança (%) 16% 24% 7% Análise das variações em $ e % DRE Simplificado Variação em $ Variação em % Total Unitário Total Unitário Receita * 100,000.00 0.0 10.00% 0.00% (-) Gastos Totais * 65,000.00 * (0.53) 6.89% -2.83% (-) Gastos Fixos 0.0 * (0.53) 0.00% -9.09% (-) Gastos Variáveis * 65,000.00 0.0 10.00% 0.00% (=) Lucro Operacional (s/ Desp fin) * 35,000.00 * 0.53 62.50% 47.73% (-) Despesas financeiras 0.0 0.0 0.00% 0.00% (=) Lucro Líquido * 35,000.00 * 0.53 62.50% 47.73% Menu Menu Preços Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Gleison Glienke CUSTOS.XLS: Análise de custo padrão versus real Componentes Padrão Real Diferença Q Preço ($) Custo ($) Q Preço ($) Custo ($) Q Preço ($) Custo ($) Materiais * 9.00 * 700.00 * 6,300.0000 * 10.0000 * 730.0000 * 7,300.0000 * 1.0000 * 30.0000 * 1,000.0000 MOD * 2.00 * 8.00 * 16.0000 * 1.0000 * 4.0000 * 4.0000 * (1.0000) * (4.0000) * (12.0000) CIF * 0.85 * 1.00 * 0.8500 * 0.9000 * 0.9500 * 0.8550 * 0.0500 * (0.0500) * 0.0050 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 Soma * 6,316.0000 * 7,304.0000 * 988.0000 Legenda : variações favoráveis em azul, defavoráveis em vermelho. Menu Ciclo de Vida Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Gleison Glienke CUSTOS.XLS: Formação de preços Alíquota do ICMS 18% Formar preços com lucro em % do preço de venda Alíquota do IPI 4% IPI integra a base de cálculo? (1=sim, 2=não) 1 sim Gasto dos produtos sem ICMS * 800.00 Sem substituição Base para cálculo da substituição * 8,000.00 60% * (0.04) Preço obtido: * 5,745.94 Lucro a ser computado no valor dos produtos * 3,690.67 Com substituição 60% * 0.00 Preço obtido: Sem substituição tributária * 6,151.67 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 Menu Preço Preço Matemática Financeira Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Gleison Glienke Séries Não Uniformes CUSTOS.XLS: Análise de custos do ciclo de vida - VPL e VUL Período Aquisição Analisada Parâmetros obtidos Período Fluxos Taxa 4% N Uni Duni Tê Lêlê Uni Duni Tê Lêlê 0 -800 K => 4.0% 4.0% 4.0% 4.0% VPL * 324.54 * 378.82 * 610.47 * 370.70 1 500 VPL * 488.25 0 * (800.00) * (500.00) * (300.00) * (850.00) VUL * 116.95 * 104.36 * 219.98 * 133.58 2 400 1 * 600.00 * 300.00 * 100.00 * 700.00 TIR 29% 41% 70% 32% 3 300 Fórmula : P8 =VPL(P6;M8:M11)+M7 2 * 400.00 * 400.00 * 400.00 * 400.00 4 200 3 * 200.00 * 200.00 * 500.00 * 200.00 Note que o investimento inicial 4 * 50.00 deve ser acrescentado fora da 5 fórmula do VPL. 6 7 8 Séries Não Uniformes 9 10 Data Fluxos Taxa 4% 11 1/1/99 -600 12 1/5/99 500 XVPL * 788.96 13 3/12/99 400 14 5/15/99 300 Fórmula : P21 =XVPL(P19;M20:M24;L20:L24) 15 6/16/99 200 16 17 18 19 Séries Não Uniformes 20 21 Data Fluxos 22 1/1/99 -550 23 1/5/99 140 XTIR 44.45% 24 3/12/99 150 25 5/15/99 155 Fórmula : P32 =XTIR(M31:M35;L31:L35) 26 6/16/99 160 27 28 29 30 Menu Regressão Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Gleison Glienke CUSTOS.XLS: Aplicações genéricas com auxílio da matemática financeira Coloque um "?" no valor que deseja obter. Juros Compostos Cálculos Financeiros Básicos Juros Compostos Regime N I PV PMT FV TIPO 3 3 3.0000% * 400.00 ? 1 Juros Compostos 0.00 0.00% 0.0 * (137.29) 0.0 Antec Tipo : 0 = postecipado, sem entrada Operações [f] [REG] 3 [n] 3 [i] 400 [PV] [g] [BEG] [PMT] Visor => -137,2933 1 = antecipado, com entrada na HP 12C : Equivalência de Taxas Taxa A Nper A Taxa B Nper B 8.0% 12 ? 1 151.82% 0 Contagem de dias Início Fim Dias úteis Dias corridos 1/1/01 6/5/01 12/31/99 12/31/99 0 0 Lembre-se : os feriados devem estar abastecidos. Último feriado fornecido = 12/25/02 Ok Primeiro feriado fornecido = 1/1/01 Ok Relação de feriados bancários (importante para a contagem de dias úteis). 1/1/01 1/1/02 2/26/01 2/11/02 2/27/01 2/12/02 4/13/01 3/29/02 4/21/01 4/21/02 5/1/01 5/1/02 6/14/01 5/30/02 9/7/01 9/7/02 10/12/01 12/12/02 11/2/01 11/2/02 11/15/01 11/15/02 12/25/01 12/25/02 0 0 0 4 0 N I PV PMT FV TIPO JS 1 0 0 0 Não existe 0 D 2 0 0 0 Não existe 0 JC 3 0 0 0 [f] [REG] 3 [n] 3 [i] 400 [PV] [g] [BEG] [PMT] Visor => -137,2933 0 l 3 c 4 fómula ==> [f] [REG] 3 [n] 3 [i] 400 [PV] [g] [BEG] [PMT] Visor => -137,2933 Opção : 3 Juros Compostos (Não Mexa !!!) 1 - Juros Simples 2 - Desconto Bancário 3 - Juros Compostos Cálculos Financeiros Gerais (Não Mexa !!!) 2 3 4 5 6 1 * (33.33) -33.33% 0.0 Não tem * (436.00) 2 0.0 0.00% 0.0 Não tem * (439.56) 3 0.0 0.00% 0.0 * (137.29) 0.0 Cálculos na HP 12C - Não Mexa Nunca !!! Juros N I PV PMT FV TIPO 0 0 0 3 [n] 3 [i] 400 [PV] 0 0 [g] [BEG] N 0 I 0 PV 0 PMT [f] [REG] 3 [n] 3 [i] 400 [PV] [g] [BEG] [PMT] Visor => -137,2933 FV 0 Tipo: 0 = postecipado, sem entrada, 1 = antecipado, com entrada Tipos de juros : 1 = simples, 2 = desconto comercial, 3 = compostos Número de períodos de capitalização Taxa de juros ao período. Valor presente da operação. Valor da prestação. Valor futuro da operação. Menu Autores Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Gleison Glienke CUSTOS.XLS: Análise de regressão aplicada Variável Independ. (X) Variável Depend. (Y) Para ajuste linear : Y = a + b.X Análise de regressão e correlação: gráficos. 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 0.0 0.0 0.0 0.0 Para Y : X estimado : 12 * 48.7063 0.0 0.0 0.0 0.0 Autores Custos Diagrama de Dispersão Modelo Linear Gabarito Custos Diagrama de Dispersão Modelo Logarítmico Custos Diagrama de Dispersão Modelo Polinomial Custos Diagrama de Dispersão Modelo Exponencial Custos Diagrama de Dispersão Modelo Potência Custos Diagrama de Dispersão Média Móvel (3p) Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Gleison Glienke CUSTOS.XLS: Autor da planilha. Gleison glienke E-Mail : gleisonglienke@outlook.com Menu Menu gleisonglienke@outlook.com Planilha CUSTOS.XLS Gerenciando Custos e Preços com o Excel Por Gleison Glienke CUSTOS.XLS: Descrever Menu
Compartilhar