Créez un calendrier de clôture et reporting

Après avoir discuté de l’intérêt d’un tel calendrier, aujourd’hui, je vous propose un exercice. Créons un planning automatique et perpétuel, comme pour le modèle de calendrier que nous avions réalisé. Nous avons vu comment traiter les jours ouvrés dans Excel dans un précédent article, cela ne devrait donc pas nous poser de problème.


 

Instructions

Commencez par télécharger le fichier.

logo telecharger fichier

Dans le premier onglet se trouvent les instructions. La solution est dans le deuxième onglet.

Pour simplifier la tâche, j’ai renseigné les jours fériés de l’année ainsi que le jour de l’an N+1. Il sert dans le calcul du calendrier de décembre de l’année N, cette clôture étant à cheval sur les deux années.

Liste jours feries calendrier cloture

Deux variables sont libres : l’année et le mois (sous forme numérique entre 1 et 12). Entrez les valeurs que vous voulez, le calendrier devra se recalculer automatiquement. L’exemple est fait avec le mois de septembre 2016.

Variables annee mois calendrier cloture

Le champ Date de clôture doit être calculé pour afficher automatiquement le dernier jour du mois choisi à l’étape précédente. Il servira ensuite de point d’entrée pour le calcul des autres dates.

Champ calcule dernier jour mois calendrier cloture

Le calendrier à proprement parler, il est sommaire mais présente quelques étapes intéressantes. Surtout, il comporte à peu près tous les types de dates que l’on peut rencontrer pour des échéances. Le calcul attendu est dans la colonne Commentaire.

Liste taches calendrier cloture

Les échéances en J+.../J-... sont calculées par rapport au dernier jour du mois, en jours ouvrés évidemment.

Pour la réunion mensuelle, on calculera le 3ème lundi du mois et comme on est ambitieux, on traitera le cas où le lundi est férié et on décalera la réunion au premier jour ouvré suivant.

Pour la déclaration de TVA, elle a lieu le 24 de chaque mois mais de la même façon, si le 24 est chômé, nous la décalerons au premier jour ouvré précédent.
Même cas pour la déclaration URSSAF mais celle-ci n’a lieu qu’une fois à la fin de chaque trimestre civil. Pour les autres mois, la case sera laissée vide.

Pour vous aider, voici les liens vers 3 articles dans lesquels nous avons vu l'essentiel pour construire ce calendrier.

 

Résolution du problème

Le calendrier complet figure dans la feuille Solution. Nous allons voir quelques explications.

La date de référence, dernier jour du mois, est calculé comme le jour 0 du mois suivant.

Formule calcul dernier jour mois calendrier cloture

Ensuite, nous ajoutons une colonne dans laquelle nous reportons le délai par rapport à cette date de référence. Et nous l’utilisons dans la formule pour le calcul des 9 premières dates.

Calcul jours ouvres calendrier cloture

La date de la réunion mensuelle est sans doute la plus complexe.
Pour savoir comment calculer une date mobile mais avec un jour fixe dans le mois, lisez cet article.
Pour trouver le premier jour ouvré suivant, lisez cette astuce.

Calcul 3eme lundi mois calendrier cloture

Les deux dernières dates sont sur le même modèle, en plus simple quand même.
On prend le 25 du mois suivant pour la déclaration TVA et on trouve le jour ouvré qui précède avec la même astuce que ci-dessus.

Calcul declaration TVA calendrier cloture

Pour la déclaration URSSAF, on rajoute juste une condition pour qu’elle ne s’affiche que lorsque le mois vaut 3, 6, 9 ou 12.

Calcul declaration URSSAF calendrier cloture

Et voilà, notre calendrier est désormais complet.

 

Evidemment, ce planning comporte peu d’éléments mais vous pouvez l’adapter aux spécificités de votre entreprise. Tous les types de dates que l’on est susceptible de trouver sont a priori présentés avec une méthode pour chacun d’eux.
N’hésitez pas à nous dire en commentaires si vous avez d’autres calculs qui ne figureraient pas dans ce calendrier.

 

Abonnez-vous à la newsletter

Illustration couverture ebook 100 trucs et astuces pour Excel-ler au travail

Et recevez gratuitement l'ebook 100 trucs et astuces pour Excel-ler au travail (5€ sur Amazon)

Votre adresse mail ne sera pas communiquée et vous pourrez vous désabonner à tout moment.

Taggé , , .Mettre en favori le Permaliens.

Laisser un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.