Revista de Ciências
Gerenciais
Vol. 16, Nº. 23, Ano 2012
MÉTODO DE SIMULAÇÃO DE MONTE CARLO EM
PLANILHA EXCEL
Desenvolvimento de uma ferramenta versátil para
análise quantitativa de riscos em gestão de projetos
Nilton Roberto dos Santos
Machado
Anhanguera Educacional
[email protected]
Alessandre Oliveira Ferreira
Faculdade Anhanguera de Valinhos
[email protected]
RESUMO
Este trabalho tem como objetivo demonstrar o desenvolvimento de uma
planilha Excel para simulação de Monte Carlo e comprovar seu
funcionamento, comparando o resultado obtido com simulação paralela
com o aplicativo Corisco, assim como disponibilizar a planilha como
ferramenta de trabalho e aprendizagem. A explicação do
desenvolvimento da planilha visa facilitar o entendimento do método
de Simulação de Monte Carlo. Encontra-se literatura farta a respeito do
tema e também planilhas desenvolvidas para essa finalidade, que são
fornecidas em CDs ou mesmo podem ser baixadas da internet, mas há
escassez de material que demonstre como desenvolver uma planilha
para simulação de Monte Carlo, com explicação detalhada do
desenvolvimento, além do fato das planilhas ou ferramentas
disponíveis para download gratuito não serem versáteis o suficiente
para aplicações genéricas. A versatilidade da planilha proposta nesse
trabalho e a facilidade de uso incentivarão os usuários a utilizá-la como
ferramenta de aprendizagem, particularmente aqueles envolvidos em
análise de riscos em projetos.
Palavras-Chave: análise de riscos; Monte Carlo; gestão de projetos.
ABSTRACT
Anhanguera Educacional Ltda.
Correspondência/Contato
Alameda Maria Tereza, 4266
Valinhos, São Paulo
CEP 13.278-181
[email protected]
This paper aims to demonstrate the development of an Excel
spreadsheet for Monte Carlo simulation and demonstrate its operation
by comparing the results obtained with the parallel simulation
application Corisco, as well as provide the spreadsheet as a tool for
work and learning. The explanation of the development on the
worksheet is designed to facilitate the understanding of the method of
Monte Carlo simulation. It is literature plentiful on the subject and also
spreadsheets development of for this purpose, which are provided on
CD or can be downloaded from the internet, but no shortage of material
that demonstrates how to develop a spreadsheet to Monte Carlo
simulation, with detailed explanation of the development, beyond the
fact of the worksheets or tools available for free download are not
versatile enough for general purpose applications. The versatility of
spreadsheet proposed in this work and ease of use will encourage users
to use it as a learning tool.
Keywords: Risk analysis; Monte Carlo Simulation; Project Management.
Coordenação
Instituto de Pesquisas Aplicadas e
Desenvolvimento Educacional - IPADE
Informe Técnico
Recebido em: 27/06/2012
Avaliado em: 11/07/2012
Publicação: 22 de novembro de 2012
223
224
Método de simulação de Monte Carlo em planilha Excel: desenvolvimento de uma ferramenta versátil para análise quantitativa de riscos em gestão de projetos
1.
INTRODUÇÃO
O método de Monte Carlo é uma poderosa ferramenta para solução de problemas não
convencionais. Sua utilização, no entanto, requer conhecimento detalhado do método,
assim como cuidados especiais na introdução de dados e interpretação dos resultados.
O detalhamento do desenvolvimento da planilha facilitará o entendimento do
método, enquanto a aplicação e comparação dos resultados com ferramentas já existentes
gerarão credibilidade ao leitor.
Como pré-requisitos para o desenvolvimento da planilha são abordados tópicos
pertinentes ao tema: métodos determinísticos e estocásticos, variáveis aleatórias, geração
de cenários, modelagem das variáveis de entrada e função de distribuição de
probabilidade, assim como a diferença entre processos e projetos.
Atenção especial foi dada à questão da estimativa das variáveis de entrada e o
método Delphi é citado como uma alternativa para se minimizar a margem de erros nessa
etapa.
A base para o desenvolvimento da planilha é a geração da função de distribuição
de densidade triangular aleatória, explicada em detalhes.
A planilha possui doze variáveis de entrada, quatro variáveis auxiliares e duas
variáveis de saída, estas últimas atreladas a histogramas e curvas de densidade
probabilidade, que serão analisadas para interpretação dos resultados e tomada de
decisão. O uso de duas variáveis de saída permite análise de custos e prazos
simultaneamente.
2.
O MÉTODO DE MONTE CARLO
O método de Monte Carlo, doravante referenciado como MMC e também conhecido
como simulação de Monte Carlo consiste basicamente na geração de números aleatórios
associados a técnicas probabilísticas e é utilizado para solução de problemas não
convencionais, cuja solução por métodos determinísticos seria muito trabalhosa, quando
não inviável.
Por método determinístico entenda-se aquele baseado em discretizações
numéricas das variáveis das funções que descrevem o processo em análise. A simulação
estocástica utiliza variáveis aleatórias como entrada e através de algoritmos
computacionais baseados nas leis da probabilidade e estatística, geram saídas que devem
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
Nilton Roberto dos Santos Machado, Alessandre Oliveira Ferreira
225
ser interpretadas como estimativas estatísticas das características reais do processo em
análise.
O MMC é, portanto, um método estocástico que utiliza variáveis aleatórias para
realizar a simulação, sendo que o resultado não será o mesmo para cada recálculo,
embora tenda a convergir para valores aproximados.
Outra característica da aplicação do MMC é a geração de cenários e também a
modelagem das variáveis de entrada através do uso de funções de distribuição de
probabilidade apropriadas ao problema em análise. Uma vez definidas as funções de
distribuição de probabilidade, a simulação de Monte Carlo é realizada através da
amostragem aleatória dessas funções. Por fim o resultado final obtido traduz-se num
histograma associado a uma curva de densidade probabilidade acumulada, esta última
em formato típico de um “S”. Essa curva de densidade probabilidade acumulada será o
objeto de estudo para solução do problema em análise e quantificará em percentual a
probabilidade de determinado valor encontrar-se acima, abaixo ou entre um intervalo de
confiança escolhido.
Uma das aplicações do MMC se dá na análise quantitativa de riscos em gestão de
projetos. É importante, portanto, diferenciar projeto de processo.
Alencar (2005, p. 5) define projeto como sendo todo conjunto de atividades
interdependentes que, tendo sido encomendado por um cliente, será executado pela
primeira vez, uma única vez, com vistas a propiciar, para este cliente, algum tipo de
benefício ou vantagem competitiva.
Segundo o PMBOK (2004, p5), um projeto é um esforço temporário empreendido
para criar um produto, serviço ou resultado exclusivo.
Processos, por sua vez, são contínuos e repetitivos. Eventuais impactos
decorrentes de incertezas presentes na fase inicial de um processo são normalmente
minimizados e até mesmo eliminados, quando então o processo torna-se estável.
Com relação aos riscos envolvidos em um projeto, cabe aqui uma breve
explanação: segundo Alencar (2005, p. 17) falar sobre riscos é falar sobre a identificação de
fatores que podem afetar o sucesso de um projeto, da probabilidade desses fatores
assumirem valores que possam prejudicar o projeto e das conseqüências destes fatores
assumirem aqueles valores em particular.
Alencar (2005, p. 18) afirma que fator de risco é qualquer evento que possa
prejudicar, total ou parcialmente, as chances de sucesso do projeto, isto é, as chances do
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
226
Método de simulação de Monte Carlo em planilha Excel: desenvolvimento de uma ferramenta versátil para análise quantitativa de riscos em gestão de projetos
projeto realizar o que foi proposto dentro do prazo e fluxo de caixa que foram
estabelecidos.
Risco por sua vez é a probabilidade de que um fator de risco venha a assumir um
valor que possa prejudicar, total ou parcialmente, as chances de sucesso de um projeto.
O risco, portanto, possui três componentes (DINIZ, 2004):

