Práticas recomendadas de modelagem financeira avançada: truques para modelagem inteligente e livre de erros

Sumário executivo

Introdução: Um Modelo Financeiro

Os modelos financeiros são uma parte indispensável do kit de ferramentas de finanças de todas as empresas. São planilhas que detalham os dados financeiros históricos de um determinado negócio, prevêem seu desempenho financeiro futuro e avaliam seu perfil de riscos e retornos. Os modelos financeiros são normalmente estruturados em torno das três demonstrações financeiras da contabilidade, a saber: demonstração de resultados, balanço patrimonial e demonstração de fluxo de caixa. A gestão da maioria das corporações depende, pelo menos em parte, dos detalhes, premissas e resultados dos modelos financeiros, todos críticos para os processos de tomada de decisão estratégica e de capital dessas empresas.

Este artigo serve como um guia passo a passo para profissionais de finanças iniciantes e intermediários que desejam seguir as práticas recomendadas de especialistas ao criar modelos financeiros. Para o modelador financeiro avançado, este artigo também apresentará uma seleção de dicas e truques de nível especializado para otimizar o tempo, a produção e a eficácia da modelagem. Vamos começar.

Planejando seu modelo

Tal como acontece com todas as coisas complexas, o primeiro passo para construir um modelo financeiro (“modelo”) é desenhar cuidadosamente um plano. Mudanças estruturais não planejadas e inesperadas no meio de um exercício de modelagem podem ser demoradas, confusas e propensas a erros, especialmente se o adaptador do modelo não for o mesmo que seu autor. Tais desafios são facilmente subvertidos com um pouco de tempo de planejamento dedicado no início do exercício. Eu recomendo que sua fase de planejamento seja da seguinte forma:

1. Defina o objetivo final do modelo.

Definir claramente o propósito de um modelo é fundamental para determinar seu layout, estrutura e resultados finais ideais. Como parte desse processo, reserve um tempo para garantir que as principais partes interessadas do seu modelo aprovem o projeto e o design do processo antes de começar a construir. Isso lhes dá a oportunidade de expressar quaisquer preferências ou intenções finais, evitando assim qualquer “deslocamento de escopo” (linguagem da indústria) ou redirecionamento doloroso no caminho.

2. Compreender os prazos tanto para a construção do modelo quanto para sua vida útil.

Embora secundário ao objetivo final do modelo, entender os cronogramas para construir o modelo e por quanto tempo o modelo será usado também são entradas importantes para determinar a abordagem do exercício de modelagem. Os modelos de longa duração e de longa duração (vida útil) são geralmente construídos de forma personalizada desde o início e incluem enormes quantidades de detalhes operacionais, flexibilidade e recursos de sensibilidade. Para modelos operacionais ou de projetos de capital mais imediatos e de menor duração, os modeladores geralmente usam modelos pré-fabricados para maximizar a velocidade de construção e minimizar os erros. Além disso, os modelos de modelo também tendem a ser mais familiares e, portanto, mais fáceis de usar/manipular por diferentes partes interessadas nas organizações.

3. Determine a compensação ideal entre “detalhe” versus “reutilização”.

Ao decidir o equilíbrio ideal entre o nível de detalhe desejado e a reutilização do modelo (ou seja, se o modelo se destina a ser retrabalhado para vários tipos/fins de transação ou se foi projetado apenas para este exercício único), um estrutura útil para decidir sobre a escolha/abordagem do modelo, que tenho seguido durante a maior parte da minha carreira, é a seguinte:

Com a fase de projeto/planejamento agora concluída e as principais decisões estabelecidas, podemos agora passar para a próxima fase de modelagem.

Estruturando seu modelo

Neste momento, estamos prontos para abrir o Excel e começar a pensar na estruturação. No nível mais alto possível, todo modelo pode/deve ser dividido em três seções: (a) entradas/condutores, (b) cálculos (demonstrações financeiras projetadas) e (c) saídas. Quanto melhor for a segregação dessas seções, mais fácil será auditar e alterar o modelo, minimizando erros e otimizando o tempo.

