Réaffectation de budget

Bonsoir

Je m'adresse à vous sur un problème de réaffectation de budget que je n'arrive pas à résoudre.

J'ai un budget global à ne pas dépasser, pour lequel j'ai une centaine de postes de dépenses (ce nombre peut bouger).

J'alloue à chaque poste un pourcentage du budget global.

Exemple simplifié avec 4 postes pour un budget global de 1000€ :

P1 : 10% (soit 100€)

P2 : 20% (soit 200€)

P3 : 30% (soit 300€)

P4 : 40% (soit 400€)

Ce que je souhaite faire, c'est qu'en cas de dépense supérieure sur un poste donné, le surconsommé soit automatiquement déduit sur les autres postes, en tenant compte de leur répartition initiale.

Ainsi, si P1 dépense 150€, je souhaite avoir la réaffectation suivante :

P2 : 200 / (200+300+400) * (1000-150) = 188,89€

P3 : 300 / (200+300+400) * (1000-150) = 283,33€

P4 : 400 / (200+300+400) * (1000-150) = 377,78€

Autrement dit : [budgetInitialDuPoste] / [sommeDesBudgetsSupérieursAuxDépenses] * ( [budgetGlobal] - [sommeDesBudgetsInférieursAuxDépenses] )

Le problème se pose lorsque certains postes dépensent plus que les reprévisions. Exemple si P2 dépense 190€, il faut faire un nouveau calcul pour réaffecter P3 et P4 :

P3 : 300 / (300+400) * (1000-150-190) = 282,86€

P4 : 400 / (300+400) * (1000-150-190) = 377,14€

Voici un fichier avec les formules que j'utilise :

Ce que je voudrais, c'est avoir une seule colonne Réaffectation, mais je ne vois pas comment faire ces étapes successives en un seul coup

Merci d'avance pour vos idées !

Flophie

Bonsoir,

Une solution

Bonjour Raja,

Merci pour cette première réponse qui va me servir à optimiser les somme(si( sucessifs

Par contre la solution que tu proposes réaffecte le montant des dépenses, alors que je voudrais réaffecter le budget...

Une autre idée ?

Merci d'avance !

Flophie

Re,

Solution modifiée.

Bonsoir Raja,

Et encore merci de t'intéresser à mon problème !

Malheureusement ta 2ème solution ne "suffit" pas, car en C3 la réaffectation est inférieure à la dépense en C2.

C'est là toute la difficulté de mon sujet... lorsque certains postes dépensent plus que les reprévisions.

Penses-tu qu'il y aurait un moyen d'avoir une formule qui fasse n réaffectations tant qu'au moins une dépense est supérieure ?

Flophie

Re,

Je suis navré. Je n'ai plus de solutions par formules. Désolé.