[AC-2007] Etude de rentabilité

[K4mu1]

Bonjour à tous, suite à mes diverses mésaventures dans l'élaboration d'une base de données, je sollicite votre aide.

Je vais donc tenter d'expliquer de manière simple mon problème :
Je travail dans un cabinet d'audit ayant une multitudes de dossiers et de travaux différents. Ce cabinet fait parfois appel à des sous traitants pour nous aider dans la finalisation de nos missions. Notre facturation s'effectue en heure, et pour cela nous remplissons une feuille hebdomadaire sous excel récapitulant tous les travaux exécutés durant la semaine avec le temps passé.


Je désire élaborer sous access une base de données permettant le calcul d'une rentabilité par dossier ainsi qu'un suivi des temps passés par dossier sachant que la base des informations démarre d'un fichier excel qui est importé dans access.

Les champs de ce fichier étant les suivants :
N°Dossier ; TYPE ; N°Intervenant ; N°SS TRAITANT ; N°date ; Mission ; Nombre heures bureau ; Nombre heures extérieur ; Exercice ; Facturable ; Rattachement ; N° Transport ; KM ; PU ; Total ; Hotel TTC ; Hotel TVA ; Carburant TTC ; Carburant TVA ; Restaurant TTC ; Restaurant TVA ; Divers TTC ; Divers TVA ; Total TTC ; Total HT ; Total TVA


Chaque semaine il devrait donc y avoir une importation de ce fichier dans la base de donnée.
Toutes les information utilses sont dans le fichier Excel. Néanmoins lorsque nous devons intégrer un nouveau salarié, un nouveau dossier.... la mise à jour devra être faite dans la base de donnée.

J'ai déja effectué un travail sur la base, mais l'on m'a conseiller de tout revoir. Je cherche donc des avis de personnes confirmer pour m'aiguiller dans ce projet.

Ci joint mon premier projet.

PS : la table travail reprenant intégralement la totalité des champs du fichier excel

[Richard_35]

Bonjour K4mu1,

De premier abord :

Table INTERVENANT :
N°Intervenant doit être une clé primaire.

Table SOUS TRAITANT :
N°SS TRAITANT doit être une clé primaire.

Table TRAVAIL :
Il faut créer un champ Id_travail en clé primaire (numéro auto) ;
N°Dossier servant de liaison pour accéder à la table DOSSIER (clé étrangère).

Relations :
DOSSIER 1----n Travail, via N°DOSSIER ;
TYPE DOSSIER 1----n Travail, via TYPE ;
INTERVENANT 1----n Travail, via N°Intervenant ;
SOUS TRAITANT 1----n Travail, via N°SS TRAITANT ;
TRANSPORT 1----n Travail, via N°Transport ;
FACTURATION : ? - dépend de ton analyse.
==> remarques : pour que la partie "n" s'affiche, il faut que les deux champs de liaison soient de même type ; appliquer l'intégrité référentielle.

Je te suggère, également, d'arranger tes tables pour que les relations ne se croisent pas.

Ceci, sans rentrer dans le fond de l'analyse.

[K4mu1]

Tout d'abord merci de bien vouloir vous pencher sur mon probleme.
Je vais réarranger mon model relationnel alors.
Par contre le fait que je rajoute une colonne (Id_travail) devant n° de dossier, cela bloque l'import de la feuille excel car les champs ne sont pas correspondant. Faut il alors que je crée également une colonne vide ID_travail dans ma feuille excel ?

[Richard_35]

Si le nom des champs est le même dans la table Access et dans le fichier Excel (sauf Id_travail), il ne devrait pas avoir de problème : Id_travail se remplira tout seul.

Sinon, tu peux créer une liaison (table liée) sur le fichier Excel, et effectuer une requête "ajout" ou "création de table", selon le cas.

[ClaudeLELOUP]

Bonjour,

Peux-tu nous dire quel historique contient le fichier Excel.
Chaque semaine, lorsque tu l'importes, ce fichier contient-il encore toutes les informations de la semaine précédente ?

[K4mu1]

Une requete ajout ?
Mais avant tout, en quoi le fait de créer cette clé primaire est important ?

[K4mu1]

Alors ca c'est une bonne question. Il existe une fiche par semaine. Donc chaque import dans la base prend uniquement la fiche de la semaine. L'historique étant gardé dans la table travail.
En somme c'est comme si la fiche est remise à zéro chaque semaine.

[Richard_35]

Une requete ajout ?

==> dans la requête, click droit à l'endroit où tu as tes tables, choisir "Requête ajout" et répondre aux messages.

Mais avant tout, en quoi le fait de créer cette clé primaire est important ?

==> ta question revient à demander : "à quoi cela sert-il d'avoir une clé primaire ?". Eh bien cela sert à identifier, de manière unique, chaque enregistrement d'une table afin de pouvoir accéder, à coup sûr, à l'enregistrement choisi.


Concrètement, pour ta table TRAVAIL, d'après ce que j'ai compris, tu auras plusieurs travaux (de la table TRAVAIL) pour un dossier, non ?

[ClaudeLELOUP]

Ceci me donne l'occasion de saluer Richard_35.

Si je comprends bien :
Il y a une feuille Excel dont le format est constant.
Chaque semaine, on repart avec une feuille vierge.
Chaque fois qu'une dépense a été faite pour un dossier, on ajoute une ligne à cette feuille au fur et à mesure.
Quand la semaine est terminée, on importe cette feuille dans la BDD.

Ton but : exploiter cette info "en vrac" pour calculer une rentabilité par dossier.

Correct ?
------

Lorsque tu l'importes, y a-t-il des anomalies dans ce fichier Excel ? Faut-il parfois corriger une donnée des semaines passées ?

[K4mu1]

@Richard : et bien oui il se peux que l'on intervienne sur le même dossier pendant plusieurs jours (donc plusieurs fois le meme n° de dossier.
Mais on peut également intervenir sur le même dossier en faisant des travaux différents, reseignés dans la colonne "type".

@Claude : exactement, pour calculer d'une part la rentabilité par dossier et type de travaux (table type) (via le formulaire rentabilité) et le temps passé par dossier (via le formulaire fichedil)

Parfois il est vrai que lorsqu'on importe la fiche excel, des erreurs se glissent et du coup il y a un message d'erreur, donc des rectifications sont a faire dans la fiche.

[ClaudeLELOUP]

Donc la table Travail contient toutes les données de base de la rentabilité.

Mis à part certaines données signalétiques telles que
- le taux horaire de l'intervenant
- le taux horaire facturé au client
- ...

Si j'ai bien compris, ta BDD contient donc des tables inutiles comme par exemple Factssstt, Facturation dont l'information fait double emploi avec celle déjà contenue dans Travail.

Correct ?

[K4mu1]

