Cómo reestructuramos los pronósticos pobres en pronósticos ligeramente mejores (continuación)

En un artículo anterior, hablé sobre cómo, con el propósito de pronosticar ingresos, las personas crearon un archivo de Excel grande y complejo ( puede leerlo aquí ). Decidimos intervenir en esta vergüenza y propusimos rehacer el modelo de pronóstico para que hubiera menos errores, una operación más fácil y una flexibilidad en el ajuste.

¿Cuáles son los problemas clave en el modelo descrito?

1. Los datos, el modelo y las vistas se mezclan en una sola entidad. Debido a esto, un cambio en al menos un elemento destruye todo este monolito.
2. Cálculo excesivo para el procesamiento manual, lo que da lugar a errores y errores tipográficos en grandes cantidades.

Lo que sugerimos:

1. En el modelo inicial, los datos iniciales sobre los que se construyó no aparecían en ninguna parte. Propusimos ingresar estos datos en el formato de la segunda forma normal en el archivo Excel en 2 hojas separadas (ventas y número de clientes). Afortunadamente, los datos de ventas en nuestra agregación por mes son solo decenas de miles de líneas, no millones. También configuramos para recibir estos datos usando Power Query directamente desde la base de datos.
2. Creamos una hoja de modelado, que consta de 3 bloques:
   
   • Tabla de pivote de ingresos
   • Tabla resumen del número de clientes.
   • Tabla de liquidación de cheques promedio
     Cada tabla dinámica es una tabla dinámica construida sobre los datos de origen en los detalles requeridos para la simulación actual por departamentos y unidades, en los detalles requeridos sobre períodos (meses).
     
3. En la Hoja de simulación, creamos modelos de pronóstico simples basados ​​en series de tiempo históricas. Ampliamos el número de clientes y la factura promedio, y los ingresos totales previstos se consideraron como un producto de estos valores. Después de revisar los datos, se nos ocurrieron 3 modelos de pronóstico: mediana para períodos pasados, suavizado exponencial triple y reducción a cero (cuando necesitamos 0 pronóstico).
   
4. El cálculo del recibo promedio (hecho) y los ingresos (pronóstico) no se hace haciendo referencia a las celdas, sino usando VLR y marca de desplazamiento, lo que hace que los cálculos sean resistentes a los cambios en los datos iniciales.
   
5. Está claro que ahora el usuario no puede leer el modelo, porque Hay demasiados significados. Para hacer esto, construimos hojas de unidades separadas. Cada hoja tiene una celda que selecciona qué datos deben resumirse en esta hoja. Según VLOOKUP, extraemos datos de la hoja del modelo a la hoja.
6. La creación de 30 hojas por unidades se realizó de acuerdo con un procedimiento especial. Primero, se crea la primera hoja, una de las unidades, que contiene todos los nombres posibles de los departamentos. Si no hay un departamento en una subdivisión, entonces las fórmulas los devuelven a 0. Para hacer las 30 subdivisiones, creamos duplicados y cambiamos los nombres en la celda de control (se usa para generar la clave BUSCARV) y tenemos los datos necesarios en el formulario de presentación. VLOOKUP puede usar más de 1 celda como clave, si usa el truco: combine las celdas que necesita en una usando la concatenación (la función CLIP o el símbolo &).
7. Se ha agregado un elemento al formulario de presentación que le permite controlar el modelo: un simple multiplicador de los valores pronosticados de la verificación y el número promedio. Este elemento se recopila en una hoja técnica especial utilizando la función INDIRECTA, que le permite utilizar el enlace generado. Desde esta hoja técnica, todas estas correcciones se transfieren utilizando la BUSCARV a la hoja modelo.
   
8. Las hojas de generalización ya no son una suma de hojas de presentación, sino que se construyen exactamente de la misma manera que todas las demás hojas, sumando los datos en una hoja con un modelo. Como resultado, las representaciones son representaciones puras y no tienen dependencias entre ellas.

Lo que tenemos

1. Siempre está claro de qué dígitos obtuvimos los datos (porque se guardó la consulta Power Query).
2. Podemos cambiar los datos sin romper el modelo.
3. Los cambios en la estructura y la jerarquía requerirán modificaciones menores (solo necesita cambiar los nombres en 1 hoja de la presentación y luego hacer que se duplique).
4. Redujimos drásticamente la cantidad de posibles errores, porque La mayoría de los datos se completan con fórmulas, enlaces y claves.
5. El cliente recibió un pronóstico interactivo, en el que puede cambiar los valores él mismo e inmediatamente recibir un pronóstico.
6. Pudimos satisfacer simultáneamente los requisitos de que necesitamos datos tanto en términos anuales como mensuales.
7. Se puede usar en el próximo período presupuestario.
8. Puede cambiar los modelos de pronóstico si nos parecen inapropiados.

¿Por qué decidimos quedarnos en Excel y no rehacerlo en otras tecnologías?

1. Necesitábamos dejar este archivo en la operación de los empleados actuales. Dentro de Excel, es más fácil para nosotros mostrar cómo funciona todo esto y qué pueden solucionar.
2. Excel hace frente a la tarea y a otras soluciones: entidades superfluas.
3. El cliente está acostumbrado a este formulario y a "reentrenar" ciertos costos laborales que no podíamos pagar.

Cuánto tiempo nos llevó: alrededor de 5 días hábiles, donde 1 persona pasó de 2 a 4 horas al día, y al final del día hicimos una revisión de los resultados.