domingo, 26 de julho de 2015

PLANILHA PARA MUDAR O IDIOMA DO BALANÇO PATRIMONIAL










PLANILHA PARA MUDAR O IDIOMA DO BALANÇO PATRIMONIAL



 *Esta planilha pronta para uso está disponível para download no e-mail: albertolima3@gmail.com

Introdução



Como estruturar a mudança automática do idioma do Balanço Patrimonial sem comprometer o tamanho do seu demonstrativo e evitar erros e constantes ajustes no layout e na forma de apresentação?
Suponha que você tenha que estruturar uma planilha de Balanço Patrimonial e tenha que trocar constantemente o idioma dependendo da pessoal que precisa ler e analisar esse demonstrativo;
Digamos que essa troca tenha que ser feita de uma forma automática entre o idioma Português e o Inglês;
Se você desenvolver uma nova planilha espelho em uma mesma planilha, mas com idiomas diferentes ela poderá duplicar o tamanho do seu arquivo e a probabilidade de ocorrer erros nas fórmulas, formatações e forma de apresentações serão constantes. Isso poderá duplicar o seu trabalho no uso do Excel.
Vejamos como tudo isso pode ser solucionado, a partir da planilha abaixo:
                                                                                                                    


















A partir dos dados da planilha aprenderemos hoje o que fazer para:

  • Trocar o idioma do Balanço Patrimonial e outros demonstrativos entre o Português e Inglês de forma automática;
  • Criar uma “Planilha Espelho”;
  • Incluir e excluir linha no Balanço Patrimonial sem que comprometa a estrutura original desse demonstrativo;
  • Destacar em vermelho os percentuais negativos da análise vertical de cálculo de proporcionalidade entre cada conta do Balanço Patrimonial e o Total do Ativo e Passivo, respectivamente; 
  • E principalmente, como estruturar a mudança automática do idioma do Balanço Patrimonial sem comprometer o tamanho do seu demonstrativo e evitar erros e constantes ajustes no layout e na forma de apresentação;

Instruções e Orientações para a montagem da Planilha

  • Estruture uma nova planilha ao lado do seu Balanço Patrimonial onde você deve ter os nomes das contas nas linguagem desejadas; 
  • Certifique-se de que todas as contas em Português tem a sua conta correspondente em Inglês e que todas estão alinhadas;  
  • Importante: o alinhamento (mesma linha para cada conta) é essencial para a inclusão ou exclusão de linhas (contas) sem comprometer a estrutura necessária da sua planilha;  
  • Estruture em E7 (por exemplo) um Formulário de Caixa de Combinação, onde deverá se referenciar a área específica para a escolha da linguagem, onde colocamos em I5 para Português e I6 para Inglês;  
  • Em uma das células em que você queira trocar o idioma de forma automática no Balanço Patrimonial, como, por exemplo em A14 estruture um fórmula usando a função SE para automatizar a troca do idioma: =SE(Solução!$E$7=1;Solução!H14;Solução!J14) 
  • Agora copie para as demais células em que você deseja que seja trocado o idioma; 
  • Atenção: Cuidado com as referências absolutas e relativas, pois a Planilha Espelho não tem a mesma estrutura de colunas que a o Balanço Patrimonial (número de colunas) que deverá ser apresentada;  
  • Importante: Se você quiser você poderá Proteger a Planilha para que ninguém veja esse seu truque através dessa fórmula, podendo colocar em outra pasta de trabalho e utilizar a Proteção de Pasta de Trabalho; 
  • Para finalizar e melhorar a visualização de percentuais negativos destacados em vermelho, utilize a opção de Formatação Condicional para valores menores do que zero, conforme demonstrado abaixo, pois a formatação padrão de percentual não disponibiliza a opção de apresentar valores negativos em vermelho.
  • Em alguns casos é necessário que você tenha que apresentar os seus demonstrativos financeiros, como o Balanço Patrimonial, em mais de uma língua, porém podendo manter ou não a mesma moeda. Isso facilita a elaboração e apresentação da sua planilha e reduz o tamanho da mesma.
