Modelagem Financeira em Excel (Guia Passo a Passo Grátis + Modelo)

A modelagem financeira no Excel é o processo de construção de um modelo financeiro para representar uma transação, operação, fusão, aquisição, informação financeira para analisar como uma mudança em uma variável pode afetar o retorno final, a fim de tomar uma decisão sobre um ou mais dos transações financeiras acima mencionadas.

O que é modelagem financeira no Excel?

A Modelagem Financeira no Excel está em toda a web e muito tem sido escrito sobre o aprendizado da Modelagem Financeira, entretanto, a maioria das peças de treinamento de modelagem financeira são exatamente as mesmas. Isso vai além do jargão usual e explora a modelagem financeira prática usada por banqueiros de investimento e analistas de pesquisa.

Neste Guia do Excel de modelagem financeira gratuita, pegarei um exemplo do Colgate Palmolive e prepararei um modelo financeiro totalmente integrado do zero.

Este guia tem mais de 6.000 palavras e levei 3 semanas para ser concluído. Salve esta página para referência futura e não se esqueça de compartilhá-la :-)

MAIS IMPORTANTE - Baixe o modelo do Excel de modelagem financeira da Colgate para seguir as instruções

Baixe o modelo do modelo financeiro da Colgate

Aprenda modelagem financeira passo a passo no Excel

Treinamento de modelagem financeira em Excel - leia-me primeiro

Etapa 1 - Baixe o modelo do modelo financeiro da Colgate. Você usará este modelo para o tutorial

Baixe o modelo financeiro da Colgate

Etapa 2 - Observe que você receberá dois modelos - 1) Modelo Financeiro Colgate Palmolive Não Resolvido 2) Modelo Financeiro Colgate Palmolive Resolvido

Etapa 3 - Você trabalhará no modelo de modelo financeiro não resolvido da Colgate Palmolive . Siga as instruções passo a passo para preparar um modelo financeiro totalmente integrado.

Etapa 4 - Aprendizagem feliz!

Índice

Eu criei um índice fácil de navegar para você fazer esta Modelagem Financeira

# 1 - Modelo Financeiro da Colgate - Histórico
# 2 - Análise de proporção de Colgate Palmolive
# 3 - Projetando a declaração de renda
# 4- Cronograma do Capital de Giro
# 5 - Cronograma de depreciação
# 6 - Cronograma de Amortização
# 7 - Outro cronograma de longo prazo
# 8 - Preenchendo a declaração de renda
# 9 - Tabela de patrimônio líquido
# 10 - Cronograma de Ações Pendentes
# 11 - Completando as Demonstrações de Fluxo de Caixa
# 12- Cronograma de dívida e juros recomendado
Curso de Modelagem Financeira
Modelos Financeiros Gratuitos

Se você é novo em Modelagem Financeira, dê uma olhada neste guia sobre O que é Modelagem Financeira?

Como construir um modelo financeiro no Excel?

Vejamos como um modelo financeiro é construído a partir do zero. Este guia detalhado de modelagem financeira fornecerá um guia passo a passo para a criação de um modelo financeiro. A principal abordagem adotada neste guia de modelagem financeira é modular. A abordagem modular significa essencialmente que construímos demonstrações básicas como Demonstração de Renda, Balanço e Fluxos de Caixa usando diferentes módulos / programações. O foco principal é preparar cada instrução passo a passo e conectar todas as programações de suporte às instruções principais na conclusão. Posso entender que isso pode não estar claro agora, no entanto, você perceberá que isso é muito fácil à medida que avançamos. Você pode ver abaixo vários Cronogramas / Módulos de Modelagem Financeira -

Por favor observe o seguinte -

As demonstrações principais são a Declaração de Renda, Balanço e Fluxo de Caixa.
Os cronogramas adicionais são o cronograma de depreciação, cronograma de capital de giro, cronograma de intangíveis, cronograma de patrimônio líquido, cronograma de outros itens de longo prazo, cronograma de dívida, etc.
Os cronogramas adicionais estão vinculados às declarações principais após sua conclusão
Neste guia de modelagem financeira, construiremos um modelo financeiro integrado passo a passo da Colgate Palmolive do zero.

# 1 - Modelagem Financeira no Excel - Projete os históricos

A primeira etapa no Guia de Modelagem Financeira é preparar o Histórico.

Etapa 1A - Baixe os Relatórios 10K da Colgate

“Os modelos financeiros são elaborados em excel e os primeiros passos começam com o conhecimento do desempenho do setor nos últimos anos. Compreender o passado pode nos fornecer insights valiosos relacionados ao futuro da empresa. Portanto, o primeiro passo é baixar todas as finanças da empresa e preencher as mesmas em uma planilha excel. Para a Colgate Palmolive, você pode baixar os relatórios anuais da Colgate Palmolive na seção de relações com investidores. Depois de clicar em “Relatório anual”, você encontrará a janela conforme mostrado abaixo -

Etapa 1B - Criar a planilha de demonstrações financeiras históricas

