Excel : formules et fonctionnalités avancées pour créer des flux de travail d'équipe efficaces

1. Introduction: Bonjour. Je m'appelle Al et je suis architecte de solutions chez Coda, mais je suis également un utilisateur expérimenté d' Excel depuis de nombreuses années. J'ai commencé à utiliser Excel au d'une équipe financière interne de Google. Je suis très heureuse de donner ce cours pour partager avec vous certaines fonctionnalités avancées et fonctionnalités d' Excel qui vous aideront, vous et vos collègues, à être plus productifs, à mieux analyser les données et, en fin de compte, mieux analyser les données et, en fin prendre de meilleures décisions commerciales. Le cours d'aujourd'hui explique comment utiliser certaines fonctionnalités avancées et fonctionnalités d'Excel. Ces fonctionnalités incluent les décalages, la VAN et la fonction Goal Seek pour vous aider à trouver l' entrée la plus optimale pour votre modèle. Il s'agit d'un cours avancé, mais je vais vous présenter toutes ces fonctionnalités étape par étape afin que vous puissiez les appliquer à vos modèles. Je constate que de nombreuses équipes éprouvent et prennent de mauvaises décisions commerciales ou d'investissement lorsqu'elles n'utilisent pas certaines de ces fonctionnalités. Ils ont essayé de mettre au point une formule au cas par cas. Alors que de nombreux cours Excel vous apprennent à être plus productifs et à automatiser les tâches. Ce cours explique comment aborder vos données de manière plus analytique et critique. Le principal point à retenir est de savoir comment utiliser ces fonctions et ces formules pour prendre de meilleures décisions d'investissement. Que vous travailliez dans la finance, marketing ou dans un autre domaine où vous devez évaluer de multiples opportunités. Il y a beaucoup de dollars ou de ressources en jeu. Vous repartirez avec les compétences dont vous aurez besoin pour aider votre équipe à réfléchir de manière plus critique et analytique afin de prendre de meilleures décisions commerciales. Pour le projet de classe, vous allez vous faire passer pour un analyste chez un grand détaillant en ligne. Votre objectif est d'aider ce détaillant en ligne à déterminer les nouvelles gammes de produits dans lesquelles investir, données historiques sur les revenus. Vous essaierez également de prévoir les revenus futurs à l'aide des taux de croissance, ainsi que de la recherche d'objectifs et de la fonctionnalité avancée d' Excel pour trouver le taux de croissance le plus optimal compte tenu des contraintes du que vous finirez par construire. J'espère vraiment que vous publierez vos questions sur le forum de discussion de la classe, et que vous publierez également des captures d'écran et des questions sur votre projet de classe dans la section projet de la classe. Maintenant, allons-y et commençons par la leçon 1. 2. Utilisez OFFSET W/ AVG & SUM: Cette leçon explique comment utiliser la fonction de décalage en combinaison avec les fonctions de moyenne et de somme. Cette fonction est très utile, en particulier pour les données de séries chronologiques, car elle vous permet de résumer rapidement ou de faire moyenne des données sur des mois, des trimestres ou des années afin de partager rapidement ces données avec votre vos coéquipiers et vos collègues. Nous allons écrire une fonction de décalage de base à l'étape 1, où nous allons faire référence au premier mois de nouveau MRR dans la cellule C25 tableau ci-dessous et nous allons décaler cette cellule de deux lignes et trois colonnes. C'est une fonction de décalage vraiment très basique. Avant de commencer, examinons rapidement notre modèle de croissance simple ici. Nous avons ici une croissance du chiffre d'affaires et de la clientèle avec quelques hypothèses. Nous avons également un chiffre d'affaires mensuel récurrent pour les nouveaux MRR, MRR perdus, le taux de croissance. Nous n'utiliserons pas toutes ces différentes mesures, mais vous donnerons simplement une idée de ce à quoi cela ressemble. Commençons par écrire cette fonction de décalage. Nous allons commencer dans la cellule C25, donc égal à offset. La fonction offset peut désormais prendre jusqu'à un nombre optionnel d'arguments, cinq arguments au total. Dans ce cas, nous n' utiliserons que les trois premières lignes et colonnes de référence. Largeur accrue que nous utiliserons plus tard. Mais pour l'instant, passons à la compensation. Nous allons cliquer sur notre référence, qui est C25. Nous allons le décaler de deux lignes et de trois colonnes. Deux lignes, voyez en quoi cette ligne est le deuxième paramètre. Puis trois colonnes et appuyez sur « Enter ». Regardons ce que cela fait. Dix mille quatre cent quarante-cinq, c'est le nombre. N'oubliez pas que nous compensons cela de deux lignes et trois colonnes à partir de C25. Une fonction de décalage de base comme celle-ci se contente de descendre sur deux lignes, 1, 2, puis trois colonnes vers la droite, 1, 2, 3. C'est ainsi que nous arrivons à 10 445 dans cette fonction de décalage. Vous pouvez également utiliser des nombres négatifs ici. Si c'était moins deux. cela passera de 2 373 à deux rangées, et les colonnes négatives remonteront pouvez l'imaginer, cela passera de 2 373 à deux rangées, et les colonnes négatives remonteront en arrière. Il s'agit d'une méthode de base pour utiliser les fonctions de décalage, mais la fonction de décalage entre vraiment en jeu lorsque vous créez des modèles plus avancés et que vous souhaitez synthétiser les données ou les données moyennes en fonction de vos besoins. Passons au numéro deux. Nous allons écrire une fonction de décalage ci-dessous, qui utilise les entrées du mois de début et du mois de fin, ces deux cellules ici, pour résumer les nouveaux revenus du MRR pour une période donnée. Nous voulons donner à nos coéquipiers la possibilité de participer dans un nombre de mois différent ici. Cela pourrait être un pour janvier, deux pour février, et remarquez que ces chiffres mensuels se situent en haut, ici. S'il est compris entre 3 et 6, nous voulons résumer tous les nouveaux revenus du MRR de mars à juin. Cette somme devrait être essentiellement 3 à 6 ici et le total devrait être de 14 482$. Allons-y et commençons à écrire cette fonction de décalage dès maintenant. Nous allons commencer à utiliser un cas d'utilisation plus avancé de la fonction offset. Offset, nous allons commencer par la référence, qui est à nouveau C25. Maintenant, voulons-nous déplacer cette référence par des lignes ou des colonnes ? Dans ce cas, non, nous voulons rester sur cette cellule. C'est là que nous allons mettre la virgule zéro, la virgule zéro. Nous indiquons à Excel que nous ne voulons pas déplacer cette référence de lignes ou de colonnes. Maintenant, la hauteur et la largeur indiquent Excel la taille d'une sélection Quelle est la plage que vous souhaitez placer dans cette fonction de décalage ? Tester, par exemple, si je mets un comme hauteur et cinq comme largeur, voyons ce qui se passe à titre d'exemple. Rien ne se passe, mais ce que nous demandons à Excel de faire, c'est de prendre tout ce qui se trouve dans la cellule C25 et de prendre une largeur de cinq cellules, ce sera 1, 2, 3, 4, 5. Qu'est-ce que cela signifie ? Cela signifie qu'à l'heure actuelle, cette formule indique qu'il y a une erreur, mais c'est parce qu'elle renvoie la référence de ces cinq cellules. Si j'ajoute une somme autour de cette fonction de décalage, cela me donnera la somme de ces cinq cellules ; 1, 2, 3, 4, 5, 15 279 et correspond à 15 279. Vous pouvez voir comment construire cette fonction de décalage à placer ou à imbriquer dans une fonction de somme ou une fonction de moyenne ou ce que vous voulez. Construisons cette fonction de décalage pour prendre en compte ces mois de début et de fin. Je vais supprimer la fonction de somme pour l'instant. Faisons un petit zoom avant. Au lieu de mettre 0015 ici, nous voulons indiquer à Excel s'il existe un mois de départ supérieur à un, donc peut-être que cela commence en mars dans ce cas, nous voulons réellement passer à la colonnes d'un certain montant. Au lieu de ce zéro pour le paramètre des colonnes, je vais sélectionner le mois de départ puis mettre moins un. La raison en est que si c'est trois, cela commencera à partir de C25 et nous passerons à 1, 2, 3, et remarquerons comment cela placerait la référence dans la colonne F de la cellule, qui correspond au mois 4. En fait, nous voudrons prendre le mois de départ nos coéquipiers et collègues entrent dans la cellule, soustraire un, pour que celui-ci devienne le numéro deux. Cela commencera à partir de C25, passera à 1, 2 et débutera correctement au mois 3. Nous sommes passés à un récapitulatif pour commencer en C25. Nous descendons des zéros et nous passons à trois colonnes, trois mois dans ce cas, puisque nous avons saisi le chiffre trois ici, la hauteur correspond à largeur ou à la hauteur de la gamme que nous voulons utiliser. Dans ce cas, nous voulons rester sur la ligne 25. Voici le dernier paramètre, qui est la largeur. Comment pouvons-nous dire à Excel que nous voulons essentiellement à partir des cellules 3 à 6 ? Nous pouvons prendre ce numéro de fin de mois que nos collègues saisissent. Tout ce que nous pouvons faire, c'est prendre 6 moins 3 puis plus 1. La raison pour laquelle nous voulons le faire est que la largeur doit inclure la première et la fin des cellules. à quoi ça ressemble. Cette partie du paramètre est égale à 6 moins 3, soit 3 plus 1, soit 4. Ce qu'Excel va faire, c'est démarrer dans la cellule E25, puis passer en revue 1, 2, 3. C'est la largeur, les quatre dont nous venons de parler, soit le C11 moins C10 plus 1. Maintenant que notre fonction offset est correctement écrite, nous ne pouvons pas mettre de somme autour de cela. avons maintenant 14 482. Si je prends de trois à six, souvenez-vous que mois de début est trois. Le mois de fin est de six, 1 442. Je change cela en un, ce sera maintenant de un à six. Il s'agit maintenant de 19 536. Si je change ce chiffre de sept à douze, cela prendra de juillet à décembre et 36 991. Cette fonction de décalage est vraiment intéressante car elle peut vous renvoyer une plage de cellules donnée en fonction de la façon dont vous entrez les paramètres ici. Ensuite, une fois cette plage renvoyée, vous pouvez placer une fonction de somme autour de celle-ci ou d'une moyenne et c'est ce que nous avons fait ici dans cette combinaison de décalage de somme imbriquée. Créons une autre variante de la fonction de somme et de compensation dans laquelle nous allons résumer un nouveau chiffre d'affaires MRR à partir du premier mois. Nous allons toujours commencer le premier mois ici. Ensuite, nous allons définir le montant final ou le mois de fin en fonction de ce que vous avez saisi à la deuxième étape. Nous allons passer n'importe quel mois ici. Revenons à six par exemple, et ce serait un. gros, nous voulons dire à Excel que En gros, nous voulons dire à Excel que si mon collègue saisit un six ici, je veux tout prendre de un à six. Si mon collègue inscrit un sept ici, je veux tout prendre du mois 1 au mois 7. Mais nous allons construire cela d'une manière différente en écrivant abord la fonction sum et en mettant la référence à la première cellule, qui est C25 parce que nous commençons au mois 1, colonne comment dire à Excel dans quelle cellule aller, en fonction de ce que mon collègue saisit dans la cellule C11 le mois de fin du mois. Normalement, vous pouvez simplement mettre C25, puis mettre quelque chose comme ça et avoir une référence simple. Mais pour référencer cette cellule, nous allons écrire une fonction de décalage. Je n'ai pas écrit correctement l'offset. Nous allons recommencer avec le C25. Alors, combien de lignes voulons-nous décaler ? Nous ne voulons pas placer de lignes en dessous, nous allons donc y mettre un zéro. Alors combien de colonnes voulons-nous décaler à partir du nouveau MRR ? Ce sera le dernier mois. Nous allons mettre le mois de fin. Cela fera basculer les choses de 1, 2, 3, 4, 5, 6, 7. Cela nous place dans un mois 8, donc nous voulons en fait faire un moins ici pour tenir compte de ce mouvement supplémentaire sur les cellules. Maintenant, si je mets une parenthèse fermante autour de cela, cela me donne 24 262. Si je résume les mois 1 à 7, voici les mois 1 à 7, 24 262, 24 262. C'est juste une manière différente de construire cette fonction de somme en utilisant le décalage comme deuxième cellule de référence car nous disons à Excel, à partir C25, de se déplacer vers la droite, une donnée nombre de colonnes, dans ce cas, 7 moins 1. Cette partie de la fonction revient en fait à la fonction somme, cette cellule juste là. Nous demandons à Excel de tout résumer de C25 à H25 en utilisant cette fonction de décalage pour référencer la cellule. Entraînez-vous à utiliser ces deux versions de la fonction offset. En général, vous utiliserez cette version de la fonction offset avec la fonction de somme plus souvent, car vous essayez généralement différentes parties de votre modèle. Celui-ci peut être intéressant si vous demandez à votre équipe d' indiquer où elle souhaite résumer les données ou les données moyennes. Mais c'est un peu difficile à déboguer, alors que celle-ci est une fonction imbriquée typique laquelle vous avez un décalage dans la fonction sum. 3. Choisissez des scénarios à l'aide du DÉCALAGE: Nous continuerons à utiliser la fonction de décalage, mais cette fois pour une analyse de scénario d' utilisation différente. Vous utiliserez généralement une fonction telle que la fonction de sélection lorsque vous effectuez une analyse de scénario, mais la fonction de décalage présente certains avantages car vos coéquipiers et collègues peuvent ajouter de nouvelles conditions ou nouvelles hypothèses pour l'analyse des scénarios. La fonction de décalage reprendra automatiquement ces nouveaux scénarios sans que vous ayez à ajuster les formules. Vous pouvez automatiser un peu votre modélisation grâce à cette astuce. Nous allons continuer à utiliser ce modèle de croissance SaaS que nous avons commencé à utiliser dans la première leçon. Mais remarquez comment s'alignent les nouveaux clients, cela fait maintenant défaut. Nous n'avons aucune donnée, sauf pour le premier mois de janvier 2020, leçon 1. Nous avions quelques chiffres codés en dur ici, mais dans la leçon 2, nous allons utiliser la fonction de décalage pour aider notre les coéquipiers sélectionnent différents scénarios en utilisant différents taux de croissance pour prédire avec un nouveau client les montants qui seront. C'est une méthode très courante que vous utilisez lorsque vous créez un modèle, et pour comprendre rapidement ce que nous allons faire ici, nous allons donner à nos coéquipiers la possibilité de participer les chiffres 1, 2 ou 3 ici. Selon l' option choisie, cela correspondra à différentes hypothèses de taux de croissance. S'ils en ont choisi un, cela correspondra à 10 pour cent. Ils en choisissent trois, cela correspondrait à 20 pour cent. Ensuite, ces chiffres augmenteront en fonction du taux de croissance indiqué ici. Commençons par la première étape, et nous allons écrire une fonction de sélection de base à partir de la cellule D22, qui se trouve ici. Nous allons prendre en compte le taux de croissance sélectionné en fonction de ce que nos coéquipiers saisissent dans le scénario de la cellule C8. Allons-y très rapidement et découvrons comment fonctionne la fonction de sélection. J'écris égal à choisir. C'est une fonction très basique. Il ne prend en compte que quelques paramètres différents. La première est la cellule qui contient le nombre que vous allez choisir ou l'index que vous allez choisir. Ce sera notre cellule d'entrée dans le scénario qui est C8. Ensuite, dans la valeur 1,2,3, remarquez que ce sont tous des arguments facultatifs. Je vais sélectionner D12, puis D13, puis D14. Appuyez sur « Enter ». Je vais juste coller ce formatage ici. Remarquez que cela correspond à 20 %, car nous disons qu'Excel est basé sur le nombre saisi ici, le numéro d'index, prenez ce nombre parmi les options que j'ai fournies dans valeurs 1,2 et 3. Comme il s'agit de trois, il faut la D14. Si j'en choisis une, elle me donne les 10 pour cent parce que c'est la première valeur qu'elle pourrait choisir. Deux, bien sûr, ce sera 15 pour cent. Maintenant que nous savons comment fonctionne la fonction de sélection, nous pouvons l'utiliser dans la formule pour prévoir les nouveaux clients dans les mois à venir en 2020. Tout ce que je vais faire c'est écrire égal au mois précédent fois les parenthèses un plus. Écrivons la fonction une fois de plus dans l'émission. Nous allons choisir en fonction de ce qui est indiqué ici par nos coéquipiers, puis vous proposer les différents taux. Les taux de croissance chutent par des virgules. Alors c'est ça. Mais en fait, nous allons vouloir m' assurer que nous le faisons ici, c'est bloqué. que des références, car nous allons faire glisser cette formule vers la droite. Remarquez qu'il s'agit croissance de 39 % selon la deuxième hypothèse de taux de croissance, soit 15 %. Faisons simplement glisser ça vers la droite. Vous verrez comment les chiffres augmentent proportionnellement en fonction des taux de croissance saisis. J'en mets un ici, il poussera moins vite. J'en mets un trois ici qui pousse plus. C'est ainsi que nous pouvons utiliser la fonction de sélection pour effectuer une analyse de base rapide de scénarios dans notre modèle et donner à nos coéquipiers la possibilité de modifier les hypothèses qui affectent le reste des modèle pour MRR et notre modèle. À la deuxième étape, ajoutons deux autres hypothèses de taux de croissance aux scénarios clients. Faisons simplement la fonction que nous avons écrite dans D22 en conséquence. Supposons que nous souhaitions ajouter de nouvelles hypothèses, taux de croissance, l'hypothèse numéro quatre. Ce sera l'option numéro quatre. Disons que c'est le pire des scénarios que nous allons mettre de cinq pour cent. Faisons-en un autre. Je vais juste copier ceci et changer le numéro. Ce sera le scénario le plus optimiste, soit environ 25 %. Permettez-moi de copier le formatage ci-dessous pour ces chiffres. Comment prendre en compte ces nouvelles hypothèses, car notre point de vue, notre mémoire, notre fonction initiale ne prend en compte que ces trois hypothèses. Chaque fois que vous ajoutez de nouvelles hypothèses, vous devez revenir à la fonction que vous avez choisie et saisir les options supplémentaires. J'en ai quatre. Je fais de cela une référence. Alors j'ai ce cinquième . Est-ce celui-ci ? Laisse-moi le refaire. La cinquième option est D16. Verrouillez cela et ensuite, comme je l'ai fait auparavant, je dois le faire glisser jusqu' à la droite pour m' assurer que cela tient compte des nouveaux taux ou des nouveaux taux de croissance. Si j'en mets quatre ici, ça fera cinq, c' est cassé. C'est très bien. Mais vous pouvez voir que si vous avez un modèle vraiment complexe, vous devrez constamment ajuster la fonction de sélection ici, puis faire glisser ces formules si vous avez de nouvelles hypothèses de taux de croissance. De même, si vous supprimez les nouvelles hypothèses de taux de croissance, cela ne fonctionnera plus. Je pense qu'il n'y a en fait que des valeurs par défaut à zéro. Remarquez que c'est les trois quatre. gère pas vraiment l'ajout et la suppression de croissance ou d' hypothèses ici dans votre modèle ce qui rend l'analyse de scénarios très difficile car vous devez constamment déboguer vos formules pour tenir compte de cette nouvelle croissance ou de ces nouvelles hypothèses. Pour l'étape 3, nous allons utiliser la fonction offset une fois de plus. Au lieu d'utiliser la fonction de sélection, utilisons la fonction offset et D22 qui choisit la croissance ou l'hypothèse correcte. Le meilleur, c'est que peu importe que nous ajoutions ou supprimions de la croissance ou des hypothèses à notre liste. Au lieu d'utiliser choose, je vais supprimer tout cela et écrire offset. Je vais commencer dans la cellule D11. Si vous vous souvenez du fonctionnement de la fonction offset, nous avons une référence ici. Nous allons verrouiller cette référence pour l'instant. Maintenant, de combien de lignes dois-je compenser cela ? Eh bien, nous savons que nos coéquipiers vont saisir ce numéro de scénario ici. Je peux simplement utiliser cette entrée comme nombre de lignes à décaler, je veux la verrouiller pour qu'elle ne bouge pas lorsque je la fais glisser. Si c'est cinq, cela passera du taux 1, 2, 3, 4, 5. Si c'en est un qui va juste descendre d'un. Le nombre de colonnes que je souhaite décaler sera nul, et cela devrait fonctionner. Je pense que j'ai peut-être une parenthèse fermante. Dans ce cas, si j'en mets un ici, je dois le faire glisser vers la droite. Regardez, comment si je mets le numéro un ici, je mets deux ici, trois, ça fait la même chose que la fonction de sélection. Mais ce qui est cool ici, c'est que si j' ajoute automatiquement une nouvelle croissance ou une nouvelle hypothèse, il suffit de la copier et de la coller à nouveau. Fais-en un cinq. Ce sera 25 pour cent. Je n'ai pas besoin de réajuster cette fonction de décalage à nouveau, car elle sait déjà comment cela et contrebalance mon hypothèse de taux de croissance par le nombre de cellules commençant par J11. Si j'en mets cinq ici, il détecte déjà cette nouvelle cellule. Parce que, encore une fois, juste pour résumer ce que cela fait, c'est un décalage par rapport à D11, en descendant de cinq lignes, 1, 2, 3, 4, 5, sans colonnes. Cela ramène ce taux de croissance dans ma formule. Je peux maintenant ajouter rapidement des hypothèses. Je peux supprimer les suppositions. Eh bien, la suppression ne fonctionnerait pas vraiment non plus parce que je dois le faire, ce sera juste zéro par défaut. Mais ce qui est intéressant, c'est que je peux ajouter nouvelles hypothèses de taux de croissance , et l'intérêt de la fonction de compensation est qu'elle retient ces nouveaux taux de croissance en fonction de ce que mes coéquipiers ou collègues entreprennent. cellule C8. 4. Prendre des décisions avec IRR et NPV: IRR et NPV représentent le taux de rendement interne et la valeur actuelle nette. Ce sont des fonctions qu'il est très important pour vous et vos collègues d'apprendre car elles peuvent vous aider à évaluer différents investissements ou opportunités commerciales en fonction de certains risques et rentabilité de ces opportunités. Cela vous aidera à créer une analyse de rentabilisation à présenter à la direction ou à qui que ce soit d'autre pour saisir l'occasion. Maintenant, ces fonctions sont en fait assez faciles à utiliser, mais il sera important de connaître ces scénarios et l'environnement spécifique dans lequel les utiliser. Je vais parler de tout cela dans cette leçon. L'objectif de cette leçon est de vous apprendre des formules et des cadres qui vous aideront , vous et vos collègues, à comprendre comment évaluer différentes décisions de placement. Surtout si vous souhaitez investir dans l' immobilier ou les machines. Il s'agit de savoir comment utiliser des formules pour vous aider à collaborer et à prendre de bonnes décisions commerciales. Avant de passer aux étapes de cette leçon, examinons rapidement certaines des hypothèses de notre modèle immobilier fictif ici. Nous avons quelques hypothèses à ce sujet. Nous examinons essentiellement un investissement immobilier et cette propriété immobilière spécifique a une superficie de mille pieds carrés. Le loyer au pied carré de 50$. Les dépenses d'exploitation sont de 25$ par pied carré. Le prix d'achat de 10 millions de dollars. Nous avons ici quelques autres taux de croissance pour les revenus et les dépenses, ainsi que pour le taux de capitalisation attendu. Nous avons un compte de résultat très abrégé qui indique nos recettes brutes chaque année. À partir de la première année et jusqu'à la 11e année. Ce chiffre augmente simplement selon le taux de croissance que nous avons ici dans les revenus, les dépenses d'exploitation sont les pieds carrés multipliés par les dépenses d'exploitation par pied carré. Ce chiffre augmente également à un taux fixe chaque année, ce qui nous donne notre bénéfice d'exploitation net à la ligne 32. Voici notre compte de résultat, et en bas, notre état des flux de trésorerie. Encore une fois très basique avant la première année. Avant de générer des flux de trésorerie. Notre première dépense de trésorerie est notre achat de l'année 0, donc c'est moins 10 millions de dollars pour cet investissement. Nous exploitons cette propriété et ensuite notre flux de trésorerie lié à l'exploitation est simplement le bénéfice net d'exploitation, le bénéfice d'exploitation net de chaque année à partir de l'année 1, de l'année 2, etc. Ensuite, la trésorerie nette est simplement le montant net. Vous remarquerez maintenant qu' en 10e année, nous avons notre bénéfice net d'exploitation, mais que nous manquons l'argent que nous recevons de la vente de la propriété réelle parce que nous voulons voir que cet investissement vaut un vendeur pour J'espère faire des bénéfices, mais ce chiffre est manquant. Notre objectif est de déterminer quel est ce chiffre et également de trouver d' autres formules et indicateurs pour déterminer si nous devons investir dans cette propriété par rapport une autre propriété. dans cet exemple immobilier. À l'étape 2, le flux de trésorerie provenant de la vente dans le segment occasionnel est absent et nous allons supposer que la propriété valeur de 10 millions de dollars est vendue au cours de la dixième année. Calculez cette valeur en divisant le NOI lui-même par le taux de plafonnement. Nous avons une hypothèse de taux de plafonnement de 10 %. Tout ce que nous avons à faire ici pour calculer le flux de trésorerie provenant de la vente de cette propriété après 10 ans de conservation et d' obtention des flux de trésorerie est de prendre le bénéfice d'exploitation net à la fin de la 10e année, soit ce chiffre, 2,392 millions. Ensuite, nous allons simplement diviser ce montant par le taux de plafonnement pour obtenir le prix de vente attendu. Nous avons un prix de vente estimé à 24 millions de dollars. C'est arrondi. Cela suppose que nous obtenons ces flux de trésorerie attendus et qu'ils augmentent chaque année et qu'à la fin de la 10e année , ce sera déjà notre bénéfice d'exploitation net attendu, qui deviendra notre flux de trésorerie pour l'année 11. Si nous devions conserver la propriété, dans ce cas, nous la vendrons à la fin de l'année 10. Notre argent provenant de la propriété du vendeur sera équivalent à ce chiffre que vous avez calculé, qui dépend du taux de plafonnement. Si vous regardez nos flux de trésorerie sur la période de 10 ans. Nous avons moins 10 millions pour le premier achat de la propriété. Nous avons ensuite les flux de trésorerie de première à la dixième année et, à la fin de l'année 10, nous vendons cette propriété pour 24 millions de dollars ou en espèces. À la fin de l'année 10, il s'agit de 26 millions de dollars. Cela semble être un très bon investissement. Voyons quel est le taux de rendement interne. Maintenant que nous avons un tableau complet des flux de trésorerie. Encore une fois, il s'agit d'un tableau des flux de trésorerie très simple, mais il s'agit davantage de montrer la puissance de l'utilisation de ces formules pour aider à évaluer les différentes opportunités d'investissement et d'affaires que votre entreprise pourrait faire face. Nous allons calculer le taux de rendement interne de cet investissement en faisant simplement égal à IRR, ou taux de rendement interne. Je peux littéralement donner toutes les valeurs de mon bénéfice d'exploitation net. Chacune de ces valeurs sera intégrée à cette équation IRR. Cela va recracher le taux d' actualisation auquel valeur actuelle nette de mes flux de trésorerie est nulle. Encore une fois, nous sommes égaux à IRR. Je viens de me rendre compte que nous n' utilisons pas les bons chiffres. Nous devrions en fait inclure les sorties nettes, qui sont de 10 millions. Incluons toutes ces données. Nous y avons un taux de rendement interne de 20,7 %. J'ai oublié qu'on ne peut pas simplement utiliser les flux de trésorerie annuels. Nous devons inclure les dépenses initiales, qui s'élèvent à 10 millions de dollars, ainsi que la vente finale. J'utilisais le bénéfice d'exploitation net par accident et je devrais utiliser la trésorerie nette. C'est le taux de rendement interne de ce projet en supposant et encore une fois, le TRI, je ne vais pas trop approfondir ce qu'est le taux de rendement interne. Mais c'est un chiffre que vous pouvez comparer entre vos différentes opportunités d'investissement pour voir s'il s' agit d'un bon investissement ou non. Si je trouvais un autre investissement un TRI de 25 pour cent, je choisirais avec un TRI de 25 pour cent, je choisirais évidemment cet investissement par rapport à celui-ci, car il s'agit du taux d' actualisation nécessaire pour avoir la valeur actuelle nette de notre les flux de trésorerie doivent être nuls. Maintenant, je viens de parler un peu de la valeur actuelle nette. Voyons un peu ce qu'est la valeur actuelle nette et comment nous pouvons la calculer en utilisant la fonction NPV. Nous allons vérifier que cette valeur actuelle nette de nombre en utilisant cet IRR est nulle en utilisant la fonction NPV. Je vais juste écrire vaut NPV et pour le premier paramètre je dois saisir le taux d'actualisation, [NOISE] qui est le taux de rendement interne, que je viens de calculer soit 20,7 pour cent, alors, comme pour la fonction IRR, je dois fournir un tas de valeurs. Maintenant, [BRUIT] Je vais d'abord sélectionner tous les flux de trésorerie de la première à la dixième année. Je te montrerai à quoi ça ressemble une fois que nous l'aurons fait. Remarquez comment cela équivaut à la valeur actuelle nette de ces flux de trésorerie. En supposant qu'un taux d'actualisation de 20,7 % soit de 10 millions de dollars. En réalité, afin de vérifier que cette VAN est nulle pour ce taux. En fait, vous saisiriez également la sortie de trésorerie initiale, qui est de 10 millions de dollars. Comme vous pouvez l'imaginer, cela deviendra nul. Cela montre essentiellement que notre IRR est correct. Mais il est important de savoir que la valeur actuelle nette de tous ces flux de trésorerie doit être égale à votre dépense initiale, qui est de 10 millions de dollars. C'est pourquoi vous verrez cela à 10 millions, nous n'incluons pas ces 10 millions négatifs ici dans l'achat de l'année 0. Mais si je l'inclus, évidemment, cela met tout à 0$. Ce qui est intéressant ici, c'est que si vous modifiez ce taux d' actualisation, vous remarquerez comment la VAN changera également. Rappelez-vous que le premier paramètre de cette fonction NPV est l'IRR. Supposons que je réduise ce TRI d'investissement à 15 %. Aujourd'hui, notre valeur actuelle nette a augmenté parce que nous envisageons un taux d'actualisation plus faible pour actualiser nos flux de trésorerie par rapport à la valeur actuelle nette. Ce ne sera pas le TRI correct pour ce projet car notre valeur actuelle nette est supérieure à zéro. Bien entendu, si je l' augmente au-delà du TRI à 30 %, alors la valeur actuelle nette des flux de trésorerie est négative. Depuis, le premier paramètre que nous utilisons dans la valeur actuelle est bien l'IRR. Généralement, lorsque vous comparez des opportunités d'investissement, vous pouvez utiliser une combinaison du TRI et de la valeur actuelle nette pour obtenir le montant absolu en dollars d'un investissement donné. Ramenons ce chiffre à 20,7 %. Si je comparais les investissements uniquement en fonction de leur taux de rendement interne, je vais simplement regarder ce chiffre. Mais parfois, lorsque vous comparez différentes opportunités, vous pouvez également examiner la valeur actuelle nette absolue des flux de trésorerie pour avoir une idée du montant de trésorerie qui vous arrive. Vous pouvez supprimer cette dépense initiale pour voir quelle est cette valeur actuelle nette totale, en supposant différents taux d'actualisation. 5. Recherchez avec W/ Faciliter l'utilisation de Wildcards: Parfois, vous ne savez pas exactement ce que vous voulez résumer, rechercher ou faire la moyenne dans votre ensemble de données. C'est à ce moment que vous pouvez utiliser des caractères génériques dans Excel pour effectuer des recherches floues dans vos données. Vous pouvez utiliser cette leçon pour créer un algorithme de recherche floue pour vos coéquipiers lorsqu'ils ne savent pas exactement ce qu'ils recherchent, mais qu'ils connaissent quelques lettres ou quelque chose dans le nom lui-même. La recherche à l'aide de caractères génériques est l'une des fonctionnalités les plus sous-utilisées d'Excel. Si je l'aime, c' est parce qu'il vous permet , à vous et à vos coéquipiers , de rechercher des données sans connaître l'orthographe ou l' ordre exacts des caractères de la liste. Je pense que c'est une bien meilleure façon de rechercher des données et que vous souhaitez configurer quelque chose qui soit facile à utiliser pour vos coéquipiers. À titre d'exemple, nous allons compter le nombre d' adresses situées en dessous de cette extrémité avec les lettres street, St, à l'aide d'une fonction COUNTIF. Vous verrez ces adresses ci-dessous. Certains d'entre eux se terminent par le sud, certains se terminent par drive, Dr. Nous voyons que quelques-uns qui ont St, St St St. Vous pourriez faire une variante de la fonction mid and find, ce que nous avons fait et que j'ai montré dans les cours précédents. Mais les jokers rendent vraiment cet exercice beaucoup plus facile. Vous pouvez rechercher sur Google tous les différents caractères génériques disponibles. Mais je vais vous montrer les plus courants que j'utilise lorsque j'ai besoin de rechercher des données et je ne sais pas comment les données sont présentées spécifiquement dans la liste. Nous allons écrire une fonction COUNTIF de base, ce que nous avons tous fait auparavant. Nous allons simplement parcourir notre gamme ici, la sélectionner. Le critère ici est qu'au lieu de faire égal à une valeur spécifique ou autre, ou supérieur ou inférieur à une valeur quelconque, nous allons écrire des guillemets doubles, puis l'étoile St, des guillemets doubles. Nous obtenons le chiffre quatre. Vous pouvez rapidement regarder cela et voir qu'il y a quatre adresses avec les lettres « rue » à la fin. Ce qui se passe réellement ici, c' est qu' Excel parcourt toute cette liste. Cette étoile indique essentiellement à Excel, peu importe ce qui précède les lettres St, tant qu'elle se termine par les lettres St. Ensuite, je veux compter cela dans ma fonction COUNTIF. Si St se trouve quelque part au début ou au milieu, cela ne comptera pas. Cette étoile indique à Excel tous les caractères, tous les chiffres, toutes les lettres avant St, c'est cool. Je souhaite l'inclure dans ma fonction COUNTIF. Vous pouvez voir ici si je mets Rd ici, cela n'inclurait cette adresse spécifique avec Buttermilk Road. Si je mets le Dr ici, alors il n'y a que deux adresses avec le Dr à la fin. La star est l'une de mes préférées parce que c'est comme faire une recherche floue sur la liste quand on ne sait pas exactement comment les choses sont orthographiées. Vous ne savez pas ce qui va se passer avant ou après les lettres, mais vous savez que cela doit sans aucun doute se terminer par St, dans ce cas-ci pour rue, parce que c'est là que vous vous intéressez en termes de votre ensemble de données. Maintenant, nous pouvons également utiliser d'autres fonctions à l'aide de ces caractères génériques. Nous allons résumer le nombre de ventes pour ces identifiants clients. Nous allons uniquement examiner les clients dont l'identifiant client comporte neuf caractères à l'aide de la fonction SUMIF. Vous remarquerez que certains d'entre eux comportent 1, 2, 3, 4, 5, 6, 7, 8 caractères, tandis que d'autres en ont 10, car ils peuvent contenir une lettre supplémentaire dans leur identifiant client. Nous voulons simplement résumer les ventes qui ont un identifiant client par neuf. Peut-être que les clients qui ont neuf identifiants clients sont comme les anciens clients, quelque chose comme ça. Une façon de le faire, bien sûr, est de faire une longueur de cellule. sait que c'est neuf et qu'il y en a dix. Vous pouvez ensuite additionner les identifiants clients qui ont un numéro spécifique, dans ce cas neuf. Mais pour ce qui est des jokers, je tiens à souligner à quel point il est important d'utiliser jokers dans certaines situations. Est-ce que vous n' avez pas à faire cette formule supplémentaire à côté. Ce caractère générique en forme de point d'interrogation indique qu'Excel n' inclut que les cellules contenant exactement ce nombre de caractères. Je vais écrire au SUMIF. La gamme sera cette liste d'identifiants clients. Je l'ai fait par accident. Maintenant, les critères seront des points d'interrogation. Encore une fois, vous mettez le nombre de points d' interrogation que vous souhaitez indiquer à Excel, voici le nombre exact de caractères dans la cellule que je souhaite inclure dans mes critères. Nous allons faire 1,2,3,4,5,6,7,8, 9. Cela indique à Excel de ne retirer que les valeurs comportant neuf caractères, non 10, ni huit, seulement neuf, virgules. La fourchette de somme serait juste celle des ventes et nous en avons 14 914. Vous pouvez vérifier cela en remarquant comment nous avons fait le décompte ici. Nous pourrions faire du SUMIF sur un pied d'égalité, en vérifiant simplement notre travail. SUMIF si cela équivaut à neuf, puis additionnez cela, nous obtenons le même nombre, 14 914. En utilisant une combinaison du point d'interrogation et du caractère générique étoile, cela vous permet d'effectuer des recherches assez puissantes dans vos données. Je vais simplement rechercher sur Google certains des meilleurs caractères génériques que vous pouvez utiliser et les combinaisons. Il existe des combinaisons vraiment sympas que vous pouvez faire. Mais l'utilisation d'une combinaison d' étoiles et de points d'interrogation est probablement les caractères les plus courants que j'utilise pour rechercher mes données. Pour vous montrer comment cela pourrait fonctionner pour votre équipe, nous avons les mêmes données et maintenant nous avons l'adresse, le client et les ventes. Nous allons faire une recherche floue sur VLOOKUP. C'est ainsi que vous pouvez réellement faire quelque chose pour vos coéquipiers, c' est-à-dire que vos coéquipiers ne connaissent que le nom de la rue dans l'adresse. Ils veulent pouvoir saisir rapidement le nom d'une rue et voir quel est le nombre de ventes pour ce nom de rue donné. Ils ne connaissent que le mot babeurre, ou ils ne connaissent que le mot Ross Clark, ou ils ne connaissent que le mot Montgomery. Mais ils ne connaissent pas le numéro d'adresse ou le suffixe qui correspond à la rue, à l'allée, cercle ou quoi que ce soit d'autre. Nous allons écrire une recherche floue permettant à nos coéquipiers de saisir quelque chose comme ils pourraient le faire sur Google, où ils ne connaissent pas la requête de recherche complète et souhaitent en revenir plus intelligemment résultats. Un exemple peut être égal à VLOOKUP. La valeur VLOOKUP que nous allons faire ici est un guillemet double, une étoile, un babeurre, une étoile, un guillemet double. Nous allons chercher dans ce tableau de tables, qui se trouve juste d'ici à ici. Nous allons retirer le chiffre de vente, qui est de 3,0. Normalement, lorsque vous faites un VLOOKUP, vous recherchez toujours une valeur spécifique. Vous souhaitez faire correspondre un match spécifique. Mais dans ce cas, je dis à Excel que je veux récupérer toutes les adresses contenant le mot babeurre. Peu importe ce qui vient avant ou après. C'est pourquoi j'ai une étoile avant le babeurre et après le babeurre. Cela signifie que je me fiche du numéro d'adresse, peu importe la rue. Je veux juste savoir quelles adresses contiennent le mot Buttermilk et cela se traduit par 239 ici. Cela permet à mes coéquipiers de refaire une recherche floue, dans ma formule VLOOKUP, lorsqu'ils ne connaissent pas l'adresse exacte, 321 Buttermilk Road. Vous pouvez maintenant le rendre un peu plus flexible. Je crois que vous pouvez en faire une référence et je pense que ce que vous pouvez faire ici c'est simplement créer une étoile. Disons que nous avons le mot babeurre ici. Je pense que vous pouvez réellement supprimer ceci et créer une étoile, cette étoile. Peut-être que si je mettais la citation autour de ça. Cela peut ne pas fonctionner non plus. Je dois voir comment tu peux le faire. Peut-être que vous devez faire une esperluette avec la référence de la cellule. C'est ainsi que cela fonctionnera. Si je voulais rendre recherche plus flexible pour mes coéquipiers, je pourrais dire quelque chose comme saisir l'adresse, le nom de la route ici. Ensuite, ils peuvent saisir des choses comme ils ne connaissent que Montgomery, qui est cette adresse juste ici, et elle remonte à 1562, peut-être qu'ils ne connaissent que Florence. C'est un moyen plus flexible de rechercher des informations sans avoir à connaître à nouveau l'adresse complète. Jouez avec les différents jokers. Recherchez sur Google comment vous pourriez les combiner. Mais je dirais que le point d' interrogation, l'étoile les caractères génériques sont ceux que j'utilise le plus pour effectuer des recherches floues afin rendre mes outils et modèles dans Excel plus flexibles pour mes collègues. 6. Trouver des intrants optimaux avec un objectif: [MUSIQUE] Lorsque vous avez un modèle avec plusieurs entrées, vous souhaitez parfois simplement modifier une entrée pour maximiser les profits, minimiser les coûts ou peut-être trouver le seuil de rentabilité au sein de votre modèle ou de votre entreprise. La fonction Goal Seek est parfaite pour cela, car vous pouvez indiquer à Excel d'optimiser une cellule spécifique à l'aide d'une entrée de votre modèle. Dans cette leçon, nous allons nous faire passer pour une société de covoiturage comme Uber ou Lyft pour déterminer le nombre optimal de courses dont nous avons besoin dans notre système pour atteindre le seuil de rentabilité. Lorsque vous créez un modèle, vous voulez parfois être en mesure de trouver l'intrant le plus optimal pour maximiser les profits, trouver le seuil de rentabilité sans avoir à faire de nombreux essais et erreurs. Nous allons examiner ce modèle fictif pour voir comment une société de covoiturage, disons comme Uber ou Lyft, pourrait modéliser ses bénéfices. Essayons de comprendre rapidement ce que représente le D19, ce chiffre de profit total. Nous avons quelques hypothèses ici, le nombre de manèges que cette société de covoiturage doit effectuer sur son marché. Leur taux de participation est la part de chaque trajet que la société de covoiturage prend pour chaque tarif. Le prix total par trajet, disons que la moyenne est de 15 dollars. Cela signifie que le taux de participation pour cette entreprise fictive est simplement 25 pour cent multiplié par 15, soit 3,75. Supposons qu'ils pénètrent un nouveau marché, comme une nouvelle ville pour lancer leurs capacités de covoiturage. Ils peuvent avoir des coûts de marketing fixes de 100 000$, et leur coût par trajet serait simplement le prix total par trajet moins le nombre de prises par trajet, qui correspond à leur bénéfice net total. Le bénéfice total ici est simplement de 100 000 dollars, l'investissement [NOISE] dans leurs efforts de marketing plus leur taux de participation, qui est leur bénéfice pour chaque course, multipliez-le par le nombre de courses effectuées. Vous pouvez voir ici que s'il n'y a que 100 courses, elles ont une perte nette de 99 625. Ce chiffre augmente, disons, à 2 000. Leur perte nette est légèrement moindre. Quel est le seuil de rentabilité pour cet exemple ? Est-ce 5 000 manèges ? Non, c'est beaucoup trop. Avec peu, ça fait 50 000 ? C'est un peu trop. Vous pouvez voir comment je pourrais faire un tas d' essais et d'erreurs pour déterminer ce que devrait être ce nombre pour atteindre zéro. Parce que je veux trouver le seuil de rentabilité de courses qui donnera à cette société de covoiturage fictive un seuil de rentabilité de 0$. Nous ajustons le nombre de courses ici en D10 et nous ajustons le bénéfice total, mais nous voulons être en mesure de déterminer le nombre précis de courses pour atteindre le seuil de rentabilité. Au lieu de deviner par essais et erreurs, nous allons utiliser cette fonction appelée Goal Seek, qui possède une fonctionnalité que vous pouvez utiliser pour vos modèles. Vous pouvez accéder à l'onglet « Données », à « What-If-Analysis », puis à « Goal Seek ». Cela fonctionne lorsque vous n'avez qu'à maximiser ou optimiser une variable spécifique. Dans ce cas, il s'agira d'un bénéfice total. Nous allons passer à la cellule qui est la cellule ou la valeur que nous voulons optimiser, à savoir D19. Nous voulons mettre cette valeur à zéro, car ce sera le point où nous atteindrons le seuil de rentabilité. Comment allons-nous atteindre ce seuil de rentabilité zéro ? En modifiant cette entrée, qui correspond au nombre de trajets. Nous n'allons pas modifier le taux de fréquentation ni aucune de ces autres hypothèses, simplement le nombre de trajets, car c'est ainsi que notre modèle est construit. Une fois ces paramètres définis, cliquez sur « OK ». Vous verrez qu'Excel trouve rapidement la solution, soit 26 667 courses pour être exact, pour atteindre le seuil de rentabilité de 0$ lorsque nous avons un coût marketing fixe de 100 000 dollars et votre prise est de 3,75 par manège. Si vous en ajoutez un de plus ici, vous verrez que ce bénéfice net est maintenant de 5$, donc il est un peu trop élevé. C'est une façon vraiment intéressante d'essayer trouver le point d'équilibre ou peut-être de trouver le nombre total de courses pour atteindre 1 million de dollars de bénéfices. Nous pourrions essayer de le faire très rapidement en allant dans « Données », « What-If-Analysis », « Goal Seek ». Nous pouvons à nouveau régler la cellule D19. Au lieu de le mettre à zéro, nous allons le mettre à un million en modifiant la cellule D10 qui correspond au nombre de trajets. Voyons combien de courses il faut pour réaliser un bénéfice de 1 million de dollars, soit 293 333 courses. Lorsque vous travaillez avec votre équipe et que vous essayez de collaborer pour trouver une solution optimale, au lieu de faire des essais et des erreurs, vous pouvez utiliser cette fonctionnalité de données avancée, qui est Goal Recherchez, pour trouver les hypothèses ou les entrées optimales pour maximiser les profits, pour trouver le seuil de rentabilité et les autres indicateurs que vous souhaitez atteindre pour votre équipe. 7. Optimisations complexes W/ Solver: Sur la base de la leçon précédente où nous avons utilisé Goal Seek pour optimiser une entrée et une sortie, vous aurez parfois des cas d'utilisation plus avancés, et vos collègues vous diront qu'il existe d'autres contraintes. dans votre modèle dont vous devez tenir compte. Lorsque vous devez prendre en compte plusieurs variables et contraintes pour trouver les entrées optimales pour votre modèle, vous pouvez utiliser la fonction de solveur avancée d'Excel, qui est un complément. Nous allons faire semblant d'être toujours société de covoiturage, et nous aurons de multiples contraintes et de nombreuses autres variables à prendre en compte afin maximiser nos bénéfices pour notre entreprise. Pour les modèles plus avancés dans lesquels vous devez travailler sous de multiples contraintes et où vous souhaitez optimiser un ensemble d'entrées différentes, vous ne pouvez pas utiliser la fonction Goal Seek, dont nous avons parlé à propos de la leçon 5 , vous devrez utiliser une fonctionnalité plus avancée appelée Solveur. abord, avant de commencer à utiliser Solver sur notre modèle, nous allons rapidement, dans la première étape, prendre un moment pour comprendre à quoi ressemble le modèle. Encore une fois, en prenant notre société de covoiturage comme exemple, nous avons quelques hypothèses  : le taux est de 25 %. Nous avons maintenant l'hypothèse le nombre total de chauffeurs assignables est le nombre total de chauffeurs qu'Uber ou Lyft, ou autre, peuvent avoir à leur disposition pour travailler dans leur entreprise que le nombre total de chauffeurs assignables est le nombre total de chauffeurs qu'Uber ou Lyft, ou autre, peuvent avoir à leur disposition pour travailler dans leur entreprise . proposer des trajets aux clients. Au total, 300 000 conducteurs se sont inscrits pour conduire pour notre société de covoiturage, et le nombre total de trajets que chaque conducteur peut effectuer par jour est de 10. Ce ne sont là que trois hypothèses qui sous-tendent notre modèle. Maintenant, si nous examinons cette liste de villes, sont des villes potentielles dans lesquelles notre société de covoiturage pourrait souhaiter opérer. Cette colonne, les trajets demandés par jour, correspond au nombre maximum de trajets demandés pour cette journée. Si nous partons de l'hypothèse de 10 trajets par chauffeur et par jour, cela signifie que le nombre de conducteurs nécessaires dans chacune de ces villes est simplement le nombre de trajets demandés par jour divisé par 10. Vous verrez que c'est le nombre total de conducteurs nécessaires. Le nombre total est en fait de 386 000, soit plus que le nombre de conducteurs assignables que nous avons pour tous nos clients. Nous avons ici notre problème d'offre et de demande. Nous avons également ces entrées du tarif moyen par ville. Vous pouvez voir que cela est trié par ordre décroissant. Eh bien, il est trié par ordre décroissant. New York a le tarif moyen le plus élevé, en baisse, Mumbai étant le plus bas. Si nous supposons ce taux de participation de 25  %, qui correspond au bénéfice par trajet de notre société de covoiturage, nous prenons simplement 25 %, le multiplions par le tarif moyen, puis nous pouvons obtenir le bénéfice par trajet. Notre objectif est maintenant de saisir le nombre de conducteurs assignés ici afin de maximiser le profit total. Écoutez, si je mets 100 ici, nous obtenons un bénéfice de 469, soit tout simplement 100 fois le bénéfice par trajet. Si j'en mets 100 de plus ici, nous en obtenons 336 parce que le tarif est plus bas, et nous obtenons un bénéfice total de 805. Si vous deviez le faire à la main ou manuellement, vous examineriez les contraintes ce modèle, c'est-à-dire que nous savons que nous n'avons besoin que de 45 000 conducteurs ici. L'une des tentations est de dire, eh bien, si nous avons 300 000 chauffeurs disponibles, pourquoi ne pas les placer tous dans la ville où les tarifs sont les plus élevés et nous pouvons obtenir un bénéfice total de 1,4 million de dollars ? Le problème, c'est que nous n'avons pas besoin 300 000 chauffeurs à New York, nous en faut seulement 45 000. Le nombre maximum de conducteurs que nous pouvons placer sur ce marché pour New York est tout simplement de 45 000. C'est le bénéfice maximum que nous pouvons tirer de New York , qui est de 210 000. Maintenant, vous pourriez aller plus loin et voir, d'accord, eh bien, notre prochaine ville la plus rentable, on dirait que ce sera Londres, 4,39. Nous allons essayer de maximiser le nombre de conducteurs que nous voulons dans cette ville, qui est de 37 400. Alors la prochaine ville la plus rentable semble être Sao Paulo, donc nous pourrions en faire 20 000 ici. Nous pouvons simplement continuer à parcourir la liste jusqu' à atteindre le nombre maximum de conducteurs assignés, ce qui constituera notre contrainte ici, qui est de 300 000. Cela va évidemment être très lent à faire, surtout avec une longue liste de villes. Mais je ne veux pas le faire manuellement en parcourant chaque ville et en trouvant ville qui rapporte le plus de bénéfices à mon entreprise de covoiturage, puis en essayant de saisir le maximum de places disponibles les conducteurs nécessaires pour maximiser les profits. Nous voulons déterminer quel est notre profit maximum compte tenu de ces contraintes du système. Maintenant que nous avons compris ce modèle très rapidement, examinons l'étape numéro deux. En fait, nous l'avons juste fait un petit peu. Nous l'avons fait par essais et erreurs en ajoutant des conducteurs à la colonne attribuée au conducteur pour voir comment cela affecte le bénéfice total. Nous avons rapidement découvert que nous ne pouvions pas simplement affecter 300 000 conducteurs à la ville la plus rentable, car nous n'avons pas besoin de 300 000 conducteurs à New York. Nous n'en avons besoin que de 45 000 au maximum. Au lieu de le faire manuellement, ville par ville pour maximiser nos profits, nous allons utiliser la fonction Solveur. Si vous n'avez pas encore de Solveur dans votre Excel, vous le verrez dans le menu Données, et vous verrez Solver ici. Si ce n'est pas le cas, cliquez sur le menu « Outils » de votre Excel, il se trouve en fait en dehors de la fenêtre ici, mais vous devriez avoir un onglet Outils dans votre Excel. Accédez ensuite aux compléments Excel, et vous devriez voir le complément Solver ici. Si vous ne le voyez pas, enlevez-le juste ici et cliquez sur « OK ». Ensuite, vous verrez le Solveur apparaître sous l'onglet Données du ruban. Nous allons maintenant cliquer sur l'onglet « Solveur » pour nous aider à calculer le nombre optimal de pilotes à placer dans la colonne des pilotes assignés. Avant d' entrer dans le menu du Solveur, voici quelques éléments que nous souhaitons examiner ici pour savoir comment le configurer. Nous voulons fixer l'objectif du bénéfice total, qui sera le suivant. Eh bien, mais modifiez le nombre de conducteurs assignés, qui est ces cellules ici. Les contraintes incluent que les conducteurs assignés doivent être inférieurs ou égaux aux conducteurs nécessaires, c' est-à-dire simplement que ce nombre doit être inférieur à 300 000. Le nombre total de conducteurs assignés doit également être inférieur ou égal au nombre de conducteurs assignables. Cela signifie que ce nombre ne peut pas être supérieur à ce nombre, et que ces nombres doivent tous être des entiers. Ça fait beaucoup de discussions. Jetons un coup d' œil pour voir ce que cela signifie en utilisant le menu du Solveur. Cela peut apparaître un peu petit à l'écran, mais passons en revue cela un par un. Nous avons un certain objectif. Nous voulons que cet objectif, qui est la cellule I28, soit le bénéfice total de toutes nos villes une fois que nous avons assigné des chauffeurs. Nous voulons simplement maximiser ce nombre. Nous pouvons le fixer à un certain nombre. Dans ce cas, nous voulons simplement trouver le nombre maximum optimal que nous pouvons utiliser, que nous pouvons calculer en fonction des contraintes du modèle. Maintenant, nous avons fixé notre objectif à I28, c' est-à-dire notre bénéfice total, et nous devons indiquer à Excel quelles sont les étiquettes que nous allons utiliser pour atteindre ce profit maximum. Ce sera ce formulaire ici, en changeant des cellules variables. Ce sera de la H17 à la H27. Ce sont les conducteurs que nous voulons assigner à chaque ville. Nous allons changer toutes ces cellules ou plutôt Solver va changer toutes les cellules à l'aide de l'algorithme. Nous allons maintenant intégrer les contraintes dans notre modèle. Il s'agit de la partie la plus importante de Solver, car nous devons indiquer à Excel quelles sont les contraintes avec lesquelles nous travaillons afin d' obtenir ce profit maximal. Commençons par ajouter des contraintes. La première contrainte dont nous avons parlé est le nombre de conducteurs assignés. Ce nombre doit être inférieur ou égal au nombre total de conducteurs nécessaires. Parce que n'oubliez pas que nous ne pouvons pas placer plus 45 000 conducteurs à New York, plus de 35 000 à Los Angeles. C'est ce que vous dites, H17 à H27 doit être inférieur ou égal à E17 à E27. Nous allons les ajouter à la contrainte. Nous allons dire que le numéro total attribué au conducteur ici, qui résume tous les conducteurs assignés dans cette colonne, doit être inférieur ou égal au nombre total de conducteurs assignables conducteurs, soit 300 000. Si nous nous souvenons bien, dans notre analyse de marché globale, nous avons besoin de plus de moteurs que nous n'en avons réellement Nous devons donc trouver la meilleure façon d'optimiser ce petit pool de 300 000 conducteurs, et en ajouter. La dernière contrainte est que ces chiffres, Excel va les faire passer par un algorithme. Nous voulons nous assurer que cela n'aboutit pas à des fractions ou à des décimales, car il n'y a rien de tel que la moitié d'entre nous est conducteur, un tiers de conducteur. Nous allons dire que ces nombres doivent tous être des nombres entiers. Je dis juste que c'est un entier. Nous avons ces trois contraintes dans notre système, cliquez sur « OK ». Maintenant, notre menu Solver est à peu près tout configuré. Nous avons notre objectif, nous avons les cellules que nous changeons, c' est-à-dire le nombre de conducteurs assignés. Ensuite, nous avons ces trois contraintes. Maintenant, voici quelques points que nous voulons examiner. Vous devriez probablement cocher cette option, rendre les variables sans contrainte non négatives. Cela signifie simplement que les variables que nous modifions doivent être supérieures à zéro. Cela n'a pas vraiment de sens d' assigner 500 conducteurs négatifs, nous voulons donc cocher cette case. En général, vous devez utiliser la méthode ou l'algorithme de résolution linéaire GRG. Cela permet de gérer la plupart des situations, linéaires et non linéaires . Conservez cette valeur par défaut. Vous pouvez cliquer sur « Options » ici. Il existe tout un tas d' autres options que vous pouvez utiliser, comme Ignorer les contraintes d'entiers. Nous voulons évidemment garder cela sous contrôle, car nous voulons maintenir ces contraintes. Un GRG linéaire, utilisez plusieurs démarrages, vous cliquez dessus pour trouver des réponses plus précises. Mais généralement, dans ce modèle, vous pouvez simplement laisser tous ces éléments de menu tels quels, cliquez sur « OK ». Nous avons configuré notre solveur. Essayons de voir si Solver peut déterminer le nombre total de conducteurs à assigner pour chaque ville. Cliquez sur « Résoudre » et ouah, c'était assez rapide. Solver a trouvé une solution. Nous allons appuyer sur « OK ». Cliquons simplement sur « Fermer » et voyons ce que Solver a trouvé. Comme prévu, elle a essayé de maximiser les villes les plus rentables, comme New York, elle y a placé 45 000 personnes, dont 35 000 Los Angeles. Ce sont tous des maximums. Mais vous pouvez voir qu'en vous rendant dans les villes les moins rentables, elle n'a affecté que 33 600 chauffeurs à Sao. En fait, nous allons recruter zéro chauffeur à Hong Kong et zéro à Mumbai parce que ce sont nos villes les moins rentables, afin de maintenir ce nombre maximum de conducteurs à seulement 300, 000, dont nous avons l' hypothèse de financement ici. C'est ainsi que vous pouvez utiliser Solver au sein de votre équipe, pour résoudre un problème d'optimisation complexe où vous avez de multiples contraintes, vous modifiez plusieurs leviers ou plusieurs cellules ici, qui sont Ces cellules, et vous avez un numéro que vous essayez de toucher. Il peut s'agir d'un maximum, d'un minimum, d'une certaine valeur. Dans ce cas, nous savons que, compte tenu contraintes de notre système de 300 000 conducteurs, et du nombre de conducteurs nécessaires, le profit total que nous pouvons réaliser dans ce scénario est d'un peu plus d'un million de dollars. C'est ainsi que vous pouvez utiliser Solver, une fonctionnalité beaucoup plus avancée d'Excel vous évite d'avoir à faire des essais et des erreurs, parcourir manuellement et de laisser le Solver faire le plus dur soulevant pour trouver ces chiffres. 8. Réflexions finales: Merci beaucoup d'avoir suivi ce cours sur les fonctionnalités et fonctionnalités avancées d'Excel. J'espère que vous repartirez avec plus de capacités de réflexion critique et analytique lorsqu'il s'agira d'évaluer et d'analyser vos données dans Excel. partie la plus importante de certaines de ces fonctionnalités et fonctions d'Excel est connaître les cas d'utilisation spécifiques et scénarios de niche dans lesquels les utiliser. Mais lorsque vous les apprenez, vous pouvez ensuite présenter une analyse de rentabilisation à soumettre à la direction ou à la haute direction sur l'opportunité d'investir ou non dans une entreprise ou une opportunité d'investissement spécifique. Veuillez poster vos questions et réflexions sur le forum de discussion de la classe. Je serais heureuse de vous faire part de vos commentaires et de prendre des captures d'écran de votre projet de classe pendant que vous y travaillez dans la section projet de ce cours. J'espère vous voir dans l'un de mes autres cours Excel ici sur Skillshare.