Formules de revision de prix

[cotisationsacalculer]

Bonjour,
je suis dans une impasse et ai besoin de vos lumières...
Je souhaite comparer plusieurs formules de révision de prix que j'ai dans différents contrats de vente et suivre leurs évolutions dans le temps.
Les formules d'actualisation de prix que j'ai comportent entre 2 et 4 indices, la formule type étant :
Pn = Po x [a + b*(I1n/I1o) + c*(I2n/I2o) + d*(I3n/I3o)]
Pn, I1n, I2n, I3n Prix et derniers indices connus à date de révision
Po, I1o, I2o, I3o Prix et derniers indices connus à la date de signature du contrat
a = facteur partie fixe (pas toujours présent) ; b= facteur indice I1 ; c = facteur indice I2; d = facteur indice I3 ;
e = facteur indice I4
I1 = premier indice ; I2 = deuxième indice ; I3 = troisième indice ; I4 = quatrième indice

J'ai fait un onglet avec mes différentes formules et un autre onglet avec la valeur de mes indices sur les mois précédents...mais je bloque complètement pour savoir ensuite comment "injecter" ces valeurs dans mes différentes formules...et avoir par exemple un graphique d'évolution de chaque formules avec le prix révisé. Je me demande si je présente les données comme il faut.

Auriez-vous des solutions pour moi ?
Merci à tous, je vous joins mon fichier pour illustrer le tout.

Véro

 

[Ninter]

Re : Formules de revision de prix

Bonjour cotisationsacalculer,
je n'ai pas très bien compris ta préoccupation...
Si tu peux nous expliquer les cellules G,H,I,J,K (qui contiennent des caractères alphanumériques et donc pas de données)et dans quelles onglets et cellules notamment tu veux calculer tes formules.
Merci

Ninter

 

[Victor21]

Re : Formules de revision de prix

Bonsoir cotisationscalculer, Ninter .

Pas tout compris non plus. Pas vu de Po pour faire des tests.

Quoi qu'il en soit, et en attendant des infos complémentaires, j'ai modifié la BDD des indices pour qu'elle soit utilisable dans les futures formules : voir PJ

 

[Victor21]

Re : Formules de revision de prix

Re,

Un essai en pj.
Les indices dans la feuille Formule doivent impérativement avoir la même orthographe que ceux de la BDD indices. Pour ce faire, j'ai mis une validation par liste.
La formule est à contrôler, et notamment l'ordre des opérations. De plus I4 n'y figure pas...
J'ai considéré que lorsque l'indice nouveau n'existait pas, le rapport In/Io était égal à 1.
Dans la BDD indices, vous devrez peut-être recopier vers le bas les indices qui ne sont pas mensuels, pour ne pas avoir de vide. C'est vous qui savez...

Mode d'emploi :
- Choisir la formule (1 à 7) à appliquer en B11, puis saisir
- le montant de Po en B12,
- la date de signature du contrat en B13,
- la date de révision en B14.
Vous obtenez Pn en H11.

 

[Victor21]

Re : Formules de revision de prix

Re,...

... Si vous êtes encore là
En B11 : le n° de la formule choisie
En B12 : la valeur de Po
En B13 : 0 (Date de signature sous le format aaaamm)
En B14 : n (Date de révision sous le format aaaamm)
La formule de Pn développée :

 =B12*((DECALER(B$1;$B11;)+DECALER(C$1;$B11;)*SI(SIERREUR(INDEX('BDD indices'!$C$2:$L$109;EQUIV('BDD formules'!$B$13;'BDD indices'!$C$2:$C$109;0);EQUIV(DECALER(G$1;$B$11;0);'BDD indices'!$C$1:$L$1;0));1);SIERREUR(INDEX('BDD indices'!$C$2:$L$109;EQUIV('BDD formules'!$B$14;'BDD indices'!$C$2:$C$109;0);EQUIV(DECALER(G$1;$B$11;0);'BDD indices'!$C$1:$L$1;0));1)/SIERREUR(INDEX('BDD indices'!$C$2:$L$109;EQUIV('BDD formules'!$B$13;'BDD indices'!$C$2:$C$109;0);EQUIV(DECALER(G$1;$B$11;0);'BDD indices'!$C$1:$L$1;0));1);1))+(DECALER(D$1;$B11;)*SI(SIERREUR(INDEX('BDD indices'!$C$2:$L$109;EQUIV('BDD formules'!$B$13;'BDD indices'!$C$2:$C$109;0);EQUIV(DECALER(H$1;$B$11;0);'BDD indices'!$C$1:$L$1;0));1);SIERREUR(INDEX('BDD indices'!$C$2:$L$109;EQUIV('BDD formules'!$B$14;'BDD indices'!$C$2:$C$109;0);EQUIV(DECALER(H$1;$B$11;0);'BDD indices'!$C$1:$L$1;0));1)/SIERREUR(INDEX('BDD indices'!$C$2:$L$109;EQUIV('BDD formules'!$B$13;'BDD indices'!$C$2:$C$109;0);EQUIV(DECALER(H$1;$B$11;0);'BDD indices'!$C$1:$L$1;0));1);1))+(DECALER(E$1;$B11;)*SI(SIERREUR(INDEX('BDD indices'!$C$2:$L$109;EQUIV('BDD formules'!$B$13;'BDD indices'!$C$2:$C$109;0);EQUIV(DECALER(I$1;$B$11;0);'BDD indices'!$C$1:$L$1;0));1);SIERREUR(INDEX('BDD indices'!$C$2:$L$109;EQUIV('BDD formules'!$B$14;'BDD indices'!$C$2:$C$109;0);EQUIV(DECALER(I$1;$B$11;0);'BDD indices'!$C$1:$L$1;0));1)/SIERREUR(INDEX('BDD indices'!$C$2:$L$109;EQUIV('BDD formules'!$B$13;'BDD indices'!$C$2:$C$109;0);EQUIV(DECALER(I$1;$B$11;0);'BDD indices'!$C$1:$L$1;0));1);1)))

 

