quarta-feira, 20 de maio de 2015

PLANILHA PARA ANÁLISE DE SENSIBILIDADE DE INVESTIMENTO


PLANILHA PARA ANÁLISE DE SENSIBILIDADE DE INVESTIMENTO


Introdução


*Esta Planilha está disponível para download, bastando enviar solicitação através do e-mail albertolima3@gmail.com


Ao avaliar um investimento, é importante saber qual é a sua sensibilidade a variações de dados que não conhecemos ou que não podemos estimar adequadamente. No estudo de um projeto de investimento, mesmo pequenas variações dos dados previstos podem provocar grandes mudanças no VPL.

Esta planilha especial apresenta um projeto onde uma empresa tem bastante segurança na estimativa das variáveis percentual da Geração de caixa sobre a receita total, Investimento inicial e Custo do capital. Todavia, a empresa não tem segurança na estimativa de duas variáveis:Preço de venda unitário e Volume.

Consequentemente, haverá a necessidade de se projetar três cenários:
  1. Cenário Pessimista
  2. Cenário Provável
  3. Cenário Otimista

A empresa que saber o VPL de cada um dos três cenários. Todavia, gostaria que a planilha Excel atendesse algumas condições:

  • Que uma única planilha Excel mostraria rapidamente os três cenários.
  • Que a mudança de uma premissa de qualquer variável possa ser feita com bastante rapidez.
  • Que a planilha Excel gere rapidamente um quadro resumo com os três cenários escolhidos.

Para a perfeita compreensão do assunto, dos seus propósitos e de sua utilização, definimos a metodologia utilizada em 3 estágios:
  • Caracterização do Problema
  • Solução Desenvolvida
  • Prática Para Utilização
Caracterização do Problema

Na tabela acima, uma empresa está analisando um novo investimento. Foi estimado o fluxo de caixa do projeto para 3 anos em planilha Excel. A tabela possui os seguintes campos:
  • Datas
    • Ano 0
    • Ano 1
    • Ano 2
    • Ano 3
  • Dados
    • Preço de venda unitário (para os 3 anos)
    • Volume
    • Geração de caixa sobre a receita total
    • Investimento inicial:
    • Custo do capital ao ano:
    • Projeção do fluxo de caixa
    • VPL

 Problema

O problema está simplificado. Todavia, é suficiente para mostrar com clareza como utilizar o recurso do Excel chamado  de Cenários na análise de sensibilidade de um novo investimento.
  • A linha 13 é a  mais importante: “Projeção de fluxo de caixa
  • Na linha 11, a data 0 (zero) apresenta um investimento inicial de R$ 1.000,00.
  • Na data 1, 2 e 3 da linha 6 o valor é representado pela seguinte fórmula:
    • =Preço de venda unitário × Volume × Geração de caixa sobre a receita total
Solução Desenvolvida

A empresa tem bastante segurança na estimativa das variáveis percentual da Geração de caixa sobre a receita total, Investimento inicial e Custo do capital.
Todavia, a empresa elaborou três cenários para as variáveis Preço de venda unitário e Volume.   
  • Cenário Pessimista: Preço de venda unitário de R$ 8,00 com Volume projetado de 110 unidades.
  • Cenário Provável: Preço de venda unitário de R$ 10,00 com Volume projetado de 100 unidades.
  • Cenário Otimista: Preço de venda unitário de R$ 12,00 com Volume projetado de 90 unidades.                                                                                                                                  
As variáveis Geração de caixa sobre a receita, Investimento inicial e Custo do capital permanecerão constantes nos três cenários.
A empresa que saber o VPL de cada um dos três cenários. Todavia, gostaria que a planilha Excel atendesse algumas condições:
  • Que uma única planilha Excel mostraria rapidamente os três cenários.
  • Que a mudança de uma premissa de qualquer variável possa ser feita com bastante rapidez.
  • Que a planilha Excel gere rapidamente um quadro resumo com os três cenários escolhidos.         

