segunda-feira, 29 de julho de 2013

CENÁRIOS




CENÁRIOS







O que é?


A ferramenta Cenários tem como objetivo principal salvar valores no Excel, de modo que estes podem ser substituídos de acordo com previsões feitas por você.
Mais de um grupo de valores podem ser criados e salvos de modo que você possa elaborar um relatório que reflete as variações do seu modelo para cada situação distinta.
A rigor, um cenário é somente um registro de células e valores que deverão ser alterados na sua planilha. Ao aplicarmos o cenário, os valores das células serão atualizados automaticamente e a planilha será recalculada.

Em que situações devemos aplicar a ferramenta Cenários?

Basicamente, usamos esta ferramenta quando, por exemplo, queremos fazer projeções financeiras de como uma organização reagirá perante a uma expectativa de um aumento de gastos no próximo mês, ou de um aumento de índices, vendas e etc.

Exemplo

Suponha que o dono de uma barraca de sorvetes deseja criar diferentes cenários para as vendas que irão ocorrer no verão.
Os meses a serem estudados serão os de outubro,novembro e dezembro.
O preço a ser pago por uma unidade de sorvete é R$0,80.
Feita a tabela, insira as fórmulas necessárias para que a inclusão dos valores ausentes em outubro,novembro e dezembro causem impacto na Receita bruta.
Primeiramente, na célula referente ao Total de vendas no ano utilize a fórmula de soma selecionando todos os valores de B4 até B15.
Por fim, na célula referente a Receita bruta multiplique o valor de Total de vendas no ano pelo Preço unitário.
Agora começaremos, de fato, a trabalhar com cenários.
  • Vá em ferramentas e clique em Cenários.
  • Já que você estará criando seu primeiro cenário, clique em adicionar.
  • Dê um nome para seu cenário, como por exemplo, "Previsível".
  • Logo em seguida, selecione as células referentes à Unidades Vendidas dos meses de Outubro, Novembro e Dezembro no campo Células variáveis, ou seja, o intervalo de B13:B15.
  • Clique em Ok.
Nesta janela faça o seguinte:
  • Insira os valores de Unidades Vendidas que você prevê que aconteça em um cenários previsível.
  • Após inserir os valores clique em Ok.
Uma janela de Gerenciador de Cenários se abrirá, fazendo com que você possa trabalhar com algumas opções oferecidas por ela.
A seguir, falaremos de algumas das mais utéis opções que podem vir a ser usadas por você:
  • Mostrar: quando você clica nesta opção, aparecerá em sua planília de Excel os resultados da Receita bruta no final do ano.
  • Adicionar: ao clicar nesta opção, você poderá elaborar mais cenários para diferentes perspectivas suas de futuro.
Neste caso, criarei também os cenários "Otimista" e "Pessimista".
  • Resumir: ao selecionar esta opção, você encontra duas possíveis saídas. "Resumo do cenário" e "Relatório da tabela dinâmica do cenário".
No primeiro, você cria um relatório de valores atuais das células e suas variáveis de resultado.
No segundo, você cria uma tabela dinâmica referente aos valores dos cenários existentes.

Pratique!

sexta-feira, 26 de julho de 2013

FUNÇÃO CRESCIMENTO



Função Crescimento



O que é?

Após ajustar um modelo de regressão para um conjunto de dados, podemos prever o valor médio de uma variável dependente de outra. Isto pode ser feito através do uso da função TENDÊNCIA do Excel, que devolve valores em uma tendência linear a partir de um conjunto dado de pontos.
Entretanto, em muitos casos o relacionamento entre as varáveis é não linear. Nos casos em que o relacionamento entre as variáveis não é linear, a função CRESCIMENTO do Excel poderá fornecer uma linha de ajuste mais apropriada. Em especial, a função CRESCIMENTO é utilizada quando os valores seguem uma tendência exponencial; a curva de ajuste será calculada de forma a minimizar a distância entre seus pontos.

Sintaxe da função CRESCIMENTO

