Comment calculer un seuil de rentabilité avec Excel

Connaitre le seuil de rentabilité d'un produit aide à mettre en place un modèle économique suivant plusieurs stratégies tarifaires et ainsi voir les profits envisageables. Avec le tableur Excel de la suite bureautique de Microsoft Office, il est facile d'intégrer, puis d'associer les couts fixes, les couts variables et ainsi que les choix de tarifs pour déterminer le seuil de rentabilité d'un produit que vous devrez atteindre en vendant une quantité donnée de votre produit sur une période.

Partie 1

Partie 1 sur 5:

Réaliser le tableau des couts variables

Télécharger l'article

Doublecliquez sur l'icône d'Excel et ouvrez un nouveau document. Vous allez utiliser plusieurs feuilles de ce nouveau classeur pour vos différents couts.
Cliquez sur le bouton à droite des noms des feuilles de classeurs (en bas après les onglets des trois feuilles). Selon les versions d'Excel, cette fonctionnalité se présente différemment, mais elle est toujours au même endroit.
Inscrivez C_variables, comme nom pour la nouvelle feuille de classeur que vous venez de créer. Dans cette nouvelle feuille, vous allez inscrire les couts variables, comme les frais de transport, de mise en conditionnement, etc.
Dans votre feuille de classeur, tapez dans la cellule A1 Description et dans la cellule B1 Montant.
Dans votre colonne A, inscrivez tous les différents couts variables que vous avez recensés pour votre produit, comme les frais de transport, de conditionnement.
Pour l'instant, n'inscrivez rien dans les cellules de votre colonne B. Vous y inscrirez les couts réels par la suite.
7
Réalisez un tableau. Avec les données que vous avez inscrites, vous allez faire un tableau. Ce dernier sera très utile quand il faudra écrire les formules.
- Il vous faut sélectionner toutes les cellules du tableau dans votre feuille en cliquant sur la première cellule en haut à gauche, puis étirez jusqu'à la dernière qui sera en bas à droite.
- Allez sur l'onglet Insertion, puis appuyez sur l'icône Tableau (version Excel 2010). Notez que pour la version Excel 2016 de Mac, le processus pour créer le tableau est exactement le même.
- Dans la fenêtre contextuelle qui apparait, cochez devant la case Mon tableau comporte des en-têtes, puis cliquez sur OK.
- En haut à gauche dans le ruban de votre feuille de calcul, changez le nom Tableau1 par le nom Coutsvariables, puis appuyez sur la touche Entrée.
Publicité

Partie 2

Partie 2 sur 5:

Établir le tableau des couts fixes

Télécharger l'article

Dans votre classeur Excel, appuyez sur le petit bouton qui se trouve à la droite des onglets de votre classeur Excel pour ouvrir une nouvelle feuille.
Inscrivez C_fixes, comme nom pour votre nouvelle feuille de classeur. Cette feuille de calcul aura les données des couts fixes, comme les taxes, l'assurance, le loyer, etc.
Comme pour les couts variables, vous allez construire un tableau et vous devez donc donner un titre aux colonnes qui vont être utilisées. Pour la colonne A, vous écrirez en A1 Description et dans la colonne suivante en B1 Montant.
Dans la colonne A, écrivez dans chaque cellule les couts fixes, comme l'assurance, les taxes, etc.
Laissez les cellules de la colonne B vides. Vous écrirez par la suite les montants quand vous aurez terminé de construire votre fichier.
6
Créez un tableau. Réalisez un tableau en sélectionnant toutes les cellules où vous avez inscrit des données et celles dans lesquelles vous allez écrire les montants des couts fixes.
- Allez sur l'onglet Insertion et appuyez sur l'icône Tableau.
- Cliquez dans la case qui se trouve devant Mon tableau comporte des en-têtes, la première ligne de votre tableau sera reconnue ainsi, comme l'entête de votre tableau. Ensuite, appuyez sur OK.
- Dans votre feuille Excel, vous verrez dans le ruban à droite le nom de votre tableau Tableau2, changez ce nom en inscrivant Coutsfixes, puis cliquez sur la touche Entrée.
Publicité

