Calcul de tarifs avec menu deroulant

boro

XLDnaute Nouveau
Bonjour,


Je suis nouveau sur ce forum et c’est la première fois que je fais appel à vous tous.


Je dirige un petit atelier d’impression artisanale, et afin de mieux pouvoir répondre à nos clients, je suis en train de travailler sur un outil de calcul de nos tarifs.

Étant débutant sur Excel, j’ai glané quelques info ici et là qui m’on permis d’arriver jusqu’à maintenant à mes fins, mais voilà, mon fichier recoupant beaucoup de données, je n’arrive pas à les synthétiser.


Pour vous expliquer de la manière la plus simple possible j’ai opéré de la manière suivante :


- J’ai créer des feuilles de calcul pour déterminer le coût horaire de chacune des machines que nous utilisons ainsi qu’une fiche pour le coût horaire de l’operateur (cf. -feuilles « tarif horaire Heidelberg T », « Tarif horaire massicot », « Tarif horaire operateur »)


- J’ai créé une feuille de calcul rassemblant toute les étapes nécessaires à la production d’un support imprimé dans notre atelier, avec les temps et tarifs horaires allant avec, ainsi les variantes selon le nombre de couleurs (cf.- feuille « temps d’éxé)


- Deux feuille concernant matières premières dont nous avons besoin pour la production :

· une pour les papiers avec leur coût à la feuille, et des tableaux calculant le coût selon le format, nombre d’exemplaires et le nombre de passages de couleur. On trouve aussi sur cette fiche le nombre de feuille de « passe » (gâche nécessaire au calage de la machine) (cf.- feuille « papiers »).

· Une feuille pour les matières premières restante (film flash et plaque photopolymère) avec des tableau calculant selon les formats demandés et le nombre de passages de couleur le coût de chaque matière première.


Pour finir j’ai créer une feuille pour chaque format de support sur lequel nous travaillons afin de recouper pour chacun toute les données et ainsi obtenir un prix final selon le nombre d’exemplaire et le nombre de passage de couleur.



Mon Problème est le suivant, je pensais créer un outils de calcul pour chaque format en créant une liste déroulante désignant le nombre d’exemplaire, une autre le nombre de passage de couleur, et une dernière le choix du papier, mais je ne sais pas comment m’y prendre.


La formule devrait donner littéralement quelque chose comme cela :


Prix = nombre d’exemplaires + nombre de passage couleur + temps d’exécution pour le nombre d’exemplaires et nombre de passage couleur correspondant + papier

Cela doit vous paraître totalement confus, mais je ne sais pas trop comment expliquer la chose… je vous met le fichier en lien, si vous pouviez m’éclairer, cela me serait d’une grande aide !


Merci d’avance


Joël
 

Pièces jointes

  • SACRES-CARACTERES-prix-facturation.xlsx
    76.4 KB · Affichages: 78

boro

XLDnaute Nouveau
Bonjour cisco,

alors pour donner un exemple plus concert, on va partir sur le calcul du coût de production de 300 cartes de visites 1 couleur Recto/verso.

Pour cela, on doit puiser des données dans les feuilles de calcul suivantes :

- "Cartes de visite" pour le temps d'éxécution et de roulage correspondant à ce format.
- "Polymères et flash" pour les consommables nécessaires à la production
- "Papiers" pour le choix du papier, sont cout selon le nombre d'exemplaire et le cout de la "passe" ( gache de papier nécessaire au callage machine)
- "temps d'éxe" pour le calcul du cout de façonnage avec le massicot.

Au debut je pensai faire une feuille avec un tableau ou des listes déroulantes qui rassemble les calculs pour tout les format de document que nous produisons, mais je pense que le plus simple est de travailler sur un tableau sur chacune des feuilles correspondant a chaque format.

Donc pour l'exemple :

Disons que nous voulons calculer les cout d'execution et de roulage pour 300 cartes de visites 1 couleur recto verso.

Sur la feuille "carte de visites", il faudrait que je puisse créer :
  • En B20 une liste déroulante proposant de choisir le nombre d'exemplaires entre 50 et 500
  • En B21 une liste pour le choix du nombre de couleur recto et verso correspondant à la liste A2:A13
  • En B22 une liste pour le choix du papier, faisant référence aux prix des différents papiers listés sur la feuille "papiers" et correspondant au nombre d'exemplaires voulus pour le format donné auquel s'ajoute le coute de la passes par calage selon le nombre de couleur choisi.
  • En B23 s'afficherait le coût du massicot calculé selon le type de coupe à réaliser ( ici équerrage simple + 6 coupe = "feuille temps exe B42+D42)
Le résultat de la somme de toutes ces données s'afficherait en B24

Donc, en toute logique, pour l'exemple

  • si je choisi 50 exemplaires dans la liste en B20, il faudrait que cela cible les cellules de B2 à B13
  • si je chois "1 couleur R°V°" en B21 cela ciblerai la cellule B5
  • si je choisi dans la liste papier en B22 " Fedrigoni Oldmill 510g, cela ciblerai la feuille " papier" et dans cette feuille la somme des cellules B47 et B52 correspondant au cout de ce papier pour le format carte de visite en 50 exemplaires + le cout de la passe pour le calage de couleur
  • En B23 viendrait s'afficher le coût du façonnage massicot déterminé par le format choisi --> feuille "temps exe" cellules B42+D42

En résulterait un total exprimé en euros en B24 .

Cela vous parait-il clair et possible? ci joint le fichier dans sa derniere version

Merci d'avance
 

