Aller au contenu principal
Le DojoLe Dojo
Chefs de projetIntermédiaire40 min

Calcul de rentabilité projet sur Excel

Un exercice corrigé pour suivre la rentabilité projet dans Excel poste par poste, totaliser avec SOMME.SI et afficher Rentable, À risque ou Déficitaire, avec le fichier Excel à télécharger.

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 !

Le conseil du pro
Le signal d'alarme à surveiller dès le départ, c'est un budget qui se consomme plus vite que le projet n'avance. Avoir dépensé 80 % du budget pour 60 % d'avancement, c'est 20 points d'écart, bien au-delà du seuil de 10 points qui déclenche l'alerte de cet exercice. On anticipe au lieu de constater.

Ce que tu vas construire

  • Totaliser le budget et les coûts réalisés de chaque projet à partir du détail de ses postes, avec SOMME.SI.
  • Calculer le taux de consommation du budget et le comparer à l'avancement physique déclaré.
  • Estimer le coût à terminaison en ajoutant le reste à dépenser aux coûts déjà réalisés.
  • Projeter la marge de chaque projet et la rapporter à son prix de vente.
  • Afficher un statut Rentable, À risque ou Déficitaire qui se met à jour avec les chiffres.

À connaître avant de commencer

  • Savoir saisir une soustraction et une division entre deux cellules.
  • Savoir recopier une formule vers le bas et appliquer le format Pourcentage.
  • Connaître SOMME.SI au moins de nom, on s'en sert pour additionner les lignes de coûts de chaque projet.

Voici les données de départ, réparties sur 2 onglets comme dans le fichier (clique sur un onglet sous le tableau pour changer de feuille). Copie-les ou , puis entraîne-toi avant de regarder le corrigé.

ABCDEFGHIJK
1ProjetClientPrix de venteAvancement physiqueBudget coûtsCoûts réalisésBudget consomméAlerteCoût à terminaisonMarge projetéeStatut
2Site e-commerceMaison Lenoir48 000100%
3Application mobileTransports Garnier72 00060%
4Migration ERPFonderies Morel95 00070%
5Audit sécuritéClinique des Pins18 000100%
6Formation Power BIMutuelle Arvor12 50050%
7Refonte intranetCabinet Roussel36 00070%

Résoudre l'exercice étape par étape

Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.

Ta progression
0/6
1
Totaliser le budget et le réalisé de chaque projetDans l'onglet Projets, en colonne E (Budget coûts) et en colonne F (Coûts réalisés), totalise pour chaque projet le budget de ses coûts et ses coûts réalisés, en additionnant ses lignes de l'onglet Coûts.
2
Mesurer la consommation du budgetDans l'onglet Projets, en colonne G (Budget consommé), calcule la part du budget des coûts que chaque projet a déjà dépensée.
3
Comparer la consommation à l'avancementToujours dans l'onglet Projets, en colonne H (Alerte), affiche Dérive quand cette part dépasse l'avancement physique du projet de plus de 10 points, et OK sinon.
4
Estimer le coût à terminaisonDans l'onglet Projets, en colonne I (Coût à terminaison), estime le coût à terminaison de chaque projet en ajoutant à ses coûts réalisés le reste à dépenser de ses lignes de coûts.
5
Calculer la marge projetéeDans l'onglet Projets, en colonne J (Marge projetée), calcule la marge projetée de chaque projet, c'est-à-dire ce qu'il restera du prix de vente une fois tous les coûts payés.
6
Afficher le statut de rentabilitéToujours dans l'onglet Projets, en colonne K (Statut), affiche le statut de rentabilité de chaque projet, Rentable quand la marge dépasse 10 % du prix de vente, À risque jusqu'à 10 %, Déficitaire quand la marge est négative ou nulle.
Ce qu’il faut retenir
À retenir absolument, une même marge donne deux pourcentages selon ce à quoi on la rapporte. Vendre 50 000 pour 42 000 de coûts laisse 8 000 de marge, soit 16 % du prix de vente (8 000 / 50 000), le taux de marque, ou 19 % des coûts (8 000 / 42 000), le taux de marge. Cet exercice rapporte la marge au prix de vente, et un seuil de 10 % ne se compare qu'à un pourcentage calculé de la même façon.

Astuces pour aller plus loin

Fais réestimer le reste à dépenser à chaque point projet

Le coût à terminaison ne vaut que ce que vaut le reste à dépenser. Fais-le réestimer poste par poste à chaque point d'avancement, plutôt que de le déduire du budget. La refonte intranet montre pourquoi. Elle a consommé 92,6 % de son budget pour 70 % d'avancement, et l'alerte affiche Dérive, alors que son reste à dépenser déclaré n'est que de 2 600 et la laisse Rentable. Quand les deux signaux se contredisent, c'est d'abord le reste à dépenser qu'il faut questionner.

Totalise aussi les coûts par poste

SOMME.SI regroupe les lignes selon n'importe quelle colonne. Dans une cellule libre de l'onglet Projets, =SOMME.SI(Coûts!$B:$B;"Sous-traitance";Coûts!$D:$D) additionne la sous-traitance réalisée des quatre projets qui en ont, soit 43 800. Remplace Sous-traitance par Main-d'œuvre ou Frais pour compléter la synthèse, sans tableau croisé dynamique.

Calcule la marge de l'ensemble sur les totaux

Pour rapporter la marge des six projets réunis à leur prix de vente, divise la somme des marges par la somme des prix de vente. =SOMME(J2:J7)/SOMME(C2:C7) donne 14 %, soit 39 500 de marge pour 281 500 facturés. La moyenne des six taux donnerait 11 %, parce qu'elle pèse autant la formation à 12 500 que la migration à 95 000.

S'entraîner sur 3 exercices similaires au calcul de rentabilité projet

01

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

02

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

03

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

Tu as peut-être encore une question

Détaille les coûts de chaque projet poste par poste, avec le budget, le réalisé et le reste à dépenser, puis totalise-les par projet avec SOMME.SI. La marge projetée est le prix de vente moins le coût à terminaison, c'est-à-dire le réalisé plus le reste à dépenser. Deux SI imbriqués affichent ensuite Rentable quand la marge dépasse 10 % du prix de vente, À risque jusqu'à 10 % et Déficitaire quand la marge est négative ou nulle.

Pour aller plus loin

Tout ce dont tu as besoin pour continuer à t’entraîner se trouve ici.

Besoin d’aide sur Excel ?

Rejoins le Dojo pour poser tes questions, obtenir de l’aide sur tes fichiers Excel et participer chaque mois à un cours collectif en direct.

Rejoindre gratuitement

Gratuit · Sans carte bancaire