Gráfico Waterfall (em cascata) na Análise de Resultados – Horizontal e Vertical

Oi Pessoal!

Estou escrevendo esse post para mostrar como podemos utilizar o gráfico waterfall na análise Horizontal e Vertical das demonstrações financeiras, que são relatórios contábeis que apoiam a tomada de decisão nas empresas. Os demonstrativos mais importantes são: Demonstração de Resultados (DRE), Balanço Patrimonial e Fluxo de Caixa.

Nos exemplos abaixo, usarei apenas linhas da DRE.

Obs: vocês podem baixar o arquivo de suporte ao post, com os gráficos abaixo, no link Downloads ao lado – Gráfico Waterfall (em cascata).

Vamos lá…

Gráfico Waterfall (em cascata)

Podemos dizer que é um gráfico de colunas empilhadas, onde as séries do gráfico são compostas por uma base “invisível” e uma parcela visível que mostra a evolução / variação, por exemplo, de um item da demonstração financeira.

Análise Horizontal

A análise horizontal é utilizada na verificação da evolução no tempo dos itens das demonstrações financeiras, buscando determinar tendências e avaliar o atingimento de metas planejadas. Com esta análise podemos comparar os resultados contábeis obtidos durante o decorrer do ano com o orçamento projetado para o respectivo período.

Como exemplo, criei o waterfall abaixo para mostrar o impacto das variações de cada uma das linhas da DRE na comparação do resultado orçado com o resultado real de um período qualquer.

A partir deste gráfico conseguimos avaliar como as linhas da DRE impactaram o resultado apurado, ou seja, quais Ganhos e Perdas influenciaram no atingimento ou não da meta estabelecida (resultado orçado – primeira coluna).

Considerar as variações de receita e despesas na comparação orçado versus real como Perdas e Ganhos, significa que um aumento de receita, ou Ganho de Resultado, será representado no gráfico por uma área azul aumentando o patamar da sequência das colunas, e que uma queda da receita aparecerá como uma área vermelha diminuindo esse mesmo patamar. Para as despesas, o conceito válido é o inverso, ou seja, um aumento representa uma Perda de Resultado e uma queda de despesa (economia) um Ganho de Resultado.

Atenção: os valores de receita e despesas deste gráfico correspondem as variações do orçado vs real de cada uma das linhas. Ou seja, se orçamos R$ 100 milhões de receita e apuramos como real R$ 135 milhões, tivemos um Ganho de Resultado de +R$ 35 milhões, que aparecerá no gráfico como uma área azul no valor de R$ 35 Milhões.

Análise Vertical

Quanto à análise vertical, usamos para ter ideia da representatividade de um item da demonstração financeira em relação a outro que estabelecemos como parâmetro. Por exemplo, podemos verificar qual é o percentual gasto com despesa de pessoal em relação ao valor total de despesas de um determinado período.

A aplicabilidade dessa análise pode ser melhor visualizada no waterfall abaixo que criei como exemplo.

Este gráfico mostra a representatividade de cada uma das despesas em relação a receita usada como parâmetro, ou seja, partindo de 100% da receita, conseguimos avaliar quanto cada uma das “nossas despesas” consumiu até obtermos o resultado apurado, que nada mais é que a receita diminuída das despesas.

Além de ser útil para verificarmos se os patamares de despesas apurados (realizados) estão de acordo com o que planejamos, esse tipo de análise é muito interessante, por exemplo, para compararmos os gastos de produtos e áreas similares ou mesmo para efetuarmos benchmark com a concorrência, buscando mensurar a nossa eficiência operacional.

Abs, Dr. Excel

Estrutura Do … Loop (While, Until e Wend) – VBA

Oi pessoal!

Conforme prometido no post anterior, vamos falar da estrutura Do…Loop!

Utilizamos essa estrutura para repetir um trecho de código enquanto uma determinada condição for verdadeira, ou até que uma determinada condição torne-se verdadeira. Podemos usar dois operadores condicionais diferentes, que podem aparecer no início ou no final do trecho de código, são eles: While e Until. Com isso, temos quatro formas distintas de escrever o código, conforme veremos nos exemplos a seguir.

Essa estrutura nos permite, por exemplo, criar um código determinando que o VBA percorra as células de uma coluna, preenchendo uma fórmula em todas as células até encontrar a primeira célula vazia (“”) ou até encontrar um determinado valor.

Nos exemplos a seguir, observe que o código dentro da estrutura será executado enquanto a condição (Contador <= x) for verdadeira. Quando a condição for falsa, o primeiro comando após o laço será executado (MsgBox “Soma = ” & Soma).

Importante: o código dentro da estrutura deve ser capaz de alterar a condição de verdadeira para falsa. Caso contrário, sendo a condição sempre verdadeira, os comandos dentro da estrutura serão executados infinitamente ou até o programa travar!

Agora, vamos aos exemplos das quatro formas de escrever o código dessa estrutura! Reparem que em todas obtemos o mesmo resultado.

 

Exemplo de código com While no início do trecho de código:

Sub Do_Loop_While_no_início()

Dim x, Contador, Soma

x = 10

Contador = 1

Soma = 0

‘ Efetua a soma dos dez primeiros números maiores que zero.

Do While Contador < x

Soma = Soma + Contador

Contador = Contador + 1

Loop

MsgBox “Soma = ” & Soma

End Sub

Exemplo de código com While no final do trecho de código:

Sub Do_Loop_While_no_final()

Dim x, Contador, Soma

x = 10

Contador = 1

Soma = 0

‘ Efetua a soma dos dez primeiros números maiores que zero.

Do

Soma = Soma + Contador

Contador = Contador + 1

Loop While Contador < x

MsgBox “Soma = ” & Soma

End Sub

Nos exemplos a seguir, observe que o código dentro da estrutura será executado enquanto a condição (Contador > x) for falsa. Quando a condição for verdadeira, o primeiro comando após o laço será executado (MsgBox “Soma = ” & Soma).

Importante: o código dentro da estrutura deve ser capaz de alterar a condição de falsa para verdadeira. Caso contrário, sendo a condição sempre verdadeira, os comandos dentro da estrutura serão executados infinitamente ou até o programa travar!

Exemplo de código com Until no início do trecho de código:

Sub Do_Loop_Until_no_início()

Dim x, Contador, Soma

x = 10

Contador = 1

Soma = 0

‘ Efetua a soma dos dez primeiros números maiores que zero.

Do Until Contador >= x

Soma = Soma + Contador

Contador = Contador + 1

Loop

MsgBox “Soma = ” & Soma

End Sub

 

Exemplo de código com Until no final do trecho de código:

Sub Do_Loop_Until_no_final()

Dim x, Contador, Soma

x = 10

Contador = 1

Soma = 0

‘ Efetua a soma dos dez primeiros números maiores que zero.

Do

Soma = Soma + Contador

Contador = Contador + 1

Loop Until Contador >= x

MsgBox “Soma = ” & Soma

End Sub

Essa é uma das estruturas que mais uso no desenvolvimento de relatórios de acompanhamento de resultados em Controladoria e Finanças. Mas na prática, acabo usando o laço While … Wend, que é equivalente ao primeiro exemplo acima!

Tentem, por exemplo, executar o código abaixo. Com certeza vocês visualizarão um caixa de mensagem com a soma dos dez primeiros números maiores que zero, que é igual a 45!

 

 

Sub While_Wend()

Dim x, Contador, Soma

x = 10

Contador = 1

Soma = 0

‘ Efetua a soma dos dez primeiros números maiores que zero.

While Contador < x

Soma = Soma + Contador

Contador = Contador + 1

Wend

MsgBox “Soma = ” & Soma

End Sub

Abs Dr. E

P.S. Gostaria de pedir desculpas, mas infelizmente não estou conseguindo responder todos os e-mails com dúvidas que venho recebendo. Por isso, peço que não usem o espaço de comentários do post para enviar dúvidas.

Estrutura If … Then (ElseIf, Else, End If) e Operadores Comparativos no VBA

O Pessoal!

Neste post vou falar da estrutura If…Then, que usamos para testar condições lógicas. Com certeza, essa é a parte do VBA que mais usei até hoje para automatizar minhas tarefas em Controladoria e Finanças!

Com esta estrutura podemos efetuar comparações entre os valores de duas ou mais variáveis, determinando no código que o VBA execute Funções ou comandos a partir do resultado de um teste lógico.

Podemos, por exemplo, determinar que o VBA formate o fundo de uma célula com vermelho, caso o valor preenchido nesta célula seja menor que zero.

 

A tabela abaixo contém operadores comparativos que podemos usar nessa estrutura.

 

Exemplo de código:

 

Sub Estrutura_If_Then()

‘ Declaração de variáveis.

Dim x As Integer

Dim y As Integer

x = 5

y = 3

‘ Teste lógico.

If x > y Then

