Grâce à Excel, vous pouvez créer facilement un devis, puis une facture et, avec certaines formules bien placées, vous pourrez automatiser plusieurs actions.
Tout d'abord, revoyons les mentions devant figurer obligatoirement sur tous les devis et factures que vous établirez au risque de vous exposer à une sanction pénale et une amende fiscale. Un devis n'est pas obligatoire sauf dans certaines professions mais il est recommandé et, dans ce cas, il doit comprendre certaines mentions.
- La date du devis
- Le nom et l'adresse de la société
- Le nom du client
- La date de début et la durée estimée des travaux ou de la prestation
- Le décompte détaillé de chaque prestation, en quantité et en prix unitaire
- Le prix de la main d'œuvre
- Les frais de déplacement
- Les conditions du service après-vente (garantie notamment)
- La somme globale à payer HT et TTC
Dans certains cas, il doit comprendre :
- La durée de validité de l'offre
- Le caractère gratuit ou payant du devis
- La date d'émission de la facture
- Le numéro de facture (numéro unique et chronologique)
- La date de la vente ou de la prestation de service (jour effectif de la livraison ou de la fin d'exécution de la prestation)
- L'identité de l'acheteur : nom (ou dénomination sociale) et adresse (sauf opposition de sa part, pour un particulier)
- L'identité du vendeur ou prestataire (nom patronymique d'un entrepreneur individuel avec éventuellement son nom commercial, la dénomination sociale d'une société suivie du numéro Siren et du code NAF, le numéro RCS ou RM, l'adresse du siège social)
- L'adresse de livraison qui peut être différente de celle de l'acheteur
- Le numéro individuel d'identification à la TVA du vendeur et du client professionnel (sauf pour les factures d'un montant total HT inférieur ou égal à 150 €)
- La désignation du produit (nature, marque, référence, etc.) ou de la prestation (ventilation des matériaux fournis et de la main-d'œuvre)
- Le décompte détaillé, en quantité et prix, de chaque prestation et produit fourni (facultatif si la prestation de service a fait l'objet, préalablement à son exécution, d'un devis descriptif et détaillé, accepté par le client et conforme à la prestation exécutée)
- Le prix unitaire hors TVA des produits vendus ou taux horaire hors TVA des services fournis (prix catalogue)
- Les majorations éventuelles de prix (frais de transport, d'emballage, etc.)
- Les taux de TVA légalement applicables et le montant total de la TVA correspondant (si les opérations sont soumises à des taux de TVA différents, il faut faire figurer sur chaque ligne le taux correspondant)
- Les éventuelles réductions de prix (rabais, ristourne, remise) acquises à la date de la vente ou de la prestation de service et directement liées à cette opération, à l'exclusion des escomptes non prévus sur la facture
- La somme totale à payer hors taxe (HT) et toutes taxes comprises (TTC)
- La date ou délai de paiement (date à laquelle le règlement doit intervenir) et conditions d'escompte applicables en cas de paiement à une date antérieure (en cas d'absence d'escompte, il faut mentionner " Escompte pour paiement anticipé : néant ")
- Le taux des pénalités de retard exigibles en cas de non-paiement à la date de règlement (les pénalités de retard sont exigibles sans qu'un rappel soit nécessaire)
- L'indemnité forfaitaire de 40 € pour frais de recouvrement, en cas de retard de paiement
Dans certains cas :
- Si le vendeur ou prestataire est membre d'un centre de gestion ou d'une association agréée, la mention suivante doit être ajoutée : " Membre d'une association agréée, le règlement par chèque est accepté. "
- Si le vendeur ou prestataire bénéficie de la franchise en base de TVA, la facture est en hors taxe et doit porter la mention suivante " TVA non applicable, article 293 B du CGI "
Dans le classeur Excel, j'ai créé une feuille avec la liste des produits et services à vendre ( Offre commerciale), la liste des Prospects et la liste des Clients, sachant que chaque élément possède une référence ou un numéro d'identification et est suivi de ses caractéristiques.
Je crée un nouvel onglet que je nomme Devis. Pour que vos calculs se mettent à jour automatiquement, n'oubliez pas d'activer le calcul automatique dans l'onglet Formules > Options de calcul > Automatique.
Dans le coin supérieur gauche, j'insère le logo et je saisis les informations concernant ma société (nom de l'entreprise, adresse, téléphone, e-mail, site internet, SIRET).
De l'autre côté, à droite, je crée un encadré en utilisant la fusion des cellules
Je vais indiquer ces informations en dessous de leur titre avec des formules.
Formule pour la date : =AUJOURDHUI()
Cette formule n'a pas d'arguments et elle indiquera la date du jour automatiquement.
Formule pour le numéro du devis :=CONCATENER(" SF ";ANNEE(MAINTENANT()); "0″;MOIS(MAINTENANT());JOUR(MAINTENANT()))
Cette formule permet d'afficher, en une seule cellule, les initiales de la société, suivies de l'année, du mois (arrivé à octobre, il faudra retirer le 0 de la formule, ce dernier permet simplement de garder un ordre chronologique) et du jour. Là aussi, cette formule se mettra à jour selon la date du jour. Si plusieurs devis doivent être établis en une journée, il suffira de rajouter un chiffre à la fin. Pour cela, avant la dernière parenthèse fermante, ajoutez ;1. Ce qui donne =CONCATENER(" SF ";ANNEE(MAINTENANT()); "0″;MOIS(MAINTENANT());JOUR(MAINTENANT());1) et vous n'aurez plus qu'à changer le numéro 1, en 2, 3, 4, etc.
Maintenant, nous allons indiquer les coordonnées du prospect qui devront être préalablement saisies sur la feuille Prospects. Ce qui suit est une manière de faire mais n'est pas obligatoire. Vous pouvez tout à fait saisir les coordonnées vous-même, mais la même formule sera utilisée pour les factures, ce qui évitera de saisir plusieurs fois les mêmes informations.
Nommez le tableau Prospect en sélectionnant toutes les cellules du tableau et en allant, dans l'onglet Formules > Définir un nom
En cas d'ajout de prospect à la suite du tableau, retournez dans l'onglet Formules, puis Gestionnaire de noms
Profitez-en pour créer un nom aux numéros de prospects, que vous nommerez Numéro_prospect.
Je retourne ensuite sur l'onglet Devis où je saisis Numéro prospect et, à droite, je crée une liste déroulante qui va être basée sur les numéros des prospects. Pour cela, je me place sur la cellule où faire apparaître la liste et je clique, dans l'onglet Données, sur Validation des données
Désormais, dans cette cellule, j'ai un menu déroulant avec les numéros de prospect.
Pour afficher les coordonnées sous cette forme :
M. Patrick GIRAUD
41, rue Émile Dubois
95000 CERGY
Je vais utiliser les fonctions Concaténer et RechercheV en les associant.
Formule pour la première ligne : =CONCATENER (RECHERCHEV(E9;Prospects;2); " " ;RECHERCHEV(E9;Prospects;3) ; " " ; RECHERCHEV(E9;Prospects;4) )
En orange est représentée la fonction Concaténer qui permet d'accoler plusieurs informations comme pour le Numéro de devis. C'est elle qui débute la formule pour assembler les données de la fonction RechercheV. Les points-virgules isolent chaque argument, et les guillemets avec espaces permettent d'insérer des espaces entre les informations pour ne pas qu'elles soient collées.
En vert est représentée la fonction RechercheV qui permet d'aller rechercher les informations par rapport au choix que vous aurez fait au niveau du menu déroulant du numéro de prospect.
Chaque groupe fonctionne de la même manière :
- E9 correspond à la cellule qui contient, dans notre exemple, le numéro prospect. C'est à partir de ce renseignement que la valeur à renvoyer sera recherchée.
- Prospects correspond au tableau où se trouvent les informations contenant les numéros prospects ainsi que les autres informations les concernant.
- 2 correspond au numéro de la colonne où se trouve la valeur que je souhaite afficher sachant que la colonne A = 1, B = 2, C = 3, etc.
Pour la deuxième ligne, vous n'avez pas besoin de la formule Concaténer sauf si vous utilisez une colonne Adresse2 pour les adresses longues : =RECHERCHEV(E9;Prospects;5)
Enfin, pour la 3 e et dernière ligne, la formule est calquée sur le même modèle que la première : =CONCATENER (RECHERCHEV(E9;Prospects;6) ; " " ; RECHERCHEV(E9;Prospects;7) )
Désormais, lorsque vous choisirez un numéro prospect grâce au menu déroulant, toutes les informations se mettront à jour, et en cas de modification dans le tableau des prospects, la mise à jour sera également effective.
Pour en savoir plus sur la formule Concaténer :Pour en savoir plus sur la formule RechercheV :
" Dispensée d'immatriculation en application de l'article L123-1-1 du Code de Commerce ou en application du V de l'article 19 de la loi n°96-603 du 5 juillet 1996 relative au développement du commerce et de l'artisanat. " http://www.votreassistante.net/a-quoi-sert-la-recherchev-excel-et-comment-utiliser/ http://www.votreassistante.net/fusionner-deux-cellules-en-une-excel-formule-concatener/
En tant qu'auto-entrepreneur, je note cette phrase :
Comme sur tout devis, j'indique l' Estimation de la durée de la prestation.
Puis, je détaille chaque prestation dans un tableau avec comme entête Référence, Prestation, Quantité, Prix unitaire et Total.
Pour automatiser ce tableau, j'utilise de nouveau un menu déroulant, la RechercheV et le calcul.
Je nomme d'abord les plages de cellules : Offre pour l'ensemble du tableau se trouvant dans l'onglet Offre commerciale, et Référence uniquement pour la colonne Référence du même onglet.
Je crée un menu déroulant dans ma colonne Référence de l'onglet Devis comme réalisé précédemment avec comme source =Référence.
Dans la colonne Prestation, je saisis la formule =RECHERCHEV(A26;Offre;2) et dans la colonne Prix unitaire, je saisis =RECHERCHEV(A26;Offre;3), A26 faisant référence à la liste déroulante. Pour terminer, je saisis =C26*D26 dans la colonne Total pour multiplier la Quantité par le Prix unitaire.
En recopiant ce menu déroulant et ces formules pour chaque ligne, je n'aurai qu'à choisir une prestation dans le menu déroulant et à indiquer une quantité : tout se calculera automatiquement.
J'indique les garanties sous le tableau : Chaque prestation (hors formation) est garantie 30 jours.
Puis, Devis valable 30 jours soit jusqu'au et j'insère la formule =AUJOURDHUI()+30. Celle-ci permet de calculer directement 30 jours à partir de la date du jour.
Création de la facture :J'affiche ensuite le Total HT et insère la formule =SOMME(E26:E35) qui additionne toutes les lignes de la colonne Total du tableau.
Enfin, je termine par Acompte demandé qui n'est pas obligatoire mais j'ai pour habitude de demander 30 % du montant du devis au-delà de 150 €. Si vous demandez un acompte, quel que soit le montant, insérez la formule =E43*0,3. Sinon, insérez la suivante qui contient la condition de 150 € minimum : =SI(E43>150;E43*0,3;0), E43 correspondant au Total HT.
Votre devis est terminé. N'oubliez pas d'appliquer des formats Date courte à vos cellules contenant des dates, et Monétaire à vos cellules contenant des sommes grâce au menu déroulant de l'onglet Accueil. De cette manière, ces dernières seront automatiquement arrondies à 2 chiffres après la virgule.
Pour la création de la facture, je duplique l'onglet Devis par un clic droit > Déplacer ou copier.... Je coche la case Créer une copie et je double-clique sur Offre commerciale dans la liste.
Dans ce nouvel onglet, que je renomme Facture, je remplace le mot DEVIS par FACTURE.
Je remplace Numéro prospect par Numéro client et je nomme le tableau des clients avec le nom Clients et les numéros de clients avec le nom Numéro_client comme fait précédemment dans l'onglet Prospects.
Je modifie le menu déroulant en recliquant sur Validation des données
Puis, je remplace la ligne Estimation de la durée de la prestation par Mode de paiement et j'insère 2 cases à cocher ( Virement et Chèque) en allant dans l'onglet Développeur > Insérer > Contrôles de formulaire > Case d'option 
Faites un clic sur votre feuille Excel et, pour modifier le nom de la case, cliquez droit dessus > Modifier le texte.
Sous le tableau, j'indique :
- La garantie : Chaque prestation (hors formation) est garantie 30 jours.
- Facture établie selon le devis : N°_____ en date du __/__/____ à remplir manuellement.
- Date d'échéance le : . Vous pouvez utiliser la formule =AUJOURDHUI()+8 pour une échéance à 8 jours après l'établissement de la facture.
- Au-delà de cette date, votre facture sera majorée (cf. Article ... des Conditions Générales de Prestations de Services/Conditions Générales de Vente).
- Tout professionnel en situation de retard de paiement est désormais de plein droit débiteur à l'égard du créancier d'une indemnité forfaitaire de 40 € pour frais de recouvrement, en sus des indemnités de retard (loi n°2012-387 du 22/03/2012).
- Escompte pour paiement anticipé : néant
- TVA non applicable, article 293 B du C.G.I.
Puis (selon l'emplacement de vos données dans la feuille de calculs) :
- Total HT avec la formule =SOMME(E26:E35)
- Acompte reçu avec la formule =SI(E49>150;E49*0,3;0)
- Net à payer avec la formule =E49-E50
Je termine par la mention " Le paiement doit être effectué par virement bancaire ou par chèque à l'ordre de Société Fictive. " que j'insère avec la formule Majuscule pour modifier la casse automatiquement : =MAJUSCULE(" Le paiement doit être effectué par virement bancaire ou par chèque à l'ordre de Société Fictive. ").
Je vous propose un fichier annexe dans l'Espace Membres contenant le fichier Excel utilisé dans cet article.
Jeu de briques
Snake
Pacman
Bubble