Dans ce tutoriel, je vais vous montrer comment construire un mini modèle financier dans Excel pour suivre les ventes, calculer les marges, créer des scénarios, analyser un investissement et trouver automatiquement le volume de ventes nécessaire pour atteindre un objectif.
Ici, nous allons repartir d’un exemple simple : un food-truck qui vend des menus, des boissons et des desserts.
Téléchargement
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous retrouvons les ventes d’un food-truck sur plusieurs journées.
Nous y retrouvons des produits différents, des quantités, des prix, des coûts, et même une erreur dans une cellule.
C’est important, parce que dans la vraie vie, les fichiers Excel ne sont pas toujours propres.
Avant d’écrire les formules, nous allons transformer cette plage en tableau structuré. Nous cliquons dans une cellule du tableau, puis nous utilisons le raccourci [Ctrl]+[L].
Dans la fenêtre qui s’affiche, nous vérifions que la plage commence bien en A6 et que la case « Mon tableau comporte des en-têtes » est cochée. Ensuite, nous validons avec « OK ».
Nous nous rendons ensuite dans l’onglet « Création de tableau », puis nous donnons le nom « Ventes » à notre tableau.
L’intérêt est simple : au lieu d’écrire des références compliquées comme F7:F20, nous allons pouvoir écrire des formules plus lisibles avec le nom des colonnes.
Dans la colonne « Chiffre d'affaires », nous saisissons la formule suivante.
=[@Quantité]*[@[Prix unitaire]]
Excel recopie automatiquement la formule dans toute la colonne, car nous travaillons dans un tableau structuré.
Dans la colonne « Coût total », nous faisons la même chose avec le coût unitaire.
=[@Quantité]*[@[Coût unitaire]]
Ensuite, dans la colonne « Marge brute », nous voulons calculer la différence entre le chiffre d’affaires et le coût total.
=[@[Chiffre d'affaires]]-[@[Coût total]]
Enfin, dans la colonne « Taux de marge », nous divisons la marge brute par le chiffre d’affaires.
=[@[Marge brute]]/[@[Chiffre d'affaires]]
Nous appliquons ensuite un format pourcentage à cette colonne depuis l’onglet « Accueil ».
Si nous regardons les résultats, nous pouvons constater que nous avons des erreurs dans les colonnes « Marge brute » et « Taux de marge ».
Pour les masquer, nous allons pouvoir les encapsuler dans une fonction SIERREUR().
Ainsi :
=[@[Chiffre d'affaires]]-[@[Coût total]]
Devient :
=SIERREUR([@[Chiffre d'affaires]]-[@[Coût total]];"")
Et
=[@[Marge brute]]/[@[Chiffre d'affaires]]
Devient :
=SIERREUR([@[Marge brute]]/[@[Chiffre d'affaires]];"")
À ce stade, nous avons déjà une base propre : les ventes sont calculées, les erreurs sont masquées proprement, et notre tableau pourra s’agrandir automatiquement si nous ajoutons de nouvelles lignes.
2. Calculer des totaux fiables, même avec des erreurs
Maintenant que notre tableau est prêt, nous avons une petite zone de synthèse en bas du fichier :
Le piège classique serait d’utiliser simplement SOMME.
=SOMME(Ventes[Chiffre d'affaires])
Mais si la colonne contient une erreur, la somme peut elle-même afficher une erreur.
Et dans un tableau financier, c’est embêtant, parce qu’une seule cellule incorrecte peut bloquer toute la synthèse.
Nous allons donc utiliser AGREGAT, qui permet d’effectuer des calculs en ignorant certains éléments, notamment les erreurs.
En B24, nous saisissons :
=AGREGAT(9;6;Ventes[Chiffre d'affaires])
Le premier argument 9 indique que nous voulons faire une somme. Le deuxième argument 6 indique que nous voulons ignorer les valeurs d’erreur. Le dernier argument correspond à la colonne à additionner.
En B25, nous faisons la même chose pour la marge brute.
=AGREGAT(9;6;Ventes[Marge brute])
Pour le taux de marge moyen, nous pouvons diviser la marge totale par le chiffre d’affaires total.
=B4/B3
Cette première partie nous montre une idée importante : en finance, le calcul le plus simple n’est pas toujours le plus robuste. AGREGAT est très utile quand nous recevons des fichiers imparfaits, avec des erreurs, des divisions par zéro ou des données encore en cours de correction.
Si nous voulons aller plus vite, nous pouvons aussi demander à « IA Excelformation.fr » de générer la formule directement depuis Excel en expliquant simplement notre besoin en français, puisque l’outil permet notamment de créer des formules, des tableaux et des analyses directement dans le classeur.
Ainsi, nous supprimons la marge totale que nous venons de calculer, puis nous nous rendons dans « IA Excelformation.fr » > « Créer une formule », puis nous saisissons notre demande : « Calcule la marge brute du tableau « Ventes », en ignorant les erreurs » :
Le résultat obtenu est alors identique.
3. Simuler un placement et analyser un investissement
Dans cette partie, nous allons utiliser des fonctions financières plus avancées, mais nous allons les garder très concrètes.
Imaginons que le food-truck mette de côté une partie de sa trésorerie chaque année pour acheter un deuxième camion dans quelques années.
Il dispose d’une trésorerie de départ de 10000, et peut épargner 4000€ par an, le tout placé à 6%.
Pour obtenir le montant dont il disposera dans 10 ans, nous utilisons la fonction VC dans la colonne « Valeur future » :
=VC(C31;D31;-B31;-A31)
Le premier argument est le taux, le deuxième est le nombre de périodes, le troisième est le versement régulier, et le quatrième est le capital initial.
Les montants sont saisis en négatif, car Excel raisonne en flux financiers. Ce qui sort de notre poche est négatif, et ce que nous récupérons à la fin ressort positif.
Il disposera donc de 70k€ pour acheter son deuxième camion.
Nous pouvons maintenant faire l’inverse avec la fonction VA.
Par exemple, si nous voulons déterminer le capital initial nécessaire aujourd’hui pour atteindre notre objectif de 80 000 €, en tenant compte des versements annuels de 4 000 € et du rendement de 6 % :
En cellule E36, nous saisissons donc la formule :
=-VA(C36;D36;-B36;A36)
Le premier argument, C32, correspond au taux de rendement annuel de 6 %.
Le deuxième argument, D32, correspond à la durée du placement, soit 10 années.
Le troisième argument, -B32, représente le versement annuel de 4 000 €. Il est indiqué en négatif, car il s’agit d’une somme que nous plaçons chaque année.
Enfin, le quatrième argument, A32, correspond à la valeur future souhaitée, c’est-à-dire les 80 000 € nécessaires pour acheter le deuxième camion.
Le signe moins placé devant la fonction permet simplement d’afficher le résultat sous la forme d’un montant positif.
Excel nous indique alors qu’il faudrait disposer aujourd’hui d’un capital initial de 15 231,23 €.
Ces deux fonctions sont très pratiques pour comprendre l’effet du temps sur l’argent.
Nous passons maintenant à l’analyse d’un projet.
Imaginons que nous voulons louer un emplacement premium sur un marché fréquenté. Il faut investir 25 000 € au départ, puis nous espérons générer des flux positifs.
En E42, nous obtenons le montant de la valeur actuelle nette du projet, avec la formule :
=VAN.PAIEMENTS(E41;B41:B46;A41:A46)
Cette fonction actualise chaque flux financier en tenant compte de sa date exacte.
Le premier argument, E41, correspond au taux d’actualisation annuel. Il représente le rendement minimum attendu ou le coût du financement utilisé pour évaluer le projet.
Le deuxième argument, B41:B46, contient les différents flux financiers. Les dépenses sont saisies en négatif, tandis que les recettes sont saisies en positif.
Le troisième argument, A41:A46, contient les dates auxquelles ces flux ont réellement lieu. Chaque montant de la colonne B doit donc correspondre à une date de la colonne A.
Excel prend la première date de la plage comme date de référence, puis actualise tous les autres montants en fonction du nombre exact de jours qui les sépare de cette première date.
Si le résultat est positif, cela signifie que le projet crée de la valeur selon notre taux d’actualisation.
Ensuite, pour calculer le taux de rentabilité du projet, nous utilisons :
=TRI.PAIEMENTS(H25:H30;G25:G30)
Cela signifie qu’après avoir tenu compte de l’investissement initial de 25 000 € et des encaissements futurs, le projet crée une valeur supplémentaire de 22 434 € par rapport à un rendement attendu de 8 %.
Le taux de rentabilité de 62,31 % correspond au taux d’actualisation qui ramènerait la valeur actuelle nette du projet à zéro.
Le projet est donc, sur le papier, très rentable puisque ce taux est largement supérieur aux 8 % exigés.
Bien entendu, les modèles utilisés ici sont volontairement simplifiés et ne tiennent pas compte de nombreux paramètres, tels que les taxes, l’inflation, les frais d’entretien, les coûts de financement, les variations de trésorerie ou encore les imprévus liés à l’activité.
Dans un cas réel, il faudrait donc compléter ces calculs avec des hypothèses plus détaillées et tester plusieurs scénarios : un scénario prudent, un scénario intermédiaire et un scénario optimiste.
L’objectif est avant tout de comprendre comment Excel peut nous aider à comparer différentes décisions financières et à mesurer rapidement la rentabilité potentielle d’un projet.
Ce type de fichier peut être adapté à beaucoup de situations : commerce, restaurant, activité indépendante, association, budget personnel ou projet d’investissement.
L’essentiel, c’est de toujours partir d’un tableau propre, de sécuriser les formules, puis de construire progressivement des indicateurs utiles pour prendre de meilleures décisions.
