Name: Apprenez à gérer le temps Concevez planning, échéanciers, tableaux d'absence avec Excel (versions Office 365, 2019 et 2016)
Rating: 5 (3 reviews)

Énoncé du projet

Nous allons construire un tableau qui nous permettra de suivre les factures fournisseurs et clients dans le but de prévoir le plus précisément possible l’état de notre trésorerie.

Voici quelques informations utiles :

Les factures fournisseurs seront saisies dès réception, et les factures clients dès création.
Les fournisseurs sont payés soit comptant soit sous 30, 45 ou 60 jours. Ils peuvent être payés en plusieurs fois (au maximum en 12 fois).
Les modes de paiement fournisseurs sont le prélèvement, la lettre de change, ou le chèque.
Les clients règlent à 30 jours par chèque ou par virement, avec possibilité de régler en plusieurs fois.
Les factures clients et fournisseurs doivent être saisies dans le même tableau, un moyen doit être prévu pour les différencier.

Autres besoins auxquels doit répondre ce tableau :

Déterminer le montant de la TVA à payer mensuellement.

Pour information, la TVA est payée par rapport aux encaissements et aux décaissements. C’est-à-dire lorsque le client a payé et que le fournisseur est réglé. Les opérations de vente sont soumises à un taux de TVA de 20 % et les opérations d’achat généralement soumises au taux de 20 %, peuvent...

Étape 1 : analyse du projet

Les questions-réponses

Quel est l’objectif principal à atteindre ? Déterminer l’état de la trésorerie par un suivi précis des échéances clients et fournisseurs.

Quelles sont les contraintes et/ou objectifs secondaires ?

Déterminer mensuellement l’état de la trésorerie.
Déterminer la date et le montant des échéances.
Tenir compte des paiements en plusieurs fois.
Calculer la TVA due.
Faire la différence entre client et fournisseur.
Imprimer un décompte de la TVA due par mois.
Déterminer les factures à payer selon une période définie.
Afficher ou imprimer la liste des factures pour un même fournisseur.
Automatiser les calculs pour gagner du temps.
Faciliter les recherches.
Déterminer le chiffre d’affaires par fournisseur.
Le tableau doit couvrir au minimum une année voire plusieurs.

À qui le résultat final est-il destiné ? Utilisation interne.

Sur quel support doit-il être présenté ? Fichier Excel, et papier.

Le concepteur est-il l’utilisateur ? Oui, mais il peut y avoir d’autres utilisateurs.

Qui devra saisir ou exploiter les données ? La saisie sera faite par une personne qui est aussi le concepteur, mais l’exploitation des données, la consultation peut être faite par d’autres...

Étape 2 : choix des commandes Excel

Le premier tableau à mettre en place est la liste des factures. Une partie des données est à saisir l’autre partie est à calculer. Mais c’est à partir des données de cette liste que toutes les données des autres tableaux devront être obtenues.

Voyons de quelles commandes nous allons avoir besoin.

Validation de données

Pour faciliter la saisie du taux de TVA. Nous créerons une liste avec les valeurs : 20 % - 10 % - 5,50 % - 0 %.
Pour choisir les modes de paiement, nous aurons une liste avec les valeurs suivantes : CHQ pour chèque, PREL pour prélèvement, LCR pour lettre de change (traite).
D’autre part, les factures client ou fournisseur seront saisies dans le même tableau. Nous aurons donc besoin de les différencier.

Une première solution consisterait à saisir les montants en négatif pour les factures fournisseur et en positif pour les factures client, mais le risque d’erreur de saisie serait trop grand, et nous ne pourrions pas gérer les avoirs. Une deuxième solution consisterait à créer une colonne type tiers dans laquelle l’information client ou fournisseur serait saisie.

Cette dernière solution est plus intéressante, car elle permet de saisir les montants des factures sans se soucier du signe.

Pour gagner du temps nous limiterons la saisie à une lettre, C pour client et F pour fournisseur. Le rôle de la validation des données sera d’interdire toute autre saisie en dehors de ces deux lettres.

Fonction SI

Pour plus de détails sur le principe de fonctionnement de la fonction Si, reportez-vous au chapitre Construire rapidement l’ossature d’un planning annuel.

Cette fonction sera utilisée pour le calcul :

des dates d’échéance,
du montant des échéances,
et pour le contrôle de la saisie manuelle des échéances.

Automatiser le calcul des dates d’échéance

L’un des objectifs secondaires est d’imprimer la liste des factures à payer. Pour ce faire, il nous faut calculer les différentes dates de paiement, dans les 12 colonnes prévues à cet effet.

Pourquoi 12 colonnes ? Uniquement parce qu’il est prévu un échelonnement...

Étape 3 : gestion des échéances pas à pas

Pour débuter, ouvrez le fichier Echéance_début.

Limiter la saisie à deux valeurs pour la colonne Type Tiers

Le but est d’interdire la saisie de toute autre valeur en dehors de F ou C, et d’afficher un message d’erreur.

Sélectionnez les cellules C2 à C13.

Onglet Données - groupe Outils de données - bouton Validation des données images/06SOB17N.PNG - Validation des données.

La boîte de dialogue Validation des données s’affiche.

Dans la liste déroulante Autoriser choisissez l’option Personnalisé.

Cliquez dans la zone Formule puis saisissez =OU(C2="C";C2="F")

Cliquez sur l’onglet Alerte d’erreur.

Cochez la case Quand des données non valides sont tapées pour accéder aux différentes zones.

Choisissez Stop dans la zone Style.

Saisissez Erreur de saisie dans la zone Titre.

Saisissez Attention ! Saisir C pour client ou F pour fournisseur dans la zone Message d’erreur.

La zone Titre correspond au titre de la boîte de dialogue qui s’affichera.

Cliquez sur OK.

Faites un essai et saisissez en cellule C11 la lettre A.

Validez par Entrée.

Vous obtenez le message suivant :

Il y a trois styles d’alerte d’erreur : Stop, Avertissement, Informations. Seul le style Stop interdit la validation de la valeur saisie si elle est erronée.

Cliquez sur Annuler puis saisissez les valeurs suivantes dans les cellules C2 à C13 :

Création des listes déroulantes Taux de TVA et Mode paiement

Nous allons mettre en place les éléments des deux listes.

Sélectionnez la cellule BA1.

Plutôt que d’utiliser la barre de défilement horizontale, saisissez BA1 dans la zone Nom de la barre de formule puis Entrée. Le déplacement est immédiat.

Saisissez les informations comme ci-dessous :