Se você baixar 10K de 2013, notará que apenas dois anos de dados de demonstrações financeiras estão disponíveis. Porém, para fins de Modelagem Financeira em excel, o conjunto de dados recomendado é ter os últimos 5 anos de demonstrações financeiras. Faça download dos últimos 3 anos do relatório anual e preencha o histórico.
Muitas vezes, essas tarefas parecem muito enfadonhas e tediosas, pois pode levar muito tempo e energia para formatar e colocar o excel no formato desejado.
Porém, não se deve esquecer que este é o trabalho que você deve fazer apenas uma vez para cada empresa e também, preencher os históricos ajuda o analista a entender as tendências e as demonstrações financeiras.
Portanto, não pule isso, baixe os dados e preencha os dados (mesmo se você achar que isso é trabalho de burro ;-))

Se desejar pular esta etapa, você pode baixar diretamente o modelo histórico da Colgate Palmolive aqui.

Declaração de renda da Colgate com histórico preenchido

Dados históricos do balanço da Colgate

# 2 - Análise de Razão

A segunda etapa da Modelagem Financeira no Excel é realizar a Análise de Proporção.

A chave para aprender Modelagem Financeira no Excel é ser capaz de realizar análises fundamentais. Se a análise fundamentalista ou Ratio Analysis é algo novo para você, recomendo que leia um pouco na internet. Pretendo fazer uma análise de proporção em profundidade em um de meus próximos posts, no entanto, aqui está um rápido instantâneo das proporções de Palmolive

IMPORTANTE - Por favor, note que eu atualizei a Análise de Proporção da Colgate em um post separado. Por favor, dê uma olhada nesta análise abrangente de proporção.

Etapa 2A - Análise vertical de Colgate

Na demonstração do resultado, a análise vertical é uma ferramenta universal para medir o desempenho relativo da empresa ano a ano em termos de custo e lucratividade. Deve sempre ser incluído como parte de qualquer análise financeira. Aqui, as porcentagens são calculadas em relação às vendas líquidas, que são consideradas 100%. Esse esforço de análise vertical na demonstração de resultados é frequentemente referido como análise de margem, uma vez que produz as diferentes margens em relação às vendas.

Resultados da análise vertical

A margem de lucro aumentou 240 pontos base de 56,2% em 2007 para 58,6% em 2013. Isso se deve principalmente à diminuição do custo de vendas
O lucro operacional ou EBIT também mostrou margens melhoradas, aumentando de 19,7% em 2007 para 22,4% em 2012 (um aumento de 70 pontos básicos). Isso ocorreu devido à redução dos custos gerais e administrativos de Vendas. No entanto, observe que as margens EBIT reduziram em 2013 para 20,4% devido a um aumento em “Outras despesas”. Além disso, verifique a diferença entre EBIT vs EBITDA
A margem de lucro líquido aumentou de 12,6% em 2007 para 14,5% em 2012. No entanto, a margem de lucro em 2013 diminuiu para 12,9%, principalmente devido ao aumento de “outras despesas”.
O lucro por ação aumentou de forma constante de FY2007 até FY2012. No entanto, houve uma ligeira queda no EPS do FY2013
Além disso, observe que a Depreciação e a Amortização são fornecidas separadamente na Demonstração do Resultado. Está incluído no Custo das Vendas

Etapa 2B - Análise horizontal de Colgate

A análise horizontal é uma técnica usada para avaliar tendências ao longo do tempo, calculando aumentos percentuais do Excel ou diminuições em relação a um ano base. Ele fornece uma ligação analítica entre contas calculadas em datas diferentes usando moeda com diferentes poderes de compra. Com efeito, esta análise indexa as contas e compara a evolução destas ao longo do tempo. Tal como acontece com a metodologia de análise vertical, surgirão questões que precisam ser investigadas e complementadas com outras técnicas de análise financeira. O foco é procurar sintomas de problemas que podem ser diagnosticados por meio de técnicas adicionais.

Vejamos a análise horizontal da Colgate

Resultados de análise horizontal

Vemos que as vendas líquidas aumentaram 2,0% em 2013.
Além disso, observe a tendência do Custo das Vendas, vemos que eles não cresceram na mesma proporção que as Vendas.
Essas observações são extremamente úteis enquanto fazemos modelagem financeira no Excel

Etapa 2C - Índices de liquidez da Colgate

Os índices de liquidez medem a relação entre os ativos mais líquidos de uma empresa (os mais facilmente conversíveis em dinheiro) e os passivos circulantes. Os índices de liquidez mais comuns são: Índice atual Índice de teste de ácido (ou ativo rápido) Índice de caixa
Índices de rotatividade, como rotatividade de contas a receber, rotatividade de estoque e rotatividade de contas a pagar

Principais destaques dos índices de liquidez