La table travail contient une majeure partie des informations pour déterminer la rentabilité. Le taux horaire de l'intervenant permet de calculer un cout de l'intervention par intervenant (d'ou le formulaire rentabilité : total des heures * taux).

Par contre les tables factsstt et facturation sont des informations réelles dont l'information sera rentrée manuellement en fin d'intervention.

Attention, je pense que vous confondez la facturation réelle par rapport aux frais engagés. Je vais donner quelques précisions sur ce formulaire rentabilité :
- chaque intervention à un cout (le salaire de l'intervenant). soit le nombre d'heures * le tarif de l'intervenant (salarié)
- ce salarié peut engagé des frais (déplacement, hotel, repas)
- l'addition du cout d'intervention et des frais engagés nous donne le cout brut de l'intervention.
- a cela il faut rajouter la facture que le sous traitant nous envoie et qui est renseignée par la taboe factsstt (il n'y a pas toujours de sous traitance)
- la somme de tout ces frais nous donne le montant que nous devrions facturer. or après négociation avec le client, ce n'est pas toujours le cas
- c'est pourquoi on retranche du total du cout la facturation réelle que nous établissons au client (qui provient de la table facturation).
- la résultante du calcul nous donne la rentabilité du dossier. Si la facturation est supérieur au cout de revient (salaire, déplacement, sous traitance), on est gagant, sinon on est perdant.


Le fait d'avoir le temps passé sur un dossier par le sous traitant n'est pas utilisable dans l'etude de rentabilité mais dans le formulaire fichdil. En effet, nous somme astreint a donner à chaque fin de travail sur un dossier le temps total passé. C'est pourquoi j'ai du rajouter le temps passé par le sous traitant (formulaire fichedil)

J'espère avoir pu éclairer votre lanterne.

[ClaudeLELOUP]

Résumons : [origine de la donnée]

1 Le chiffre d’affaires d’un dossier est égal à

somme des montants HTVA facturés [Facturation]

2 Le prix de revient d’un dossier est égal à

(le temps passé par un intervenant[Travail]) X (le taux horaire de l’intervenant[INTERVENANT])

plus

les frais engagés par l’intervenant (déplacement/hôtel/repas)[Travail]

plus

les factures éventuelles des sous-traitants [Factsstt]


Le résultat = (1) – (2)


D'accord ?

[K4mu1]

Exactement. C'est ce que j'ai fait dans le formulaire rentabilité.
Néanmoins dans mon calcul de rentabilité je n'arrive pas a récupérer les données de factsstt et facturation en fonction des filtres que j'ai fais dans le formulaire rentabilité.

[ClaudeLELOUP]

Quelle influence a la TVA ?
Normalement le calcul de rentabilité se fait HTVA sauf dans le cas où une partie de la TVA amont n'est pas récupérable (en Belgique en tout cas). J'ignore votre statut fiscal. Dans ton cas, toujours HTVA ?
Je pose cette question, vu que les 2 données sont présentes dans travail.

Tu as prévu 1 seul taux horaire par intervenant.
Cela veut dire que ton calcul de rentabilité sera fait à ce taux unique quelle que soit la date de prestation.

[K4mu1]

Alors dans le HT sera déja inclus le non récupérable (fait en amont dans la fiche excel) donc on ne sepréoccupe pas de la TVA, c'est simplement une information complémentaire.

Le taux horaire par intervenant est unique car il s'agit de cout horaire calculé à partir de sa fiche de paye. Toutefois, chaque intervenant à un taux horaire différent de l'autre (d'ou le champs dans la table intervenant)

Le taux est donc unique pour l'intervenant tant que cette donnée ne sera pas modifiée manuellement.

[ClaudeLELOUP]

Donc aujourd'hui tu calcules la rentabilité d'un dossier clôturé.
Tu trouves xx euros.

Demain, l'intervenant est augmenté.
...la rentabilité des ses anciens dossiers diminue.
Etrange, n'est-il pas ?

[K4mu1]

En effet c'est très étrange. N'y a t il pas une possibilité de figer un état lorsque le dossier est "finalisé" ?
Si ce n'est pas possible ce n'est pas vraiment grave vu que lorsqu'un dossier est cloture et que la rentabilité est étudié, l'état est imprimé.
Mais c'est bien vu, je ne m'était pas posé cette question !

[ClaudeLELOUP]

ce n'est pas vraiment grave vu que lorsqu'un dossier est cloture

C'est contradictoire avec l'esprit de ton formulaire :

- pas de filtre : tous les contrats, tous calculés aux taux horaires d'aujourd'hui.

- un contrat d'une année... avec des prix de revient actuels !


Enfin, on verra ensuite.

Je te reviens plus tard : je vais examiner ton formulaire rentabilité pour le faire fonctionner "techniquement" avec les données telles quelles.

[ClaudeLELOUP]

L’idée serait d’avoir une série de formulaires en gigogne.

Au niveau le plus synthétique, un formulaire comme celui-ci :



Avec, dans l'en-tête, quelques champs pour filtrer les enregistrements à afficher.
Et, éventuellement certains totaux, dans le pied.

Sa source s’obtient en quatre requêtes :

rfRentabiliteDossier01

Code :

1
2
3
4
SELECT DOSSIER.N°DOSSIER, DOSSIER.[NOM DOSSIER], Sum(nz([Nombre heures bureau],0)+nz([Nombre heures extérieur],0)) AS TotalHeures, Sum((nz([Nombre heures bureau],0)+nz([Nombre heures extérieur],0))*[TARIF]) AS EstimationIntervenant, Sum(Travail.[Hotel TTC]) AS Hôtel, Sum(Travail.[Carburant TTC]) AS Carburant, Sum(Travail.[Restaurant TTC]) AS Restaurant, Sum(Travail.[Divers TTC]) AS Divers, [TYPE DOSSIER].[NOM TYPE], Travail.Exercice
FROM DOSSIER INNER JOIN ([TYPE DOSSIER] INNER JOIN (INTERVENANT INNER JOIN Travail ON INTERVENANT.N°Intervenant = Travail.N°Intervenant) ON [TYPE DOSSIER].TYPE = Travail.TYPE) ON DOSSIER.N°DOSSIER = Travail.N°Dossier
WHERE (((Travail.Facturable)="o"))
GROUP BY DOSSIER.N°DOSSIER, DOSSIER.[NOM DOSSIER], [TYPE DOSSIER].[NOM TYPE], Travail.Exercice;

A compléter pour filtrer

rfRentabiliteDossier02

Code :

1
2
3
SELECT Factsstt.N°DOSSIER, Sum(Factsstt.MontantHT) AS Factsstt
FROM Factsstt
GROUP BY Factsstt.N°DOSSIER;

rfRentabiliteDossier03

Code :

1
2
3
SELECT Facturation.N°Dossier, Sum(Facturation.MontantHT) AS NosFactues
FROM Facturation
GROUP BY Facturation.N°Dossier;

et enfin la source : rfRentabiliteDossier04

Code :

1
2
SELECT rfRentabiliteDossier01.N°DOSSIER, rfRentabiliteDossier01.[NOM DOSSIER], rfRentabiliteDossier01.TotalHeures, rfRentabiliteDossier01.EstimationIntervenant, rfRentabiliteDossier01.Hôtel, rfRentabiliteDossier01.Carburant, rfRentabiliteDossier01.Restaurant, rfRentabiliteDossier01.Divers, rfRentabiliteDossier01.[NOM TYPE], rfRentabiliteDossier01.Exercice, rfRentabiliteDossier02.Factsstt, rfRentabiliteDossier03.NosFactues
FROM (rfRentabiliteDossier01 LEFT JOIN rfRentabiliteDossier02 ON rfRentabiliteDossier01.N°DOSSIER = rfRentabiliteDossier02.N°DOSSIER) LEFT JOIN rfRentabiliteDossier03 ON rfRentabiliteDossier01.N°DOSSIER = rfRentabiliteDossier03.N°Dossier;

La gigogne :

Un double-clic sur un champ d’un enregistrement ferait apparaître un formulaire donnant le détail du poste.
Par exemple double-clic sur 26.400 ferait apparaître la décomposition de ce montant, c’est-à-dire Nbre d’heures x taux horaire pour chaque intervenant.

Encore à un niveau plus détaillé, les dates des prestations…

-------
Vois modif dans INTERVENANT, Factsstt, Facturation.
Les autres tables devraient aussi être revues (redondances) et aussi le nommage des champs.
Tu devrais lire :

- conventions typographiques
http://argyronet.developpez.com/office/vba/convention/

----------

Qu'en pensez-vous K4mu1, Richard_35 et les autres lecteurs éventuels ?