Recomendação: deixe no seu campo visual as linhas 9 à 17 .
Importante: observe que as células abaixo em vermelho serão as variáveis do cenário.



Para criar os cenários citados, faça o seguinte;
  • Acesse o menu Ferramentas;
  • Clique em Cenários.
Nesta janela analise os três cenários criados selecionando cada um deles e clicando no botão Mostrar, observe as células variáveis e o VPL.


Prática Para Utilização

Recomendação: deixe no seu campo visual as linhas 9 à 17 e, paralelamente, faça o treinamento tendo ao seu lado o texto impresso desta planilha.
Importante: observe que as células abaixo em vermelho serão as variáveis do cenário.




Para criar os cenários citados, faça o seguinte;
  • Acesse o menu Ferramentas;
  • Clique em Cenários.
Vai aparecer a figura (caixa de diálogo) abaixo chamada de Gerenciador de cenários, veja:
  • Repare que nenhum cenário foi definido, para isso clique no botão Adicionar;
O primeiro cenário que desejamos definir é o Pessimista. Para isso preencha os campo como o problema pede:
  • Nome do cenário: “Pessimista
  • Células variáveis: “D11:D12

Observação: você poderia escolher, se quisesse, mais de duas células para variar.                                  
  • O próximo passo é clicar o botão OK
Veja que aparece automaticamente o número 10 para o Preço (D11) e 100 para o Volume. Isto acontece porque são estes os números que estão na tela no momento.                                                 
Todavia, não são os números correspondentes ao cenário Pessimista. Os números do cenário Pessimista são:
  • Preço(D11): “8
  • Volume (D12): “110
  • Clique em OK para prosseguir,

A janela Gerenciador de cenários agora aparece mostrando que já foi incluído o cenário Pessimista. Além disso, as células D11 e D12 já estão selecionadas no campo Células variáveis.
Agora, vamos recomeçar o processo clicando o botão Adicionar, para criarmos o cenário Provável, veja na figura abaixo:

  • Após inserir o nome Provável clique em OK;
No campo Preço (D11) aparece o número 10 e no campo Volume (D12) aparece o número 100. Este números aparecem automaticamente pois são aqueles que estão preenchidos no momento nas células D11 e D12. Por coincidência, são os números verdadeiros do cenário Provável. Vamos clicar no botão OK ,


A figura Gerenciador de cenários agora aparece mostrando que já foram incluídos os cenários Pessimista e Provável. Além disso, as células D11 e D12 já estão selecionadas no campo Células variáveis.
Agora, vamos recomeçar o processo clicando o botão Adicionar, para criar o último cenário, o Otimista
  • Após inserir o Nome do cenárioOtimista”, clique em OK;        

No campo Preço (D11) aparece o número 10 e no campo Volume (D12) aparece o número 100. Este números aparecem automaticamente pois são aqueles que estão preenchidos no momento nas células D11 e D12.
Todavia, não são os números correspondentes ao cenário Otimista. Os números do cenário Otimista são:
  • Preço (D11): “12
  • Volume (D12): “90
  • A janela Gerenciador de cenários agora aparece mostrando que já foram incluídos os cenários: Pessimista, Provável e Otimista. Antes de testar o recurso Cenários, vamos clicar o ícone Fechar.
Para testar o recurso Cenários, vamos colocar em nosso campo visual esta planilha Excel entre as linhas 09 e 17. Agora, vamos executar o menu Ferramentas/Cenários.
  • Quando a figura aparecer na tela, desloque-a para um canto da tela onde você possa enxergar o VPL na linha 17.                                                                                                  
Coloque o cursor no cenário o qual você deseja conhecer o VPL encontrado. Agora, clique no botão Mostrar que você observará os seguinte resultados:

Pessimista      R$ 205,54
Provável          R$ 369,94
Otimista          R$ 479,53
                                                                    
