Utilisation de l'outil Valeur Cible - Free
Initiation à la recherche : méthodologie (du S2 « enseignement ») .... Elle permet
toutefois d'affiner, de corriger les choix de modules. Le suivi ..... Descriptif des UE
, des modalités pédagogiques (CM, TD, TP, projet, stages, etc.) ...... et
comparaison formelle avec la méthode du simplexe; Cas des problèmes de
grande taille;.
part of the document
eau 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 dautres termes, à partir de quelle quantité vendue, lentreprise 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
Excel a déterminé que la quantité de 960 produits vendus permettait dobtenir un bénéfice nul. Donc au delà, lactivité de lentreprise devient rentable
Application 2
En utilisant loutils Valeurs cibles, déterminez les résultats de léquation suivante :
12016=12.5x2+0.5x-800
Avec valeurs cibles on trouve UNE solution : 32000
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 dun 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 duvre directe : 1 100 Heures
Capacité de latelier : 700 heures machines
Les consommations unitaire 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ématiques 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 latelier)
x ( 0
Contraintes évidentes y ( 0
z ( 0
ATTENTION
Il serait réducteur de vouloir à tout prix résoudre le système dinéquations aux 3 contraintes techniques car lobjectif est de maximiser le profit (représenté par la fonction économique). Une telle démarche aboutirait à une solution mathématiquement acceptable mais incorrect é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 :
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 :
En D7 =24*x+17*y+15*z
En D9 =26*x+23*y+18*z
En D10 =1.3*x+0.9*y+1.1*z
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 napparaît pas dans le menu Outils cest quil nest pas installé. Pour linstaller, il faut activer le menu Outils Macros complémentaires cocher la case Solveur et valider :
Lassistant solveur se présente ainsi :
Ce que lon doit obtenir après paramétrage est résumé ci-après :
Exemple dajout dune contrainte :
Après avoir cliqué sur Ajouter, on obtient ceci :
Pour la première contrainte, la boîte de dialogue affiche ceci :
Afficher les options :
Cliquez sur le bouton
Vous obtenez ceci :
Validez en cliquant sur le bouton OK
Prêt à déterminer la solution au problème ?
Alors cliquez sur le bouton
A la fin, la boîte de dialogue suivante saffiche :
On peut sélectionner les rapports pour les afficher (ils sont difficiles à analyser). Il ne reste plus quà valider puis à observer le résultat :
Loptimum 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 Suvre 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 Suvre et dheures machine (cest à 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, lentreprise a augmenté de 20% le nombre dheures de MOD (soit 1320 h au lieu de 110) et dheures machine (soit 840 au lieu de 700).
Cette action a telle 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 dun coût (sous contraintes) Application facultative, vous pouvez passer à lapplication 3 pour voir comment utiliser le solveur dans la résolution de systèmes déquations.
Lentreprise ROBIDON doit faire face à une importante commande. Elle doit livrer 83 500 conteneurs et 175 000 bidons.
En une heure, latelier 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 latelier moulage
y-> temps de travail de latelier 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 lentreprise est astreinte à produire AU MOINS 83500 bidons et 175000 conteneurs (doù le sens des inégalités).
Résolution dans EXCEL :
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 dheures optimale dheures de latelier 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 suivants :
10x+5y+2z = 3369
0,5x+3y+1,2z = 1871.4
11x+7y+3z = 4341
Réalisation dans Excel :
La boîte de dialogue du solveur est à paramétrer comme suit :
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 nième degré à n inconnues.
Partie 3 : Régression linéaire sur excel
Données du problème :
Lentreprise latendance.com a réalisé un relevé de son chiffres daffaires pour les 12 premiers mois de lannée. Ce relevé figure ci-après :
MoisCaff127000232000328000434000533500637600739300840000943000104300011455001249000
Activité :
Saisissez ces éléments dans une feuille dun nouveau classeur
A laide des fonctions dexcel, déterminez :
Le coefficient de corrélation entre lévolution des chiffres daffaires et des mois
Le coefficient de pente de la droite dajustement linéaire découlant de ces données (si lajustement est justifié)
La constante b de la droite dajustement linéaire.
Dans le tableau des données, vous ajouterez une troisième colonne pour déterminer les chiffres daffaires ajustés.
Représentez graphiquement la série des chiffres daffaires réels et des chiffres daffaires ajustés.
Conseils : pour les formules, aidez vous de lassistant fonction fonctions statistiques Eléments de corrigé :
Le graphique correspondant :
Parties 4 : Les fonctions financières dExcel
Les fonctions données ci-dessous concerne les emprunts indivis remboursés par annuitésconstantes de fin de période. Certaines fonctions retournant des valeurs négatives, il faut lesfaire précéder d'un signe (Excel considère les remboursements comme des flux négatifs à soustraire du capitalDans les formules ci-dessous nous retiendrons les notations suivantesNotationCo 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)Cn la valeur acquise par une suite d'annuitésa 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 puissancecorrespond à l'accent circonflexe ^ ( 9)Objectif de la formuleFormuleExempleCalcul 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
Périodes
C0=a(1+i)-1+a(1+i)-2+a(1+i)-3+
+ a(1+i)-n
Doù
Capitalisation des annuités
Le problème est cette fois ci de déterminer à léchéance la valeur acquise par les versements
Cn=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
Doù
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 versementTaux équivalentTaux proportionnelMois1,12(1/12) - 1 =0.00948879soit0.95%0,12/12=0.01soit1.00% par mois par moisTrimestre1,12(1/4) - 1 =0.02873734soit2.87%0,12/4=0.03soit3.00% par trimestre par trimestreSemestre1,12(1/2) - 1 =0.05830052soit5.83%0,12/2=0.06soit6.00% par semestre par semestre
Perfectionnement tableur spécial maths © F. REDONNET HYPERLINK "http://www.ticegestion.com" http://www.ticegestion.com
Page PAGE 13/ NUMPAGES 13
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)
Les quantités de produits sont obligatoirement positives ou nulles
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
1) Il faut indiquer ici la cellule contenant la formule de la fonction économique (soit dans lexemple la cellule D7)
2) Il faut définir les variables (x, y et z). Excel recherche automatiquement les variables dans la cellule cible
3) Il faut définir les cellules contenant les formules liées aux contraintes
On définit ici lobjectif envisagé, soit une maximisation
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 dautres 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
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 des équations longues, on peut allonger le temps de réponse, le nombre ditérations, la précision,
Cochez cette case pour voir les résultats intermédiaires au cours des itérations successives. Pas de panique, Excel nira pas (dans lexemple jusquà 100 itérations)
Ça ne fait que commencer !
Cliquez sur continuer, jusquà ce que la solution optimale soit atteinte
Cellule nommée x
Cellule nommée y
=320*x+110*y
=1200*x+130*y
=1400*x+1200*y
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 valeur cible est ici une équation quelconque (dans lexemple, il sagit de la première
Il faut indiquer ici que lon cherche à atteindre une valeur exacte
On indique la valeur du membre de droite de la 1ère équation
EMBED Equation
EMBED Equation
EMBED Equation
EMBED Equation
1
2
3
n
a
a
a
a
a
0
C0
Cn
EMBED Equation
a
a
a
a
a
n
0
1
3
2
??????????????????
Cn
C0
EMBED Equation
