rechoperationnelle.pdf

EXERCICES DE

RREECCHHEERRCCHHEE OOPPRRAATTIIOONNNNEELLLLEE ((SSOOLLVVEEUURRSS))

(version 4.0 Rvision 10 du 2014-11-25)

Attention! Nous utilisons majoritairement Microsoft Office Excel pour la rsolution des exercices dans le prsent

document car c'est le logiciel de calcul le plus courant ce jour dans les entreprises.

Sciences.ch Recherche oprationnelle (solveurs)

Serveur d'exercices 2/89

EXERCICE 1.

Niveau: Gymnase (Lyce)

Auteur: Vincent ISOZ ([email protected])

Mots-cls: recherche oprationnelle, choix

nonc:

Un chef de projet connaissant le prix auquel il peut facturer au maximum ses consultants

(concurrence oblige 250.-/h.) et le prix qu'ils cotent en interne (ressource la moins chre 160.-/h.) souhaite atteindre une marge commerciale de 15'000.- pour son futur projet client

ncessitant 600 heures de travail.

Jusqu'o le chef de projet peut-il baisser le montant du tarif horaire vendu au client tout en

cherchant la meilleure ressource interne possible (celle ayant le cot interne le plus lev le niveau le plus expert - avec les contraintes dfinies), pour avoir une marge bnficiaire de

15'000.- ?

Remarque: Evidemment il s'agit d'une simple quation du premier degr que l'on peut

rsoudre la main ou avec l'outil Cible de MS Excel mais l'ide ici est juste de se familiariser

avec le solveur.

Solution:

Dans MS Excel, nous construisons le tableau suivant:

avec dans B4 la relation suivante:

=B1*(B2-B3)

Nous paramtrons le solveur ainsi:



et nous le lanons. Il vient alors comme rsultat au problme:

Nous pouvons donc facturer au minimum 217.50.-/h. au client et prendre un consultant

interne de type Junior B qui nous coterait au plus 192.50.-/h.



EXERCICE 2.



Mots-cls: recherche oprationnelle, optimisation production

nonc:

Supposons qu'une usine fabrique 2 pices P1 et P2 usines dans deux ateliers A1 et A2.

Les temps d'usinage sont pour P1 de 3 heures dans l'atelier A1 et de 6 heures dans l'atelier A2

et pour P2 de 4 heures dans l'atelier A1 et de 3 heures dans l'atelier A2.

Le temps de disponibilit hebdomadaire de l'atelier A1 est de 160 heures et celui de l'atelier

A2 de 180 heures.

La marge bnficiaire est de 1'200.- pour une pice P1 et 1'000.- pour une pice P2.

La question est: Quelle production de chaque type doit-on fabriquer pour maximiser la marge

hebdomadaire?

A rsoudre en utilisant la reprsentation graphique et MS Office Excel!

Solution:

D'abord, il est possible de poser le systme d'inquations:

1: 3 1 4 2 160

2 : 6 1 3 2 180

1, 2 0

A X X

A X X

X X

Ensuite, la fonction conomique:

1200 1 1000 2Z X X

