Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

XL 2010 calcul automatique et prorata / mois

kawi99

XLDnaute Junior
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
 

Pièces jointes

  • Commandes véhicules.xlsx
    77.6 KB · Affichages: 175

CISCO

XLDnaute Barbatruc
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
 
Dernière édition:

kawi99

XLDnaute Junior
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

XLDnaute Barbatruc
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.
 

Pièces jointes

  • Commandes véhicules.xlsx
    93 KB · Affichages: 120

kawi99

XLDnaute Junior
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
 

Pièces jointes

  • F2_Commandes véhicules.xlsx
    41.2 KB · Affichages: 104

kawi99

XLDnaute Junior
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

XLDnaute Barbatruc
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

XLDnaute Barbatruc
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

XLDnaute Junior
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
 

Pièces jointes

  • F2_Commandes véhicules_V2.xlsx
    17.4 KB · Affichages: 96

CISCO

XLDnaute Barbatruc
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
 

CISCO

XLDnaute Barbatruc
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
 

Pièces jointes

  • F2_Commandes véhicules_V2travail.xlsx
    29.8 KB · Affichages: 79

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…