Segui a mesma abordagem estrutural para quase todos os modelos que construí; uma abordagem que eu e meus respectivos stakeholders sempre achamos prático, digerível e, em última análise, útil. Suas seções são as seguintes:

1. Página de rosto (guia): codinome do projeto, uma descrição da intenção do modelo, as informações de contato do autor e quaisquer isenções de responsabilidade aplicáveis.
2. Guia Drivers: Entradas e suposições.
3. Guia Modelo: Cálculos (ou seja, as três projeções e cálculos das demonstrações financeiras).
4. Guia Saídas: Um resumo limpo e organizado dos destaques mais importantes do modelo.
5. Guia Sensibilidades: A variedade de cenários, sensibilidades e resultados de dados nos quais a administração dependerá à medida que fizerem a transição para o processo de tomada de decisão.

Vou dividir cada uma dessas seções para você, uma de cada vez. Do seguinte modo:

Capa

A capa é o primeiro ponto de contato com o seu trabalho. Embora seja o mais simples de construir, quando bem feito, deixa uma ótima primeira impressão e explica claramente o que está por vir. Uma capa simples e instrutiva geralmente é a melhor abordagem e geralmente inclui as seguintes seções:

1. Nome do Modelo: Autoexplicativo.
2. Objetivo do Modelo: Um parágrafo descrevendo seu(s) uso(s) pretendido(s)
3. Índice do Modelo: Uma breve tabela detalhando a descrição e a finalidade de cada guia. Esta seção pode ser especialmente útil para operadores não financeiros, ajudando-os a “digerir” a estrutura e o fluxo do modelo, destacando quais guias eles precisam usar para entradas, quais saídas devem ser focadas durante a tomada de decisões e quais guias de cálculo complexas devem ser usadas. deixe intocado.
4. Histórico de versões do modelo: Investir alguns segundos na digitação, por data, das principais alterações feitas no modelo à medida que você avança sempre economiza tempo no caminho, especialmente se você precisar refazer e reverter/modificar alterações. Isso é especialmente verdadeiro para modelos complexos e modelos que você pode usar como modelos em períodos futuros.
5. Informações de contato do autor: Autoexplicativo
6. Isenções de responsabilidade legais aplicáveis ​​(se houver, conforme fornecido pelo seu consultor jurídico) : Autoexplicativo

Observação: recomendo que a página de rosto seja sempre fechada para qualquer pessoa e todos sem autorização expressa para fazer alterações, fora do autor.

Guia do motorista: entradas e suposições

Imediatamente após a capa do modelo, deve vir a aba drivers (entradas) . Você deve garantir que essa guia seja clara, concisa e fácil de entender, pois essa é a guia que os operadores não financeiros provavelmente manipularão com mais frequência. Normalmente, recomendo implementar duas seções de entrada na guia de entradas, uma para entradas estáticas e outra para dinâmicas . Por entradas estáticas quero dizer entradas que não mudam ao longo do tempo, como o hipotético “tamanho de uma usina” ou “saldo inicial da dívida de uma empresa”; e por entradas dinâmicas , quero dizer entradas que são variáveis ​​ao longo do tempo (por exemplo, mês a mês ou ano a ano), como premissas de “inflação”, “custo da dívida” ou premissas de “crescimento da receita”.

Exemplo de uma guia de drivers e suposições de amostra

Nas duas seções de entrada estática versus dinâmica acima, recomendo que você também separe claramente seus dados em dois tipos: (1) figuras codificadas que não mudam independentemente do cenário de suposições e (b) parâmetros de sensibilização que irão conduza diferentes cenários de suposições e, finalmente, suas tabelas de sensibilidade. Observe, no entanto, que você nunca sabe totalmente quais parâmetros vão constituir parâmetros de sensibilidade e quais você não saberá até os estágios finais do projeto. Para obter mais informações sobre modelagem de sensibilidade, consulte o artigo a seguir.

