terça-feira, 25 de fevereiro de 2014

PREVISÃO





PREVISÃO






O que é?

A função PREVISÃO estima valores a partir de duas séries de dados que sigam um modelo de dependência linear.
A regressão linear aproxima uma reta y(x) = ?x + c a um conjunto de pontos dados onde há dependência de uma variável em relação a outra. A reta é calculada de forma a minimizar a distância entre seus pontos e os valores observados.
Por exemplo, sabendo-se que há uma relação aproximadamente linear entre o número de carros circulando em uma cidade e a quantidade de monóxido de carbono lançada por estes carros na atmosfera, dados obtidos por regressão podem servir para a estimativa de quanto deste gás tóxico será emitido anualmente quando determinada quantidade de carros estiver em circulação.
A diferença entre as funções PREVISÃO e TENDÊNCIA é que TENDÊNCIA pode ser aplicada em forma matricial, de forma a calcular vários valores simultaneamente.

Sintaxe da função

=PREVISÃO(xval_conhecidos_yval_conhecidos_x)
  • X é o valor de x cujo y(x) você deseja obter;
  • Val_conhecidos_y é o intervalo de valores dependentes (os y(x));
  • Val_conhecidos_x é o intervalo de valores independentes (os x).
Neste exemplo, a secretaria do meio ambiente de uma cidade realiza uma pesquisa sobre a quantidade de monóxido de carbono despejada na atmosfera pelos carros em perímetro urbano. Estimativas da quantidade de CO na atmosfera são realizadas pela secretaria a cada mil novos carros registrados pelo departamento de tráfego; veja as últimas oito medições:
É razoável que haja uma relação linear entre a quantidade de CO emitido e o número de carros circulando pela cidade. Um gráfico dos dados acima confirma a relação existente; observe que a regressão linear produz uma reta que representa bem os pares medidos:
Usaremos a função PREVISÃO para estimar quantas toneladas de CO serão lançadas à atmosfera anualmente quando a cidade tiver 20000 carros. Observe a aplicação da função:
F12 tem o número de carros cuja emissão desejamos estimar; G4:G11 é o intervalo com as emissões para a quantidade de carros listada em F4:F11. Observe o resultado da função:

Pratique!



sábado, 22 de fevereiro de 2014

COMPARANDO AUTOMATICAMENTE O REAL COM O ORÇADO






COMPARANDO AUTOMATICAMENTE O REAL COM O 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, em 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?

Para que possamos encontrar as respostas para o respectivo problema e montar a planilha definitiva, clique: Baixar planilha

terça-feira, 18 de fevereiro de 2014

DEFININDO ÁREAS DINAMICAMENTE






DEFININDO ÁREAS DINAMICAMENTE



Introdução


No cotidiano de uso do Excel, é comum utilizamos funções que recebem intervalos de células como argumentos: por exemplo, as funções soma ou média podem trabalhar sobre intervalos para obter a soma ou a média de seus valores.
Entretanto, se a estrutura de uma planilha é alterada freqüentemente (por exemplo, porque precisamos inserir novas linhas com informações que devem ser acrescentadas ao cômputo), será freqüente chegarmos a uma situação em que as referências de fórmulas precisam ser reescritas para abrigar estes novos dados.
No exemplo para download, temos uma tabela de receitas mensais de uma empresa, e um gráfico que representa estas informações. A cada mês informações novas são inseridas na tabela, e a fonte de dados do gráfico precisa ser alterada para que ele leve em consideração os novos dados. Existe uma solução para que não precisemos atualizar os dados de entrada do gráfico quando novas informações forem inseridas no arquivo?

Mês
 Receita
01/2006
 R$                       5.568,37
02/2006
 R$                       6.357,08
03/2006
 R$                       7.255,73
04/2006
 R$                       8.029,54
05/2006
 R$                       8.714,58
06/2006
 R$                       9.255,14
07/2006
 R$                       9.955,21
08/2006
 R$                     10.804,46
09/2006
 R$                     11.303,40


Nesta planilha, elaboramos um gráfico da receita mensal de determinada empresa utilizando informações disponíveis até setembro de 2006, representadas de forma simplificada através da tabela acima. A região dos dados de origem do gráfico é definida como B4:C12. 

Suponha que a planilha seja atualizada com os resultados mais recentes todo mês, ou seja, os intervalos Bi:Ci com i > 12 serão preenchidos progressivamente ao longo do tempo. Com a região de origem dos dados do gráfico definida previamente, será necessário alterá-la sempre que inserirmos tais informações na tabela. Confira a planilha "Solução" para saber como evitar ter que refazer referências que dependem da quantidade de dados em seqüência na tabela, como na situação acima.
Clique em Baixar planilha  para ver solução

domingo, 16 de fevereiro de 2014

ANÁLISE DE VIABILIDADE DE INVESTIMENTO EM FRANQUIA - XVPL





XVPL (Valor presente líquido)






O que é?