‘ Comandos a serem executados quando x for maior que y.

MsgBox “x é maior que y.”

Else

‘ Comandos a serem executados quando x for menor ou igual a y.

MsgBox “x é menor que y.”

End If

End Sub

 

A estrutura acima é formada por um único teste lógico: x > y. Se esta condição for atendida, o VBA mostrará a mensagem “x é maior que y”. Caso contrário, mostrará a mensagem “x é menor que y”, independentemente do valor de x.

Neste exemplo, sendo x igual a y, teremos um problema, pois ambas mensagens não corresponderão ao resultado correto. Aqui, o ideal seria trabalharmos com três hipóteses: x > y, x < y ou x = y. No entanto, precisaríamos incluir mais uma condição à estrutura…

E o que é interessante e que justifica a utilização desta estrutura na criação de planilhas inteligentes, com validações, cálculos, formatações etc. complexos, é que podemos efetuar quantos testes forem necessários. Para isso, basta incluirmos testes adicionais utilizando ElseIf na estrutura.

 

Concluindo, quando trabalhamos com testes lógicos, utilizamos If (no primeiro teste), ElseIf (nos testes intermediários) e Else, por último, para cobrir todas as possibilidades de resultados diferentes dos esperados nos testes efetuados.

Para melhor entender o significado do Else, podemos dizer que sempre que os testes lógicos da estrutura não forem atendidos, o VBA executará os comandos após o Else.

Podemos exemplificar, alterando o código anterior da seguinte forma:

Sub Estrutura_If_Then()

‘ Declaração de variáveis.

Dim x As Integer

Dim y As Integer

x = 5

y = 3

‘ Teste lógico.

If x > y Then

‘ Comandos a serem executados quando x for maior que y.

MsgBox “x é maior que y.”

ElseIf x < y Then

‘ Comandos a serem executados quando x for menor que y.

MsgBox “x é menor que y.”

Else

‘ Comandos a serem executados quando x assumir qualquer outro valor.

MsgBox “x não é maior nem menor que y.”

End If

End Sub

Como sabemos que x só pode assumir três valores distintos em relação a y, poderíamos substituir o Else por ElseIf x = y Then.

Importante: para que o VBA entenda essa estrutura, ela deve conter obrigatoriamente If (teste lógico) Then e End If. Os intermediários ElseIf e Else podem ser usados de acordo com suas necessidades de testes lógicos, mas não são obrigatórios!

Abs, Dr. E

P.S. Gostaria de pedir desculpas, mas infelizmente não estou conseguindo responder todos os e-mails com dúvidas que venho recebendo. Por isso, peço que não usem o espaço de comentários do post para enviar dúvidas.

Editor VBA (destaque: propriedade plan Visible)

Oi Pessoal!

Neste post vou mostrar a “cara” do Editor VBA!

Ao entrar no editor, que pode ser acessado clicando Alt+F11 no teclado, visualizamos as janelas do VBA, conforme figura abaixo.

 

 

No lado esquerdo da tela temos duas janelas:

 

Projeto – VBA Project: nessa janela são exibidos os vários elementos que fazem parte da pasta de trabalho atual. Aqui aparecem as planilhas e módulos da pasta de trabalho (arquivo.xls) carregada no Excel. Uma Macro pode ser criada em uma determinada planilha ou em Módulos. Estes últimos são utilizados para criar Funções e Procedimentos que podem ser chamados em todas as planilhas da pasta de trabalho atual.

Janela Propriedades: A janela abaixo da janela Project exibe as propriedades do elemento selecionado na janela Project. Por exemplo: ao selecionar Plan1 na janela Project, serão exibidas as propriedades desta planilha na janela logo abaixo, janela Propriedades, conforme figura a seguir.

Uma das propriedades das planilhas do Excel que vale a pena destacar aqui, é a propriedade Visible (abaixo em azul).

Esta propriedade possui três valores:

-1 – xlSheetVisible

0 – xlSheetHidden

2 – xlSheetVeryHidden

Os dois primeiros, -1 e 0, podemos acessar diretamente pelo Excel, correspondendo às opções Exibir e Ocultar planilhas. Já o terceiro (2 – xlSheetVeryHidden), só pode ser selecionado através do VBA e permite que ocultemos uma planilha sem que mesma seja visualizada pelo usuário de Excel como planilha Oculta! Ou seja, o usuário só percebe a existência da planilha entrando no VBA.

