A utilização de cenários é um requisito essencial do planeamento financeiro. Os gestores têm necessidade de verificar rapidamente o impacto de diversos conjuntos de pressupostos nos resultados. Uma forma visualmente interessante de o fazer é deixar o utilizador escolher o cenário em função da célula ativa apresentando-se os cálculos e resultados imediatamente.

Para realizar este pequeno exercício vamos precisar de utilizar as seguintes funcionalidades do Excel:

• Eventos: de forma simplista, podemos definir eventos como acontecimentos, situações em que o utilizador executa uma determinada ação, como mudar a célula ativa, abrir ou fechar um livro do Excel, gravar um ficheiro, etc. O Excel pode reagir a um vasto conjunto de eventos, se programados através da sua linguagem VBA (Visual Basic for Applications). No exemplo abaixo demonstrado, veremos como pedir ao Excel que altere o cenário escolhido em função da célula ativa;
• Intervalos com nome: em vez de referenciarmos as células pela sua designação mais comum, isto é, pela letra da coluna e o número da linha (A1, B2, etc.), podemos definir nomes para células ou conjuntos de células. Desta forma, é mais fácil entender o significado do conteúdo dessas células nas fórmulas e na programação VBA. Abaixo veremos como definimos os intervalos cenários e escolhido para designar os três cenários possíveis o que o utilizador escolhe para simular;
• Capacidade para ler e escrever nas células usando o VBA: Como pretendemos alterar o cenário em função da célula ativa, teremos de pedir ao VBA para ler o valor dessa célula e escrevê-lo na célula que contém o cenário escolhido;
• Fórmulas: as que nos interessam aqui, principalmente, são as que permitem executar os cálculos correspondentes ao cenário escolhido de forma dinâmica;
• Gráfico combinado: este tipo de gráfico, como o nome indica, combina duas séries que pretendamos analisar simultaneamente. Interessa-nos analisar o resultado operacional e o número de unidades vendidas.

Introdução e leitura de pressupostos

A nossa empresa vende sapatos a retalho e por simplificação vamos assumir que vende apenas um único produto.

Supondo que pretende estimar os resultados dos próximos quatro anos partindo do ano base de 2015, ela define os seguintes pressupostos:

• Unidades vendidas
• Preço de venda unitário
• Custo variável unitário
• Custos fixos
• Taxa de crescimento das vendas

Para cada um destes pressupostos, a empresa constrói três cenários possíveis (base, otimista e pessimista). Deste modo, poderia conceber a seguinte tabela:

Para destacar as células que pretendemos dinamizar, isto é, que sejam a base para a escolha do cenário se o utilizador as selecionar, marcámos essas células à cor laranja. Assim, se o utilizador pretende escolher o cenário base, seleciona a célula C3; se pretende o cenário otimista, seleciona a célula D3 e se pretende o cenário pessimista, seleciona o cenário pessimista.

Ainda antes de escrevermos o código que dá origem a esta funcionalidade, vamos acrescentar uma nova coluna onde se recolhem os pressupostos do cenário escolhido. Esta nova coluna será a base para os cálculos que alimentam o modelo financeiro e poderia ser algo do género:

A verde temos o número do cenário, que pode variar entre 1 e 3, conforme se pretenda o cenário base, otimista e pessimista, respetivamente. Este número deverá corresponder à ordem pela qual organizamos os cenários nas colunas D a E.

O cabeçalho que está na célula G2, é escrito de forma dinâmica através da seguinte fórmula:

="Cenário "&OFFSET(B3;;escolhido)

Esta fórmula junta dois textos através do símbolo &: a palavra “Cenário” e o resultado da fórmula OFFSET, que conterá a palavra “Base”, “Otimista” e “Pessimista”.

Assim, a fórmula OFFSET (DESLOCAMENTO em Português), começa por recolher a referência a um ponto de partida por nós definido. Neste caso, o ponto de partida é a célula B3.

De seguida, diz-nos o conteúdo da célula que se encontra o número de linhas abaixo do segundo argumento (vazio, neste caso, portanto da própria linha 3) e o número de colunas à direita do valor do terceiro argumento (que neste caso, corresponde ao intervalo cujo nome é escolhido).

Ora, o escolhido é precisamente o valor da célula anteriormente marcada a verde.

Ora, desta forma, obtemos um cabeçalho com um texto dinâmico que varia em função do cenário escolhido. É uma maneira de apresentar a escolha do utilizador de forma mais apelativa.

A recolha dos valores dos pressupostos é obtida a partir da utilização da mesma fórmula OFFSET nas células seguintes.

A construção do modelo financeiro

No separador seguinte introduzimos o modelo financeiro que recolhe o valor dos pressupostos para o cenário escolhido e que estão na coluna G, vista anteriormente:

A macro que altera o cenário de forma dinâmica

Para alterar o cenário escolhido em função da célula ativa, precisamos de duas macros:

• Uma para identificar o evento correspondente à célula ativa
• Outra para executar a ação pretendida nesse caso.

Assim, a primeira macro seria:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(ActiveCell, Range("cenarios")) Is Nothing Then
    Call mudaCenario
  End If
End Sub

Esta macro executa o proecimento mudaCenario se a célula ativa estiver dentro do intervalo cenários (as células marcadas a laranja, como vimos anteriormente). E esse procedimento, que corresponde à segunda macro, lê o valor do cenário escolhido e escreve-o no intervalo escolhido (que não é mais do que a célula anteriormente marcada a verde):

Sub mudaCenario()
  Dim numColuna As Long, numCenario As Long
    numColuna = ActiveCell.Column
  numCenario = numColuna - 2
ActiveSheet.Range("escolhido") = numCenario
End Sub

O resultado final

Com estas fórmulas e macros, tudo o que nos resta para colocar em prática esta pequena aplicação Excel é de um gráfico combinado que mostre os resultados que nos interessa analisar (resultado operacional e unidades vendidas).

Assim, o efeito final resultaria no seguinte:

Este é um efeito interessante e que pode ser desenvolvido para destacar algum tipo de informação escolhida pelo utilizador de forma dinâmica. Por exemplo, poderia ser utilizado para simular resultados de clientes, vendas por segmento geográfico ou por produto, entre várias outras situações. 

A partir do link abaixo poderá fazer o download do ficheiro que utilizei como exemplo.