Excel : Traiter des données d’année fiscale dans un tableau croisé dynamique

Il n’est pas toujours facile de travailler dans Excel avec des années fiscales qui ne correspondent pas aux années calendrier, par exemple, dans le cas d’une entreprise dont l’année financière débuterait le 1er avril et se terminerait le 31 mars. En général, cela ne pose aucun problème lors de l’élaboration d’un modèle financier car on ne fait que débuter le modèle par le mois d’ouverture, par exemple avril, et on termine par le mois de fermeture, dans ce cas-ci mars, et s’il y a lieu, on insère une section permettant de calculer les montants cumulés dans une section à côté de la section mensuelle. La difficulté survient plutôt lorsqu’on travaille avec des tableaux croisés dynamiques ou des modèles de données. Cet article se limite à la question des tableaux croisés dynamiques mais un autre article traitera prochainement de la manipulation des dates fiscales dans un modèle de données et notamment de la façon d’insérer une hiérarchie de dates dans Power Pivot.

Première solution… incorrecte

Il serait possible de travailler “manuellement” les dates fiscales dans un tableau croisé dynamique, mais le tout comporte quelques limites. Pour ce faire, il faudrait simplement ajouter une colonne “Mois” à la base de données d’origine et y insérer le nom des mois correspondants. Par exemple, ci-bas, j’ai utilisé la fonction TEXT (ou TEXTE en français) avec l’abbréviation “mmm” pour obtenir le nom des mois où a eu lieu chacune des transactions de vente de ma base de données. Idéalement, on n’ajouterait pas de colonnes calculées dans la base de données Excel pour ne pas l’alourdir inutilement. On passerait plutôt, par exemple, par Power Query ou Power Pivot ou encore, on utiliserait une macro VBA pour copier la formule aux nouvelles données et les coller ensuite en valeurs.

Présentation erronée des données dans le tableau croisé dynamique

Ci-bas, j’ai élaboré 4 tableaux croisés dynamiques. Le 1er tableau est un tableau régulier, i.e. selon les dates calendrier (et non les dates fiscales). En-dessous de ce tableau, on aperçoit un tableau croisé dynamique cumulatif pour présenter les résultats cumulés (ou YTD si vous préférez). Il est ensuite possible, à droite, de présenter les mêmes données mais en déplaçant le mois d’avril en première position (vous pouvez le faire manuellement, en cliquant sur les étiquettes de lignes). Cela fonctionne bien à une exception près… le nom des années fiscales n’est pas approprié… et par conséquent, les montants non plus! Si vous regardez bien, les ventes d’avril 2012 dans le premier tableau, elles sont de 1 373,24$ et c’est bien ce qui est traduit dans le tableau de droite. Par contre, dans le tableau de droite, en janvier, on reprend les ventes de janvier 2012, alors qu’on devrait présenter les ventes d’avril 2013!

Deuxième solution… plus adéquate

Dans l’exemple ci-bas, j’ai ajouté un petit tableau à la droite de ma base de données. Ce tableau sert à convertir les mois et les années calendrier (MC et AC) en mois et en années fiscales (MF et AF). À noter que la fonction IF se traduit par SI en français.

Ici, j’utilise des formules simples. À noter qu’une multitude de formules arriveraient au même résultat. Vous pourriez également simplement entrer les données correspondantes en valeurs mais si vous souhaitez pouvoir agir dynamiquement sur la case K5 (premier mois de l’année fiscale), vous devrez utiliser des formules.

Ajout de colonnes calculées dans la base de données

Tel que mentionné plus tôt, l’idéal serait de ne pas ajouter de colonnes calculées directement dans la base de données dans Excel pour éviter des problèmes de performance. Il serait préférable de passer par Power Query, Power Pivot ou d’utiliser un macro VBA pour ensuite copier les données en valeurs. Cela dit, encore une fois, une tonne de formules pourraient arriver au même résultat que celle que j’utilise ci-bas mais j’aime bien l’utilisation de la fonction SUMIFS (ou SOMME.SI.ENS en français) pour le traitement de ce genre de données. Cette fonction peut être utilisée car toutes les combinaisons MC (mois calendrier) et AC (année calendrier) sont uniques.

On peut donc insérer une colonne calculée pour indiquer le mois fiscal (MF) et l’année fiscale (AF). Bien sûr, nous pourrions également ajouter des colonnes pour calculer directement les ventes cumulées, pour les mois et les années fiscales, dans la base de données mais encore une fois, je veux éviter de l’alourdir inutilement. Je vais donc plutôt traiter le tout directement dans les tableaux croisés dynamiques ci-haut et ci-bas.

Dans l’exemple ci-dessous, le premier tableau croisé dynamique présente les ventes par mois et par année calendrier alors que le deuxième tableau croisé dynamique présente les données par mois et par année fiscale. Ainsi, les ventes des 3 premiers mois de 2012 (janvier, février et mars) deviennent les ventes des 3 derniers mois de l’année fiscale “2012”, puisque l’année fiscale débute en avril, avec les ventes de 1 373.24$. L’incohérence de la solution illustrée en 1 n’est plus.

Vous devez consolider des données, les analyser, les interpréter et présenter des conclusions utiles pour des fins de prise de décision ? Apprenez à développer des solutions robustes et automatisées avec nos formations en Tableaux de bord avec Excel.

On peut ensuite reprendre ces deux tableaux croisés dynamiques et présenter les données en mois cumulés.

Pour présenter les résultats cumulés dans un tableau croisé dynamique, utilisez l’option “Affichez les valeurs” (Show Values As) dans les paramètres de champs de valeurs et choisissez l’option “Résultats cumulés par” (Running Total In) dans le champ de base MC ou MF, selon que vous soyez dans le tableau croisé dynamique en année calendrier ou fiscale.

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Pour une introduction à l’élaboration d’un tableau de bord dans Excel, et ainsi apprendre à alimenter, structurer et présenter vos données, suivez notre formation Excel – Tableaux de bord (niveau 1).

Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Tableaux de bord (niveau 1) :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.