Por isso, acho que essa propriedade pode ajudar bastante o pessoal de Controladoria e Finanças, que trabalha com confidencialidade de informações, já que para acessar determinada planilha marcada com a propriedade 2, xlSheetVeryHidden, você precisaria acessar o editor do VBA, que, na minha opinião, possibilita maior segurança que os bloqueios do Excel.

Abs, Dr. E

P.S. Gostaria de pedir desculpas, mas infelizmente não estou conseguindo responder todos os e-mails com dúvidas que venho recebendo. Por isso, peço que não usem o espaço de comentários do post para enviar dúvidas.

Teclas de Atalho no Excel

Oi Pessoal!

Segue abaixo uma lista com alguns dos atalhos que considero importantes para usuários avançados de Excel, como o pessoal de Controladoria e Finanças!

Parece besteira, mas a troca do mouse pelo teclado faz muita diferença, principalmente para quem utiliza o Excel diariamente nas suas oito horas de trabalho. Você realmente consegue produzir mais em menos tempo. Mas é preciso ter esses atalhos na ponta da língua, ou melhor, na ponta dos dedos!

Essa é uma das coisas que as versões 2007 e 2010 do Excel apresentaram de melhoria em relação à versão 2003. A forma como você acessa os menus/ comandos a partir do teclado. Nessas duas últimas versões, reparem que ao selecionar a tecla Alt os menus do Excel ficam identificados por letras. Então, basta ir clicando nas letras até encontrar o comando que você precisa.

Bom, como os comandos do Excel podem ser acessados conforme citei acima, procurei incluir nessa lista alguns atalhos que considero indispensáveis e que não são acessados pelo formato acima (tecla Alt + letra de acesso ao menu desejado).

Abrir arquivo CTRL+A

Colar CTRL+V

Copiar CTRL+C

Desfazer CTRL+Z

Exibir Auto-filtro da coluna atual ALT+seta para baixo

Fechar Auto-filtro da coluna atual Esc

Fechar janela CTRL+W

Fechar o Excel Alt+F4

Formatar células CTRL+1

Imprimir CTRL+P

Inserir Gráfico F11

Inserir linhas ou colunas SHIFT+CTRL+ +

Inserir planilha SHIFT+F11

Ir para F5

Ir para o final da planilha CTRL+End

Ir para o início da planilha CTRL+Home

Localizar e substituir CTRL+F

Mover para a planilha anterior CTRL+PgUp

Mover para a próxima planilha CTRL+PgDn

Nova pasta de trabalho CTRL+O

Recortar CTRL+X

Selecionar a planilha atual e a anterior SHIFT+CTRL+PgUp

Selecionar a planilha atual e a seguinte SHIFT+CTRL+PgDn

Selecionar as células que contêm fórmulas com referência direta à célula ativa CTRL+]

Selecionar as células que contêm fórmulas com referência direta ou indireta à célula ativa CTRL+SHIFT+}

Selecionar as células visíveis na seleção atual ALT+;

Selecionar até a última célula usada CTRL+SHIFT+End

Selecionar até o início da linha SHIFT+Home

Selecionar até o início da planilha CTRL+SHIFT+Home

Selecionar até última célula não vazia na mesma linha ou coluna CTRL+SHIFT+tecla de direção

Selecionar célula a célula SHIFT+Seta

Selecionar coluna ativa CTRL + barra de espaços

Selecionar linha ativa SHIFT + barra de espaços

Selecionar tudo CTRL+T

Selecionar uma tela para baixo SHIFT+PgDn

Selecionar uma tela para cima SHIFT+PgUp

Gostaria de destacar o atalho Alt+; (Selecionar as células visíveis na seleção atual). Acho esse atalho uma das melhores ferramentas para incluirmos fórmulas, alterarmos formatações etc. em apenas algumas células de uma determinada seleção.

Abs, Dr. E

P.S. Gostaria de pedir desculpas, mas infelizmente não estou conseguindo responder todos os e-mails com dúvidas que venho recebendo. Por isso, peço que não usem o espaço de comentários do post para enviar dúvidas.

Análise de Resultados! (fórmulas: Indireto, SomarProduto e SomaSe)

Oi Pessoal!

Recebi um comentário no post Índice+Corresp (PROCV mais eficiente!), enviado pelo Rafael Ovelha, falando sobre a fórmula Indireto. Após pesquisar melhor sobre essa fórmula em alguns fóruns, resolvi escrever esse post.

