Créer un échéancier sous Excel

Pour gérer vos factures, il vous sera utile de créer un échéancier Excel pour connaître toutes les échéances et les majorations si besoin.

Dans ce tutoriel, nous allons créer le même échéancier que j’utilise pour mes factures. Néanmoins, vous pourrez, bien sûr, le présenter comme vous le souhaitez.

Pour commencer, nous allons créer les titres, les bordures, modifier les formats, pour ensuite terminer par les formules.

Dans mon échéancier, j’ai 2 onglets : l’un qui s’appelle Échéancier et l’autre CA facturé.

Dans le premier, j’ai la liste de mes factures et, dans le second, j’ai le chiffre d’affaires généré par chaque client et le chiffre d’affaires total.

Création des titres :

Pour commencer, je saisis dans la cellule A1 du premier onglet Date du jour et de E1 à G1 (en les fusionnant avec le bouton

 de l’onglet Accueil) Taux d’intérêt légal pour l’année 2013.

Sur la ligne 4, je saisis tous les entêtes de mon tableau, à savoir :

• Raison sociale (et/ou Nom si vous avez des particuliers en tant que client)
• N° facture
• Date facture
• Date d’échéance : date limite de paiement avant la majoration
• Montant : montant de la facture
• Débours : étant auto-entrepreneur, certains frais peuvent être comptés comme des frais de débours (renvoi par La Poste de documents remis par le client par exemple). Dans ce cas, le montant déclaré sera le montant de la colonne Montant (sauf si une majoration est appliquée) et le montant de la facture sera celui de la colonne Total facture.
• Total facture : égal à Montant + Débours
• Jours en dépassement : cette colonne comptera les jours écoulés entre Date d’échéance et Date du jour pour le calcul de la majoration
• Majoration : montant de la majoration en cas de dépassement de la date d’échéance
• Solde restant dû : égal à Total facture (Montant + Débours) + Majoration
• Réglée : j’affiche simplement En attente lorsque la facture n’est pas encore payée, puis Validée lorsque le paiement est reçu
• Première/Deuxième et Troisième relance : ces colonnes permettent d’écrire les dates des relances si nécessaires.
• Observations : cette colonne permet d’inscrire des informations supplémentaires concernant la facture, le paiement ou la relance.

Étant auto-entrepreneur, je ne facture pas de TVA, mais si c’est votre cas, vous rajouterez une colonne pour cette dernière.

Pour tous les titres, j’applique du bleu avec comme couleur RVB 51-102-255, je les place en gras et je les centre. J’applique une bordure fine à chaque élément.

Format des cellules :

En H1, je clique, dans le groupe Nombre de l’onglet Accueil, sur le menu déroulant qui est par défaut sur Standard, je le change en Pourcentage.

Pour les colonnes Raison sociale, N° facture, Réglée, Première relance, Deuxième relance Troisième relance et Observations, je laisse le format Standard.

Pour les colonnes Date facture et Date d’échéance, j’applique le format Date courte.

Pour les colonnes Montant, Débours, Total facture, Majoration et Solde restant dû, j’applique le format Monétaire.

Enfin, pour la colonne Jours en dépassement, j’applique le format Nombre que je modifie dans la boîte de dialogue Format de cellule pour ne pas avoir de décimale.

Insertion des formules :