A proporção atual da Colgate é superior a 1,0 em todos os anos. Isso implica que os ativos circulantes são maiores do que os passivos circulantes e talvez a Colgate tenha liquidez suficiente
O Quick Ratio da Colgate está na faixa de 0,6-0,7, isso significa que o Caixa da Colgates e títulos negociáveis podem pagar por até 70% do passivo circulante. Esta parece ser uma situação razoável para a Colgate.
O ciclo de recebimento de caixa diminuiu de 43 dias em 2009 para 39 dias em 2013. Isso se deve principalmente à redução no período de recebimento de contas a receber.

Além disso, dê uma olhada neste artigo detalhado sobre Ciclo de conversão de dinheiro

Etapa 2D - Índices de lucratividade operacional da Colgate

Os índices de lucratividade avaliam a capacidade de uma empresa de gerar ganhos relativos a vendas, ativos e patrimônio líquido

Destaques principais - Índices de lucratividade da Colgate

Como podemos ver na tabela acima, a Colgate possui um ROE próximo a 100%, o que implica em grande retorno para os acionistas.

Etapa 2E - Análise de Risco da Colgate

Por meio da Análise de Risco, procuramos avaliar se as empresas conseguirão pagar suas obrigações (dívidas) de curto e longo prazo. Calculamos índices de alavancagem que enfocam a suficiência de ativos ou geração de ativos. As proporções que são analisadas são

Rácio dívida / capital próprio
Relação de dívida
Taxa de cobertura de juros
O índice dívida / patrimônio líquido aumentou continuamente para um nível mais alto de 2,23x. Isso significa maior alavancagem financeira e riscos no mercado
No entanto, o Índice de cobertura de juros é muito alto, o que significa menos risco de inadimplência no pagamento de juros.

# 3 - Modelagem Financeira no Excel - Projete a Demonstração de Resultados

A terceira etapa na Modelagem Financeira é prever a Demonstração de Resultados, onde começaremos com a modelagem dos itens de Vendas ou Receitas.

Etapa 3A - Projeções de receitas

Para a maioria das empresas, as receitas são um fator fundamental para o desempenho econômico. Um modelo de receita lógico e bem projetado que reflita com precisão o tipo e os valores dos fluxos de receita é extremamente importante. Existem tantas maneiras de criar um cronograma de receitas quanto empresas. Alguns tipos comuns incluem:

Crescimento de vendas: a suposição de crescimento de vendas em cada período define a mudança em relação ao período anterior. Este é um método simples e comumente usado, mas não oferece nenhuma visão dos componentes ou da dinâmica do crescimento.
Efeitos inflacionários e de volume / mix: em vez de uma simples suposição de crescimento, são usados um fator de inflação de preços e um fator de volume. Essa abordagem útil permite a modelagem de custos fixos e variáveis em empresas de vários produtos e leva em consideração os movimentos de preço versus volume.
Volume Unitário, Mudança no Volume, Preço Médio e Mudança no Preço: Este método é apropriado para empresas que possuem um mix de produtos simples; permite a análise do impacto de várias variáveis-chave.
Tamanho e crescimento do mercado em dólar: participação no mercado e mudança na participação - útil para os casos em que há informações disponíveis sobre a dinâmica do mercado e onde essas premissas são provavelmente fundamentais para uma decisão. Por exemplo, a indústria de telecomunicações
Tamanho e crescimento do mercado unitário: é mais detalhado do que o caso anterior e é útil quando o preço no mercado é uma variável-chave. (Para uma empresa com uma estratégia de desconto no preço, por exemplo, ou o melhor jogador de nicho com preço premium), por exemplo, mercado de carros de luxo
Capacidade de volume, taxa de utilização da capacidade e preço médio: essas premissas podem ser importantes para empresas onde a capacidade de produção é importante para a decisão. (Na compra de capacidade adicional, por exemplo, ou para determinar se a expansão exigiria novos investimentos.)
Disponibilidade e preços do produto
A receita foi impulsionada pelo investimento em capital, marketing ou P&D
Receita com base na base instalada (venda contínua de peças, descartáveis, serviços e complementos, etc). Os exemplos incluem empresas clássicas de lâmina de barbear e empresas como computadores, onde as vendas de serviços, software e atualizações são importantes. Modelar a base instalada é fundamental (novas adições à base, atrito na base, receita contínua por cliente, etc.).
Com base no funcionário: por exemplo, receitas de empresas de serviços profissionais ou empresas com base em vendas, como corretores. A modelagem deve focar na equipe líquida, receita por funcionário (geralmente com base em horas faturáveis). Modelos mais detalhados incluirão antiguidade e outros fatores que afetam os preços.
Com base em lojas, instalações ou metragem quadrada: as empresas de varejo são frequentemente modeladas com base nas lojas (lojas antigas mais novas em cada ano) e na receita por loja.
Com base no fator de ocupação: esta abordagem é aplicável a companhias aéreas, hotéis, cinemas e outras empresas com baixos custos marginais.

Projetando as receitas da Colgate

Vejamos agora o relatório Colgate 10K 2013. Observamos que na demonstração do resultado, a Colgate não forneceu informações segmentais, no entanto, como uma informação adicional, a Colgate forneceu alguns detalhes dos segmentos na Página 87 Fonte - Colgate 2013 - 10K, Página 86

Como não temos mais informações sobre os segmentos, projetaremos as vendas futuras da Colgate com base nos dados disponíveis. Usaremos a abordagem de crescimento de vendas em todos os segmentos para derivar as previsões. Por favor, veja a imagem abaixo. Calculamos a taxa de crescimento ano a ano para cada segmento. Agora podemos assumir uma porcentagem de crescimento de vendas com base nas tendências históricas e projetar as receitas de cada segmento. As vendas líquidas totais são a soma total do Segmento Oral, Higiene Pessoal e Doméstica e Nutrição Pet.

Etapa 3B - Projeções de custos

Porcentagem das receitas: simples, mas não oferece nenhuma visão sobre qualquer influência (economia de escala ou carga de custo fixo
Custos diferentes da depreciação como um percentual das receitas e depreciação de um cronograma separado: Esta abordagem é realmente o mínimo aceitável na maioria dos casos e permite apenas uma análise parcial da alavancagem operacional.
Custos variáveis com base na receita ou volume, custos fixos com base em tendências históricas e depreciação de um cronograma separado: Esta abordagem é o mínimo necessário para a análise de sensibilidade da lucratividade com base em vários cenários de receita

Projeções de custos para Colgate

Para projetar o custo, a análise vertical feita anteriormente será útil. Vamos dar uma olhada na análise vertical -

Como já projetamos as vendas, todos os outros custos são algumas margens dessas vendas.
A abordagem consiste em seguir as diretrizes das margens históricas de custo e despesas e, em seguida, prever a margem futura.
Por exemplo, o Custo das Vendas tem estado na faixa de 41% -42% nos últimos 5 anos. Podemos olhar para a previsão das margens nesta base.
Da mesma forma, as despesas com vendas, gerais e administrativas têm estado historicamente na faixa de 34% -36%. Podemos assumir a futura margem de despesas de SG&A nesta base. Da mesma forma, podemos prosseguir para outro conjunto de despesas.

Usando as margens acima, podemos encontrar os valores reais por cálculos anteriores.

Para calcular a provisão para impostos, usamos o pressuposto da Taxa de Imposto Efetiva

Além disso, observe que não completamos a linha “Despesa (Receita) de Juros”, pois faremos uma nova leitura da Demonstração de Resultado em um estágio posterior.
Despesa de juros e receita de juros.
Também não calculamos a Depreciação e Amortização que já foi incluída no Custo das Vendas.
Isso completa a declaração de renda (pelo menos por enquanto!)

# 4- Modelagem Financeira - Cronograma de Capital de Giro

Agora que concluímos a declaração de renda, a quarta etapa da Modelagem Financeira é examinar a Programação do Capital de Giro.

Abaixo estão as etapas que devem ser seguidas para o cronograma de capital de giro

Etapa 4A - Vincule as vendas líquidas e o custo das vendas

Etapa 4B - Consulte os dados do balanço patrimonial relacionados ao capital de giro

Faça referência aos dados anteriores do balanço patrimonial
Calcular o capital de giro líquido
Chegue a um aumento / diminuição do capital de giro
Observe que não incluímos dívida de curto prazo e caixa e equivalentes de caixa no capital de giro. Trataremos da dívida e do caixa e equivalentes de caixa separadamente.

Etapa 4C - Calcular as taxas de rotatividade

Calcular taxas e percentagens históricas
Use o final ou saldo médio
Ambos são aceitáveis, desde que a consistência seja mantida

Etapa 4D - Preencher as premissas para itens de capital de giro futuros

Certos itens sem um driver óbvio geralmente são assumidos em valores constantes
Certifique-se de que as suposições são razoáveis e alinhadas com o negócio

Etapa 4E - Projetar os saldos de capital de giro futuros

Etapa 4F - Calcular as mudanças no Capital de Giro

Chegue ao fluxo de caixa com base em itens de linha individuais
Certifique-se de que os sinais são precisos!

Etapa 4G - Vincular o capital de giro previsto ao balanço patrimonial

Etapa 4H - Vincule o capital de giro à demonstração do fluxo de caixa

# 5 - Modelagem Financeira em Excel - Cronograma de Depreciação

Com a conclusão do cronograma de capital de giro, o próximo passo nesta Modelagem Financeira é projetar o Capex da Colgate e projetar os números de Depreciação e Ativos. Colgate 2013 - 10K, Página 49

Depreciação e amortização não é fornecida como um item de linha separado, no entanto, está incluída no custo das vendas
Nesses casos, dê uma olhada nas demonstrações de fluxo de caixa, onde você encontrará a Despesa de Depreciação e Amortização. Observe também que os números abaixo são 1) Depreciação 2) Amortização. Então, qual é o número da depreciação?
Saldo final para imobilizado = saldo inicial + Capex - Depreciação - Ajuste para vendas de ativos (equação BASE)

Etapa 5A - Vincular os números de vendas líquidas na programação de depreciação