Criei uma planilha usando as fórmulas Indireto, SomarProduto e SomaSe, onde montei uma tabela resumo de despesas por período, com classificação por natureza (tipo de despesa). Quem fizer o download (vide * abaixo) do arquivo vai perceber que se trata de uma tabela simples, onde procurei demonstrar, de forma clara e objetiva, como criar planilhas inteligentes para análise/ controle de resultados.

É claro que podemos elaborar algo bem mais completo e complexo, entretanto, meu principal objetivo aqui no blog é dar uma ideia resumida de Excel avançado e VBA, com exemplos simples, mas que reflitam as atividades do pessoal de Controladoria/ Finanças voltadas para planejamento e controle de orçamento.

Então, vou deixar sempre a parte do “completo e complexo” por conta da criatividade e necessidade individual de cada um dos leitores que acompanham o blog!

* Vocês podem efetuar o download da planilha de apoio clicando aqui!

Bom, vamos começar pela fórmula SomaSe, que na minha opinião é a mais fácil das três.

Usando a tabela abaixo como exemplo, podemos obter o valor total das despesas classificadas como ESCRITÓRIO (na coluna B) no mês de fevereiro (coluna D), escrevendo o seguinte:

=SOMASE(B:B;”ESCRITÓRIO”;D:D)


Nesta expressão, pedimos ao Excel que efetue a soma de todos os valores da coluna D, que foram classificados como “ESCRITÓRIO” na coluna B. Em outras palavras, é como se a fórmula percorre-se toda a coluna B, desde a primeira linha, e considerasse na soma todas as despesas com escritório.

Neste exemplo, o resultado será 8.300, correspondente a soma dos seguintes valores: 5.000 + 1.500 + 1.200 + 600.

Agora vamos entender como funciona a fórmula SomarProduto, calculando o total de despesas com COMISSÕES nos meses de janeiro e fevereiro, conforme figura abaixo.

Para isso, devemos escrever o seguinte:

=SOMARPRODUTO(C1:E1;C9:E9)

Como o próprio nome sugere, a fórmula SomarProduto efetua a soma dos produtos dos intervalos de células que a compõe. Simplificando, podemos dizer que o Excel multiplica cada célula do primeiro range por cada célula do segundo range (células que estejam na mesma ordem nos dois intervalos) e, no final, soma todos os resultados das multiplicações. Assim, podemos concluir que, necessariamente, os dois ranges da fórmula devem possuir o mesmo tamanho.

Para este exemplo, a fórmula acima é equivalente à seguinte expressão matemática:

1×1.500 + 1×100 + 0x1.500 = 1.600

Agora sim, vamos falar da fórmula Indireto!

Quando procurei por essa fórmula nas funções do Excel, encontrei o seguinte texto descritivo: “Retorna uma referência indicada por um valor de texto”.

A partir daí, tive a ideia de usar esta fórmula para montar uma tabela comparativa de despesas por período, vinculando a escolha do mês de análise à posição (coluna) deste mês na planilha.

Exemplificando, como podem reparar na tabela abaixo, o mês de fevereiro encontra-se na coluna G. Então, para obtermos a soma de todos os valores desta coluna, basta escrevermos =SOMA(G:G).

Mas como efetuar uma soma dinâmica, onde poderemos alterar o mês de análise sem precisar alterar a fórmula de soma?

É nesta parte que entra a fórmula Indireto

O que fiz foi vincular cada um dos meses a uma posição (coluna na planilha) e incluir esta referência na soma dos valores, usando a fórmula Indireto. Com isso, a fórmula ficou da seguinte forma: =SOMA(INDIRETO(W7)). Onde o valor da célula W7 representa o range com os valores que queremos somar (G:G).

Obs: reparem que para alterar o valor da célula W7, usei a fórmula Procv, vinculando o mês selecionado a coluna onde estão as despesas desse mês. (para entender melhor como funciona a fórmula Procv, procure por esta fórmula na pesquisa do blog!

Para deixar a fórmula mais inteligente, troquei a fórmula Soma por SomaSe, criando um resumo das despesas por classificação. Além disso, usei a fórmula SomarProduto para calcular o total acumulado do período na coluna R, ou seja, a soma de janeiro até o mês selecionado.

Espero que esse post ajude bastante, principalmente o pessoal de Controladoria, na hora de montar análises para as apresentações mensais de resultado!

Abs, Dr. E

P.S. Gostaria de pedir desculpas, mas infelizmente não estou conseguindo responder todos os e-mails com dúvidas que venho recebendo. Por isso, peço que não usem o espaço de comentários do post para enviar dúvidas.