Um evento.

A probabilidade de ocorrência do evento.

O impacto decorrente do evento.
Segundo o PMBOK (2004, p.240) o risco se origina da incerteza que está presente
em todos os projetos.
Keeling (2002, p.53) enfatiza que em todo projeto existe um elemento de risco,
sendo alguns elementos de risco mínimo e outros de forte propensão a altos riscos. O
Autor frisa que o risco é um processo contínuo e que, portanto, deve ser administrado
durante todo o ciclo de vida do projeto.
Ainda com relação à gestão de riscos em projetos, cabe aqui enfatizar a
importância desse quesito, ainda que de forma sucinta:
Muitas organizações desenvolvem, ao longo do tempo, uma cultura na qual aqueles que
ousam apontar as incertezas a que todo projeto está naturalmente exposto são taxados
de pessimistas ou derrotistas. Esse tipo de organização tende a valorizar os esforços dos
“heróis de última hora”, que trabalham ininterruptamente para finalizar aquilo que
deveria ter sido feito dentro do calendário de atividades acordado com o cliente.
(ALENCAR, 2005, p.48)
Alencar (2005, p. 7) sugere que executivos em posição de decisão devem
certificar-se de que seus gerentes de projetos estejam qualificados para lidar com as
incertezas associadas aos projetos que gerenciam.
No modelo de avaliação quantitativa dos riscos de custos e prazos de um projeto,
utilizar-se-á do MMC, cuja técnica consiste em gerar uma grande quantidade de cenários
dos possíveis custos ou prazos individuais do projeto e cujo resultado final se dará
através da análise da curva de distribuição de probabilidade acumulada do custo ou
prazo total do projeto. As variáveis de entrada, ou seja, os custos ou prazos individuais ou
parciais devem ser estimados por especialistas e também podem ser aplicadas técnicas
específicas para minimizar a margem de erro das estimativas, como por exemplo, a
técnica Delphi.
O método Delphi de pesquisa tem como característica básica a consulta a
especialistas, preservando o anonimato dos entrevistados, porém ao mesmo tempo
permitindo a interação entre esses especialistas.
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
Nilton Roberto dos Santos Machado, Alessandre Oliveira Ferreira
227
A comunicação entre os membros da equipe de especialistas é feita de forma
indireta, justamente para evitar a influência de uns sobre os outros. Isso é particularmente
interessante quando os membros da equipe consultada têm diferentes níveis hierárquicos:
com essa técnica a opinião do almoxarife e a do gerente passam a ter o mesmo peso.
A consulta aos especialistas é feita através de formulários, que após serem
respondidos pelos especialistas são analisados e em seguida é verificado se o grupo
chegou a um consenso; enquanto o grupo não chegar a um consenso, o mediador fornece
ao grupo as estatísticas dos resultados obtidos e um novo questionário é elaborado,
visando não desviar dos objetivos do projeto. O processo se repete até que o consenso
tenha sido atingido, conforme Figura 1.
Figura 1 - Fluxograma Método Delphi.
O MMC é iterativo e requer um número de cenários elevado para situar-se
dentro de uma margem de erro aceitável. O número de cenários gerados deve ser
compatível com a margem de erro esperada e a interpretação da curva de densidade
probabilidade acumulada gerada em função desses cenários guiará o gestor de projetos a
assumir riscos calculados, ao invés de colocar margens de segurança tanto para prazos
quanto para custos que poderiam inviabilizar determinado projeto.
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
228
Método de simulação de Monte Carlo em planilha Excel: desenvolvimento de uma ferramenta versátil para análise quantitativa de riscos em gestão de projetos
As variáveis aleatórias (randômicas) geradas por métodos computacionais são na
realidade variáveis pseudo-aleatórias e serão consideradas válidas para o fim a que se
aplica esse trabalho.
Uma função que se encaixa muito bem ao modelo de prazos e custos em gestão
de projetos é a função de distribuição de densidade triangular e será adotada como
padrão para a finalidade a que se destina esse trabalho.
Nesse caso (distribuição triangular) os especialistas definem três pontos distintos
da distribuição para cada variável de entrada, ou seja, para estimativa dos custos ou
prazos individuais ou parciais do projeto: a estimativa do valor mínimo possível (min), o
valor mais provável (Mp) e o valor máximo possível estimado (máx). Esses três valores
representam a opinião dos especialistas e os cenários possíveis serão gerados
aleatoriamente
baseados
nessas
estimativas.
É
possível
também
contingenciar
determinados custos ou prazos individuais. A contingência de determinado item no custo
ou prazo total do projeto está vinculada à probabilidade de ocorrência desse item durante
o ciclo de vida de um projeto.
O modelo de risco de custo de um projeto parte da premissa que o custo total do
projeto será o resultado da soma dos custos individuais.
Portanto, seja C o custo total de um projeto e C1, C2,... Cn os custos parciais, o
custo total será: C = C1 + C2 + ...+ Cn. Numa eventual contingencia, o custo parcial
contingenciado terá peso diferenciado em função do grau de contingencia (probabilidade
de ocorrência) do mesmo.
No modelo de risco de prazo, o prazo final de um projeto não será
necessariamente a soma dos prazos individuais ou parciais, mas deverá levar em
consideração os prazos envolvidos nas atividades pertencentes ao caminho crítico,
também conhecido como CPM (Critical Path Method), que corresponde ao percurso com
maior duração da rede de atividades do projeto,
Figura 2- Modelo de Risco de Prazo
Como podem ser observadas na Figura 2, as variáveis A, B, C, D e E são as
tarefas envolvidas num projeto, enquanto TA, TB, TC, TD e TE são os prazos estimados
de cada tarefa. Nesse caso o prazo total, T, será dato por:
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
Nilton Roberto dos Santos Machado, Alessandre Oliveira Ferreira
229
T = TA + MÁXIMO (TD; TB+TC) + TE, onde a fórmula “MÁXIMO” retornará o
maior valor entre TD e TB+TC.
Uma vez definidos os critérios para os possíveis caminhos críticos, o próximo
passo é atribuir os valores individuais de cada prazo do projeto (TA... TE), através dos
valores min, Mp e max estimados pelos especialistas.
O MMC gerará uma grande quantidade de cenários aleatórios baseados nessas
estimativas e nos critérios adotados para os possíveis caminhos críticos e o resultado final
será uma curva da distribuição de probabilidade acumulada da probabilidade de risco do
prazo final do projeto, curva essa em formato de “S” que deverá ser analisada pelo gestor
do projeto que decidirá que risco quer assumir.
3.
HISTÓRICO
O MMC tem suas origens quando o matemático Stanislaw Ulam, em 1946, ao jogar
paciência, resolve questionar quais seriam as chances de sucesso em um jogo com 52
cartas. Ao tentar utilizar de análise combinatória percebeu haver uma alternativa mais
prática que seria simular o resultado fazendo inúmeras jogadas e contar os resultados
obtidos, porém ainda assim envolvia cálculos demorados, por serem iterativos.
Coincidentemente nessa época surge o primeiro computador eletrônico,
desenvolvido durante a segunda guerra mundial, o ENIAC (Eletronic Numerical
Integrator And Computer).
Ulam trabalhou no projeto Manhattan junto com John Von Neumann e Nicholas
Metropolis no desenvolvimento da bomba atômica durante a II guerra e incentivou Von
Neumann a utilizar métodos de amostragem estatística para solucionar o problema da
difusão de nêutrons em material sujeito a fissão nuclear, utilizando o computador.
A característica aleatória desse método fez com que Nicholas Metropolis
sugerisse o nome de Método de Monte Carlo, em analogia ao famoso cassino.
4.
SIMULAÇÃO UTILIZANDO DISTRIBUIÇÃO TRIANGULAR ALEATÓRIA
Numa distribuição triangular, a probabilidade de determinado valor “x” ocorrer
corresponde à área do triângulo representado na figura 3:
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
230
Método de simulação de Monte Carlo em planilha Excel: desenvolvimento de uma ferramenta versátil para análise quantitativa de riscos em gestão de projetos
Figura 3 – Função Distribuição Triangular
min – valor mínimo assumido pela função
mp – valor mais provável da ocorrência
max – valor máximo assumido pela função
Para o lado esquerdo da distribuição, representada na figura 4, tem-se que:
Figura 4 – Distribuição triangular acumulada , lado esquerdo
( x  min) 2
min  x  mp 
(mp  min)  (max  min)
Sendo RN uma variável aleatória, no intervalo
min  x  mp , correspondente
ao lado esquerdo da distribuição triangular em relação ao pico (mp), tem-se:
( x  min) 2
RN 
(mp  min)  (max  min)
x  min 2  RN   mp  min   max  min 
( x  min)  ( RN )  (mp  min)  (max  min)
Portanto:
x  min  ( RN )  (mp  min)  (max  min)
Equação 1 - F.D.P. triangular acumulada aleatória: lado esquerdo
Para o lado direito da distribuição, representada na figura 4, tem-se que:
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
Nilton Roberto dos Santos Machado, Alessandre Oliveira Ferreira
231
(max  x) 2
mp  x  max  1 
(max  mp)  (max  min)
Figura 5– Distribuição triangular acumulada, lado direito.
Sendo RN uma variável aleatória, no intervalo
mp  x  max , correspondente
ao lado direito da distribuição triangular em relação ao pico (mp), tem-se:
(max  x) 2
RN  1 
(max  mp)  (max  min)
Então,
(max  x) 2
1  RN  
(max  mp)  (max  min)
(max  x)  (1  RN )  (max  mp)  (max  min)
Portanto,
x  max  (1  RN )  (max  mp)  (max  min)
Equação 2 - F.D.P. triangular acumulada aleatória: lado direito
As equações 1 e 2 estão traduzidas em fórmulas padrão Excel e alocadas nas
células de C10 a C1010 conforme mostrado na Figura 6.
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
232
Método de simulação de Monte Carlo em planilha Excel: desenvolvimento de uma ferramenta versátil para análise quantitativa de riscos em gestão de projetos
Figura 6- FormulasFigura 6 - Excel para a FDP triangular acumulada aleatória
A célula B7 define o ponto de inflexão da curva triangular, dado por:
mp  min 
max  min 
A estrutura das fórmulas utilizadas de B10 a B1010 é mostrada em detalhes na
Figura 7:
Figura 7 - Estrutura da função “SE” do Excel para a FDP triangular acumulada aleatória
A planilha gera então 1000 números aleatórios através da função ALEATORIO
()1 do Excel e uma distribuição triangular aleatória de 1000 pontos será gerada quando a
planilha for alimentada com os parâmetros min, mp e max, conforme mostra a Figura 8:
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
Nilton Roberto dos Santos Machado, Alessandre Oliveira Ferreira
233
Figura 8 - Geração de FDP triangular aleatória com mil pontos
Uma vez obtida a distribuição triangular aleatória de 1000 pontos, para efeito de
visualização gráfica agrupa-se as variáveis aleatórias em 40 intervalos distintos,
calculando-se a freqüência acumulada dos intervalos e também a relativa, conforme
mostrado na Figura 9:
Figura 9 - Cálculo da frequência acumulada e relativa
Para se obter os 40 intervalos é necessário definir os pontos mínimo e máximo da
distribuição, assim como o “tamanho” do intervalo. As células F2 e F3 calculam os valores
máximo e mínimo da distribuição aleatória, enquanto a célula F4 define o tamanho de
cada um dos 40 intervalos. De posse dessas informações, na coluna J têm-se os 40
intervalos da distribuição, na coluna K a freqüência acumulada, através da fórmula
FREQÜENCIA (matriz_dados; matriz_bin) do Excel e também a freqüência relativa na
coluna L. O gráfico da distribuição aleatória é traçado selecionando-se as colunas J e L, ou
seja, com os intervalos na abscissa e freqüência relativa no eixo das ordenadas, conforme
mostrado na Figura 10:
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
234
Método de simulação de Monte Carlo em planilha Excel: desenvolvimento de uma ferramenta versátil para análise quantitativa de riscos em gestão de projetos
Figura 10 - Geração do histograma da FDP aleatória
A curva da distribuição aleatória é traçado selecionando-se as colunas J e M, ou
seja, com os intervalos na abscissa e P(x<X) no eixo das ordenadas, conforme mostrado na
Figura 11:
Figura 11 – geração da curva da FDP aleatória acumulada
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
Nilton Roberto dos Santos Machado, Alessandre Oliveira Ferreira
235
Visto que o gráfico da distribuição triangular aleatória assim como a curva de
distribuição de probabilidade acumulada tem um número reduzido de pontos e que nesse
caso tem função ilustrativa, recorre-se à função ORDEM.PERCENTUAL2 do Excel para
determinar com maior exatidão as probabilidades P(x<X) e P(x>X), onde P(x>X) = 1P(x<X), conforme mostrado nas células H7 e H8 da Figura 12.
Na célula H8 P(x<X) é subtraída de 1-(Px<X) e com isso obtém-se a
probabilidade da variável x encontrar-se no intervalo especificado em H7 e H8
Figura 12 – Função Ordempercentual2 do Excel
Como exemplo, calcula-se a probabilidade de ocorrência entre os valores 13 e 19
na distribuição Triang (10, 15, 25) , conforme mostrado na Figura 13:
Figura 13 – Simulação Triang (10,15,25) aleatória acumulada
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
236
Método de simulação de Monte Carlo em planilha Excel: desenvolvimento de uma ferramenta versátil para análise quantitativa de riscos em gestão de projetos
A probabilidade de ocorrência de valores entre 13 e 19 pode ser expressa por:
P( x  19)  P( x  13)  0,760  0,120  0,64  64%
5.
DESENVOLVIMENTO DA PLANILHA PARA SIMULAÇÃO DE MONTE CARLO
A planilha desenvolvida para aplicação do MMC será doravante denominada planilha
SMC. Essa planilha foi dimensionada para permitir até doze variáveis de entrada
aleatórias independentes, quatro variáveis auxiliares e duas variáveis de saída.
A geração de mil cenários para cada variável independente é feita utilizando-se a
planilha de geração de distribuição triangular aleatória desenvolvida anteriormente como
apoio. Macros desenvolvidas com o recurso gravar macro copiam os parâmetros mínimo,
mais provável e máximo de cada variável de entrada, transportam esses parâmetros para
a planilha geradora de distribuição triangular aleatória através da função colar e os mil
cenários obtidos são por sua vez copiados dessa planilha de apoio e coladas na planilha
SMC, na coluna da entrada correspondente. A macro que desempenha essa função repete
esse processo doze vezes consecutivamente, uma vez para cada entrada.
Quatro variáveis auxiliares são utilizadas como apoio para fórmulas envolvendo
as doze variáveis de entrada. A função das variáveis auxiliares é manipular as variáveis
de entrada através de fórmulas e agrupar as informações em blocos, organizando dessa
forma o equacionamento do problema: isso é particularmente útil quando se utiliza
modelo de riscos de prazo. Nesse caso uma área da planilha é reservada para
representação do método do caminho crítico, de forma gráfica.
Figura 14 – Planilha SMC
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
Nilton Roberto dos Santos Machado, Alessandre Oliveira Ferreira
237
Duas variáveis de saída estão disponíveis e são atreladas a dois histogramas e
curvas de distribuição de probabilidade acumulada distintos, denominados S1 e S2. As
variáveis de saída devem estar vinculadas às variáveis auxiliares, através de fórmulas.
Observa-se na Figura 14 uma área reservada às doze entradas (E1... E12); cada
variável de entrada é parametrizada com os valores mínimo (min), mais provável (Mp) e
máximo (max). A linha intermediária entre as variáveis de entrada (E1.. E12) e os
parâmetros de cada entrada corresponde à contingenciação de cada entrada.
As quatro variáveis auxiliares encontram-se agrupadas em área separada (A1..
A4), com uma área específica para fórmulas vinculadas às variáveis de entrada.
As duas variáveis de saída estão agrupadas em área reservada ao lado das
auxiliares e também possuem uma linha para o título de cada saída. Uma variável de
saída pode agrupar uma ou mais variáveis auxiliares através de fórmulas.
Figura 15 – Entradas contingenciadas: distribuição de Bernoulli
As entradas de dados podem ser contingenciadas e para isso é utilizada a
distribuição de Bernoulli, alocada nas células adjacentes à direita das células que
compõem os mil cenários gerados, como mostrado na Figura 15. Uma macro copia os
valores contingenciados na distribuição de Bernoulli e retroalimenta as células de
entrada.
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
238
Método de simulação de Monte Carlo em planilha Excel: desenvolvimento de uma ferramenta versátil para análise quantitativa de riscos em gestão de projetos
6.
ESTUDO DE CASO
Projeto de Construção – Alencar (2005, p.161)
Baseado nas estimativas parciais de duração de cada etapa de um projeto de construção, o
diretor de uma empreiteira deve estimar o prazo total do projeto, assumindo um risco de
15%.
A Tabela 1 mostra as durações, em dias, de cada etapa desse projeto:
Tabela 1 - Projeto de Construção.
Duração (em dias)
Tarefa
Descrição
Predecessora
Min
MP
Max
A
Obter Materiais
-
2
4
18
B
Obter Mão-de-Obra
-
5
9
19
C
Escavar
-
4
10
28
D
Colocar Fundação
C
8
13
36
E
Construir Estrutura
B,D
44
60
100
F
Instalação Hidráulica
E
30
40
74
G
Instalação Elétrica
E
9
20
43
H
Acabamento Interior
F,G
24
30
48
I
Acabamento Exterior
F
28
29
96
J
Limpeza Local
H,I
10
10
12
Fonte: Alencar (2005, p.161).
Um primeiro estudo será feito utilizando-se as ferramentas de análise PERT
(Program Evaluation and Review Technique) do aplicativo MS Project® da Microsoft
Corporation, vide Figura 16:
Figura 16 – Ferramentas PERT do MS Project 2007©
Os pesos dos cenários otimista, esperado e pessimista são, por default, 1, 4 e 1
respectivamente, conforme mostrado na figura 17:
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
Nilton Roberto dos Santos Machado, Alessandre Oliveira Ferreira
239
Figura 17 – Níveis de importância para os cálculos PERT do MS Project 2007©
Alimentando-se o MS Project com os dados estimados para as durações
individuais de cada etapa do projeto e considerando-se os pesos para cada cenário obtémse a duração total do projeto, demonstrada na figura 18:
Figura 18 – Planilha de entrada PERT do MS Project 2007©
O MS Project calcula a duração de cada etapa multiplicando as durações
otimista, esperada e pessimista por seus respectivos pesos, somando-as e em seguida
dividindo o resultado dessa soma pelo valor da soma dos pesos.
Por exemplo, na etapa B – Contratar mão-de-obra, a duração é calculada como
demonstrado na equação 3:
5  1  9  4  19  1  5  36  19  60  10dias
1  4  1
6
6
Equação 3 – cálculo de duração x níveis de importância PERT
A duração total do projeto é a soma das durações das etapas.
O caminho crítico, ou CPM, é mostrado no Gantt de controle do MS Project,
conforme figura 19:
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
240
Método de simulação de Monte Carlo em planilha Excel: desenvolvimento de uma ferramenta versátil para análise quantitativa de riscos em gestão de projetos
Figura 19 – Gantt de controle do MS Project 2007©
A ferramenta PERT Chart Expert da Critical Tools Inc. propicia melhor
visualização gráfica, conforme mostrado na figura 20:
Figura 20 – Pert view 1 do PERT Chart Expert 2.5©
Nessa etapa tanto o Gantt de controle do MS Project quanto o PERT Chart Expert
oferecem ao gestor do projeto não apenas uma estimativa do prazo, mas também uma
visão detalhada de cada etapa com o caminho crítico em destaque, de extrema
importância para acompanhamento da obra e tomada de decisões.
Ocorre que não foi observada a questão do risco a ser assumido, tido como
premissa para que diretor da empreiteira corresse um risco calculado. Nesse caso utilizarse-á da planilha SMC, mostrada na figura 21:
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
Nilton Roberto dos Santos Machado, Alessandre Oliveira Ferreira
241
Figura 21 – projeto de construção – estudo de caso
As células de entrada E1.. E10 são alimentadas com as informações das durações
de cada etapa, enquanto nas células denominadas “Auxiliares” são inseridas as fórmulas
Excel que irão delinear o caminho crítico através da função “MÁXIMO” do Excel. Uma
das duas saídas da planilha é utilizada, no caso a saída S1.
A planilha também comporta uma área logo acima das células auxiliares para
que uma figura seja inserida, permitindo verificar quais etapas são precedentes ou
sucessoras, facilitando assim a inserção das fórmulas e posterior entendimento das
mesmas.
A simulação gerará mil cenários, com as dez entradas variando aleatoriamente,
gerando dessa forma mil caminhos críticos aleatórios. Essas informações são agrupadas
em intervalos e um histograma é gerado, originando uma curva de densidade de
probabilidade acumulada, que representa a probabilidade de determinado valor
encontrar-se acima ou abaixo de um intervalo de confiança escolhido.
No caso do projeto de construção em análise, observa-se que para o risco de 15%
a ser assumido pelo diretor da empreiteira, o prazo estimado para o projeto é de 236 dias,
divergindo substancialmente dos 186 dias previstos pelo MS Project.
De fato, se os 186 dias previstos com o uso do MS Project forem analisados
utilizando-se a curva de densidade probabilidade gerada pela planilha, constata-se que
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
242
Método de simulação de Monte Carlo em planilha Excel: desenvolvimento de uma ferramenta versátil para análise quantitativa de riscos em gestão de projetos
esses 186 dias representam praticamente 15% de chances de sucesso e 85% de fracasso, ou
seja, exatamente o oposto do desejado.
Para aproximar as estimativas de ambas as ferramentas, uma alternativa seria
alterar os pesos previstos para os níveis de importância otimista, esperado e pessimista do
MS Project, de 1-4-1 para 1-3-2, tornando o cenário um pouco mais pessimista. Dessa
forma o prazo total estimado pelo MS Project será de 217 dias, o que representaria, pela
análise da curva gerada pela planilha apenas 61% de probabilidade de ocorrer nesse
prazo.
A próxima alternativa seria então alterar os níveis de importância para 1-2-3,
reforçando ainda mais o cenário pessimista e obtendo-se assim um prazo total estimado
de 247 dias, que analisado pela curva gerada pela planilha representaria uma
probabilidade de 93% de sucesso, ou seja, 93% de probabilidade de que o projeto seja
concluído dentro do prazo estimado. Essa divergência entre ambos os métodos é
confirmada a seguir:
É muito importante ressaltar que o fato do prazo de conclusão do projeto obtido
na simulação ser maior que o previsto pelo método CPM não é uma mera coincidência.
Na maioria dos casos, as datas de conclusão geradas por métodos determinísticos como o
CPM são otimistas (SCHUYLER, 2001). Mesmo quando as estimativas de durações são
elaboradas cuidadosamente (GALVÃO, 2005).
Uma vez que o MMC alterna aleatoriamente o caminho crítico centenas de vezes,
a análise indutiva da simulação estocástica pelo método probabilístico torna a tomada de
decisão mais consciente que a análise dos dados gerados pelo método determinístico do
CPM, devido às próprias incertezas inerentes aos projetos.
O método nos ajuda a construir um “mundo hipotético” em cada simulação, que
representa um cenário para um mundo possível com determinada probabilidade de
ocorrência (GALVÃO, 2005).
Finalizando, a divergência de estimativas de término do projeto entre os dois
métodos tem origem na “variância aleatória”:
Quando as durações das atividades são substituídas por distribuições de
probabilidades, a data esperada para o término da conclusão do projeto tende a ser maior
que a data calculada no enfoque determinístico. Em outras palavras, quando o risco do
projeto não é considerado, as estimativas de prazo e custo tendem a ser otimistas e isso
contribui para o fato de que um grande número de projetos demora mais e custa mais
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
Nilton Roberto dos Santos Machado, Alessandre Oliveira Ferreira
243
caro do que foi inicialmente informado aos patrocinadores. Esse efeito é conhecido como
variância aleatória (SCHUYLER, 2001).
Em alguns casos, a diferença entre as estimativas de término do projeto obtido
pelas abordagens determinísticas (CPM) e probabilísticas (simulação de Monte Carlo)
pode ser dramática. (GALVÃO, 2005)
7.
CONSIDERAÇÕES FINAIS
Esse artigo abordou o desenvolvimento de uma ferramenta versátil para análise
quantitativa de riscos em gestão de projetos e que pode ser utilizada também como
instrumento de aprendizagem do Método de Simulação de Monte Carlo
Recursos adicionais podem ser desenvolvidos, como por exemplo, geração de
função de distribuição normal aleatória para aplicação do teorema do limite central,
demonstrado em Alencar (2005, p132 - 142) no exemplo “The Blue BIRD Rent a Car”.
Com relação à utilização do método, deve-se atentar aos questionamentos de
Lúcio Diniz (PMP - 2004):

