Discussion :

[Malick]

Pour les besoins de ma comptabilité, je suis en train de concevoir une base de données pour un meilleur suivi des subventions et de leur rattachement à l'exercice concerné. Pour cela, j'ai le modèle suivant :



Les règles de gestion :

• un contrat a une durée d'au moins trois (3) ans ;
• un contrat peut faire l'objet de plusieurs renouvellements d’où la table t_Renouvellement ;

L'idée pour moi est de remplir automatiquement , lors de la saisie d'un nouveau contrat, la table t_Repartitions en fonction des éléments saisis dans la table t_Contrats via son formulaire. Cette répartition devrait se faire en fonction de la durée du contrat.

Dans t_Repartitions j'ai :

• idRepartition_pk
• idContrat_fk : pour identifier le contrat concerné par l'enregistrement
• idRenouvellement_fk : en cas de renouvellement, permet d'identifier le renouvellement concerné et ainsi remonter vers le contrat
• MoisDotation : devrait correspondre à la date d'anniversaire de la signature du contrat (j’ai pensé à unDateAdd()
• AnneeDotation : Correspond à l'année de la dotation
• MontantDotation : Montant réparti en fonction de la durée, cela pour chaque année

J'ai commencé avec ceci (code récupéré depuis le site de Hervé INISAN et que j'ai tenté d'adapter à ma situation) :

Code vba :

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
Private Sub Repartition()
    ' Quelques variables
    Dim rst As DAO.Recordset
    Dim Duree As Long
 
    ' Ouvrir la table en lecture/écriture
    Set rst = CurrentDb.OpenRecordset("tRepartitions", dbOpenDynaset)
 
        ' Boucler sur la durée du contrat
    For Duree = 1 To Me.lblDureeContrat
        ' Créer un enregistrement dans la table
        rst.AddNew
 
        ' Alimenter les champs
        rst("idContrat_fk") = Me.lblidContrat_pk
        'rst("MoisDotation") =
        rst("AnneeDotation") = 0
        rst("MontantDotation") = Me.lblMontantSubvention / Me.lblDureeContrat
 
        ' Valider
        rst.Update
    Next
 
    rst.Close
    Set rst = Nothing
    MsgBox "Opération terminée !", vbInformation
End Sub

Résultat obtenu :



Le résultat obtenu n'est pas satisfaisant, car je bloque pour remplir automatiquement les autres champs vides.

Seriez-vous disponible à m'aider pour trouver une solution à cela ?
Auriez-vous d'autres idées ou solutions ?

Merci d'avance
Cordialement,
Malick

[Malick]

Salut,

Avec ce code et pour un Montant global de 100 000 FCFA :

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
Private Sub Repartition()
    ' Quelques variables
    Dim rst As DAO.Recordset
    Dim Duree As Long
 
    ' Ouvrir la table en lecture/écriture
    Set rst = CurrentDb.OpenRecordset("tRepartitions", dbOpenDynaset)
 
        ' Boucler sur la durée du contrat
    For Duree = 1 To Me.lblDureeContrat
        ' Créer un enregistrement dans la table
        rst.AddNew
 
        ' Alimenter les champs
        rst("idContrat_fk") = Me.lblidContrat_pk
        rst("PeriodeDotation") = DateAdd("yyyy", 1, Me.lblDateDebutContrat)
        rst("MontantDotation") = Me.lblMontantSubvention / Me.lblDureeContrat
 
        ' Valider
        rst.Update
    Next
 
    rst.Close
    Set rst = Nothing
    MsgBox "Opération terminée !", vbInformation
End Sub

J'obtiens ceci lors comme génération d'écriture :



Comment pourrais-je faire en sorte que dans le champ PeriodeDotation, les données s'incrémentent de +1 année. Ainsi j'aurais dans mon exemple : 01/06/2016 - 01/06/2017 - 01/06/2018 ?

Merci d'avance

[ClaudeLELOUP]

Bonjour Malick,

pour un meilleur suivi des subventions et de leur rattachement à l'exercice concerné.

Peux-tu expliquer le problème comptable : je ne comprends pas le but.

S'agit-il de répartir la prise en charge de la subvention sur la durée de vie du contrat ?

[Malick]

Claude

Merci pour ce retour.

Bonjour Malick,

S'agit-il de répartir la prise en charge de la subvention sur la durée de vie du contrat ?

C'est exactement cela.

Et chaque année, une facture relative à la somme à payer est envoyée à chaque partenaire pour qu'il puisse régler la quote-part de l'année concernée. Le montant de la facture correspond au montant se trouvant sur la table t_Repartition en fonction de l'année concernée.

