Systeme par tranches, mais avec prise en compte de l'annee precedente

agnesd

XLDnaute Occasionnel
Chers tous, bonjour,

J'essaie de faire une simulation sur un systeme par tranches, avec differents paliers, mais je me demande comment faire pour que soit pris en compte ce qui avait deja ete calcule l'annee precedente.

Dans mon systeme, j'ai quatre tranches, et du coup, si pour une reference j'avais deja eu des valeurs l'annee d'avant, je devrais savoir l'annee suivante dans quelle tranche reprendre mes calculs (un peu comme le systeme de l'impot sur le revenu, mais avec un historique), bref, en annexe mon classeur, pourriez vous svp me donner un coup de main ou votre avis eclaire ?

En annexe le classeur avec mes tranches et un deuxieme onglet avec qq exemples que j'essaie de calculer. Ce sera bien plus parlant que des explications ecrites, je pense...

Merci d'avance !

Agnes
 

Fichiers joints

CISCO

XLDnaute Barbatruc
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour

Et si tu nous en disais un peu plus...

@ plus
 

agnesd

XLDnaute Occasionnel
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

ok, voici mes criteres :

Tranche 1 Tranche 2 Tranche 3 Tranche 4
Première tranche Deuxième tranche Troisième tranche Quatrième tranche
50,000 100,000 200,000 700,000
50,000 50,000 100,000 500,000
650,000 600,000 500,000 0
De 1 à 50.000 entrées De 50 000 à 100 000 entrées De 100 000 à 200 000 entrées De 200 000 à 700 000 entrées
12.00 11.00 10.00 5.00

alors disons qu'en 2014, j'ai atteint le chiffre de 700 000, comme c'est mon plafond, en 2015, je ne dois plus rien comptabiliser. Mais si en 2014, j'ai realise disons 45000, alors l'annee suivante, si j'ai realise encore 56734 entrees par exemple, cela devrait correspondre a 5000 entrees dans la premiere tranche qui va jusqu'a 50 000, valorise a 12 l'unite, moins les 45 000 de l'annee precedente, plus 51734 dans la deuxieme tranche valorisee ici a 11 l'unite. Soit un total de 629 074 pour 2015 (60 000 plus 569074).

Je cherche une formule qui me dise d'une part que si j'ai un chiffre pour 2014, cela doit etre pris en compte pour le calcul de la tranche, en etablissant dans laquelle je me trouve en 2015, en deduisant ce qui a deja ete calcule en 2014.
De 1 à 50.000 entrées ? c est a dire premiere tranche, moins le chiffre de 2014 le cas echeant
De 50 000 à 100 000 entrées ? c est a dire deuxieme tranche, moins le chiffre de 2014 le cas echeant
De 100 000 à 200 000 entrées ? c est a dire troisieme tranche, moins le chiffre de 2014 le cas echeant
De 200 000 à 700 000 entrées ? c est a dire quatrime tranche, moins le chiffre de 2014 le cas echeant
Et si c'est au dela de 700 000 entrees des 2014, alors je n'ai rien a prendre en compte car j'ai deja atteint mon plafond.
J'ai donc choisi de faire figurer les tranches dans un autre onglet pour pouvoir m'y referer.

Mais la formule est un peu alambiquee pour moi. Melanger si est vide avec autre chose ?

Merci d'avance pour tout conseil...

Agnes
 

agnesd

XLDnaute Occasionnel
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Chers tous, sinon si vous connaissez un topic similaire ou un systeme qui s'en approche (avec des tranches a prendre en compte une annee, et continuer l'annee suivante) et auquel je pourrai me referer, je vous en serais reconnaissante... Merci ! Agnes
 

agnesd

XLDnaute Occasionnel
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Rebonjour,

J'ai teste la formule suivante, mais il me manque la partie qui deduirait les entrees 2014 : =IF(B2>=Intervalle1,B2*PremiereValorisation,IF(AND(B2>0,B2<C2),B2*C2,0))

J'ai mis des noms dans le classeur pour simplifier le systeme de tranches.

Any ideas?

merci aux experts...

Agnes
 

Fichiers joints

CISCO

XLDnaute Barbatruc
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour

Cela serait encore mieux si tu écrivais les équations qui te donnent les résultats espérés dans la colonne E, pas sous la forme d'une formule Excel avec des SI(.... mais sous la forme = (56 000- 22 000) * 2,5+ 3.

@ plus
 

agnesd

XLDnaute Occasionnel
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour Cisco (et bonjour a tous) et merci pour ta suggestion que je n'arrive malheureusement pas a appliquer.

J'ai essaye avec differentes formules, cf classeur. Mais je cherche une formule qui me dise quels taux appliquer en fonction de ce qui a ete applique l'annee precedente et je seche... Rajouter des colonnes me disant les taux a appliquer ?

Merci !

Agnes
 

Fichiers joints

CISCO

XLDnaute Barbatruc
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour

Il faut bien admettre que je ne comprends pas trop ton besoin, et que par conséquent je n'ai pas trop envie d'utiliser mon dernier neurone pour chercher une formule si je ne suis pas certain de partir dans la bonne direction...

D'après ce que j'ai compris, un exemple : En 2014, entrées 20000, en 2015, entrées 75000. Quel calcul doit-on faire ?
11 * 75000
12 * 50000 + 11* (75000-50000)
12 * 50000 + 11 * (75000-20000-50000)

Si tu as d'autres propositions...

@ plus
 

agnesd

XLDnaute Occasionnel
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

bonjour et merci a toi, loin de moi l'idee de vouloir te faire perdre des neurones... C'est plutot moi qui m'arrache les cheveux, j'avais eu l'aide de certains experts l'an dernier ou l'annee d'avant, notamment Modeste, pour mettre en place un systeme de tranches avec valorisations degressives, et je souhaiterais aller plus avant en reprenant d'une annee sur l'autre le calcul.

Ainsi, si en 2014, j'avais 53 000 unites valorisees a 12 pour les 50 000 premieres, puis a 11 pour les 3000 suivantes, en 2015, je reprendrai mon systeme de tranches avec les nouvelles unites realisees, disons 700 000, alors je deduirai les 53 000 de 2014, et reprendrai ensuite avec 47 000 valorisees a 11 (ma deuxieme tranche), puis les 100 000 suivantes valorisees a 10 (ma troisieme tranche et ma troisieme valorisation) et enfin les 500 000 derniers a 5, soit un total de 4,017,000 (517,000 + 1,000,000 + 2,500,000).

Alors que si je n'avais pas eu d'unites en 2014, je commencais directement avec les premieres unites, en comptabilisant les intervalles de mes tranches, 50,000 et 50,000 et 100,000 et enfin 500,000, respectivement multipliees par 12.00, 11.00, 10.00 et 5.00, pour un total de 8,400,000 (600,000+600,000+1,200,000+6,000,000).

Bref, je seche.

En annexe mes nouvelles pietres tentatives.

Merci a vous...

Agnes

Bonjour

Il faut bien admettre que je ne comprends pas trop ton besoin, et que par conséquent je n'ai pas trop envie d'utiliser mon dernier neurone pour chercher une formule si je ne suis pas certain de partir dans la bonne direction...

D'après ce que j'ai compris, un exemple : En 2014, entrées 20000, en 2015, entrées 75000. Quel calcul doit-on faire ?
11 * 75000
12 * 50000 + 11* (75000-50000)
12 * 50000 + 11 * (75000-20000-50000)

Si tu as d'autres propositions...

@ plus
 

Fichiers joints

CISCO

XLDnaute Barbatruc
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonsoir

OK, je pense avoir compris... Je te fais ça ce soir ou demain.

@ plus
 

agnesd

XLDnaute Occasionnel
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

bonjour et merci beaucoup a toi, Cisco.

Cela a tout a fait l'air de marcher pour 2014 (notamment le plafond), sauf le fait que tu divises les valeurs par 100, j'ai rectifie en jaune, mais sinon cela semble etre la bonne piste. La question est maintenant, comment faire pour "continuer" ce calcul en 2015 en tenant compte de la tranche atteinte en 2014 ?

Bonne journee et merci d'avance a toi !

Agnes
 

Fichiers joints

CISCO

XLDnaute Barbatruc
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour

Effectivement, j'ai divisé par 100 car je pensais qu'il s'agissait de 12 %, 11 %... même si cela ne semblait pas être le cas d'après tes explications. A quoi correspondent les résultats que la formule donne ? Ce ne sont pas des primes...

Je regarde pour ce qui est de prendre en compte les résultats sur 2014 et 2015 en même temps. J'ai une ou deux idées, mais ne sais pas laquelle adopter sans partir dans des formules tordues avec de nombreux cas particuliers à contrôler.
Est-ce qu'il te faudrait une méthode pouvant prendre en compte dans le futur encore plus d'années, à savoir 2014, 2015, 2016 et ainsi de suite ?

@ plus
 

agnesd

XLDnaute Occasionnel
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour et un grand merci a toi.

Ce sont des montants fictifs dans le cadre de recherches pour un potentiel systeme de soutien.
Oui, si possible, ce serait vraiment bien de pouvoir l'appliquer a plusieurs annees (un systeme reproductible), mais tout coup de main le bienvenu, car je seche....

Bonne journee !

Agnes

Bonjour

Effectivement, j'ai divisé par 100 car je pensais qu'il s'agissait de 12 %, 11 %... même si cela ne semblait pas être le cas d'après tes explications. A quoi correspondent les résultats que la formule donne ? Ce ne sont pas des primes...

Je regarde pour ce qui est de prendre en compte les résultats sur 2014 et 2015 en même temps. J'ai une ou deux idées, mais ne sais pas laquelle adopter sans partir dans des formules tordues avec de nombreux cas particuliers à contrôler.
Est-ce qu'il te faudrait une méthode pouvant prendre en compte dans le futur encore plus d'années, à savoir 2014, 2015, 2016 et ainsi de suite ?

@ plus
 

CISCO

XLDnaute Barbatruc
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour

Dans le fichier bis en pièce jointe, on peut obtenir le total pour l'année 2014 avec une seule formule (une fois les /100 éliminés), sans colonne intermédiaire. Cela donne dans J15
Code:
SI(C15="";0;
SI(C15<=E$14;(C15-D$14)*E$13;
SI(C15<=F$14;(E$14-D$14)*E$13+(C15-E$14)*F$13;
SI(C15<=G$14;(E$14-D$14)*E$13+(F$14-E$14)*F$13+(C15-F$14)*G$13;
SI(C15<=H$14;(E$14-D$14)*E$13+(F$14-E$14)*F$13+(G$14-F$14)*G$13+(C15-G$14)*H$13;
(E$14-D$14)*E$13+(F$14-E$14)*F$13+(G$14-F$14)*G$13+(H$14-G$14)*H$13)))))
ce qui revient à faire un test par rapport à chaque tranche, et à multiplier ce qui correspond aux diverses tranches par le coefficient correspondant. On voit qu'il y a des répétitions, ce qui veut dire qu'on doit pouvoir simplifier tout cela.

