quelques notions excel en cgo

10 avril 2007

quelques notions excel en cgo

quelques notions excel en CGO

Les fonctions mathématiques, statistiques et financières

Partie 1 : Utilisation de l’outil Valeur Cible

EXEMPLE

Une entreprise fabrique et commercialise un produit : le PEON

On vous communique les renseignements suivants :

Marge sur coût variable unitaire : 1 200 € (Proportionnelle aux quantités vendues

Charges fixes : 12000 €/an

Exprimez l’équation du résultat en fonction de x, les quantités vendues

Y=1200x-12000

L’outil valeur cible va permettre de déterminer la valeur de la variable x en fonction du résultat y désiré.

Dans Excel on va réaliser le tableau suivant :

La formule en D6 est : =D4*D2-D3

Remarque : en nommant la cellule D4 en x, la formule aurait été =D2x-D3

x a été initialisé à 0

Question : Quelles doivent être les quantités vendues pour que le résultat soit nul (en d’autres termes, à partir de quelle quantité vendue, l’entreprise devient elle rentable ?)

Cela revient à résoudre l’équation suivante :

0=125x-120000

Pour résoudre l’équation avec Excel, cliquez sur le menu Outils puis valeurs cibles

Cellules contenant l’équation de résultat. La cellule à définir doit obligatoirement contenir une formule

Objectif à atteindre (variable y)

Cellule variable (x) dont on veut connaître la valeur (inconnue)

Excel a déterminé que la quantité de 960 produits vendus permettait d’obtenir un bénéfice nul. Donc au delà, l’activité de l’entreprise devient rentable

Application

En utilisant l’outils Valeurs cibles, déterminez les résultats de l’équation suivante :

12016=12,5x²+0,5x-800

Avec valeurs cibles on trouve UNE solution : 32

Partie 2 : Utilisation du Solveur

Le solveur est utilisé en programmation linéaire pour résoudre des problèmes de maximisation de profit sous contraintes (détermination d’un programme de production), pour des minimisations de coûts et pour résoudre des systèmes d’équations à plusieurs inconnues.

Le solveur repose sur une approche par essais successifs appelés des itérations, jusqu’à obtenir la solution optimale.

Application 1 : Maximisation sous contraintes

La société TINE fabrique et commercialise 3 produits P1, P2 et P3. Elle souhaite déterminer la quantité de produits P1, P2 et P3 qui maximise sa marge sur coût de revient, compte tenu des contraintes de production.

Les capacités maximales de traitement (contraintes techniques) sont les suivantes :

- Quantité de matières premières : 32 000 kg

- Main d’œuvre directe : 1 100 Heures

- Capacité de l’atelier : 700 heures machines

Les consommations unitaires de chaque produit sont les suivantes

- 1 produit P1 nécessite 26 kg de matières, 1,3 h de MOD et 0,7 h machines

- 1 produit P2 nécessite 23 kg de matières, 0,9 h de MOD et 0,6 h machines

- 1 produit P3 nécessite 18 kg de matières, 1,1 h de MOD et 0,5 h machines

La marge générée par la vente de chaque produit est la suivante :

- P1 = 24 €

- P2 = 17 €

- P3 = 15 €

Formulation mathématique du problème :

x -> quantité de P1

y -> quantité de P2

z -> quantité de P3

Fonction économique : 24x+17y+15z=Zmax

Contraintes techniques : 26x+ 23y+ 18z £ 32.000 (Capacité de traitement de la matière)

1,3x+0,9y+1,1z £ 1.100 (Capacité maxi en MOD)

0,7x+0,6y+0,5z £ 700 (Capacité maxi de l’atelier)

Les quantités de produits sont obligatoirement positives ou nulles

x ³ 0

Contraintes évidentes y ³ 0

z ³ 0

ATTENTION

Il serait réducteur de vouloir à tout prix résoudre le système d’inéquations aux 3 contraintes techniques car l’objectif est de maximiser le profit (représenté par la fonction économique). Une telle démarche aboutirait à une solution mathématiquement acceptable mais incorrecte économiquement.

Pour résoudre « à la main » le problème posé, les gestionnaires utilisent une méthode appelée le SIMPLEX qui repose sur la méthode du pivot.

Résolution dans EXCEL

Réalisez le tableau suivant :

Les 3 variables de base sont ici (x, y et z)

Texte purement descriptif, ne sert qu’à se repérer dans la feuille de calcul

Contient les formules de chaque équation

- Nommez les cellules B3 à B5 (menu Insertion – Nom – Définir) B3 est nommé x, B4 est nommé y et B5 est nommé z. Leur valeur est initialisé à 0

- Formules de calcul :

o En D7 =24*x+17*y+15*z

o En D9 =26*x+23*y+18*z

o En D10 =1.3*x+0.9*y+1.1*z

o En D11 =0.7*x+0.6*y+0.5*z

- les cellules E9 à E11 sont fixes

Utilisation du solveur :

Menu Outils – Solveur

Si le solveur n’apparaît pas dans le menu Outils c’est qu’il n’est pas installé. Pour l’installer, il faut activer le menu Outils – Macros complémentaires – cocher la case Solveur et valider :

1) Il faut indiquer ici la cellule contenant la formule de la fonction économique (soit dans l’exemple la cellule D7)