Boa Sorte

domingo, 12 de julho de 2015

PLANILHA PARA PROJETAR VENDAS USANDO REGRESSÃO LINEAR





PLANILHA PARA PROJETAR VENDAS USANDO REGRESSÃO LINEAR

(Esta planilha pronta está disponível para download através do e-mail albertolima3@gmail.com)


Introdução


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 pelosgastos com propaganda e também pela comissão de vendas.


Projeção Utilizando Funções Estatísticas(Projeção com 2 Variáveis)

Utilizando as Funções PROJ.LIN e TENDENCIA 

































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 temos 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;


Qual o objetivo?                         

  • 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%?

ORIENTAÇÕES PARA A MONTAGEM E UTILIZAÇÃO DA PLANILHA



  1. Nessa planilha especial você pode calcular o valor de projeção de receita de vendas de duas formas:
    1. Utilizando a equação da reta (com o uso da função PROJ.LIN); ou
    2. Utilizando a função TENDÊNCIA.
  2. Calcule o r2 (Grau de Confiança da Reta), M1, M2 e a constante B, através da expansão matricial da função PROJ.LIN do Excel, utilizando os seguintes passos:
    1. Determine o coeficiente angular (M) através da função PROJ.LIN. O resultado da fórmula será, nesse caso, M2.
    2. Mantenha o cursor posicionado no resultado do PROJ.LIN (M2).
    3. Expanda o cursor de uma para três colunas e de uma para quatro linhas, utilizando o seu próprio mouse ou segurando a tecla Shift do teclado e expandido o cursor com as teclas de setas do seu teclado;
    4. Solte tudo simultaneamente;
    5. Aperte e em seguida solte a tecla F2 no canto superior esquerdo do seu teclado para editar a função PROJ.LIN;
    6. Aperte simultaneamente as teclas na seguinte seqüência: Crtl, Shift e Enter;
    7. Para finalizar, solte tudo simultaneamente;
    8. Você terá como resultado uma pequena tabela como apresentado acima na célula J10.
  3. Com a expansão da função PROJ.LIN de forma matricial é possível obter e analisar diversos dados, principalmente para estruturar a sua equação da reta para projeção linear múltipla;
  4. Verifique o posicionamento das informações mais relevantes para a estruturação da sua fórmula para calcular a projeção de receita de vendas com o uso do PROJ.LIN;
  5. Você deve ter atenção dobrada para estruturar a sua nova equação da reta, onde serão necessárias as seguintes informações:
    1. Y = (M1.X1)+(M2.X2)+B
    2. Importante: Verifique corretamente o posicionamento de cada variável para estruturar a sua fórmula!
  6. Com a estruturação da fórmula e referência as respectivas células você terá como resultado o valor projetado de venda com o uso da função PROJ.LIN e um grau de confiança de correlação dos dados históricos de 62,35%.
  7. Agora, para efeito de comparação, calcule o valor de projeção de vendas utilizando a função TENDÊNCIA.
  8. Conclusão: considerando a correlação dos dados históricos de Gastos com Propaganda, % de Comissão de Vendas e a Receita de Vendas, para um Gasto com Propaganda de $95 mil reais e a determinação do novo % de comissão de vendas estipulado em 2,5%, teremos um valor de receia de venda de R$1.000,44 mil, com uma probabilidade de acerto de 62,35%, conforme determinado pelo r2.




Boa Sorte

sexta-feira, 3 de julho de 2015

PLANILHA PARA PROJEÇÃO E AJUSTE DAS VENDAS










PLANILHA PARA PROJEÇÃO E AJUSTE DA RECEITA DE VENDAS


A Planilha pronta para download poderá ser solicitada pelo e-mail
albertolima3@gmail.com


Introdução

