quinta-feira, 29 de novembro de 2012

COMO PROJETAR VENDAS COM REGRESSÃO MÚLTIPLA



COMO PROJETAR VENDAS COM REGRESSÃO LINEAR MÚLTIPLA

Muitas vezes buscamos uma forma mais técnica para projetar a receita de vendas através de modelos estatísticos mais adequados.

Se a empresa entende que exista uma alta correlação entre o aumento de receita das vendas com os gastos com propaganda e também o incentivo através de comissão de vendas, estaremos então diante de uma Regressão Linear Múltipla, pois a Receita de Vendas é influenciada por mais de uma variável "controlada" .

Nesse caso é possível utilizar tanto a função PROJ.LIN (que pode dar informações estatísticas mais detalhadas) ou mesmo a função TENDÊNCIA (que é utilizada quanto já tenho certeza do auto grau de correlação entre as variáveis).

Vejamos então como projetar a receita de vendas para esse mês, tendo como correlação duas variáveis que, a princípio, influenciam no resultado de receita de vendas, que, através da estimativas são determinadas pelos gastos com propaganda e também pela comissão de vendas.

A tabela seguinte será a referência para a planilha definitiva do nosso exemplo de hoje:




























Informações
§  A tabela acima demonstra a evolução das receitas de uma empresa ao longo dos últimos 20 meses;
§  A evolução anual das receitas apresenta uma relação razoável com a evolução dos Gastos com Propaganda e Comissão de Vendas simultaneamente;
§  Isso pode caracterizar uma alta relação entre as variáveis dependentes (Vendas (Y)) e as variáveis independentes (Gastos com Propaganda (M1) e Comissão de Vendas (M2)) que devemos confirmar através da correlação do r2, que pode ser calculado através da expansão da função matricial do PROJ.LIN e também da função TENDÊNCIA do Excel para determinar esse grau de correlação.
§  Nesse caso temos um problema que envolve a regressão linear múltipla, pois vendas estão relacionadas com duas variáveis, que nesse caso são Gastos com Propaganda e Comissão de Vendas.  Se fosse relacionada a mais apenas uma variável, essa seria considerada como regressão linear simples.
§  Não aconselhamos que a variável dependente (X1) seja incluída na tabela como percentual, pois nesse caso o valor da fórmula retornará como incorreta. Aconselhamos que transforme em número absoluto, conforme demonstrado na coluna D da tabela.
§  Nesse caso é possível utilizar tanto a função PROJ.LIN (que pode dar informações estatísticas mais detalhadas) ou mesmo a função TENDÊNCIA (que é utilizada quando já tenho certeza do auto grau de correlação).
§  Importante: Quanto maior for a série histórica, melhor será a qualidade da reta de projetada.
O que fazer?
§  Se a empresa precisa projetar a receita de vendas de uma forma estatística mais técnica;
§  Se a empresa entende que exista uma alta correlação entre o aumento das vendas, os gastos com propaganda e o incentivo através de comissão de vendas;
Problema                         
  • Como projetar a receita de vendas para esse mês, tendo como correlação duas variáveis que, a princípio, influenciam no resultado do aumento de receita de vendas, que, segundo estimativas, deveremos determinar os gastos com propaganda nem $95 mil e  comissão de vendas em 2,5%?
  Solução
  • Para que possamos ter acesso às informações que nos permitirão montar a planilha e obter as respostas para nosso problemas, clique : Baixe a planilha para praticar.
Boa Sorte

terça-feira, 27 de novembro de 2012

COMPARANDO AUTOMATICAMENTE O REAL X ORÇADO




COMPARANDO AUTOMATICAMENTE O REAL X ORÇADO


Como é possível automatizar relatórios gerenciais de controle Real x Orçado de Entradas e Desembolso de Caixa com o uso de Macros gravadas para facilitar e agilizar o processo de análise?

O que fazer para acionar essas macros através de um botão de Formulário do Excel;

Para completar o Relatório mensal Real x Orçado com contas que vão além das Entradas e Desembolso de Caixa, como:
  • Nível desejado de Caixa;
  • Empréstimos a Captar;
  • Aplicações no Mercado Financeiro;
  • Amortização de Empréstimos;
  • Resgate de Aplicação Financeira.
Vejamos como tudo isso pode ser feito de forma simples e criativa.











