XL 2016 Compte de résultat par centre analytique

[Amayelle]

Bonjour le forum,



Je me permets de vous solliciter sur une mise en place d’un compte de résultat par projet en partant des données analytiques (charges et produit) issues d’une application de gestion.

J’ai 5 feuilles

1. C’est le plan comptable

2. C’est le plan analytique, avec des CENTRE analytique.

Chaque section analytique représente un projet

3. Données N : ce sont les données de l’année en cours, ici 2013

4. Données N1 : ce sont les données des années précédentes

5. C’est le compte de résultat

Le compte de résultat se calcule en tenant en compte du plan comptable et analytique.

Je souhaiterais pouvoir sortir le compte de résultat de l’ensemble des projets, et aussi pouvoir filtrer et ne le sortir que par projet (Centre analytique).

Est possible de mettre une procédure de mise à jour des feuilles Données après les copier/coller, et de n’effectuer les calculs que si je fais par exemple ctrl+R



Merci d’avance si vous pouvez m’aider



Mes sincères salutations

 

[zebanx]

Bonjour Amayelle

Si je devais avoir une approche sur ce type de sujet, je le traiterai perso par formule en sommeprod et non en VBA (le CTRL+ R du 3 sur la sheet compte de résultat ne servirait pas).

Quelques remarques cependant :
- SOMMEPROD, sauf erreur de ma part, fonctionne mal avec des données en "N/A" (sheets "données") donc il faudrait soit veiller à ce que toute recherche trouve sa cible en colonne K et L des sheets données soit, en cas d'erreur, de les mettre à zéro.
- Comment faire fonctionner votre recherche sur le compte de résultats :
est-ce par numéro de centre (colonne A) ou par intitulé de compte analytique car il y a parfois plusieurs numéros pour un intitulé ?
- pour faciliter l'écriture des formules, il serait peut-être bien de regrouper les données dans une seule feuille en mettant, en colonne A, l'année de référence.
- On pourrait doubler les formules de SOMMEPROD pour avoir le tri soit par centre soit, si la rubrique de sélection est vide, avoir la totalité des données de compte de résultats.
La sélection sur l'intitulé analytique se ferait à partir d'une liste déroulante, donc la nécessité de prévoir une BDD pour ne pas avoir de doublons sur les centres (si l'intitulé est la clé de recherche).

Dans l'attente de votre retour, cdlt
zebanx

 

[Amayelle]

Bonjour zebank,

Merci pour votre intervention.
La recherche sur le compte de résultat s'effectue par le centre qui représente un projet englobant l'ensemble des charges et produits.
Chaque centre est unique, donc il doit s'agir d'une erreur sur ma BDD.
Je ne sais pas comment sortir un compte de résultat en regroupant l'ensemble des données sur la même feuille.
Mon besoin reste le même :
- celui de pouvoir sortir un compte de résultat de l'ensemble des projets et de pouvoir ausssi grâce à une liste déroulante sélectionner un projet(centre).
- et de pouvoir comparer 2 périodes (années).

Merci à vous
Sincères salutations

 

[Amayelle]

Bonjour zebank, le forum,
En ayant les données sur la même feuille, il faut aussi prévoir la mise à jour des données après un copier/coller par exemple.
Enfin, en espérant votre retour
Sincères salutations

 

[zebanx]

Bonjour Amayelle,

Un essai.
Quelques remarques sur les choix effectués :

- repartir sur une seule table (datas) en indiquant les années en colonne "A"

- les formules ont été changées pour afficher "0" si la rechercheV ne donnait rien. Je n'ai pas touché ces colonnes (soit I, K et L) ni cherché la raison de l'affichage de l'erreur - chacun son job -)

- une BDD a été crée pour avoir une identité unique par centre. Vous avez une mise en forme conditionnelle pratique permettant de voir immédiatement les doublons (sheets analytique)

- les calculs de P&L sont automatiques à partir des données : date (B4 et C4) et du choix du centre (ou pas - en cellule C6).
Un TCD permet de contrôler que les données correspondent.
Le TCD ne permet toutefois pas de faire le classement dans l'ordre souhaité, d'où l'usage de SOMMEPROD.
J'ai par contre rajouté une ligne pour pouvoir avoir l 'exhaustivité des rubriques (en jaune).

S'agissant des centres (cellule A6), si vous saisissez la ou les premières lettres, la liste déroulante se limite à vous restreindre les choix. Je trouve cela plus pratique surtout quand la liste est conséquente.
Si vous voulez tous les centres, ne rien mettre dans la cellule.
Les sommeprod sont pour l'instant paramétré sur un "range" de 500 lignes environ.
Vous pouvez modifier directement le chiffre dans les formules et faire des copiers-collers.
Et s'agissant des charges produits, les formules présentent un signe "-" (pour les produits) et un signe "+") pour les charges afin de n'avoir que des chiffres positifs sur le tableau de P&L.

Pour la mise à jour des données, comme indiqué sur le dernier post, c'est à vous me semble-t-il de valider préalablement qu'elles sont correctement reprises sur la table (par exemple pour savoir pourquoi il y a des "0" qui apparaissent) et de prévoir, c'est un conseil, une historisation des fichiers avant de lancer un traitement récurrent.

Cdlt
zebanx

 

[Amayelle]

Bonjour zebank,

Après un 1er travail, je reviens vers toi sur quelques points.

1. Le chiffre d'affaire de l'année en cours (cellule C9)sort en négatif, j'ai fais un test sur le centre 3 Cité bobigny en lui mettant du CA en 2013, il sort en négaitf!

2. Je peux sortir un P&L par centre, ce qui est super. Est ce possible d'avoir aussi le globalisé, c'est à dire de l'ensemble des centre.

Enfin, c'est très bien déjà, et si c'est possible de voir 2 points s'il vous plaît

Veuillez recevoir mes sincères salutations.
Bien cordialement

 

[zebanx]

Bonsoir Amayelle

Ok , merci -

Mes réponses :

Point 1 : sur le fichier des "datas" (ou données 1 ou données 2), les produits ont un signe négatif et les charges sont présentées en positif sur la colonne "solde".
Dans le fichier de départ, il n'y a pas de C.A. 2013 sur 3 Cités Bobigny.
Donc s'il faut rajouter une valeur manuellement, il faut conserver la nomenclature de départ.
Sinon veiller à ce que le solde soit une différence entre les crédits et les débits et, dans ce cas, il faudrait modifier pour toutes les formules le signe devant chaque formule.*

Point 2 :
Il faut mettre la cellule A6 à vide par l'utilisation du bouton "suppr" (C'était il me semble indiqué).
Vous pouvez à chaque fois contrôler que les données ressortent à l'identique en utilisant le TCD.

