Résoudre des prestations réciproques avec Excel

Dans la méthode des coûts complets et plus particulièrement des centres d’analyse, l’objectif est de déverser les coûts indirects. Le premier déversement (répartition primaire) permet d’envoyer les coûts indirects vers les centres auxiliaires et principaux. Le second déversement de répartir les coûts des centres auxiliaires dans les centres principaux. Enfin, nous utiliserons les unités d’oeuvre pour calculer la partie du coût indirect du produit ou service.

Une des problématiques dans le calcul de la répartition secondaire, est qu’avant de réaliser cette répartition, il est nécessaire d’avoir le montant des coûts indirects pour chaque centre auxiliaire. Or il est possible que des prestations existent (c’est d’ailleurs traditionnellement le cas) entre ces centres auxiliaires. Lorsque ces prestations réciproques ne sont qu’entre deux centres, tout va bien mais dès que le nombre augmente, nous devons résoudre x équations à x inconnues. La seule méthode pour résoudre ce type d’équation est l’utilisation de matrices.
Afin de bien comprendre la problématique, nous allons prendre un exemple simple.
Nous avons 3 centres auxiliaires: Gestion du personnel (GP), Communication (COM) et Direction Générale (DG). Les prestations sont réparties comme indiqué ci-dessous:
L’objectif est donc de trouver le montant des charges indirectes pour chaque centre. Cela revient à résoudre les équations suivantes:
(1) GP = 250 000 € + 32% COM + 18% DG      soit GP -32% COM -18% DG = 250 000 €
(2) COM = 36 000 € + 27% GP + 8% DG          soit -27% GP + COM -8% DG = 36 000 €
(3) DG = 85 000 € + 24% GP + 12% COM        soit -24% GP -12% COM + dG = 85 000 €
Les trois inconnues sont donc GP, COM et DG.
Pour résoudre ce système d’équations, il faut utiliser les matrices. La première étape est donc d’avoir les équations avec les inconnues dans le même ordre.

Pour supprimer la matrice A, il faut multiplier par la matrice inverse A-1
Nous aurons ainsi : matrice (GP;COM;DG) = A-1 x B
Une fonction EXCEL existe: INVERSEMAT
Il faut ensuite faire le produit matriciel : PRODUITMAT

Voici comment retranscrire cela dans Excel:
Nos utiliserons la fonction TRANSPOSE pour construire les matrices A et B. Puis INVERSEMAT pour construire A-1.
Toutes les formules utilisées sont des formules matricielles.
Voici ensuite le résultat obtenu:
Nous avons donc réussi très facilement grâce à Excel, à résoudre la problématique des prestations réciproques.

2 thoughts on “Résoudre des prestations réciproques avec Excel

  • 2 novembre 2016 at 10 h 59 min
    Permalink

    Ça fonctionne – Super – Merci

    Reply
    • 7 novembre 2016 at 11 h 23 min
      Permalink

      Avec plaisir !

      Reply

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *