EXCEL PARA NEGOCIOS La creciente competencia en los

EXCEL PARA NEGOCIOS La creciente competencia en los negocios exige un proceso de toma de decisiones cada vez más complejo. El no incorporar la tecnología necesaria, llevaría a un deterioro permanente de la productividad y la competencia en las empresas. Por lo anterior, es indispensable que se cuente con herramientas ágiles y precisas que permitan analizar una situación y evaluar los resultados, antes de tomar una decisión.

La hoja de cálculo excell ha ganado un espacio como herramienta básica de trabajo en las empresas. Pone a disposición de la Gerencia herramientas para construir modelos financieros que permitan analizar el impacto de una decisión en los objetivos y resultados de la empresa. EMPRESA Es un sistema integrado por un conjunto de personas, procesos, bienes y relaciones que se ponen bajo la dirección de un administrador para que alcance unos objetivos predeterminados, formando así una estructura con decisión económica.

FUNCIONES Son fórmulas que resuelven problemas generales; su característica principal es que de una manera rápida y sencilla ejecutan cálculos complejos. Las funciones dejan el resultado que calculan en la celda donde han sido escritas. El principio de las funciones es ahorrar tiempo en los cálculos dispendiosos, esto quiere decir que es más importante conocer el significado del resultado que arroja que conocer la mecánica de su creación.

Ejemplo sobre el valor de cuotas trimestrales de un crédito en dólares Las entradas son la variables habituales de un crédito: Valor del crédito, plazo, tasa interés, tipo de cambio y tasa de devaluación El proceso son operaciones que se hacen con las variables de entrada para poder calcular las salidas. Ej: calcular el interés periódico y la devaluación del período Las salidas son las cifras que se quieren obtener en el modelo, en este caso el valor de la cuota en pesos

Ejemplo sobre el valor de cuotas mensuales de un crédito en pesos

TABLAS DE DATOS Permite calcular el valor que tomará una variable intermedia o una variable de salida, ante los valores concretos que se asignan a una o dos variables de entrada, intermedios o de salida. La función de las tablas de datos es medir el efecto que una variable de entrada tiene en una variable de salida.

Ejemplo de tabla de datos Cuál es el valor de la cuota de un crédito de $5. 000, con cuotas mensuales, concedido a un plazo de un año, a diferentes valores de tasa de interés. Aquí se busca el valor que tomará una variable de salida (la cuota), ante cambios en una variable de entrada (la tasa de interés), permaneciendo constantes las demás variables. Para resolver este caso se recurre a las tablas de datos, los pasos para utilizarlas son las siguientes:

1. Se efectúan los cálculos originales, en este caso se calcula el valor de la cuota de un crédito.

2. Se digita los valores que tomará la variable cuyo efecto se desea probar (variable independiente). En este caso para el cambio, la tasa de interés se tomará con variaciones de 0, 25 puntos porcentuales hacia arriba y hacia abajo del valor utilizado en el modelo (2% como se aprecia en la celda B 2)

3. Colocar en la celda diagonal al primer valor escrito (en el ejemplo G 3 que está diagonal a F 4) una referencia a la celda que contiene la variable de respuesta que se está analizando (en G 3 se escribió =B 5) (variable dependiente).

4. Se selecciona el rango de la tabla (F 3: G 12) y se elige la opción DATOS / TABLA del menú de texto, para que se abra el diálogo de tabla. El cuadro de diálogo ofrece 2 cajas de entrada para introducir la dirección de la celda donde se encuentra la variable cuyo efectos se está probando. Celda de entrada fila: se utiliza cuando los valores que se asignan a la variable se han escrito a lo largo de una fila. Celda de entrada columna: se utiliza cuando los valores que se asignan a la variable se han escrito a lo largo de una columna, como es el caso del ejemplo que se está desarrollando (columna F 4: F 12)

BUSCAR OBJETIVO Permite calcular cuál es el valor que debe tener una variable de entrada para que una variable de proceso o de salida tome un valor determinado que se considera como un objeto a alcanzar. Se debe tener en cuenta las siguientes condiciones para las variables que se utilizan en la Herramienta Buscar Objetivo:

1. La variable a la cual se le busca el valor, se debe digitar como valor en las entradas. 2. La variable a la cual se le asigna un valor como objetivo debe estar en el modelo como una fórmula y puede estar ubicada en el proceso o en las salidas. 3. La variable objetivo o celda objetivo, no es necesario que contenga directamente en su fórmula la variable elemental, pero se requiere que la variable elemental influya (directa o indirectamente) en el resultado de la misma.

Ejemplo de Buscar Objetivo Cuál es el valor máximo de la tasa de interés para que el valor de la cuota se mantenga por debajo de $500. 000. El objetivo es mantener la cuota por debajo de un valor determinado, cambiando la tasa de interés. Los pasos son los siguientes: 1. Se elige la opción Herramientas / Buscar Objetivo (Datos / Análisis Y si), para que se despliegue la ventana Buscar Objetivo; no se requiere estar ubicado en alguna celda en especial dentro del modelo, ya que la ventana pide que se digiten las direcciones de las celdas y el valor que debe tomar la celda objetivo.

2. Se introducen los valores en las cajas de entrada de la ventana Buscar Objetivo, así: • Definir Celda: Se introduce la dirección de la celda objetivo, o sea la variable intermedia o de resultados, la cual se desea que alcance un valor determinado. En el ejemplo B 15, que es la dirección donde se calcula el valor de la cuota.

• • Con el Valor: Se digita el valor objetivo que se desea alcanzar en la respuesta de la celda objetivo. Tiene que digitarse un valor y no es posible utilizar direcciones. En el ejemplo $500. 000 que es el valor fijado como objetivo. Para Cambiar la Celda: Se introduce la dirección de la celda cambiante, o sea la dirección de la variable elemental que influye sobre la respuesta. En el ejemplo B 12, que es la dirección donde se encuentra la tasa de interés, que es la variable elemental que se está probando.

3. Se selecciona Aceptar y se obtiene una ventana donde se informa, el estado de la búsqueda del objetivo: Si el Excel encuentra una solución y se está satisfecho con ella, se selecciona nuevamente Aceptar para que se modifique el modelo con el nuevo valor de la variable elemental. En el ejemplo, para que el valor de la cuota sea de $500. 000 se puede aceptar máximo una tasa interés del 2, 9228541%

ESCENARIOS Es una herramienta del Excel que permite almacenar y recuperar varios valores en una celda de entrada. En los modelos financieros con Excel, un escenario está conformado por un conjunto de variables de entrada y se dice que es una hipótesis sobre el comportamiento que ese conjunto de variables pueda tener. Como no se conocen los valores que tomarán las variables en el futuro, se debe recurrir a hipótesis (escenarios) sobre su comportamiento futuro.

Las hipótesis deben provenir de estudios efectuados sobre previsiones del futuro, ya sea por el administrador financiero o por entidades externas (gobierno, gremios, etc. ) El administrador de escenarios de Excel permite: 1. Hacer supuestos sobre el comportamiento futuro de un conjunto de variables de entrada (hipótesis). 2. Medir el impacto de ese comportamiento en los resultados del modelo y 3. Presentar un resumen de los resultados de las diferentes hipótesis que se estén evaluando.

Ejemplo de Escenarios Los pasos para crear y usar los escenarios del Excel son los siguientes: 1. Planificar los escenarios que se utilizarán, definiendo: a) ¿Cuántos escenarios (hipótesis) se estudiarán? b) ¿Cuáles variables conformarán cada escenario? c) ¿Cuál será el valor que tomarán las variables en cada escenario?