Le trac des deux droites dans MS Excel, donne le polygone des contraintes (c'est que l'on fait

dans les petites classes d'coles):



o nous voyons de suite ou sont les maximums ainsi que l'optimum.

Pour rsoudre le problme dans MS Excel (eh oui! MS Project n'est pas fait pour

l'optimisation ce qui est logique!), crez un tableau du type suivant:

et ensuite, avec le solveur MS Excel, crez les contraintes adaptes du type (attention les

rfrences de cellules ne sont pas donnes correctement ci-dessous afin de ne pas vous

mcher tout le boulot!):



Les solutions seront alors aprs l'excution du solveur:

1 16 . 2 28 .X pcs X pcs



EXERCICE 3.



Mots-cls: recherche oprationnelle, optimisation budget

nonc:

Soit le tableau ci-dessous dans MS Excel:

Auquel correspondent les formules ci-dessous:



Comment rpartir quitablement sur les cellules B12 E12 les 40 000 Francs (valeur saisir

dans les contraintes pour la cellule F12) de budget pour optimiser (maximiser) au mieux les

bnfices (cellule G16) ?

Solution:

Pour rsoudre cet exercice il suffit de lancer le solveur et d'y saisir:

Afin d'obtenir le rsultat ci-dessous:



EXERCICE 4.

Niveau: Universit (Fac)

Auteur: Julien BERTRAND

Mots-cls: recherche oprationnelle, optimisation de charge

nonc:

Une socit fabrique trois types de pices. Le processus de fabrication pour chaque produit

ncessite le passage par trois types de machines. L'ordre de passage par machine est le

suivant:

- Machine 1: pour les oprations de dcoupe du mtal

- Machine 2: pour les oprations de roulage

- Machine 3: pour les oprations de soudage

Les trois ateliers sont regroups par technologie et comprennent chacun un seul type de

machines. Les capacits nettes respectives de ces trois ateliers sont:

- pour l'atelier de dcoupage: 10'000 heures par mois

- pour l'atelier de roulage: 7'000 heures par mois

- pour l'atelier de soudage: 5'000 heures par mois

Les marges dgages par ces trois produits sont de 0.30.- par pices de type 1 appel P1,

0.40.- par pices P2, 0.20.- par pice P3.

Les temps unitaires de fabrication par produit et par atelier sont exprims en heures et sont

donnes ci-dessous par atelier et par type de pice:

Atelier 1 Atelier 2 Atelier 3

P1 0.01 0.005 0.001

P2 0.002 0.01 0

P3 0 0.02 0.1

Pour le mois suivant, les commandes fermes en portefeuille reprsentent une quantit de

500'000 pices P1, 250'000 pices P2 et 50'000 pices P3. Ces quantits sont produire et

livre pour le mois.

Le problme consiste trouver la quantit mensuelle optimale fabriquer par produit de

faon maximiser la marge globale.

Hypothses:



H1. Le taux de rebut est suppos nul

H2. La main d'uvre n'est pas interchangeable

H3. Il n'existe pas d'encours ni de stock de scurit.

Solution:

D'abord, un calcul simple nous donne en termes de charge:

1: 500 '000 0.01 250'000 0.02 10'000 [ ]

2 : 500 '000 0.005 250'000 0.01 50'000 0.02 6 '000 [ ]

3 : 500 '000 0.001 50'000 0.1 5'500 [ ]

A h

A h

A h

Nous constatons don que l'atelier 3 serait en surcharge de 500 heures, ce qui ne permettrait

pas de raliser la totalit du carnet de commandes du mois sans avoir augmenter la capacit

de production de cet atelier.

L'atelier 2 serait en sous-charge de 1'000 heures de travail et l'atelier 1 serait l'quilibre de

charge.

Utilisons maintenant MS Excel pour optimiser la marge en connaissant cet tat des faits:

avec les formules suivantes:



avec les paramtres suivants du solveur:

Nous obtenons alors:



Les rsultats obtenus ne satisfont pas le carnet de commandes. Il reste, en effet, 5'000 P3

livrer pour le mois.

Trois choix au moins sont possible, soit livrer certains clients le mois suivant, soit augmenter

la capacit de produit, soit jouer avec les clients privilgis en priorit.



EXERCICE 5.


Auteur: Vincent ISOZ

Mots-cls: recherche oprationnelle, optimisation d'allocation (algorithme hongrois)

Nous avons mentionn dans le cours thorique l'algorithme Hongrois pour l'affectation d'une

ressource un tche/vnement/machine (1 1). Le cas ci-dessous est pour une matrice carre

comme l'habitude avec les exemples acadmiques. Si le nombre de ressources n'est pas gal

au nombre de tches/vnements/machines il suffit d'adapter la matrice avec des

lignes/colonnes en plus ayant des composantes nulles.

L'ide est de reprendre le mme exemple que dans le cours thorique sachant comme

l'habitude que cela suppose les tches tant en parallles et que pour de petites dimensions le

calcul peut se faire bien videmment la main...

Nous crons d'abord le contenu suivant sur une feuille de notre choix:



Avec les fonctions/formules suivantes:

Nous paramtrons le solveur de la manire suivante:



Et si nous l'excutons nous obtenons bien le rsultat conforme celui fait la main:



EXERCICE 6.



Mots-cls: recherche oprationnelle, optimisation mlanges

nonc:

Une entreprise sidrurgique a reu commande de cinq tonnes d'acier destin la fabrication

de carrosseries automobiles. Les teneurs de cet acier en diffrents lments chimiques doivent

se trouver dans les fourchettes suivantes:

Elment chimique Teneur minimale

Teneur

maximale

Carbone ( C ) 2% 3%

Cuivre ( Cu ) 0.40% 0.60%

Manganse ( Mn ) 1.20% 1.65%

Pour fabriquer cet acier, l'entreprise dispose de sept matires premires dont les teneurs, les

quantits disponibles et les cours d'achat sont donns dans le tableau suivant:

Matire

premire

Teneur

en C (%)

Teneur

en Cu (%)

Teneur

en Mn (%)

Stock disponible

(Kg) Cot (.-/Kg)

Ferraille 1 2.5 0 1.3 4000 0.2

Ferraille 2 3 0 0.8 3000 0.25

Ferraille 3 0 0.3 0 6000 0.15

Ferraile 4 0 90 0 5000 0.22

Ferraile 5 0 96 4 2000 0.26

Ferraille 6 0 0.4 1.2 3000 0.2

Ferraille 7 0 0.6 0 2500 0.17

Dterminer les quantits de ferrailles mlanger pour obtenir la commande souhaite par le

client au meilleur cot.

Le problme est rsoudre avec MS Office Excel!

Solution:



Pour rsoudre ce problme le plus simple est de construire dans MS Excel une table du type

de la page suivante:

Avec les formules suivantes:

Dans D12: =SOMMEPROD($C$3:$C$9;D3:D9)/$D$13

Dans E12: =SOMMEPROD($C$3:$C$9;E3:E9)/$D$13

Dans F12: =SOMMEPROD($C$3:$C$9;F3:F9)/$D$13

Dans H12: =SOMMEPROD($C$3:$C$9;H3:H9)

Dans D14: =SOMME(C3:C9)

Ensuite, il faut lancer le solveur avec les paramtres suivants:

Pour obtenir les rsultats:



EXERCICE 7.



Mots-cls: recherche oprationnelle, optimisation stocks

nonc:

Une entreprise dispose d'une usine et de cinq entrepts implants en fonction d'une clientle

rgionale distribuer et chacun est considr comme un centre de profit.

Les marges par produit sont diffrentes par rgion.

Pour le produit PA, les marges exprimes par rapport cot de revient du produit sont

respectivement de 120%, 130%, 120%, 150% et 140% pour les entrepts E1, E2, E3, E4, E5.

Le cot de revient usine est de 1'000.- par unit de produit PA fabriqu.

Les prvisions des ventes pour la semaine venir sont de:

- 2'500 PA pour l'entrept E1



- 500 PA pour l'entrept E4


Le stock initial en PA est nul dans chaque entrept. Le stock actuel de l'usine est de 7'000 PA.

Il n'est pas possible de fabriquer les produits manquant dans le dlai restant, d'ores et dj,

une perte prvisionnelle de chiffre d'affaires est constate.

Pour minimiser cette perte et pour maximiser le chiffre d'affaires total, une rpartition

optimale des quantits fournir aux diffrents entrepts est rechercher.

A ce jour, le volume disponible dans les diffrents entrepts est de:

- 1'500 [m3] pour l'entrept E1



- 200 [m3] pour l'entrept E4

- 600 [m3] pour l'entrept E1

Le volume d'une unit de produit PA est de 0.5 [m3].



Optimisez le problme avec MS Excel et OpenOffice Calc et discuter des diffrences

obtenues.

Solution:

D'abord, avec MS Excel nous crons la feuille suivante:




On para mtrise le solveur:

avec la liste des contraintes complte suivante:

On lance la recherche et MS Excel trouve:



Ce qui satisfait les contraintes mais pas la demande

La fonction conomique vaut 8'910'000.-

Avec OpenOffice Calc, nous avons la mme feuille mais le solveur sera paramtr ainsi:



avec la liste de toutes les contraintes:

En lanant la recherche nous obtenons:



Nous voyons donc que la fonction conomique la mme valeur mais que la rpartition des

livraisons ne se fera pas de la mme manire. Ainsi les deux logiciels donnent une rponse

optimale mais pas identique qui joue entre les entrepts E1 et E3 qui ont la mme marge et suffisamment de volume pour stocker tout ce qui doit arriver.

Il est dommage qu'aucun des deux logiciels n'indique qu'il y ait plusieurs solutions (mme si

on pouvait s'en douter la vue de l'nonc).



EXERCICE 8.



Mots-cls: recherche oprationnelle, optimisation logistique, problme de transport

nonc:

Il s'agit de livrer un produit trois clients europens (Client 1, 2 et 3) d'une entreprise qui

dispose de deux usines de fabrication (Usine1 et 2). Le transport est assur par un systme

logistique qui utilise un rseau de 5 plates-formes (PF1 PF5). Les capacits de transport sur

chacun des liens du rseau sont limites aux valeurs donnes dans le graphe suivant:

Les quantits de produit disponibles en stock dans les usines sont respectivement de 35 pour

Usine 1 et 25 pour Usine 2. Les demandes des trois clients sont respectivement de 15 pour

Client 1 et pour Client 2 et 20 pour Client 3.

Trouver un programme de transport qui satisfasse la demande des clients

Remarque: Il s'agit alors dun problme de flot maximal que nous pouvons modliser en indiquant que la quantit de produit qui transite sur chaque arc doit rester infrieure sa

capacit, que les flux sont conservs dans tous les sommets (sauf Source et Puits), et que la

somme des flux arrivant au Puits doit tre maximale.




Solution:

Une technique possible consiste crer un tableau de flot du type suivant:

et plus bas un autre tableau du type:

o dans Total reu nous avons les somme des colonnes et dans Total mis la somme des

lignes.

La cellule Flot reprsente la somme des puits maximiser.

Le solveur doit alors simplement (mais il fallait y penser) tre configur tel que prsent ci-

dessous:



Le solveur exprime ici simplement que:

1. Nous n'avons pas le droit de dpasser les maximaux des capacits des lignes de transport

spcifi dans le premier tableau via la contrainte: $C$19:$N$30



EXERCICE 9.

Niveau: Lyce (Gymnase)


Mots-cls: recherche oprationnelle, optimisation marge

nonc:

Un fabricant de montres fait un bnfice de 15.- sur chaque montre d'une gamme 1 et un

bnfice de 8.- sur chaque montre de gamme 2.

Pour satisfaire la demande des vendeurs, la production journalire de montres de gamme 1

devrait se situer entre 30 et 80, et la production journalire de montres de gamme 2 entre 10 et

30.

Pour maintenir une bonne qualit, le nombre total de montres ne devrait pas dpasser 80 par

jour.

Combien de montres de chaque type faudrait-il fabriquer quotidiennement pour raliser un

bnfice maximum?


Remarque: Le problme peut tre rsolu trs intuitivement sans le solveur mais bon pour le plaisir

Solution:

Nous construisons par exemple la feuille suivante:

avec les relations suivantes:



et nous configurons le solveur ainsi:

ce qui nous donne:

et qui est la solution du problme!



EXERCICE 10.



Mots-cls: recherche oprationnelle, optimisation du choix des fournisseurs

nonc:

Une socit importatrice de caf achte des lots de grains de caf en vrac, puis les spare en

grains de premier choix, ordinaires et inutilisables.

La socit a besoin d'au moins 280 tonnes de grains de premier choix et 200 tonnes de grains

ordinaires.

Elle peut acheter des grains non tris volont chez deux fournisseurs qui contiennent les

pourcentages suivants de grains de premier choix, ordinaires et inutilisables:

Fournisseur 1er choix Ordinaire Inutilisable

A 20% 50% 30%

B 40% 20% 40%

Chez le fournisseur A le cot la tonne est de 125.- et chez le fournisseur B de 200.-.

Trouvez la combinaison optimale permettant de satisfaire les besoins tout en investissant un

minimum d'argent.

Solution:

Nous crons la feuille suivante:



Avec les relations suivantes:

et nous configurons le solveur ainsi:



ce qui nous donne:




EXERCICE 11.



Mots-cls: recherche oprationnelle, optimisation en investissement matriel

nonc:

Une entreprise dsire acqurir des fraiseuses manuelles (FM) et automatises (FA) pour sa

production.

L'entreprise ne peut dpenser plus de 200'000.- pour les machines et pas plus de 1'000.- par

mois pour la maintenance.

Les fraiseuses manuelles cotent 20'000.-/pice et en moyenne 200.- par mois pour la

maintenance.

Les fraiseuses automatises cotent 40'000.-/pice et en moyenne 150.- par mois pour la

maintenance.

Sachant que chaque fraiseuse manuelle peut produire 15 units par mois et chaque

automatise 25 par mois, trouver le nombre de chacune acheter pour maximiser la capacit

de production.

MS Excel ne pouvant rsoudre ce problme ce jour, nous utiliserons OpenOffice.org.

Solution:

Nous crons la feuille suivante:



avec les relations:



et nous configurons le solveur de OpenOffice.org Calc de la manire suivante:

ce qui nous donne:




EXERCICE 12.



Mots-cls: recherche oprationnelle, MRP (Material Requirements Planning)

nonc:

Dans une entreprise, nous avons les donnes suivantes:

Quelle est la meilleure manire de rpartir la production sur les 6 mois afin de minimiser le

cot total sachant que nous avons un stock initial de 50 units?

Solution:

Pour trouver la solution, dans un premier temps, nous construisons un tableau avec la

production effective et les stocks effectifs qui seront calculs:

Ensuite, nous devons aussi crire la fonction de cot optimiser:



Ensuite, nous lanons le solveur avec les paramtres suivants:

et nous obtenons:



et voil pour un MRP-0 (Material Requirements Planning).



EXERCICE 13.



Mots-cls: recherche oprationnelle, optimisation de fonction de plan d'exprience

nonc:

Une entreprise produit des aliments dont une acidit est produite par des ferments qui

transforment le lactose en acide lactique. Nous avons souhait diminuer le got acide en

jouant sur trois facteurs:

1. Le taux de dilution (rapport volumique eau ajoute/lait brut): [0.5%,2%] 1x

2. Le pH (suivant quantit de stabilisant inject): [5,6] 2x

3. Taux de concentration du lait (rapport volumique lait brut/stabilis): [1.5%,2.5%] 3x

A l'aide d'une rgression linaire multiple, nous avons obtenu l'quation quadratique complte

suivante de l'tude de l'appauvrissement en % en nous basant sur des valeurs factorielles des

intervalles:

2 2 21 2 3 1 2 1 3 2 3 1 2 350.1 0.07 0.11 0.06 4.22 0.32 2.2 0.07 3.6 9.1y x x x x x x x x x x x x

Le but est de maximiser y (c'est--dire l'appauvrissement en acide) car le produit sera meilleur

si l'appauvrissement est lev.

Solution:

Nous partons du tableau MS Excel suivant dans lequel nous avons utilise la relation de

transformation des units centres rduites dans la colonne C (cf. chapitre de Gnie

Industriel):

Avec les paramtres suivants du solveur:



Ce qui donne:



EXERCICE 14.

Niveau: Fac (Universit)


Mots-cls: recherche oprationnelle, optimisation de l'ordonnancement, algorithme

volutionnaire

nonc:

Un employ une srie de tches indpendantes excut et pour lesquelles les informations

suivantes lui sont communiques par son systme informatique:

Nous souhaiterions savoir quel doit tre l'ordonnancement des tches afin de minimiser le

retard du travail (si retard il y a...).

Comme il y a:

6! 720

combinaisons possibles, nous n'allons pas faire cela la main...

Solution:

D'abord, il faut oublier de trouver une solution et cela ne sera pas possible avec MS Excel

2007 ou antrieur... car il y manque dans le solveur une contrainte nomme "tous diffrents".

Nous utiliserons donc MS Excel 2010.

Pour rsoudre ce problme avec MS Excel 2010, nous pouvons d'abord prparer un tableau du

type suivant quelques lignes plus bas dans la mme feuille:



o l'ide est d'avoir dans le tableau en haut l'ordonnancement original (pour en garder une

trace et comparer le avant/aprs) et en bas le tableau optimis.