=CRESCIMENTO(val_conhecidos_yval_conhecidos_xnovos_valores_xconstante)
  • Val_conhecidos_y são valores de y que você já conhece na relação y(x) = b·mx;
  • Val_conhecidos_x são valores de x que você já conhece na relação y(x) = b·mx. Para cada valor de x há um valor de y correspondente de acordo com a relação acima;
  • Novo_x é o novo valor de x para o qual você deseja que a função CRESCIMENTO devolva um valor y(x)correspondente;
  • Constante é um valor lógico que força a constante b a se igualar a um. Este valor é opcional e definido por padrão como verdadeiro. Caso o valor utilizado seja falso, a constante será igual a um, de forma a obtermos a relação y(x) = mx, ou seja, a exponencial passará obrigatoriamente pelo ponto (0; 1).

Exemplo

Suponha que o gerente de vendas de uma loja quer prever o retorno de uma campanha de marketing através de link patrocinado na internet. O portal escolhido fornece uma tabela de acessos em função do tempo de exposição na internet. A quantidade de acessos, de acordo com a experiência do portal que oferece o serviço, é estimada conforme a tabela abaixo:
O departamento de vendas da loja deseja estimar o número de acessos após 40 horas de exposição do link patrocinado no portal. Usaremos a função CRESCIMENTO para realizar esta previsão. Antes de continuarmos, contudo, observe um gráfico da curva ajustada para os pontos acima:
A estimativa para o número de acessos após 40 horas corresponde ao ponto desta curva, ou seja, é dada pelo valor de y(40). Para estimar qual é a curva apropriada, contudo, a função CRESCIMENTO precisa dos pares (horas; acessos). Siga os passos abaixo para realizar a previsão:
  • Abra o assistente de função;
  • Selecione a categoria estatística;
  • Selecione a função CRESCIMENTO;
  • Na janela de argumentos da função, forneça os argumentos adequados. A primeira série de dados é H4:H11, a segunda, G4:G11 e o valor de x é G12. Observe o preenchimento dos campos:
Encerre o assistente clicando no botão OK. Observe a fórmula gerada:
Veja também o resultado da função, que indica o número estimado de acessos após quarenta horas de exposição do link:

Pratique!

quinta-feira, 25 de julho de 2013

CRIANDO UM GRÁFICO DINÂMICO



Criando um gráfico dinâmico






O que é?
A criação de tabelas dinâmicas é um excelente recurso para movimentar dinamicamente as informações de linhas e colunas dentro de uma tabela, permitindo a exibição de campos selecionados, o cruzamento de dados, a aplicação de filtros e a realização de cálculos matemáticos e estatísticos, como por exemplo, a soma, o produto ou o desvio padrão em campos previamente determinados.
Além de todas essas opções disponíveis, quando criamos um relatório de tabela dinâmica também podemos gerar gráficos dinâmicos, que seguem a mesma lógica da tabela, podendo ser dinamicamente alterados de acordo com as necessidades do usuário. Veja no exemplo a seguir, como usar este recurso.

Exemplo

Tomando como exemplo a planilha do conteúdo Criação de Tabela Dinâmica,  postado em 23/07/2013,vamos aprender como criar gráficos dinâmicos.
  • Com a planilha aberta, clique com o botão direito do mouse sobre a tabela dinâmica.
  • Outra maneira de acessar o recurso de gráfico dinâmico, é acessando o menu Inserir, e escolher a opçãoGráfico.
  • Feito isso, automaticamente os dados dispostos na tabela dinâmica serão convertidos para o formato de um gráfico.
  • Em nosso exemplo, a tabela exibe a quantidade, o valor e o tipo de produto vendido por cada vendedor. Para saber, por exemplo, qual foi o vendedor que vendeu "scanners", selecione o campo Produto e clique sobre este produto.
  • Clique em OK para confirmar, e verá que apenas o Paulo vendeu este produto.
  • Agora, caso queira saber qual foram os vendedores que venderam mais de dez peças, independentes do produto, acione o campo Quantidade e selecione as opções 10, 11 e 15, e depois selecione no campo Produto a opção (Mostrar tudo):
  • Clique em OK para confirmar e verá que apenas Pedro e Antônio conseguiram vender a quantidade de produtos especificada.