Se definen tres escenarios sobre la situación futura de los créditos bancarios: Situación actual, restricción leve de los créditos y restricción grave de los créditos. Cada uno de los escenarios anteriores está compuesto por dos variables: Tasa de Interés y Plazo de los Créditos.

Finalmente, en cada escenario, las variables toman los siguientes valores: Los valores que se asignen a las variables en cada escenario no tienen ninguna proporcionalidad, ya que sólo obedecen a las especulaciones que se hagan sobre el futuro de una situación.

2. Se elige HERRAMIENTAS / ESCENARIOS del menú de texto y se despliega la ventana principal de la herramienta. En el Excel 2007: Datos / Herramientas de Datos / Análisis Y Si Como no se han creado todavía escenarios, la venta se presenta con el siguiente aspecto: Para crear los escenarios que se hayan planificado, se debe entrar por el botón Agregar que permite iniciar el trabajo

3. El botón Agregar despliega una ventana en la cual se puede crear el primer escenario, diligenciando las dos cajas de texto.

Nombre del Escenario: Es el nombre que se haya elegido para la hipótesis en la etapa de planificación. Se recomienda asignar un nombre que posteriormente sirva para identificar la situación que se está analizando. Celdas Cambiantes: Es la dirección donde se encuentran las variables elementales (celdas de entrada) que conforman la hipótesis. Las celdas cambiantes tienen que contener números, es decir que no se pueden utilizar variables intermedias ni de resultados.

4. Al aceptar la entrada de los datos anteriores, se muestra una ventana que permite la entrada de los valores que van a tomar las variables. Para los otros escenarios se cambian los valores $B$2 = 0, 025 y $B$3 = 10. Para continuar creando los otros escenarios que se han planificado se oprime el botón agregar y se repiten los pasos 3 y 4.

5. Para ver el efecto de cada escenario basta con seleccionarlos en la caja de cada escenario y oprimir el botón Mostrar e inmediatamente se modifican los datos de entrada en el modelo.

6. Se puede apreciar los resultados oprimiendo el botón Resumen. Al oprimir aceptar se inserta una hoja nueva donde se presentan los escenarios creados.

SOLVER Permite modificar simultáneamente un conjunto de variables de entrada para optimizar el resultado de una variable de salida. Se deben seguir los siguientes pasos generales: ØIdentificar el objetivo o sea determinar el valor al cual se desea llegar. ØConocer las condiciones que impone internamente la disponibilidad de recursos y los objetivos de la empresa, y externamente el entorno económico y legal. ØAplicar la técnica para encontrar la combinación óptima de recursos

EJEMPLO: Se determinará cuál es la mejor combinación de fuentes de crédito para minimizar el valor de la cuota. Una empresa que requiere $30. 000 y tiene cupo aprobado en tres fuentes, cada una de las cuales ofrece tasa, plazo y comisión diferente, por lo tanto le interesa conocer cuánto debe utilizar de cada cupo para satisfacer sus necesidades y pagar la menor cuota posible. Los pasos para utilizar el Solver son los siguientes:

1) Construir el modelo con cualquier combinación de las fuentes, sin importar el resultado que arroje la suma de las cuotas.

2) Definir los términos del modelo, que consiste en determinar el objetivo y las restricciones: a) El objetivo es la dirección de la variable que se quiere optimizar; en este caso es la suma de las cuotas, que se han calculado en la celda F 13, la cual se busca que tenga el menor valor posible. Esta se llama celda objetivo. b) Las restricciones son las limitaciones técnicas, legales, etc. Internas o externas de la empresa, que impiden que se utilicen los recursos ilimitadamente, tienen que ver con el funcionamiento del modelo y en el ejemplo que se sigue se han definido 3 restricciones así:

Ø El porcentaje que se utilice de cada fuente debe ser mayor o igual a cero (restricción de no negatividad). Este porcentaje está expresado en el rango de B 6: D 6. Ø El monto que se utilice de cada fuente debe ser menor o igual al cupo disponible. Este monto se expresa en el rango B 8: D 8, como una fórmula que multiplica el cupo aprobado por el porcentaje de utilización. Ø La suma de los cupos utilizados debe ser igual a la necesidad de recursos. Los recursos necesitados están en la celda B 1 y la suma de uso de los cupos en la celda F 8.

Activar función SOLVER en la hoja de excel

Activar función SOLVER en la hoja de excel

3) Se elige HERRAMIENTAS / SOLVER del menú de texto:

a) Celda Objetivo: para introducir la dirección de la celda que se ha definido como objetivo, en este caso F 13, donde se suman las cuotas a pagar en cada fuente. b) Valor de la Celda Objetivo: para definir cuál es el objetivo que se busca para la celda, ya sea buscar el valor máximo, el mínimo o hacerla igual a un determinado valor. En el ejemplo se elige minimizar. c) Cambiando las Celdas: para introducir la dirección de las celdas que se deben ajustar hasta que la celda objetivo se optimice; estas celdas ajustadas debe estar relacionadas directa o indirectamente con la celda objetivo.

Esta área tiene el botón Estimar que sirve para el excel muestre las celdas que contienen las variables elementales y que tienen relación con la celda objetivo. Este es el conjunto de variables elementales que se ha seleccionado para que tenga una combinación óptima (B 6: D 6) d) Sujetas a las siguientes restricciones: para introducir la lista de restricción que debe cumplir la solución, se opera mediante los botones agregar, cambiar y eliminar. El primero sirve para crear restricciones nuevas y los dos últimos para modificar restricciones existentes. Para crear nuevas restricciones se procede así:

Ø Referencia de la Celda: Es la dirección donde se encuentra la variable que tendrá la restricción. Puede ser un rango o una celda, que contenga variables intermedias o de resultados. Ø Caja de Operador: Es una lista de desplegables que ofrece las alternativas de relación entre la celda y la restricción. Ø Restricción: Es la condición que deben cumplir las celdas de la restricción. Puede ser un valor, una fórmula o una referencia a una celda. Hay 3 restricciones en el ejemplo:

1) El porcentaje de utilización de los cupos debe ser mayor o igual que cero 2) El monto utilizado de cada fuente debe ser menor o igual que el cupo aprobado en cada fuente. 3) La suma de los cupos utilizados debe ser igual a los fondos necesitados.

4) Se resuelve el problema oprimiendo el botón Resolver y el Solver muestra un mensaje en la ventana de resultados, informando si la respuesta hallada satisface las restricciones o no.

5) Al aceptar la respuesta se modifica el modelo con los siguientes valores:

Se observa que la suma de las cuotas toma el valor de $2. 212. 562, que es el menor valor que puede adquirir, utilizando $15. 000 de la fuente B (un 75% del cupo aprobado) y $15. 000 de la fuente C (la totalidad del cupo aprobado). Como se ve no utiliza la fuente A, pues aunque es la de menor tasa de interés, el plazo es el más corto lo que hace que la cuota sea muy alta. El costo ponderado del crédito es 4. 17% mensual. Este es un modelo para una empresa que posiblemente tiene problemas de liquidez y busca una cuota muy baja, sin importar el costo del crédito.

Si el caso es para una empresa que busca rentabilidad, sin importar el valor de la cuota , el objetivo ahora es minimizar el costo del crédito y para ello basta con modificar la celda objetivo del valor de la cuota (F 13) al costo ponderado (F 15). La venta de parámetros del solver queda así:

Solo cambia la celda objetivo de F 13 (suma de las cuotas) a F 15 (costo ponderado del crédito), y la respuesta es la siguiente: En este caso utiliza la totalidad del cupo de la fuente A y B y no utiliza la fuente C. El costo ponderado del crédito baja a 4, 0% mensual, pero la cuota sube a $2. 658. 078.