Configure os itens de linha
Vendas Líquidas de Referência
Entrada de despesas de capital anteriores
Chegue ao Capex como uma% das vendas líquidas

Etapa 5B - Previsão dos itens de dispêndio de capital

Para prever as despesas de capital, existem várias abordagens. Uma abordagem comum é olhar para os comunicados de imprensa, projeções de gerenciamento, MD&A para entender a visão da empresa sobre as despesas de capital futuras
Se a empresa forneceu orientações sobre futuros dispêndios de capital, podemos obter esses números diretamente.
No entanto, se os números do Capex não estiverem disponíveis diretamente, podemos calculá-lo grosseiramente usando Capex como% das vendas (como feito abaixo)
Use seu julgamento com base no conhecimento da indústria e outros motivadores razoáveis

Etapa 5C - Informações anteriores de referência

Usaremos Saldo Final para PPE = Saldo inicial + Capex - Depreciação - Ajuste para Vendas de Ativos (equação BASE)
É muito difícil reconciliar o PP&E passado devido a reapresentações, vendas de ativos, etc.
Portanto, é recomendado não reconciliar o EPI anterior, pois pode causar alguma confusão.

Política de depreciação da Colgate

Notamos que a Colgate não forneceu explicitamente uma divisão detalhada dos Ativos. Em vez disso, eles distribuíram todos os ativos em terrenos, edifícios, máquinas e outros equipamentos
Além disso, a vida útil de máquinas e equipamentos é fornecida dentro do intervalo. Nesse caso, teremos que fazer algumas suposições para chegar à vida útil média restante para os ativos
Além disso, a orientação de vida útil não é fornecida para “Outros Equipamentos”. Teremos que estimar a vida útil de outros Equipamentos

Colgate 2013 - 10K, Página 55

Abaixo está a divisão dos detalhes do ativo imobilizado de 2012 e 2013

Colgate 2013 - 10K, Página 91

Etapa 5D - Estimar a divisão do ativo imobilizado (PPE)

Primeiro, encontre os pesos dos ativos do PPE atual (2013)
Assumiremos que esses pesos dos ativos de imobilizado de 2013 continuarão no futuro
Usamos esses pesos de ativos para calcular a divisão da despesa de capital estimada

Etapa 5E - Estimar a depreciação dos ativos

Observe que não calculamos a depreciação de terrenos, pois os terrenos não são um ativo depreciável
Para estimar a depreciação de melhorias em edifícios, primeiro usamos a estrutura abaixo.
A depreciação aqui é dividida em duas partes - 1) depreciação do Ativo de Benfeitorias já listado no balanço patrimonial 2) depreciação das futuras Benfeitorias
Para calcular a depreciação das melhorias de construção listadas no ativo, usamos o método linear simples de depreciação
Para calcular a depreciação futura, primeiro transpomos o Capex usando a função TRANSPOSE no Excel
Calculamos a depreciação da contribuição de ativos de cada ano
Além disso, a depreciação do primeiro ano é dividida por 2, pois assumimos a convenção de meio do ano para a implantação de ativos

Depreciação Total de Melhorias de Construção = depreciação do Ativo de Melhorias de Construção já listado no balanço patrimonial + depreciação de melhorias de construção futuras O processo acima para estimar a depreciação é usado para calcular a depreciação de 1) Equipamento de Fabricação e Maquinário e 2) Outros Equipamentos como mostrado abaixo.

Outros tipos de equipamento

Depreciação total da Colgate = Depreciação (Melhorias na construção) + Depreciação (Máquinas e equipamentos) + Depreciação (outro equipamento) Uma vez que descobrimos os valores de depreciação total, podemos colocá-los na equação BASE como mostrado abaixo

Com isso, obtemos os números do PP&E líquido final para cada um dos anos

Etapa 5F - Vincule o PP&E líquido ao balanço patrimonial

# 6 - Cronograma de Amortização

A sexta etapa desta Modelagem Financeira em Excel é a previsão da Amortização. Temos duas categorias amplas a serem consideradas aqui - 1) Goodwill e 2) Outros Intangíveis.

Etapa 6A - Previsão de Goodwill

Colgate 2013 - 10K, Página 61

O goodwill surge no balanço quando uma empresa adquire outra empresa. Normalmente é muito difícil projetar o Goodwill para os anos futuros.
No entanto, o Goodwill está sujeito a testes de imparidade anuais que são realizados pela própria empresa. Os analistas não estão em posição de realizar tais testes e preparar estimativas de deficiências
A maioria dos analistas não projeta goodwill, eles apenas mantêm isso constante e é o que faremos também no nosso caso.

Etapa 6B - Previsão de outros ativos intangíveis

Conforme observado no Relatório 10K da Colgate, a maior parte do intangível de vida finita está relacionada à aquisição da Sanex
“Adições a intangíveis” também são muito difíceis de projetar
O relatório 10K da Colgate nos fornece os detalhes dos próximos 5 anos de despesas de amortização.
Usaremos essas estimativas em nosso Modelo Financeiro Colgate 2013 - 10K, Página 61