Análise qualitativa com ações ou análise quantitativa sem ações?

A maravilha dos instrumentos para análise quantitativa de riscos: uma
armadilha para os maravilhados?

Softwares para análise quantitativa de riscos em projetos: muita modelagem para pouco conteúdo?
De fato a utilização desse método deve ser embasada em critérios que levem em
consideração o porte do projeto, a qualidade das informações dos dados de entrada, a
capacidade de resposta aos riscos, assim como a estratégia de monitoramento e controle
de riscos, caso contrário deve-se optar pelos métodos qualitativos, sob pena de se utilizar
de muita sofisticação para pouco resultado.
REFERÊNCIAS
ALENCAR, Antonio Juarez; SCHMITZ, Eber Assis. Análise de Risco em Gerência de Projetos. Rio
de Janeiro: Brasport, 2005. 172p.
BRIGHTON WEBS LTD. - Data & Analysis Services for Industry & Education. Triangular
Distribution. Disponível em: <http://www.brighton-webs.co.uk/distributions/triangular.asp>.
Acesso em: 10 out. 2010.
DINIZ, Lúcio J. Análise de Riscos em Projetos : Uma abordagem Qualitativa ou Quantitativa? PMI
– Project Management Institute Brazil, Minas Gerais, 31 de Agosto de 2004. Disponível em:
<http://www.pmimg.org.br/downloads>. Acesso em: 21 nov. 2009.
GALVÃO, Márcio. Análise Quantitativa de Riscos com simulação de Monte Carlo. Mundo
Project Management - MPM prática. Editora Mundo Ltda, 2005. Disponível em:
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244
244
Método de simulação de Monte Carlo em planilha Excel: desenvolvimento de uma ferramenta versátil para análise quantitativa de riscos em gestão de projetos
<http://www.pellisistemas.com.br/novo/pt/biblioteca/arquivos/Monte_Carlo.pdf>. Acesso em:
15 jan. 2011.
KEELING, Ralph. Gestão de Projetos: uma abordagem global. São Paulo: Saraiva, 2002. 281p.
MARTINS, Daniel de Araújo; SOUZA, Dayse da Mata Oliveira; MELO, Klaus Charlie Nogueira
Serafim de. Utilização do Método Delphi no processo de planejamento estratégico. ABEPRO –
Associação Brasileira de Engenharia de Produção, XXVI ENEGEP - Fortaleza, CE, Brasil, 9 a 11 de
Outubro de 2006. Disponível em:
<http://www.abepro.org.br/biblioteca/ENEGEP2006_TR450301_8469.pdf>. Acesso em: 21
novembro de 2009.
MATIAS JR., Rivalino. Análise Quantitativa de Risco Baseada no Método de Monte Carlo:
Abordagem PMBOK. I Congresso Brasileiro de Gerenciamento de Projetos – Florianópolis 29 a 31
de março de 2006. Disponível em: <http://www.pmisc.org.br/congresso>. Acesso em: 14 jan. 2011.
MORAES, Renato de Oliveira; LAURINDO, Fernando José Barbin. O método de Monte Carlo com
o MS Excel. XI SIMPEP – Bauru, SP, Brasil, 08 a 10 de novembro de 2004. Disponível em:
<http://www.simpep.feb.unesp.br>. Acesso em: 15 jan. 2011.
MORETTIN, Luiz Gonzaga. Estatística Básica - Volume 1 - Probabilidade. São Paulo: Pearson
Education do Brasil, 1999. 210p.
RISKTECH.com.br – o portal Brasileiro de tecnologia em risco. Preço de uma Opção por Simulação
de Monte-Carlo. Disponível em: <http://www.risktech.com.br>. Acesso em: 21 nov. 2009.
SALLES Júnior; CORRÊA, Carlos Alberto. Gerenciamento de Riscos em Projetos. Rio de Janeiro:
Editora FGV, 2006. 160p.
VERTEX 42 Tm - The Guide to Excel in Everything. Monte Carlo Simulation in Excel: A Practical
Guide For Science, Engineering, and Financial Risk Analysis. Disponível em:
<http://www.vertex42.com/ExcelArticles/mc/index.html>. Acesso em: 21 nov. 2009.
WOFRAM MATHWORLD – The web’s most extensive mathematics resource. Triangular
Distribution. Disponível em <http://mathworld.wolfram.com/TriangularDistribution.html>.
Acesso em: 10 out. 2010.
WRIGHT, James Terence Coulter; GIOVINAZZO, Renata Alves. DELPHI – Uma Ferramenta de
Apoio ao Planejamento Prospectivo. Caderno de Pesquisas em Administração, São Paulo, v.01,
n.12, 2.trim. 2000. Disponível em: <http://www.iea.usp.br/tematicas/futuro/projeto/delphi.pdf>.
Acesso em: 21 set. 2008.
Nilton Roberto dos Santos Machado
Gerente dos laboratórios de Engenharia
Anhanguera Educacional, Departamento
Desenvolvimento Pedagógico.
da
de
Alessandre Oliveira Ferreira
Professor da Faculdade Anhanguera de Valinhos MBA em Gestão de Projetos.
Revista de Ciências Gerenciais  Vol. 16, Nº. 23, Ano 2012  p. 223-244