Como transformamos as previsões ruins em previsões um pouco melhores (continuação)

Em um artigo anterior, falei sobre como, para fins de previsão de receita, as pessoas criaram um arquivo excel grande e complexo ( você pode ler aqui ). Decidimos intervir nessa vergonha e propusemos refazer o modelo de previsão para que houvesse menos erros, operação mais fácil e flexibilidade no ajuste.

Quais são os principais problemas no modelo descrito:

1. Dados, modelo e visualizações são misturados em uma entidade. Por isso, uma alteração em pelo menos um elemento destrói todo o monólito.
2. Cálculo excessivo para processamento manual, o que gera erros e erros de digitação em grandes quantidades.

O que sugerimos:

1. No modelo inicial, os dados iniciais nos quais foram construídos não apareceram em lugar algum. Propusemos inserir esses dados no formato da 2ª forma normal no próprio arquivo do Excel em 2 folhas separadas (vendas e número de clientes). Felizmente, os dados de vendas em nossa agregação por mês são apenas dezenas de milhares de linhas, não milhões. Também configuramos para receber esses dados usando o Power Query diretamente do banco de dados.
2. Criamos uma planilha de modelagem, composta por 3 blocos:
   
   • Tabela dinâmica de receita
   • Tabela de resumo do número de clientes
   • Tabela de liquidação das verificações médias
     Cada tabela dinâmica é uma tabela dinâmica criada com base nos dados de origem nos detalhes necessários para a simulação atual por departamentos e divisões, nos detalhes necessários em períodos (meses).
     
3. Na Planilha de simulação, criamos modelos simples de previsão com base em séries temporais históricas. Estendemos o número de clientes e a fatura média, e a receita total prevista foi considerada como um produto desses valores. Depois de revisar os dados, criamos três modelos de previsão: mediana para períodos passados, suavização tripla exponencial e zeragem (quando precisamos de previsão 0).
   
4. O cálculo do recebimento médio (fato) e da receita (previsão) é feito não fazendo referência às células, mas usando VLR e marcação offset, o que torna os cálculos resistentes a alterações nos dados iniciais.
   
5. É claro que agora o modelo não é legível pelo usuário, porque existem muitos significados. Para isso, construímos folhas de unidades separadas. Cada planilha possui uma célula que seleciona quais dados devem ser resumidos nesta planilha. Com base no VLOOKUP, extraímos dados da planilha de modelo para a planilha.
6. A criação de 30 folhas por unidades foi realizada de acordo com um procedimento especial. Primeiro, a primeira planilha é criada, uma das unidades, que contém todos os nomes possíveis dos departamentos. Se não houver departamento na unidade, as fórmulas retornarão a 0. Para criar todas as 30 unidades, criamos duplicatas e alteramos os nomes na célula de controle (usada para gerar a chave VLOOKUP) e temos os dados necessários na forma de uma apresentação. O VLOOKUP pode usar mais de uma célula como chave, se você usar o truque: combine as células necessárias em uma usando concatenação (a função CLIP ou o símbolo &).
7. Um elemento foi adicionado ao formulário de apresentação que permite controlar o modelo: um multiplicador simples para os valores previstos da verificação e número médios. Esse elemento é coletado em uma planilha técnica especial usando a função INDIRETO, que permite usar o link gerado. Nesta planilha técnica, todas essas correções são transferidas usando o VLOOKUP para a planilha de modelo.
   
8. As folhas de generalização não são mais um somatório das folhas de apresentação, mas são construídas exatamente da mesma maneira que todas as outras folhas - somando os dados em uma folha com um modelo. Como resultado, as representações são representações puras e não têm dependências entre si.

O que temos:

1. É sempre claro de quais dígitos obtivemos os dados (porque a consulta do Power Query foi salva).
2. Podemos alterar os dados sem quebrar o modelo.
3. Alterações na estrutura e hierarquia exigirão pequenas modificações (você só precisa alterar os nomes em 1 folha da apresentação e depois duplicá-la).
4. Reduzimos drasticamente o número de erros em potencial, porque A maioria dos dados é preenchida usando fórmulas, links e chaves.
5. O cliente recebeu uma previsão interativa, na qual ele próprio pode alterar os valores e receber imediatamente uma previsão.
6. Conseguimos satisfazer simultaneamente os requisitos de que precisamos de dados, tanto em termos anuais quanto mensais.
7. Pode ser usado no próximo período do orçamento.
8. Você pode alterar os modelos de previsão se eles nos parecerem inadequados.

Por que decidimos permanecer no Excel e não o refizemos em algumas outras tecnologias?

1. Precisávamos deixar esse arquivo na operação dos funcionários atuais. No Excel, é mais fácil mostrar como tudo isso funciona e o que eles podem corrigir.
2. O Excel lida com a tarefa e outras soluções - entidades supérfluas.
3. O cliente está acostumado a esse formulário e a "treinar" novamente certos custos de mão-de-obra que não poderíamos pagar.

Quanto tempo nos levou: cerca de 5 dias úteis, onde 1 pessoa passou de 2 a 4 horas por dia e, no final do dia, fizemos uma revisão dos resultados.