Pièces jointes

  • SACRES-CARACTERES-prix-facturation.xlsx
    108.7 KB · Affichages: 48

boro

XLDnaute Nouveau
Le choix de la somme B42 +D42 et non B41+D41 est déterminé par le type de coupe qui est à réalisé selon le format. Par exemple pour les cartes de visites et les format 10x10, nous imprimons deux cartes par format avant de recouper le tout une fois l'impression terminée.

On retrouve en A40:A42 les types de coupe a réaliser:
équerrage seul : recoupe des quatre bords de la feuille
équerrage + coupe 2 : recoupe des quatre bords de la feuille + coupe en deux du format
équerrage + coupe 3,4,5,6 : recoupe des quatre bords de la feuille + coupe supplémentaires des différents format finis disposé sur la feuille

pour les cartes de visites et les formats 10 x10 on est donc sur une coupe avec "équerrage + coupe 3,4,5,6" ce qui justifie le choix de la somme B42+D42.
Pour tout les autres formats de production en dehors des deux cités precedement, ce sera une coupe en "equerrage seul" donc B40+D40.

j'espere que j'ai été assez clair dans mon explication :)

merci beaucoup de ton aide !

a +
 

CISCO

XLDnaute Barbatruc
Bonjour

...pour les cartes de visites et les formats 10 x10 on est donc sur une coupe avec "équerrage + coupe 3,4,5,6" ce qui justifie le choix de la somme B42+D42. Pour tout les autres formats de production en dehors des deux cités précédemment, ce sera une coupe en "équerrage seul" donc B40+D40

Oui, tu as été assez clair dans ton explication. Si j'ai bien compris, tu n'as donc pas besoin de B41 + D41...

@ plus
 

CISCO

XLDnaute Barbatruc
Bonsoir

Cf. en pièce jointe.

*Attention, cela fonctionne s'il y a bien les mêmes listes

50
100
200
300
400
500

pour chacun des papiers sur la feuille "Papiers", et que toutes les plages B22:G22, B31:G31, B43:G43 et ainsi de suite, sont identiques à la plage B14:G14.

@ plus
 

Pièces jointes

  • SACRES-CARACTERES-prix-facturation.xlsx
    52.4 KB · Affichages: 32

boro

XLDnaute Nouveau
Bonsoir,

j'avais noté cela afin de trouver le moyen de prendre en compte le coût de la passe de calage ( perte, gâche de papier en nombre de format pré-prod lors d'un calage couleur ou d'un calage recto/verso, qui est de 10 feuilles) ainsi que la passe variable durant le roulage / impression ( pourcentage de format préprod perdu ou loupé durant le roulage) qui est de 8% du total de formats choisi.

Par exemple, si l'on veut connaitre le coût de la passe calage et de la passe variable pour le format carte de visite, une couleur recto/verso 100 exemplaires sur papier fedrigoni oldmill 510g, cela donnerait cela:

Sur la feuille de calcul papier:

passe de calage = coût de 10 formats pré-prod x 2 ( calage 1 couleur recto/verso) = B52*2= 2,13€
passe variable pour 100 exemplaires = 8% du coût de 100 exemplaires x 2 ( calage recto/verso) = ((8*B45)/100)*2 = 0,92€

Pour donc connaitre l'ensemble du coût de la passe papier pour ce projet on ajoute 2,13+0,92 au cout du papier pour le nombre d'exemplaire choisi du format (B45 pour cet exemple)

et encore merci de te pencher sur le fichier, j'essaye encore de l'amélioré mais je crois que j'arrive au bout de mes capacité sur excel

@ plus
 

CISCO

XLDnaute Barbatruc
Bonjour

Et donc, il faut multiplier à chaque fois B52 par le nombre de passage, par ex, dans le cas du papier FEDRIGONI OLD MILL 510g, pour 3 couleur R°V°+ 1 couleur V°, cela donnerait B52 * (3*2+1) ?

@ plus
 

CISCO

XLDnaute Barbatruc
Bonjour

Cf. en pièce jointe.

Pour simplifier la formule, j'ai rajouté dans le tableau une colonne carte_de_visite!B avec comme en-tête nombre de passe.

Dans le gestionnaire, j'ai aussi défini le nom "nbredepasse" qui permet d'obtenir l'information correspondante dans la plage B2:B13 en fonction de l'information dans B21.

Puisque tu écris qu'on prend 8 % du coût, je n'ai pas utilisé les données de la ligne Papiers!51, mais j'ai multiplié par 1+0,08*nbredepasse dans la formule dans carte_de_visite!C22.

Explique moi ce qui va, ne va pas, manque...

@ plus
 

Pièces jointes

  • SACRES-CARACTERES-prix-facturation.xlsx
    48.6 KB · Affichages: 40

boro

XLDnaute Nouveau
Bonjour,

merci beaucoup cisco !! ça fonctionne bien :)
la seule chose que je vois par contre c'est qu'en rajoutant la colonne "nbre de passe", cela a décalé tt le reste dans la formule de calcul, par exemple maintenant si on choisi 100 exemplaires, cela donne le prix correspondant à 50.

Sinon tout fonctionne bien, la seule chose qu'il me reste à faire c'est lier les derniers frais ( polymere et flash) au reste du calcul, pour qu'il s'ajoute automatiquement aussi.

En tout cas au top ! je vais déciner ta formule sur les pages des autres formats :)

merci !
 

Statistiques des forums

Discussions
311 726
Messages
2 081 955
Membres
101 852
dernier inscrit
dthi16088