Depois de termos conhecido os recursos de filtragem e manipulação de dados de um gráfico dinâmico, se desejar alterar este gráfico, personalizando-o de acordo com o seu gosto pessoal, é só clicar com o botão direito do mouse sobre o gráfico e escolher as opções Formatar área de plotagemTipo de Gráfico ou Opções de gráfico.

Pratique!

O procedimento de personalização dos atributos do gráfico dinâmico é que o mesmo que o usado para a formatação de gráficos convencionais não-dinâmicos.

terça-feira, 23 de julho de 2013

CRIAÇÃO DE TABELA DINÂMICA





Criação de tabela dinâmica




O que é?
É uma ferramenta interativa e muito poderosa para análise de dados e tomada de decisões, cruzando informações de maneira rápida e flexível.
Através dela você pode resumir centenas de informações de forma clara e objetiva e assim facilitar a análise dessas informações, gerando rapidamente novas tabelas onde serão analisadas de várias formas diferentes.
Podemos dizer que o recurso Tabela dinâmica, como o próprio nome diz, torna o trabalho de análise de dados muito mais dinâmico e prático.

Como aplicar

Siga os passo abaixo para saber como utilizar a Tabela dinâmica:
  • Posicione o cursor numa região dentro da tabela onde deveremos implantar a Tabela Dinâmica;
  • Clique em Dados no menu do Excel;
  • Em seguida procure e clique em Relatório de Tabela e Gráficos Dinâmicos;
Abrirá uma janela a qual lhe mostrará como seguir o passo-a-passo, conforme demonstrado no exemplo abaixo:
Etapa 1 - Informação do tipo de base de dados
  • Nesta primeira tela indique se os dados estão na planilha ou virão de uma fonte externa (no caso, selecionaremos a primeira opção) e escolha criar somente uma Tabela Dinâmica. A seguir clique em Avançar:
Etapa 2 - Informação do local da origem da base de dados
Esta é a etapa em que você deverá selecionar a origem dos dados:
  • Se o seu cursor já estiver dentro da planilha ele já vai considerar que esta é a planilha que deverá trabalhar com a Tabela Dinâmica, considerando a primeira linha como cabeçalho;
  • Se a origem for outro arquivo Excel ou outro banco de dados externo, clique em Procurar e localize o arquivo;
  • Logo em seguida clique em Avançar.
Etapa 3 - Informação de onde será inserida a tabela dinâmica
  • Escolha se você quer criar a tabela dinâmica na planilha atual ou em uma nova planilha;
  • Depois clique no batão Layout localizado na parte de baixo da tela. No Layout você irá definir as áreas e os campos que a sua tabela dinâmica terá.
Etapa 4 - Informação de como deverá ser o Layout da tabela dinâmica
Observe que a tela do Layout tem dois conjuntos:
Abaixo você tem a tabela dinâmica que permite fazer diversas análises, cruzando informações e dispondo os itens conforme desejado:
  • Conjunto de Dados ou Campos, neste caso (VendedorProdutoQuantidade e Valor);
  • O diagrama das áreas da tabela possui quatro campos para distribuir as informações de diversas formas, da melhor forma possível, com o objetivo de propiciar diversos tipos de análises (PáginaColunaLinha e Dados). É nessa área que é feita a escolha e o posicionamento dos campos que irão compor a tabela dinâmica;
  • Para posicionar os campos nas devidas áreas, basta clicar sobre o campo (lado direito da tela) e manter o botão do mouse pressionado e em seguinda arrastar para a área desejada no centro da janela, na posição desejada (parte branca central da tela);
  • Continuamos, portanto, com a operação de clicar e arrastar os campos para as suas respectivas áreas, conforme desejado, podendo avançar e retroceder com o mouse até obter a estrutura desejada.
  • Após posicionado todos os campos desejados, clique em OK e retorne a tela anterior.
  • Clique em Concluir e você terá um resultado parcial da sua tabela dinâmica, conforme demonstrado abaixo.
  • Caso deseje, oculte as linhas de subtotais que considerar desnecessárias para a análise dessa planilha e faça um ajuste no formato dos números. Assim você deverá ter a figura abaixo como resultado final.

Pratique!

sábado, 20 de julho de 2013

ANÁLISE DE VIABILIDADE FINANCEIRA - FUNÇÃO XVPL (Valor Presente Líquido)




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.