Boa Sorte


sábado, 9 de maio de 2015

PLANILHA PARA OTIMIZAÇÃO DE INVESTIMENTOS




PLANILHA PARA OTIMIZAÇÃO DE INVESTIMENTOS


INTRODUÇÃO


Nesta planilha especial, você encontra um método de otimização de investimentos envolvendo o Solver. Suponha que você pode investir em seis projetos distintos, independentes e com VPLs positivos; contudo, há uma restrição de capital que impede que todos sejam aprovados.

Como investir neste caso? Desejamos gastar o máximo possível do capital disponível, obtendo a maior soma de VPLs. Este problema pode ser resolvido facilmente através da aplicação do Solver. 

CENÁRIO - ORIENTAÇÕES GERAIS 

Informações

  • Temos 6 projetos sendo analisados;
  • Todos os projetos são independentes. Portanto, todos eles podem ser aprovados simultaneamente;
  • Todos os projetos têm VPL (valor presente líquido) positivo.
Como proceder

  • Se a empresa dispuser de R$ 245.000 de capital, deverá aprovar todos os 6 projetos;
  • Se a empresa dispuser de apenas R$ 50.000 de capital, deverá selecionar o projeto A, que demanda R$ 45.000 de investimento e projeta um VPL de R$ 5.000. O projeto E também apresenta um VPL de R$ 5.000, mas demanda um investimento de R$ 50.000. Os projetos B e D juntos demandam R$ 50.000 de investimento, mas projetam um VPL consolidado de R$ 4.500. Em resumo: considerando a restrição de capital de R$ 50.000, a combinação de projetos que maximiza o VPL é {A};
  • Se a empresa dispuser de apenas R$ 100.000 de capital, deverá selecionar os projetos A e E, que demandam R$ 95.000 de investimento e projetam no consolidado um VPL de R$ 10.000. Escolha qualquer combinação de projetos que respeite a restrição de capital de R$ 100.000, e você verá que nenhuma delas projetará um VPL consolidado superior a R$ 10.000. Em resumo: respeitando a restrição de R$ 100.000 de capital, a combinação de projetos que maximiza o VPL é {A, E};
  • Se a empresa dispuser de apenas R$ 150.000 de capital, deverá selecionar os projetos A, B, D e E, que demandam R$ 145.000 de investimento e projetam um VPL consolidado de R$ 14.500. Escolha qualquer combinação de projetos que respeite a restrição de capital de R$ 150.000, e você verá que nenhuma delas projetará um VPL consolidado superior a R$ 14.500. Em resumo: respeitando a restrição de R$ 150.000 de capital, a combinação de projetos que maximiza o VPL é {A, B, D, E}.

Problema                           

  • Como automatizar estes procedimentos que permitem encontrar a combinação de projetos que maximize o VPL, utilizando o recurso Solver do Excel?

INSTRUÇÕES DE MONTAGEM DA PLANILHA




  • A tabela foi estendida com três colunas à direita;
  • A coluna E admite os valores 1 e 0, que indicam se o investimento é realizado ou não;
  • As colunas F e G fornecem, respectivamente, o investimento e o VPL de cada projeto se ele é aceito (ou seja, se o valor da coluna E na linha correspondente é 1). Altere os valores na coluna E para conferir as mudanças na tabela.

Ajuste do Solver

