XL 2010 calcul automatique et prorata / mois

[kawi99]

Bonjour à tous,

Essayant de m'avancer dans mon travail, je tente (en vain) de finaliser un tableau de calcul automatique (montants liés à des locations).

Je m'explique (c'est compliqué -enfin pour moi... N'hésitez pas à me dire si je ne suis pas clair) :
une personne loue un véhicule.
* Si c'est une première location, le 1er mois il ne paie rien, le mois suivant, il paie le prorata (nombre de jour d'utilisation. 3jours*loyer journalier s'il l'a récupéré le 28/09 par exemple), les mois suivant (jusqu'à restitution), il paie la totalité du loyer.
* Si ce n'est pas la première location, le premier mois, il paie son ancien loyer, le mois suivant il paie un prorata de l'ancien et du nouveau loyer (exemple : restitution/livraison le 05/09 > 5J*ancien loyer journalier + 25J*nouveau loyer/Jour) en M+1 (Octobre). Les mois suivant, la totalité,...

Je vous joins un tableau qui devrait être plus explicite. Les informations recherchées sont entre les colonnes AF et BL (colorié en vert fluo dans le tableau).
AO:AZ : Mois (de changement sympbolisé par une croix "X").
BA:BL : Montants
Ca fait 3h que j'essaie sans succès. Pour faciliter les calculs, j'ai recrée des colonnes AF:AK + AN mais je n'y arrive pas ;(

Pour faciliter ces explications, je vous donne un exemple des résultats attendus :
Monsieur Nicolas VVVV (lignes 25/26) a un 1er véhicule du 26/02 au 16/09 puis un autre du 16/09 jusqu'à....
Les résultats attendus devraient être :
Mars : 2j*(342.90/30) = 22.86€
Avril>Sept : 342.90€
Octobre : 16j*11.43€ + 14j*11,77€ = 347.63€
Novembre et suite : 353.03€

! Attention : 2 particularités :
1* Il y a un mois de décalage en Paie en la livraison/restitution (exemple, un changement en septembre sera pris en compte en octobre)
2* le coût journalier (AF) se calcule en 30e (en nom pas en fonction de jours sur le mois).

Voilà! Merci d'avance de toute l'aide que vous pourrez m'apporter en cette jolie journée car là je n'y arrive pas et je dois donner ce tableau demain à la première heure...

Bien à vous, Kawi

 

[CISCO]

Bonsoir

Est-ce que ta demande est toujours d'actualité, même si la date de remise du fichier est dépassée ?

@ plus

 

[kawi99]

Bonsoir,

Oui oui, bien sûr !!! Merci

K99

 

[CISCO]

Bonjour

Quelques questions, STP ?

1) Le fichier en pièce jointe est vide à partir de la colonne AF. Est-ce que c'est le bon fichier, ou est-ce que tu as décalé l'ensemble pour ne mettre sur le forum que les colonnes concernant le problème à résoudre ?

2) A quoi correspond la somme 135,59 dans T4 ? Que devrait-il y avoir dans cette cellule ?

3) Je suppose qu'il te faut le loyer, mois par mois, et non pas le total à la restitution. C'est bien ça ?

@ plus

 

[kawi99]

Bonjour Cisco,

Ah aha ah !! je suis trop fort

J'ai juste fermé le fichier sur le mauvais onglet. Les informations se trouvent dans "Suivi Véhicules" et non pas "Calcul LC Paie" (qui normalement est une synthese des informations utiles chaque mois recherché).

Merci, K99

 

[CISCO]

Bonjour

J'avais commencé quelque chose sur la feuille "Calcul LC Paie", après la colonne AF. Je n'ai tiré les formules matricielles (donc à valider avec Ctrl+maj+entrer) que sur les 4 premières lignes. Il y a peut être plus simple, mais, pour le moment...Dis moi si les valeurs trouvées sont correctes.

Je regarderai dans l'autre onglet ce soir.

@ plus

Attention : Il y a une formule différente dans la colonne BQ.

 

[kawi99]

Re,

Wouahhou la formule !

Voila (en PJ / F2_Comm...) ce que j'avais essayé de continuer... C'est pas terrible mais ça donne peut-être un meilleur aperçu (BC:BR) du résultat attendu mais en "moche" et sans cacul des proratas que je n'ai pas su faire...

Sinon, que calcul la colonne BQ, je n'ai pas compris?

Merci, K99

 

[kawi99]

Bonsoir Cisco,

