Rapport sur les prévisions glissantes en Excel En-tête du blog XLCubed

Reporting avec Réalisé et Prévisionnel glissant

Dans les rapports financiers, il est très courant de souhaiter un affichage mensuel du réalisé et du prévisionnel sur l'ensemble de l'année. La plupart du temps, l'on souhaite afficher le réalisé pour les mois passés et compléter les mois futurs avec les données du prévisionnel, afin que la précision du total annuel s'affine au fur et à mesure de l'avancement dans l'année.

Comme Excel est un environnement très flexible, ce type de rapport est souvent construit en utilisant un mélange de copier-coller et de formules. Bien que cette approche permette d'atteindre l'objectif, cette façon de faire nécessite un grand nombre de manipulations à chaque mise à jour mensuelle.

Le défi

Pour construire ce type de rapport, le plus grand défi réside souvent dans le fait que les données du réalisé et du prévisionnel sont stockées dans deux mesures différentes ou dans deux membres d'une hiérarchie de type "scénarios". Avec la plupart des outils clients, le réalisé et le prévisionnel seront tous deux affichés pour chacun des mois pour lesquels des données existent. Ce que l'on cherche à afficher est cependant différent : on souhaite voir le réalisé existant et compléter les mois restants avec le prévisionnel.

Tableau XLCubed dans Excel montrant les données réelles et prévues pour tous les mois

Ce que l'on souhaite réellement en réalité c'est un rapport dynamique qu'il ne soit pas nécéssaire de modifier ou de recommencer chaque mois. Dans un article de blog précédent, il y a quelques années, nous avions montré comment créer ce genre de rapport en s'appuyant sur la fonctionnalité "Garder / Exclure de l'affichage". Bien que cette approche soit toujours valide, il existe désormais une façon plus intuitive d'obtenir le même résultat grâce à la fonctionnalité "Conserver - Conserver les membres et créer un jeu dynamique".

Continuer à lire "Rapport glissant sur les chiffres réelles et les prévisions"

Rolling Forecast reporting in Excel

Dans les rapports mensuels pour l'année en cours, les chiffres les plus pertinents pour les périodes fermées sont les chiffres réels, et pour les mois ouverts ou futurs, les prévisions. Dans la plupart des cubes, les chiffres réels et les prévisions (ou le budget) sont mis en œuvre soit comme des mesures différentes, soit comme partie d'une hiérarchie de scénarios. Cela peut entraîner des difficultés dans l'établissement des rapports, car si vous placez la période et les chiffres réels/prévus sur le même axe, vous obtiendrez des mois en double qui montrent les prévisions pour des périodes où les chiffres réels sont déjà présents, comme dans le tableau croisé dynamique ci-dessous.

 

 RP10