Voici les formules classiques explicites du deuxime tableau:

Et ensuite nous lanons le solveur avec l'algorithme volutionnaire (normalement ce problme

a peu de chances de trouver une solution avec l'algorithme GRG non linaire, mais nous

constatons que MS Excel 2010 en trouvera quand mme une, car nous le souponons d'utiliser

quand mme l'algorithme volutionnaire):



Ce qui donne:

Et il ne faut pas tre surpris si pour l'alorithme volutionnaire, chaque excution, et dans le

cas particulier prsent, l'ordre des 4 premires tches peut tre permut puisqu'elles

n'influencent pas sur le rsultat final quel que soit leur ordonnancement.



EXERCICE 15.



Mots-cls: recherche oprationnelle, k-means, algorithme volutionnaire

nonc:

Considrons les donnes suivantes:

Nous souhaitons utiliser le solveur volutionnaire de MS Excel pour trouver trois centrodes

et comparer le rsultat un logiciel spcialsi comme Minitab ou Tanagra.

Solution:

Nous crons sur la mme feuille le tableau suivant:



avec les formules triviales pour les trois colonnes N, O, P o l'on retrouve la norme de la

distance euclidienne:



et les formules suivantes pour les deux colonnes restantes:

Ensuite, nous lanons le solveur de MS Excel (dans le prsent cas il s'agit de la version 2010

du logiciel) avec les paramtres suivants en faisant bien attention prendre l'algorithme