• Date du jour : en B1, je saisis la formule =AUJOURDHUI. Comme elle n’a pas d’argument, je la termine par () (parenthèse ouvrante et fermante). Cette formule n’est pas obligatoire, car vous pouvez insérer celle-ci à l’intérieur d’une formule mais cela permettra de ne pas avoir trop d’arguments dans vos formules et d’avoir la date d’un seul coup d’œil.
• Taux d’intérêt légal : en H1, je saisis 0,04 qui correspond au taux d’intérêt légal pour cette année. Il sera bien sûr à modifier manuellement aux alentours des mois de février et mars (mois de publication du nouveau taux).
• Total facture : en G5, je saisis =E5+F5 qui additionne Montant et Débours.
• Jours en dépassement : en H5, nous allons combiner 2 formules car le but est d’avoir le nombre de jours entre Date facture et Date d’échéance seulement si la Date d’échéance est dépassée. Les 2 formules à combiner sont la formule SI et la formule DATEDIF. La formule DATEDIF se compose des 2 dates dont vous souhaitez calculer l’écart et le format vers lequel renvoyer la valeur, c’est-à-dire d (day) pour jour, m (month) pour mois et y (year) pour année. Il existe également 3 autres formats mais nous y reviendrons dans un prochain article. Pour recopier facilement cette formule, nous allons bloquer la cellule B1 avec le signe du dollar puisque la date sera toujours la même.
  Notre formule est donc =DATEDIF(D5;$B$1; »d »).
  Pour la formule SI, il suffit d’ajouter la condition pour que la formule DATEDIF s’applique et la valeur à afficher si la condition n’est pas respectée. Nous souhaitons afficher le résultat de DATEDIF uniquement si la date d’échéance est indiquée donc différente de 0 sinon elle affiche 0.
  La formule complète est =SI(D5>0;DATEDIF(D5;$B$1; »d »);0) donc Si Date d’échéance est supérieur à 0 Alors Excel affiche le nombre de jours entre la Date du jour et la Date d’échéance Sinon il affiche 0.

• Majoration : en I5, nous avons également besoin de la formule SI qui doit donner Si Date d’échéance est différente de 0 Alors Excel calcule Total facture x Jours en dépassement x Taux d’intérêt légal pour l’année 2013 x 3 + 40 € Sinon il affiche 0. Le taux d’intérêt légal doit être multiplié par 3 au minimum et je le place entre parenthèses à cause de la règle de priorité des calculs. J’ajoute également 40 €, qui depuis 2013, est une mention qui doit obligatoirement figurer sur les factures, c’est une indemnité forfaitaire pour frais de recouvrement due au créancier en cas de retard de paiement. La formule est =SI(D5>0;G5*H5*($H$1*3)+40;0). Vous pouvez aussi multiplier le taux directement en H1, ce qui donnera dans cette cellule =0,04*3/100 et en I5, =SI(D5>0;G5*H5*$H$1+40;0).
• Solde restant dû : en J5, je saisis =G5+I5 qui additionne Total facture et Majoration.

Pour terminer, il ne vous reste plus qu’à recopier vos formules sur toutes vos lignes.

Pour le second onglet, dans la colonne A, vous allez saisir les uns en dessous des autres les noms de vos clients et vous terminerez par une ligne TOTAL. Écrivez bien les noms de la même manière, la casse n’est pas importante, mais si vous utilisez des initiales dans le premier onglet, faites de même dans le second.

À la colonne B, le chiffre d’affaires généré par chaque client sera affiché. Pour cela, nous allons utiliser la formule SOMME.SI. Elle se compose de la plage où se trouvent les données sur lesquelles portent le critère, le critère selon lequel additionner et la plage des sommes à additionner.

La formule en B1 sera =SOMME.SI(‘Échéancier’!A5:E50;A1;’Échéancier’!E5:E50) qui indique que la plage où se trouve le critère sont les cellules A5 à E50 de l’onglet Échéancier, que le critère est le nom du client qui se trouve en A1 et que les sommes à additionner se trouvent parmi les cellules E5 à E50 de l’onglet Échéancier.

À chaque ajout de nouvelle facture, la formule SOMME.SI ajoutera le total dans la case correspondante, tant que la facture est encore dans la plage de cellules indiquée, auquel cas, il faudra étendre la plage.

Enfin, à la ligne TOTAL, il suffira d’utiliser la Somme automatique de l’onglet Accueil

 ou Formules .

Téléchargez « Créer un échéancier sous Excel » au format PDF