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

Índice

O que é modelagem financeira no Excel?

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ções financeiras 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. Muito foi escrito sobre o aprendizado de Modelagem Financeira; entretanto, a maioria das peças de treinamento de modelagem financeira são 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 três 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

Modelagem Financeira no Treinamento do 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

Digite o endereço de e-mail Ao continuar a etapa acima, você concorda com nossos Termos de uso e Política de privacidade.

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 - Boa aprendizagem!

Índice

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

Como construir um modelo financeiro?
# 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. O sistema modular significa, essencialmente, a construção de declarações básicas, como Declaração de Renda, Balanço e Fluxo de Caixa, usando diferentes módulos / programações. O foco principal é preparar cada declaração passo a passo e conectar todos os programas de suporte às declaraçõ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 declarações principais são a Declaração de Renda, Balanço e Fluxo de Caixa.
As diferentes programações são a programação de depreciação, programação de capital de giro, programação de intangíveis, programação de patrimônio líquido, programação de outros itens de longo prazo, cronograma de dívida, etc.
Os diferentes cronogramas estão vinculados às declarações principais após sua conclusão.
Neste guia de modelagem financeira, construiremos um modelo econômico integrado passo a passo da Colgate Palmolive do zero.

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

A primeira etapa do 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 esse é 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 você deseja 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 patrimonial 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 elementar ou Análise de Razão é algo novo para você, recomendo que leia um pouco na internet. Pretendo fazer uma análise de proporção em profundidade em um dos meus próximos posts, no entanto, aqui está um rápido instantâneo das proporções do Colgate 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%. Este esforço de análise vertical na demonstração de resultados é freqüentemente 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 melhores, 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 de EBIT reduziram em 2013 para 20,4% devido ao aumento de “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 a moeda com poderes de compra variados. Com efeito, esta análise indexa os relatórios e compara a evolução destes 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 usando métodos 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 de 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 o índice atual, índice de teste de ácido (ou ativo rápido).
Índices de rotatividade como rotatividade de contas a receber, rotatividade de estoque e rotatividade de contas a pagar

Principais destaques dos índices de liquidez

O Current Ratio da Colgate é mais significativo do que 1,0 para todos os anos. Isso implica que os ativos circulantes são mais significativos 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 Colgates e os títulos negociáveis podem pagar até 70% do passivo circulante. Esta parece ser uma boa situação 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 tem um ROE próximo a 100%, o que implica em excelentes retornos 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 taxas analisadas são

Rácio dívida / capital próprio
Relação de dívida
Taxa de cobertura de juros
O índice de endividamento em relação ao patrimônio líquido aumentou continuamente para um nível mais alto de 2,23x. Isso significa maior alavancagem financeira e riscos no mercado
Porém, o Índice de Cobertura de Juros é muito alto, indicando menor 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 receita

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 uma tabela 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, variação no volume, preço médio e variação 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 a precificação no mercado é uma variável crucial. (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, o 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 é essencial 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 add-ons, etc.). Os exemplos incluem empresas clássicas de lâmina de barbear e empresas como computadores em que as vendas de serviços, software e atualizações são essenciais. Modelar a base instalada é fundamental (novas adições ao piso, atrito no solo, 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 custos marginais baixos.

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 por segmento; entretanto, 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 recursos, 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 elemento. Agora podemos assumir uma porcentagem de crescimento de vendas com base nas tendências históricas e projetar as receitas em cada parte. 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 diferente: 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 é seguir as diretrizes das margens históricas de custo e despesas e, então, prever a margem futura.
Por exemplo, o Custo das Vendas tem estado na faixa de 41% -42% nos últimos cinco 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 Efetiva de Imposto.

Além disso, observe que não completamos a linha “Despesa (Receita) de Juros”, pois faremos uma nova leitura da Demonstração de Resultados 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á está 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 completamos a declaração de renda, a quarta etapa na 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. Lidaremos com dívida e 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 proeminente geralmente são considerados 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 finalizaçã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

A Depreciação e Amortização não são fornecidas como um item de linha separado; no entanto, está incluído 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. Além disso, observe 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 prática comum é consultar os comunicados à imprensa, as projeções de gerenciamento, o MD&A para entender a visão da empresa sobre os futuros gastos de capital
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 de maneira grosseira usando Capex como% das vendas (como feito abaixo)
Use seu julgamento com base no conhecimento da indústria e outros fatores 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)
É complicado 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