Informações 
  • Essa planilha apresenta valores para análise comparativa de ENTRADAS e DESEMBOLSO de Caixa, confrontando o REAL x ORÇADO; 
  • É preciso buscar uma forma rápida de visualização adequada dos valores, sendo hora REAL hora ORÇADO e, am alguns momentos REAL x ORÇADO; 
  • É preciso apresentar a disponibilidade de caixa, para que possa ser re-planejado o futuro programado o futuro da empresa; 
O que fazer? 
  • Para visualizar de forma rápida os valores de Desembolsos e Entradas de Caixa? 
  • Para criar uma macro gravada para apresentar hora o Real, hora o Orçado e hora o Real x Orçado; 
  • Para acionar essas macros através de um botão de Formulário do Excel; 
  • Para completar o Relatório mensal Real x Orçado com contas que vão além das Entradas e Desembolso de Caixa, como: 
Nível desejado de Caixa; 
Empréstimos a Captar; 
Aplicações no Mercado Financeiro; 
Amortização de Empréstimos; 
Resgate de Aplicação Financeira. 
Problema
  • Como é possível automatizar relatórios gerenciais de controle Real x Orçado de Entradas e Desembolso de Caixa com o uso de Macros gravadas para facilitar e agilizar o processo de análise?
Solução
BOA SORTE

sábado, 24 de novembro de 2012

PLANILHA PARA CÁLCULO DO LEASING




Como Estruturar uma Planilha para Cálculo do Leasing



Essa planilha especial apresenta um exemplo hipotético de cálculo de leasing de um bem qualquer. Portanto como estruturar uma planilha para cálculo da parcela mensal do Leasing ?
  • Como calcular o valor da prestação mensal?
  • Como calcular o valor da contra prestação mensal?
  • Como calcular o VRG mensal?
  • Como calcular os juros até a data de aniversário do contrato e também até o final do mês?
  • Como colocar a palavra “meses” junto com o número de prestações e de vida útil do bem, sem afetar o cálculo?
Enfim, é presiso analisar todas as formas de financiamento detalhado, como uma planilha para cálculo do Leasing.

Informações 
  • Essa planilha apresenta um exemplo hipotético de cálculo de leasing de um bem qualquer; 
  • Vamos considerar o valor do VRG (Valor Residual Garantido) do bem aproximadamente 64% do valor total do mesmo; 
  • Vamos considerar também R$520,00 , aproximadamente 3%, de tarifa a ser financiada; 
  • A vida útil do bem é de 42 meses, mas ele será financiado em 24 meses; 
  • O juros a apropriar será de aproximadamente 2,20% enquanto que os juros da parcela será de aproximadamente 2,23%; 
O que fazer
  • Para calcular o valor da prestação mensal? 
  • Para calcular o valor da contra prestação mensal? 
  • Para calcular o VRG mensal? 
  • Para calcular os juros até a data de aniversário do contrato e também até o final do mês? 
  • Para colocar a palavra “meses” junto com o número de prestações e de vida útil do bem, sem afetar o cálculo?
Problema 
  • Como estruturar uma planilha para o cálculo da parcela mensal do Leasing ?
Solução
BOA SORTE

quinta-feira, 22 de novembro de 2012

DEPRECIAÇÃO - FUNÇÃO AMORLINC



FUNÇÃO FINANCEIRA - AMORLINC





Hoje aprenderemos a calcular o valor da depreciação de alguns ativos, utilizando a função AMORLINC, pertencente ao grupo de funções financeiras do excel, cujo objetivo é retornar a depreciação para cada período contábil. Esta função é fornecida para o sistema contábil francês e é normalmente utilizada no sistema financeiro brasileiro.
Se um ativo for adquirido no meio do período contábil, a depreciação pro rata deverá ser considerada.

Sintaxe

=AMORLINC(custo; data_aquisição; primeiro_período; recuperação; período_tot; taxa_deprec; ano_base)
Onde:
  • custo é o custo do ativo;
  • data_aquisição é a data de compra do ativo aplicado;
  • primeiro_período é a data final do primeiro período;
  • recuperação é o valor de recuperação no final da vida útil do ativo;
  • período_tot é o período;
  • taxa_deprec é a taxa de depreciação;
  • ano_base é o ano-base a ser adotado.

Observações