Bonne soirée
zebanx

* ce qui peut être réalisé rapidement en collant -1 dans une cellule, en te positionnant sur cette cellule puis CTRL + C et sélectionner les zones à modifier puis collage spécial et choisir formules / multiplication.

 

[Amayelle]

Bonjour zebank,
J'ai un complément quand à l'utilisation du fichier
Je souhaite faire des simulations de P&L par rapport à 2 types de scénarios
Comparer les
résultats de l'année de référence sur des prévions de P&L optimiste et pessimiste .

en ayant ajouté 2 centres analytiques OPT20140 (scénario optimiste) et PES2014 (scénario pessimiste)

Ci-joint le fichier

Merci d'avance pour ton aide
Sincères salutations

 

[zebanx]

Je souhaite faire des simulations de P&L par rapport à 2 types de scénarios
Comparer les résultats de l'année de référence sur des prévions de P&L optimiste et pessimiste .
En ayant ajouté 2 centres analytiques OPT20140 (scénario optimiste) et PES2014 (scénario pessimiste)

Bonjour Amayelle,

Il y a bien un fichier mais pour comprendre ce qui est souhaité...
Vous rajoutez 2 centres qui correspondent à Allemagne et France et vous compléter le tableau de datas correspondant à ces deux rubriques, jusqu'ici, ça va.

Après , il faudrait retrouver des données "optimistes" et "pessimistes" dans la grille P&L...euh...
Et sans un chiffre pour comprendre le résultat que vous voulez obtenir.
Si vous pouviez m'apporter plus de précision et sur le fichier directement svp, ça m'aiderait à tenter de vous répondre. Mais on va y arriver !

Bon apm.
zebanx

 

[Amayelle]

Bonjour zebank,
Dans la BDD, j'ai rajouté des prévisions de coûts et de CA pour 2014.
Le raisonnement est d'établir des prévisions de résultats 2014
optimistes par rapport à 2013, et des prévisions pessimiste 2014 par rapport à 2013.
J'entends par prévisions optimistes (centre OPT20170 ajouté dans le plan et le libellé France dans l'analytique ) , une amélioration du CA et/ou une baisse des coûts.
Même chose pour le scénario de résultats pessimiste.
Merci de ton aide
À bientôt j'espère
Sincères salutations

 

[zebanx]

Re-

J'ai copié les formules en modifiant les titres de la colonne 2014 en 2014O et 2014P.
Les résultats sont cohérents avec le TCD avec un problème sur la ligne 214 (2014 mais ni optimiste ni pessimiste).

J'espère que cela correspond à votre attente

++ zebanx