Observamos 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 para a 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 melhorias do edifício já listado no balanço patrimonial, 2) depreciação das futuras melhorias do edifício.
Para calcular a depreciação das melhorias de construção listadas no ativo, usamos o método da linha reta simples de depreciação.
Para calcular a depreciação futura, primeiro transpomos o Capex usando a função TRANSPOSE do Excel.
Calculamos a depreciação das contribuições de ativos de cada ano.
Além disso, a depreciação do primeiro ano é dividida por dois, já que assumimos a convenção de meio do ano para a implantação de ativos.

Depreciação Total de Melhorias de Edifícios = depreciação do Ativo de Melhorias de Edifícios já listado no balanço patrimonial + depreciação de melhorias futuras de Edifícios 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 (equipamento adicional) Uma vez que descobrimos os valores reais de depreciação, 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 ativo imobilizado 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. Geralmente é complicado projetar o Goodwill para os anos futuros.
No entanto, o Goodwill é sujeito anualmente a testes de imparidade, os quais são efectuados pela própria empresa. Os analistas não estão em posição de conduzir tais testes e preparar estimativas de imparidades.
A maioria dos analistas não projeta boa vontade; eles apenas mantêm isso constante, o que também faremos 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 complicadas de projetar
O relatório 10K da Colgate nos fornece os detalhes dos próximos cinco 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 vinculados 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. É quando nos preparamos para as “sobras” que não têm 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 investimentos 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 à Declaração de Fluxo de Caixa

Observe que, se mantivemos 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 nesta 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, assuma que o número futuro de ações primárias 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 a nossa próxima programação, ou seja, a Tabela 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, conforme 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 de recompra 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 o 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 seus 10 mil ações de relatório é que eles autorizaram a recompra de até 50 milhões de ações. Colgate 2013 - 10K, Página 35

Para saber 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 real da ação = 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 médio de 17x - 25x
Abaixo está o instantâneo da Reuters que nos ajuda a validar a gama de PE para a 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 ver 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 um 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 ações: 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 prazo 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 correta; no entanto, devido à falta de dados, não estou levando mais nenhuma questão de opção adiante. Acabamos de levá-la como zero, conforme destacado na área cinza acima Além disso, as unidades de estoque restrito estã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

Prever os dividendos estimados usando o Dividend Payout Ratio.
Despesa de dividendo fixo Pagamento por ação
Dos relatórios 10K, extraímos todas as informações anteriores sobre os 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 distribuição de dividendos da Colgate conforme mostrado abaixo - Observamos que o índice de distribuição de dividendos tem estado amplamente na faixa de 50% -60%. Vamos supor o índice de distribuição de dividendos de 55% nos anos futuros.
Também podemos vincular a receita líquida projetada à demonstração do resultado.
Usando o lucro líquido projetado e o índice de distribuição 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, receitas de 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 em circulação de ações

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 as porcentagens brutas previstas (reais)
Calcular ações básicas e diluídas médias
Referencie as ações projetadas à demonstração de resultados (lembre-se do acúmulo da demonstração de resultados!)
Entrada de informações de compartilhamentos históricos 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
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 ações.

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 a seguir.
Além disso, adicione o efeito das opções e unidades de ações restritas (referenciado na tabela de patrimônio líquido) para encontrar a Média ponderada diluída das 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 nosso próximo e último cronograma nesta Modelagem Financeira, ou seja, o Cronograma de Dívida. Até este estágio, existem apenas algumas coisas 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 declaraçõ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

Grau 12A - Definir um cronograma de dívida

Referencie 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 Pagamento 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 US $ 500 milhões por ano.

Pule a seção de Emissão / Reembolso de Dívidas de Longo Prazo, Caixa Disponível para Facilidade de Crédito Rotativo e Revólver por enquanto Do relatório 10K da Colgate; observamos os detalhes disponíveis no Revolved Credit Facility Colgate 2013 - 10K, Página 35

Também é fornecido em 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 no 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 -Calcule os empréstimos / playdowns 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 - Dívida do Link Principal e rebaixamentos do Revolver para Fluxos de Caixa

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

Demarcar 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 com o básico e leva você ao nível avançado de empregos em bancos de investimento. Este curso é dividido em cinco partes -

Parte 1 - Treinamento de banco de investimento - Cursos básicos
(26 cursos)
Parte 2 - Treinamento em modelagem avançada de banco de investimento
(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!