Croisement de courbes
Bonjour,
Je voudrais savoir comment calculer un croisement de courbes. Je m'explique :
J'ai un produit dont le prix unitaire de revient diminue en fonction de la quantité.
Le prix global augmente en fonction de la quantité.
Par exemple :
1000 ex coût global 2000 €, cout unitaire 2 €
2000 ex coût global 3600 €, cout unitaire 1.80 €
3000 ex coût global 4800 €, cout unitaire 1.60 €
4000 ex coût global 6000 €, cout unitaire 1.5 €
J'ai un prix de vente de 12 €
Je voudrais croiser les courbes, pour savoir :
1 - Quand est ce que je suis rentable.
2 - Combien il me faut faire de ventes pour générer le même revenu que le tirage précédent.
Par exemple pour un tirage de 1000 exemplaires, je suis rentable à 167 exemplaires.
Si je vends tout, je génère un bénéfice de (1000ex x 12) - 2000 € = 10 000 euros de bénéfice
Si je fais 2000 exemplaires, je suis rentable à 300 exemplaires, mais il faudra que j'en vendes 1133 pour générer le même bénéfice que si j'en vends 1000.
Soit seulement 133 de plus que si j'en fais 1000.
L'objectif pour moi étant de déterminer quel est la meilleure quantité à fabriquer par rapport au risque
Merci pour votre aide
Bonjour,
Votre sujet est un problème d'algèbre mais pas uniquement.
1000 ex coût global 2000 €, cout unitaire 2 € : F1(x) = 2x
2000 ex coût global 3600 €, cout unitaire 1.80 € : F2(x) = 1.80x
3000 ex coût global 4800 €, cout unitaire 1.60 € : F3(x) = 1.60x
4000 ex coût global 6000 €, cout unitaire 1.5 € : F4(x) = 1.50x
J'ai un prix de vente de 12 € : V1(x) = 12x.
La notion de risque ici est assez subjective. Êtes vous capable d'évaluer la quantité qui sera vendue ? Si vous ne pouvez pas, vous ne pouvez pas déterminer ce qui sera réellement rentable.
D'un point de vue stratégique, je dirai que le prix de vente devrait être modulé en fonction de la quantité produite, plus vous produisez , plus vous baissez les prix, moins de marge à l'unité mais plus de ventes donc vous vous rattrapez sur la quantité.
Mais encore une fois, il faudrait faire une étude de marché pour savoir combien de clients seraient prêts à payer 8€, 10€, 12€ ...
Avec cette information (approximative), vous aurez votre réponse :
Bénéfice = x * (Vu - Pu) où x est le nombre prévisibles de vente , Vu le prix de vente unitaire et Pu, le coût de production unitaire. A évaluer pour chaque cas.
Mais il me semble que vous devez tenir compte, du stock qui vous reste sur les bras. Il n'a pas de valeur nulle et vous pourriez le solder.
Désolé si je vous pose plus de questions que je ne fournis de réponse.
Si vous avez tous les éléments, je pourrai vous donner le résultat des équations à poser
Merci Clr pour ce début de réponse,
Je vais apporter quelques éléments utiles :
Le prix de vente fixé à 12 € (non modulable) est issu de notre étude de marché.
Passé une certaine valeur, le stock restant peut être considéré comme ayant une valeur nulle, c'est un produit millésimé qu'on ne peut donc solder.
Sur la notion de risque, il est vrai qu'elle est assez subjective et basée sur notre connaissance du marché.
J'ai commencé à faire un tableur pour trouver les seuils de rentabilité que je mets en pj.
J'ai visualisé manuellement les différents seuils évoqués, mais j'aimerais avoir une formule automatique pour pouvoir visualiser les changements lorsque je modifie certains paramètres (le prix de production est brut et je voudrais l'affiner, j'ai notamment une licence à payer qui est proportionelle au total des ventes)
Merci pour votre aide
Bonsoir Bart et Benji,
en pj le fichier où j'ai créé une nouvelle feuille "Feuil1" (pas très malin de ma part).
Le fichier est un .xlsm même s'il ne contient pas de macros mais cette transformation est nécessaire pour les formules contenus dans les noms que j'ai créés (voir Menu Formules -> Gestionnaire de noms
Elle affiche un graphique de :
- 3 courbes (des droites) correspondant pour chacune d'elles à une combinaison de PU et Coût de production
- une droite horizontale Objectif
L'interception de chaque droite avec la droite objectif donne le point "objectif" qui ne s'affiche pas mais que le peut connaitre en survolant à la souris l'intersection (une bulle s'affiche avec par exemple au point d'intersection de la courbe objectif et courbe 2 000 ex série "objectif" Point "620...." signifiant que le point d'intersection correspond à un nombre d'exemplaires = 620
Concernant le paramétrage :
- le pu (cellule (E6), l'objectif (f6) peuvent être changés, les courbes sont affectées dynamiquement.
- les formules sont dans la colonne C
(par exemple en C6 : 'pu*x-6191 est équivalent par exempleà la formule suivante pour B12 = ($E$6*A12) - 6191 que l'on étirerait sur toute la colonne B
Il est impératif de conserver l'apostrophe en 1er caractère, le x. Le 6191 représente le coût de production (que je ne sais pas donner dans la formule et qui est rappelé à titre indicatif en colonne D.
Pour la légende des courbes dans le graphique, j'ai utilisé la colonne A (2 000 ex. .... et Objectif) en respectant les couleurs.
En colonnes G, H et I, le paramétrage des abscisses (les x) : valeurs mini et maxi, nombre de points (ici le nombre de points importe peu c'est une droite.
Il sera peut-être possible de prendre en compte votre coût de production revu (mais toujours avec un report manuel dans chaque formule) ou un copier- Collage spécial valeurs (on verra plus tard).
Dans un premier temps, dites-moi si ma proposition vous est utile, en complément de votre feuille (qui s'appelle maintenant "Feuil0").
Rendons à César ce qui est à César, sans ce tuto dont je me suis très largement inspiré https://www.excel-exercice.com/graphique-fx/
j'aurais été incapable de vous proposer cette solution
Une nouvelle version du fichier. Je reprends mon message précédent en tenant compte des aménagements
Le fichier est un .xlsm même s'il ne contient pas de macros mais cette transformation est nécessaire pour les formules contenus dans les noms que j'ai créés (voir Menu Formules -> Gestionnaire de noms)
La feuille affiche un graphique de :
- 3 courbes (des droites) correspondant pour chacune d'elles à une combinaison de PU et Coût de production
- une droite horizontale Objectif
L'interception de chaque droite avec la droite objectif donne le point "objectif" qui ne s'affiche pas mais que le peut connaitre en survolant à la souris l'intersection (une bulle s'affiche avec par exemple au point d'intersection de la courbe objectif et courbe 2 000 ex série "objectif" Point "620...." signifiant que le point d'intersection correspond à un nombre d'exemplaires = 620
Concernant le paramétrage :
- le pu (cellule D2), l'objectif (E2), les coûts de production (D6,D7,D8) peuvent être changés, les courbes sont affectées dynamiquement.
- les formules sont dans la colonne C
(par exemple en C6 : 'pu*x-cp_1 est équivalent par exemple à la formule suivante pour B12 = ($E$6*A12) - $E$6 que l'on étirerait sur toute la colonne B
Il est impératif, si vous deviez modifier la formule (mais il n'y a pas de raison) de conserver l'apostrophe en 1er caractère.
E6, E7 et E8 représente le nombre d'exemplaires à vendre pour atteindre l'objectif (c'est une formule).
Pour la légende des courbes dans le graphique, j'ai utilisé la colonne A (2 000 ex. .... et Objectif) en respectant les couleurs.
En colonnes G, H et I, le paramétrage des abscisses (les x) : valeurs mini et maxi, nombre de points.
Il est possible de faire un zoom, pour se situer au niveau de l'objectif à atteindre. Pour cela
si l'on veut zoomer au niveau de l'objectif à atteindre (par exemple correspondant à 620 exemplaires pour la formule 2000 ex et 783 pour la formule 4000 ex), on indique pour valeur minimum de x (cellule I6) 600 et pour valeur maximum (I7) 800.
Le nombre de points (cellule I8) permet d'afficher les valeurs du nombre d'exemplaires dans le graphiques.
Rendons à César ce qui est à César, sans ce tuto dont je me suis très largement inspiré qui permet d'afficher un graphique à partir d'une simple formule sans avoir utiliser une plage de valeurs source https://www.excel-exercice.com/graphique-fx/
j'aurais été incapable de vous proposer cette solution
bonjour clr,
ce n'est pas nécessairement un "xlsm", voici votre fichier comme "xlsx" et avec les 3 labels des croisements.
Merci à tous pour vos contributions, vous m'avez maché le travail :D
Je n'ai plus qu'à l'adapter pour affiner les résultats
Encore merci
Bonjour,
ce message est pour BsAlv :
J'ai regardé votre fichier, malheureusement, à l'ouverture, j'ai le message suivant
Les références dans mon fichier
De ce fait, je ne peux pas voir ce que vous avez rectifié pour avoir les étiquettes des points d'intersection des courbes.
Si vous avez une solution pour avoir ces étiquettes de manière dynamique, c'est à dire réactualisées automatiquement pour toute modification d'un coût de production, du PU ou de l'objectif global, comme dans ma solution, je veux bien la récupérer.
Une remarque : comme je l'ai indiqué précédemment.
Le fichier est un .xlsm même s'il ne contient pas de macros mais cette transformation est nécessaire pour les formules contenus dans les noms que j'ai créés (voir Menu Formules -> Gestionnaire de noms)
Merci d'avance
bonjour clr,
j'avais continué avec votre fichier, mais simplement ajouté ces labels, mais je n'avais pas vu ce problème. Normallement, on n'a plus besoin de ces plages nommées y1, y2, etc et alors c'est plus facile
Merci pour le retour.
C'est la solution "classique" qui marche parfaitement avec une bonne lisibilité sur le graphique.
Ma solution plus sophistiquée mais moins adaptée ici, évite d'avoir à faire un tableau de valeurs et permet un zoom dans le graphique (m'a permis de découvrir cette gestion un peu particulière des graphiques).
Pour bien appréhender la différence entre les 2 solutions :
La vôtre qui nécessite d'avoir un tableau de valeurs pour chaque série
La mienne qui ne nécessite pas de tableau,
Je vous propose dans les 2 fichiers de saisir pour valeur d'objectif : 50 000.
Dans votre fichier, comme les tableaux de chaque série ne vont pas assez loin, dans le graphique, vous ne pouvez pas avoir d'intersection.
Vous devrez étirer les formules, ainsi que les quantités en colonne A, (Il y a déjà 400 lignes) et adapter en conséquence X dans le gestionnaire de noms.
Avec mon fichier qui s'appuie uniquement sur les formules, il n'y a rien à faire. Solution qui supporte donc des paramètres non bornés.
Comme je l'ai déjà dit, votre solution convient très bien au problème posé.
Et pour avoir les étiquettes des intersections des droites qui n'étaient pas possible avec mon fichier (alors qu'elles étaient là avec le vôtre), je dois ne pas utiliser de formule pour la série Objectif, mais la colonne nombre d'ex pour Objectif (les 3 valeurs en vert)
re,
si vous modifiez G2 en 50.000, les 4 points de croissements sont dans la plage B1:E2 qui sont en même temps la 5ième série de mon graphique.
Donc des que vous faites cela et les min et max des axes X et Y ont un choix libre, vous verrez cette 5ième série dans le graphique.
Avec le dernier fichier que vous m'avez transmis.
En mettant l'objectif à 50000, j'obtiens ceci.
pas d'intersection dans le graphique (Normal puisque le xMax = 900)
je passe le xmax de la plage à 55 000 pour avoir l'intersection et j'obtiens la fenêtre d'erreur ci-dessous
Si j'ai bien compris, vous affichez bien une intersection.
Bon, pas grave.
re,
si on n'intervient pas (manuellement ou avec VBA) au niveau du min, max ou ... des axes X et Y, c'est excel qui choisit ces valeurs. Comme ca, je sais vous montrer des valeurs extremes, et puis en VBA je change les min et les max des axes.
Bonsoir Bart,
mon ordi doit mal digérer vos fichiers !
Avec le dernier,
Sans changer les paramètres j'ai ceci
En voulant zoomer dans la zone d'intersection de 4000 à 6000 donc en passant xmin à 4000 etxMax à 6000, j'obtiens ceci
peut-être à cause de ma version d'excel différente de la vôtre
re,
excel a une forte volonté, c'est pourquoi je disais de fixer les limites min et max avec quelque règles de VBA ou à main.
Sans cela, mon ordi a les mêmes problèmes que le vôtre.