Calcul d'un Chiffre d'Affaires complexe (vérifier date d'échéance)

[Tikale]

Bonjour à tous,

J'aurais besoin de calculer le CA de ma société, qui est assez complexe à faire. J'ai aujourd'hui une solution qui tourne, mais qui est très lourde (4 feuilles juste pour le calculs), peut-être existe-t-il une solution plus optimisée ?

La composition du CA :

Le CA est composé de prestations "one shot" et de récurrent.
Pour le one shot, c'est tout simple, on me paie 10k€ en mai, ça s'ajoute au CA du mois de mai.
Pour le récurrent, c'est plus complexe. Il faut gérer le montant de l'abonnement, la durée de l'abonnement (paie tout les 3 mois, 6 mois, ans), les échéances de paiement (qui dépendent de la date d'inscription et la durée de l'abonnement) et la résiliation éventuelle de l'abonnement.

Ma solution actuelle :

J'ai une feuille avec la liste des clients et toutes les infos dessus : montant payé par mois, date d'inscription, date de résiliation (vide si non résilié), engagement.

J'ai une feuille qui me sert à calculer les échéances pour chaque client. Pour la ligne client 1, j'ai ma première colonne qui correspond à l'inscription, puis les autres colonnes avec un edate d'une durée égale à la durée d'engagement.

J'ai une feuille qui extrait le mois de ces échéances de paiement
Une autre qui extrait l'année

Ensuite, j'ai une feuille qui pour chaque mois (en colonne) et chaque client (en ligne), me calcule le montant perçu. Sur chaque case, il y a un if qui vérifie que le client n'a pas résilié ce mois ci, un autre qui vérifie si le mois de cette colonne correspond à un mois d'une échéance, et enfin un qui fait la même chose avec l'année (car on peut avoir une échéance en mai 2011 mais pas en mai 2012, il faut gérer l'année).

Et enfin je somme chaque colonne pour avoir le CA, ce qui est assez complexe au final.

N'y a-t-il pas une solution plus simple par hasard ?
Je pense que c'est surtout au niveau de la vérification de l'échéance que mon système est trop lourd. L'idée, c'est juste de vérifier si au mois X, le cient Y doit payer son abonnement d'une valeur de Z ou pas (sachant qu'on sait quand il s'est inscrit, et qu'on sait qu'il paye tout les W mois, et si il a résilié ou pas)

Si vous avez des idées, je suis preneur

Par avance, merci !

 

[Tibo]

Re : Calcul d'un Chiffre d'Affaires complexe (vérifier date d'échéance)

Bonjour,

...Si vous avez des idées, je suis preneur...

La première idée (la principale) serait de nous joindre un extrait de ton fichier (pas besoin du fichier complet), sans données confidentielles et en nous mettant manuellement pour quelques cas le résultat attendu, avec un bout d'explication sur le pourquoi du comment.


A te (re)lire avec ce fichier.

@+

 

[Tikale]

Re : Calcul d'un Chiffre d'Affaires complexe (vérifier date d'échéance)

Merci pour ta réponse. En effet ça sera plus clair avec un fichier, le voici.

Sur la première feuille, on saisi les données. Par exemple, client 15 inscrit le 13 mars, paye 300€ le jour de son inscription. Il paye ensuite 300€ tout les 3 mois pour une durée indéterminée. Il n'est pas déclaré comme ayant résilié pour le moment.

Le but est de pouvoir remplir les infos de la feuille 2, à savoir, combien on récupère sur notre compte bancaire chaque mois, combien on a au total sur l'année, combien on fait de récurrent au total.

La 3ème feuille est celle qui, pour un client et un mois donné, me donne le montant qu'il nous paye. C'est donc la somme des colonnes qui me donne le CA sur le mois. Et pour avoir ceci (cf la formule utilisée), j'ai besoin des 4 feuilles suivantes.

Ce que j'aurais aimé faire, c'est pouvoir obtenir les infos de la feuille 2 directement, de manière assez simple, en utilisant grand maximum une feuille. Car actuellement, si j'ai 800 clients et que je fais mes prévisions sur 10 ans, ça fait 8000 cases avec une formule complexe, le tout copié sur 5 feuilles, ça met énormément de temps à charger.


Je pense que ça se joue surtout sur le check de l'échéance (vérifier si tel moi, tel client doit payer ou pas), mais je ne sais pas trop comment faire.

Par avance, merci

 

[Tikale]

Re : Calcul d'un Chiffre d'Affaires complexe (vérifier date d'échéance)

Petit up, personne ?

 

[Tibo]

Re : Calcul d'un Chiffre d'Affaires complexe (vérifier date d'échéance)

Bonjour,

J'ai regardé ton fichier à plusieurs reprises. Je n'y ai pas compris grand chose.

Peux-tu, pour un ou deux lignes, en expliquer dans le détail les tenants et les aboutissants ?

Les résultats que retournent ton fichier sont bien corrects ?

A quel niveau souhaites-tu une amélioration ?

A te (re)lire avec davantage de détails et explications.

@+

 

[Staple1600]

Re : Calcul d'un Chiffre d'Affaires complexe (vérifier date d'échéance)

Bonjour


15 aout
dimanche
été
vacances

quatre mots , autant d'indices

qui explique... que

peu de personnes passent sur XLD.

 

[chris]

Re : Calcul d'un Chiffre d'Affaires complexe (vérifier date d'échéance)

Bonjour
Bise à Staple et Tibo

J'ai eu du mal à décoder le principe de fonctionnement mais je pense l'avoir finalement compris mais y ai passé du temps.
Ci-joint le classeur avec une formule qui remplace tous les onglets intermédiaires.
On peut sans doute améliorer...

 

[Tikale]

Re : Calcul d'un Chiffre d'Affaires complexe (vérifier date d'échéance)

Une fois encore, merci pour vos réponse !! Je crois qu'on y est presque avec la formule de Chris, mais il reste un petit cas qui n'est pas géré.

@ Chris : c’est vraiment nickel, exactement ce que je voulais, tout le calcul en une formule (donc une seule feuille), mais il y a deux petit soucis :

- Les noms des colones/cases sont assez complexes, je vois des « R2C6:R31C6 » je sais pas trop à quoi ça correspond (d’habitude j’ai un format A2 :C30). Est-il possible de retourner à un format classique ?

- Cela ne gère que le cas des clients qui payent sur moins de 12 mois. Si j’ai un client qui paye tous les 15 mois, ça va m’indiquer qu’il paye tous les 12 mois. Ceci est du à la fonction MOD(MONTH(R1C)-MONTH('Lise des clients'!R2C2:R31C2);'Lise des clients'!R2C4:R31C4)=0, qui ne "regarde" que le mois et non l’année. J’ai cherché une fonction qui combine month() et year() mais rien trouvé Ca existe ?

@Tibo : voici un peu plus d’explication sur mon doc, j’avoue qu’il n’est pas très simple.

La feuille liste des clients est de la saisie pure, je rentre les infos sur chaque client, c’est ce qui va me servir à calculer le CA.

La feuille chiffre d’affaires est celle où il y a le calcul. J’y reviens après.

La feuille Ech_date_montant est la plus importante. Elle se lit de la sorte : « colonne 31/12/2010, ligne client 6, 299,99€ » signifie que le client 6 doit me payer 300€ pendant le mois de décembre (entre le 1er et le 31).
Du coup, la feuille d’avant, chiffre d’affaire, est la somme de chaque colonne. On additionne le total dû par chaque client en décembre 2010, on a le CA de décembre 2010.

Maintenant, la formule utilisée :

=IF(OR('Lise des clients'!RC6>Ech_date_montant!R1C;'Lise des clients'!RC6=0);COUNTIFS(Ech_mois!R;MONTH(Ech_date_montant!R1C);Ech_années!R;YEAR(Ech_date_montant!R1C))*Ech_montant!RC1;0)

Le If vérifie que le client n’a pas résilié. Si la date de résiliation n’est pas passé ou que la case est vide, le client n’a pas résilié, il faut donc afficher ce qu’il doit payer, soit

COUNTIFS(Ech_mois!R;MONTH(Ech_date_montant!R1C);Ech_années!R;YEAR(Ech_date_montant!R1C))*Ech_montant!RC1

Sinon, on affiche 0 (le client a résilié auparavant) car le client ne paie plus.

Ensuite, le coutifs sert à aller vérifier que à la date où l’on se trouve (si on est dans la colonne décembre 2012 par exemple), le client a bien quelque chose à payer.

Dans la feuille Ech_date, j’ai la date exacte de toutes les prochaines échéances du client (date d’inscription + un certain nombre de mois). Ensuite j’ai une autre feuille qui extrait le mois de cette date, une autre qui extrait l’année.

Du coup, mon countfis vérifie que pour le mois et l’année de la colonne où l’on se trouve, il existe bien une échéance de paiement pour le client, sinon il retourne 0€. Si oui, il retourne le montant de l’échéance, qui est calculé dans la feuille ech_montant.

Comme tu peux le voir, c’est assez lourd comme process, et ça fait plein de cases à remplir (imagine avec 10 000 clients sur 15 ans...). C’est pourquoi je souhaiterais simplifier ça, en ayant une formule comme celle de Chris qui calcule le CA mensuel directement, c’est beaucoup plus léger.

 

[chris]

Re : Calcul d'un Chiffre d'Affaires complexe (vérifier date d'échéance)

Bonjour

Le fichier était en mode L1C1 quand je l'ai ouvert : j'ai modifié de même que la formule.

C'est mieux d'éviter les autres onglets mais vu ton volume, le temps de calcul sera conséquent.

Je te conseille de mettre ta source sous forme de tableau de façon à utiliser des plages dynamiques.

 

[Tikale]

Re : Calcul d'un Chiffre d'Affaires complexe (vérifier date d'échéance)

Eh bien je crois qu'on y est, c'est exactement ce que je cherchais Chris, merci beaucoup!
Chapeau pour le coup du *12 sur l'année, j'ai mis du temps à comprendre à quoi il servait, et c'est typiquement le genre de trucs que j'aurais oublié de faire

Merci aussi à tous ceux qui se sont penchés sur mon problème ! Je pense qu'on se recroisera, je ne compte pas m’éclipser du forum car j'ai pour projet d'apprendre sérieusement excel et le VBA à partir du 15 septembre

A bientôt !