Excel: fórmulas y características avanzadas para crear flujos de trabajo de equipo eficientes

1. Introducción: Hola ahí. Mi nombre es Al, y soy arquitecto de soluciones en Coda, pero también he sido un usuario avanzado de Excel durante muchos años. Empecé a usar excel en un equipo de finanzas internas de Google. Estoy muy emocionado de dar esta clase para compartir contigo algunas características avanzadas y funcionalidades en Excel que te ayudarán a ti y a tus compañeros de equipo a ser más productivos, analizar mejor los datos y, en última instancia tomar mejores decisiones de negocio. La clase de hoy trata sobre cómo usar algunas características avanzadas y funcionalidad en Excel. Estas características incluyen compensaciones, VAN y la función de búsqueda de objetivos para ayudarlo a encontrar la entrada más óptima para su modelo. Ahora bien, este es un curso avanzado, pero voy a guiarte a través de todas estas características paso a paso para que las puedas aplicar a tus modelos. Veo que muchos equipos luchan, y toman malas decisiones de negocio o decisiones de inversión cuando no usan algunas de estas características. Intentaron armar una fórmula de manera ad hoc. Mientras que muchas clases de Excel te enseñan cómo ser más productivo y cómo automatizar tareas. Esta clase trata sobre cómo pensar de manera más analítica y crítica sobre sus datos. La clave para llevar con la que quiero que te vayas es cómo usar estas funciones, y fórmulas para tomar mejores decisiones de inversión. Ya sea que trabajes en finanzas, mercadotecnia u otro campo donde tengas que evaluar múltiples oportunidades. Hay muchos dólares o muchos recursos en juego. Te irás con las habilidades que necesitarás para ayudar a tu equipo a pensar de manera más crítica y analítica para tomar mejores decisiones comerciales. Para el proyecto de clase, fingirás ser un analista en un gran minorista en línea. Su objetivo es ayudar a este minorista en línea a descubrir en qué nuevas líneas de productos invertir, dados los datos históricos de ingresos. También intentará predecir los ingresos futuros utilizando las tasas de crecimiento, y también usando Goal Seek, y la función avanzada en Excel para encontrar la tasa de crecimiento más óptima dadas las limitaciones en el que eventualmente construirás. Realmente espero que publiques tus preguntas en el foro de discusión de la clase, y también publiques capturas de pantalla, y preguntas sobre tu proyecto de clase en la sección de proyectos de la clase. Ahora sigamos adelante, y comencemos con la Lección 1. 2. Usa OFFSET W/ AVG y SUM: Esta lección trata sobre cómo usar la función offset en combinación con las funciones average y sum. Esta es una función realmente útil, especialmente para datos de series temporales, ya que le permite resumir rápidamente o promediar datos a lo largo de meses, trimestres o años para compartir rápidamente estos datos con su compañeros de equipo y tus compañeros. Vamos a escribir una función básica de desplazamiento en el Paso 1, donde vamos a hacer referencia al primer mes de nuevo MRR en la celda C25 en esta tabla a continuación y vamos a compensar esta celda por dos filas y tres columnas. Es una función de desplazamiento realmente, muy básica. Antes de comenzar a hacer esto, echemos un vistazo rápidamente a nuestro sencillo modelo de crecimiento aquí. Tenemos ingresos y crecimiento de clientes aquí con algunas suposiciones. También tenemos un ingreso mensual recurrente aquí para nuevos MRR, MRR perdidos, la tasa de crecimiento. No usaremos todas estas métricas diferentes sino solo te daremos una idea de cómo es esto. Empecemos a escribir esta función offset. Vamos a empezar en la celda C25 así es igual a desplazamiento. Ahora la función offset puede tomar hasta un número opcional de argumentos, cinco argumentos en total. En este caso, sólo vamos a usar las tres primeras filas y columnas de referencia. Ancho elevado que usaremos más adelante. Pero por ahora, vamos a compensar. Vamos a dar click en nuestra referencia, que es C25. Vamos a compensarlo por dos filas y tres columnas. Dos filas, vea como esta fila es el segundo parámetro. Después tres columnas y pulsa “Enter”. Veamos qué hace esto. Diez mil cuatrocientos cuarenta y cinco es el número. Recuerde que compensamos esto por dos filas y tres columnas comenzando en C25. Una función básica de desplazamiento como esta, todo lo que hace es bajar dos filas, 1, 2, y luego tres columnas a la derecha, 1, 2, 3. Así es como llegamos a 10,445 en esta función de desplazamiento. También puedes usar números negativos aquí. Si esto fue negativo dos. Esto pasará de 2,373 hasta dos filas, y las columnas negativas irán hacia atrás como se podría imaginar. Esta es una forma básica de usar las funciones de desplazamiento, pero la función offset realmente entra en juego cuando estás construyendo algunos modelos más avanzados y quieres resumir los datos o los datos promedio dependiendo de cuáles sean tus necesidades. Vamos al número dos. a escribir una función de compensación a continuación, que utiliza las entradas del mes inicial y el mes final, estas dos celdas aquí mismo, para resumir, los nuevos ingresos MRR para un período de tiempo específico. Queremos darle a nuestros compañeros la posibilidad de ingresar en diferente número de meses aquí. Este podría ser uno para enero, dos para febrero, y observe cómo están los números de estos meses a lo largo de la parte superior justo aquí. Si son del 3 al 6, queremos resumir todos los nuevos ingresos de MRR de marzo a junio. Esta suma debería ser básicamente 3-6 aquí mismo y el total debería ser de $14,482. Sigamos adelante y comencemos a escribir esta función de offset ahora mismo. Vamos a empezar a usar un caso de uso más avanzado de la función offset. Offset, vamos a comenzar con la referencia, que es C25 nuevamente. Ahora bien, ¿queremos mover esta referencia por alguna fila o columna? En este caso, no, queremos quedarnos en esta celda. Ahí es donde vamos a poner coma cero, coma cero. Le decimos a Excel, no queremos desplazar esta referencia por ninguna fila o columna alguna. Ahora la altura y el ancho le está diciendo a Excel qué tan grande es una selección, ¿cuál es el rango que quieres poner dentro de esta función de desplazamiento? Probemos, por ejemplo, si pongo uno como la altura y cinco como el ancho, veamos qué pasa como ejemplo. No pasa nada, pero lo que le estamos diciendo a Excel, es tomar todo de la celda C25 y tomar un ancho de cinco celdas, esto será 1, 2, 3, 4, 5. ¿ Qué significa eso? Esto quiere decir que ahora mismo esta fórmula, dice que hay un error, pero eso es porque está devolviendo la referencia de estas cinco celdas. Si agrego una suma alrededor de esta función de desplazamiento, me va a dar la suma de estas cinco celdas; 1, 2, 3, 4, 5, 15,279, y coincide con 15,279. Puedes ver cómo podemos construir esta función offset para poner dentro o anidar dentro una función sum o una función promedio o lo que sea que sea que quieras. Construyamos esta función de compensación para tener en cuenta estos meses iniciales y meses finales. Voy a eliminar la función sum por ahora. Vamos a acercarnos un poco. En lugar de poner aquí 0015, queremos decirle a Excel si hay un mes de inicio que sea mayor que uno, así que tal vez sea a partir de marzo en este caso, queremos cambiar realmente sobre el columnas por una cierta cantidad. En lugar de este cero para el parámetro de columnas, voy a seleccionar el mes de inicio y luego poner menos uno. El motivo es porque si se trata de tres, esto comenzará desde C25 y vamos a ir 1, 2, 3, y notaremos cómo esto pondría la referencia en la celda columna F, que es el Mes 4. En realidad vamos a querer tomar el mes de inicio que nuestros compañeros y compañeros entren a la celda, restar uno, para que esto se convierta en el número dos. Esto será a partir de C25 irá a 1, 2, y comenzará correctamente en el Mes 3. Hemos cambiado a un resumen para comenzar en C25. Estamos bajando ceros y estamos pasando a tres columnas, tres meses en este caso, tres meses en este caso, ya que estamos ingresando el número tres aquí, la altura es qué tan amplia o cuál es la altura de lagama que queremos usar. En este caso, queremos quedarnos en la Fila 25. Aquí está el último parámetro, que es el ancho. ¿ Cómo le decimos a Excel básicamente que queremos de las celdas 3-6? Podemos tomar este número de mes final en el que ingresan nuestros compañeros. Todo lo que podemos hacer es tomar 6 menos 3 y luego más 1. La razón por la que queremos hacer eso es porque el ancho tiene que incluir las celdas primera y final. Lo que parece es esto. Esta parte del parámetro equivale a 6 menos 3, que es 3 más 1 que es 4. Lo que Excel va a hacer es iniciar en celda E25 y luego repasar 1, 2, 3. Este es el ancho, los cuatro de los que acabamos de hablar, que es el C11 menos C10 más 1. Ahora que tenemos nuestra función offset escrita correctamente, no podemos poner una suma alrededor de esto. Ahora tenemos 14,482. Si tomo de tres a seis recuerdo comenzar mes es tres mes final es seis, 1,442 cambio esto a uno, este ahora será de uno a seis. Esto es ahora 19.536. Si cambio esto de un siete a doce, esto tomará de julio a diciembre y 36 mil 991. Esta función de desplazamiento es realmente interesante porque puede devolverle un rango dado de celdas dependiendo de cómo ingrese en los parámetros aquí. Entonces una vez que se devuelve ese rango, entonces puedes poner una función sum alrededor de esto o un promedio y eso es lo que hicimos aquí en esta combinación de compensación de suma anidada. Construyamos otra variación de la función sum y offset donde vamos a resumir un nuevo ingreso MRR a partir del Mes 1. Siempre vamos a empezar en el Mes 1 aquí mismo. Entonces vamos a establecer el monto final o el mes final como lo que sea que ingresaste en el paso dos. Vamos a ir a cualquier mes aquí. Pasemos esto de nuevo a seis por ejemplo, y este sería uno. Esencialmente queremos decirle a Excel es si mi colega entra en un seis aquí, quiero llevarle todo del uno al seis. Si mi colega entra en un siete aquí, quiero llevarme de todo desde el Mes 1 hasta el Mes 7. Pero vamos a construir esto de una manera diferente escribiendo primero la función sum y poniendo la referencia a la primera celda, que es C25 porque estamos comenzando en Mes 1, columna cómo le decimos a Excel a qué celda ir, dependiendo de lo que mi colega entre en la celda C11 el mes final. Normalmente podrías simplemente poner C25, luego poner algo así y tener una simple referencia. Pero para hacer referencia a esa celda, vamos a escribir una función de desplazamiento. No escribí offset correctamente. Vamos a empezar con C25 una vez más. Entonces, ¿cuántas filas queremos compensar? No queremos ir a ninguna fila abajo, así que ahí vamos a poner un cero. Entonces, ¿cuántas columnas queremos compensar a partir del nuevo MRR? Ese va a ser el mes final. Vamos a poner el mes final. Esto desplazará las cosas en 1, 2, 3, 4, 5, 6, 7. Esto nos pone en un Mes 8, así que en realidad queremos hacer un menos uno aquí para dar cuenta de ese movimiento extra sobre las celdas. Ahora bien, si pongo un paréntesis de cierre alrededor de esto, esto me da 24,262. Si resumo el Mes 1 al Mes 7 aquí así que el Mes 1 al Mes 7, 24,262, 24,262. Esta es solo una forma diferente de construir esta función de suma usando offset como la segunda celda de referencia porque le estamos diciendo a Excel a partir C25 moverse hacia la derecha, un dado número de columnas, en este caso, 7 menos 1. Esta parte de la función en realidad regresa a la función sum, esta celda aquí mismo. Le estamos diciendo a Excel que resuma todo desde C25 hasta H25 usando esta función de desplazamiento para hacer referencia a la celda. Practica el uso de estas dos versiones de la función offset. Normalmente usarás esta versión de la función offset con la función sum más a menudo porque normalmente estás probando algunas partes diferentes de tu modelo. Este podría ser interesante si tienes a tu equipo como entradas proporcionadas en donde quieren resumir datos o datos promedio. Pero esto es un poco difícil de depurar, mientras que esta es una función anidada típica donde se tiene desplazamiento dentro de la función sum. 3. Escenarios de elige usando OFFSET: Continuaremos usando la función offset, pero esta vez para un análisis de escenario de caso de uso diferente. Normalmente usarás una función como la función de elegir cuando estás haciendo análisis de escenarios, pero la función de compensación tiene algunas ventajas porque tus compañeros de equipo y colegas pueden agregar nuevas condiciones o nuevos supuestos al análisis de escenarios. La función offset recogerá automáticamente estos nuevos escenarios sin que tengas que ajustar las fórmulas. Puedes automatizar un poco tu modelaje con este tip. Vamos a seguir usando este modelo de crecimiento SaaS que comenzamos a usar en la lección uno. Pero fíjense cómo la nueva línea de clientes, ahora falta esto. No tenemos ningún dato excepto el primer mes de enero de 2020, lección uno, aquí teníamos algunos números codificados, pero en la lección dos, vamos a usar la función offset para ayudar a nuestro compañeros de equipo seleccionan diferentes escenarios utilizando diferentes tasas de crecimiento para predecir con un nuevo cliente las cantidades serán. Este es un método muy común usas cuando estás construyendo un modelo, y solo para entender rápidamente lo que vamos a hacer aquí, vamos a dar a nuestros compañeros la capacidad ingresar los números 1, 2 o 3 aquí. Dependiendo de qué opción elijan, esto corresponderá a diferentes supuestos de tasa de crecimiento. Si escogieron uno, esto corresponderá al 10 por ciento. Escogen tres, esto correspondería al 20 por ciento. Entonces estos números crecerán dependiendo de qué tasa de crecimiento se ingrese aquí. Empecemos con el paso uno, y vamos a escribir una función básica de elegir comenzando en la celda D22, que está aquí mismo. Vamos a tomar en cuenta la tasa de crecimiento que se selecciona en función de lo que nuestros compañeros entran en el escenario en la celda C8. Vayamos por aquí muy rápido y descubramos cómo funciona la función de elegir. Escribo igual a elegir. Es una función muy básica. Todo lo que hace es tomar algunos parámetros diferentes. El primero es qué celda contiene el número que vas a elegir o el índice que vas a elegir. Esa va a ser nuestra celda de escenario de ingreso que es C8. Entonces en valor 1,2,3, observe cómo estos son todos argumentos opcionales. Voy a seleccionar D12, después D13, después D14. Golpea “Enter”. Yo sólo voy a pegar este formato por aquí. Observe cómo esto corresponde al 20 por ciento, porque estamos diciendo que Excel se basa en qué número se ingresa aquí, el número índice, tome ese número de las opciones he proporcionado en valores 1,2 y 3. Como se trata de tres, se necesita D14. Si elijo uno, me da el 10 por ciento porque ese es el primer valor que podría elegir función. Dos por supuesto, será del 15 por ciento. Ahora que sabemos cómo funciona la función de elegir, podemos usar esta función de elección en la fórmula para predecir nuevos clientes en los próximos meses en 2020. Todo lo que voy a hacer es escribir igual al mes anterior veces paréntesis uno más. Escribamos la función una vez más el show. Vamos a elegir en base a lo que aquí indiquen nuestros compañeros y luego proporcionaremos las diferentes tarifas. Las tasas de crecimiento caen por comas. Entonces eso es todo. Pero en realidad vamos a querer el quiero asegurarme de que hacemos aquí es encerrado. Eso son todas referencias porque vamos a arrastrar esta fórmula hacia la derecha. Observe cómo esto es 39 por ciento creciendo por el segundo supuesto de tasa de crecimiento, que es 15 por ciento. Solo arrastremos esto hacia la derecha. Verás cómo los números aumentan proporcionalmente en función de qué tasas de crecimiento se ingresan. Pongo uno aquí, va a crecer menos rápido. Pongo un tres aquí crece más. Así es como podemos usar la función de elegir para hacer un rápido análisis básico de escenarios en nuestro modelo y les da a nuestros compañeros la capacidad de cambiar aquí los supuestos que afectan al resto de modelo para MRR y nuestro modelo. En el paso dos, agreguemos dos supuestos de tasa de crecimiento más a los escenarios del cliente. Simplemente hagamos la función que escribimos en D22 en consecuencia. Digamos que queremos sumar en algunos nuevos supuestos, tasa de crecimiento, suposición número cuatro. Esta será la opción número cuatro. Digamos que este es el peor de los casos que vamos a poner cinco por ciento. Hagamos otra. Sólo voy a copiar esto y cambiar el número. Este será el escenario más optimista, que es como el 25 por ciento. Permítanme copiar el formato aquí abajo a estos números. ¿ Cómo tomamos en cuenta estos nuevos supuestos porque nuestra visión, recordamos, nuestra función original sólo toma en cuenta estos tres. Cada vez que agregues nuevas suposiciones, tendrás que volver a tu función de elegir y poner las opciones adicionales. Tengo cuatro. Eso lo hago como referencia. Entonces tengo este quinto . ¿Es éste? Déjame hacerlo otra vez. La quinta opción es D16. Bloquear eso y luego como lo hice antes, tengo que arrastrar esto hasta la derecha para asegurarme de que esto da cuenta de las nuevas tasas o nuevas tasas de crecimiento. Si pongo cuatro aquí, estos serán cinco, esto se rompió. Eso está bien. Pero puedes ver como si tienes un modelo realmente complejo, tendrás que ajustar constantemente la función choose aquí y luego arrastrar esas fórmulas si tienes nuevos supuestos de tasa de crecimiento. De igual manera, si borras nuevas suposiciones de tasa de crecimiento, esto ya no funcionará. Creo que en realidad solo hay valores predeterminados a cero. Observe que esto es las tres cuatro patas. Realmente no maneja agregar y eliminar crecimiento o suposiciones aquí en su modelo lo que hace que el análisis de escenarios sea realmente difícil porque tiene que depurar constantemente sus fórmulas para dar cuenta de estos nuevos crecimientos o supuestos. Para el paso 3, vamos a usar la función offset una vez más. En lugar de usar la función choose usemos la función offset y D22 que escoge el crecimiento o suposición correcta. La mejor parte de esto es que no importará si agregamos o eliminamos crecimiento o suposiciones a nuestra lista. En lugar de usar elegir, voy a eliminar todo esto y escribir offset. Voy a iniciar en la celda D11. Si recuerda cómo funciona la función offset, tenemos una referencia aquí. Vamos a encerrar esta referencia por ahora. Ahora, ¿con cuántas filas quiero compensar esto? Bueno, sabemos que nuestros compañeros van a estar ingresando ese número de escenario aquí. Simplemente puedo usar esta entrada como el número de filas a desplazar, quiero bloquear esto para que no se mueva cuando arrastre esto sobre. Si se trata de cinco, esto pasará de la tasa 1, 2, 3, 4, 5. Si es uno que va a ir justo abajo uno. El número de columnas que quiero compensar sobre esto será cero, y esto solo debería funcionar. Creo que podría haber conseguido un paréntesis de cierre. En este caso, si pongo uno aquí, necesito arrastrar esto hacia la derecha. Mira aquí, cómo si pongo aquí el número uno, pongo un dos aquí, tres, hace lo mismo que la función de elegir. Pero lo genial aquí es que si agrego automáticamente en un nuevo crecimiento o suposición, solo copia y pega esto de nuevo. Haz de esto un cinco. Esto será del 25 por ciento. No tengo que volver a ajustar esta función de desplazamiento porque ya sabe un vistazo a esto y compensar mi suposición de tasa de crecimiento por sin embargo muchas celdas comenzando en D11. Si pongo cinco aquí, ya recoge esta nueva celda. Porque lo que, de nuevo, sólo para recapitular lo que está haciendo esto, se está compensando desde D11, bajando cinco filas, 1, 2, 3, 4, 5, sin columnas. Regresa esa tasa de crecimiento a mi fórmula. Ahora puedo agregar suposiciones rápidamente. Puedo borrar suposiciones. Bueno borrar tampoco realmente no funcionaría porque tengo que hacerlo, esto solo va a ser por defecto a cero. Pero lo genial es que puedo agregar nuevas suposiciones de tasa de crecimiento y la belleza de la función offset es que recogería esas nuevas tasas de crecimiento dependiendo de lo que entren mis compañeros de equipo o colegas celda C8. 4. Tomar decisiones con IRR y NPV: TIR y VAN representan tasa interna de rendimiento y valor presente neto. Estas son funciones que son muy importantes para que tú y tus compañeros aprendan porque pueden ayudarte a evaluar diferentes inversiones u oportunidades de negocio dados ciertos riesgos y rentabilidad de estas oportunidades. Esto te ayudará a crear un caso de negocio para llevar a la gerencia o a quien sea en qué oportunidad perseguir. Ahora bien, estas funciones son en realidad bastante fáciles de usar, pero conocer estos escenarios y el entorno específico en el que utilizarlos va a ser importante. Hablaré de todo eso en esta lección. El objetivo de esta lección es enseñarte algunas fórmulas y marcos para ayudarte a ti y a tus compañeros de equipo a descubrir cómo evaluar diferentes decisiones de inversión. Especialmente si buscas invertir en bienes raíces o maquinaria. Se trata de cómo usar fórmulas para ayudarlos a colaborar y descubrir cómo tomar decisiones comerciales sólidas. Antes de entrar en los pasos aquí para esta lección, echemos un vistazo rápidamente a algunas de las suposiciones en nuestro modelo inmobiliario ficticio aquí. Tenemos algunas suposiciones aquí en adelante. Básicamente estamos buscando una inversión inmobiliaria y esta propiedad inmobiliaria específica tiene mil pies cuadrados. La renta por pie cuadrado de $50. El OpEx es de $25 por pie cuadrado. El precio de compra de 10 millones de dólares. Tenemos algunas otras tasas de crecimiento aquí para el gasto por ingresos y también la tasa de tope esperada. Tenemos un estado de resultados muy abreviado que muestra nuestros ingresos brutos cada año. Comenzando en el Año 1 y hasta el Año 11. Este número simplemente crece por esa tasa de crecimiento que tenemos aquí en los ingresos gastos operativos es el pie cuadrado multiplicado por el OpEx por pie cuadrado. Esto también crece a una tasa fija cada año, lo que nos da nuestro ingreso operativo neto en la fila 32. Este es nuestro estado de resultados, y aquí abajo está nuestro estado de flujos de efectivo. Nuevamente muy básico en antes del Año 1. Antes de generar cualquier flujo de caja. Nuestro primer desembolso de efectivo es nuestra compra en el Año 0 por lo que es negativo $10 millones para esta inversión. Estamos empujando esta propiedad y luego nuestro flujo de efectivo de las operaciones es simplemente el NOI, el ingreso operativo neto cada año partir del Año 1, Año 2, etc. Entonces el efectivo neto es simplemente el monto neto. Ahora te darás cuenta aquí que en Year 10 tenemos nuestro NOI, pero nos falta el efectivo que obtenemos de vender la propiedad real porque queremos ver esta inversión vale la pena hacer una propiedad de vendedor para ojalá obtenga ganancias, y falta este número. Nuestro objetivo es averiguar cuál es ese número y también averiguar algunas otras fórmulas y métricas para averiguar si debemos o no invertir en esta propiedad frente a alguna otra propiedad en este ejemplo inmobiliario. En el Paso 2, falta el flujo de caja de la venta en el segmento casual y vamos a suponer que la propiedad que es propiedad de 10 millones de dólares se vende en el año 10. Calcula este valor dividiendo el propio NOI por la tasa de tope. Tenemos un supuesto de tasa de tope que es del 10 por ciento. Todo lo que tenemos que hacer aquí para que calculemos el flujo de caja de la venta de esta propiedad después 10 años de aferrarse a ella y obtener los flujos de efectivo es tomar el ingreso operativo neto al final del año 10, que es este número, 2.392 millones. Entonces simplemente vamos a dividir eso por la tasa de tope para obtener el precio de venta esperado. Tenemos un precio de venta estimado de 24 millones de dólares. Se redondea hacia arriba. Eso es asumiendo que obtenemos estos flujos de efectivo esperados y crecen cada año y que para finales del Año 10 este ya va a ser nuestro ingreso operativo neto esperado, que se convierte en nuestro flujosde efectivo para el año 11. Si tuviéramos que retener la propiedad, en este caso, venderemos la propiedad al final del año 10. Nuestro efectivo de la propiedad del vendedor va a ser equivalente a este número que calculaste, que depende de la tasa de tope. Si nos fijamos en nuestros flujos de efectivo durante el periodo de los 10 años. Tenemos menos 10 millones para la primera compra de la propiedad. Luego tenemos flujos de efectivo del Año 1 al 10 y al final del Año 10, vendemos esta propiedad por $24 millones o efectivo. Al cierre del Año 10 es de 26 millones de dólares. Parece una inversión bastante buena. Echemos un vistazo a cuál es la tasa interna de rendimiento. Ahora que tenemos un estado completo de flujo de caja. Nuevamente, este es un estado de flujo de caja muy simple, pero esto es más para mostrar el poder de usar estas fórmulas para ayudar a evaluar diferentes oportunidades de inversión y negocios que su empresa podría estar enfrentando. Vamos a calcular la tasa interna de retorno de esta inversión simplemente haciendo igual TIR, o tasa interna de retorno. Literalmente puedo dar todos los valores de mi ingreso operativo neto. Cada uno de estos valores va a ser enchufado a esta ecuación IRR. Volverá a escupir la tasa de descuento a la que mi valor actual neto de mi flujo de caja es cero. Volvamos a igualar a TIR. Me acabo de dar cuenta de que estamos usando los números equivocados. De hecho, deberíamos estar incluyendo la salida neta, que es de los 10 millones. Incluyamos todos estos datos. Ahí tenemos la tasa interna de retorno es de 20.7 por ciento. Olvidé que no puedes simplemente usar los flujos de efectivo anuales anuales. Tenemos que incluir el desembolso inicial, que es de 10 millones de dólares, así como la venta final. Estaba usando el ingreso operativo neto por accidente y debería estar usando el efectivo neto. Esta es la tasa interna de rendimiento de este proyecto asumiendo y esto otra vez, la TIR, no voy a adentrarme demasiado en lo que es la tasa interna de retorno. Pero este es un número que puedes comparar entre tus diversas oportunidades de inversión para ver si se trata o no de una buena inversión. Si encontrara otra inversión que tuviera una TIR del 25 por ciento entonces obviamente iría con esa inversión sobre esta, porque esta es la tasa de descuento necesaria para tener el valor presente neto de nuestro flujos de efectivo para ser cero. Ahora aquí, acabo de hablar un poco sobre el valor presente neto. Vamos a analizar un poco qué es el valor presente neto y cómo podemos calcularlo usando la función NPV. Vamos a verificar que este valor presente neto de este número usando este IRR sea cero mediante el uso de la función NPV. Sólo voy a escribir es igual a NPV y para el primer parámetro necesito ingresar la tasa de descuento, [RUIDO] que es la tasa interna de retorno, que acabo de calcular así 20.7 por ciento, entonces similar a la función IRR, tengo que proporcionar un montón de valores. Ahora, [RUIDO] Primero voy a seleccionar todos los flujos de efectivo del Año 1 al Año 10. Te voy a mostrar cómo es esto después de que lo hagamos. Observe cómo esto equivale al valor actual neto de estos flujos de efectivo. Suponiendo que una tasa de descuento de 20.7 por ciento es de $10 millones. En realidad, para comprobar que este VPN es cero para esta tasa. De hecho, también ingresarías en la salida inicial de efectivo, que es este negativo de 10 millones. Como puedes imaginar, esto se convertirá en cero. Esto básicamente demuestra que nuestra TIR es correcta. Pero es importante saber que el valor actual neto de todos estos flujos de efectivo debe ser igual a su desembolso inicial, que es de 10 millones de dólares. Por eso verás esto como 10 millones, no incluimos ese negativo 10 millones aquí en la compra en el Año 0. Pero si lo incluyo obviamente pone todo en $0. Ahora lo interesante aquí es que si cambias esta tasa de descuento, notarás cómo cambiará también el VPN. Recuerda cómo el primer parámetro en esta función de VPN es la TIR. Digamos que disminuyo esta inversión TIR a 15 por ciento. Ahora nuestro valor actual neto ha aumentado porque estamos buscando una tasa de descuento más baja para descontar nuestros flujos de efectivo al valor presente neto. Esta no será la TIR correcta para este proyecto porque nuestro valor presente neto es mayor a cero. Por supuesto, si aumento esto a más allá de la TIR al 30 por ciento, entonces el valor actual neto de los flujos de efectivo es negativo. Ya que de nuevo, el primer parámetro que estamos usando en el valor presente es de hecho la TIR. Por lo general, cuando comparas oportunidades de inversión, podrías usar una combinación de la TIR y también el valor actual neto para obtener la cantidad absoluta en dólares para una inversión determinada. Llevemos esto de vuelta al 20.7 por ciento. Si estuviera comparando inversiones solo en base a su tasa interna de rendimiento, solo miraré este número. Pero a veces, cuando estás comparando diferentes oportunidades, es posible que también quieras mirar el valor actual neto absoluto de los flujos de efectivo para tener una idea de cuánto efectivo está entrando. Podrías eliminar este desembolso inicial para ver cuál es ese valor actual neto total, asumiendo varias tasas de descuento. 5. Buscar con facilidad usando tarjetas de animales: A veces no sabes exactamente lo que quieres resumir o buscar o promediar en tu conjunto de datos. Ahí es cuando puedes usar comodines en Excel para hacer algunas búsquedas difusas de tus datos. Puedes usar esta lección para construir un algoritmo de búsqueda difusa para tus compañeros de equipo cuando no saben exactamente lo que están buscando, pero saben algunas letras o algo en el propio nombre. búsqueda con comodines es una de las características más subutilizadas en Excel. La razón por la que me gusta es porque realmente te da a ti y a tus compañeros una forma de buscar datos pero sin saber la ortografía exacta o orden de los personajes de la lista. Creo que es una forma mucho mejor de buscar datos y quieres configurar algo que sea fácil de usar para tus compañeros de equipo. Sólo un ejemplo, vamos a contar el número de direcciones debajo que terminan con las letras street, St, usando una función COUNTIF. Verás estas direcciones a continuación. Algunos de ellos terminan con sur, algunos de ellos terminan con drive, Dr. Vemos que algunos que tienen St, St St St Se podría hacer una variación de la función mid and find, que hemos hecho y que he mostrado en clases anteriores. Pero los comodines realmente hacen de este un ejercicio mucho más fácil. Puedes buscar en Google todos los diferentes comodines disponibles. Pero te voy a mostrar los más comunes que utilizo cuando necesito buscar datos y no sé cómo se presentan los datos específicamente en la lista. Vamos a escribir una función básica COUNTIF, que todos hemos hecho antes. Sólo vamos a pasar por nuestra gama aquí, seleccione eso. El criterio aquí es que en lugar de hacer igual a algún valor específico o lo que sea, o mayor o menor que algún valor, vamos a escribir comillas dobles, luego la estrella St, comillas dobles. Obtenemos el número cuatro. Rápidamente puedes mirar esto y ver que hay cuatro direcciones con la calle de letras al final. Lo que realmente está sucediendo aquí es que Excel está revisando toda esta lista. Esta estrella básicamente le dice a Excel, no me importa lo que venga antes de las letras St, siempre y cuando termine con las letras St. Entonces quiero contar eso en mi función COUNTIF. Si St está en algún lugar en el principio o en el medio, no lo contará. Esta estrella básicamente le dice a Excel cualquier carácter, cualquier número, letras antes de St, es genial. Quiero incluirlo en mi función COUNTIF. Puedes ver aquí si pongo en Rd aquí, solo incluiría esta dirección específica con Buttermilk Road. Si pongo aquí al Dr., entonces sólo tiene dos direcciones que tienen al Dr. al final. El estrella es uno de mis favoritos porque es como hacer una búsqueda difusa en la lista cuando no sabes exactamente cómo se escriben las cosas. No sabes lo que va a venir antes de las letras o después de las cartas, pero sabes que sin duda tiene que terminar con St en este caso por calle, porque ahí es donde te interesa en, en términos de su conjunto de datos. Ahora también podemos usar otras funciones usando estos comodines. Vamos a resumir el número de ventas de estos ID de clientes. Solo vamos a ver a los clientes que tengan nueve caracteres en su ID de cliente usando la función SUMIF. Notarás que algunos de estos tienen 1,2,3,4,5,6,7,8, 9 caracteres, pero otros tienen 10 caracteres porque podrían tener una letra extra ahí ID de cliente. Solo queremos resumir las ventas que tienen identificaciones de clientes con nueve. A lo mejor los clientes que tienen nueve ID de cliente son como los antiguos clientes heredados, algo así. Una forma de hacer esto, por supuesto, es que vamos a estar haciendo una longitud de la celda. Sabe como son nueve y hay unos pocos de 10. Entonces puedes sumar los ID de cliente que tengan un número específico, en este caso nueve. Pero con los comodines otra vez, quiero recalcar lo importante que es usar comodines en ciertas situaciones. Es que no tienes que hacer esta fórmula extra por el lado. Este comodín de signo de interrogación le dice a Excel solo incluir celdas que tengan exactamente ese número de caracteres en las celdas. Voy a escribir SUMIF. El rango va a ser esta lista de identificaciones de clientes. Lo hizo por accidente. Ahora los criterios van a ser signos de interrogación. Nuevamente, pones el número de signos de interrogación que quieres decirle a Excel, aquí tienes el número exacto de caracteres en la celda que quiero incluir en mis criterios. Vamos a hacer 1,2,3,4,5,6,7,8, 9. Esto le dice a Excel que solo tire hacia atrás los valores que tienen nueve caracteres, no 10, no ocho solo nueve, coma. El rango de suma solo serían las ventas y tenemos 14,914. Podrías volver a verificar esto por, fíjate como hicimos el conteo a por aquí. Podríamos hacer iguales SUMIF, solo haciendo esto doble verificar nuestro trabajo. SUMIF si esto equivale a nueve, y luego resumirlo, obtenemos el mismo número, 14,914. Usando una combinación del comodín del signo de interrogación y el comodín estrella, esto le permite hacer algunas búsquedas bastante poderosas en sus datos. Solo buscaré en Google para ver algunos de los mejores comodines que puedes usar y las combinaciones. Hay algunas combinaciones realmente geniales que puedes hacer. Pero usar una combinación de los comodines de estrella y signo de interrogación son probablemente los más comunes que utilizo para buscar mis datos. Para mostrarte cómo esto podría funcionar para tu equipo, tenemos nuestros mismos datos y ahora tenemos dirección más cliente más ventas. Vamos a hacer una búsqueda difusa a BUSCARV. Así es como en realidad podrías hacer algo por tus compañeros de equipo, que es que tus compañeros solo conocen el nombre de la calle en la dirección. Quieren poder escribir rápidamente el nombre de una calle y ver cuál es el número de ventas para esos nombres de calles dadas. Sólo conocen la palabra Suero de leche, o sólo conocen la palabra Ross Clark, o sólo conocen la palabra Montgomery. Pero desconocen el número de dirección o el sufijo que es calle, unidades, círculo o lo que sea. Vamos a escribir una búsqueda difusa permitiendo a nuestros compañeros entrar en algo como podrían en Google donde no conocen la consulta de búsqueda completa y quieren recuperar algo inteligente resultados. Un ejemplo podría ser igual a BUSCARV. El valor BUSCARV aquí que vamos a hacer es doble cotización, estrella, suero de leche, estrella, doble cotización. Vamos a buscar en esta matriz de tablas, que es justo de aquí para aquí. Vamos a retirar el número de ventas, que es 3,0. Normalmente cuando estás haciendo una BUSCARV, siempre estás buscando un valor específico. Quieres emparejar a través de un partido específico. Pero en este caso, le digo a Excel, quiero sacar cualquier dirección que tenga la palabra Suero de leche en ellas. No importa lo que venga antes o después. Por eso tengo estrella antes Suero de Leche y después del Suero de Leche. Esto significa que no me importa cuál sea el número de dirección, no me importa cuál sea la calle de la carretera. Yo sólo quiero saber qué direcciones tienen la palabra Suero de leche en ellas y eso sale a 239 aquí mismo. Esto permite a mis compañeros volver a hacer una búsqueda difusa, dentro de mi fórmula BUSCARV, cuando no conocen la dirección exacta, que es 321 Buttermilk Road. Ahora puedes hacer esto un poco más flexible. Creo que en realidad puedes hacer esto como referencia y creo que lo que puedes hacer aquí es en realidad simplemente hacer una estrella. Digamos que aquí tenemos la palabra Suero de leche. Creo que en realidad puedes borrar esto y hacer estrella, esa estrella. A lo mejor si pongo la cotización alrededor de eso. Esto puede no funcionar tampoco. Tengo que indagar en cómo puedes hacer esto. A lo mejor hay que hacer un ampersand con la referencia celular. Asíva a funcionar. Si quisiera hacer esta búsqueda más flexible para mis compañeros de equipo, podría decir algo como ingresar dirección, nombre de la carretera aquí. Entonces pueden escribir cosas como solo conocen, Montgomery, que es esta dirección de aquí mismo, y retrocede 1562, tal vez solo conocen Florencia. Es como una forma más flexible de buscar cosas sin tener que volver a saber la dirección completa. Juega con los diferentes comodines. Google cómo podrías ser capaz de combinarlos. Pero yo diría que el signo de interrogación, la estrella, los comodines son los que más uso para hacer búsquedas difusas para que mis herramientas y modelos en Excel sean más flexibles para mis compañeros de equipo. 6. Encuentra las entradas óptimas con / busca de metas: [ MÚSICA] Cuando tienes un modelo con múltiples entradas, a veces solo quieres ajustar una entrada para maximizar las ganancias, minimizar los costos o tal vez encontrar el punto de equilibrio dentro de tu modelo o tu negocio. La función de búsqueda de objetivos es perfecta para esto porque puede decirle a Excel que optimice una celda específica usando una entrada en su modelo. En esta lección, vamos a pretender ser una compañía de viaje compartidos como Uber o Lyft para averiguar cuál es el número óptimo de paseos que necesitamos tener en nuestro sistema para alcanzar el punto de equilibrio. Cuando estás construyendo un modelo, a veces quieres poder encontrar la entrada más óptima para maximizar las ganancias, para encontrar el punto de equilibrio sin tener que hacer un montón de prueba y error. Vamos a ver este modelo ficticio de cómo una compañía de transporte, digamos como Uber o Lyft, podría modelar sus ganancias. Tratemos de entender rápidamente lo que representa el D19, este número total de ganancias. Aquí tenemos algunas suposiciones, la cantidad de paseos que esta compañía de viaje compartidos tiene que hacer en su mercado. Su tasa de toma es la cantidad de cada viaje que la compañía de viaje comparte toma de cada tarifa. El total justo por viaje, digamos que el promedio es de 15 dólares. Eso significa que la tasa de toma para esta compañía ficticia es simplemente 25 por ciento por 15 que es 3.75. Digamos que están entrando en un nuevo mercado, como una nueva ciudad para lanzar sus capacidades de viaje compartidos. Podrían tener costos fijos de marketing de $100,000, y su costo por viaje sería simplemente una tarifa total por viaje menos la toma por viaje, que es su beneficio neto total. El beneficio total aquí es simplemente 100,000, [RUIDO] inversión en sus esfuerzos de marketing más su tasa de toma, que es su ganancia en cada viaje, multiplíquelo por el número de paseos que se están realizando. Puedes ver aquí si solo hay 100 paseos entonces tienen una pérdida neta de 99,625. Esto aumenta, digamos a 2 mil. Su pérdida neta es un poco menor. ¿ Cuál es el punto de equilibrio para este ejemplo? ¿ Son 5 mil paseos? No, es demasiado. Con poco, ¿son 50 mil? Esoes un poco demasiado. Se puede ver como pude hacer un montón de prueba y error para averiguar cuál debería ser este número para llegar a un cero. Porque quiero encontrar el número de salidas de equilibrio que le dará a esta compañía ficticia de viaje compartidos un punto de equilibrio de 0 dólares. Estamos ajustando el número de recorridos aquí en D10 y estamos ajustando el beneficio total, pero queremos poder calcular el número preciso de recorridos para lograr que esto llegue al punto de equilibrio. En lugar de adivinar con prueba y error, vamos a utilizar esta función llamada Goal Seek, que tiene una característica que puedes usar para tus modelos. Puedes ir a la pestaña “Datos”, ir a “Qué pasaría si”, y luego ir a “Búsqueda de objetivos”. Esto funciona cuando solo se tiene que maximizar u optimizar una variable específica. En este caso, va a ser el beneficio total. Vamos a la celda que es la celda o valor que queremos optimizar, que es D19. Queremos establecer este valor a cero porque ese va a ser el punto en el que estamos en el punto de equilibrio. ¿ Cómo vamos a llegar a este punto de equilibrio cero? Al cambiar esta entrada, que es el número de paseos. No vamos a cambiar la tasa de toma ni ninguno de estos otros supuestos, solo el número de recorridos porque así es como se construye nuestro modelo. Una vez que tengas estos parámetros establecidos, presiona “Bien”. Verás que Excel presenta rápidamente la solución, que es 26,667 paseos para ser exactos, para alcanzar un punto de equilibrio de $0 cuando tenemos un costo fijo de marketing de $100,000 y tu toma es de 3.75 por paseo. Si agregas uno más aquí, verás como esta ganancia neta ahora es de 5 dólares, por lo que es un poco demasiado alta. Esta es una manera realmente interesante de tratar encontrar el punto de equilibrio o tal vez quieras encontrar el número total de atracciones para llegar a $1 millón en ganancias. Podríamos intentar hacerlo muy rápido yendo a “Data”, “ What-Sif-Analysis”, “Goal Seek”. Podemos establecer, de nuevo, la celda D19. En lugar de establecerlo a cero, lo estableceremos en un millón cambiando la celda D10, que es el número de recorridos. Veamos cuántos paseos se necesitan para llegar a una ganancia de $1 millón, y eso va a ser 293,333 paseos. Cuando trabajas con tu equipo e intentas colaborar para encontrar una solución óptima, en lugar de hacer prueba y error, puedes usar esta función avanzada de datos, que es Objetivo Busque, para encontrar los supuestos o insumos óptimos para maximizar las ganancias, para encontrar el punto de equilibrio, y otras métricas que desea lograr para su equipo. 7. Optimizaciones complejas con resolución: A partir de la lección anterior en la que usamos Goal Seek para optimizar una entrada y maximizar una salida, a veces tendrás casos de uso más avanzados y tus compañeros de equipo te dicen que hay otras restricciones en tu modelo que tienes que dar cuenta. Cuando tiene múltiples variables y múltiples restricciones que debe tener en cuenta a medida que encuentre las entradas óptimas para su modelo, puede usar la función de solucionador avanzado en Excel, que es un ad-in. Vamos a fingir que seguimos siendo esta compañía de viajes compartidos, y tendremos múltiples restricciones y múltiples otras variables a tener en cuenta para maximizar nuestro beneficio para nuestro empresa. Para modelos más avanzados en los que tienes múltiples restricciones con las que necesitas trabajar y quieres maximizar un montón de entradas diferentes, no puedes usar la función de búsqueda de objetivos, que hemos hablado sobre en la lección cinco, vas a tener que usar una función más avanzada llamada Solver. Primero, antes de empezar a usar Solver en nuestro modelo aquí, vamos a rápidamente en el Paso Uno, tomarnos un momento para entender cómo es el modelo. Nuevamente, usando nuestra compañía de viajes compartidos como ejemplo, tenemos algunas suposiciones aquí, la tasa de toma es del 25 por ciento. Ahora tenemos un supuesto de el número total de conductores asignables es el número total de conductores que Uber o Lyft o lo que sea, puedan tener a su disposición para trabajar en su empresa para proporcionar paseos a los clientes. Hay un total de 300,000 conductores que se han inscrito para conducir en nuestra compañía de viajes compartidos, y el número total de viajes que cada conductor puede hacer por día es de 10. Estas son solo tres suposiciones que tenemos en nuestro modelo. Ahora bien, si miramos esta lista de ciudades, estas son ciudades potenciales en las que nuestra compañía de viajes compartidos podría querer operar. Esta columna, los viajes solicitados por día, este es el número máximo de viajes que se solicitan para ese día. Si tenemos una suposición de 10 viajes por conductor por día, eso significa que los conductores necesarios en cada una de estas ciudades son simplemente los viajes solicitados por día divididos entre 10. Esto, verá es el número total de conductores necesarios. El número total es en realidad 386,000, lo que es más que el número de conductores asignables que tenemos para todos nuestros clientes. Tenemos nuestro tema de oferta y demanda aquí. También tenemos estos insumos de la tarifa promedio por ciudad. Se puede ver que esto está ordenado en orden descendente. Bueno, está ordenado en orden descendente. Nueva York tiene la tarifa promedio más alta, bajando con Mumbai siendo la más baja. Si asumimos esta tasa de toma del 25 por ciento, que es la ganancia por viaje de nuestra compañía de viajes compartidos, simplemente tomamos el 25 por ciento, la multiplicamos por la tarifa promedio, y luego podemos obtener la ganancia por viaje. Ahora nuestro objetivo es ingresar el número de conductores asignados aquí para maximizar el beneficio total. Mira aquí, si pongo 100 aquí, obtenemos una ganancia de 469, que es simplemente 100 veces la ganancia por viaje. Si pongo otros 100 aquí, obtenemos 336 porque la tarifa es más baja, y aquí obtenemos una ganancia total, 805. Si hicieras esto a mano o manualmente, mirarías las restricciones en este modelo, que es, bueno, sabemos que aquí solo necesitamos 45,000 conductores. Una tentación es decir, bueno, si tenemos 300 mil conductores disponibles, ¿por qué no los ponemos a todos en la ciudad de tarifa más alta y podemos obtener una ganancia total de 1.4 millones? Bueno, el problema es que no necesitamos 300 mil conductores en Nueva York, solo necesitamos 45 mil. El número máximo de conductores que podemos poner en este mercado para Nueva York es simplemente 45,000. Esta es la ganancia máxima que podemos sacar de Nueva York que es de 210 mil. Ahora podrías ir por la línea y ver, bien, bueno, nuestra próxima ciudad más rentable, en realidad parece que va a ser Londres, 4.39. Vamos a tratar de maximizar el número de conductores que queremos en esta ciudad, que es de 37,400. Entonces la siguiente ciudad más rentable parece que va a ser Sao Paulo, así que podríamos hacer 20,000 aquí. Podemos simplemente seguir revisando la lista hasta llegar a un número máximo de conductores asignados, que va a ser nuestra restricción aquí, que es 300,000. Esto va a ser obviamente muy lento para hacerlo sobre todo con una larga lista de ciudades. Pero no quiero hacer esto manualmente yendo por cada ciudad y encontrando qué ciudad tiene más ganancias para mi compañía de viajes compartidos, y luego tratando de enchufar el número máximo disponible los conductores necesarios para maximizar el beneficio. Queremos averiguar cuál es nuestro máximo beneficio dado estas limitaciones en el sistema. Ahora que entendemos este modelo muy rápido, veamos el Paso Número Dos. De hecho, acabamos de hacer esto un poco. Lo hicimos a través de prueba y error, agregando conductores a la columna asignada del conductor para ver cómo esto afecta el beneficio total. Rápidamente descubrimos que no podemos simplemente asignar 300,000 conductores a la ciudad más rentable, porque no necesitamos 300,000 conductores en Nueva York. Solo necesitamos 45,000 como máximo. En lugar de hacer esto manualmente, una ciudad por una ciudad para maximizar nuestras ganancias, vamos a utilizar la función Solver. Si aún no tienes Solver en tu Excel, verás esto en el menú Datos, y verás Solver aquí. Si no tienes eso, haz clic en el menú “Herramientas” en tu Excel, realidad está fuera de la ventana aquí, pero deberías tener una pestaña Herramientas en tu Excel. Luego ve a complementos de Excel, y deberías ver el complemento Solver aquí mismo. Si no lo ves, solo quítalo aquí mismo, dale, “Bien”. Después verás que Solver aparece debajo la pestaña Datos en la cinta. Ahora vamos a dar click en la pestaña “Solver” para ayudarnos a calcular el número óptimo de drivers para poner en la columna de drivers asignados. Ahora, antes de entrar en el menú de Solver, hay algunas cosas que queremos ver aquí para averiguar cómo configurar esto. Queremos fijar el objetivo a la ganancia total, que va a ser ésta. Bueno, pero cambia el número de conductores asignados, que son estas celdas de aquí mismo. Las restricciones incluyen conductores asignados deben ser menores o iguales a los conductores necesarios, que es simplemente este número, tiene que ser menor a 300,000. El total de conductores asignados también debe ser menor o igual que los conductores asignables. Eso significa que este número no puede ser mayor que este número, y también todos estos números tienen que ser enteros. Eso es mucho hablar. Solo echemos un vistazo para ver qué significa esto usando el menú de Solver real. Esto podría aparecer un poco pequeño en la pantalla, pero vamos a pasar por esto uno por uno. Tenemos algún objetivo. Queremos este objetivo, que es la celda I28, ese es nuestro beneficio total de todas nuestras ciudades después de haber asignado conductores. Queremos simplemente maximizar ese número. Podemos establecer esto en algún número. En este caso, solo queremos encontrar el número máximo óptimo que podamos usar, que podamos calcular dadas las restricciones en el modelo. Ahora, nos fijamos nuestro objetivo para ser I28, que es nuestra ganancia total, y necesitamos decirle a Excel cuáles son las etiquetas que vamos a sacar para llegar a esa ganancia máxima. Esa va a ser esta forma aquí, cambiando celdas variables. Va a ser de H17 a H27. Estos son los conductores que queremos asignar a cada ciudad diferente. Vamos a estar cambiando todas estas celdas o mejor dicho Solver va a estar cambiando todas las celdas usando el algoritmo. Ahora vamos a poner en las limitaciones dentro de nuestro modelo. Esta es la parte más importante de Solver, porque tenemos que decirle a Excel cuáles son las restricciones con las que estamos trabajando para llegar a esta ganancia máxima. Empecemos a agregar restricciones. La primera restricción de la que hablamos es el número de conductores asignados. Esto tiene que ser menor o igual al número total de conductores necesarios. Porque recuerden, no podemos poner más de 45 mil conductores en Nueva York, más de 35 mil en Los Ángeles. Eso es lo que dices, H17 a H27 tiene que ser menor o igual que E17 a E27. Vamos a agregarlos a la restricción. Vamos a decir, el conductor tiene asignado este número total aquí mismo, que resume todos los conductores asignados en esa columna, tiene que ser menor o igual al número total de asignables choferes, que es 300,000. Si recordamos, en nuestro análisis de mercado total, necesitamos más conductores de los que realmente tenemos, así que tenemos que encontrar la mejor manera de optimizar este pequeño grupo de 300,000 conductores, y agregar. La restricción final es que estos números, Excel va a poner esto a través de algún algoritmo. Queremos asegurarnos de que eso no viene con algunas fracciones o decimales, porque no hay tal cosa como la mitad de nosotros chofer, tercio de un chofer. Vamos a decir que todos estos números tienen que ser enteros. Simplemente diciendo que esto es un entero. Tenemos esas tres limitaciones en nuestro sistema, golpeamos “Bien”. Ahora nuestro menú Solver está prácticamente todo configurado. Tenemos nuestro objetivo, tenemos las celdas que estamos cambiando, que es el número de conductores asignados. Entonces tenemos estas tres limitaciones. Ahora algunas cosas aquí las queremos ver. Probablemente quieras marcar esto, hacer que las variables no restringidas no sean negativas. Esto simplemente significa que estas variables que estamos cambiando tienen que estar por encima de cero. Realmente no tiene sentido tener asignados 500 conductores negativos, así que queremos marcar esto. Normalmente, querrás usar el método o algoritmo de resolución lineal GRG. Esto maneja la mayoría de las situaciones, lineales y no lineales . Déjalo como predeterminado. Puede hacer clic en “Opciones” aquí. Hay un montón de otras opciones que puedes hacer, como Ignorar restricciones de enteros. Obviamente queremos mantener eso bajo control porque queremos mantener estas limitaciones. Un GRG lineal, use multi-inicio, haga clic en eso para encontrar respuestas más precisas. Pero por lo general, hablando en este modelo así, puedes simplemente dejar todos estos elementos del menú tal cual, pulsa “Bien”. Tenemos nuestro Solver configurado. Intentemos ver si Solver puede averiguar el número total de conductores a asignar para cada ciudad diferente. Golpea “Resolver” y guau, eso fue bastante rápido. Solver encontró una solución. Vamos a golpear “Bien”. Solo vamos a presionar “Cerrar”, y veamos qué se le ocurrió a Solver. Como era de esperar, trató de maximizar las ciudades con más ganancias como Nueva York, puso 45,000 ahí, 35,000 Los Ángeles. Estos son todos los máximos. Pero se puede ver a medida que baja a las ciudades de menor ganancia, solo asignó 33 mil 600 choferes a Sao. En realidad decía, vamos a poner cero conductores en Hong Kong y cero en Mumbai porque esas son nuestras ciudades menos rentables, fin de mantener este número máximo de conductores a tan solo 300, 000, que tenemos aquí el supuesto de financiamiento. Así es como puedes usar Solver en tu equipo, para resolver básicamente un problema de optimización complejo donde tienes múltiples restricciones, estás cambiando múltiples palancas o múltiples celdas aquí, que son estas celdas, y tienes algún número al que intentas golpear. Podría ser un máximo, podría ser un mínimo, podría ser un cierto valor. En este caso, sabemos que dadas nuestras limitaciones en nuestro sistema de 300,000 conductores, y estos conductores necesitaban, el beneficio total que podemos obtener en este escenario es de poco más de un millón de dólares. Así es como puedes usar Solver, una característica mucho más avanzada en Excel para evitar que tengas que hacer prueba y error, y pasar por esto manualmente, y solo tener a Solver hacer el duro levantando para encontrar estos números. 8. Reflexiones finales: Muchas gracias por tomar esta clase sobre características y funcionalidades avanzadas de Excel. Espero que te vayas con más habilidades de pensamiento crítico y analítico a la hora de evaluar y analizar tus datos en Excel. La parte más importante de algunas de estas características y funciones en Excel es conocer los casos de uso específicos y escenarios de nicho en los que utilizarlos. Pero cuando los aprendes, puedes hacer un caso de negocio para traer vuelta a la gerencia o a C-suite sobre si invertir o no en un negocio específico o una oportunidad de inversión. Por favor, publique sus preguntas y pensamientos en el foro de discusión de la clase. Estaré encantado de brindarte comentarios y también tomar capturas de pantalla de tu proyecto de clase mientras estás trabajando en ellos en la sección de proyectos de esta clase. Espero verte en una de mis otras clases de Excel aquí en Skillshare.