Tableur EXCEL/Exercices/Exercice EXCEL Analyse de scénario

Exercice 1 : choix des bateaux

On veut transporter un minimum de 1500 personnes, 300 véhicules et 400 tonnes de matériel. On dispose de :

• 30 bateaux de type A, susceptibles chacun de transposer en pleine charge 30 personnes, 10 véhicules et 10 tonnes de matériel
• 32 bateaux de type B, susceptibles chacun de transposer en pleine charge 50 personnes, 6 véhicules et 10 tonnes de matériel

Le coût est respectivement de 45000€ pour le bateau A et de 60000€ pour le bateau B.

Déterminez le nombre de bateaux de chaque type qui minimise le coût de ce transport.

Solution

On note x le nombre de bateaux A et y le nombre de bateau B.
La contrainte en personnes: 30x + 50y ≥ 1500
La contrainte en véhicules : 10x + 6y ≥ 300
La contrainte en matériel : 10x + 10y ≥ 400
La contrainte de disponibilité : 0 ≤ x ≤ 30 et 0 ≤ y ≤ 32

Voici la modélisation qui doit être faite dans Excel:

étiquette

ETAPE 1 spécifier la cellule cible et choisir l'optimum min.

la fonction économique

ETAPE 2 spécifier les cellules variables x et y

étiquette

ETAPE 3 spécifier les contraintes

étiquette

étiquette

étiquette

étiquette

étiquette

ETAPE 4 les options du Solveur : cocher modèle supposé linéaire et supposé non négatif
ETAPE 5 la résolution de problème : on clic sur le bouton Résoudre et on sélectionne Réponses dans la fenêtre Rapports et on clic sur ok

résultat obtenu si l'on ne choisit pas l'option rétablir les valeurs d'origine

Le rapport suivant doit apparaître :

rapport que fait excel, il résout le problème de cette manière

Avec 25 bateaux A et 15 bateaux B, on obtient le coût le plus faible dans la cellule B15.