Microsoft 365 Aide sur formule de calcul

[comptavia]

Bonjour à toutes et tous.

Voilà ma problématique : J'ai créé un fichier de suivi de facture clients afin de savoir quand je peux reverser la rétrocommission au commercial concerné. Dans ce fichier, j'aurai donc 1 feuille Excel par commercial.

Voici le processus :

- Je reçois une demande de facturation du commercial que je saisis dans la partie "dossier à facturer" (le traitement continue uniquement si le dossier à facturer est validé comme conforme)
- Lorsque tout est OK, je fais la facture et je saisis dans la partie "facture client" date, n° facture, partenaire/client, montant HT et sélectionne le Taux de TVA (montant TVA & Montant TTC calculés automatiquement)
- Une fois la facture réglée par le client, je reverse la commission correspondante au commercial.

La commission à reverser correspond à : montant facture client HT * taux de commission MAIS ce taux est évolutif en fonction de seuils correspondant au CA réalisé par chaque commercial (par exemple entre 0 et 40 000 € : 0.65, entre 40 001 et 100 000 € : 0.70, > à 100 000 € : 0.75) que je centralise pour chacun dans un autre fichier "CHIFFRE AFFAIRES COMMERCIAUX"

Voilà mon problème : j'ai mis une formule de calcul dans montant HT de la partie "FACTURE COMMERCIAL" qui correspond à :

=SI(J5="";"";J5*$Z$4)

Avec J5 = montant HT facture client
$Z$4 = taux de commission (qui se modifie en fonction des informations du fichier "CHIFFRE AFFAIRES COMMERCIAUX")

mais lorsque le commercial dépassera le seuil, le taux de commission va changer et me modifier toutes les lignes précédant le changement, ce que je ne veux pas.

Je vous mets le fichier pour que vous puissiez comprendre...

Comme je pratique très peu le VBA, je ne sais pas du tout comment faire pour éviter ce problème.

Alors si quelqu'un pouvait m'aider ce serait vraiment sympathique.

Johann

 

[Dim.Reichart]

Bonjour,
Je n'y connais pas grand chose en commerce, alors je ne suis pas sur de ce que je vais dire, mais je conseillerais dans le fichier CA commerciaux, d'avoir une colonne qui retrace l'évolution du CA total de chaque commercial avec la date de progression dans la colonne précédente, et d'utiliser uniquement la dernière ligne pour calculer la commission.
Au passage, cela vous permettra également de retracer l'évolution des CA de chaque commerciaux.
Ais-je bien compris l'énoncé?

 

[comptavia]

Le problème c'est que le CA n'est pas constitué que des montants facturés, il y a également des commissions sur des encours, lesquels ne figurent pas dans ce tableau. Merci pour votre concours.

 

[comptavia]

Bonjour,

Je complète mon précédent message avec les éléments suivants :

Ce qui me pose problème c'est que je ne veux pas que la formule de calcul change l'intégralité des cellules (et notamment les cellules précédentes) lorsque le seuil sera atteint et que le taux changera...

Exemple : ligne de facture de janvier 100 € HT, taux de commission 0.65 donnant un reversement de 65 € HT...
ligne de facture de juillet 100 € HT, taux de commission 0.70 donnant un reversement de 70 € HT

Avec ma formule actuelle, lors du dépassement de seuil (en juillet !) le taux change et modifie la ligne de facture de janvier également mais cela ne correspond pas à la réalité et c'est ça mon problème.

Je vous mets également une capture écran du fichier Excel "CA par commerciaux" pour info.

 

[tbft]

Bonjour
serait il possible d'avoir un fichier avec des données fausses mais représentatives, svp?

 

[comptavia]

Alors j'avais mis un fichier projet suivi des factures clients pour commerciaux dans mon message initial. Vous faut-il une copie de l'autre (CA commerciaux) ?

 

[Dim.Reichart]

Bonjour,
Idéalement, pour se faire une bonne idée de ce que vous voulez faire et obtenir, il faudrait des exemples pour chaque fichier cité, et également, au moins quelques exemples du resultat souhaité (bien que dans votre cas, l'explication semble suffire).
Pour ma part, n'ayant pas compris comment est obtenu le chiffre du CA, qui est indispensable, je ne peux en faire plus.

 

[comptavia]

J'ai essayé de vous faire un truc rapide mais je n'ai pas pu faire le lien entre les 2 fichiers... Je pensais aller chercher le taux de commissions dans la cellule E24 du fichier CA commerciaux.
Vous me direz si cela vous convient !

 

[Dim.Reichart]

Donc, afin d'être sur que je comprenne bien:
-Dans le fichier CA commerciaux, le CA et la commission liée sont calculés chaque mois (déjà en place)
-Cette commission doit affecter une "prime" pour les ventes du mois suivant, et uniquement du mois suivant

Est-ce qu'il est possible d'ajouter une recopie des valeurs de tous les mois dans le fichier Suivi Facturation? Peut etre sur un autre onglet, un récapitulatif de tous les commerciaux, pour toute l'année.
La formule colonne S du fichier Suivi pourrait utiliser le chiffre du mois concerné pour chaque ligne.
Si oui, je peux commencer quelque chose dans ce sens sur vos fichiers exemple.

 

[comptavia]

Oui il n’y a pas de problème...

 

[Dim.Reichart]

Re-bonjour,
J'ai du copier les valeurs brutes car il restait des liaisons externes dans vos fichiers.
J'ai corrigé l'orthographe des mois, partculiérement Février, Août et Décembre qui requièrent des accents pour être reconnus comme des dates.
Sur l'onglet Base, un bouton qui va déclencher une macro (à transferer dans un autre bouton, ou à l'ouverture du fichier), et récuperer les commissions de chaque commercial, pour chaque mois et le stocker dans un nouvel onglet, que je vous conseille de masquer.
Puis sur l'onglet Base, j'ai ajouté une colonne, qui effectue un RechercheV, avec le mois de la vente, et le commercial dans l'onglet précédemment cité. On utilise ensuite ce chiffre en lieu et place de AA4 avant.
Est-ce que cela correspond au resultat souhaité?

 

[comptavia]

Bonsoir,
Votre proposition me semble correspondre à ma problématique. Je vais tâcher de l'adapter à mes fichiers.
Je vous remercie pour votre précieuse aide.
Très bon Week-end à vous.
Johann

 

[Dim.Reichart]

Ok, bon courage.