j'ai oublié de répondre à certaines questions... Dans l'ordre :
1) probleme d'onglet :/
2) il y avait un 135.59€ en T4?
3) oui, les proratas (début, fin si pas d'autre livraison et en cas de modification entre une restitution et une livraison) et mois pleins entre les 2..

La formule semble quasi parfaite, juste un doute sur la première ligne (fevrier comptait 29j cette annee, donc 3j*11.43=34.29€ et non 22.86€?)

Merci, K99

 

[CISCO]

Bonjour

Re,
Wouahhou la formule !

Il y a certainement plus simple, mais comme ce n'était pas sur cette feuille qu'il fallait faire ce type de calculs, je ne vais peut être pas continuer dans cette voie...

Sinon, que calcule la colonne BQ, je n'ai pas compris?
Merci, K99

Si j'ai bien compris, si la restitution est faite avant la fin du mois, le client doit payer la location corresponde au prorata (par rapport au nombre de jours depuis le début du mois et loyer mensuel), uniquement à la fin du mois correspondant à la livraison suivante. Cette colonne BQ met "en mémoire" cette somme à faire payer plus tard. Les formules dans les colonnes AG:BP vont chercher cette somme si besoin est, c-à-d à la fin du mois correspondant à la livraison suivante.

@ plus

 

[CISCO]

Rebonjour

Bonsoir Cisco,
2) il y avait un 135.59€ en T4?

Et oui, dans Calcul LC paie! T4.

La formule semble quasi parfaite, juste un doute sur la première ligne (fevrier comptait 29j cette annee, donc 3j*11.43=34.29€ et non 22.86€?)

OK. Je vais vérifier ce "détail".

@ plus

 

[CISCO]

Rebonjour

Bonjour à tous,

2* le coût journalier (AF) se calcule en 30e (en nom pas en fonction de jours sur le mois).

Bien à vous, Kawi

Une petite question au sujet du calcul du prorata : Si le loyer mensuel est de X €, pour 29 jours de location, le client doit payer 29*X/30 €. Pour 30 jours de location, c'est X €. Idem pour 31 jours, si c'est un mois comportant 31 jours. C'est bien ça ?

@ plus

 

[kawi99]

Bonjour Cisco,

Calcul LC paie! T4.

Ah oui dans le tableau initial. Cest le loyer qui compte (colonne U).

Concernant le mode de calcul en 30e, ce n'est pas très clair mais il faut maintenir cette règle. Au final, j'avais cherché une astuce afin de ne pas payer plus cher les mois en 31j (ce qui est inversement applicable à février). J'ai donc crée la colonne Y qui dit que si le calcul est supérieur à un loyer mensuel, il faut appliquer le loyer, sinon le prorata.

Pour finir, je vous joins un nouveau fichier qui sera peut-etre plus clair (sans certitude vue l'usine à gaz et le fait qu'ils ne soient pas tous d'accord...).

J'y ai mis 3 exemples où j'ai forcé les valeurs de BC à BN (je n'ai pas touché au reste) :

* Exemple pour le matricule 1 (colonne B) : 1ere voiture le 15/01, restitution/nouvelle livraison le 15/06 (jusqu'à la fin de l'année).

* Exemple pour le matricule 2 (pour un collaborateur parti, mentionné par un "X" en colonne A) : 1ere voiture le 15/01, restitution/nouvelle livraison le 15/06, sortie le 30/09.

* Exemple pour le matricule 3 : (pour un collaborateur parti) : 1ere voiture le 01/01, 1ere restitution/nouvelle livraison le 15/04, 2nde restitution/nouvelle livraison le 15/09, sortie le 30/11.

Règles :
1 - décalage d'un mois à chaque fois en Paie (une livraison en janvier sera facturée à partir de février)
2 - dernier mois (pour les sortants), régularisation mois M-1 + mois en cours (un sortant le 12 septembre paie aout + jusqu'au 12/09).

PS : j'ai pris des dates fictives au 15 (et mis par défaut des moitiés de loyers, il se peut qu'il y ait des écarts si les mois sont en 31j)...

Désolé de ces précisions de dernières minutes, j'espère que ce ne sera pas trop compliqué à adapter.

Merci Cisco.

Bien cordialement, K99

 

[CISCO]

Bonsoir

Dans BH6 (colonne juin 2016), ne serait-ce pas plutôt 400 € ( somme qui correspond à la location de mai), et dans BI6, 350 € (somme qui correspond à 200 € pour les 15 premiers jours de juin, et 150 € pour les 15 derniers jours du même mois), puis dans 300 € dans les autres colonnes, toujours sur la ligne 6 ?

@ plus

 

[kawi99]

Bonsoir,
Effectivement, petite erreur :
BH5 (puisqu'ancien loyer) = 400€, BI6 = 350 puis 300€ sur le reste de la ligne 6
Merci. bonne nuit

 

[CISCO]

Bonjour

Cf. en pièce jointe, l'état de mon "travail" en ce moment.

Cela ne convient pas totalement pour deux ou trois raisons :
* les formules matricielles ne donnent pas toujours le même résultat que toi
* cela ne fonctionne pas bien si un véhicule est utilisé pendant moins d'un mois, dans le même mois, du 15/01 au 25/01 par ex.

Je cherche à compléter cela, ou à passer par une autre méthode pour trouver les résultats désirés.

Pourrais tu me dire ce que tu dois trouver réellement pour la période du 15/01 au 31/01, à payer fin février :
15 * 400/30 = 200
16 * 400/30 = 213,33 (puisqu'il y a 31 jours en janvier)
17 * 400/30 = 226,67 (si on prend aussi en compte le 15) ?

@ plus