Teste de Hipóteses no Excel (parte 1) Teste de cenários

Hoje aprenderemos como usar as ferramentas de testes de hipóteses no Excel. Esse tutorial será dividido em 3 partes e cada uma abordará uma das ferramentas disponíveis pelo Excel: Cenários, Atingir Metas e Tabela de Dados para variáveis. Com estas ferramentas você poderá usar vários conjuntos diferentes de valores em uma ou mais fórmulas para explorar todos os resultados variados.

Confira aqui todas as partes do nosso especial sobre os Testes de Hipóteses:

Pode ser útil: Agora o Excel possui um recurso para criar gráficos de previsões. Tudo automaticamente. Confira aqui como fazer o procedimento.

Será possível, por exemplo, realizar testes de hipóteses para criar dois orçamentos, cada um assumindo um certo nível de receita. Você pode também, especificar um resultado a ser gerado por uma fórmula e, em seguida, determinar que conjuntos de valores irão gerar esse resultado. O Excel oferece várias ferramentas diferentes para ajudá-lo a realizar o tipo de análise mais adequado às suas necessidades.

Lembramos ainda que além dessas três ferramentas, você pode instalar suplementos que o ajudem a realizar testes de hipóteses, como o suplemento Solver, que é semelhante a Atingir Meta, mas pode acomodar mais variáveis. Também pode criar previsões usando a alça de preenchimento e os vários comandos integrados no Excel. Para obter modelos mais avançados, é possível usar o suplemento Ferramentas de Análise.

Bom, acabada esta breve introdução, passemos ao nosso primeiro objeto de estudo que será o uso dos Cenários para considerar diferentes variáveis.Para começar imaginemos o seguinte cenário de uma empresa.

Repare que há as previsões de faturamento e de gasto com salários para o ano inteiro. Por enquanto não precisamos explica nada, apenas repare que na linha com os valores totais está a seguinte fórmula "Faturamento + Variação Fat. - salários - Variação sal." Por exemplo, para o Total do mês de janeiro inserimos a seguinte fórmula =C6+C7-C8-C9 Assim, a fórmula está preparada para trabalhar com as variações de cenários que serão inseridas futuramente.

Agora que já temos o cenário consolidado, vamos colocar as variáveis que serão usadas no nosso teste de hipóteses. Criaremos a célula "Faturamento" colocaremos a fórmula =SOMA(C6:N7) desta forma assim que editarmos a variação em porcentagem, os resultados serão atualizados automaticamente, utilizando os diferentes cenários. Criaremos também "Salários" e faremos a mesma coisa =SOMA(C8:N9) Já a caixa "Variação Fat." e "Variação Sal." terão a mesma fórmula =C15/100 Não esqueça de selecionar essas 2 células e clicar em "Estilo de Porcentagem". Pronto, agora elas estarão automaticamente formatadas como porcentagem.

Para finalizar esse passo, vamos explicar a continha que inserimos nas células C15 e C16. Ela nada mais é do que a fórmula para porcentagem. Portanto, se quisermos 5% é só efetuarmos 5 dividido por 100, já que 5% nada mais é do que 0,05 ;)

Olhe como está ficando. Veja que já podemos ver a soma dos salários e do faturamento, mesmo sem a inclusão de uma variável.

Agora preencheremos os últimos dados que faltam, as linhas referentes a "% Fat." e "% Sal." Aqui temos um truque, digitaremos a fórmula =C6*$C15 na célula C7. Repare que a função está mandando o Excel pegar o valor de faturamento de janeiro (C6) e multiplicar pela "Variação Fat." (C15). Fácil, certo? Apenas um detalhe: Veja que antes de C15 inserimos o caractere especial " $ ", ele age como um fixador para o valor, ou seja, por mais que copiemos e arrastemos a célula, o "$C15" manter-se-á sempre intacto. Desta forma, apenas arraste a célula C7 até N7, e ficará "=D6*$C15", "=E6*$C15" ...

Para a "% Sal." Seguiremos o mesmo padrão, veja a fórmula a ser inserida e depois arrastada para as seguintes =C8*$C16.

Bom, nossa tabela está pronta e só falta inserir a porcentagem que queremos incidir nas medições. Apenas para exemplificar, se inserirmos o número 5 na caixa " Variação Fat." repare que os valores de "Faturamento" será atualizada com a variável de 5% Veja como ficará:

Agora que nossa tabela está devidamente preenchida criaremos de fato os cenários. Para isso vá na guia "Dados", depois "Teste de Hipóteses" e "Adicionar..." Veja:

Agora vamos criar os cenários. Digite o nome do cenário, por exemplo "Péssimo" e em "Células Variáveis" selecione C15 e C16, aquelas que correspondem à "Variação Fat." e "Variação Sal." Após, dê um ok e digite os valores referentes a um cenário péssimo, em nosso caso, -0,1 (ou seja, -10% em valores decimais) para C15, referente a "Variação Fat." e 0,1 para C16, referente a "Variação Sal". Depois crie o cenário "Normal" e deixe os valores 0, ou seja, sem valores bons ou ruins. E por fim, crie o cenário "Ótimo", com 0,1 para "Faturamento" e -0,1 para "Salários".

Veja agora que ao dar 2 cliques em "Ótimo" as variáveis serão inseridas automaticamente com um aumento de 10% no faturamento e um decréscimo de 10% nos salários, aumentando assim, o total de cada mês e por consequência o faturamento anual.

Pronto, nossa tabela de previsão de cenários está pronta, mas ela pode fazer ainda mais por você. Vamos facilitar ainda mais as coisas para que não precisemos ficar abrindo o gerenciador de cenários toda vez. Para isto vamos criar um relatório de cenários.

Para ser criado perceba que na mesma janela "Gerenciador de Cenários" há um botão chamado "Resumir..." Clique nele e na caixa seguinte e marque a opção "Resumo do cenário" e nas células de resultado marque aquelas referentes ao Faturamento, Salários, Variação Fat. e Variação Sal., no nosso exemplo, de C13 a C16.

Veja então que o Excel criará uma nova planilha com o "Resumo do cenário".

Por fim, é só editar os nomes certinhos e veja que você terá um resumo de todos os cenários possíveis delimitados para sua empresa.

Feito, pessoal. Vejam que agora você tem uma projeção de cenários profissional do seu negócio. Explore essa ferramenta pois ela é mais útil do que você imagina. Crie mais cenários se precisar e deixe do jeito que desejar. Note que um cenário pode ter diversas variáveis e pode acomodar até 32 valores.

E não esqueça de conferir abaixo todas as partes do nosso especial sobre os Testes de Hipóteses:

Abaixo segue o link do download do exemplo utilizado em aula.