Quantas unidades pedir quando a demanda é incerta?
Peça a quantidade que a demanda não ultrapassa em 66,7% das vezes, o fractil crítico: com demanda média de 1.000 unidades (desvio padrão de 200) e cada unidade vendida por 10, com custo de 4 e valendo 1 se sobrar, são 1.086,1 unidades, para um lucro esperado de 5.345,5. Uma busca de Monte Carlo na planilha de exemplo escolhe 1.090, o passo mais próximo desse ótimo.
O fractil crítico é o ponto em que a chance de vender mais uma unidade, vezes o que ela rende, iguala a chance de ela sobrar, vezes o que ela custa nesse caso; quando uma venda perdida deixa de render mais do que custa uma unidade que sobra, esse ponto fica acima da mediana da demanda, o nível que a demanda ultrapassa metade das vezes. Neste exemplo a demanda é normal, então a mediana é igual à média, e os valores estão nas unidades monetárias da planilha. A busca percorre pedidos de 600 a 1.600 unidades em passos de 10; com 1.090, o lucro médio é 5.345 ± 31 (intervalo de 95%, 5.000 temporadas simuladas), e a demanda supera o estoque em 32,6% das temporadas.
O modelo
Este é o problema do jornaleiro: um único pedido, feito antes da temporada de vendas; uma demanda que só se conhece como faixa de possibilidades; unidades não vendidas liquidadas a preço baixo no fim; e a demanda acima do pedido, perdida. A planilha tem uma única aba, Model, com rótulos na coluna A e valores na coluna B:
Quantidade de pedido, B1 | 1.000 unidades (a decisão) |
|---|---|
Preço, B2 | 10 por unidade vendida |
Custo unitário, B3 | 4 por unidade pedida |
Valor residual, B4 | 1 por unidade que sobra |
Demanda, B6 | normal, média de 1.000 unidades, desvio padrão de 200 |
Quatro fórmulas transformam um pedido e uma demanda no resultado da temporada: unidades vendidas, Sold, =MIN(B1,B6); unidades que sobram, Leftover, =MAX(B1-B6,0); o lucro, Profit, =B2*B8+B4*B9-B3*B1, vendas mais valor residual menos o custo do pedido inteiro; e as vendas perdidas, Lost sales, =MAX(B6-B1,0), a demanda que o pedido não conseguiu atender. Uma ruptura de estoque aqui é uma temporada com vendas perdidas: demanda acima do pedido. Não há funções de suplemento.
O arquivo para download traz essas fórmulas, o pedido de 1.000 unidades e um valor fixo provisório na célula da demanda. A distribuição da demanda, as duas saídas (Profit e Lost sales), a meta de ruptura de estoque e o estudo de otimização ficam definidos no xellstorm e abrem junto com o exemplo, ao lado da planilha, que continua como você a montaria.
Uma distribuição normal admite, em princípio, demanda abaixo de zero, mas o zero fica 5 desvios padrão abaixo da média, uma chance de cerca de 1 em 3,5 milhões; a menor das 5.000 demandas simuladas é 200 unidades. A demanda é sorteada como um número contínuo, então vendas, sobras e o pedido encontrado mais adiante com Atingir meta podem ser frações de unidade; arredonde para unidades inteiras ao fazer o pedido.
Por que pedir mais que a demanda média?
Pense na última unidade do pedido. Se ela vender, rende o preço menos o custo: 10 − 4 = 6. Se sobrar, perde o custo menos o valor residual: 4 − 1 = 3. Acrescentar a unidade compensa enquanto a chance de ela vender, vezes 6, for maior que a chance de ela sobrar, vezes 3: ou seja, enquanto a chance de vendê-la estiver acima de 3 / (6 + 3) = 33,3%. Com um pedido igual à demanda média, 1.000 unidades, a unidade seguinte vende em metade das vezes, então pedidos maiores compensam. Deixam de compensar onde a demanda fica abaixo do pedido em 66,7% das vezes.
A simulação mostra a mesma aritmética temporada a temporada. Nas mesmas 5.000 temporadas simuladas, pedir 1.090 unidades em vez de 1.000 rende exatamente 540 a mais (90 × 6) nas 32,6% das temporadas em que a demanda chega a 1.090, e exatamente 270 a menos (90 × 3) nas 50% em que a demanda fica em 1.000 ou menos; nas temporadas intermediárias, a diferença fica entre as duas. Em média, o pedido maior rende 63,5 a mais por temporada, como diz a fórmula exata a seguir (63,5).
A solução exata: o fractil crítico
A regra tem nome, fractil crítico: o melhor pedido Q* é o nível de demanda que não é ultrapassado com probabilidade (preço − custo) / (preço − valor residual), aqui (10 − 4) / (10 − 1) = 0,667. Para demanda normal, Q* = média + z × desvio padrão, em que z = 0,4307 é o ponto da distribuição normal padrão com 66,7% abaixo dele (=NORM.S.INV(6/9) no Excel). Logo, Q* = 1.000 + 0,4307 × 200 = 1.086,1 unidades.
O lucro esperado também tem forma fechada. Para qualquer pedido Q, com z = (Q − média) / desvio padrão, ele é (preço − valor residual) × (média − desvio padrão × L(z)) − (custo − valor residual) × Q, em que L(z) = φ(z) − z × (1 − Φ(z)) é a função de perda da normal padrão, com φ a densidade normal padrão e Φ a distribuição acumulada (no Excel, =NORM.S.DIST(z,FALSE)-z*(1-NORM.S.DIST(z,TRUE))); desvio padrão × L(z) são as vendas perdidas esperadas. Em Q* isso se reduz a (preço − custo) × média − (preço − valor residual) × desvio padrão × φ(z) = 5.345,5, e a chance de ruptura de estoque é 1 − 0,667 = 33,3%. Já um pedido igual à demanda média, de 1.000 unidades, dá um lucro esperado de 5.281,9.
Busca da quantidade de pedido por simulação
As fórmulas exigem um modelo simples assim (um produto, um pedido, preços fixos) e uma distribuição de demanda cujos quantis e vendas perdidas esperadas tenham fórmula; uma simulação só precisa de um jeito de sortear a demanda, e este exemplo confere que ela chega à mesma resposta. O estudo de otimização que abre com o exemplo define a quantidade de pedido (B1) como a decisão e testa todos os pedidos de 600 a 1.600 unidades em passos de 10, 101 candidatos, para maximizar a média de Profit. Há uma restrição: a chance de ruptura de estoque, a parcela das iterações com Lost sales acima de zero, deve ser de no máximo 50%. Cada candidato é simulado com 1.000 iterações, os três melhores são rodados de novo com 5.000, e todas as rodadas usam os mesmos sorteios de demanda. Esses números aleatórios comuns fazem os candidatos diferirem só pelo pedido, não pela sorte dos sorteios.
| Pedido, unidades | Lucro médio | Lucro esperado exato | Chance de ruptura de estoque |
|---|---|---|---|
| 1.090 | 5.345 ± 31 | 5.345,4 | 32,6% |
| 1.100 | 5.344 ± 31 | 5.344,0 | 30,9% |
| 1.110 | 5.341 ± 32 | 5.340,9 | 29,1% |
A nova rodada escolhe 1.090 unidades: um lucro médio de 5.345 ± 31, contra o valor exato de 5.345,4 nesse pedido, e ruptura de estoque em 32,6% das temporadas (valor exato: 32,6%). É o passo mais próximo do ótimo exato de 1.086,1, e o valor exato do lucro esperado, 5.345,5, fica dentro do intervalo.
Os intervalos dos três melhores pedidos se sobrepõem quase por completo, mas o vencedor é claro. Cada intervalo é a estimativa do xellstorm para o ruído da média daquele candidato, calculada a partir de quanto a média varia entre 20 lotes das iterações. Com a amostragem por Hipercubo Latino (o padrão do xellstorm), que distribui os sorteios de cada rodada de modo uniforme pela distribuição da demanda, a média de uma rodada inteira é ainda mais estável do que o intervalo sugere: rodando de novo com 3 outras sementes, o lucro médio com 1.090 unidades fica a até 0,2 do valor desta rodada.
O que decide é a diferença entre os candidatos, e como os mesmos sorteios empurram todos para cima ou para baixo juntos, as diferenças são muito mais estáveis que os intervalos. Comparando temporada a temporada, 1.090 supera 1.100 em 1,43 (intervalo de 95% de 0,47 a 2,39), um intervalo que exclui o zero: é melhor além do acaso, mas por pouco. A diferença exata no lucro esperado é 1,43. Perto do topo a curva de lucro é plana, então errar por um passo custa quase nada; nas pontas da faixa, 600 e 1.600 unidades, o lucro esperado cai para 3.585 e 4.199.
A busca em si colocou 1.100 em primeiro, com lucro médio de 5.370,3, contra 5.369,9 para 1.090. Ela usa as primeiras 1.000 das 5.000 iterações, que por acaso sorteiam um pouco mais de demanda: 1.003,7 unidades em média, contra 1.000,0 no conjunto todo. Isso eleva o lucro dos pedidos maiores perto do topo e desloca o pico um passo para cima. A nova rodada com 5.000 iterações decide a questão, e o xellstorm avisa quando ela muda o vencedor.
A restrição e o custo de ter menos rupturas de estoque
Aqui, a restrição de ruptura de estoque não chega a limitar a escolha. Ela exclui pedidos de 1.000 unidades ou menos (com 1.000, 51,6% das iterações da busca ficam sem estoque): com demanda normal, um pedido igual à média fica sem estoque em metade das vezes. O melhor pedido já reduz a chance de ruptura de estoque a 32,6%.
Se uma ruptura de estoque em 32,6% das temporadas é demais, por exemplo porque clientes que encontram a prateleira vazia não voltam, escolha a chance que você aceita e deixe o recurso Atingir meta (Goal seek) encontrar o pedido. O Atingir meta do xellstorm divide ao meio a faixa de pedidos até que a estatística atinja a meta. Com 5.000 iterações por avaliação, ele chega a 1.256,25 unidades em 7 avaliações, e exatamente 10% das iterações ficam sem estoque. A solução exata é o nível de demanda com 90% de chance de não ser ultrapassado: 1.000 + 200 × NORM.S.INV(0.9) = 1.256,3 unidades.
| Pedido, unidades | 1.000 | 1.090 | 1.256,25 |
|---|---|---|---|
| Lucro médio | 5.282 | 5.345 | 5.146 |
| Lucro em P10 | 3.695 | 3.425 | 2.926 |
| Chance de ruptura de estoque | 50% | 32,6% | 10% |
| Média de unidades que sobram | 80 | 133 | 266 |
| Média de vendas perdidas, unidades | 80 | 43 | 9 |
Reduzir a chance de ruptura de estoque de 32,6% para 10% significa pedir 166 unidades a mais (133 delas sobram em uma temporada média) e abrir mão de 199 de lucro médio por temporada (3,7%; a diferença exata no lucro esperado entre os dois pedidos é 199,4). Isso também reduz o lucro em P10, o nível que 90% das temporadas atingem, de 3.425 para 2.926: em uma temporada fraca, um pedido maior deixa mais estoque sem vender (veja P50, P80 e P90). Pedir apenas a demanda média tem o maior P10 dos três, mas fica sem estoque em 50% das temporadas. Decidir se menos rupturas de estoque valem o preço é uma questão de negócio; a simulação dá o número desse preço.
Experimente
- Abra o modelo no xellstorm. A distribuição da demanda, as saídas Profit e Lost sales, a meta (Lost sales acima de zero, a chance de ruptura de estoque) e o estudo de otimização já vêm definidos; não precisa instalar nada nem se cadastrar, e a planilha é calculada no navegador.
- Rode o modelo. Com o pedido salvo na planilha, de 1.000 unidades, a semente do exemplo (7) e 5.000 iterações, o lucro médio é 5.282 e a chance de ruptura de estoque, 50%.
- Abra a etapa Optimize. A quantidade de pedido (
Model!B1) é a célula de decisão, de 600 a 1.600 em passos de 10; o objetivo maximiza a média de Profit, e a restrição mantém a probabilidade de Lost sales acima de zero em no máximo 50%. Clique em Optimize: o app avalia cada pedido com 1.000 iterações, roda de novo os três melhores com 5.000, compara o vencedor com o segundo colocado iteração por iteração e desenha o lucro médio por quantidade de pedido, com faixa de 95%. - Para o pedido com 10% de chance de ruptura de estoque, escolha Goal seek: defina a estatística como Probability, a condição como > com limite 0 em Lost sales e a meta como 0,1; apague o campo Step da quantidade de pedido (ele guarda 10, vindo do estudo), defina Trials per evaluation como 5.000 e clique em Seek goal.
- Clique em Set as fixed cells e rode a simulação de novo para ver os resultados completos no pedido escolhido, ou em Add as scenario para compará-lo com o pedido salvo nos mesmos sorteios.
Dá para fazer só com o Excel?
Para este caso exato, dá: com demanda normal, =NORM.INV((B2-B3)/(B2-B4), 1000, 200) devolve diretamente o pedido do fractil crítico, 1.086,1. Para uma demanda assimétrica, troque NORM.INV pela inversa dessa distribuição (LOGNORM.INV, GAMMA.INV ou PERCENTILE.INC sobre as vendas passadas), mas a própria regra deixa de valer quando vários produtos dividem um orçamento ou um armazém, há um tamanho mínimo de pedido ou um preço de liquidação depende de quanto sobrou. Uma simulação dá conta desses casos, mas otimizar um modelo simulado só com o Excel é incômodo: cada recálculo sorteia novos números aleatórios, então uma tabela de dados ou o Solver compara quantidades de pedido com sorteios diferentes e acaba perseguindo o ruído. Avaliar cada candidato em um único conjunto fixo de sorteios é o que o xellstorm faz por você. Simulação de Monte Carlo no Excel mostra a abordagem geral.
Perguntas
O que é o modelo do jornaleiro?
O modelo do jornaleiro é o problema clássico de escolher quanto estocar para um único período de vendas antes de conhecer a demanda, quando as unidades que sobram são liquidadas abaixo do custo e a demanda além do estoque é perdida. O melhor pedido é o fractil crítico da demanda: a quantidade que a demanda não ultrapassa com probabilidade (preço − custo) / (preço − valor residual). O nome vem de um vendedor de jornais que decide, toda manhã, quantos exemplares comprar; o mesmo problema surge com produtos sazonais, alimentos frescos, capacidade para eventos e lotes de produção únicos. Para estoque reposto o tempo todo, veja qual ponto de pedido equilibra custo e ruptura de estoque.
Quando o melhor pedido fica abaixo da demanda média?
O melhor pedido fica abaixo da demanda média quando uma unidade que sobra custa mais do que uma venda perdida deixa de render, ou seja, quando o custo unitário menos o valor residual é maior que o preço menos o custo unitário. Então o fractil crítico fica abaixo de um meio e, para uma distribuição de demanda simétrica como a normal, o melhor pedido fica abaixo da média. Margens estreitas e estoque não vendido que não vale nada puxam o pedido para baixo; margens altas e um bom valor residual o puxam para cima.
Por que avaliar cada quantidade de pedido com os mesmos números aleatórios?
Avaliar todas as quantidades de pedido com a mesma demanda simulada (os números aleatórios comuns) faz com que as diferenças entre os candidatos venham só dos pedidos. Neste exemplo, os intervalos de 95% dos três melhores pedidos se sobrepõem quase por completo, mas a diferença temporada a temporada entre os dois melhores tem um intervalo de 0,47 a 2,39, que exclui o zero. Com amostragem simples de Monte Carlo, sorteios novos para cada candidato fariam a busca perseguir ruído: medir a diferença entre esses dois pedidos com a mesma precisão exigiria cerca de 2.000 vezes mais iterações do que com sorteios comuns.
E se a demanda não for normal?
Se a demanda não for normal, o fractil crítico continua valendo, mas o pedido tem de vir da função quantil dessa distribuição, e fórmulas como a do lucro esperado acima deixam de se aplicar. Uma simulação só precisa da distribuição em si: no xellstorm, escolha outra distribuição para a célula da demanda, como lognormal, gama, PERT, Poisson ou binomial negativa, ajuste uma aos dados de vendas passadas na etapa Distributions, ou deixe a planilha calcular a demanda a partir de outras entradas incertas, e rode a mesma otimização.
Veja também
- Política de estoque
Qual ponto de pedido equilibra custo e ruptura de estoque?
Elevar o ponto de pedido de 180 para 250 unidades reduz a chance de um ano com ruptura de estoque de 28,0% para 5,1% e soma 345 a um custo anual médio de 2.133. - Guia
P50, P80 e P90: o que significam e em qual nível orçar
P80 é o custo que 80% dos resultados simulados não ultrapassam. Seis itens de custo de uma obra têm P80 que, somados, dão 2.843, mas o P80 da soma deles é 2.756 (milhares de US$). - Guia
Simulação de Monte Carlo no Excel, com ou sem suplemento
Três formas de rodar uma simulação de Monte Carlo em um modelo do Excel: RAND() e uma tabela de dados, um suplemento ou uma ferramenta no navegador, sem suplemento. Fórmula PERT incluída.
O xellstorm é uma ferramenta de simulação de Monte Carlo para modelos do Excel, que roda no navegador: sem suplemento, e o arquivo nunca sai do seu computador.