Guia Modelo: Cálculos Detalhados e Construção Operacional

Essa guia representa o coração do modelo, onde todas as entradas, premissas e cenários trabalham juntos para projetar o desempenho financeiro de uma empresa em seus anos finais. É também nessa guia que vários cenários baseados em suposições serão executados, bem como a parte de avaliação do exercício que será conduzida antes da decisão estratégica final.

Exemplo de uma guia de modelo de amostra

Aba Cenários e Sensibilidades

Operadores de modelo de terceiros autorizados usarão a guia Cenários e Sensibilidades com bastante frequência, mesmo que seja apenas para selecionar sua escolha de cenários pré-programados. Por esse motivo, você deve construir cenários intuitivamente, proteger os cenários reais da edição externa e construir sensibilidades suficientemente variadas, de modo que o punhado de cenários pré-programados seja suficiente para produzir uma visão ampla dos possíveis resultados, uma vez que as tabelas de sensibilidade (exemplo abaixo) também são construídos.

Para sua consideração, a estrutura de formato de cenários em que confiei ao longo de minha carreira é a seguinte, apenas um tipo de exemplo:

Algumas observações sobre a imagem acima:

1. O usuário do modelo deve poder editar apenas isso, pois é onde ele selecionará o número do cenário. O número refere-se a um dos cenários apresentados no lado direito da planilha. O usuário apresentará então o cenário selecionado (neste caso, o nº 6) na primeira coluna. Esta é a única coluna da planilha Cenário e Sensibilidades referenciada no modelo.
2. Adicione aqui alguns campos de descrição que resumam efetivamente o que o cenário selecionado representa.
3. Eu sempre acho muito útil, especialmente se alguém usar o modelo, adicionar uma coluna que especifica cada unidade de entrada.
4. Esta coluna puxa a estatística/campo de alavancagem do cenário selecionado (neste caso, nº 6), que são todos exibidos à direita (em azul). A fórmula necessária para conduzi-lo é uma função de deslocamento, ou seja, “=OFFSET (inserir célula vazia imediatamente à esquerda do primeiro cenário destacado em vermelho acima,, Célula onde o cenário está selecionado/destacado)”. Observe que há um espaço vazio entre as duas células para que as duas vírgulas (,,) não sejam um erro de digitação.
5. Agrupe suas suposições em macrocategorias e subcategorias. Isso ajudará você (o modelador) e seus usuários a obter uma compreensão clara de qual cenário o modelo selecionou.

Exemplo de uma Tabela de Sensibilidade de Amostra

Guia de saída

As guias de saída são as guias que os operadores do modelo usarão com mais frequência. Ao longo dos anos, me vi inclinado para pelo menos três guias de saída para modelos médios a complexos:

1. Guia de Resultados Financeiros: Este é um resumo abreviado das finanças detalhadas na guia do modelo. Geralmente são apresentados anualmente (mesmo que o modelo possa ser trimestral). Essa saída deve ter entre 50 e 150 linhas e deve apresentar todos os principais itens de linha das guias de cálculo. Certifique-se de apresentar detalhes suficientes para garantir que os usuários não alternem entre esta guia e várias guias de Cálculos. Observe também que, como prática recomendada, nenhuma guia de saída deve refazer qualquer cálculo e essas informações devem incluir apenas links diretos em.
2. Guia Resumo Executivo: Esta guia é bastante padrão e geralmente apresenta uma mistura de gráficos, tabelas e tabelas, ilustrando, da forma mais simples e fácil de digerir possível, as várias tendências, análises e estatísticas-chave de resumo que executivos e membros do conselho precisam para navegar em seus decisões-chave.
3. Guia de Saída Específica: Esta guia contém saídas específicas, geralmente ditadas pelo modelo do memorando de investimento, a apresentação do comitê de investimento ou solicitações de executivos e membros do conselho conforme necessário para chegar a seus pontos de decisão.

Exemplo de uma guia de saída de modelo, incluindo tabelas, gráficos e gráficos