Partie 3

Partie 3 sur 5:

Créer la page pour le seuil de rentabilité

Télécharger l'article

Prenez l'onglet Feuil1 et renommez-le SR (Seuil de Rentabilité). En donnant cette dénomination à cette feuille, il vous sera plus facile de la retrouver parmi toutes vos feuilles dans votre classeur Excel. Elle présentera les différents éléments qui permettent de connaitre le seuil de rentabilité.
2
Élaborez votre tableau. Il est important de bien organiser votre tableau pour pouvoir retrouver rapidement les données essentielles.
- Commencez par la cellule A1 en y inscrivant Ventes pour la section qui concernera les ventes du produit.
- En cellule B2, tapez Prix par unité, cela sera le prix que vous aurez décidé pour une unité de votre produit.
- Inscrivez Unités vendues dans la cellule B3. Il s'agira du nombre d'unités du produit vendu sur une période donnée.
- Passez dans la cellule A4 et écrivez Couts. C'est le titre pour la section des couts.
- Allez à la cellule B5 et tapez Couts variables. Il sera question ici, des couts qui fluctuent, comme le cout de conditionnement ou d'expédition.
- Dans la cellule B6, écrivez Couts fixes. Vous y verrez les couts qui sont constants à chaque période, comme le loyer, les taxes.
- Cliquez dans la cellule A7 et inscrivez-y Revenu. Il s'agira de la section des gains obtenus après la vente des unités du produit.
- Accédez à la cellule B8 et tapez Marge par unité. Ce sera le montant gagné par unité en ayant déduit les couts variables.
- Passez à la cellule B9 et écrivez Marge brute. Vous y trouverez les gains que vous aurez faits pour la vente totale des unités de votre produit après avoir soustrait les couts variables.
- En cellule A10, tapez SR. Ce sera la section qui concerne le seuil de rentabilité.
- Allez dans la cellule B11 et inscrivez Unités. Vous y trouverez le nombre d'unités de votre produit qu'il vous faudra vendre pour couvrir toutes vos dépenses (couts).
3
Modifiez le format des nombres des cellules. Selon les valeurs que vous allez inscrire dans les différentes cellules d'entrées et de sorties d'argent, vous devez faire en sorte de bien les présenter.
- Sélectionnez les cellules C2, C5, C6, C8 et C9, puis dans l'onglet Accueil, faites un clic droit et dans le menu contextuel qui apparait, choisissez Format de cellule. Dans la fenêtre qui s'ouvre, sélectionnez Monétaire dans le menu vertical à gauche.
- Cliquez dans les cellules C3 et C11 pour les sélectionner. Ensuite, faites un clic droit, sélectionnez Format de cellule, puis choisissez Nombre dans le menu vertical à gauche. Sur la droite, en face de Nombre de décimales remplacez le chiffre « 2 » par le chiffre « 0 » pour n'avoir que des chiffres entiers.
4
Identifiez les plages pour créer les formules. Pour faciliter la création et la mise à jour des différentes formules de votre feuille de seuil de rentabilité (SR), créez des noms pour les différentes plages que vous allez sélectionner.
- Dans votre feuille, sélectionnez les cellules B2 à C3. Ensuite, cliquez sur l'onglet Formules, puis sur l'icône Depuis sélection. Enfin, appuyez sur OK.
- Cliquez sur la cellule B5 et allez jusqu'à la cellule C6. Dans l'onglet Formules, appuyez sur Depuis sélection, puis sur OK.
- Appuyez sur la cellule B8 et étirez en sélectionnant jusqu'à la cellule C9. Cliquez sur Depuis sélection dans l'onglet Formules. Ensuite, appuyez sur OK.
- Passez en surbrillance les cellules B11 à C11, puis sélectionnez la fonctionnalité Depuis sélection dans l'onglet Formules. Enfin, cliquez sur OK.
Publicité

Partie 4

Partie 4 sur 5:

Inscrire les formules

Télécharger l'article

1
Écrivez la formule pour les couts variables. Allez dans la cellule C5. C'est dans cette cellule que vous allez écrire la formule qui donnera les couts variables suivant le nombre d'unités du produit vendu.
- La formule à écrire sera =SOMME(Coutsvariables)*Unités_vendues. La première variable fait appel à votre tableau Coutsvariables qui se trouvent sur votre feuille C_variables et la deuxième variable est la cellule C3 qui a été nommée ainsi précédemment.
2
Accédez à la cellule C6. Dans la cellule C6 de votre feuille de calcul, vous allez taper la formule pour les couts fixes que vous avez pour votre produit.
- Dans la cellule C6, inscrivez =SOMME(Coutsfixes). Cette variable qui reste fixe représente les couts fixes qui se trouvent dans le tableau Coutsfixes de l'onglet C_fixes de votre classeur Excel.
3
Créez la formule pour la marge par unité dans la cellule C8. Dans cette cellule, vous connaitrez le gain pour une unité de votre produit après avoir déduit les couts variables.
- Vous devrez écrire
  =Prix_par_unité-SOMME(Coutsvariables) dans la cellule C8. Ce sont les variables de la cellule C2 et du tableau Coutsvariables de la feuille C_variables.
4
Inscrivez la formule pour la marge brute. Cliquez dans la cellule C9. Dans cette cellule, vous allez mettre la formule qui vous donnera les gains réalisés une fois que vous avez vendu vos unités de votre produit après déduction des couts variables.
- Sélectionnez la cellule C9 et écrivez la formule =Marge_par_unité*Unités_vendues. La première variable est celle de la cellule C8 et la deuxième de la cellule C3.
5
Allez dans la cellule C11. Dans la cellule C11, vous allez écrire la dernière formule, celle qui vous informera de combien d'unités de votre produit vous devez vendre pour pouvoir atteindre le seuil de rentabilité.
- Inscrivez la formule =SIERREUR(Couts_fixes/Marge_par_unité;0) dans la cellule C11. Pour cette formule, les variables sont celles des cellules C6 et C8.
Publicité

Partie 5

Partie 5 sur 5:

Trouver le seuil de rentabilité

Télécharger l'article

1
Écrivez les données des couts variables. Allez sur la feuille de classeur C_variables et tapez tous les couts variables que vous avez pour votre produit. Sachez que si vous notez bien l'intégralité des couts variables, votre seuil de rentabilité sera en conséquence plus juste.
- Inscrivez les couts variables dans le tableau Coutsvariables pour une unité vendue.
Dans l'onglet C_fixes, inscrivez l'ensemble des couts fixes que vous avez dans le tableau Coutsfixes. Ces couts sont souvent les mêmes et à payer une fois par mois, comme le loyer.
Sur l'onglet SR de votre classeur Excel, tapez un premier prix à l'unité pour votre produit. Par la suite, suivant les différents calculs vous pourrez le faire évoluer.
Sur une période qui court entre deux moments où vous payez vos couts fixes, comme votre assurance ou votre loyer, donnez le nombre d'unités de votre produit que vous voulez vendre dans cet intervalle de temps.
Dans votre feuille SR, constatez à la cellule C11 le nombre d'unités de votre produit que vous devez vendre sur la période qui se trouve entre deux payements de vos couts fixes pour arriver à votre seuil de rentabilité. Sachez que cette valeur peut évoluer suivant le prix à l'unité, les couts fixes et variables.
En modifiant le prix à l'unité de votre produit, vous verrez que la quantité d'unité à vendre s'ajustera en conséquence. Faites plusieurs essais avec différents prix pour voir combien d'unités vous devez vendre pour être au seuil de rentabilité ^{[1]XSource de recherche}.
Publicité

Conseils

En augmentant le prix de vente, il vous sera possible d'arriver au seuil de rentabilité en vendant moins d'unité de votre produit. Toutefois, la hausse du prix peut aussi entrainer une baisse globale de vos ventes.