On fait donc encore plus court en ne prenant que la dernière ligne de la formule précédente, et en la modifiant :
* en comparant la valeur de l'année (C15) à la valeur max de la tranche avec MIN(C15;valeur max de la tranche) par exemple MIN(C15;50000)
* et en ne prenant en compte que les termes positifs (avec un MAX(0;MIN(C15;...)), correspondant aux tranches précédentes (les MIN(C15;...) -... des tranches au dessus de la tranche correspondant à la valeur de l'année donnent des valeurs négatives, qu'il ne faut pas prendre en compte) (le premier terme est forcément positif ou nul donc pas de MAX(0; dans ce cas). Cela donne en K15
Code:
SI(C15="";0;(MIN(C15;E$14)-D$14)*E$13+MAX(0;(MIN(C15;F$14)-E$14))*F$13+MAX(0;(MIN(C15;G$14)-F$14))*G$13+MAX(0;(MIN(C15;H$14)-G$14))*H$13)
Pour que cela soit facilement utilisable avec plusieurs années, j'ai redisposé les données à partir de la ligne 25. Ainsi on a en dessous les unes des autres les données de chaque année (B32:B40 pour 2014), et les résultats espérés (B43:B51 toujours pour 2014). Pour obtenir les résultats correspondant à une nouvelle année, il suffit de tirer les formules de la ligne 43 vers la droite (une fois la plage des données de l'année remplie, bien sûr) puis vers le bas.

La formule dans B43 à la même forme que la formule ci-dessus, mais utilise d'autres cellules (contenant les valeurs 50000, 100000...). Pour pouvoir tenir compte des résultats des années précédentes, la formule utilise, non plus des MIN(C15, mais des MIN(somme(valeurs des années précédentes et de l'année en cours);... (ce qu'on écrit sous la forme SOMME($B32:B32)). De plus, à la fin de la formule, il faut aussi soustraire le total des sommes déjà versées les années précédentes, sur la même ligne (SOMME($A43:A43)). Autrement dit, dans la première partie de la formule, on fait les calculs avec le total des valeurs sur plusieurs années, et à la fin, on retire ce qui a déja été versé. Cela donne en B43
Code:
SI(B32="";0;MIN(SOMME($B32:B32);$B$26)*$C$26         'calcul pour la tranche 0 à 50000
+ MAX(0;(MIN(SOMME($B32:B32);$B$27)-$B$26))*$C$27    'calcul pour la tranche 50000 à 100000
+ MAX(0;(MIN(SOMME($B32:B32);$B$28)-$B$27))*$C$28    'calcul pour la tranche 100000 à 200000
+ MAX(0;(MIN(SOMME($B32:B32);$B$29)-$B$28))*$C$29    'calcul pour la tranche 100000 à 700000
-SOMME($A43:A43))                'soustraction des sommes versées les années précédentes
Dans le fichier ter, il y a les mêmes relations, mais j'ai supprimé les lignes 12 à 23 correspondant aux calculs faits initialement uniquement pour l'année 2014.

@ plus

P.S : Pour voir les résultats intermédiaires, faire, après avoir sélectionnée la cellule contenant la formule à étudier, --> Formule --> Evaluation des formules.
 

Fichiers joints

Dernière édition:

agnesd

XLDnaute Occasionnel
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

un enorme merci, je vais passer la journee de demain en etudiant tout ca, je te remercie enormement pour ton aide ! Si j'ai des questions, je ne manquerai pas de te contacter...

Merci a toi et aux experts du site, vous m'aidez a chaque fois a progresser (et m'aidez tout court aussi !),

Agnes
 

agnesd

XLDnaute Occasionnel
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour Cisco et un grand, grand merci !

Voici mes premieres questions :

- dans le fichier ter, en B et C26, j'ai deux montants, mais en fait une fois mon plafond de 700 000 unites atteint une annee, le calcul devrait s'arreter, aurais tu stp une idee pour que la formule prenne cela en compte ?

Dois je rajouter par exemple un =SI(C20>B17,0,D20) quelque part dans la formule que tu proposes et le tirer d'annee en annee ?

- dans le fichier bis, comment eviter les valeurs negatives pour les annees ou je n'avais aucune unite enregistree ?

je vais reregarder comment tu as procede dans le fichier bis et reviendrai surement vers toi avec d'autres questions...

Merci beaucoup d'avance a toi et excellente journee, ainsi qu'aux personnes presentes sur le forum !

Agnes
 
Dernière édition:

CISCO

XLDnaute Barbatruc
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour

- dans le fichier ter, en B et C26, j'ai deux montants, mais en fait une fois mon plafond de 700 000 unités atteint une année, le calcul devrait s'arrêter, aurais tu stp une idée pour que la formule prenne cela en compte ?
En B37, tu as un calcul pour des entrées égales à 743657 qui donne 50 000*12+ (100 000-50 000)*11+(200 000-100 000)*10+(700 000-200 000)*5 - 0 = 4 650 000. Les entrées en plus de 700 000 ne sont pas prises en compte.
En C37, le calcul correspond à 743657+27 entrées et donne 4 650 000 - 4 650 000 = 0. Comme le plafond de 700 000 a été dépassé, toutes les autres valeurs, pour cette ligne, seront égales à 0 car le calcul donnera toujours 4 650 000 - 4 650 000 .
Si tu veux, on peut faire disparaitre ces 0 de deux manières différentes :
* Soit avec un format personnalisé qui "efface" tous les 0 du tableau
* Soit avec une condition SI en plus dans la formule.
Qu'est-ce qui t'arrange le plus ?

