Discussion :

[Bad Bond]

Bonjour,

Sur excel, j'ai des achats et des ventes journalières.

Savez-vous si on peut coder en VBA pour calculer les marges (d'abord en calculant un résultat bien entendu) ? sachant que les ventes d'un jour ne compensent pas forcément les achats du même jour, mais de 2 ou 3 jours antérieurs. Voir desfois, il faut 3 jour de ventes pour compenser 1 ou 2 jours d'achat.

Par exemple, je ne sais pas à l'avance si il faut prendre 2 ou 5 jours de vente pour compenser 1 ou 5 jour d'achat.

D'avance merci

[Pierre Dumas]

Bonjour

Oui, très certainement.

Mais le souci, ce n'est pas le VBA : ce sont les règles qu'il faut appliquer.

Qu'est-ce qui compense quoi ? Selon quels critères ? Comment peut-on le formuler en une ou plusieurs phrases ?

Ensuite, on pourra trouver le moyen.

Cela pourra être du VBA, du Power Query ou simplement des formules.

En espérant qu cela aide

Pierre Dumas

[Bad Bond]

Ok merci de la réponse
je suis pas de la partie, j'ai un peu de mal à voir le potentiel de VBA

Pour te répondre :
Qu'est-ce qui compense quoi ? Les ventes compenses les achats, j'ai un montant supérieur de vente par rapport aux achats

Selon quels critères ? en fonction du résultat généré entre la différence entre les ventes et les achats sur une période donnée

Comment peut-on le formuler en une ou plusieurs phrases ? pourquoi pas avec un exemple
J1 : achat = 1 ; vente = 0
J2 : achat = 3 ; vente = 0
J3 : achat = 1 ; vente = 5
J4 : achat = 4 ; vente = 0
J5 : achat = 6 ; vente = 2
etc...
Donc la vente du J3 est visiblement liée aux jours 1 et 2 mais pas le 3, sinon j'aurait un résultat de 0 sur l'opération
La vente du J5 est visiblement liée au jour 3 mais pas les jours 4 et 5, pour la même raison que précédement.

[Phil333250]

Bonjour à tous

Il ne faut pas mettre la charrue avant les bœufs.
Comme le précise Pierre il faut dans un premier temps poser un "cahier des charges" clair et précis.

Qu'est-ce qui compense quoi ? Les ventes compenses les achats, j'ai un montant supérieur de vente par rapport aux achats

Quelle(s) est (sont) la (les) règle(s) de compensation ?

la vente du J3 est visiblement liée aux jours 1 et 2 mais pas le 3

Que veut dire visiblement ? Pourquoi ce n'est pas lié uniquement à J1 ?
Pourquoi la vente de J5 n'est pas lié aux achats de J2 ?

Que représentent les valeurs, des quantités, des euros ?

Si euros :
J1 : achat = 100€ ; vente = 0
J2 : achat = 300€ ; vente = 0
J3 : achat = 100e ; vente = 500€
=> On voit ici que les achats de J1 peuvent générer 500€ de vente. Tout dépend de la marge appliquée.
Mais les 500€ peuvent aussi être générés grâce à J1 & J2.
Ils peuvent être également générés grâce à J1 et une partie de J2.

J4 : achat = 4 ; vente = 0
J5 : achat = 6 ; vente = 2
=> Même raisonnement.

Si quantité :
Il me semble alors qu'il s'agit de gestion de stocks et de valorisation de stock. Et là, il existe plusieurs méthodes : FIFO, LIFO, CMUP, etc.

Ces quelques lignes pour te montrer les questions que l'on peut se poser.
Afin de pouvoir t'aider il faut absolument que tu soit très clair et précis dans ta demande.

Ce n'est que quand tout sera clair que

on pourra trouver le moyen. Cela pourra être du VBA, du Power Query ou simplement des formules.

Philippe

[Bad Bond]

Ok merci

les règles peuvent être une opération réalisant une marge entre 2% et 8%, avec pas plus de 5 jours d'écart.
Ce qui devrait répondre à la question du "visiblement".

Il n'y pas de gestion de quantité ou de stock. Le stock fluctue et il ne doit pas rester un stock de plus de 2 semaines.
Donc la gestion du stock, ou de la variation de stock n'est pas important.

A+

[Mat.M]

Bonjour,
Sur excel, j'ai des achats et des ventes journalières.

bonsoir vous voyez bien que vous atteignez les limites d'Excel.
Sinon il faut faire des triggers une application serveur qui tourne sans arrêt et déclenche des bonnes requêtes lorsqu'il le faut
Pour cela il faut une application serveur liée à un SGBDR genre Oracle ou Ms Server ou MySQL
Parce que sinon vous ne vous en sortirez jamais.
Excel c'est un outil de small office small business.
Cela ne permet pas de faire des traitements sur des données en flux tendu.
Pour ce qui est de la gestion des stocks comment vous faites les ré-approvisionnements ?
Donc c'est tout un système à concevoir sur papier

[Bad Bond]

je me suis mal expliqué car j'ai l'impression qu'on se disperse.

J'ai des fichiers excel avec des ventes journalières et des achats journaliers. Ce sont des donnés anciennes, je ne cherche pas à faire des analyses toutes les semaines.

Je cherche un système pour automatiser le calcul d'un résultat entre les ventes et les achats mais au jour le jour ou de semaine en semaine. Le soucis est que des achats effectués à un instant T peuvent être revendus à T+1 ou T+5 ou T+6.
La gestion du stock ne me concerne pas, dans la mesure où la variation de stock est très faible. Donc ce n'est pas significatif.

Merci

[fred1599]

Hello,

À partir des seuls totaux journaliers, on ne peut pas connaître la correspondance réelle entre achats et ventes. On peut seulement appliquer une règle conventionnelle, par exemple FIFO, avec fractionnement des montants, délai maximal et marge admissible. Si votre objectif est uniquement la marge d’une semaine ou d’une période et que la variation de stock est négligeable, il suffit en revanche de comparer les cumuls des ventes et des achats. Merci de préciser lequel de ces deux résultats vous recherchez, ainsi que le traitement des reliquats et la règle exacte concernant T+5 ou T+6.

[Bad Bond]

je cherche à calculer la marge.
Le souci est que je ne sais pas à quelle période correspondent les ventes liées aux achats de T et/ou T+1.
Donc je suis obligé d'y aller à taton, en prenant les achats et en comparant avec les ventes.

Par exemple, sur une période les ventes en T+3 et T+4 peuvent correspondent aux achats de T
Et d'autres fois, les ventes en T+3 et T+4 peuvent correspondent aux achats de T à T+3.

[fred1599]

Pouvez-vous fournir un exemple complet avec une dizaine de jours et, pour chaque jour, le résultat exact que vous attendez ?

[Bad Bond]

voici un exemple d'un traitement fait manuellement.
Comme il y'a beaucoup de ligne, j'avais tout regroupé par semaine au préalable.



Merci

[fred1599]

Merci pour l'exemple, là c'est beaucoup plus clair.

Si je comprends bien votre tableau, vous ne cherchez pas simplement à calculer une marge semaine par semaine.

Vous faites plutôt ceci :

- les achats sont regroupés en périodes successives ;
- les ventes sont également regroupées en périodes successives ;
- les deux périodes associées ne couvrent pas forcément exactement les mêmes semaines ;
- une semaine déjà utilisée dans une période ne semble pas être réutilisée dans la suivante.

Par exemple, dans votre tableau :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
Ventes S1 à S3 : 11 462,22
Achats S1 à S3 : 10 677,01
 
Marge = 11 462,22 - 10 677,01
= 785,21
 
% marge = 785,21 / 11 462,22
= 6,85 %

Puis :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
Ventes S4 à S8
correspondent à
Achats S4 à S7

et la période suivante reprend donc les achats à partir de S8 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
Ventes S9 à S11
Achats S8 à S11

Ça, on peut parfaitement l'automatiser en VBA.

Par contre il reste une règle indispensable à préciser avant d'écrire l'algorithme : comment choisissez-vous la fin d'une période lorsqu'il existe plusieurs possibilités ?

Car je remarque aussi dans votre exemple des marges de :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
12,21 %
12,20 %
1,11 %
9,55 %
25,68 %
21,96 %

Donc la règle indiquée précédemment « marge entre 2 % et 8 % » ne semble finalement pas être une contrainte obligatoire.

Prenons un cas où, en cumulant progressivement les semaines, on pourrait obtenir :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
S1 à S3 : marge 3 %
S1 à S4 : marge 5 %
S1 à S5 : marge 7 %

Les trois solutions respecteraient une marge comprise entre 2 et 8 %.

Laquelle choisiriez-vous manuellement et pourquoi ?

La période la plus courte ?
La marge la plus proche d'une valeur cible ?
La première marge positive ?
Celle qui permet ensuite de mieux faire correspondre les périodes suivantes ?

C'est cette règle qui permettra de reproduire automatiquement votre tableau.

Une fois ce point défini, le VBA n'est à mon avis plus le problème principal : il s'agira surtout de parcourir chronologiquement les achats et ventes, de tester les cumuls possibles et de valider chaque couple de périodes sans réutiliser les semaines déjà affectées.

[Mat.M]

Le souci est que je ne sais pas à quelle période correspondent les ventes liées aux achats de T et/ou T+1.
Donc je suis obligé d'y aller à taton, en prenant les achats et en comparant avec les ventes.

bah il suffit d'enregistrer les dates et heures des ventes et des achats , non ?
Pour ça comme je l'ai expliqué dans mon premier message il faut un système qui génére des écritures en cas d'opérations de vente ou d'achat.
A partir du moment où vous avez tous les éléments en votre possession vous pouvez calculer des marges.
Et procéder ainsi ça s'appelle de la gestion et mouvements de stocks.
Mais continuer à vous entêter vous écrivez que vous ne voulez pas de gestion de stocks
Vous devriez regarder des outils comme Power BI par exemple

[Bad Bond]

ok merci pour vos réponses
si j'ai bien compris, VBA est une bonne idée.
En même temps, on peut faire des virus avec ça...

pour répondre à votre question, je prendrai dans l'ordre
Période la plus courte, car ce n'est pas logique (au vu de l'activité) de se constituer un stock
La marge postive, car on ne revend pas à perte (normalement)
La marge la plus proche de l'objectif, qui normalement entre 2% et 8 % (au delà, faudra trouver une explication et donc vérifier dans le détail)

Enfin, votre phrase :

Une fois ce point défini, le VBA n'est à mon avis plus le problème principal : il s'agira surtout de parcourir chronologiquement les achats et ventes, de tester les cumuls possibles et de valider chaque couple de périodes sans réutiliser les semaines déjà affectées.

c'est exactement ça.

Me reste plus qu'à me lancer dans le vba

Merci pour les aides

[Bad Bond]

bah il suffit d'enregistrer les dates et heures des ventes et des achats , non ?
Pour ça comme je l'ai expliqué dans mon premier message il faut un système qui génére des écritures en cas d'opérations de vente ou d'achat.
A partir du moment où vous avez tous les éléments en votre possession vous pouvez calculer des marges.
Et procéder ainsi ça s'appelle de la gestion et mouvements de stocks.
Mais continuer à vous entêter vous écrivez que vous ne voulez pas de gestion de stocks
Vous devriez regarder des outils comme Power BI par exemple

Mon souci est que je ne connais pas les correspondances entre les achats et les ventes.
Ce que je veux dire, par exemple, c'est que je ne sais pas si les ventes en jour 4 correspondent aux ahchats des jours 1 et 2 ou juste jour 1, ou jours 1 a 4.

Vu l'activité, il n'est pas dans leur intérêt de conserver du stock trop longtemps. Bien sûr qu'ils en ont, mais l'activité fait qu'ils ne le stockent pas plus d'une semaine en général.

[fred1599]

Merci, c'est beaucoup plus clair.

Je retiens donc :

- période la plus courte possible ;
- marge obligatoirement positive ;
- objectif entre 2 % et 8 % ;
- au-delà de 8 %, résultat à signaler pour vérification ;
- les semaines déjà utilisées ne sont plus reprises ensuite.

Il reste juste un point à préciser :

Si la période la plus courte donne +1 %, mais qu'en ajoutant une semaine on obtient +5 %, laquelle choisissez-vous ?

C'est cette réponse qui permettra de fixer définitivement la logique du VBA.

[Bad Bond]

Si la période la plus courte donne +1 %, mais qu'en ajoutant une semaine on obtient +5 %, laquelle choisissez-vous ?

je choisirai la marge à +5% à condition qu'entre la vente et le dernier achat pris en compte il n'y ait pas plus de 2 semaines d'écart (voir une semaine et demi), car ça serait incohérent avec la façon de fonctionner.

[Mat.M]

pour ce qui est du VBA il y a des tutos il me semble sur ce site.
Si vous voulez calculer une période déterminée il y a la fonction DateDiff() puis avec un if bref une condition logique vous pouvez faire les calculs correspondants
Pour trouver des correspondances entre achats et ventes utiliser DateDiff() au besoin et faire des comparaison if then.
C'est évident qu'en ne manipulant que des cellules là c'est plus compliqué il faut passer par du code VBA