L’assistant solveur se présente ainsi :

On définit ici l’objectif envisagé, soit une maximisation

3) Il faut définir les cellules contenant les formules liées aux contraintes

2) Il faut définir les variables (x, y et z). Excel recherche automatiquement les variables dans la cellule cible

Ce que l’on doit obtenir après paramétrage est résumé ci-après :

Les références aux cellules ont été sélectionnées à la souris. (voir ci-dessous) à la place des cellules E10, E11 et E9, on aurait pu saisir les valeurs 1100, 700 et 32000. Mais pour faire de nouvelles simulations avec d’autres contraintes, il suffira simplement de modifier la feuille de calcul. Pour ajouter, modifier ou supprimer une contrainte, il suffit de la sélectionner puis de cliquer sur le bouton adéquat

Exemple d’ajout d’une contrainte :

Après avoir cliqué sur Ajouter, on obtient ceci :

Cliquez ici pour sélectionner la cellule

Après sélection et validation de la cellule, on clique ici, on sélectionne la cellule contenant la contrainte et on valide

Pour la première contrainte, la boîte de dialogue affiche ceci :

Afficher les options :

Cliquez sur le bouton

Vous obtenez ceci :

??????????????????

Cochez cette case pour voir les résultats intermédiaires au cours des itérations successives. Pas de panique, Excel n’ira pas (dans l’exemple jusqu’à 100 itérations)

Pour des équations longues, on peut allonger le temps de réponse, le nombre d’itérations, la précision,…

Validez en cliquant sur le bouton OK

Prêt à déterminer la solution au problème ?

Alors cliquez sur le bouton

Ça ne fait que commencer !

Cliquez sur continuer, jusqu’à ce que la solution optimale soit atteinte

A la fin, la boîte de dialogue suivante s’affiche :

On peut sélectionner les rapports pour les afficher (ils sont difficiles à analyser). Il ne reste plus qu’à valider puis à observer le résultat :

L’optimum est atteint avec une production de 200 P1 et 933 P2 (P3 est à abandonner), ce qui donne une marge maximale de 20667 €.

Le plein emploi est atteint pour la main d’œuvre et les heures machines, en revanche la capacité maximale de matières premières utilisées n’est pas atteinte (sous-emploi). Si l’entreprise pouvait éventuellement desserrer les contraintes de main d’œuvre et d’heures machine (c’est à dire augmenter la capacité), elle pourrait peut être atteindre le plein emploi au niveau de la matière première.

Application

Grâce à une meilleure organisation technique et à un recrutement de personnel intérimaire, l’entreprise a augmenté de 20% le nombre d’heures de MOD (soit 1320 h au lieu de 1100) et d’heures machine (soit 840 au lieu de 700).

Cette action a t’elle une conséquence sur :

- le volume de production

- la marge

- la capacité utilisée ?

Modifiez en conséquence la feuille de calcul, exécutez de nouveau le solveur et commentez

Remarque : on peut garder une trace des différentes simulations en « enregistrant le scénario »

APPLICATION 2 : Minimisation d’un coût (sous contraintes)

Application facultative, vous pouvez passer à l’application 3 pour voir comment utiliser le solveur dans la résolution de systèmes d’équations.

L’entreprise ROBIDON doit faire face à une importante commande. Elle doit livrer 83 500 conteneurs et 175 000 bidons.

En une heure, l’atelier moulage produit 1 200 bidons et 1 400 conteneurs. L’atelier Assemblage traite quant à lui en 1 heure 130 bidons et 1 200 conteneurs

Les coûts horaires s’élèvent à 320 € pour l’atelier moulage et à 110 € pour l’atelier assemblage.

L’entreprise souhaite déterminer la durée totale optimale de chaque atelier pour minimiser le coût de la commande.

Formulation mathématique

x-> temps de travail de l’atelier moulage

y-> temps de travail de l’atelier assemblage

Fonction économique : 320x + 110y = Zmin

Contraintes (bidons) 1200x + 130y ³ 83500

(Conteneurs) 1400x+1200y ³ 175000

x ³ 0

y ³ 0

On doit donc ici minimiser le coût, sachant que l’entreprise est astreinte à produire AU MOINS 83500 bidons et 175000 conteneurs (d’où le sens des inégalités).

Résolution dans EXCEL :

Cellule nommée y

=1400*x+1200*y

=1200*x+130*y

=320*x+110*y

Cellule nommée x

Dans un nouveau classeur, préparez le tableau suivant :

Exécutez le solveur, paramétrez le correctement et résolvez ce problème en indiquant :

- le nombre d’heures optimale d’heures de l’atelier moulage et assemblage

- Le coût total

ATTENTION au sens des inégalités.

Pour vous aider, la boîte de dialogue du solveur contient les paramètres suivants :

APPLICATION 3 : Utilisation du solveur pour résoudre un système d’équations

Résoudre le système d’équations suivantes :

10x+5y+2z = 3369

0,5x+3y+1,2z = 1871.4

11x+7y+3z = 4341

