Exercice TD 3.2

Dans la figure 4.30, on voit qu'on a recopié les données dans le tableur (on peut faire un copier-coller de celles-ci), puis on a inséré une colonne pour les quantités (colonne C), qui sont à déterminer, ainsi que leurs bornes 17 et 20 dans les lignes 11 et 12.

On saisit des quantités quelconques de C4 à C7 afin que les formules écrites ensuite donnent d'autres valeurs que 0 (heureusement, il n'est pas nécessaire qu'elles soient une solution du problème !). Les formules sont ensuite tapées dans la partie encadrée. En C8 c'est la somme des quantités « =somme(C4 :C7) ». En D8, la somme des achats pondérés par les quantités « =sommeprod($C4 :$C7 ;D4 :D7) », formule recopiée dans les cellules E8 et F8. En G8, c'est le coût total obtenu en sommant les divers coûts « =somme(D8 :F8) ».

On sélectionne la cellule G8 contenant le cout total, puis on lance le solveur par « Outils>Solveur ». On remplit les divers champs de la fenêtre du solveur en cliquant sur les plages de cellules le cas échéant (voir Fig 4.31).

Puis on clique sur « résoudre ». Et si on choisit de conserver le résultat on obtient le nombre optimal de quantités de chaque type qu'il convient d'acheter avec les coûts et les contraintes spécifiées (Voir Fig. 4.32).

Les solutions proposées donnent un coût total de 62 500€, alors qu'on aurait pu avoir un coût total inférieur en prenant les 17 ordinateurs les moins chers. Bien sûr les contraintes sont à l'origine de cette solution. Mais elles ne sont peut-être pas si rigides que cela. Notamment, l'entreprise accepterait certainement un coût total à peine supérieur, pour avoir 18 ordinateurs au lieu de 17. On peut voir sur la figure 4.32 que les plafonds de coûts ne sont pas atteints. Il reste en trésorerie 1100 € de coûts d'achat, 100 € de coûts d'entretien et autant de fonctionnement, soit 1300€ en tout. Cela ne permet pas d'acheter un nouvel ordinateur (le moins cher est à 3500 €), mais il est peut-être possible d'augmenter les deux derniers plafonds de coûts afin d'avoir d'autres solutions. Une aide à ce type de raisonnement est fournie par les rapports du solveur d'Excel (voir à droite de la figure 4.29 les rubriques « Réponses, sensibilité, limites » du champ « Rapports »). Mais on peut également procéder par essais successifs en changeant quelques cellules et en relançant le solveur sans avoir besoin de remplir à nouveau les champs à condition de se replacer sur la cellule de la valeur à optimiser.