Suivre la rentabilité d'un projet, c'est confronter les coûts réalisés au budget initial, poste par poste, pour savoir si la marge tient encore. En revanche, tant que ces chiffres ne sont reconstitués qu'à la fin, on découvre le dérapage une fois la facture envoyée, quand il n'y a plus rien à ajuster. Dans cet exercice, on va voir ensemble comment laisser Excel calculer tout ça en continu.
L'objectif est de passer d'un simple devis à un tableau de pilotage vivant, qui te dit en un coup d'œil où la marge se construit et quels postes la grignotent. C'est le réflexe attendu d'un chef de projet ou d'un consultant qui veut agir pendant que le projet tourne, pas après !
Ce que tu vas construire
Comparer budget initial et coûts réalisés poste par poste, l'écart étant lu directement dans la colonne dédiée.- Totaliser les coûts réalisés et calculer le taux de consommation budgétaire global du projet.
- Afficher un statut de rentabilité : Rentable, À risque ou Déficitaire.
- Repérer les postes en dépassement grâce à leur colonne de statut.
- Regrouper les coûts réalisés par catégorie avec SOMME.SI pour une vue synthétique.
À connaître avant de commencer
- Savoir saisir une soustraction et un pourcentage simple entre deux cellules.
- Savoir recopier une formule le long des postes de coûts.
- Connaître SOMME.SI au moins de nom : on l'utilise pour regrouper les coûts par catégorie.
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 | |
|---|---|---|---|---|---|
| 1 | Poste | Budget | Réalisé | Écart | Statut |
| 2 | Main-d'œuvre | 25 000 | 23 500 | +1 500 | OK |
| 3 | Matériel | 8 000 | 9 200 | -1 200 | Dépassé |
| 4 | Sous-traitance | 12 000 | 11 800 | +200 | OK |
| 5 | Frais de déplacement | 3 500 | 4 100 | -600 | Dépassé |
| 6 | Hébergement | 2 000 | 1 850 | +150 | OK |
| 7 | Licences logicielles | 1 500 | 1 500 | 0 | OK |
| 8 | Formation équipe | 4 000 | 2 800 | +1 200 | OK |
| 9 | Frais généraux (5%) | 2 800 | 2 800 | 0 | OK |
| 10 | Marge prévue (15%) | 8 820 | - | - | - |
| 11 | Imprévus (3%) | 1 764 | 950 | +814 | OK |
Exercice guidé
Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.
Utilise SOMME pour totaliser tous les coûts réalisés. Compare avec le budget total. La différence te donne l'écart global du projet : positif = sous le budget, négatif = dépassement.
=SOMME(C2:C10)Résultat attendu : Le coût total réalisé du projet (ex: 44 500 euros sur un budget de 45 000).
Astuces pour aller plus loin
Alerte à 80% de budget consommé
Si tu as consommé 80% du budget mais que le projet n'est avancé qu'à 60%, c'est le signal d'alarme. Ajoute une cellule qui compare ces deux taux et affiche une alerte dès que l'écart dépasse 10 points. Tu anticipes au lieu de constater.
Sépare les coûts fixes des variables
Dans ta liste de postes, distingue ce qui est fixe (salaires, loyer, licences) de ce qui est variable (déplacements, sous-traitance, matériel). Les dépassements viennent presque toujours des variables. SOMME.SI te trie ça en une formule.
Le taux de marge se calcule sur le prix de vente
Marge / Prix de vente, pas Marge / Coût. Si tu vends 50 000 et que tes coûts sont 42 000, ta marge est 16% (8 000 / 50 000), pas 19% (8 000 / 42 000). L'erreur est courante et fausse toutes les comparaisons entre projets.
Questions fréquentes
Crée un tableau avec un poste par ligne, une colonne Budget et une colonne Réalisé. La marge = Prix de vente - SOMME(Réalisé). Une formule SI affiche Rentable si la marge dépasse 10%, À risque entre 0 et 10%, Déficitaire si négative. Tu mets à jour au fil du projet et tu vois le statut en direct.
Écart = Budget - Réalisé, soit =B2-C2 si B2 est le budget et C2 le réalisé. Un résultat positif signifie que tu es sous le budget, négatif = dépassement. Pour le taux d'écart, =( B2-C2)/B2 donne un pourcentage. Utilise ABS() si tu veux traiter les deux sens de la même façon.
=SOMME(Réalisé)/SOMME(Budget) donne la part du budget déjà consommée. À 70% du projet, si tu es à 90% de budget consommé, il faut agir. Compare ce taux avec le pourcentage d'avancement physique pour détecter les dérives tôt.
Utilise SOMME.SI : =SOMME.SI(A:A;"Sous-traitance";C:C) additionne tous les réalisés dont la catégorie est Sous-traitance. Recopie la formule pour chaque type de coût. Tu obtiens une synthèse par catégorie sans tableau croisé dynamique.
Marge projetée = Prix de vente - Coûts réalisés - Coûts restants estimés. Ajoute une colonne Reste à dépenser pour chaque poste et calcule =PrixVente - SOMME(Réalisé) - SOMME(ResteADépenser). Tu as la marge finale probable avant la fin du projet.
Ajoute deux colonnes : Jours prévus et Jours consommés. Le coût journalier = salaire chargé / jours ouvrés du mois. =Jours_consommés * Coût_journalier donne le coût de main-d'œuvre réel. Compare avec le budget de main-d'œuvre initial pour voir si la productivité tient.
3 exercices similaires au calcul de rentabilité projet
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
Analyse des écarts budget vs réalisé
Construis un suivi budgétaire qui calcule l'écart en valeur et en pourcentage de chaque poste, puis flague automatiquement les dépassements à expliquer.
Voir l'exercice
Planning de production
Créer un planning de production qui calcule automatiquement les dates de livraison en tenant compte des jours ouvrés, des contraintes de capacité et des priorités.
Voir l'exercice
Envie de t'entraîner plus ?
Des dizaines de cas pratiques Excel corrigés pour progresser pour de vrai.
Voir tous les exercices