Como criar verificações de erro em seu modelo financeiro do Excel - manequins

Mesmo que você apenas tenha começado a modelar, você provavelmente está ciente de como é fácil cometer um erro em um Modelo financeiro! Existem três maneiras de evitar erros em um modelo financeiro:

• Evite cometer os erros em primeiro lugar. Neste livro, descrevo várias técnicas que você pode empregar para evitar cometer erros em primeiro lugar, como ser consistente com suas fórmulas.
• Verifique o modelo para erros. Apesar de seus melhores esforços, os erros inevitavelmente podem se deslizar, então verifique, verifique novamente e peça a outra pessoa que verifique seu modelo depois que estiver completo.
• Incluir verificações de erro. Como você está construindo o modelo, inclua verificações de erro que impedem erros inadvertidos de escorregar para o modelo devido a entradas incorretas ou erro do usuário.

A informação aqui se concentra nos dois primeiros pontos: técnicas para construção de modelos para reduzir erros, bem como formas de verificar o modelo de erros.

As verificações de erro são uma parte crítica de um modelo financeiro bem construído para que o usuário ou modelo possa ver de relance se as fórmulas estão sendo calculadas corretamente. Por exemplo, ao criar relatórios de gerenciamento, verifique se a soma do relatório de cada departamento agrega ao total da empresa. Isso pode ser feito inserindo uma função IF simples, entre outros métodos.

No exemplo mostrado aqui, um orçamento de capital foi construído com datas estimadas de gastos na coluna E. Na tabela de gastos de capital mostrada nas colunas F a Q, os gastos são distribuídos ao longo do ano completo. O modelador sabe que o valor total de capital gasto de US $ 124, 700 mostrado na célula D17 deve ser o mesmo que o valor total do cronograma de capital mostrado na célula R17 e, se os dois valores não forem iguais, o modelo não está calculando adequadamente. Portanto, a célula de verificação de erros E1 contém a fórmula muito simples = R17-D17.

Uma verificação de erro simples.

Abaixo, você pode ver que um usuário inseriu um valor incorreto na célula E4. 20 de fevereiro não é uma entrada válida, porque o cronograma de gasto de capital só permite datas em 2019. Isso significa que o modelo financeiro abaixo está incorreto - o usuário entrou US $ 124, 700 de capital no modelo, mas apenas US $ 115, 700 foi alocado ao longo do ano. O número que mostra na célula E1 (9 000) alerta o usuário para o fato de que existe um problema. Baixe o arquivo 0401. xlsx e selecione as abas marcadas 4-9 e 4-10 para tentar ativar essa verificação de erro por você mesmo.

Uma verificação de erro foi acionada.

Esta verificação de erro é muito simples e bastante discreta.Para torná-lo mais óbvio, você pode preferir incluir a descrição "verificação de erro" ao lado da célula de verificação de erro na célula D1, o que tornaria mais óbvio para o usuário o que aconteceu quando a verificação de erro é acionada.

A verificação de erro mostrada acima é um método preferido de verificação de erros, porque é tão simples e rápido de construir. Como retorna um valor no caso de um erro, pode ser um pouco discreto demais para seus gostos - ele não necessariamente alerta imediatamente o usuário de que um erro foi feito. No entanto, é certamente rápido e fácil de seguir e, por esse motivo, uma seleção de erro bastante comum, favorecida por muitos modeladores.

Se você usar esse tipo de verificação de erro, formata-o usando o estilo Comma (encontrado na guia Início no grupo Números) e remova a casa decimal e formata-a com uma fonte vermelha. Desta forma, o zero não mostrará se não houver erro, e um número vermelho mostrará se houver um erro.

Alternativamente, você pode preferir a outra fórmula de verificação de erros, como = D17 = R17, que retornará o valor VERDADEIRO se forem iguais ou FALSO se não estiverem. No entanto, esse método também está sujeito a um erro falso.

Permitir tolerância ao erro

= IF (D17R17, "erro", 0) é uma verificação de erro superior, mas, de vez em quando, pode retornar um erro falso, mesmo que os valores sejam os mesmos. Este "erro" em uma verificação de erro é causado pelo fato de o Excel carregar cálculos para 14 casas decimais. Depois disso, trunca o valor e pode causar uma pequena discrepância, que irá denunciar um erro quando apenas 0 00000000000001 estiver desligado.

Para evitar esse problema potencial, você poderia usar uma fórmula de valor absoluto, o que permitiria uma tolerância ao erro. = IF (ABS (D17-R17)> 1, "erro", 0) permitirá que os valores sejam desativados em US $ 1 antes de relatar um erro. Se você usar a função ABS no Excel, isso terá o valor absoluto do resultado, por isso não importa se é um número positivo ou negativo.

Existem muitas variações desta fórmula. Alguns modeladores preferem mostrar a palavra OK se os números estiverem certos e Verificar se não estiverem.

Aplicando formatação condicional a uma verificação de erro

Para tornar a verificação de erro ainda mais prominente para o usuário, considere usar formatação condicional para adicionar uma regra que torne a célula inteira ficar vermelha se a verificação de erro for disparada. Na guia Início da Faixa de opções, no grupo Estilos, clique no botão de formatação condicional. Em seguida, passe o mouse sobre Highlight Cells Rule e selecione Equal To.

Aplicando formatação condicional a uma verificação de erro.

Quando a caixa de diálogo Equal To aparecer, digite a palavra erro nas caixas Format Cells que são iguais a e clique em OK. Por padrão, ele transformará a célula em Vermelho claro com texto vermelho escuro, mas você pode mudar isso na caixa suspensa.

Editando a cor de formatação condicional.

A formatação condicional é popular nas verificações de erro porque faz com que a verificação de erro se destaque mais quando foi acionada. E, com alguma sorte, o usuário perceberá que há um erro no modelo financeiro.A formatação condicional não se limita às verificações de erro - pode ser útil para outros modelos onde você deseja chamar a atenção do usuário para anormalidades ou destaques nos cálculos.