XL 2013 Calcul de stock restant et coûts

[Cra_cotte]

Bonjour,

Après avoir longuement fouillé sur le forum, sans réussir à adapter les cas déjà traités, je me décide à poser la question.
Je vous remercie par avance de l'aide que vous pourrez m'apporter.

Je dois créer un document me permettant de calculer les coûts de stockage de ma matière première, jour par jour.
Je stock du cacao, dans un magasin A.
Je paye des frais à compter du 16ème jour de stockage, à hauteur de 0.05€ la tonne par jour de stockage supplémentaire.
Nous considérons que la première entrée est la première sortie, c'est la règle pour le payment des frais, pas le choix.
Exemple :
Je rentre en magasin le 01/08 : 500 T
Je sors du magasin le 03/08 : 300 T
Je rentre en magasin le 05/08 : 1000 T
Le 16/08 : Je paye (500T-300T)*0.05€
Du 17/08 au 20/08 : Je paye (500T-300T)*0.05€
Le 21/08 : Je paye (500T-300T)*0.05€ + 1000*0.05€

Se pose le problème du first in fist out qui me semble difficile à modéliser par des formules Excel.
Et le problème de la date.
Je ne maîtrise pas Access. Je pensais à faire une application VBA pour automatiser le calcul mais je ne vois pas comment indexer les sorties afin de "vider" les entrées les plus vieilles et basculer sur les suivantes.

A noter qu'il y a un seul produit, un seul magasin et un seul coût de stockage.
Je n'ai pour le moment aucun tableau de prêt.
Si quelqu'un à un modèle qui pourrait correspondre à cela, une idée de comment procéder et quel support serait le mieux adapté, je prends !
Ne pas hésiter s'il y a des questions supplémentaires.

Par avance merci !
Bonne fin de journée,

 

[CISCO]

Bonjour

J'avais déjà "planché" sur ce type de problème pour des actions en bourse. Je vais essayer de te retrouver ça pour l'adapter à ton problème, mais pas tout de suite : Je n'ai pas le temps pour cela en ce moment...

@ plus

 

[Cra_cotte]

Bonjour,

Ce serait super.
Merci beaucoup !

Bonne journée,

 

[CISCO]

Bonsoir

Deux questions, STP :

1)

Bonjour,
Exemple :
Je rentre en magasin le 01/08 : 500 T
Je sors du magasin le 03/08 : 300 T
Je rentre en magasin le 05/08 : 1000 T
Le 16/08 : Je paye (500T-300T)*0.05€
Du 17/08 au 20/08 : Je paye (500T-300T)*0.05€
Le 21/08 : Je paye (500T-300T)*0.05€ + 1000*0.05€

Bonne fin de journée,

Ne serait-ce pas plutôt
le 19/08 , (500 - 300) * 4* 0,05
Le 20, (500 -300) * 0,05 + 1000 * 0,05 ?

2) Ne pourrais tu nous donner un exemple plus long, avec plus d'entrées et de sorties, et avec les équations donnant les sommes à verser pour les dates correspondantes ?

Pour le moment, j'ai fait un tableau calculant automatiquement les quantités en stock, en fonction des entrées et des sorties. Cf. en pièce jointe.

@ plus

 

[CISCO]

Bonjour

En listant tous les jours, le 1er, le 2, le 3 août et ainsi de suite, c'est beaucoup plus facile...

Le fichier FifoCra_cotte6 contient les formules écrites en dur. Si tu as besoin de plus de dates (plus bas que la ligne 21 ou que la ligne 16, ou plus à droite que la colonne AH), il faudra modifier les formules (par exemple, dans H2, tu n'auras plus SOMMEPROD(($G$5:$G$16+15<=H1)*H5:H16)*0,05 mais SOMMEPROD(($G$5:$G$30+15<=H1)*H5:H30)*0,05 ) et les tirer vers le bas et/ou vers la droite.

Comme cela n'est pas très pratique, je te propose un autre fichier, FifoCra_cotte7, qui utilise des plages dynamiques définies dans le gestionnaire de noms. Dans ce cas, tu n'as qu'à tirer les formules vers le bas et/ou vers la droite pour agrandir les trois tableaux A1:B21, C1: D21 et G1:AH16. Plus besoin de modifier les formules.

Je chercherais plus tard une autre possibilité donnant un tableau plus concis, sans avoir besoin de lister toutes les dates, le 1er, le 2, le 3 août, et ainsi de suite, mais uniquement, les dates des entrées, des sorties, et celles où il faut payer quelque chose.

@ plus