* Advertência: Na versão do Excel 2013 a Ferramenta SOLVER deve estar disponível na Guia DADOS, caso contrário você deve ir em Arquivo, Opções e selecionar "Suplementos", vai aparecer uma Caixa para você selecionar a Ferramenta SOLVER, ativando-a ao clicar OK.

  • Clique no menu Ferramentas, e em seguida no item Solver. Observe a janela que se abrirá:
  • As células variáveis são o intervalo na coluna E, que determinam a decisão de investir ou não no projeto. Desejamos maximizar o VPL dos projetos selecionados (G12 com a condição Máx. A soma dos investimentos selecionados deve ser menor que o capital disponível, ou seja, F12 < D3. A decisão de investir ou não no projeto é binária, e por isso criamos a restrição E6:E11 = binário;
  • ATENÇÃO - Essas orientações precisam ser atualizadas em função das alterações que podem ter ocorrido pelas edições de versões recentes do Excel.

Solução

  • Para obter a solução, clique no botão Resolver. Em seguida, clique em OK novamente;
  • Ao aceitar a solução do Solver, você obterá o resultado. Devemos aceitar os projetos A e E, pois esta é a combinação que maximiza o VPL respeitando a restrição de capital.

* A Planilha pronta para uso está disponível para download que poderá ser solicitada através do e-mail albertolima3@gmail.com

Boa Sorte

domingo, 3 de maio de 2015

PLANILHA PARA ATUALIZAÇÃO AUTOMÁTICA DE CONTRATOS







PLANILHA PARA ATUALIZAÇÃO AUTOMÁTICA DE CONTRATOS


*Esta planilha, pronta para uso, está disponível para download, que poderá ser solicitado através do e-mail: albertolima3@gmail.com


Introdução

Como é possível apresentar valores contratuais reajustados através de índices financeiros acumulados desde a data da contratação?

Como isso pode ser feito automaticamente, utilizando números índices em faixa de períodos diferentes sem a necessidade de ajustes constantes nas fórmulas?

Suponha que você tenha que apresentar o reajuste de alguns contratos, todos eles pela taxa mensal SELIC mais 1% ao mês . Para facilitar, vamos supor que todos os contratos utilizam essa metodologia.

Como fazer para importar dados externos de um site que tenha o histórico do índice requisitado (ex.:www.calculos.com.br), ou qualquer outro, conforme cada contrato?

Isso, e muito mais, é o que veremos nessa Planilha Especial .


Informações 

  • Suponha que você tenha que apresentar o reajuste de alguns contratos, todos eles pela taxa mensal SELIC mais 1% ao mês. Para facilitar, vamos supor que todos os contratos utilizam essa metodologia. 
  • Considere que os ajustes são calculados a partir do mês subseqüente ao mês da assinatura do contrato até o mês anterior do mês atual; 
  • Considere também que deve ser apresentado em destaque a taxa acumulada mais o adicional mensal até o mês anterior do mês atual; 
  • Deve ser apresentado também o mês e anos em que deve ser iniciado o reajuste e o mês atual de atualizado já no primeiro dia do mês; 
  • Para facilitar os nossos cálculos considere que todos os contratos foram firmados sempre no primeiro dia de cada mês; 

O que fazer?

  • Para calcular a taxa acumulada desde a data de contratação até o mês atual, mas de forma automática?
  • Para importar dados externos de um site que tenha o histórico do índice requisitado (ex.: www.calculos.com.br) ou qualquer outro, de acordo com cada contrato?
  • Para apresentar automaticamente a data de início e fim dos períodos em que deverá ocorrer o reajuste do contrato?
  • Para estruturar uma forma de busca do mês subseqüente ao mês do contrato para iniciar os reajustes?
  • Para apresentar a taxa acumulada desde a sua criação até a data atual?
  • Para apresentar a taxa acumulada anual?

Problema 


  1. Como é possível apresentar valores contratuais reajustados através de índices financeiros acumulados desde a data da contratação?
  2. Como isso pode ser feito automaticamente, utilizando números índices em faixa de períodos diferentes sem a necessidade de ajustes constantes nas fórmulas?

Instruções para a montagem da Planilha



§  Posicione o cursor em M20 e faça uma busca na Internet para trazer o histórico de índices financeiros utilizando a opção do seu menu do Excel de Dados/Importar dados Externos. Sugerimos nesse exemplo a utilização da Taxa Mensal SELIC, onde você pode encontrar, por exemplo no site www.calculos.com.br ou qualquer outro, onde você pode trazer o endereço exato da tabela à partir da versão XP do Excel ou mais recente, clicando com o botão direito do mouse e depois na opção Propriedades para apresentar o endereço exato da tabela que você quer buscar para a sua planilha;
§  Depois que a tabela original do site estiver na sua planilha, será necessário inverter a seqüência da tabela para que você possa acumular os percentuais em uma seqüência lógica, iniciando na menor data (que no caso é fevereiro de 95) até a última taxa. Para isso, será necessário ordenar a tabela através da opção Dados/Classificar primeiro por ano e depois por mês, lembrando que na segunda etapa você deve alterar em Opções para mês por extenso. Assim iniciaremos a nossa tabela em fevereiro de 95 até o mês e ano atual; Alertamos que deve ficar atento para efetuar as necessárias correções provocadas pelas mudanças de versões do Excel;
§  Como os valores dos índices estarão estruturados como números puros e não como percentual, para que possamos efetuar os cálculos com os índices, aconselhamos estruturar uma fórmula simples na coluna P para buscar o valor do índice na coluna O e depois dividir por 100 para transformar em número decimal. Posteriormente  você deverá formatar como número percentual;
§  Na coluna Q você deve estruturar uma função SE, que vai testar se tiver um índice percentual na coluna P deverá então adicionar mais 1%, para atender a política dos contratos sugeridos;
§  Na coluna R você deverá apresentar o número percentual índice ajustado, multiplicando através de número índice o percentual da SELIC com o 1% de adicional. Obs: Entende-se como Número Índice o número percentual mais 1 (um). No final do cálculo retira-se esse número um que serviu como auxílio para o nosso cálculo;
§  Para acumular o índice no ano, utilizamos o mesmo procedimento na coluna S. Isso será utilizado apenas para efeito de comparação dos índices anuais. Para facilitar os nossos cálculos, utilizamos na coluna S a função VFPLANO.
§  Na coluna L você deverá criar um índice para acumular a taxa na periodicidade desejada, conforme cada contrato, onde são utilizadas as funções PROCV e DATA, com referência ao mês vigente de cada índice localizado na coluna N, onde deverá ser formatado como mês e ano. Essa informação é estruturada com base no mês apresentado pela tabela original (que buscamos na Internet) e a Tabela Auxiliar desenvolvida em N4;

§  A coluna K apresenta apenas a taxa SELIC acumulada desde fevereiro de 1995;

§  Com base no índice acumulado no período do reajuste apresentado na coluna E, na coluna F vamos apresentar o valor monetário original reajustado desde a data da contratação até a data do mês anteriormente ao atual, conforme anteriormente comentado;
§  Nas células que vão de G8 até G13 vamos apresentar o mês subseqüente ao mês da contratação (necessário para início dos cálculos dos reajustes), utilizando a função FIMMÊS para apresentar o fim do mês da data da contratação e, posteriormente, somando mais 1 (um) para que apresente sempre o primeiro dia do próximo mês necessário para o reajuste acordado, conforme comentado;
§  O mesmo procedimento deverá ser adotado na faixa de células de H8 até H13, considerando que o final do mês atual eu posso buscar através da função HOJE que trás a informação do próprio sistema;
§  Lembre-se sempre de atualizar a planilha quanto necessário, que nesse caso deverá ocorrer a partir de maio/07. Caso contrário a sua tabela apresentará um erro de não disponível (N/D), por não encontrar a data do mês vigente.

Conclusão


  • É importante sempre manter os valores contratuais sempre atualizados, não só para que sejam adequadamente contabilizados, mas também para que sejam controlados.  E isso pode ser facilmente organizado pelo Excel, mas é importante analisar as características de cada contrato e buscar sempre índices financeiros atualizado, preferencialmente de forma automática, para evitar a digitação desnecessária e , consequentemente erro indesejáveis.