1-) As datas devem ser inseridas com a função DATA ou como resultado de outras fórmulas ou funções. Por exemplo, use DATA(2007,6,1) para 1 de junho de 2007. Se as datas forem inseridas como texto surgirão problemas. O Excel armazena datas como números de série seqüenciais padrão para que eles possam ser usados em cálculos. Por padrão, 1° de janeiro de 1900 é o número de série 1 e 1° de janeiro de 2008 é o número de série 39448 porque está 39.448 dias após 1° de janeiro de 1900.
2-) Números padrão para o uso de ano_base:
  • Zero ou omitido - 360 dias (Método NASD);
  • 1 - Real;
  • 3 - 365 dias em um ano;
  • 4 - 360 dias em um ano (método da Europa).
No exemplo abaixo, desejamos calcular a depreciação de itens como EdifíciosMóveis e Utensílios eAutomóveis. Vejamos como aplicar usando a função AMORLINC.

Exemplo

Aplicando a função:
=AMORLINC(B$4; B$5; B$6; B$7; B$10; B$8; B$9), temos o seguinte resultado:
Com base no exemplo mostrado acima, podemos notar a eficiência e a grande utilidade da função AMORLINC no dia-a-dia de profissionais da área de finanças. Se imaginarmos uma situação onde há a necessidade de trabalhar com, por exemplo, diversos períodos dentro de uma mesma planilha, veremos o papel importante que essa função desempenha em determinadas situações.

Pratique!


sexta-feira, 16 de novembro de 2012

COMO CALCULAR E ADMINISTRAR A DESPESA DE DEPRECIAÇÃO




COMO CALCULAR E ADMINISTRAR A DESPESA DE DEPRECIAÇÃO


  • Como calcular e administrar despesas de depreciação em períodos fracionados?
  • Como acompanhar a depreciação acumulada sem que ela ultrapasse o valor total do bem imobilizado?
  • O que fazer para calcular e administrar corretamente a despesa de depreciação com diferentes valores, taxa de depreciação e vida útil?
  • O que fazer para que o cálculo da despesa de depreciação acumulada não supere o custo original do bem pelo método de linha reta?
  • Como facilitar o cálculo da despesa de depreciação sem o uso de uma função lógica (SE), sem o uso de uma função aninhada complexa (uma função dentro da outra), em especial para períodos fracionados?
  • Para encontrar uma função específica no Excel para calcular e administrar a despesa de depreciação sem que ela supere (quando acumulada) o custo original do bem?
Esses e outros detalhes é o que veremos nessa planilha especial.


Informações

  • Suponha que você queira calcular e controlar a despesa de depreciação de diferentes bens imobilizados;
  • O bem foi adquirido hoje e assim deve de ser registrado junto à contabilidade. Isso é apenas um artifício para que possamos forçar o cálculo da despesa de depreciação fracionada.
  • O final desse ano será o primeiro ano fracionado para cálculo da despesa de depreciação;
  • A taxa de depreciação de vida útil do bem adequada a cada tipo de bem imobilizado;
  • Para efeito do cálculo, considere o ano com 365 dias em um ano.
  • Para esse exemplo não iremos considerar valores de recuperação para efeito do nosso cálculo.

O que fazer?

§  Para calcular e administrar corretamente a despesa de depreciação com diferentes valores, taxa de depreciação e vida útil?
§  Para que o cálculo da despesa de depreciação acumulada não supere o custo original do bem pelo método de linha reta?
§   Facilitar o cálculo da despesa de depreciação sem o uso de uma função lógica (SE), sem o uso de uma função aninhada complexa, em especial para períodos fracionados?
§  Para encontrar uma função específica no Excel para calcular e administrar a despesa de depreciação sem que ela supere (quando acumulada) o custo original do bem?

Problema                        

  • Como calcular e administrar despesas de depreciação em períodos fracionados?
  • Como acompanhar a depreciação acumulada sem que ela ultrapasse o valor total do bem imobilizado?
Solução
BOA SORTE

terça-feira, 13 de novembro de 2012

TAXA INTERNA DE RETORNO MODIFICADA





Taxa Interna de Retorno Modificada (MTIR)


O que é?

TIR modificada (ou MTIR) é uma forma alterada da taxa interna de retorno e procura corrigir problemas relacionados à diferença de taxas reais de financiamento dos investimentos (despesas com valores negativos) e de aplicação de caixa excedente (receitas com valores positivos) existente no cálculo da TIR.
A principal finalidade da MTIR é estabelecer o retorno de um investimento que contemple a aplicação dos fluxos excedentes por uma taxa de aplicação e os déficits de fluxos por uma taxa de captação.

