Comment nous avons transformé les mauvaises prévisions en prévisions légèrement meilleures (suite)

Dans un article précédent, j'ai expliqué comment, aux fins de la prévision des revenus, les gens ont créé un fichier Excel volumineux et complexe ( vous pouvez le lire ici ). Nous avons décidé d'intervenir dans cette honte et avons proposé de refaire le modèle de prévision afin qu'il y ait moins d'erreurs, un fonctionnement plus facile et une flexibilité de réglage.

Quels sont les principaux problèmes du modèle décrit:

1. Les données, le modèle et les vues sont mélangés en une seule entité. Pour cette raison, un changement dans au moins un élément détruit tout ce monolithe.
2. Calcul excessif pour le traitement manuel, ce qui provoque des erreurs et des fautes de frappe en grandes quantités.

Ce que nous avons suggéré:

1. Dans le modèle initial, les données initiales sur lesquelles il a été construit n'apparaissaient nulle part. Nous avons proposé de saisir ces données au format du 2ème formulaire normal dans le fichier Excel lui-même sur 2 feuilles distinctes (ventes et nombre de clients). Heureusement, les données de ventes dans notre agrégation par mois ne sont que des dizaines de milliers de lignes, pas des millions. Nous avons également configuré pour recevoir ces données à l'aide de Power Query directement à partir de la base de données.
2. Nous avons créé une feuille de modélisation, qui se compose de 3 blocs:
   
   • Tableau croisé dynamique des revenus
   • Tableau récapitulatif du nombre de clients
   • Tableau de règlement des chèques moyens
     Chaque tableau croisé dynamique est un tableau croisé dynamique construit sur les données source dans les détails requis pour la simulation en cours par les départements et divisions, dans les détails requis sur les périodes (mois).
     
3. Dans la feuille de simulation, nous avons construit des modèles de prévision simples basés sur des séries chronologiques historiques. Nous avons augmenté le nombre de clients et la facture moyenne, et le total des revenus prévisionnels a été considéré comme un produit de ces valeurs. Après avoir examiné les données, nous avons trouvé 3 modèles de prévision: médiane pour les périodes passées, triple lissage exponentiel et réduction à zéro (lorsque nous avons besoin de 0 prévision).
   
4. Le calcul de la recette moyenne (fait) et du revenu (prévision) se fait non pas en référençant les cellules, mais en utilisant le VLR et le marquage offset, ce qui rend les calculs résistants aux changements des données initiales.
   
5. Il est clair que maintenant le modèle n'est pas lisible par l'utilisateur, car il y a trop de significations. Pour ce faire, nous avons construit des fiches d'unités distinctes. Chaque feuille possède une cellule qui sélectionne les données à résumer dans cette feuille. Sur la base de RECHERCHEV, nous extrayons des données de la feuille de modèle dans la feuille.
6. La création de 30 feuilles par unités s'est faite selon une procédure spéciale. Tout d'abord, la première feuille est créée, l'une des unités, qui contient tous les noms possibles des départements. S'il n'y a pas de département dans une subdivision, les formules les ramènent à 0. Pour créer les 30 subdivisions, nous créons des doublons et modifions les noms dans la cellule de contrôle (il est utilisé pour générer la clé RECHERCHEV) et nous avons les données nécessaires sous la forme d'une présentation. Le RECHERCHEV peut utiliser plus d'une cellule comme clé, si vous utilisez l'astuce: combinez les cellules dont vous avez besoin en une en utilisant la concaténation (la fonction CLIP ou le symbole &).
7. Un élément a été ajouté au formulaire de présentation qui vous permet de contrôler le modèle: un simple multiplicateur aux valeurs prédites de la vérification et du nombre moyens. Cet élément est collecté sur une fiche technique spéciale à l'aide de la fonction INDIRECTE, qui vous permet d'utiliser le lien généré. A partir de cette fiche technique, toutes ces corrections sont transférées à l'aide de la RECHERCHEV sur la fiche modèle.
   
8. Les feuilles de généralisation ne sont plus un résumé des feuilles de présentation, mais sont construites exactement de la même manière que toutes les autres feuilles - en additionnant les données sur une feuille avec un modèle. En conséquence, les représentations sont de pures représentations et n'ont aucune dépendance entre elles.

Ce que nous avons obtenu:

1. Il est toujours clair à partir de quels chiffres nous avons obtenu les données (car la requête Power Query a été enregistrée).
2. Nous pouvons changer les données sans casser le modèle.
3. Les changements dans la structure et la hiérarchie nécessiteront des modifications mineures (il vous suffit de changer les noms sur 1 feuille de la présentation et ensuite de les dupliquer).
4. Nous avons considérablement réduit le nombre d'erreurs potentielles, car La plupart des données sont remplies à l'aide de formules, de liens et de clés.
5. Le client a reçu une prévision interactive, dans laquelle il peut modifier lui-même les valeurs et recevoir immédiatement une prévision.
6. Nous avons été en mesure de répondre simultanément aux exigences dont nous avons besoin de données à la fois en termes annuels et mensuels.
7. Peut être utilisé dans la prochaine période budgétaire.
8. Vous pouvez modifier les modèles de prévision s'ils nous semblent inappropriés.

Pourquoi avons-nous décidé de rester dans Excel et ne l'avons pas refait sur d'autres technologies?

1. Nous devions laisser ce dossier dans le fonctionnement des employés actuels. Dans Excel, il nous est plus facile de montrer comment tout cela fonctionne et ce qu'ils peuvent résoudre.
2. Excel fait face à la tâche et à d'autres solutions - des entités superflues.
3. Le client est habitué à ce formulaire et à «recycler» certains coûts de main-d'œuvre que nous ne pouvions pas nous permettre.

Combien de temps cela nous a pris: environ 5 jours ouvrables, où 1 personne a passé 2 à 4 heures par jour, et à la fin de la journée, nous avons examiné les résultats.