Da mesma maneira que a função TIR está relacionada à XTIR , aqui a função XVPL está relacionada à VPL.
Como já visto no conteúdo Valor Presente Líquido, a função VPL é usada para a análise de viabilidade de investimentos, nas quais o resultado desta função indica se o retorno de um projeto de investimento é viável e pagará o investimento inicial (se resultado for positivo) ou inviável (se o retorno é menor que o investimento inicial e o resultado for negativo).
No caso da função XVPL, o que a diferencia da função VPL é que no caso da XVPL é possível calcular o valor presente líquido para o somatório de fluxos monetários que não sejam necessariamente periódicos. Ao contrário do que ocorre com a função VPL que só trabalha com a seqüência de fluxos de caixa periódicos.

Sintaxe

=XVPL(taxa; valores; datas)
Onde:
  • Taxa é a taxa de desconto a ser aplicada ao fluxo de caixa.
  • Valores é uma seqüência de fluxos de caixa que corresponde aos pagamentos nas datas estipuladas. O primeiro pagamento é opcional e corresponde a um custo que ocorre no início do investimento. No caso de o primeiro valor for um custo, este deverá ser negativo. Todos os pagamentos subsequentes são descontados com base em um ano de 365 dias. A série de valores deve conter no mínimo um valor positivo e um negativo.
  • Datas é o intervalo de datas que corresponde aos pagamentos de fluxos de capital. A primeira data marca o início do intervalo. Todas as datas subseqüentes deverão ser posteriores à primeira, mas podem estar em qualquer ordem.
Em nosso exemplo, iremos avaliar se o investimento de aquisição de uma franquia é viável. Isto será feito por meio da função de valor presente líquido, onde os fluxos de caixa de diferentes períodos é trazidos para o valor presente a partir de uma taxa de desconto; caso estes fluxos paguem o investimento inicial, o projeto cria valor para o investidor, tornando-se assim viável.
Faremos o cálculo do VPL com base na taxa de desconto de 7%.
Os fluxos de capitais estão representados no intervalo de células C3:C10 e os períodos de pagamentos correspondentes estão no intervalo B3:B10.
Para calcular o VPL a partir desta taxa, siga os passos a seguir:
  • Posicione o mouse sobre o local que será calculado a função XVPL, neste caso na célula C12;
  • Na célula C12, digite a fórmula: =XVPL(C11;C3:C10;B3:B10);
  • Tecle ENTER e confira o resultado.
Neste exemplo, o resultado retornado foi positivo, o que torna este projeto de investimento viável.

Pratique!

Exemplo

Importante: Para que essa função seja reconhecida pelo Excel, é necessário saber disponibilizar novas funções de planilha. Para isso, acesse ao menu Ferramentas, clique sobre Suplementos e ative a opção Ferramentas de Análise. Clique em OK para confirmar.

domingo, 9 de fevereiro de 2014

COMO PREVER PAGAMENTOS




COMO PREVER PAGAMENTOS








  • Suponha que você tenha que elaborar uma planilha de planejamento de pagamentos. Como determinar os valores a serem pagos na semana, mês e ano corrente destacado de uma lista de duplicatas a pagar?
  • Como proceder se você quiser saber o dia da semana em que deverá ser feito o pagamento? E se, por motivo de espaço, quiser saber o dia da semana de forma abreviada (três letras)?
  • Como fazer se você quiser destacar o dia do pagamento, além do dia da semana, quanto ao mês, dia e ano?
  • E se você quiser destacar os pagamentos da semana, mês ou ano corrente, destacando duplicata por duplicata?
  • E se você quiser saber separadamente o valor total a ser pago no ano e no próximo ano também?
  • Nesse caso existem milhares de maneiras de se estruturar uma planilha adequada para obter as informações desejadas. Vejamos uma sugestão de como isso pode ser feito.

PREVISÃO DE PAGAMENTOS
Número da
Data
Valor
Duplicata
completa
a Pagar



DP-199
04/03/2014
R$   16.626,00
DP-659
29/01/2014
R$   74.796,00
DP-154
16/05/2014
R$   99.142,00
DP-542
28/10/2013
R$   52.025,00
DP-783
09/06/2013
R$   92.010,00
DP-990
27/07/2013
R$   23.413,00
DP-894
01/07/2013
R$   26.779,00
DP-896
12/08/2013
R$   36.450,00
DP-006
10/03/2014
R$   23.712,00
DP-006
04/01/2014
R$   83.678,00
DP-897
29/08/2013
R$   71.202,00
DP-898
08/09/2013
R$   94.553,00
DP-454
08/11/2013
R$   41.932,00
DP-455
07/04/2014
R$    81.163,00
DP-456
02/02/2014
R$   53.236,00
DP-457
10/08/2013
R$   50.423,00
Total

921.140,00


Informações 
  • Suponha que você tenha que elaborar uma planinha de planejamento de pagamentos na semana, mês e ano; 
  • Para testar bem a planilha, o Excel deverá apresentar datas aleatórias (função ALEATÓRIOENTRE) entre a data de hoje (função HOJE) e a data daqui a 12 meses (função DATAM); 
  • Para explorar esse teste na planilha, o Excel deverá apresentar valores aleatórios (função ALEATÓRIOENTRE) entre R$99 e R$99.999; 
