Aide fichier rentabilite chantier

Bonjour,

Ma mère m'a demandé un peu d'aide pour pouvoir calculer la rentabilité des chantiers effectués.

Pour résumé la situation :

Il y a plusieurs employés qui vont travailler sur plusieurs chantiers.

On connait le prix /j de chaque employé ainsi que le montant de la facture de chaque chantier.

L'objectif est donc de savoir si tel chantier a été rentable ou non.

J'ai tenté de l'aider en mettant au point le fichier excel joint. Je ne suis absolument pas un pro d'excel et loin de la même je suis un utilisateur basique.

Je vais tout de même prendre le soin d'expliquer comment marche ce fichier excel :

Onglet variable :

La colonne A contient le nom de chaque employé. Si un nouvel employé est embauché, c'est à cet endroit qu'il faut le renseigner.

La colonne B contient le salaire de l'employé à la journée.

La colonne D contient le nom du chantier. Il est nécessaire d'ajouter chaque nouveau chantier à cet endroit.

La colonne E contient au montant facturé pour le chantier.

Cet onglet est à remplir lorsqu'un nouvel employé est présent ou lors d'un nouveau chantier.

Onglet Pointage :

La colonne A contient le nom de l'employé

La colonne B contient le chantier sur lequel il a travaillé

La colonne C contient la date à laquelle il à travaillé

La colonne D reprend le salaire de la personne (cela me sert dans un autre onglet ensuite, je n'ai pas su faire sans).

Cet onglet est à remplir chaque jour pour indiquer que telle personne à travailler sur tel chantier tel jour.

Onglet Bilan:

Cet onglet permet de récupérer chaque chantier et calcule le cout des employé en fonction du nombre de jour que chacun y a travaillé et de son taux horraire. Il est nécessaire de saisir manuellement le cout en essence et du péage.

Le calcul du bénéfice est ensuite effectué par chantier.

Onglet Resume Chantier :

C'est un tableau croisé dynamique qui permet de savoir le nombre de jour comptabilisé par chantié par employé avec la possibilité de filtrer selon des dates.

Je solicite votre aide pour :

• Vérifier que ce que j'ai fais semble correcte et viable dans le temps.
• N'y aurait t'il pas plus simple ?
• Intégrer le calcul automatique des frais d'essences et de péage. L'objectif ici étant de diviser la somme payée dans le mois (ex 1000 euros d'essences) et de la diviser par le nombre de chantier qu'il y a eu dans le mois (ex 8) et donc avoir une somme de 125€ renseignée dans le champ essence de l'onglet bilan de façon automatique pour les 8 chantiers en question.
• Existe t'il un moyen de générer de façon automatique des camamberts par chantier pour connaitre le % de bénéfice, le % dépensé en payant les employés et le % essence et le % péage.

PS : Toute amélioration, modification est bien évidement la bienvenue. Ce fichier excel étant voué à être utilisé dans le temps.

Merci par avance pour votre aide !

Bonjour

Je ne construirais pas comme cela.

Un Tableau des employés :

Nom, matricule et coût journalier.

Peut-être prévoir aussi des dates de début et fin de coût car le salaire peut évoluer au fil du temps.

Un tableau des affectations :

Matricule, Chantier, Date début, Date fin

Un tableau des coûts chantiers :

Nom chantier, Type coût (essence, péage...), Montant, Date

Un tableau des factures chantiers :

Nom chantier, Date, Montant

Tous ces tableaux doivent être en tableaux structurés sans lignes vides

Si tu as bien le version 365 (ce que laisse supposer 1911 qui n'est pas une version mais une variante) on peut tout calculer par PowerQuery (intégré) sans RECHERCHEV ni nécessité d'ajouter le coît du personnel affecté dans le tableau des coûts chantiers puisqu'il ressortira automatiquement du tableau d'affectations

Bonjour Matteu, bonjour à tous,

Dans le genre, je peux proposer ce fichier que j'avais développé pour une demande similaire.

Multi-salariés, multi-chantiers, multi-horaires, calcul du prix de la main-d’œuvre, ...

Comme c'est assez complet, il vaut mieux lire la notice (dans le zip) pour l'utiliser.

La saisie des heures réalisées par chaque salarié se fait pas sélection dans la ligne correspondante => voir image animée

Il me semble que ça pourrait répondre au besoin

Pierre

Bonjour

Ci joint une proposition premier jet à tester:

• transformation des tableau en" Tableaux" , avec définition d'étiquettes pour les noms des colonnes
• Feuille variable appelée Table avec les listes d’agents/Salaire et de chantiers, également transformés en tableaux avec étiquettes en entête cela permet de mieux repérer les zones dans les formules, ces dernières se mettant automatiquement à jour en cas de rajout ou suppression ( attention ne supprimer que les cellules composant les tableaux pas les lignes entières !
• Utilisation des listes agents et chantier pour saisir dans pointage
• pour rajouter des infos ( lignes ou cellules dans un tableau) cliquer sur la dernière cellule remplie et touche Tab -->, une ligne se rajoute avec conservation du format et des formules précédentes

A regarder

je viens de voir le fichier de pierrep56 qui va plus loin

Cordialement

FINDRH

Tout d'abord, merci beaucoup pour vos retours si rapide.

@78chris :

Le problème de ton système c'est qu'il faut connaitre à l'avance les données (date de début et de fin) sauf qu'elle ne les maîtrise pas pour tout. L'exemple étant avec les frais d'essence et de péage où il ne lui est pas possible de savoir combien d'essence a été dépensé pour tel ou tel chantier car c'est une facture au mois qu'elle reçoit et qu'il y aura plusieurs chantier par mois.

Pour les autres tableau, je pense que c'est OK mais comment faire les liens ensuite ?

Je ne cherche pas à ce qu'on fasse le travail à ma place car je connais très bien le principe des forum (étant un membre de la communauté technet de microsoft) par contre, j'ai du mal à voir la finalité de ces tableau ensuite.

Je connais pas du tout powerquerry, je vais regarder ca

@pierrep56

je teste ca et je fais un retour.

@findrh

je vais quand même jetter un coup d'oeil à ton fichier et faire un retour.

RE

Je parlais des dates de début et de fin d'affectation pour le mois : au pire on peut prendre comme fin la date de calcul...

Si tout est flou, je ne vois pas comment tu peux calculer la rentabilité chantier par chantier.

Il faut à minima des quotes parts

Avant de mettre en équation, Excel ou autre, je pense qu'il faut affiner l'analyse...

Ouaou, je viens de voir une presentation de power query via un lien youtube super bien fait et en effet, ç'est super intéressant!

Comme j'avais indiqué, mon niveau excel s'arrête à faire des multiplication / addition de cellules entres elles...

Ca me fait passer à des tables SQL un peu partout, on fait les jointures pour lier tout ce qui faut et ensuite on fait un tableau croisée dynamique pour avoir ce qu'on veut.

Sur le principe ca me plait beaucoup. Faut que je teste ta technique voir si c'est facilement utilisable ou non.

Je vais tester les deux autres fichiers avant ca

L'aspect péage + essence restera flou malheureusement c'est la raison pour laquelle le calcul sera fait via un système de "moyenne".

Pour faire simple, jusqu'à aujourd'hui, aucun moyen n'était en place pour calculer la rentabilité de l'activité et c'est justement parce qu'elle pense que bien souvent ce n'est pas rentable qu'elle souhaite mettre en place cela.

La seule information dont elle disposera tous les jours sera :

Tel employé a travaillé sur tel chantier aujourd'hui. Elle aura donc la liste de tous les employé et leur lieu de travail par jour.

Pour l'essence + le péage, elle a la facture mensuelle de vinci / relevé bancaire et si elle à fait 10 chantier dans le mois elle divisera par 10 par exemple. Cela ne permettra pas d'être au plus juste par chantier, on en est tout à fait conscient, mais au moins la donnée sera prise en compte. Il est question de plusieurs milliers de km par mois.

@PierreP56

Je viens de tester le fichier. C'est facile à utiliser et ergonomique mais ne répond pas réellement au besoin de ma mère. C'est très bien pour voir quelle est la durée de travail de chaque employé et le cout associé mais dans son cas l'accent souhaite être mis sur la partie "bénéfice" et non pas "planning". Joli travail en tous cas !

@findRH

Il est très similaire au mien mais certainement plus propre d'un point de vue "excel" je suppose !

=> Je pense que je vais tenter la première proposition qui m'a été faite avec plusieurs tableau et ensuite rassembler tout ça et je reviens vers vous pour voir ce que ça donne.

RE

On doit pouvoir proratiser l'essence selon la distance du chantier.

Pour le péage on doit aussi pouvoir repérer les trajets

Pour PowerQuery si les tables sont bien structurées c'est simple et puissant.

Je ne suis pas rentré dans les détails, mais voici pourquoi c'est compliqué la partie essence + péage :

Mon beau père (c'est lui l'artisan, ma mère fait la compta) habite à marseillan.

Le travail est souvent aux alentours de Toulouse.

Les employé habitent sur Fronton pour la plus part.

Parfois mon beau père descend pour fournir des devis, ou vérifier que tout se passe bien sur les chantiers.

Parfois il descend et va chercher les employés pour les amener sur le lieu de travail.

Il paye l'essence aux employés ainsi que le péage.

La tracabilité est compliqué tout simplement parce que les jours ne se ressemblent pas... Et qu'il n'y a pas de remonté faite au jour le jour de ses infos. Ce n'est qu'à la fin du mois / de la semaine via les tickets de la station essence que ma mère va devoir opérer au mieux... Donc malheureusement cet aspet la sera toujours "approximatif" aujourd'hui mais ce sera toujours mieux que rien.

Je vais faire exactement ce que tu m'as dis dans ton premier message pour tester powerquery

Bon bé finalement, j'ai réussi à faire les 4 tableaux. J'ai enlevé quelque champs mais je ne parviens pas à conceptualiser ce que j'ai besoin ensuite...

J'ai bien fais une connexion par tableau mais je ne comprends pas quoi faire ensuite.

L'objectif étant d'avoir la m^me chose que ce que j'avais dans mon fichier à moi soit :

• Combien de jour chaque employés à travaillé par chantier
• Quelle est le bénéfice par chantier
• Quelle est la répartition des charges sur le chantier (diagramme camembert)

Voilà ou j'en suis pour l'instant dans mon fichier en PJ (beaucoup de choses ne vont pas ^^)

Bon bé j'ai réussi à avancer un peu.

J'ai bien les tableaux que je souhaites :

le nombre de jour par chantier par personnes.

le total des salaires

le total des charges de transport

Par contre, maintenant il faut que j'arrive à lier tout ça pour avoir un tableau ou j'aurai par chantier le montant facturé - le bénéfice - le montant des charges par catégories (essence, péages, salaire).

Sur internet, les exemples que je trouve ont déjà dans le même tableau les données charges + produit alors que dans mon cas, elles sont dans 3 tableaux distincts (charge employé, charge transport, facture) et je ne vois pas comment les regrouper :/

Re

J'ai un peu modifié puis nommé les tableaux initiaux (les noms automatiques manquent de lisibilité)

J'ai limité à décembre car sinon il serait plus logique de mettre janvier 2020...

Mais on peut ajouter des données sur d'autres mois

Traités dans PowerQuery

2 façons de faire le bilan selon le niveau de détail voulu dans ce bilan et la périodicité de facturation...

Faute d'infos j'ai pris 2 angles mais tout est adaptable...

Edit : j'ai repris ton avant dernier jet car le dernier n'était pas posté quand j'ai commencé à bosser dessus

Ouaou en effet, c'est vachement mieux que ce que j'avais fais de mon côté par contre je comprends pas trop comment ça a été fait. Je vais regarder les connexion de données qui ont été établie pour cela.

Le bilan powerquery est très bien car je vais conservé un tableau pour le détail des jours, et un tableau pour les charges de transport et un tableau pour les charges salariales.

Petite remarque, j'ai crée un nouvel employé + un nouveau chantier + un pointage sur ce chantier + une facture associé à ce nouveau chantier et lorsque j'actualise les données il y a une erreur :

Je viens d'observer comment tu as fait pour le calcul des charges, en effet, je n'aurai pas su faire car cet après midi c'est la première fois que j'utilise powerquery mais c'est bien je peux voir l'historique de ce que tu as fait. C'est loin d'être simple pour une première fois avec l'ajout de colone + le groupement + changement de type de colone.

C'est "logique" lorsque je le lis, mais à réaliser c'est toujours plus complexe

RE

matteu a écrit :

Petite remarque, j'ai crée un nouvel employé + un nouveau chantier + un pointage sur ce chantier + une facture associé à ce nouveau chantier et lorsque j'actualise les données il y a une erreur :

Il faut juste espacer un peu plus les TCD qui doivent se marcher sur les pieds

La difficulté est surtout le calcul des jours entre début et fin.

Pour convertir une date en année et mois il y a plusieurs solutions : j'ai fait au plus simple

Le changement de type : PowerQuery le fait tout seul mais pas forcément parfaitement

Prendre l'habitude forcer si pas OK

Pour les regroupements c'est très simple : une fois qu'on en a fait un...

Si tu listes pas à pas ce que j'ai fait, je pense que c'est logique et du coup la méthode va transparaitre.

Si on a l'habitude des BD et des relations cela aide car ici on fait plus de la "gestion" de BD que du tableur

J'ai tout déplacer et j'ai le même problème...

Si j'actualise tous les éléments 1 à 1 je n'ai aucun problème

RE

En fait c'est les 2 bilans l'un au dessus de l'autre qui fait réagir Excel

Si on déplace le Bilan par TCD et son titre ailleurs, il ne tousse plus.

En effet

J'avais remarqué que c'était lors de l'ajout d'un chantier et sa facture associée de mon côté.

Bon bé je vais tester ca un peu plus en profondeur pour voir si tout ce que je veux est bon, mais ca semble être le cas.

Bilan2 ne m'intéresse pas au final car on à le montant des charges sur la droite.

Merci beaucoup de ton aide, je viens faire un retour ce soir ou demain.

Bon bé ça m'a l'air plutôt pas mal

Je souhaiterais avoir un graphique en histogramme pour voir les chantiers et leur bénéfice. Toutefois, lorsque je veux faire un GCD à partir du TCD, je n'ai pas l'option pour afficher le bénéfice mais j'ai toutes les autres (facture, essence, main d'oeuvre, ...). Ce n'est pas faisable ?

De la même façon, si je souhaite avoir un graphe pour le second TCD qui reprend uniquement le nombre total de jour par chantier ce n'est pas possible ? A chaque fois, il me met les employés dans le graphe. Je suppose que le GCD s'appuie sur le TCD et qu'il n'est pas "customisable".

RE

Le bénéfice correspond au total et un GC n'affiche jamais le total.

De toutes façons cela mélangerait un peu les choux et les carottes en partant de ce TCD

Tu veux afficher quoi concrètement ?

Tu peux te servir du tableau Bilan en n'affichant que la colonne bénéfice

il faudrait aussi qu'on définisse les jours non travaillés car là cela calcule 7/7

Attention PowerQuery est sensible à la casse.

Si les noms de chantiers ne sont pas toujours écrits de façon strictement identique, cela ne marche pas correctement