Neste momento, a fase de construção do modelo está oficialmente concluída. Podemos voltar nossa atenção para algumas das melhores práticas de modelagem de nível de especialista que mencionei no início do artigo. Vamos começar com a formatação.

Formatando seu modelo

Em primeiro lugar, é importante notar que cada empresa/grupo pode ter suas próprias preferências ou práticas internas. Como tal, durante a construção, é importante primeiro verificar - e aderir - a qualquer formato que sua respectiva empresa prescreve. Na ausência de práticas específicas da empresa, no entanto, o conteúdo abaixo detalha a linguagem universal de Wall Street para a formatação de um modelo.

O primeiro e mais simples método de formatação para modelagem financeira é usar esquemas de cores consistentes e identificáveis ​​para denotar diferentes tipos de células e dados. Do seguinte modo:

Azul = Entradas ou quaisquer dados codificados, como valores históricos, suposições e drivers.

Preto = Fórmulas, cálculos ou referências derivadas da mesma planilha.

Verde = Fórmulas, cálculos e referências a outras planilhas (observe que alguns modelos pulam esta etapa completamente e usam preto para essas células).

Roxo = Links, entradas, fórmulas, referências ou cálculos para outros arquivos do Excel (mais uma vez, observe que alguns modelos pulam essa etapa completamente e também usam preto para essas células).

Vermelho = Erro a ser corrigido.

Exemplo de resumo financeiro bem formatado (codificado por cores)

Observe que não há funcionalidade de automação integrada para codificar por cores suas planilhas do Excel de acordo com os padrões universais de codificação de cores acima. Em vez disso, você pode criar sua(s) própria(s) macro(s) para alcançar esses resultados e, posteriormente, criar combinações de atalhos para codificar automaticamente seu trabalho com cores.

Em algum momento do meu passado recente, recebi de um colega (a quem agradeço até hoje), as seguintes macros (incluindo instruções detalhadas), que desde então me pouparam várias horas de trabalho manual. Gostaria de compartilhá-los, se me permite.

Instruções de criação de macros (para versões Mac e PC do Excel):

1. Pressione Alt + W + M + R, simultaneamente, para nomear e começar a gravar sua macro.
2. Aperte F5 (“Jump to Cell”) e depois Alt + S, simultaneamente, para chegar ao menu “Go to Special”.
3. Pressione “O” para selecionar Constantes e “X” para desmarcar o texto.
   
4. Agora pressione Alt + H + FC (ou Ctrl + 1), simultaneamente, e selecione a cor da fonte azul para essas constantes.
5. Pressione Esc.
6. Agora faça a mesma coisa, começando com F5, mas selecione Fórmulas (F) em vez de constantes e pressione “X” para desmarcar o texto.
7. Agora pressione Alt + H + FC (ou Ctrl + 1) e selecione uma cor de fonte preta para essas constantes.
8. Pare de gravar a macro com Alt + W + M + R ou Alt + T + M + R.

Links para outras pastas de trabalho

Encontrar links para outras pastas de trabalho e planilhas é complicado, e você provavelmente terá que usar o VBA para que isso funcione corretamente. Aqui está a ideia básica: procure a presença do símbolo “!” em cada célula que contém uma fórmula em sua pasta de trabalho e, em seguida, altere a cor da fonte para verde. Você precisará modificar isso no Editor VBA e torná-lo um for each loop em todas as instâncias de “!” você encontra e, em seguida, altere a cor da fonte para cada um deles.

Esteja ciente de que esse atalho ainda não funcionará 100% do tempo porque algumas fórmulas farão referência a células em outras planilhas sem vincular diretamente a elas. Felizmente, as células verdes são mais raras do que as células pretas ou azuis, portanto, o método acima funciona muito bem na maioria dos modelos (e você pode formatar organicamente o restante de seus links para outras planilhas manualmente à medida que surgem ou à medida que você as encontra).

Práticas recomendadas para auditar um modelo

Ao modelar, eu o encorajo a sempre ter esta única pergunta no fundo de sua mente: “Estou tornando este modelo facilmente auditável?” porque para cada tarefa executada, fórmula criada e link construído, sempre haverá uma maneira mais rápida e “mais suja” (no jargão da indústria) de fazer o trabalho. Esses truques e truques, por mais inteligentes que possam parecer no momento, e especialmente após intervalos de tempo, invariavelmente serão esquecidos e levarão a erros difíceis de rastrear. Manter um revisor de terceira pessoa em mente o guiará pelo processo e o ajudará a tomar a decisão certa em momentos-chave.

Abaixo estão uma série de práticas recomendadas sobre como construir com uma mentalidade de auditor. Do seguinte modo:

1. Uma linha, uma fórmula

Você deve ter apenas uma fórmula por linha, o que significa que qualquer fórmula usada na primeira célula de qualquer linha deve ser a mesma fórmula aplicada uniformemente em toda a linha. Os usuários devem entender a estrutura do seu modelo observando a primeira célula de cada linha à medida que avançam verticalmente no seu modelo.

Embora isso seja simples em princípio, é violado com frequência suficiente para destacar ainda mais. Um exemplo comum geralmente ocorre quando as planilhas são divididas entre um grupo de colunas de "financeiros históricos" e "previsões para o ano anterior" (veja a imagem acima intitulada "Exemplo de resumo financeiro bem formatado (codificado por cores)", como referência ).

Uma maneira fácil de lidar com essas instâncias é o uso de sinalizadores (por exemplo, 1/0, TRUE / FALSE ) posicionados na parte superior da planilha e, em seguida, referenciados usando instruções IF no corpo do modelo. Uma ilustração simples disso no trabalho é a seguinte:

Exemplo de "O uso de sinalizadores" na modelagem do Excel

2. Não há números codificados embutidos nas fórmulas

Nunca use números codificados embutidos em fórmulas porque eles são muito difíceis de identificar se o usuário estiver menos familiarizado com o modelo. Em vez disso, destaque claramente e separe as entradas/hard-codes das fórmulas; melhor ainda, reúna todas as entradas/hard-codes (conforme apropriado) e agregue-os na mesma guia. Em seguida, faça com que suas fórmulas extraiam/referenciem-nas conforme apropriado da célula necessária e da guia apropriada.

3. Simples é sempre melhor

É sempre melhor evitar fórmulas complicadas. Em vez disso, divida sua fórmula em etapas facilmente digeríveis. Em vez de uma linha aparentemente organizada, essa abordagem geralmente cria muito mais linhas, resultando em uma planilha maior; mas que será muito mais fácil de seguir e auditar por terceiros.

4. Adira de forma consistente à sua convenção de sinais

Você deve decidir no tempo zero qual será sua convenção/chave de sinais. A título de ilustração, pergunte-se na fase de projeto do seu modelo: "Os custos, despesas, deduções, depreciação, CapEx, etc. serão apresentados como números negativos ou positivos?" Minha preferência pessoal é sempre apresentar os custos como números negativos por dois motivos: (a) os totais serão sempre somas diretas e você minimizará o erro do usuário, e (b) será mais fácil identificar erros usando apenas os sinais.

5. Evite nomear suas células, em vez disso, confie na lógica de grade do Excel