volutionnaire ce qui fait que nous assumons qu' chaque fois que nous pourrions avoir un

rsultat diffrent:



et nous avons en lanant le solveur:



avec le tableau:



Des logiciels spcialiss de statistiques comme Minitab ou de Data Mining comme Tanagra

donnent en comparaison les valeurs des 3 centres et qui sont une fois reportes dans

MS Excel:



avec le tableau:



La diffrence s'explique assez simplement! Un logiciel comme MS Excel minimise la

distance des points aux centres mais est incapable en mme temps de maximiser la distance

entre les centres. Par contre les logiciels de statistiques ont les algorithmes qu'il faut pour cela.

Minitab ne donne pas de graphique mais Tanagra lui donne:



EXERCICE 16.



Mots-cls: recherche oprationnelle, nomographe, algorithme volutionnaire

nonc:

Dans le domaine du contrle qualit la rception, il est courant pour le respect des normes

international de faire appel un nomographe pour calcul la taille du lot qui doit tre contrle

et le nombre d'chantillons qu'il faut en tirer en fonction du niveau de qualit acceptable.

Malheureusement:

1. La lecture du nomographe est pas toujours aise (et pas amusante)

2. Il n'y avait pas de solution avec le solveur des versions antrieures Excel 2010

Voyons le problme:

Un lot de bouteilles est livr sous forme de lots de 10'000 units correspondant N. Nous

cherchons mettre en place un plan de contrle de la rception par attributs avec la

probabilit cumule (risque) de 1% que le client rejette tort le lot avec moins de 2.5% ( p ) de non conformes. De son ct le client souhaite une probabilit cumule (risque) de

10% d'accepter tort un lot avec plus de 5% ( p ) de non conformes.

Solution:

Le but est de dterminer donc la valeur de A et de n qui satisfont le systme du systme

suivant:

Il nous faudra d'abord transforme cela en un seul systme car le solveur n'a qu'une valeur

cible. Nous faisons alors la somme:

0 0

1

Np N NpNp N NpA Ak n k k n k

N Nk kn n

C C C C

C C

Soit dans notre cas:



10'000 2.5% 10'000 10'000 2.5% 10'000 5% 10'000 10'00015%

10'000 10'0000 0

1 1% 10% 109%A A

k n k k n k

k kn n

C C C C

C C

Soit dans le langage MS Excel cela donnera:

Soit explicitement:

Maintenant, pour rsourdre ce problme, il est clair et vidant qu'il faudra passer par un

algorithme volutionnaire. Nous lanons alors le solveur avec les paramtres suivants (aprs

plusieurs heures d'essais pour tre honnte):



La difficult principale est de savoir que de par la convergence vers une loi binomiale pour N

grand (voir nomographe), n ne dpassera probablement jamais les 1'000 units et n les 150

tant donn les niveaux de risques d'usage dans les entreprises (raison pour laquelle le

nomographe s'arrte 1000 pour n et 150 pour A). Donc il faut adapter les contraintes de

$B$12 et $B$9 en consquence car sinon vous pouvez attendre parfois une journe entire de

slection alatoire sans avoir de rsultat.

De plus, le solveur volutionnaire ne trouvera tel quel jamais de rponse exacte pour la a

simple raison qu'il n'y en pas relativement au niveau de prcision impos par dfaut dans les

option du solveur.

Il faut donc petit petit augmenter les valeurs de la prcision des contraintes. Dans le cas

prsent, des solutions ralisables sont obtenus partir d'une prcision sur les contraintes de

0.00001:



En lanant le solveur, nous souvent des rsultats diffrents ou gaux mais proche mais proche

de que ce que nous obtenons avec le nomographe binomial (A 30 et n 700).

Voici les rsultats obtenus avec 9 essais diffrents:

Essai B9 (A) B12 (n)

1 24 623

2 23 603

3 23 603

4 20 533

5 24 623

6 27 691

7 26 667

8 22 579

9 27 691

Dans le doute, prenez le rsultat qui maximise n et A.



EXERCICE 17.



Mots-cls: Mthode CRAFT

nonc:

Soit trois centre de production A, B, C avec des flux de pices de mme cot en bidirectionnel

qui font donc un aller-retour (videmment nous pourrions choisir d'avoir des flux que

unidirectionnels ou bidirectionnels partiels):

Nous pouvons positionner ces trois centres de production en trois lieux avec des distances

d'aller que nous supposerons diffrentes des distances du retour (pour compliquer un peu

l'exemple):

Ainsi, la distance du lieu 1 au lieu 2 est de 15 (en centaines de kilomtres) l'aller mais de 10

au retour. La distance du lieu 1 au lieu 3 est de 30 (toujours en centaines de kilomtres) mais

de 20 au retour et enfin la distance du lieu 2 au lieu 3 est de 40 l'aller mais de 25 au retour.



La question est: O positionner les centres de production pour minimiser le cot des

distances?

Solution:

Bien videmment, le problme ici prsent peut tre rsolu la main car il y a que 3!=6

possibilits de placer les centres. Mais les calculs croissent trs vite puisque le nombre de

possibilits est gal la factorielle du nombre de centres.

Pour rsoudre ceci dans le cas gnral avec un tableur et sans programmation, nous

construisons d'abord un endroit la liste des chemins possibles numrots (il n'y pas de

formules derrire) de H5 H7 qui serviront des variables note problme d'optimisation et

