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

Publié le 04 janvier 2015
par Sophie Marchand M.Sc., CPA, CGA, MVP
Année fiscale - Tableau croisé dynamique

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.

Année fiscale - Tableau croisé dynamique

 

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!

Année fiscale - Tableau croisé dynamique

 

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.

Année fiscale - Tableau croisé dynamique

 

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.

Année fiscale - Tableau croisé dynamique

 

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.

Année fiscale - Tableau croisé dynamique

 

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.

Année fiscale - Tableau croisé dynamique

 

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.

Année fiscale - Tableau croisé dynamique

 

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

Année fiscale - Tableau croisé dynamique

 

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.

Année fiscale - Tableau croisé dynamique

 

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

2 réflexions sur “Excel: Traiter des données d’année fiscale dans un tableau croisé dynamique”

  1. Bonjour,
    Petite question en rapport à ça. Est-il possible d’utiliser les segments chronologiques lorsque l’année financière est différentes de l’année calendrier? J’ai eu desproblèmes dans le passé, sans compter que notre calendrier était composé de mois de 28 ou 35 jours (semaines pleines). Merci.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top