Sempre que possível, recomendo evitar nomear suas células, pois fica difícil localizar a entrada de origem para a referida célula nomeada (por exemplo, “Inflação”) no futuro. Em vez disso, recomendo que você confie na convenção de grade do Excel em suas fórmulas (por exemplo, simplesmente vinculando à célula C4 ou local, [Tab Name]l'!G21 , se a referência estiver em uma guia ou pasta de trabalho diferente).

6. Nunca tenha a mesma entrada em vários locais

Organize suas entradas de forma simples e transparente. É minha recomendação que você consolide todas as entradas em algumas guias de driver e faça referência a elas a partir de seus pontos de origem singulares em toda a planilha.

7. Evite vincular arquivos

Evite vincular a outros arquivos. É melhor inserir os dados relevantes necessários de um arquivo diferente como entradas codificadas, que você atualiza manualmente conforme necessário. A ligação cruzada é conhecida por travar modelos maiores do Excel ou atualizar de forma inconsistente, criando assim erros difíceis de rastrear.

8. Não oculte planilhas ou linhas

Em planilhas mais longas, “agrupe” linhas/colunas em vez de “escondê-las”.

9. Menos guias maiores são melhores do que várias guias menores

Esta prática é baseada 100% na experiência. É mais fácil seguir e auditar uma matriz contínua de dados em uma planilha grande e contígua do que em várias guias ou, pior ainda, em várias planilhas com links cruzados.

10. Crie verificações em todo o seu modelo por meio de "Verificações de erros agregadas" localizadas em uma guia

As verificações são a maneira mais fácil de revisar rapidamente a integridade de um modelo. Os “cheques” abrangem tudo, desde garantir que os totais que deveriam estar empatados realmente estejam empatados até garantir que o balanço patrimonial de uma pessoa esteja realmente equilibrado. Normalmente, crio algumas verificações na parte superior ou inferior de cada planilha e as consolido em uma "guia de verificação" separada. Isso garante que seja fácil encontrar um erro no modelo e rastrear onde esse erro se originou.

Amostra de um Balanço Patrimonial "Cheque"

Observe que confiar apenas em verificações para verificar a integridade de um modelo nunca é uma boa ideia, pois as verificações geralmente são de alto nível. Mas é um bom ponto de partida.

Carve Out – Para usuários avançados: Dicas do Excel

Esta seção abrange algumas práticas recomendadas do Excel muito eficazes para nossos usuários mais avançados. Isso pode exigir um pouco de ajuste, mas deve economizar várias horas de trabalho mais tarde e ser relativamente simples de implementar. Eles são os seguintes, em resumo, balas sucintas e diretas:

1. Use tantos atalhos de teclado quanto possível. Existem vários arquivos na internet sobre atalhos de teclado do Excel que atendem a diferentes necessidades. Vou citar alguns aqui:
   • Folha de dicas da chave de modelagem
   • Lista abrangente de dicas do Excel
2. Use F5 (“ir para especial”) para localizar rapidamente todos os números ou fórmulas codificados.
3. Use Trace Precedentes e Trace Dependentes para auditar o modelo.
4. Use XNPV e XIRR para permitir a aplicação de datas custom__ aos fluxos de caixa, a caminho de uma análise de retornos; isso, ao contrário das funções NPV e IRR do Excel, que pressupõem implicitamente intervalos de tempo equidistantes para o cálculo.
5. Use a função INDEX MATCH sobre a função VLOOKUP para pesquisar informações em planilhas grandes.
6. VLOOKUP é quase sempre superior a instruções IF ; fique confortável com isso.
7. Adquira o hábito de incluir IFERROR na sintaxe de suas fórmulas.
8. Use uma combinação da função de data, EOMONTH e instruções IF para tornar as datas dinâmicas.
9. Remova linhas de grade ao apresentar ou compartilhar o modelo financeiro; ele cria um documento de saída mais limpo e polido.

Ame-o ou odeie-o…

Ame ou odeie, o Excel é onisciente, onipresente e onipotente quando se trata de finanças corporativas, análise e tomada de decisões orientada por dados. E acredite ou não, não precisa ser intimidante ou doloroso, mesmo para iniciantes ou não iniciados. Como a maioria das coisas na vida, prática, consistência e atenção aos detalhes (no caso do Excel, atalhos) o levarão até lá.

Depois de se familiarizar com o aplicativo, você descobrirá que ele é uma ferramenta poderosa de produtividade e narrativa numérica, com a qual você poderá funcionar escassamente, mesmo em sua vida pessoal. À medida que você progride pelos vários estágios da fluência no Excel, desejo o melhor e o encorajo a manter este artigo como um guia prático que você consulta com frequência.