qui correspondent chacun une des distances d'aller (donc in extenso nous dduisons la

distance de retour associe d'o le fait qu'il soit inutile d'indexer les distance de retour):

Ensuite, nous construisons la matrice des distances (qui sera dynamique puisque c'est elle que

nous cherchons optimiser), la matrice des flux (qui sera statique puisque impose) et enfin la

matrice des produits composante par composante et la somme correspondante que nous

cherchons minimiser:



Le lecteur aura remarqu que la partie suprieure de la matrice des distances optimales tant

simplement le retour de l'aller correspondant, nous devons faire en sort que la formule

reprenne l'index du chemin d'aller incrment de 3 lignes pour avoir le chemin du retour

associ

Ensuite, nous lanons le solveur avec les paramtres suivants:



aprs une petite attente, nous obtenons:



Donc en d'autres termes il faut placer le centre de production A en 2, le centre de production B

en 3 et enfin le centre de production C en 1.



EXERCICE 18.


Auteur: Alain BOITEL

Mots-cls: Frontire de Markowitz

nonc:

Considrons trois titres composants un portefeuille en proportions gales (que nous

supposerons dans des proportions gales dans le portefeuille) et les n observations de leur

rendement ,i jR saisis dans MS Excel (la composant j pouvant tre vu comme une priode

temporelle):

Dterminez la frontire d'efficience du portefeuille selon le modle de Markowitz ainsi que la

C.M.L. et la pondration des actifs qui minimise la variance pour une esprance maximum

pour une portefeuille compos d'un actif sans risque d'un rendement de 0.22.

Remarque:

Harry Max Markowitz (n le 24 aot 1927 Chicago) est un conomiste amricain. laurat du

Prix Nobel d'conomie en 1990. C'est donc l'auteur du modle de diversification efficiente des

portefeuilles d'actifs financiers.

Markowitz dveloppa la base mathmatique et les consquences de cette analyse dans sa

thse, soutenue en 1954. Milton Friedman, qui faisait partie du jury, lui aurait dclar:

"Harry, ceci n'est pas une thse d'conomie, et nous ne pouvons vous donner un doctorat

d'conomie pour quelque chose qui n'est pas de l'conomie. Ce n'est pas des maths, ce n'est

pas de l'conomie, ce n'est mme pas de la gestion."

Solution:

Dessous la table donne prcdemment nous allons crer dans MS Excel le tableau contenant

les proportions iX des titres (que nous supposerons quidistribues, soit 1/3), nous

afficherons la moyenne du rendement i calcule bien videmment selon l'estimateur:



,

1

( 2 : 6)

( 2 : 6)

( 2 : 6)

n

i j

j

i i

R Moyenne B B

E R Moyenne C Cn

Moyenne D D

et la variance 2i calcule pour chaque titre par l'estimateur:

2

,

12

( 2 : 6)

( 2 : 6)1

( 2 : 6)

n

i j i

j

i

R Var B B

Var C Cn

Var D D

Ce qui nous donne le tableau suivant dans MS Excel:

Soit sous forme dtaille dans MS Excel toujours:

Nous devons maintenant calculer le rendement moyen du portefeuille selon:

1 1 2 1 3 11

n

p i i

i

E R X R X X X

Cette relation est un peu longue saisir, et le sera davantage si nous avons un nombre bien

plus important de titres.

Dans notre cas, il s'agit de faire la somme des produits terme terme de deux plages de

cellules ( iX et i ) ayant la mme dimension (mme nombre de lignes et mme nombre de

colonnes). Nous pouvons alors utiliser la fonction suivant dans MS Excel:

SOMMEPROD(B14:D14;B15:D15)

Pour la variance du portefeuille, c'est un peu plus compliqu puisqu'il s'agira de calculer:



2 2

1

2 2

1

2 cov ,

2 cov ,

n

p p i i i j j i

i i j

n

i i i j j i

i i j

V R R X V R X X R R

X X X R R

La relation dveloppe dans notre cas particulier donne:

2 2 2 2 2 2 21 1 2 2 3 3 1 2 1 2

1 3 1 3 2 3 2 3

2 cov ,

2 cov , 2 cov ,

pR X X X X X R R

X X R R X X R R

L'astuce pour appliquer ceci dans MS Excel consiste utiliser l'algbre linaire et crire cette

relation sous forme matricielle comme nous l'avons dmontr:

2 Tp ijR X c X

Ce qui quivaut dans MS Excel crire:

=SOMMEPROD(PRODUITMAT(B14:D14;G14:I16);B14:D14)

Soit sous forme matricielle explicite:

21 2 1 3 1 1

2 21 2 3 1 2 2 3 2 2

231 3 2 3 3

cov , cov ,

cov , cov ,

cov , cov ,

p

R R R R X

R X X X R R R R X

XR R R R

Donc en se basant sur les tableaux prcdents, il est simple dans MS Excel d'obtenir la

matrice de covariance:

Soit sous forme dtaille dans MS Excel toujours:



Rappel: La matrice des covariances est symtrique (cf. chapitre de Statistiques).

Et pour l'esprance et la variance du portefeuille nous aurons donc le tableau suivant:

en appliquant donc les relations susmentionnes:

Le problme maintenant est de dterminer pour un rendement du portefeuille fix (B19), les

proportions des diffrents titres qui minimisent le risque.

Aprs avoir ajout les deux cellules B24 (rendement espr/attendu du portefeuille) et B25

(nombre total des parts du portefeuille):

Nous devons donc maintenant rsoudre le problme d'optimisation non linaire:

2min :

0.2

1

p

p

i

R

E R

X

et ceci ne peut que se faire (simplement) l'aide du solveur:



Ce que nous allons faire l'aide du solveur est de chercher et reporter les solutions pour des

rendements de 0.2 0.245 par pas de 0.05. A chaque rsultat, nous noterons le numro de

l'itration, la variance du portefeuille 2 pR et l'esprance de rendement pE R qui tait exige. Cela devrait donner (bon il faudrait automatiser dans l'idal la procdure par du

VBA):

Ce qui donne la frontire efficiente de Markowitz suivante sous forme graphique dans

MS Excel:



Maintenant il est ais avec MS Excel de dterminer l'quation de cette parabole en utilisant

l'outil d'interpolation (nous sommes obligs dans MS Excel de tourner la parabole pour

cela):

Maintenant, nous allons dterminer la C.M.L (capital market line) qui est la droite forme par

l'ensemble des portefeuilles composs de l'actif sans risque, d'une part, et du portefeuille de

march, d'autre part. Par construction, elle associe chaque niveau de risque, la rentabilit

espre la plus lev.



Nous allons pour dterminer cette droite avec MS Excel nous fixer dans un premier temps un

taux de rendement sans risque que nous noterons fR et que nous prendrons arbitrairement

comme valant 0.22. Nous avons donc la courbe de Markowitz d'quation:

2 218.795 8.389 0.9384y x x ax bx c

et la droite:

' 'y a x b

avec la condition:

''

0.22

ba

Nous avons alors deux quations connues deux inconnues pour rsoudre ce problme

(l'intersection de la droite et la parabole pour la premire et l'galit de la pente de la parabole

et de la droite au point d'intersection):

2 ' '

2 '

M M M

M

ax bx c a x b

ax b a

La deuxime quation nous donne:

'

' 0.22

2 2M

bb

a bx

a a

Inject dans la premire quation:



2' ' '

'0.22 0.22 0.22 '2 2 0.22 2

b b bb b b

ba b c b

a a a

Si nous rsolvons ce polynme du deuxime degr nous avons deux solutions relles (Excel

n'arrive pas dterminer les racines de ce polynme):

1 2' -0.6822748631 ' 0.1207634890b b

La solution 2 est liminer (nous le savons en essayant de la prendre comme solution). Nous

avons donc:

'' -0.6822748631 ' =0.3101249378

0.22

bb a

Ce qui donne sous forme graphique:

Soit sous forme traditionnelle:



Il vient aussi immdiatement:

'

0.22 0.23142657462

M

bb

xa

Ainsi, en rutilisant le solveur comme plus haut mais avec cette nouvelle valeur pour

l'esprance, nous obtenons pour un portefeuille du march compos d'un actif sans risque de

rendement 0.22, un rendement global efficient de 0.2314276 avec la composition suivante du portefeuille donne par le solveur:

1

2

3

0.055

1.079

0.024

X

X

X



EXERCICE 19.


Auteur: Alain BOITEL

Mots-cls: Frontire de Sharpe

nonc:

Considrons trois titres composants un portefeuille en proportions gales et les n observations

de leur rendement ,i jR saisis dans MS Excel. Ces rendements seront compars un indice de

rfrence I qui sera le rendement d'un portefeuille de march de rfrence MPF :

Le but se de dterminer la frontire d'efficience du portefeuille avec le modle de Sharpe.

Solution:

En dtail sous forme graphique voici d'abord les bta (rendement de l'actif en fonction du

rendement du portefeuille de march/indice de rfrence) obtenus par MS Excel:



et le tableau de construction suivant pour le calcul des bta, la variance et l'esprance des

diffrents titres:

Voici les dtails du calcul (remarquez que les bta sont obtenus l'aide d'une simple

rgression linaire avec l'indice de rfrence qui est le portefeuille et les autres paramtres

avec les estimateurs non biaiss):

L'esprance du rendement du portefeuille compos des trois titres est facile calculer puisque

nous avons leur rendement. Donc:

1 1 2 1 3 11

n

p i i

i

E R X R X X X

Ce qui donne sous MS Excel:

Soit de manire dtaille:

Maintenant, il nous faut calculer l'esprance en utilisant la relation dmontre dans la partie

thorique des paragraphes prcdents:



2 2 Tp p I ijV R R X X

avec pour rappel dans notre cas particulier:

21

1 2 1 32

22

, 2 1 2 32

22

3 1 3 2 2

I

i j

I

I

avec dans notre exemple 2 0.039I (cellule B13).

Soit sous forme dveloppe pour notre exemple:

2 2 2 2 2 2 21 1 2 2 3 32 2 2

1 2 1 2 1 3 1 3 2 3 2 32 2 2

p

I I I

R X X X

X X X X X X

Ce qui donne dans MS Excel pour notre matrice des bta:

Soit sous forme dveloppe (la matrice est symtrique):

Et finalement le couple variance/esprance du portefeuille est donn par:

Soit sous forme dtaille:



Une fois ceci fait, nous procdons comme pour la frontire de Markowitz. Nous utilisons le

solveur en minimisant la variance tout en imposant une esprance et une contrainte comme

quoi la somme des parts des actifs financiers est gale l'unit:

Ce qui donne le tableau variance/rendement suivant ( comparer avec le mme tableau de

Markowitz):

et le graphique suivant (comparaison directe avec Markowitz mise en vidence):



La suite de l'exercice (C.M.L.) se fait de la mme manire que dans le modle de Markowitz.



EXERCICE 20.



Mots-cls: Modle logistique, Lissage exponentiel

nonc:

Considrons le tableau suivant fait avec MS Excel (les ventes sont en centaines de millier

d'units):

et le graphique associ:



qui pourrait tre jug comme linaire suivant quel moment commence l'analyse descriptive

des ventes dans l'entreprise.

Dterminer le modle thorique prdictif des ventes avec le modle logistique linaris, le

modle logistique optimis et le lissage exponentiel.

Solution:

Pour dterminer le modle thorique, nous allons linariser l'quation logistique en utilisant

un seuil hypothtique (objectif de ventes du march) 800.

Donc:

Soit calculer la nouvelle variable expliquer:

et le modle linaire s'crit donc:

avec donc:



Soit:

Dans notre exemple, la rgression linaire (cf. chapitre de Mthodes Numriques) donne:

0.2247 2.7908ty t

Nous avons alors immdiatement:

800

1 16.294 0.7988t t

y

Soit sous forme graphique:

avec ce modle formel, nous avons une somme des carrs des carts entre les mesures et le

modle (cf. chapitre de Statistique) de:

3520.828SSE

Maintenant, entrons ces donnes dans MS Excel sous la forme suivante:



avec la structure suivante:



Si nous lanons le solveur avec les paramtres suivants:



Ce qui donne:

Soit:

717.94

1 14.7744 0.7849t t

y

avec:

698.511SSE

soit nettement infrieur notre approche utilisant la rgression linaire et donc meilleur.

Graphiquement cela donne:



Nous voyons nettement que le modle du solveur (modle numrique) est meilleur que le

modle formel donn par une rgression linaire!

Maintenant, comparons au lissage exponentiel. Pour cela crez une nouvelle colonne

comme indiqu ci-dessous:



et lancez l'outil de lissage exponentiel (exponential smoothing) de l'utilitaire d'analyse de

MS Excel:

et validez par OK. Vous aurez alors:



Nous voyons nettement l'infriorit du modle de lissage par rapport au modle logistique et

ce mme graphiquement (bon objectivement il est injuste des les comparer car ils n'ont

absolument pas les mmes fondements mathmatiques):



EXERCICE 21.



Mots-cls: Dfaillance Weibull

nonc:

Nous avons dans MS Excel les donnes suivantes reprsentant les dures de vies de sept

machines donnes en annes:

Nous avons calcul dans B13 la moyenne arithmtique (l'esprance) et dans B14 l'estimateur

de maximum de vraisemblance de l'cart-type de la loi Normale. (hmm):

Nous voulons modliser la dure de vie par une loi de Weibull de paramtres:

0, ?, ?

Nous demandons:

1. De dterminer , en utilisant la mthode de Lloyd et Lipov

2. Dterminer MUT MTBF (suppose temps d'arrt pour rparation ngligeable)

3. De calculer la probabilit cumule R que la machine tienne le coup plus de 20 mois.

4. La probabilit cumule F que la machine tombe en panne entre le 15 et 30me

mois.

5. La dure limite de garantie minimale assurant une probabilit cumule de fonctionnement

(fiabilit) de 95%.

Solution:

S1. Nous avons dmontr dans le chapitre Technique de Gestion que pour 0 :



1

1( ) 1E X

et:

2

2 21 1

2 1

Nous crons donc une zone dans la feuille Excel avec la structure suivante:

Avec les formules suivantes:

La fonction Gamma ( ne pas confondre avec la loi Gamma!) n'existant pas dans MS Excel

nous devons prendre l'exponentielle de la fonction du logarithme nprien de la fonction

Gamma qui elle est disponible O_o.

Une fois ceci fait, nous allons demander l'aide du solveur de MS Excel de dterminer les

valeurs de E2 et E3 afin de faire correspondre l'esprance et l'cart-type de la loi de Weibull

aux valeurs obtenues dans B13 et B14.

Mais le solveur a besoin d'un objectif et il peut en avoir qu'un. Nous devons donc jouer avec

des paramtres supplmentaires qui en tant qu'objectif unique imposeront la dtermination de

E2 et E3. Nous allons pour cela jouer avec la variation entre l'esprance calcule par le

solveur et l'esprance dtermine exprimentalement et idem avec l'cart-type. Cela nous

donne:




Nous pouvons maintenant lancer le solveur avec les paramtres:

L'excution du solveur donne:

E2 3.59

E3 20.7

S2. L'excution du solveur donne aussi:

1

1( ) 1 18.65E X MUT MTTF

S3. La probabilit que la machine tienne le coup plus de 20 mois est alors donne par:

R=1-WEIBULL(20;beta;nu;1)=58.67%



et donc qu'elle tienne le coup au moins 20 mois:

=WEIBULL(20;beta;nu;1)=41.32%

S4. La probabilit que la machine tombe en panne entre le 15 et 30me

mois:

F=WEIBULL(30;beta;nu;1)- WEIBULL(15;beta;nu;1)=70.78%

S5. Pour rpondre cette question nous reprenons les cellules suivantes nous d'abord les

cellules suivantes dans MS Excel nous ayant server pour le calcul de la deuxime partie de la

solution 3 (S3):

Soit avec les formules:

Maintenant nous utilisons l'outil cible de MS Excel dans Outils/Outil cible:

En validant par OK, nous obtenons alors l'cran:

G15=9.07

annes. Donc nous pouvons garantir nos machines 9 annes sans avoir peu de perdre trop

d'argent en les commercialisant.

rechoperationnelle.pdf

Documents