Budget prévisionnel annuel sur Excel
Un exercice corrigé pour construire un budget prévisionnel dans Excel sur 12 mois, comparer le budget au réalisé poste par poste et projeter l'année, avec le fichier Excel à télécharger.
Un budget prévisionnel, ce sont les postes en lignes et les douze mois en colonnes, avec un résultat qui se met à jour à mesure que les chiffres réels arrivent. En pratique, le réalisé finit rarement en face du prévu, et un dépassement peut couver pendant des mois sans que personne le voie venir. Dans cet exercice, on va construire ensemble un budget qui signale ces dérapages au bon moment.
L'objectif est de bâtir un outil de pilotage simple et lisible, qui regroupe les dépenses par nature et trace ta trajectoire jusqu'à la fin de l'année. C'est exactement ce qu'attend un dirigeant de TPE-PME ou un contrôleur de gestion, voir venir plutôt que constater après coup !
Ce que tu vas construire
Ventiler les recettes et dépenses mois par mois sur 12 mois.- Calculer automatiquement le résultat mensuel et le résultat cumulé.
- Regrouper les dépenses par catégorie pour distinguer fixe, variable et investissement.
- Comparer budget initial et réalisé avec un indicateur de statut par poste.
- Projeter le résultat annuel à partir des mois déjà écoulés.
À connaître avant de commencer
- Savoir recopier une formule vers la droite sur les 12 mois et vers le bas sur les postes.
- Savoir figer une référence avec les $ pour comparer chaque mois au budget annuel.
- Connaître SOMME.SI.ENS au moins de nom : on regroupe les dépenses par catégorie pas à pas.
Voici les données de départ de cet exercice. Copie-les ou télécharge le fichier Excel, puis entraîne-toi avant de regarder le corrigé.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Poste | Jan | Fév | Mar | ... | Déc | Total | Budget |
| 2 | CA produits | 45 000 | 48 000 | 52 000 | ... | 60 000 | 620 000 | 600 000 |
| 3 | Achats | 18 000 | 19 500 | 21 000 | ... | 24 000 | 248 000 | 240 000 |
| 4 | Salaires | 35 000 | 35 000 | 35 000 | ... | 35 000 | 420 000 | 420 000 |
| 5 | Charges sociales | 14 000 | 14 000 | 14 000 | ... | 14 000 | 168 000 | 168 000 |
| 6 | Loyer | 8 500 | 8 500 | 8 500 | ... | 8 500 | 102 000 | 102 000 |
| 7 | Marketing | 5 000 | 6 200 | 7 800 | ... | 4 500 | 68 000 | 60 000 |
| 8 | Assurances | 2 200 | 2 200 | 2 200 | ... | 2 200 | 26 400 | 26 400 |
| 9 | Télécom & IT | 1 800 | 1 800 | 1 800 | ... | 1 800 | 21 600 | 21 600 |
| 10 | Déplacements | 3 000 | 2 500 | 4 200 | ... | 1 500 | 35 000 | 30 000 |
| 11 | CA prestations | 12 000 | 10 500 | 14 000 | ... | 15 000 | 155 000 | 144 000 |
Exercice guidé
Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.
Astuces pour aller plus loin
Ne touche jamais au budget initial
Le budget initial est ta référence de départ : ne le modifie pas en cours d'année. Si tu veux ajuster les prévisions, crée une ligne Re-forecast à côté. Tu gardes ainsi trois visions : budget, re-forecast et réalisé, sans perdre la base de comparaison.
Pondère la projection par la saisonnalité
=SOMME(B2:D2)/3*12 projette sur l'année en supposant que les 9 mois restants ressemblent aux 3 premiers. Si ton activité est saisonnière, pondère chaque mois par son coefficient historique (décembre peut valoir 2x janvier). Les données N-1 sont ta meilleure source.
15 à 25 postes, pas plus
En dessous de 15 postes tu ne vois pas où ça dérape, au-dessus de 25 tu passes plus de temps à saisir qu'à analyser. Regroupe les petites dépenses dans un poste Divers et garde le détail dans un onglet annexe si tu en as besoin.
3 exercices similaires au budget prévisionnel annuel
Rapprochement bancaire automatisé
Construire un rapprochement bancaire automatisé qui compare ton relevé bancaire avec ta comptabilité et identifie les écarts en quelques secondes.
Voir l'exercice
Matrice de décision multicritère
Construis une matrice de décision pondérée pour comparer plusieurs options sur des critères chiffrés et sortir une recommandation objective.
Voir l'exercice
Détecter les anomalies d'un journal comptable
Passe au crible un extrait de journal comptable pour repérer les doublons de pièces, les écritures déséquilibrées et les montants hors seuil avec NB.SI, SOMME.SI et SI.
Voir l'exercice
Le modèle prêt à l'emploi
Découvre notre tableau de comptabilité simple pour tenir tes recettes et tes dépenses sans logiciel
Questions fréquentes
Crée un tableau avec les postes en lignes (CA, achats, salaires, loyer...) et les 12 mois en colonnes. SOMME calcule les totaux mensuels et annuels. Ajoute une ligne Réalisé sous chaque poste et une formule SI pour signaler les écarts. Le fichier corrigé de cet exercice est téléchargeable plus haut.
=SI(ABS((Réalisé-Budget)/Budget)<0.05;"OK";SI(ABS((Réalisé-Budget)/Budget)<0.15;"Attention";"Alerte")) affiche trois niveaux d'alerte. ABS() traite les écarts positifs et négatifs de la même façon. Applique une mise en forme conditionnelle pour colorier les cellules Alerte en rouge.
=SOMME(B2:D2)/3*12 extrapole sur 12 mois à partir des 3 premiers. Adapte le diviseur au nombre de mois réalisés. Si ton activité est cyclique, pondère par les coefficients saisonniers calculés sur les données de l'année précédente.
SOMME.SI.ENS(B:B;$A:$A;"Salaires") totalise toutes les lignes dont la catégorie est Salaires. Crée un tableau récapitulatif avec une formule par catégorie (fixes, variables, investissement). Utilise une liste déroulante pour les catégories afin d'éviter les fautes de frappe.
Toujours en HT. La TVA n'est ni un revenu ni une charge : c'est un flux de trésorerie que tu collectes pour l'État. Ton budget de CA et de dépenses doit être en HT. Si tu veux suivre les encaissements et décaissements réels, crée un onglet trésorerie distinct en TTC.
Ne répartis pas le CA uniformément sur 12 mois. Calcule les coefficients saisonniers sur N-1 : coefficient du mois = CA du mois / (CA annuel / 12). Multiplie ensuite le budget mensuel par son coefficient. Un décembre à 1,5 représente 50% de plus que la moyenne mensuelle.
Toute la boîte à outils Excel
Formules, exercices, modèles, raccourcis, lexique : tout ce qu’il te faut pour progresser, en accès libre.
Tout voir