Sintaxe da função MTIR

=MTIR(valorestaxa_financiamentotaxa_reinvestimento)
  • Valores é um intervalo que contenha os fluxos de caixa do investimento. Estes números representam uma série de pagamentos (valores negativos) e recebimentos (valores positivos) que ocorrem em períodos regulares. Os valores devem conter pelo menos um valor positivo e um negativo para calcular a taxa interna de retorno modificada.
    Observação: Se não houver ao menos um valor negativo neste intervalo, MTIR devolverá o valor de erro #DIV/0!; este erro ocorre porque a fórmula da MTIR envolve um quociente cujo denominador está relacionado aos pagamentos;
  • Taxa_financiamento é a taxa de juros paga sobre o dinheiro usado nos fluxos de caixa;
  • Taxa_reinvestimento é a taxa de juros recebida nos fluxos de caixa ao reinvesti-los. Geralmente, esta taxa representa o custo de capital do projeto.
Observação: Se você tentar utilizar a função MTIR e ela não for encontrada, será necessário ativar o suplemento de ferramentas de análise, através do item suplementos do menu ferramentas. Para instalar este recurso, o CD do Microsoft Office pode ser necessário.

Exemplo

No exemplo a seguir, calcularemos a MTIR de um projeto onde, além do investimento inicial, há um gasto complementar ao final do primeiro período. Observe a planilha:
Projeto de investimento; sua análise de viabilidade será feita através da MTIR.
Neste exemplo, a taxa de captação (financiamento) é de 15% ao período, e a de aplicação (reinvestimento), 10% ao período. Não podemos utilizar a TIR neste caso porque a taxa de reinvestimento ao longo do ciclo de vida do projeto não é a mesma da taxa de financiamento. Siga os passos abaixo para calcular a MTIR:
  • Abra o assistente de função;
  • Selecione a categoria financeira;
  • Selecione a função MTIR;
  • Aplique os argumentos da função e dê OK quando terminar. Observe a janela do assistente com os dados preenchidos:
Assistente de função para a função MTIR.
Veja a fórmula da MTIR na célula G16:
Fórmula da função MTIR.
Observe o resultado da função:


Resultado da função MTIR.

sábado, 10 de novembro de 2012

COMO ESTRUTURAR PLANOS DE CONTAS SINTÉTICOS E ANALÍTICOS



COMO ESTRUTURAR PLANOS DE CONTAS SINTÉTICOS E ANALÍTICOS

Como construir um Plano de Contas lógico e sequencial para o Balanço Patrimonial Analítico que sirva também para o Balanço Patrimonial Sintético?

Como evitar erros de digitação e inconsitência entre o analítico e o sintético?

A probabilidade de erro de digitação no número de conta e das respectivas descrições e valores e muito alto e deve se evitar ao máximo a re-digitação desses tipos de dados para que não ocorram erros, pois os mesmos dados e valores do demonstrativo analítico deverão ser os do demonstrativo sintético;

Vejamos como isso pode ser estruturado.



Informações 
  • Suponha que você tenha que estruturar uma planilha de Plano de Contas para o Balanço Patrimonial; 
  • Digamos que você tenha que criar esse Plano de Contas que sirva tanto para o Balanço Patrimonial Analítico (utilizando os dados acima) como para o Balanço Patrimonial Sintético; 
  • A probabilidade de erro de digitação no número de conta e das respectivas descrições e valores e muito alto e deve se evitar ao máximo a re-digitação desses tipos de dados para que não ocorram erros, pois os mesmos dados e valores do demonstrativo analítico deverão ser os do demonstrativo sintético; 
O que fazer? 
  • Para construir um Plano de Contas lógico e seqüencial? 
  • Para utilizar uma Formatação Personalizada especificamente criada para o Plano de Contas? 
  • Para evitar re-digitação do Plano de Contas para a construção de demonstrativos financeiros com o grau diferenciado de detalhamento? 
  • Para que o Excel busque do demonstrativo analítico a descrição e os valores da conta desejada? 
  • Para não apresentar erros de disponibilidade, caso não seja localizada a conta requisitada? 
Problema 
  • Com base no Balanço Patrimonial Analítico apresentado acima, construa um Balanço Patrimonial Sintético, apresentado as principais contas do Plano de Contas com “chave” para buscar a descrição e os respectivos valores da mesma.