[Bad Bond]

merci bien
je me doutais bien que mon projet était possible avec du VBA, sachant que des mecs font des virus avec.
Mais je voulais être sûr de ne pas passer à côté de quelque chose de plus simple, ou d'être complétement à l'ouest

[fred1599]

Ok,

L’algorithme pourrait appliquer les critères dans cet ordre :

1. cumuler chronologiquement les achats et les ventes, sans réutiliser une période déjà affectée ;
2. ne conserver que les associations dont la marge est positive ;
3. privilégier une marge comprise entre 2 % et 8 %, idéalement la plus proche de votre objectif ;
4. imposer un écart maximal de deux semaines entre la vente et le dernier achat retenu ;
5. parmi les solutions équivalentes, choisir la période la plus courte ;
6. signaler les cas sans solution ou présentant une marge supérieure à 8 % pour vérification manuelle.

Dans votre exemple, la solution à +5 % serait donc retenue plutôt que celle à +1 %, à condition de respecter ce délai maximal.

Il faut néanmoins garder à l’esprit que le résultat restera une reconstitution probable des correspondances entre achats et ventes, et non une marge comptable certaine puisqu’il n’existe pas de traçabilité réelle des mouvements de stock. Mais avec vos contraintes métier, cette estimation peut être suffisamment cohérente pour faire ressortir les anomalies.

Un algo à tester, et donc à effectuer en VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
DébutAchats = première semaine non utilisée
DébutVentes = première semaine non utilisée
 
Tant qu'il reste des achats et des ventes :
 
    Générer toutes les combinaisons possibles :
        Achats de DébutAchats à FinAchats
        Ventes de DébutVentes à FinVentes
 
    Ne tester que les combinaisons pour lesquelles :
        - la marge est positive ;
        - le dernier achat et la vente ne sont pas séparés
          de plus de deux semaines.
 
    Pour chaque combinaison :
        TotalAchats = somme des achats
        TotalVentes = somme des ventes
        Marge = (TotalVentes - TotalAchats) / TotalVentes
 
    Classer les solutions dans cet ordre :
        1. marge comprise entre 2 % et 8 % ;
        2. marge la plus proche de l'objectif, par exemple 5 % ;
        3. période la plus courte ;
        4. fin de période la plus ancienne.
 
    Retenir la meilleure solution.
 
    Enregistrer le résultat :
        - semaines d'achats retenues ;
        - semaines de ventes retenues ;
        - totaux ;
        - montant et pourcentage de marge.
 
    Reprendre à :
        FinAchats + 1
        FinVentes + 1