Calcul du coût de revient en coûts complets sur Excel
Un exercice corrigé pour calculer un coût de revient en coûts complets, du tableau de répartition des charges indirectes au résultat analytique, avec le fichier Excel à télécharger.
Le coût de revient dit ce que coûte vraiment un produit une fois fabriqué et vendu, charges indirectes comprises. Dans une menuiserie qui fabrique des tables et des chaises en chêne, le loyer de l'atelier, l'entretien des machines ou le travail de l'administration ne se rattachent pourtant à aucun meuble en particulier. C'est justement ce que règle la méthode des coûts complets, et on va la dérouler ensemble dans Excel, du tableau de répartition jusqu'au résultat de chaque produit !
L'objectif est de faire circuler les charges d'un tableau à l'autre par des formules, sans jamais ressaisir un montant. Les centres auxiliaires se vident dans les centres principaux, chaque centre trouve le coût de son unité d'œuvre, puis ces coûts s'imputent aux produits. Une fois ces liens posés, une clé ou un prix modifié se répercute partout, et on voit aussitôt quel produit gagne de l'argent et lequel en perd.
Ce que tu vas construire
- Compléter un tableau de répartition des charges indirectes, de la répartition primaire aux totaux après répartition secondaire.
- Répartir deux centres auxiliaires en cascade avec des clés en pourcentage.
- Compter les unités d'œuvre de chaque centre principal avec SOMMEPROD, puis en calculer le coût au centime avec ARRONDI.
- Calculer le coût d'achat des matières consommées, le coût de production et le coût de revient de deux produits.
- Dégager le résultat analytique de chaque produit et repérer celui qui est vendu à perte.
- Recopier une même formule vers le bas et vers la droite grâce aux références mixtes.
À connaître avant de commencer
- Savoir figer une référence avec les $, y compris à moitié, du type B$2 ou $B2.
- Savoir pointer une cellule d'un autre onglet, du type Paramètres!B3.
- Connaître la différence entre charges directes et charges indirectes, au moins dans les grandes lignes.
Voici les données de départ, réparties sur 4 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é.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Élément | Entretien | Administration | Approvisionnement | Atelier | Distribution |
| 2 | Répartition primaire | 4 000 | 7 600 | 2 300 | 12 000 | 5 000 |
| 3 | Clé de répartition Entretien | -100% | 10% | 15% | 50% | 25% |
| 4 | Répartition Entretien | |||||
| 5 | Clé de répartition Administration | -100% | 20% | 30% | 50% | |
| 6 | Répartition Administration | |||||
| 7 | Totaux après répartition secondaire | |||||
| 8 | Nature de l'unité d'œuvre | Mètre cube de chêne acheté | Heure-machine | 100 € de chiffre d'affaires | ||
| 9 | Nombre d'unités d'œuvre | |||||
| 10 | Coût de l'unité d'œuvre |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Élément | Coût unitaire | Table | Chaise |
| 2 | Coût d'achat du chêne consommé | |||
| 3 | Main-d'œuvre directe | |||
| 4 | Charges indirectes de l'atelier | |||
| 5 | Coût de production | |||
| 6 | Charges indirectes de distribution | |||
| 7 | Coût de revient | |||
| 8 | Chiffre d'affaires | |||
| 9 | Résultat analytique |
| A | B | C | |
|---|---|---|---|
| 1 | Donnée | Table | Chaise |
| 2 | Quantité produite et vendue | 150 | 400 |
| 3 | Chêne consommé par unité (m³) | 0,08 | 0,015 |
| 4 | Heures de main-d'œuvre directe par unité | 5 | 1,2 |
| 5 | Heures-machine par unité | 2 | 0,5 |
| 6 | Prix de vente unitaire (€) | 420 | 80 |
| A | B | |
|---|---|---|
| 1 | Paramètre | Valeur |
| 2 | Chêne acheté en mars (m³) | 18 |
| 3 | Prix d'achat du chêne (€ le m³) | 1 450 |
| 4 | Taux horaire de la main-d'œuvre directe (€) | 28 |
| 5 | Stock de chêne en début et en fin de mois (m³) | 0 |
Résoudre l'exercice étape par étape
Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.
Astuces pour aller plus loin
Contrôle le tableau par ses totaux
La répartition secondaire déplace les charges sans en créer ni en perdre. Les cinq centres totalisent 30 900 euros sur la ligne 2 comme sur la ligne 7, et =SOMME(B2:F2)-SOMME(B7:F7) doit renvoyer 0 dans une cellule libre de l'onglet Répartition. Si ce contrôle ne tombe pas à 0, ou si un centre auxiliaire garde un montant sur la ligne 7, une clé ou une formule de répartition est à revoir.
Rapproche le résultat de la comptabilité générale
En comptabilité générale, le mois dégage 3 560 euros, soit 95 000 euros de ventes moins 26 100 euros d'achats de chêne, 34 440 euros de main-d'œuvre directe et 30 900 euros de charges indirectes. Les deux résultats analytiques additionnés donnent 3 556,50 euros. Les 3,50 euros d'écart sont la différence d'arrondi du coût de l'unité d'œuvre de la distribution, qu'un rapprochement doit toujours expliquer.
Avec un stock de matière, passe au coût moyen pondéré
Ici, tout le chêne acheté en mars est consommé dans le mois, et le mètre cube consommé coûte donc son coût d'achat. S'il restait un stock, les sorties se valoriseraient par exemple au coût moyen unitaire pondéré, soit la valeur du stock initial plus le coût d'achat des entrées, divisée par la quantité initiale plus la quantité entrée. Seul le coût unitaire de la ligne 2 de l'onglet Coûts changerait, les autres formules tiennent. Pour la valeur de ce stock au bilan, en revanche, le plan comptable général écarte les coûts administratifs (article 213-31), et la part du centre Administration reçue par l'approvisionnement n'y entrerait pas.
S'entraîner sur 3 exercices similaires au calcul du coût de revient en coûts complets
État de rapprochement bancaire automatisé
Pointer ton relevé contre la comptabilité avec RECHERCHEV, puis dresser l'état de rapprochement bancaire en deux colonnes jusqu'à l'égalité des soldes rectifiés.
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 en partie double pour repérer les écritures saisies deux fois, les pièces déséquilibrées et les montants hors seuil avec NB.SI.ENS, SOMME.SI et SI.
Voir l'exercice
Tu as peut-être encore une question
Suis la chaîne des coûts complets. Le coût d'achat des matières consommées, la main-d'œuvre directe et les charges indirectes de production donnent le coût de production, auquel on ajoute les charges de distribution pour obtenir le coût de revient. Dans Excel, chaque montant se calcule en multipliant une quantité par un coût unitaire, et SOMME additionne ensuite les lignes. Des liens entre onglets évitent de ressaisir le moindre montant.
Le coût de production réunit le coût d'achat des matières consommées et les charges de production, directes et indirectes. Le coût de revient ajoute au coût de production des produits vendus les charges de distribution. Dans cet exercice, la table coûte 51 240 euros à produire et 57 873,90 euros une fois ses charges de distribution imputées.
La répartition primaire affecte les charges indirectes aux centres d'analyse. La répartition secondaire vide ensuite les centres auxiliaires dans les autres centres selon des clés en pourcentage, en commençant par celui qui ne reçoit rien des autres. Pour chaque centre principal, on indique enfin la nature et le nombre d'unités d'œuvre, puis on divise son total après répartition secondaire par ce nombre.
Divise le total du centre après répartition secondaire par le nombre d'unités d'œuvre de la période. L'atelier de l'exercice totalise 16 400 euros pour 500 heures-machine, soit 32,80 euros de l'heure. Dans Excel, =ARRONDI(E7/E9;2) donne ce coût au centime près, ce qui peut créer une petite différence d'arrondi au moment d'imputer les charges.
Pour un centre comme la distribution, l'activité se mesure en valeur plutôt qu'en heures. On compte combien de fois 100 euros entrent dans le chiffre d'affaires, ici 95 000 euros, soit 950 unités d'œuvre. Le coût de l'unité, 10,53 euros, revient alors à imputer environ 10,53 % de son chiffre d'affaires à chaque produit.
Retranche son coût de revient de son chiffre d'affaires. Dans cet exercice, la table dégage 5 126,10 euros sur 63 000 euros de ventes, alors que la chaise perd 1 569,60 euros sur 32 000 euros. Un résultat négatif ne suffit pas à abandonner un produit, car une partie de ses charges indirectes resterait à couvrir par les autres.
Pour aller plus loin
Tout ce dont tu as besoin pour continuer à t’entraîner se trouve ici.
Récupère notre modèle de plan de trésorerie
Télécharger le fichier
Ton cours Excel gratuit
3 chapitres pour apprendre les bases et découvrir l'automatisation.
Voir le cours
Télécharge notre cours Excel complet en PDF
Télécharger
Le quiz qui révèle ton vrai niveau
Faire le test
Le dictionnaire des formules Excel
Chercher une formule