Réalisation dans Excel :

Les cellules ont été nommées x, y et z

Les formules suivantes ont été définies :

=10*x+5*y+2*z

=5*x+3*y+1.2*z

=11*x+7*y+3*z

La boîte de dialogue du solveur est à paramétrer comme suit :

La valeur cible est ici une équation quelconque (dans l’exemple, il s’agit de la première

On indique la valeur du membre de droite de la 1^ère équation

Il faut indiquer ici que l’on cherche à atteindre une valeur exacte

Vous devez trouver la solution suivante :

X = 150

Y = 225

Z = 372

Pour conclure sur le solveur, il est également possible de résoudre des systèmes d’équation du n_ième degré à n inconnues.

Partie 3 : Régression linéaire sur Excel

Données du problème :

L’entreprise latendance.com a réalisé un relevé de son chiffre d’affaires pour les 12 premiers mois de l’année. Ce relevé figure ci-après :

Mois

27000

32000

28000

34000

33500

37600

39300

40000

43000

45500

49000

Activité :

1) Saisissez ces éléments dans une feuille d’un nouveau classeur

2) A l’aide des fonctions d’Excel, déterminez :

a. Le coefficient de corrélation entre l’évolution des chiffres d’affaires et des mois.

b. Le coefficient de pente de la droite d’ajustement linéaire découlant de ces données (si l’ajustement est justifié).

c. La constante «b»de la droite d’ajustement linéaire

3) Dans le tableau des données, vous ajouterez une troisième colonne pour déterminer les chiffres d’affaires ajustés.

4) Représentez graphiquement la série des chiffres d’affaires réels et des chiffres d’affaires ajustés.

Conseils : pour les formules, aidez vous de l’assistant fonction – fonctions statistiques

Eléments de corrigé :

Le graphique correspondant :

Parties 4 : Les fonctions financières d’Excel

Les fonctions données ci-dessous concerne les emprunts indivis remboursés par annuités

constantes de fin de période. Certaines fonctions retournant des valeurs négatives, il faut les

faire précéder d'un signe – (Excel considère les remboursements comme des flux négatifs à soustraire du capital

Dans les formules ci-dessous nous retiendrons les notations suivantes

Notation

C_o la valeur actuelle d'une suite d'annuités (ou encore le montant de l'emprunt)

n le nombre de versements périodiques (ou d'annuités)

C_n la valeur acquise par une suite d'annuités

a le montant de l'annuité (ou de la mensualité...)

i le taux d'intérêt pour 1 € et pour la période de temps considéré

Il est possible d'utiliser les formules habituelles sachant que la fonction élévation à la puissance

correspond à l'accent circonflexe ^ (<Alt Gr> 9)

Objectif de la formule

Formule

Exemple

Calcul du kième amortissement

=PRINCPER(i;k;n;Co)

=-PRINCPER(0.7%;5;24;420000)

16 588.64 F

calcul de l'annuité

=VPM(i;n;Co)

=-VPM(0.7%;24;420000)

19 072.18 F

Valeur actuelle

=VA(i;n;a)

=-VA(0.7%;24;19072.18)

420 000.08 F

Valeur acquise

=VC(i;n;a)

=-VC(0.7%;24;19072.18)

496 542.78 F

Recherche du taux

=TAUX(n;- a;Co)

=TAUX(24;-19072.18;420000)

0.700%

PRINCIPES DE CALCUL

Les formules ci-dessus sont celles utilisées couramment en mathématiques financières. Elles résultent des propriétés des progressions géométriques. Des explications complémentaires sont données dans les pages suivantes.

Actualisation des annuités

Compte tenu des annuités versés périodiquement, à quoi correspond le capital à l’époque 0

…

C₀

C_n

Périodes

C₀=a(1+i)^-1+a(1+i)^-2+a(1+i)^-3+ … + a(1+i)^-n

D’où

Capitalisation des annuités

Le problème est cette fois ci de déterminer à l’échéance la valeur acquise par les versements

…

C₀

C_n

C_n=a(1+i)^n-1+a(1+i)^n-2+a(1+i)^n-3 … + a(1+i)^-3+a(1+i)^-2+a(1+i)^-1+a

D’où

Problème du taux : Le taux doit être adapté à la périodicité des paiements. En mathématiques financières, on utilise le taux équivalent, en revanche, les banquiers utilisent un taux proportionnel (plus avantageux pour eux).

Exemples : pour un taux annuel de 12%

Périodicité de versement

Taux équivalent

Taux proportionnel

Mois

1,12^(1/12)- 1 =

0.00948879

soit

0.95%

0,12/12=

0.01

soit

1.00%

par mois

Trimestre

1,12^(1/4)- 1 =

0.02873734

soit

2.87%

0,12/4=

0.03

soit

3.00%

par trimestre

Semestre

1,12^(1/2)- 1 =

0.05830052

soit

5.83%

0,12/2=

0.06

soit

6.00%

par semestre

Posté par leblogdesbts à 15:06 - Commentaires […] - Permalien [#]

Partager cet article

Vous aimez ?

0 vote

fiche d'activité BTS CGO

BD d'après excel

Commentaires