quinta-feira, 18 de julho de 2013

FUNÇÃO DEPRECIAÇÃO



Depreciação (DPD)


O que é?


A função DPD calcula a depreciação de um ativo, dado seu valor de compra, resíduo e vida útil. Depreciação é um conceito utilizado em finanças associado à perda de valor de um bem ao longo do seu ciclo de vida: ele é utilizado principalmente em balanços patrimoniais para evitar que os valores declarados de bens duráveis, como por exemplo ativos fixos, fiquem além dos seus valores reais, levando em consideração seu desgaste ou obsolescência.
DPD calcula a depreciação linear do ativo em questão. Se você estiver montando um balanço, será necessário consultar as Normas Brasileiras de Contabilidade quanto a períodos, prazos e modelos de depreciação, e valores residuais regulados para cada tipo de ativo.

Como aplicar a função

Use a seguinte fórmula:
=DPD(custo; valor_residual; vida_útil)
  • Custo é o valor de compra do ativo;
  • Valor_residual é o valor do ativo no fim da sua vida útil (geralmente zero, e em outros casos seu valor de venda, deduzidos os custos);
  • Vida_útil é o número de períodos sobre o qual o ativo sofrerá depreciação; em vários casos, é o número de períodos de uso do ativo.

Exemplo

No exemplo a seguir, temos uma pequena tabela de bens e desejamos calcular a depreciação de cada um. Observe a planilha:
A coluna C contém o custo dos itens; a coluna D contém seus valores residuais, e E, a vida útil em meses. Para calcular a depreciação, use a seguinte fórmula:
Veja o resultado aplicado a todos os bens:

Pratique!

terça-feira, 16 de julho de 2013

ANALISANDO O PONTO DE EQUILÍBRIO DE UM PRODUTO




ANALISANDO O PONTO DE EQUILÍBRIO DE UM PRODUTO




Definimos o ponto de equilíbrio de um produto como o valor que iguala a receita obtida na venda deste produto com o seu custo total de produção. O cálculo do ponto de equilíbrio fornece um instrumento para tomada rápida de decisão quando temos disponíveis seu preço de venda, custo fixo e variável: por exemplo, a expectativa de venda de um produto pode ajudar a definir se sua produção é viável ou não (caso seja maior que o volume produzido no ponto de equilíbrio), ou podemos fazer variar o preço de venda para realizar uma análise de sensibilidade.


Esta planilha especial realiza a análise do ponto de equilíbrio com algumas informações extras. Por exemplo, temos um valor de lucro mínimo estabelecido para que o produto seja considerado viável, e a aplicação do recurso Tabela para avaliar diferentes cenários de ponto de equilíbrio para mudanças no custo fixo e variável. Um gráfico também foi elaborado para representar o modelo de receita, custo e lucro, e indicar a linha de corte para o lucro mínimo esperado.


Suponha que uma empresa pretende estudar a viabilidade de um de seus produtos através de uma análise do volume de vendas necessário para cobrir seu custo de produção. Chamamos o volume de vendas que iguala a receita aos custos de ponto de equilíbrio deste produto. 

DADOS
Preço de venda
 R$                  8,00
Custo variável unitário
 R$                  4,00
Custo fixo
 R$           1.600,00
Lucro mínimo exigido
 R$           5.000,00


Dados


Preço de venda
 R$                  8,00


Custo variável unitário
 R$                  4,00


Custo fixo
 R$           1.600,00


Lucro mínimo exigido
 R$           5.000,00


Margem de contribuição
 R$                  4,00
= C4 - C5

Ponto de equilíbrio
                   400,00
= C6 / (C4 - C5)
Receita no p.e.
 R$           3.200,00
= C9 * C4

Ponto de equilíbrio com lucro
               1.650,00
= (C6 + C7) / (C4 - C5)
Receita no p.e. com lucro
 R$         13.200,00
= C11 * C4





Nas tabela acima, dispomos das informações necessárias para o cálculo do ponto de equilíbrio de determinado produto, bem como o lucro mínimo exigido para que sua venda seja viável.

Confira a planilha "Solução" para aprender a calcular e analisar seu ponto de equilíbrio. Baixar planilha