Hoje vamos aprender como montar uma planilha de projeção das receitas de vendas mais automática, evidenciando eventuais ajustes nos preços e respectivos volumes e variações cambiais projetadas.

Elaborar a projeção de receitas de venda envolve a atenção de diversos detalhes como a necessidade ajustes e atualização de dados no futuro quando não são estruturados de forma automática.

Quando os ajustes de uma planilha são feitos manualmente, a probabilidade de erros e “esquecimentos” é muito maior, o que aumenta na mesma proporção a probabilidade de erros no resultado final e consequentemente na análise e, o que é pior, na tomada de decisão.

Na elaboração de uma planilha muitos tentam “encurtar” o trabalho fazendo ajustes direto na fórmula de uma ou mais células, o que provoca um processo demorado na busca de eventuais diferenças para localizar o “porquê” os valores não apresentam um resultado final desejado, principalmente quando mais de uma pessoa mexe na mesma planilha com objetivos diferentes.

Vejamos como isso pode ser feito de forma prática!

O que fazer?

  • Se datas projetadas não são automáticas e há sempre a necessidade de ajustes;
  • Se não são evidenciados os eventuais ajustes no tempo do Preço de Venda e Volume por produto assim como a Variação Cambial para os produtos exportados?;
  • Se eu quiser deixar automática a periodicidade das projeções, dependendo de interesses internos na empresa e necessidade de detalhamento, podendo mudar a periodicidade em diversas formas como anual, mensal, trimestral, etc.

Considere a Planilha abaixo e siga as orientações para sua montagem:




Montagem da planilha

§  Em B6 (Data da elaboração das projeções) você pode utilizar a função HOJE para automatizar a data.
§  Já em B7 (Data base) aconselhemos o uso da função FIMMÊS para apresentar a data final do mês, aninhada com a função HOJE, completando o campo meses em “-1 para considerar o mês exatamente anterior ao mês em análise para que seja instituído como base.
§  Em F7 você deverá exibir uma “Barra de Formulários” seguindo a seqüência de Exibir/Barra de Ferramentas/Formulários.
§  Nesse caso sugerimos a escolha do formulário “Controle Giratório” colocando os limites de Valor mínimo de 1, Valor máximo de 12, Alteração Incremental de 1, e Vínculo da Célula em $E$7 para apresentar o valor.
Pronto! Agora será possível vocês estipular de forma automática a periodicidade da sua projeção, podendo ser mensal, trimestral, anual ou outra forma mais adequado às suas necessidades.

§  Em B10 você deverá referencial com a célula B7 para manter a mesma base de partida.
§  Já à partir de C10 você deverá incluir a fórmula FIMMÊS em cada célula para considerar sempre a data final do mês, estipulando a célula anterior como Data_inicial e a célula E7 como meses.  O mesmo deverá ser feito para as células seqüenciais.
§  Acima da linha do Preço de Venda, você deverá incluir uma linha para apresentar as prováveis Variações do Preço de Venda em suas projeções que dever apresentar uma formatação %.
No primeiro preço projetado, você deverá considerar o preço anterior e multiplicar pela a variação do período, ou seja: Preço de Venda Anterior * (1+Variação do Preço de Venda no Período). Isso ajustará o preço ou manterá o mesmo se a variação for zero.
§  O mesmo procedimento deverá ser incluído para Volume do Produto A, Câmbio (Mercado Externo), Preço de Venda do Produto B e Volume do Produto B, que deverão ser ajustados pelas suas respectivas variações (cada item deverá ter a sua própria variação);

Conclusões

  • Dessa forma as variações serão muito mais automáticas as principais variáveis de uma Receita de Venda estarão evidenciadas e poderão ser ajustadas facilmente dependendo do interesse e expectativa futuras.
  • O mesmo se diz quanto a necessidade de ajustes rápidos quanto a periodicidade das projeções quanto a ser mensal, trimestral, anual, etc.

Boa Sorte