Solução
  • Para obter as informações que irão ajudá-los a solucionar o problema acima e montar a respectiva planilha, clique no link abaixo:
  • Baixe a planilha para praticar.
BOA SORTE

terça-feira, 6 de novembro de 2012

COMO AGENDAR PAGAMENTOS SÓ PARA DIAS ÚTEIS



COMO AGENDAR PAGAMENTOS SÓ PARA DIAS ÚTEIS

Nem sempre a data de face dos pagamentos de boletos, duplicatas e demais débitos caem em dias úteis no mês, o que fazer para não considerar essas datas?

Para melhor trabalhar o capital da empresa disponível em caixa é permitido efetuar o pagamento no primeiro dia útil após as datas de vencimentos que caem em finais de semana e/ou feriados programados, exceção feita para pagamentos de impostos e tributos que aconselhamos que sejam efetuado, pelo menos, no dia útil imediatamente anterior a data de vencimentos que eventualmente caem em dias de finais de semana e/ou feriados.

Vamos simular a melhor data útil para efetuar o nosso pagamento, otimizando a nossa planilha com outras funções importantes.


PREVISÃO DE PAGAMENTOS
Documento
Valor
Data Vcto.
Data Pgto
Dia da Semana
Dif. Datas
D-444
154,50
01/01/2012
02/01/2012
seg
1
E-052
54,54
25/01/2012
26/01/2012
qui
1
E-059
117,00
21/04/2012
23/04/2012
seg
2
E-952
978,00
01/05/2012
02/05/2012
qua
1
D-151
155,00
25/05/2012
25/05/2012
sex
-
D-151
348,75
20/06/2012
20/06/2012
qua
-
D-450
4.557,78
05/08/2012
06/08/2012
seg
1
D-400
12.247,00
07/09/2012
10/09/2012
seg
3
D-400
27.555,75
12/10/2012
15/10/2012
seg
3
D-478
900,00
12/11/2012
12/11/2012
seg
-
E-052
4.000,00
13/11/2012
13/11/2012
ter
-
E-454
4.852,00
15/11/2012
16/11/2012
sex
1
E-454
10.917,00
01/12/2012
03/12/2012
seg
2
E-059
52,00
05/12/2012
05/12/2012
qua
-
D-454
701,50
12/12/2012
12/12/2012
qua
-
E-168
7.682,00
25/12/2012
26/12/2012
qua
1
Total
75.272,82
16
16

Relação de Feriados
Dia da Semana
Confraternização universal
01/01/2012
dom
Aniversário da Cidade
25/01/2012
qua
Carnaval
20/02/2012
seg
Paixão de Cristo
06/04/2012
sex
Tiradentes
21/04/2012
sáb
Dia do Trabalho
01/05/2012
ter
Corpus Christi
07/06/2012
qui
Independência do Brasil
07/09/2012
sex
N. S. Aparecida
12/10/2012
sex
Finados
02/11/2012
sex
Programação da República
15/11/2012
qui
Natal
25/12/2012
ter

Informações
  • Temos programado dezesseis pagamentos em diferentes datas no ano; 
  • São definidos doze feriados oficiais para esse ano; 
  • Para melhor organização do fluxo de caixa, os pagamentos não devem ser programados nos feriados e finais de semana, podendo ser postergado para o próximo dia útil, exceção feita para pagamentos e impostos e tributos que precisam ser antecipados da data de vencimento que ocasionalmente caem em feriados e dias da semana, pois nesse caso será cobrado multa e juros. 
O que fazer? 
  • Se a data de vencimento do pagamento cai no final de semana? Qual o dia exato para determinar o pagamento, considerando dias úteis?
  • Se a data de vencimento do pagamento cai em uma data programada de feriado? Qual o dia exato para determinar o pagamento, considerando dias úteis? 
  • Se for necessário postergar alguns dias do pagamento para melhorar a performance do fluxo de caixa e efetuar os pagamentos sem multa ou juros? 
  • Como determinar os pagamentos somente em dias úteis? 
  • Como apresentar o dia da semana em que o pagamento será efetuado?
Problema 
  • Com determinar a data de pagamento de débitos que eventualmente tenham sido programados para dias que caem no final de semana ou em dias de feriados? 
Solução
  • Para que possam tem acesso às informações para encontrar as respostas para o problema e montar a respectiva planilha, clique no link abaixo:

Boa Sorte