[cotisationsacalculer]

Re : Formules de revision de prix

Bonjour et merci beaucoup d'avoir pris le temps de comprendre mes difficultés.
Je suis plus qu'emballée par la façon dont vous avez traité mon fichier.
Je ne connaissais pas les fonction "décaler" ni "index"...
Grâce à vous le calcul d'actualisation du prix se fait automatiquement entre deux dates et par formule, un grand merci.
J'aimerais encore quelques conseils. Le calcul du prix actualisé est la première partie de mon travail, la seconde est d'avoir une sortie graphique, évolution sur plusieurs mois du prix pour chaque formule. Je ne sais pas comment procéder à partir des éléments que vous avez calculés pour faire ce suivi (graphique avec les dates - mois et années - en abscisse et les prix révisés en ordonnées).
Auriez-vous des conseils ?
Merci à vous,
Véro

 

[Victor21]

Re : Formules de revision de prix

Re,

Pas spécialiste des graphiques, je vous propose quand même quelque chose.
J'ai complété la table des indices de la manière suivante :
Date antérieure à leur création : indice à 100
Dates manquantes (indice trimestriel) : précédent indice connu.

PJ à contrôler avec soin si vous devez l'utiliser...

 

[cotisationsacalculer]

Re : Formules de revision de prix

Re,
merci encore. J'étais justement en train de tester ce type de formule mais je n'arrivais pas à intégrer la formule "DECALER" et n'avais pas mis de condition "SI" lorsqu'il n'y a pas tous les indices...ce qui me donnais des erreurs évidemment...
J'ai décalé quelques colonnes dans ma BDD des formules mais voici où j'en étais :
=B$1*(('BDD formules'!$F$2+('BDD formules'!$G$2*(SIERREUR(INDEX('BDD indices'!$C$2:$P$169;EQUIV($A6;'BDD indices'!$C$2:$C$169;0);EQUIV('BDD formules'!$K$2;'BDD indices'!$C$1:$P$1;0));1)/SIERREUR(INDEX('BDD indices'!$C$2:$P$169;EQUIV(B$2;'BDD indices'!$C$2:$C$169;0);EQUIV('BDD formules'!$K$2;'BDD indices'!$C$1:$P$1;0));1)))+('BDD formules'!$H$2*(SIERREUR(INDEX('BDD indices'!$C$2:$P$169;EQUIV($A6;'BDD indices'!$C$2:$C$169;0);EQUIV('BDD formules'!$L$2;'BDD indices'!$C$1:$P$1;0));1)/SIERREUR(INDEX('BDD indices'!$C$2:$P$169;EQUIV(B$2;'BDD indices'!$C$2:$C$169;0);EQUIV('BDD formules'!$L$2;'BDD indices'!$C$1:$P$1;0));1)))+('BDD formules'!$I$2*(SIERREUR(INDEX('BDD indices'!$C$2:$P$169;EQUIV($A6;'BDD indices'!$C$2:$C$169;0);EQUIV('BDD formules'!$M$2;'BDD indices'!$C$1:$P$1;0));1)/SIERREUR(INDEX('BDD indices'!$C$2:$P$169;EQUIV(B$2;'BDD indices'!$C$2:$C$169;0);EQUIV('BDD formules'!$M$2;'BDD indices'!$C$1:$P$1;0));1)))+('BDD formules'!$J$2*(SIERREUR(INDEX('BDD indices'!$C$2:$P$169;EQUIV($A6;'BDD indices'!$C$2:$C$169;0);EQUIV('BDD formules'!$N$2;'BDD indices'!$C$1:$P$1;0));1)/SIERREUR(INDEX('BDD indices'!$C$2:$P$169;EQUIV(B$2;'BDD indices'!$C$2:$C$169;0);EQUIV('BDD formules'!$N$2;'BDD indices'!$C$1:$P$1;0));1)))))

je vais donc bien regarder ce que vous avez fait...et compléter ma formule qui commence à être très longue !
Merci pour tout...

 

[cotisationsacalculer]

Re : Formules de revision de prix

C'est bon, j'ai complété la formule et elle fonctionne !
Encore merci de votre curiosité, j'ai de quoi travailler maintenant...
Bonne soirée