Consolidação de dados esparsos, análise de sensibilidade e automação no Google Sheets
Foco total na segunda aula
1) Dados Esparsos
Base de fluxo de caixa em datas irregulares para consolidação anual.
2) Tabela Dinâmica
Agrupar por ano e obter fluxo líquido anual do projeto.
3) VPL
Calcular VPL com taxa de desconto e fluxo anual consolidado.
4) Sensibilidade
VPL em grade de cenários (taxa e variação de receita).
5) Macro no Google Sheets
Automatizar o cálculo do VPL em 1 clique.
Materiais para praticar automação no Google Sheets
Do dado bruto ao VPL automatizado
Ao final, a turma será capaz de:
- Consolidar dados esparsos com Tabela Dinâmica.
- Calcular VPL com fluxo anual consolidado.
- Executar análise de sensibilidade simples e objetiva.
- Criar macro no Google Sheets para recálculo de VPL.
Importante: Hoje teremos 3 exercícios dinâmicos.
Aba: Fluxo_Detalhado
Estrutura sugerida: Data, Projeto, Tipo, Valor
| Data | Projeto | Tipo | Valor |
|---|---|---|---|
| 15/02/2024 | A | Investimento | -120000 |
| 20/05/2024 | A | Receita | 18000 |
| 10/09/2024 | A | Custo | -7000 |
| 25/01/2025 | A | Receita | 26000 |
| 13/06/2025 | A | Custo | -9000 |
| 02/11/2025 | A | Receita | 32000 |
| 08/03/2026 | A | Receita | 38000 |
| 19/08/2026 | A | Custo | -11000 |
| 11/12/2026 | A | Receita | 42000 |
| 22/04/2027 | A | Receita | 50000 |
| 14/10/2027 | A | Custo | -13000 |
| 05/12/2027 | A | Receita | 54000 |
Consolidar por ano para preparar o VPL
- Selecione a aba
Fluxo_Detalhadocompleta. - Menu: Inserir → Tabela dinâmica (nova aba).
- Linhas: Data (agrupar por ano).
- Valores: Soma de Valor.
- Filtro (opcional): Projeto = A.
Exemplo: 2024, 2025, 2026, 2027 em linhas separadas.
Aba: VPL
B1: taxa de desconto (ex.: 0,12)
C2: fluxo inicial (ano 0, negativo)
C3:C6: fluxos anuais futuros consolidados
B3: resultado do VPL
=VPL(B1;C3:C6)+C2
Checagem didática: confirmar sinal do investimento inicial e periodicidade anual.
Monte uma grade de 15 cenários
Base: use os fluxos anuais consolidados da Tabela Dinâmica.
Variações:
- Taxa: 8%, 10%, 12%, 14%, 16%
- Receita: -10%, 0%, +10%
Entregas:
- Tabela com os 15 VPLs.
- Melhor e pior cenário.
- Faixa de taxa em que o VPL fica negativo.
- Conclusão de viabilidade.
Entendendo o comportamento do projeto
Comparação de Projetos (Ponto de Fisher)
- Eixo X: Diferentes taxas de desconto testadas.
- Eixo Y: O VPL de cada projeto.
- Cada curva representa um projeto diferente. Todas caem à medida que a taxa aumenta.
- O ponto onde as retas se cruzam é a Taxa de Fisher: a taxa exata na qual dois projetos têm o mesmo VPL.
- Abaixo do ponto de Fisher, um projeto é melhor; acima dele, a vantagem se inverte (decisão de alocação de capital).
Curvas que se cruzam mostram projetos mutuamente excludentes.
No contexto do exercício:
O melhor cenário desloca a curva "para cima", aumentando a TIR e o VPL para qualquer taxa dada. O pior cenário puxa a curva para baixo, tornando o projeto inexequível em taxas menores.
Como decidir com base nos perfis de VPL?
A metodologia Stage-Gate de Robert G. Cooper divide projetos em fases (Stages) separados por portões de decisão (Gates). Nos portões, decide-se por Go/Kill/Hold.
Gate 1 & 2: Viabilidade Inicial
Estimativas grosseiras. O VPL é analisado sob muitos cenários para ver se justifica avançar para o Business Case.
Gate 3: Business Case (Go to Development)
A decisão crucial: Aqui o perfil de VPL e a Sensibilidade (exercício anterior) comandam. Se a curva do VPL for frágil (VPL cai negativamente com pequenas mudanças de taxa), o projeto pode receber um Kill.
Gates 4 & 5: Testing e Launch
VPL é revisado com custos reais. Projetos já em andamento só são mortos se o perfil de VPL despencar drasticamente (ex: custo explodiu).
Calculando o Cruzamento de Projetos
Você precisa analisar dois projetos excludentes (A e B) para a diretoria. Use os dados base abaixo.
Missão:
- No Google Sheets, monte uma tabela listando taxas de desconto de 0% até 16% (saltando de 2 em 2%).
- Calcule o VPL do Projeto A e do Projeto B para cada linha de taxa.
- Faça o Gráfico de Linha mostrando as curvas dos dois projetos juntas.
- Encontre a TIR aproximada (ou exata) de cada projeto.
- Descubra a Taxa de Fisher visualmente no gráfico (o ponto onde os VPLs são iguais).
Decisão Escrita: Responda na planilha: Se a taxa da empresa for 5%, qual projeto escolhemos? E se for 13%?
Desafio de automação
- Criar macro chamada
calcularVPLProjeto. - Ler taxa, fluxo inicial e fluxos futuros.
- Escrever o VPL na célula de saída.
- Aplicar formato de moeda no resultado.
Critério de sucesso: macro funcional e recálculo sem editar fórmula manualmente.
Resumo da aula
- Dados esparsos foram consolidados via Tabela Dinâmica.
- VPL foi calculado com fluxo anual consolidado.
- Sensibilidade foi aplicada em grade de cenários.
- Macro no Google Sheets automatizou o cálculo.
Entregas da aula: 3 exercícios e uma macro de presente na planilha.