Etapa 6C - Os intangíveis líquidos finais estão ligados aos "Outros Ativos Intangíveis"

Etapa 6D - vincular Depreciação e Amortização às Demonstrações de Fluxo de Caixa

Etapa 6E - Vincular Capex e Adição a Intangíveis às demonstrações de fluxo de caixa

# 7 - Outro cronograma de longo prazo

A próxima etapa nesta Modelagem Financeira é preparar o Outro Cronograma de Longo Prazo. Esse é o cronograma que preparamos para as “sobras” que não possuem direcionadores específicos para a previsão. No caso da Colgate, os outros Itens de Longo Prazo (sobras) foram Imposto de Renda Diferido (passivo e ativo), Outros ativos e outros passivos.

Etapa 7A - Referência aos dados históricos do balanço patrimonial

Além disso, calcule as mudanças nesses itens.

Etapa 7B - Previsão de ativos e passivos de longo prazo

Mantenha os itens de longo prazo constantes para os anos projetados, no caso de não haver drivers visíveis
Vincule os itens de longo prazo previstos ao balanço patrimonial conforme mostrado abaixo

Etapa 7C - Referência de outros itens de longo prazo ao balanço patrimonial

Etapa 7D - Vincule os itens de longo prazo à Demonstração do Fluxo de Caixa

Observe que, se mantivermos os ativos e passivos de longo prazo constantes, a mudança que flui para a demonstração do fluxo de caixa seria zero.

# 8 - Modelagem Financeira em Excel - Completando a Demonstração de Resultados

Antes de prosseguirmos neste Modelagem Financeira baseada em Excel, vamos voltar e reler a Demonstração de Resultados
Preencher as ações médias ponderadas básicas históricas e o número médio ponderado diluído de ações
Esses números estão disponíveis no relatório 10K da Colgate

Etapa 8A - Referência às ações básicas e diluídas

Nesta fase, assume-se que o número futuro de ações básicas e diluídas permanecerá o mesmo de 2013.

Etapa 8B - Calcular o lucro básico e diluído por ação

Com isso, estamos prontos para avançar para nosso próximo cronograma, ou seja, Cronograma de Patrimônio Líquido.

# 9 - Modelagem Financeira - Tabela de Patrimônio Líquido

A próxima etapa neste treinamento de Modelagem Financeira em Excel é examinar a Programação do Patrimônio Líquido. O objetivo principal deste cronograma é projetar itens relacionados ao patrimônio líquido, como patrimônio líquido, dividendos, recompra de ações, receitas de opções, etc. O relatório 10K da Colgate nos fornece os detalhes das ações ordinárias e atividades de ações em tesouraria nos últimos anos, conforme mostrado abaixo. Colgate 2013 - 10K, Página 68

Etapa 9A - Recompra de ações: preencher os números históricos

Historicamente, a Colgate recomprou ações, como podemos ver no cronograma acima.
Preencha a recompra de ações da Colgate (milhões) na planilha do excel.
Vincule o EPS diluído histórico da demonstração de resultados
O valor histórico recomprado deve ser referenciado nas demonstrações de fluxo de caixa

Além disso, dê uma olhada em Recompra acelerada de ações

Etapa 9B - Recompra de ações: calcule o múltiplo PE (múltiplo EPS)

Calcule o preço médio implícito pelo qual a Colgate fez recompra de ações historicamente. Isso é calculado como valor recomprado / número de ações
Calcule o múltiplo PE = Preço de Ação Implícito / EPS

Etapa 9C - Recompra de ações: Encontrar as ações da Colgate recompradas

A Colgate não fez nenhum anúncio oficial de quantas ações pretende recomprar. A única informação de que suas ações de relatório de 10K são autorizadas a recompra de até 50 milhões de ações. Colgate 2013 - 10K, Página 35

Para encontrar o número de ações recompradas, precisamos assumir o valor de recompra de ações. Com base no valor histórico da recompra, considerei esse número como $ 1.500 milhões para todos os anos futuros.
Para encontrar o número de ações recompradas, precisamos do preço de ação implícito projetado da recompra potencial.
Preço de ação implícito = EPS multiplex PE assumido
As compras futuras múltiplas de PE podem ser assumidas com base em tendências históricas. Observamos que a Colgate recomprou ações em uma faixa de PE de 17x - 25x
Abaixo está o instantâneo da Reuters que nos ajuda a validar a gama de PE para Colgate

www.reuters.com

Em nosso caso, presumi que todas as recompras futuras da Colgate serão em um PE múltiplo de 19x.
Usando o PE de 19x, podemos encontrar o preço implícito = EPS x 19
Agora que encontramos o preço implícito, podemos encontrar o número de ações recompradas = $ quantia usada para recompra / preço implícito

Etapa 9D - Opções de estoque: preencher dados históricos

A partir do resumo das ações ordinárias e do patrimônio líquido, sabemos a quantidade de opções exercidas a cada ano.

Além disso, também temos a opção Receitas das demonstrações de fluxo de caixa (aprox)