- dans le fichier bis, comment éviter les valeurs négatives pour les années ou je n'avais aucune unité enregistrée ?
Il y avait une parenthèse mal placée à la fin des formules dans les fichiers bis et ter, dans la première version envoyée avec le post #15 à 14 h 05.
Par ex, dans le bis, dans B43, il faut =SI(B32="";0;;MIN.........*$C$29-SOMME($A43:A43)) au lieu de =SI(B32="";0;MIN.........*$C$29)-SOMME($A43:A43) (la parenthèse rouge droite doit être à la fin de la formule, et pas avant le - SOMME). Avec la première version, le SOMME($A43:A43) est soustrait même pour les cellules vides (puisqu'il n'est pas inclus dans le SI(....)). Ce qui donne dans C49 SI(C38="";0;MIN....)-SOMME($A49:B49) = 0-SOMME($A49:B49) qui est négatif.
Mais j'avais corrigé les pièces jointes dès 16 h 55 . Si tu ne l'as pas déjà fait, retélécharge la dernière version. Si le problème persiste, explique le moi.

@ plus
 
Dernière édition:

agnesd

XLDnaute Occasionnel
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour et une nouvelle fois un grand merci, suis en deplacement, n'avais pas eu la nouvelle version. Vais regarder les nouveaux fichiers et faire de mon mieux pour comprendre tout ton cheminement (pas forcement evident au premier regard pour moi...), te dirai !

Merci encore,

Agnes
 

CISCO

XLDnaute Barbatruc
Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour

Pour t'aider un peu à comprendre la formule...

Imaginons que la somme des entrées sur les années précédentes et l'année en cours soit de 120 000, donc qu'elle appartient à la tranche 3 allant de 100 000 (= max tranche 2) à 200 000 (= max tranche 3).

Uniquement pour cette tranche 3, le calcul est (120 000 - 100 000) * 10, ce qu'on peut écrire sous la forme (sommeentrées - maxtranche2) * coef3.

Si la somme des entrées sur... avait été supérieure à 200 000, le calcul pour cette tranche 3 aurait été (200 000 - 100 000) *10 ce qu'on peut écrire sous la forme (maxtranche3 - maxtranche2) * coef3.

Pour prendre en compte les deux possibilités précédentes pour la tranche 3 avec une seule formule, on peut faire avec (MIN(sommeentrées;maxtranche3) - maxtranche2) * coef3.

Si la somme des entrées n'avait été que de 90 000, donc avait appartenu à la tranche 2, cette formule pour la tranche 3 aurait donné un nombre négatif : (MIN(sommeentrées;maxtranche3) - maxtranche2) * coef3 = (sommeentrées - maxtranche2) * coef3 = (90 000 - 100 000) * 10 < 0. Or, il ne fallait pas faire de calcul dans cette tranche 3 dans ce cas (puisque 90 000 appartient à la tranche 2), ou trouver 0 pour cette tranche 3.

Pour ne pas prendre en compte cette valeur négative, on transforme la formule précédente pour la tranche 3 sous la forme (MAX(0;MIN(sommeentrées;maxtranche3) - maxtranche2)) * coef3.

En fait, il faut faire cela pour chaque tranche, ce qui donne
(MAX(0;MIN(sommeentrées;maxtranche1) - 0)) * coef1
+(MAX(0;MIN(sommeentrées;maxtranche2) - maxtranche1)) * coef2
+(MAX(0;MIN(sommeentrées;maxtranche3) - maxtranche2)) * coef3
+(MAX(0;MIN(sommeentrées;maxtranche4) - maxtranche3)) * coef4.

Après, il ne reste plus qu'à rajouter une condition SI(... pour ne pas faire de calcul du tout si il ni a pas eu d'entrées pendant l'année en cours, et à soustraire les versements déjà effectués les années précédentes, avec - SOMME(....

J'ai mis les mêmes noms dans la pièce jointe. C'est le même principe que dans le fichier ter précédent, mais avec des noms. Cf. les noms dans le gestionnaire de noms.

@ plus
 

Fichiers joints

Dernière édition:

Discussions similaires


Haut Bas