Certains contrats sont signés en cours d'année et par conséquent pour un exercice donné, je devrais faire le prorata du montant à répartir sur une année. Le reliquat du montant annuel sera logé dans un compte de Produit constaté d'avance.

Je compte ensuite, via une requête, faire le rattachement du produit à chaque exercice comptable (année) en fonction de la date de signature du contrat. Cette requête me permettra d'alimenter ma base de reporting mensuel.

Est-ce que tu comprends mieux mon objectif ?

Merci d'avance

[ClaudeLELOUP]

Mais alors, si la db contient pour le contrat :
- la date de départ ;
- la durée ;
- la subvention,
le prorata à une date pourrait se calculer avec une fonction.
Pourquoi veux-tu ajouter des tables ?

[Malick]

le prorata à une date pourrait se calculer avec une fonction.

Effectivement, mais c'est l'incrémentation des lignes qui me pose problème en fonction de la durée.

Au moment de saisir le contrat, j'ai créé dans le formulaire un champ DureeContrat avec la fonction DateDiff().

Pourquoi veux-tu ajouter des tables ?

L'idée m'a été proposée sur le Chat, afin de générer les opérations directement dans la table t_Repartitions et gérer en même temps les renouvellements.

En effet, un contrat terminé peut être renouvelé, et dans une telle situation, le montant à répartir sera celui du contrat de renouvellement.

Tu vois mieux ?

Si tu as une autre vision de la chose, je suis preneur aussi pour échanger dessus.

Edit :

	je pense comprendre ta logique. C'est à dire au lieu de raisonner en année, je devrais résonner en mois et ainsi faire la répartition par mois. C'est bien cela ?

[ClaudeLELOUP]

Qu'as-tu comme données au départ ? concernant le contrat ?

- la date début
- la date fin ou la durée
- le montant de la subvention



Que dois-tu réclamer et quand ?

Donne-moi un exemple chiffré.


Explique un cas de "renouvellement".

[Malick]

Qu'as-tu comme données au départ ? concernant le contrat ?

Dans les contrats, la durée est précisée soit en mois, soit en années.

• Date début
• Durée

Que dois-tu réclamer et quand ?

Sur l'année N+1, à la date d'anniversaire de la signature du contrat, je dois réclamer au partenaire le montant de la subvention annuelle.
Exemple

Le 30/06/2016, on signe un contrat qui prendra fin le 30/06/2019. Notre partenaire s'engage à nous verser une subvention globale de 600 000 FCFA qui sera répartie en fonction de la durée du contrat (600 000/3=200 000 par an).

• Cela dit, on va établir une première facture correspondant à la première année du contrat notamment la période du 01/07/2016 au 30/06/2017 pour un montant de 200 000 FCFA à partir de la date de saisie pour réclamer la quote-part de l'année courante. Étant donné que le contrat est signé en cours d'année, je souhaiterais avoir la répartition de la subvention annuelle en fonction de la durée restante sur l'année, et ainsi faire le prorata à rattacher à l'exercice courant. Le reliquat sera placé dans un compte de produits à recevoir (idem pour les facturations suivantes).
• Une deuxième facture sera établie le 30/06/2017 pour réclamer la subvention de la période du 01/07/2017 au 30/06/2018 soit 200 000 FCFA.
• Une troisième facture sera également établie le 3/06/2018 pour réclamer la subvention de la période du 01/07/2018 au 30/06/2019 soit 200 000 FCFA.

Explique un cas de "renouvellement".

Arrivé à échéance, soit le 30/06/2019, le contrat peut faire l'objet d'un premier renouvellement (ex. : 1 200 000 FCFA) pour une durée donnée (ex. : 5 ans). Cela dit, comme pour le contrat initial, il me faudra faire la répartition en fonction de la durée du renouvellement et du MontantRenouvellement. Ce qui revient à répartir les 1 200 000 sur la durée du renouvellement pour avoir 300 000 FCFA/an.
Vient ensuite le calcul des proratas pour faire le rattachement des produits à leur exercice comptable.

Voilà en gros le projet.

Que penses-tu des explications ?

[Malick]

Claude,

Voici un exemple chiffré de ce que je souhaite faire avec le code posté dans mon premier billet :



Un contrat arrivé à échéance peut faire l'objet d'un renouvellement. Ainsi, un tableau de répartition du montant renouvelé semblable à celui des contrats sera également fait.

La base de données permettra ainsi de faire le suivi des contrats.

Tu vois mieux ce que je souhaite faire ?

Bonne soirée et merci de ta disponibilité

Cordialement

[ClaudeLELOUP]

Bonjour Malick,

Je verrais plutôt comme ceci :




Pour les événements à déclencher dans ta compta :
- quand passes-tu l’écriture
   Partenaire
     à Subventions perçues
(Probablement à l’anniversaire pour la fraction courue pendant l’année écoulée.)