Com isso, devemos ser capazes de encontrar o preço de exercício efetivo

Colgate 2013 - 10K, Página 53

Além disso, observe que as opções de ações têm prazos contratuais de seis anos e direitos adquiridos por três anos. Colgate 2013 - 10K, Página 69

Com esses dados, preenchemos os dados das Opções conforme abaixo. Observamos também que o preço de exercício médio ponderado das opções de ações em 2013 foi de $ 42 e o número de opções exercíveis foi de 24,151 milhões Colgate 2013 - 10K, Página 70

Etapa 9E - Opções de estoque: Encontre os rendimentos da opção

Colocando esses números em nossos dados de opções abaixo, observamos que o produto da opção é de $ 1,014 bilhão

Etapa 9F - Opções de Estoque: Previsão de Dados da Unidade de Estoque Restrito

Além das stock options, existem Restricted Stock Units atribuídas aos colaboradores com o período médio ponderado de 2,2 anos Colgate 2013 - 10K, Página 81

Preenchendo esses dados no conjunto de dados de opções Para simplificar, não projetamos a emissão de opções (sei que essa não é a suposição certa, no entanto, devido à falta de dados, não vou aceitar mais nenhuma emissão de opções daqui para frente. Acabamos de fazer estes são zero, conforme destacado na área cinza acima. Além disso, as unidades de estoque restrito são projetadas em 2,0 milhões no futuro.

Além disso, dê uma olhada no Método de Ações em Tesouraria

Etapa 9G- Dividendos: Prever os Dividendos

Previsão de dividendos estimados usando a taxa de pagamento de dividendos
Despesa de dividendo fixo Pagamento por ação
Dos relatórios 10K, extraímos todas as informações anteriores sobre dividendos
Com as informações dos dividendos pagos, podemos descobrir a relação de pagamento de dividendos = Dividendos totais pagos / Lucro líquido.
Eu calculei o índice de pagamento de dividendos da Colgate conforme visto abaixo - Observamos que o índice de pagamento de dividendos tem estado amplamente na faixa de 50% -60%. Vamos supor o índice de distribuição de dividendos de 55% nos anos futuros.
Podemos também vincular a receita líquida projetada à demonstração de resultados
Usando o lucro líquido projetado e o índice de pagamento de dividendos, podemos encontrar o total de dividendos pagos

Etapa 8H - Previsão da conta de patrimônio em sua totalidade

Com a previsão de recompra de ações, valor das opções e dividendos pagos, estamos prontos para completar a Tabela de Patrimônio Líquido. Vincule tudo isso para encontrar o saldo patrimonial final para cada ano, conforme mostrado abaixo.

Etapa 9I - Vincular o patrimônio líquido final ao balanço patrimonial

Etapa 9J - Vincular dividendos, recompra de ações e opções para CF

# 10 - Cronograma de Ações Pendentes

A próxima etapa nesta modelagem financeira on-line no treinamento do Excel é examinar a Programação de Valorização de Ações. Resumo da programação de ações em circulação

Ações básicas - reais e médias
Capture efeitos passados de opções e conversíveis conforme apropriado
Ações diluídas - média
Ações de referência recompradas e novas ações de opções exercidas
Calcular participações básicas previstas (reais)
Calcular ações básicas e diluídas médias
Referencie as ações projetadas à Declaração de Renda (lembre-se do Acúmulo da Declaração de Renda!)
Insira informações históricas pendentes
Nota : Este cronograma é comumente integrado com o cronograma de patrimônio

Etapa 10A - Insira os números históricos do relatório 10K

As ações emitidas (realização real das opções) e as ações recompradas podem ser referenciadas na tabela de patrimônio líquido
Além disso, a entrada ponderou um número médio de ações e o efeito das opções de ações nos anos históricos.

Etapa 10B - Vincule as emissões e recompras de ações da tabela de patrimônio líquido.

Ações básicas (término) = Ações básicas (início) + Emissões de ações - Ações recompradas.

Etapa 10C - Encontre as ações médias ponderadas básicas,

encontramos uma média de dois anos, conforme mostrado abaixo.
Além disso, adicione o efeito de opções e unidades de ações restritas (referenciadas na tabela de patrimônio líquido) para encontrar a Média ponderada diluída de ações.

Etapa 10D - Vincular ações ponderadas básicas e diluídas à demonstração de resultados

Agora que calculamos a média ponderada diluída das ações, é hora de atualizarmos o mesmo na Demonstração do Resultado.
Vincule as ações médias ponderadas diluídas previstas em circulação à demonstração de resultados, conforme mostrado abaixo

Com isso, completamos a Programação de Valorização de Ações e o tempo para avançar para nosso próximo conjunto de declarações.

# 11 - Completando as Demonstrações de Fluxo de Caixa

É importante que completemos totalmente as demonstrações do fluxo de caixa antes de passarmos para o nosso próximo e último cronograma nesta Modelagem Financeira, ou seja, o Cronograma da Dívida. Até este estágio, há apenas algumas coisas que estão incompletas

Declaração de rendimentos - despesas / receitas de juros estão incompletas nesta fase
Balanço - itens de caixa e dívidas estão incompletos nesta fase

Etapa 11A - Calcular o Fluxo de Caixa para Atividades de Financiamento

Além disso, verifique o fluxo de caixa de financiamento

Etapa 11B - Encontre o aumento líquido (diminuição) em Caixa e Equivalentes de Caixa

Etapa 11C = Preencher as demonstrações de fluxo de caixa

Encontre o caixa e os equivalentes de caixa no final do ano.

Etapa 11D - Vincule o caixa e os equivalentes de caixa ao balanço patrimonial.

Agora estamos prontos para cuidar de nosso último e último cronograma, ou seja, Cronograma de Dívida e Juros

# 12- Modelagem Financeira em Excel - Cronograma de Dívida e Juros

A próxima etapa neste Modelagem Financeira Online é completar o Cronograma de Dívida e Juros. Resumo da Dívida e Juros - Cronograma

Etapa 12A - Definir um cronograma de dívida

Consulte o fluxo de caixa disponível para financiamento
Referência a todas as fontes de capital e usos de dinheiro

Etapa 12B - Calcular o fluxo de caixa do reembolso da dívida

Faça referência ao saldo de caixa inicial do balanço patrimonial
Deduza um saldo de caixa mínimo. Presumimos que a Colgate gostaria de manter um mínimo de $ 500 milhões por ano.

Ignorar a emissão / amortização de dívidas de longo prazo, caixa disponível para a seção de crédito rotativo e revólver por enquanto No relatório 10K da Colgate, observamos os detalhes disponíveis na linha de crédito rotativo Colgate 2013 - 10K, página 35

Também é fornecido nas informações adicionais sobre a dívida os reembolsos de dívida de longo prazo comprometidos. Colgate 2013 - 10K, Página 36

Etapa 12C - Calcular a dívida de longo prazo final

Usamos o cronograma de amortização da dívida de longo prazo fornecido acima e calculamos o saldo final das amortizações da dívida de longo prazo

Etapa 12D - Vincule os reembolsos de dívida de longo prazo.

Etapa 12E - Calcular os empréstimos / pagamentos discricionários

Usando a fórmula de varredura de caixa conforme mostrado abaixo, calcule os empréstimos / pagamentos discricionários.

Etapa 12F - Calcular a despesa de juros da dívida de longo prazo

Calcule o saldo médio para Linha de Crédito Rotativo e Dívida de Longo Prazo
Faça uma suposição razoável para uma taxa de juros com base nas informações fornecidas no relatório 10K
Calcular a despesa total de juros = saldo médio da dívida x taxa de juros

Encontre a despesa total de juros = juros (linha de crédito rotativo) + juros (dívida de longo prazo)

Etapa 12G - Vincular a dívida principal e as retiradas do revólver aos fluxos de caixa

Etapa 12H - Referência Atual e Longo Prazo ao Balanço

Demarque a Parcela Atual da Dívida de Longo Prazo e da dívida de Longo Prazo conforme mostrado abaixo

Vincule a linha de crédito rotativo, a dívida de longo prazo e a parcela atual da dívida de longo prazo ao balanço patrimonial

Etapa 12I - Calcular a receita de juros usando o saldo de caixa médio

Etapa 12J - Vincule a despesa de juros e receita de juros à declaração de receita

Realize a verificação do Balanço: Total de ativos = passivos + patrimônio líquido

Etapa 12K - Auditar o balanço patrimonial

Se houver alguma discrepância, precisamos auditar o modelo e verificar se há erros de ligação

Curso de Modelagem Financeira Recomendado

Espero que você tenha gostado do Guia grátis de modelagem financeira em Excel. Se você deseja aprender Modelagem Financeira no Excel por meio de nossas palestras em vídeo de especialistas, você também pode dar uma olhada em nosso Treinamento de Banco de Investimento. Este é principalmente um pacote de treinamento de banco de investimento com 99 cursos. Este curso começa do básico e leva você ao nível avançado de empregos em bancos de investimento. Este curso é dividido em 5 partes -

Parte 1 - Treinamento de banco de investimento - cursos básicos
(26 cursos)
Parte 2 - Treinamento de modelagem de banco de investimento avançado
(20 cursos)
Parte 3 - Complementos de banco de investimento
(13 cursos)
Parte 4 - Cursos da Fundação de Banco de Investimento
(23 cursos)
Parte 5 - Habilidades brandas para banqueiros de investimento
(17 cursos)

Download de modelos financeiros

Modelo Financeiro Alibaba
Modelo Financeiro da Caixa IPO
Modelos de modelagem financeira
Curso de Modelagem Financeira Bancária

Qual o proximo?

Se você aprendeu algo novo ou gostou da Modelagem Financeira baseada em Excel, deixe um comentário abaixo. Diz-me o que pensas. Muito obrigado e tome cuidado. Aprendizagem feliz!