O que fazer?
  • Se eu quiser saber o dia da semana em que deverá ser feito o pagamento? E se, por motivo de espaço, quiser saber o dia da semana de forma abreviada (três letras)?
  • Se eu quiser destacar o dia do pagamento, além do dia da semana, quanto ao mês, dia e ano?
  • Se eu quiser destacar os pagamentos da semana, mês ou ano corrente, destacando duplicata por duplicata?
  • Se eu quiser saber separadamente o valor total a ser pago no ano e também no próximo ano?
Problema 
  • Como determinar os valores a serem pagos na semana, mês e ano corrente destacado de uma lista de duplicatas a pagar? 

terça-feira, 4 de fevereiro de 2014

COMO COMPARAR SEUS PREÇOS COM OS DA CONCORRÊNCIA




COMO COMPARAR SEUS PREÇOS COM OS DA CONCORRÊNCIA







Nesta planilha especial, você encontra um método para analisar uma tabela com o preço dos produtos vendidos por uma empresa através dos supermercados. Suponha que uma empresa fabrica três produtos e gostaria de acompanhar de forma constante os preços dos mesmos nos cinco principais supermercados da região. Obviamente a planilha se aplica para qualquer segmento de mercado ou preços de bens ou serviço, conforme sua conveniência.

Empresa
  • Pão de Açucar
  • Extra
  • Carrefour
  • Wal-Mart
  • Sonda
Produto
  • Sabão
  • Amaciante
  • Tira mancha
Semanalmente a empresa elabora uma tabela para acompanhamento do preço de venda cobrado por estas cinco redes nos seus três produtos:



Com base na tabela acima a empresa faz a seguinte análise:
  • O preço médio cobrado de cada produto;
  • O desvio padrão em relação a esta média;
  • O preço máximo encontrado na pesquisa;
  • O supermercado que está cobrando o preço máximo;
  • O preço mínimo encontrado na pesquisa;
  • O supermercado que está cobrando o preço mínimo.

Produto
Preço médio
Desvio padrão
Preço máximo
Supermercado
Preço mínimo
Supermercado
Sabão
 R$                   0,94
9,57%
 R$                   1,10
 Sonda
 R$                   0,85
 Extra
Amaciante
 R$                   2,80
13,04%
 R$                   3,00
 Pão de Açucar
 R$                   2,65
 Wal-Mart
Tira manchas
 R$                   4,78
23,15%
 R$                   5,10
 Carrefour
 R$                   4,50
 Sonda

    Solução
    O exemplo está simplificado. Todavia, é suficiente para mostrar com clareza como utilizar os recursos do Excel para elaborar automaticamente as informações desejadas. 

    Importante: Vamos explicar o uso dos recursos do Excel na elaboração do quadro analítico. 
  • Na coluna B temos os três produtos pesquisados: Sabão, Amaciante e Tira manchas. 
  • Na coluna C temos a informação Preço Médio. É a média aritmética simples dos preços encontrados nos supermercados. A função do Excel utilizada para gerar esta informação chama-se MÉDIA. Exemplo:Em C12 encontramos a seguinte fórmula: =MÉDIA(C5:G5). 
  • Na coluna D temos a informação desvio padrão. Dá uma idéia da dispersão dos preços cobrados nos supermercados ao redor da média. A função do Excel utilizada para gerar esta informação chama-se DESVPADP. Exemplo: Em D12 a encontramos a seguinte fórmula:=DESVPAD(C5:G5).
  • Na coluna E temos a informação Preço máximo. Informa o preço máximo encontrado na pesquisa. A fórmula do Excel utilizada para gerar esta informação chama-se MÁXIMO: Exemplo:Em E12 encontramos a seguinte fórmula:=MÁXIMO(C5:G5).
  • Na coluna F temos a informação Supermercado. Informa o supermercado que está cobrando o preço máximo encontrado na pesquisa. A função do Excel utilizada para gerar esta informação chama-se SE. Exemplo: Em F12 encontramos a seguinte fórmula: =SE(E12=C5;$C$4;SE(E12=D5;$D$4;SE(E12=E5;$E$4;SE(E12=F5;$F$4;SE(E12=G5;$G$4;FALSO))))).
  • Na coluna G temos a informação Preço Mínimo. Informa o preço mínimo encontrado na pesquisa. A função do Excel utilizada para gerar esta informação chama-se MÍNIMO:
  • Exemplo: Em G12 encontramos a seguinte fórmula:=MÍNIMO(C5:G5).
  • Na coluna H temos a informação Supermercado. Informa o supermercado que está cobrando o Preço Mínimo encontrado na pesquisa. A função do Excel utilizada para gerar esta informação chama-se SE. Em G29 a fórmula é:=SE(B19=D29;"Pão de  Açúcar";SE(C19=D29;"Extra";SE(D19=D29;"Carrefour";SE(E19=D29;"Wal-Mart";"Sonda"))))
  • Baixar planilha