Navigation:  QDV Tools for EXCEL > QDV Distribution Tool

Cas d'utilisation

Pour être conforme à l'article 10.3.3 du CCAG Travaux 2009, un devis QDV7 (ou une partie) doit être fourni selon un modèle Excel particulier qui inclut les minutes et déduit des frais indirects par application de coefficients au coût total. Plus précisément, il faut distinguer :

▪au niveau minute, les déboursés ou frais directs, décomposés en dépenses de salaires et indemnités du personnel, charges salariales, dépenses de matériaux et de matières consommables, dépenses de matériel

▪au niveau poste, éventuellement séparés en parts :

▪les frais généraux, d'une part, les impôts et taxes, d'autre part, tous exprimés par des pourcentages des déboursés

▪la marge pour risques et bénéfices, exprimés par un pourcentage de la somme Déboursés + Frais généraux + Impôts et taxes.

 

Il y aura autant de feuilles dans le classeur que de postes. Un récapitulatif tenant compte des quantités par poste n'est pas demandé. Le modèle peut être organisé en chapitres.

Les cas simples où les parts propre, sous-traitée et cotraitée ne doivent pas être séparées ou portent les mêmes coefficients, peuvent se traiter depuis QDV7. Voir Etats selon CCAG Travaux.

On utilisera QDV Distribution Tool si le devis comporte une partie propre, une partie sous-traitée et une partie cotraitée, chacune avec des coefficients différents. C'est souvent le cas :

▪des métiers du second œuvre

▪des métiers du BTP (Gros Œuvre, Infrastructures, VRD, …).

 

Les frais indirects peuvent être différents de ce que prévoit l'article 10.3.3 (frais de chantier, de pilotage, ...) et les coefficients appliqués sur des bases différentes. C'est le cas dans nos exemples.

 

Terminologie

La mise en œuvre de l'outil ne requiert pas normalement la connaissance de QDV7. Cependant, dans ce qui suit on utilise plutôt la terminologie native de QDV7, qui diffère un peu de celle du CCAG Travaux :

 

DEVIS QDV7

On appelle Ressources les types définis pour chaque jeu de colonnes de l'environnement :

▪Matériel

▪Main d'œuvre

▪jeux de colonnes supplémentaires éventuels.

Chaque ressource est identifiée par son ID, appelée clé de répartition.

On appelle Méta Données :

▪les ressources

▪les nœuds de l'arborescence WBS.

 

MODELE

On appelle Modèle la trame Excel imposée ; des exemples figurent en <dossier d'installation>\QDV 7\Samples\Stuff_4_QDVTools4XL. Dans le dialogue, elle est nommée Feuille à dupliquer ; seules les colonnes à gauche de la Première colonne de la zone de travail seront publiées.

On appelle Première ligne à dupliquer la ligne de référence contenant les formules : elle sera répliquée pour chaque article.

On appelle Chapitre un ensemble de minutes dont les identifiants de type sont associés. Le chapitrage ou Organisation permet en effet de regrouper les minutes selon ces identifiants. Voir les exemples de "structure" fournis. Ne pas le confondre avec la pagination qui fait qu'on créera autant de feuilles que de postes (et de branches) sélectionnés.

 

TRAITEMENT

On appelle Algorithmes les règles de calcul permettant d'adapter le devis QDV7 au modèle. En plus d'une formule, un algorithme comporte au moins un objectif soumis à un solveur (dans Excel, le sens est plus large). Par nature, ces calculs exigent une heuristique avec des itérations.

On appelle Phases les états successifs en vue d'une résolution des anomalies.

On appelle Décomposition le résultat final d'une génération : la ventilation d'un PV ligne sur plusieurs colonnes prédéfinies (Prix coefficientés) ; on peut créer une base articles Excel, appelée Jeu de données. On peut ainsi charger l'algorithme et les données d'une précédente décomposition pour être sûr que les prix initaires d'articles de mêmes références se décomposent avec les mêmes valeurs.

 

Principe

QDV7 distingue les minutes par les ressources associées. Il s'agit de remplacer cette distinction par une distinction selon 6 catégories au maximum, par exemple :

▪Salaires, indemnités, et charges sociales

▪Frais généraux

▪Matériaux et consommables

▪Sous-traitance

▪Matériels.

Cette affectation manuelle dirige les prix de vente des jeux de colonnes de chaque ligne de minute selon cette nouvelle grille.

Ceci revient à "commuter" chaque PV vers une colonne ou une autre (Répartition) :

Il n'est pas utile normalement d'ouvrir QDV7. Cependant, il est conseillé d'ouvrir le devis et de le recalculer.

On distingue 3 phases :

Phase 1. Récupération des données du devis QDV7 et application de la formule

Phase 2. Mise en œuvre de la compensation et génération des anomalies potentielles

Phase 3. Génération du devis final ou création d'une base d'articles.

 

 

Activer le complément Excel

Exigé : Excel 2013 ou ultérieur

1.Dans la suite QDV7, ouvrir QDV Tools for Excel

2.cliquer sur Activate QDV Tools for Excel, puis sur Close.

 

Ouvrir et préparer le modèle Excel

1.Ouvrir le modèle ; dans l'onglet QDV, sélectionner l'application Distribution (icône chevalet à gauche) :

2.saisir les coefficients (barème pour l'estimation comme dans la Feuille des ventes de QDV7) et les formules de calcul (les cellules contenant les coefficients pourront être remplies dans le volet)

3.saisir la formule de calcul du Prix de vente unitaire dans la ligne de référence

4.dans Formules>Gestionnaire de noms, nommer éventuellement les cellules de la ligne de référence qui sont utilisées dans la formule

5.laisser une ligne vide après la ligne de référence ; pour l'option Organisation, insérer à la place un nombre pair de lignes (une ligne de titre de chapitre et une ligne à dupliquer)

6.dans la ligne de total, les colonnes de total doivent additionner les valeurs de toutes lignes précédentes par une formule SOMME(<plage>)

7.dans la zone d'application des coefficients, pour la base, faire référence aux totaux de la ligne de total (les références peuvent être absolues ou relatives)  

8. pour tester la formule, saisir des valeurs dans la ligne de référence et, si cela n'a pas été fait à l'étape 2, des valeurs pour les cellules contenant les coefficients.

Dans la zone où sont appliqués les coefficients, s'assurer de ne pas avoir dans la même colonne du texte et des nombres.

Limitations :

- Ne pas activer l'option de cacul avec les valeurs affichées, au lieu des valeurs stockées

- Eviter les fonctions autres que SOMME, MAX, MIN (sinon il faudra les resaisir dans l'outil).

- Formatter correctement les cellules réceptrices

- Pour plus de clarté, on peut colorer les cellules où une saisie directe ou non est nécessaire

- Pour alléger les formules, mettre les cellules contenant les coefficients au format Pourcentage

- Dans les options avancées, groupe Options d'affichage de la feuille de calcul, cocher la case Afficher un zéro dans les cellules qui ont une valeur nulle.

 

Lier à un devis et paramétrer

Ceci est nécessaire si le modèle n'a pas encore été enregistré avec Excel et QDV Distribution Tool. En effet, l'enregistrement inclut le lien, les métadonnées et les paramètres de l'outil.

S'assurer que le curseur n'est PAS dans la barre de formule ; sinon Excel attend une formule : la barre d'état indique Modifier, au lieu de Prêt et un message s'affiche.

Pour établir un lien avec le devis, cliquer sur l'icône Lier de l'onglet QDV.

Sélectionner le devis ; l'icône s'estompe.

Le titre de la fenêtre devient <nom du fichier Excel>[QDV-><chemin complet du devis>]

Enregistrer le fichier.

Pour changer de devis, cliquer sur Délier et répondre deux fois Non aux questions.

 

Pour accélérer la sélection de champs dans les listes pour le mappage, marquer les champs utiles au préalable :

1.Sélectionner Devis>Ouvrir et sélectionner le devis

2.recalculer tout (Ctrl F5) et enregistrer (si ce n'est pas fait, l'outil le fera en phase 1)

3.dans chaque gestionnaire de champs, sélectionner un champ (natif ou libre) à utiliser (PV par unité d'un jeu de colonnes et PV par unité en colonne libre)

4.faire un clic droit et sélectionner Afficher par défaut dans les outils Excel ; [SHOW_BY_DEFAULT_IN_TOOLS4XL] apparaît dans la colonne Commentaire

5.répéter et cliquer sur OK.

Si deux ressources de deux Jeux de colonnes ont le même identifiant du type, bloquer la contribution de l'une d'elles par clic droit sur le champ Prix total dans le gestionnaire de champs des minutes et sélection de Cacher dans les outils Excel (ceci se lit dans la colonne Commentaire).

BONNES PRATIQUES Pour éviter des écarts, ne pas prendre trop de décimales dans le WBS (Fichier>Options>Arrondis).

NOTA Puisque la clé de voûte de l'outil est la distinction par les ressources, vérifier que la plupart des minutes contribuent à un ou deux types (selon qu'elles consomment Matériel et Main d'œuvre ou seulement l'un des deux)

Les groupes peuvent être traités. Mais les calculs sont faux en présence de champs libres et de quantités différentes de 1. Insérer donc des champs par unité, par exemple COTRAIT_PVUNIT_QENT au lieu de COTRAIT_PVTOTAL_QENT.

 

Sélectionner Paramètres. La configuration qui s'affiche est la dernière utilisée par QDV Distribution Tool (mais le lien au devis doit être établi avant).

Pour prédéfinir les Options du volet flottant :

▪option "Descriptions dans les listes" : cocher "Montrer par défaut la description (c'est-à-dire le nom) au lieu du code (identifiant) dans les listes"

▪option "Seulement les colonnes par défaut" : cocher "Dans les listes, utiliser les colonnes par défaut (c'est-à-dire marquées) pour réduire la taille des listes"

▪option Exclusion conditionnelle de lignes des minutes : sélectionner la colonne dont les valeurs seront comparées à la valeur spécifiée.

L'onglet Paramètres>Répartition permet de définir un filigrane indiquant le numéro de phase (état), et une description pour la ligne d'ajustement créée pour résoudre certaines anomalies.

Pour que les nouveaux paramètres de distribution s'appliquent, fermer et réouvrir Excel.

 

Importer les Méta Données (si pas encore fait)

Cliquer sur Méta Données. Ceci lit les ressources du devis.

 

Mapper la trame

MAPPAGE (onglet Paramètres)

La signification des champs du bandeau Données de poste doit être indiquée à l'outil.

La signification des colonnes de la vue des minutes doit être indiquée à l'outil.

Voir Mapper la trame.

 

AFFECTATION DES IDENTIFIANTS (onglet Répartition)

Sélectionner dans la liste déroulante une colonne à décomposer.

Sélectionner dans la liste issue du devis un identifiant dont les prix de vente doivent être aggrégés dans cette colonne ;  le verser dans la zone de réception. Au besoin, recommencer avec d'autres identifiants.

Recommencer avec les autres colonnes à décomposer.

Enregistrer souvent le paramétrage.

 

Générer chaque phase

DEFINIR LES ALGORITHMES

Voir Définir les algorithmes.

 

AFFECTATION DES IDENTIFIANTS (option de l'onglet Organisation)

SELECTION DE PORTEE ET PAGINATION (onglet Sélection)

Cocher les postes à retenir. On peut panacher branches et postes comme sur le schéma de principe (branche A et postes B1, B2, etc).

GENERATIONS

Voir Générer chaque phase.

 

Exemple sans organisation

Les coefficients du modèle ne sont pas écrasés car aucune nouvelle valeur n'est saisie dans le volet :

Sous-détail

 

---

Détails

Mapper la trame

Définir les algorithmes

Générer chaque phase

Exemple avec colonne libre

Mettre en cohérence

Remettre un bordereau des coûts