- pour les clôtures comptables (à quelle cadence chez toi ? Mensuelle ? Trimestrielle ? Au 31/12 ?), je te propose de comptabiliser
   Redressements d’actif pour recettes différées compte de bilan
     à Subventions redressements compte de résultat

À concurrence du prorata couru depuis la dernière perception.
Avec extourne systématique en début de période suivante.
Cette méthode règle l'empoisonnant problème des arrondis et est autocorrective (les erreurs éventuelles d'une clôture sont extournées lors de la clôture suivante).

[Malick]

Bonjour Claude,

Merci pour ce retour.

Pour les événements à déclencher dans ta compta :
- quand passes-tu l’écriture
Partenaire
à Subventions perçues
(Probablement à l’anniversaire pour la fraction courue pendant l’année écoulée.)

Effectivement.

pour les clôtures comptables (à quelle cadence chez toi ? Mensuelle ? Trimestrielle ? Au 31/12 ?), je te propose de comptabiliser
Redressements d’actif pour recettes différées compte de bilan
à Subventions redressements compte de résultat

Les clôtures comptables se font au 31/12. Il s'agit des produits constatés d'avance (recettes différées) et l'extourne se fait sur N+1.

Pour revenir à ton modèle, dans t_Contrats, est-ce qu'on va garder les champs DateDebutContrat et DureContrat ?

Dans t_Tranches :

• Subvention : correspond à la répartition faite du montant global se trouvant dans la table t_Contrats ?
• Debut et Duree : Correspondent à quoi (la même chose que dans t_Contrats) ? Comment seront-ils renseignés ?

Merci encore

Cordialement
Malick

[ClaudeLELOUP]

t_Contrats, est-ce qu'on va garder les champs DateDebutContrat et DureContrat ?

Non, Debut et Duree sont des attributs de la Tranche. (Il existe peut-être un mot plus adéquat que « Tranche » pour désigner cette période de la vie d'un contrat susceptible de reconduction.)

Quand j'écris « AutresCaracteristiques », je pense à des détails qui ne sont pas utiles pour expliquer mon idée. Par exemple : l'identité des partenaires, la description du contrat, quel responsable gère ce dossier, etc.
Avec une telle organisation des données, il faut
- un processus qui se déclenche à chaque anniversaire pour comptabiliser la perception de l’annualité de subvention ;
- une procédure pour calculer la somme des prorata en fin d’année.

Tout est-il clair pour toi ?

[Malick]

Non, Debut et Duree sont des attributs de la Tranche. (Il existe peut-être un mot plus adéquat que « Tranche » pour désigner cette période de la vie d'un contrat susceptible de reconduction.)

Ok je comprends maintenant l'idée. Donc c'est la table t_Tranches qui va permettre de gérer en même temps les renouvellements de contrats ?

Si c'est bien cela, l'idée est claire pour moi.

Avec une telle organisation des données, il faut
- un processus qui se déclenche à chaque anniversaire pour comptabiliser la perception de l’annualité de subvention ;
- une procédure pour calculer la somme des prorata en fin d’année.

VBA s'impose donc ?

Edit : je vais faire un modèle basé sur ta proposition pour voir ce que cela va donner.

[ClaudeLELOUP]

c'est la table t_Tranches qui va permettre de gérer en même temps les renouvellements de contrats ?

Oui, c'est dans cette table qu'on trouve les caractéristiques des « tranches de vie » des contrats.

VBA s'impose donc ?

Sans doute ! Et probablement l'ajout de champs techniques dans les tables (pour luter contre les différences d'arrondis notamment).

Fais signe si tu as besoin d'un coup de main.

[Malick]

Fais signe si tu as besoin d'un coup de main.

Merci beaucoup, je te reviens en cas de blocage.

Cordialement,
Malick

[Malick]

Salut Claude,

Le tableau de répartition des montants et des tranches sera établi au moyen d'une requête ?

La génération se fera-t-elle automatiquement après l'ajout d'un nouveau contrat ?

Merci d'avance

[ClaudeLELOUP]

Je propose qu'on commence par le début.

Peux-tu concevoir un formulaire père/fils pour encoder des contrats et ainsi garnir les tables tContrats et tTranches.

Nous disposerons ainsi d'une db de test pour mettre au point les deux processus :

- écriture (journalière) à l'anniversaire ;

- écriture au 31/12.

[Malick]

Peux-tu concevoir un formulaire père/fils pour encoder des contrats et ainsi garnir les tables tContrats et tTranches.

Entendu, je fais cela et te reviens.

Merci

[ClaudeLELOUP]

N'oublie pas : je dispose d'Access2000.

[Malick]

Pas de souci pour Access 2000, c'est intégré