Configuration d'une feuille de calcul Solver dans Excel - Factures

Voici un classeur Excel configuré pour résoudre un problème de modélisation d'optimisation pour une entreprise unipersonnelle. Si vous choisissez de construire vous-même l'exemple du classeur Solver (une bonne idée), vous voulez indiquer à Excel d'afficher les formules réelles plutôt que les résultats des formules dans le classeur.

C'est d'ailleurs ce que fait ce classeur. Pour ce faire, sélectionnez la plage de feuille de calcul dans laquelle vous souhaitez afficher les formules réelles plutôt que les résultats de la formule, puis appuyez simultanément sur les touches Ctrl et '(accent grave). En appuyant sur Ctrl + ', vous demandez à Excel d'afficher la formule plutôt que le résultat de la formule dans la plage sélectionnée.

La configuration d'un classeur Solver nécessite trois étapes:

1. Identifier les variables du solveur.

   Tout d'abord, vous voulez identifier les variables dans votre problème de modélisation d'optimisation. Dans le cas d'essayer de comprendre le nombre de livres à écrire et de séminaires à donner pour faire le plus d'argent dans votre entreprise d'une personne, les deux variables Solver sont livres et séminaires.

   Vous devez entrer les étiquettes affichées dans la plage A1: A3, puis les valeurs de la variable de départ affichées dans la plage B2: B3. Cette partie de la feuille de calcul n'a rien de magique. Il identifie simplement les variables qui entrent dans la fonction objectif. La fonction objectif est la formule que vous voulez maximiser ou minimiser. Les valeurs stockées dans la plage de la feuille de calcul B2: B3 sont les suppositions de départ sur ce que devraient être les valeurs de variables optimales.

   C'est juste une supposition que le nombre optimal de livres à écrire est de deux et que le nombre optimal de séminaires à donner est de huit. Vous ne saurez pas quel est le nombre optimal de livres et de séminaires avant d'avoir résolu le problème.

   Bien que vous n'ayez pas à nommer les cellules qui contiennent les valeurs des variables - dans ce cas, les cellules B2 et B3 - nommant ces cellules, votre formule objectif et vos formules de contraintes sont beaucoup plus faciles à comprendre. Donc, vous devriez nommer les cellules.

   Si vous configurez un classeur comme celui-ci, vous pouvez nommer les cellules de la variable en sélectionnant la feuille de travail A2: B3, puis en cliquant sur le bouton de commande Créer à partir de la sélection. Lorsque Excel affiche la boîte de dialogue Créer des noms à partir de la sélection, activez la case à cocher Colonne gauche et cliquez sur OK.

   Ceci indique à Excel d'utiliser les étiquettes dans la colonne de gauche: Ceci serait la plage A2: A3 - pour nommer la plage B2: B3. En d'autres termes, en suivant ces étapes, vous nommez les livres de la cellule B2 et vous nommez les séminaires de la cellule B3.

   

2. Décrivez la fonction objectif.

   La fonction objectif, montrée dans la cellule B5, donne la formule que vous voulez optimiser. Dans le cas d'une formule de profit, vous voulez maximiser une fonction parce que vous voulez maximiser les profits, bien sûr.

   Toutes les fonctions objectives ne doivent pas être maximisées. Certaines fonctions objectives doivent être minimisées. Par exemple, si vous créez une fonction objectif qui décrit le coût d'un programme de publicité ou le risque d'un programme d'investissement, vous pouvez logiquement choisir de minimiser vos coûts ou de minimiser vos risques.

   Pour décrire la fonction objectif, créez une formule décrivant la valeur que vous souhaitez optimiser. Dans le cas d'une fonction de profit pour l'entreprise d'une personne, vous gagnez 15 000 $ pour chaque livre que vous écrivez et 20 000 $ pour chaque séminaire que vous donnez. Vous pouvez le décrire en entrant la formule = 15000 * Livres + 20000 * Séminaires .

   En d'autres termes, vous pouvez calculer les bénéfices de votre entreprise d'une personne en multipliant le nombre de livres que vous écrivez 15 000 $ et le nombre de séminaires que vous donnez 20 000 $. B5.

3. Identifiez toute contrainte de fonction objective.

   Dans la feuille de travail A8: C11, les contraintes sont décrites et identifiées sur la fonction objectif. Quatre contraintes peuvent limiter les profits que vous pouvez réaliser dans votre entreprise:

   • Limite de trésorerie requise: La première contrainte (cellule A8) quantifie la contrainte de trésorerie requise. Dans cet exemple, chaque livre nécessite 500 $ en espèces, et chaque séminaire nécessite 2 500 $ en espèces. Si vous avez 20 000 $ en argent pour investir dans des livres et des séminaires, vous êtes limité dans le nombre de livres que vous pouvez écrire et le nombre de séminaires que vous pouvez donner par l'argent, l'investissement initial que vous devez faire.

     La formule dans la cellule B8 décrit les liquidités requises par votre entreprise. La valeur affichée dans la cellule C8, 20000, identifie la contrainte réelle.

   • Limite d'heures de travail: La contrainte de limite d'heures de travail est quantifiée en ayant la formule dans la cellule B9 et la valeur 1880 dans la cellule C9. Utilisez ces deux informations, la formule et la valeur constante, pour décrire une limite d'heures de travail. En un mot, cette contrainte indique que le nombre d'heures que vous avez passées sur les livres et les séminaires doit être inférieur à 1880.

   • Politique du nombre minimum de livres: La contrainte que vous devez écrire au moins un livre par an est mis en place dans les cellules B10 et C10. La formule = Livres va dans la cellule B10. Le nombre minimum de livres, 1, va dans la cellule C10.

   • Nombre minimum de séminaires: La contrainte que vous devez donner au moins quatre séminaires par an est mise en place dans les cellules B11 et C11. La formule va dans la cellule B11. Le nombre minimum de séminaires valeur constante, 4, va dans la cellule C11.

Après avoir donné les formules de contraintes et fourni les constantes auxquelles les résultats de la formule seront comparés, vous êtes prêt à résoudre votre problème de modélisation d'optimisation. Avec le classeur mis en place, la résolution de la fonction est en réalité très facile.

Configurer le classeur et définir le problème de la fonction objectif et des formules de contraintes est la partie la plus difficile.