Formule Excel charges constatées d'avance
Bonsoir à tous les passionnés d'Excel !
J'apprécie beaucoup ce forum et me permets donc de vous soumettre un problème que je n'arrive pas à résoudre.
Je cherche à créer une formule Excel qui permette de calculer à la fin de chaque mois, pour une (des) factures(s) donnée(s) une charge constatée d'avance. (CCA dans notre jargon)
A l'attention des non-comptables (que je respecte néanmoins
A la fin de chaque mois on doit comptabiliser, pour chaque facture donnée, la portion du montant de la facture qui ne correspond pas à la période concernée.
Par exemple si je reçois et comptabilise le 01/12/16 une facture d'assurance de 12 000 € qui court du 01/01/17 au 31/12/17, je dois pouvoir établir :
qu'au 31/12/16 : la charge constatée d'avance (anticipée) est de : 12 000
qu'au 31/01/17 : " " " " " 11 000
qu'au 28/02/17 : " " " " " 10 000
etc.
Le calcul pour un mois donné (ex: au 31/12/16) est assez simple, mais cela se complique quand on veut formuler via Excel tous les cas possibles (factures trimestrielles, bimestrielles ou autres).
J'ai essayé d'utiliser SI.CONDITIONS mais sans succès.
Je me permets de vous joindre un fichier Excel qui indique la problématique et les résultats souhaités.
En comptant sur votre talent et vos propositions !
Bien à vous tous
Bonjour,
Formule suivante en F9 et à copier vers la droite :
=ARRONDI.INF(($D9-F$7)/30;0)*$A9/12Remarque : Pour éviter les CCA et FNP, on préconise d'utiliser les comptes d'abonnements( comptes 488xxxxx) mensuels des charges ou de produits.
Bonjour Raja,
Merci de votre réponse qui m'a bien aidé.
Malheureusement dans des cas moins simples, ça conduit à des CCA "négatifs" si on continue à tirer la formule vers la droite, et je voudrais qu'on puisse copier la formule dans un tableau sur 24 ou 36 mois, sans se soucier de la période concernée par la facture (mois, trimestre, annuelle, bisannuelle,...)
A l'aide de votre formule,et en utilisant SI.CONDITIONS, dans le fichier joint, j'ai réussi à obtenir le résultat souhaité pour le mois M dans différents cas possibles.
Mais ça ne fonctionne pas pour les mois suivants ...
Auriez-vous une idée ?
Merci d'avance
MichaelH
Re,
La solution a été donnée selon ta demande. Il vaut mieux tout dire pour avoir une solution adaptée à toutes les situations. On gagne du temps ainsi. Ceci étant dit, essaye la formule suivante en G18 à copier vers la droite et vers le bas :
=SI(G$16>$C18;MAX(ARRONDI(($D18-G$16)*$A18/$E18;2);0);$A18)Tu pourras adapter cette formule pour les mois complets, mais il faut calculer les nombres de jour total à repartir.
NB: Les calculs se feront par nombre de jours restants.
Bonsoir le forum,
MICHAELH a écrit :Je cherche à créer une formule Excel qui permette de calculer à la fin de chaque mois, pour une (des) factures(s) donnée(s) une charge constatée d'avance. (CCA dans notre jargon)
Les règles de comptabilisation ont été modifiées ???
Dans le cas présenté : le 01/12/N la facture d'assurance est enregistrée.
Etant donné qu'elle ne concerne pas l'exercice comptable N : elle est annulée le 31/12/N à l'occasion des travaux d'inventaire.
Ecriture de contrepassation au début de l'exercice N+1 : la charge est à nouveau débitée une fois pour toutes.
C'est l'exercice N+1 qui la supportera.
http://www.memocompta.fr/comptabilite/fin-exercice/cca/
Salut mbbp,
C'est un problème d'arrêté comptable qui peut être différent de fin d'exercice que l'auditeur soulève. Ceci dit le problème est le même, il faut retrouver les charges voire les produits qui n'appartiennent pas à la période concernée (mensuel trimestriel ou annuel). Justement pour éviter de se casser la tête, on utilise les comptes d'abonnement, quand il s'agit d'une période assez longue.
Salut Raja,
Merci pour cette information.
Cela dit, pour le problème soulevé, il me semble beaucoup plus simple d'utiliser les "écritures classiques".
Bonsoir,
J'ai joint un schéma pour éviter les CCA voire FNP. ça se pratique dans pas mal d'entreprises qui effectuent les arrêtés comptables périodiques et dans des cabinets comptables.
Bonjour Raja,
Géniale ta formule !
Merci bcp et bravo à toi
Cdt
MichaelH
Bonsoir Raja,
Je me permets de faire encore appel à ton talent (sans flagornerie aucune) :
Si je souhaite obtenir, non pas les soldes à la fin de chaque mois, mais les 'flux" mensuels, comment peut-on faire ?
De façon que les cumuls de 'dotations' soient égaux à zéro.
Comme dans le fichier joint (à partir de la colonne AB )
Je n'y parviens pas.
Merci d'avance pour ton aide.
MichaelH
Bonsoir,
Formule en AB16 à copier vers le bas et vers la droite :
=$A16-G16-SOMME($AA16:AA16)Merci Raja,mais ça ne correspond pas au résultat attendu : la somme des 'flux" doit être égale à zéro, et pas à 12 000.
Idéalement les flux seraient calculés directement, (à partir des colonnes A à E) sans utiliser les formules des colonnes G à L que tu m'as fournies.
Merci encore Raja
MichaelH
Re,
Essaye ça :
=MIN(G16-F16;$A16)Ou la formule unique :
=MIN(SI(G$14>$C16;MAX(ARRONDI(($D16-G$14)*$A16/$E16;2);0);$A16)-F16;$A16)Oui Raja tu as raison il faut que je clarifie :
En clair ce que j'appelle "flux" ce sont des écritures au débit ou au crédit d'un compte 486000 ( et non pas la constatation d'un "stock" à un moment donné )
Dans mon fichier Formule CCA-2, en colonne AK, le total des "flux" est bien égal à zéro : (exemple ligne 16)
Au 30/06 on constate une CCA de 7 434 € (c'est pour moi un "flux", qui a pour contrepartie une baisse de charge comptable)
Au 31/07, on déduit la quotité (charge) correspondant à juillet : (-) 4 043 € (on en est en cumul à 3 391 €, comme en cellule H )
Au 31/08, on déduit la quotité " " " 01/08 au 26/08 : (-) 3 391 € ( " " " " " 0 € comme en cellule I )
Je me demandais donc si on pouvait passer au résultat attendu dans ma colonne AB directement, sans passer par ta super formule de la colonne G.
Merci beaucoup de ton intérêt.
Bonne soirée à toi
Re,
As-tu regardé et essayé les formules proposées dans mon précédent message ?
Re,
oui je viens de voir à l'instant, incredible formule, mais crois-tu qu'on pourrait se passer des calculs de soldes "intermédiaires" (ex: cellules G16 à L16) ? pour obtenir directement le résultat des colonnes AB AC AD... ?
Bien à toi
Re,
Donc tu n'as pas testé la 2ème formule qui se passe des calculs intermédiaires. Accessoirement, je te signale que nous sommes dans une discussion close par toi. Soit il faut décocher la résolution du problème soit il faut commencer un autre fil. Comme ça, c'est comprhénsible pour la communauté.
Bonjour Raja,
Si, j'ai bien testé ta formule (qui commence par MIN(SI...), mais sauf erreur, la formule une fois tirée en colonne AC se réfère au calcul intermédiaire en colonne G, la AD à H,etc.
Cdt
MichaelH
Re,
OK, je vois. Teste ça :
=MIN(SI(AB$14>$C16;MAX(ARRONDI(($D16-AB$14)*$A16/$E16;2);0);$A16)-SOMME($AA16:AA16);$A16)C'est tout bonnement parfait...et génial !!
Bravo Raja et merci
Sans doute à une prochaine fois...
MichaelH