Affecter une dépense future au budget annuel correspondant

Question

Je suis en train de construire un outil sous excel qui permettra de gérer les primes mobilité dans une entreprise. Ces primes sont versées tous les mois pendant 3 ans (1 montant fixe par an) : par exemple la 1ere année, le salarié touche 500€ par mois, la 2nde année 300€ par mois et la 3ème année 150€ par mois. Ces primes pouvant démarrer n’importe quel mois, les versements peuvent donc s’effectuer sur 4 années civiles. Un des objectifs de cet outil est de définir le budget prévisionnel alloué aux primes mobilité année par année. Dans la base de données, une ligne correspond à un salarié. Je voudrais que les différentes sommes qui vont lui être payées soient automatiquement affectées aux budgets des années correspondantes. Dans l’exemple joint, je sais par exemple dire que j’ai déjà 17 860€ à prévoir sur mon budget 2012. N’étant pas informaticienne, j’ai commencé à développer tout un tas de calculs intermédiaires dans ma base de données, en clair, je suis en train de faire une usine à gaz et je me dis que vous avez peut-être une solution plus « simple », d’autant que mon outil n’est pas pérenne dans le temps. Merci d'avance à tous ! Carole...

dachevid · Answer

Voir dans le fichier joint, j'ai modifié rapidement quelques formules mais ton tableau est déjà bien exploitable, les SI font bien leur travail, c'est vrai que par contre si on doit le continuer pendant 40 jusqu'à la retraite à 75 ans il finira par ressembler à une grosse usine à gaz. A+...

dachevid · Answer

Je vais y jeter un oeil plus approfondi.

As-tu remarqué que tes calculs en colonne N sont faux, tu as eu de la chance avec la première formule car il y a 6 mois sur chaque année mais ensuite tu as inversé les durées, résultats on se retrouve ensuite avec 8 et 4 et la deuxième année se retrouve avec 8+8 mois au lieu de 8+4.

Tu dois faire E*J+D*K et tu as E*K+D*J.

Regarde tes sommes en F et P elles sont différentes.

A+

Edit :

J'ai modifié la formule :

=SI($H3=N$2;$O3;SI($H3=O$2;$N3;SI($H3=P$2;$M3;SI($H3=Q$2;$L3;""))))

A recopier sur toutes les lignes et colonnes.

A terme on doit même pouvoir se passer des colonnes HIJK mais on peut juste les masquer, il faudrait mettre leurs formules dans les calculs en colonnes LMNO.

32exemple-mobilite.zip (10.44 Ko)

dachevid · Answer

Bien vu, je suis effectivement allé un peu vite et suis resté sur "4 ans". Du coup je passé forcément sur une année bissextile mais sur 3 ans ce n'est pas toujours vrai, je dois donc parfois avancer de 1094 (3*365 -1) parfois de 1095 (1*366+2*365 -1).

Avec ça en G 3 ça va marcher :

=DATE(ANNEE(B3)+3;MOIS(B3);JOUR(B3))-1

A+

dachevid · Answer

Re, Formule en Q3 : =SI($H3=N$2;$O3;SI($H3=O$2;$N3;SI($H3=P$2;$M3;SI($H3=Q$2;$L3;"")))) Formule en S10 : =SI($H10=P$2;$O10;SI($H10=Q$2;$N10;SI($H10=R$2;$M10;SI($H10=S$2;$L10;"")))) Si la valeur en colonne H sur la ligne en cours (c'est l'année de début en H3, H4...H10) est égale à la valeur 3 colonnes à gauche et en ligne 2 (donc P2 pour S10 et N2 pour H3, P2, N2 contiennent aussi des années) alors j'affiche la valeur de la colonne O sur la ligne en cours, Sinon si la valeur en colonne H sur la ligne en cours est égale à la valeur 2 colonnes à gauche et en ligne alors j'affiche la valeur de la colonne N sur la ligne en cours, Sinon si la valeur en colonne H sur la ligne en cours est égale à la valeur 1 colonne à gauche et en ligne 2 alors j'affiche la valeur de la colonne M sur la ligne en cours, Sinon si la valeur en colonne H sur la ligne en cours est égale à la valeur sur la colonne en cours et en ligne 2 alors j'affiche la valeur de la colonne L sur la ligne en cours, Sinon je n'affiche rien "" car on n'est pas sur l'une des trois ou quatre années à traiter. Ce n'est pas forcément plus clair je l'avoue mais c'est la transcription mot à mot de la formule, désolé ! A+...