Nous souhaiterions que le rapport soit asymétrique (c'est-à-dire que les mois retournés soient différents pour le réel et le budget). Différents outils peuvent gérer cela, et il a toujours été possible d'y parvenir dans XLCubed grâce à des formules ou dans une grille. Le plus difficile est de rendre le rapport entièrement dynamique sans devoir le redessiner à chaque fois. L'idéal serait que l'utilisateur puisse changer de trancheur pour sélectionner le dernier mois écoulé (ou le reprendre directement dans le cube), et que les mois appropriés soient reportés dans les rubriques Réel et Budget. Voici comment y parvenir dans une grille....

L'approche utilise une combinaison de :

  • Fourchettes Excel pour déterminer les mois disponibles, et qui sont pour Réel et Budget
  • Une trancheuse XLCubed pour choisir les "dernières actualités".
  • Une grille XLCubed avec la mention "Exclure de l'affichage" pointant vers les plages pertinentes ci-dessus

Voyons cet exemple qui utilise le cube Finance de la base de données AdventureWorks DW 2008R2.

Nous allons utiliser les données de l'exercice 2006 et communiquer les chiffres réels et budgétaires de tous les services. Nous utiliserons un slicer pour sélectionner le dernier mois réel que nous voulons rapporter. Pour les autres mois de l'exercice 2006, nous communiquerons les valeurs budgétaires.

Année entière de Mois dans le rapport

Il existe plusieurs moyens d'y parvenir - nous avons ajouté un trancheur piloté par Excel (XLCubed - Slicer - Excel) basé sur la fourchette $AA$1:$AB$12 comme indiqué ci-dessous, contenant tous les mois de l'exercice 2006. Cela permet à l'utilisateur de choisir le dernier mois pour lequel nous voulons rapporter les chiffres réels.

 

RP1

RP3

 

 

 

 

 

 

 

 

 

Dans $C$1, la formule =VALUE(B1) convertit le texte produit par le trancheur en un nombre que nous utiliserons pour calculer quels mois doivent déclarer le réel et quel budget, voir la capture d'écran ci-dessous :

RP2

 

 

 

 

Tableau des dates

Il s'agit d'une plage Excel qui soutient la logique de déclaration. La rangée 2 contient tous les mois de l'année de déclaration. La rangée 3 attribue un numéro supplémentaire à chaque mois, de 1 à 12.

We can then compare the value of the selected month from the slicer ($C$1) with the value for each month. Basically if the month is less than or equal to the selected last actuals month it should be reported as Actuals, otherwise as Budget.  An example formula contained in B4 for Actuals would be: =IF(B3<=$C$1,B2,””) . For the Budget row the formula has the inverse logic.

À ce stade, nous avons utilisé un peu de XLCubed et la flexibilité d'Excel pour faire correspondre les mois appropriés aux données réelles et au budget. Nous devons maintenant ajouter le rapport lui-même.

Nous allons créer notre grille - dans un premier temps, nous l'avons configurée pour présenter les chiffres du budget et du réel sur toute l'année, en basant la sélection de Date.Fiscal sur la fourchette contenant tous les mois de l'année ( G$2:M$2).

Nous utilisons ensuite le menu contextuel de XLCubed (Keep - Exclude From Display) pour exclure les membres. Peu importe les tranches qui sont exclues à ce stade. Pour ce faire, sélectionnez "Actual", puis le premier mois disponible et enfin "Exclude From Display". Répétez cette opération pour "Budget" et le premier mois disponible. Nous exclurons juillet 2005 pour "Réel" et août 2005 pour "Budget". Vous remarquerez le marqueur de commentaire rouge sur la grille. Cliquez avec le bouton droit de la souris sur cette cellule et choisissez Axe - Modifier, puis allez à l'onglet Tranches exclues.

RP6 RP7

 

 

 

 

 

 

 

 

Les captures d'écran ci-dessus montrent que nous excluons actuellement le mois de juillet 2005 des données réelles ainsi que le mois d'août 2005 du budget.

Nous pouvons alors facilement modifier la sélection de la date de l'exercice pour chaque scénario. Pour le Budget, nous exclurons les valeurs du Budget pour les mois dans les cellules $B$4:$M$4 (car nous voulons rapporter les Réels pour ces entrées) et les valeurs des Réels pour les mois dans les cellules $B$5:$M$5 (car nous voulons rapporter les Budgets pour ces entrées).

RP8

Le rapport de la grille ressemble maintenant à ceci, les chiffres réels étant rapportés jusqu'en novembre 2005 et budgétisés pour le reste de l'exercice 2006 :

RP5

Nous avons également activé l'option Fusionner les cellules répétitives (dans Propriétés de la grille). Vous pouvez effectuer un formatage supplémentaire, par exemple en colorant toutes les valeurs de Budget et en masquant les en-têtes de grille.

RP9

Le résultat final est un rapport à grille unique donnant la combinaison Réel:Budget du mois que nous voulons avec le processus de maintenance mensuelle, un cas de simple changement de sélection d'une boîte combinée.