Descarga la aplicación para disfrutar aún más
Vista previa del material en texto
Casos prácticos de Excel aplicados a la gestión empresarial No está permitida la reproducción total o parcial del presente manual bajo cualquiera de sus formas gráficas o audiovisuales sin la autorización previa y por escrito de los titulares del depósito legal. Impreso en España – Printed in Spain ISBN: 978-84-92578-43-6 DEPÓSITO LEGAL: MA-1485-2008 Este manual es propiedad de: PUBLICACIONES VÉRTICE S.L. C/ Ter 2-4-6 Pol. Ind. El Viso 29006 Málaga. Tfno: 902 53 24 32 www.editorialvertice.com info@editorialvertice.com www.editorialvertice.com mailto:info@editorialvertice.com ÍÍNNDDIICCEE GGEENNEERRAALL TEMA 1. CONTROL DE STOCK. 1.1. ELABORACIÓN DE UN PRESUPUESTO . . . . . . . . . . . . . . . . . . . . . . .1 1.2. PRESUPUESTO CON CÓDIGOS AUTOMÁTICOS . . . . . . . . . . . . . . . . .6 1.3. CONTROL DE STOCK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15 IDEAS CLAVE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21 AUTOEVALUACIÓN DEL TEMA 1. . . . . . . . . . . . . . . . . . . . . . . . . . .22 TEMA 2. GESTIÓN FINANCIERA. 2.1. PRÉSTAMOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .23 2.2. PAGOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .27 2.3. AMORTIZACIÓN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .37 2.4. VAN / TIR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .40 2.4.1. VAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .40 2.4.2. TIR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .41 IDEAS CLAVE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43 AUTOEVALUACIÓN DEL TEMA 2. . . . . . . . . . . . . . . . . . . . . . . . . . .44 TEMA 3. GESTIÓN CONTABLE. 3.1. DIARIO DE CAJA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45 3.2. COSTES DE PRODUCCIÓN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .52 IDEAS CLAVE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .61 AUTOEVALUACIÓN DEL TEMA 3. . . . . . . . . . . . . . . . . . . . . . . . . . .62 Casos prácticos de Excel aplicados a la gestión empresarial I Índice General Ed.1.0. TEMA 4. NÓMINAS Y SEGURIDAD SOCIAL. 4.1. NÓMINA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63 4.2. TC2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71 4.3. ANTICIPOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .75 IDEAS CLAVE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .80 AUTOEVALUACIÓN DEL TEMA 4. . . . . . . . . . . . . . . . . . . . . . . . . . .81 TEMA 5. GRÁFICOS. 5.1. INTRODUCCIÓN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .83 5.2. GRÁFICO DE COLUMNAS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .83 5.3. GRÁFICO CIRCULAR O DE SECTORES . . . . . . . . . . . . . . . . . . . . . .87 5.4. GRÁFICO DE LINEAS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .92 5.5. GRÁFICOS DINÁMICOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .95 IDEAS CLAVE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .98 AUTOEVALUACIÓN DEL TEMA 5. . . . . . . . . . . . . . . . . . . . . . . . . . .99 Índice General Ed.1.0. II Casos prácticos de Excel aplicados a la gestión empresarial TEMA 1 CONTROL DE STOCK 1.1. ELABORACIÓN DE UN PRESUPUESTO. 1.2. PRESUPUESTO CON CÓDIGOS AUTOMÁTICOS. 1.3. CONTROL DE STOCK. 1.1. ELABORACIÓN DE UN PRESUPUESTO. Una de las tareas más frecuentes en las empresas es la realización de presupuestos, en los cuales, se detalla una lista artículos o servicios junto con sus precios unitarios y las cantidades requeridas de cada uno. A Partir de estos datos generamos el precio total por cada artículo o servicio, multiplicando el precio unitario por la cantidad requerida. Finalmente se calcula un Total global que se origina en la suma de todos los totales de cada artículo o servicio y sobre éste se aplica un porcentaje de descuento, el cual dará lugar al importe final. Sólo queda por aplicar los impuestos y tendremos el resultado neto. El presupuesto que vamos a elaborar es similar al siguiente: Ed. 1.0 Tema 1: Control de Stock. Casos prácticos de Excel aplicados a la gestión empresarial 1 Comenzaremos creando un área donde se introducirán posteriormente los datos de los clientes. El área abarcará las celdillas comprendidas entre las coordenadas A2 y B5. Para facilitar la posterior entrada de datos en dicho área realizaremos cuatro uniones horizontales que incluyan las celdas de las columnas 2 y 3. Por ejemplo, en el primero de los casos marcamos las celdas A2 y B2 Y seguidamente seleccionamos el botón de herramientas combinar y centrar. Una vez realizadas las uniones seleccionaremos todas y trazaremos un cuadro alrededor de ellas utilizando la opción bordes de la barra de herramientas seguida de bordes externos. Esta opción será la que deberá utilizarse para aplicar el rayado en otras partes del presupuesto. Téngase en cuenta las diferentes opciones tanto de tipo de líneas como de aplicación de las mismas. Finalmente en la celda A1 introduciremos el título del Área que hemos creado: “DATOS CLIENTE”. Las líneas que aparecen de color gris dentro del área creada no son visibles al imprimir el documento, por lo cual, no debemos preocuparnos por ellas. En el caso de las áreas Nº DE PRESUPUESTO Y FECHA sólo se ha seleccionado una celda y se ha recuadrado la misma (celdas E2 y E4) utilizando la columna D para incluir las etiquetas de las mismas (D2 y D4). El resultado final será el siguiente: Una vez finalizada la cabecera del presupuesto pasaremos a crear el cuerpo del mismo, es decir, el área donde aparecerán los diferentes artículos o conceptos que forma nuestro presupuesto. En nuestro caso y a modo de ejemplo hemos creado una línea donde deberá recogerse el código del artículo, la descripción, el P.V.P., la cantidad y el importe. Este último será el resultado de multiplicar el P.V.P. por la cantidad. Vamos a realizar una previsión en la cual se contempla que nuestro presupuesto pueda llegar a soportar hasta 20 artículos distintos. Para crear la cuadrícula seleccionaremos las celdas comprendidas entre la A8 y la E27. Nuevamente seleccionamos la opción bordes de la barra de herramientas seguida de todos los bordes. En la fila 7 y desde la celda A7 a la E7 introducimos los nombres de las columnas. El siguiente paso será calcular el total por línea. Calcularemos el primero de ellos y copiaremos la formula en las restantes 19 celdas de totales. Tema 1: Control de Stock. Ed. 1.0 2 Casos prácticos de Excel aplicados a la gestión empresarial Desplazamos el cursor a la celda E8 en introducimos la siguiente formula: =C8*D8 para que multiplique el P.V.P. (C8) por la cantidad (D8). Al pulsar la tecla Enter el resultado que aparecerá será cero. Al introducir datos en las celdas anteriormente indicadas variará dicho valor para expresar el resultado de los cálculos. Para copiar la fórmula en el resto de las celdas, hacemos un “clic” con el puntero sobre la celda E8 y desplazamos el mismo a la esquina inferior derecha de la celda. El puntero cambiará su forma de cruz blanca a una cruz negra, pulsamos el botón izquierdo y arrastramos hasta la celda E27. Este proceso habrá copiado la fórmula en cada celda y la habrá transformado a las coordenadas correspondientes a cada una de las filas. A continuación, en la celda E28 calcularemos el total global. En este caso aplicaremos la función SUMA. Desplazamos el puntero a la celda E28 y escribimos la fórmula =SUMA(E8:E27). De esta forma le estaremos indicando a Excel que sume los valores incluidos entre las celdas E8 y E27ambas incluidas. El proceso anterior puede realizarse también seleccionado el botón Autosuma de la barra de herramientas y seleccionando a continuación las celdas que se desean sumar. En la celda D29 introduciremos el porcentaje de descuento que haremos sobre el total anterior. Al tratarse de un valor expresado en forma de porcentaje, deberemos cambiar el formato de la celda. Este procedimiento se realizará desplegando la opción Formato de la Barra de menú y escogiendo la opción Celda. El resultado será la visualización de una ventana de opciones en la cual deberá seleccionarse la pestaña número. Ed. 1.0 Tema 1: Control de Stock. Casos prácticos de Excel aplicados a la gestión empresarial 3 Finalmente puede ajustarse el número de decimales y reducirlo o aumentarlo (por defecto 2) según las necesidades. En el siguiente paso situaremos el puntero en la celda E29 y multiplicaremos el Total anterior (E28) por el porcentaje de descuento (D29). La fórmula sería = E28 * D29. El resultado de esta operación puede dar un número de decimales excesivo, para provocar un redondeo deberemos emplear la función REDONDEAR, cuya sintaxis es: REDONDEAR (Cantidad; nº de decimales). En nuestro caso la fórmula expresa quedaría modificada de la siguiente forma: =REDONDEAR(E28*D29;2). De esta forma le estaríamos indicando que el redondeo tuviese sólo dos decimales. El importe final será el resultado de restar el descuento al importe total. El procedimiento consistiría en colocar el curso sobre la celda E30 y aplicar la siguiente fórmula: =E28-E29. El cálculo del IVA consistirá en multiplicar el importe final (E30) por un 16% que corresponde al tipo de IVA que se aplica sobre nuestros productos. La fórmula en este caso sería: = E30*16%. Al igual que en el caso del descuento puede aplicarse la función REDONDEAR para que se ajuste el número de decimales. Puede ocurrir que los productos que vendemos tengan diferentes tipos de IVA, en cuyo caso la aplicación del IVA se realizaría sobre cada una de las líneas del presupuesto y no sobre el total de la misma. El Importe Final será el resultado de sumar al neto el total del IVA. La fórmula sería: = E30 + E31. Para que la factura se parezca lo más posible al resultado que deseamos obtener impreso podemos eliminar la visualización de las líneas que Excel coloca por defecto para identificar las celdas. Este procedimiento se realiza seleccionando Opciones del menú Herramientas contenido en la Barra de menú. Tema 1: Control de Stock. Ed. 1.0 4 Casos prácticos de Excel aplicados a la gestión empresarial Se visualizará una nueva ventana en la que deberemos seleccionar la pestaña con el nombre Ver y desmarcamos la opción Líneas de división. Finalmente el resultado que se visualice antes de meter datos, debe ser similar al siguiente: Ed. 1.0 Tema 1: Control de Stock. Casos prácticos de Excel aplicados a la gestión empresarial 5 Al introducir datos sobre el presupuesto veremos que los totales por línea así como los totales del presupuesto van cambiando. En la siguiente imagen puede apreciarse un presupuesto realizado con el formato creado. 1.2. PRESUPUESTO CON CÓDIGOS AUTOMÁTICOS. Sobre el presupuesto anterior vamos a realizar una variante para que al introducir el código de un producto devuelva de forma automática la descripción y el precio del producto. Para poder realizar este ejercicio es necesario que previamente en Excel se haya registrado una lista que contenga el código del producto, la descripción y el P.V.P. Esta información la recogeremos en una segunda hoja del libro para que el usuario no vea dichos datos. Tema 1: Control de Stock. Ed. 1.0 6 Casos prácticos de Excel aplicados a la gestión empresarial Comenzaremos seleccionando la Hoja 2 del libro de Excel y registrando la siguiente tabla: Para que la tabla sea operativa cuando utilicemos la función BUSCARV es necesaria que esté ordenada por la clave de búsqueda. Así, en nuestro caso, ordenaremos la tabla en función del código, ya que la búsqueda se realizará en función del valor de este campo. Para ordenar la tabla deberemos seleccionar todas las celdas comprendidas en la misma desde el código “1115” hasta el precio “9,35”. A continuación seleccionaremos la opción Ordenar del menú Datos. El resultado será la visualización de la siguiente ventana: Ed. 1.0 Tema 1: Control de Stock. Casos prácticos de Excel aplicados a la gestión empresarial 7 En esta ventana procuraremos que la clave de ordenación sea el código y que figure en orden ascendente. Una vez comprobado pulsaremos el botón Aceptar. El resultado será el siguiente: Recordemos que esta tabla se ha creado seleccionado la Hoja 2 del libro de Excel que creamos en el apartado anterior. Nuestro siguiente cometido consiste en que Excel devuelva la descripción y el PVP del producto al introducir el código en el presupuesto. Para ello necesitaremos la función BUSCARV, cuya sintaxis es: BUSCARV(Valor buscado, Matriz buscar en, Indicador de columnas) Para aplicar la función colocaremos el puntero sobre la celda B8, o lo que es lo mismo, la descripción del primero de los artículos. Escribiremos el principio de la formula: =BUSCARV( y a continuación seleccionaremos la celda que contiene el valor que servirá como guía en la búsqueda de los datos, en nuestro ejemplo será el código del producto (A8). El argumento “Matriz buscar en” hace referencia a la tabla de datos que contiene todos los datos. En el ejemplo abarcaría desde la celda A2 a la celda C12 de la hoja 2. El argumento sería: Hoja2!A2:C12. Finalmente queda por señalar el “Indicador de columnas”, el cual, hace referencia al número de columna de la matriz seleccionada en el argumento “Matriz buscar en” en la que se encuentra el dato que debe devolver la función. En la tabla la columna 1 sería el código, la columna 2 sería la descripción y la columna 3 el PVP. El resultado que pretendemos obtener es la descripción del producto, por tanto, debemos escribir 2 como argumento. La función al completo sería: =BUSCARV(A8; Hoja2!A2:C12;2) El mismo procedimiento lo aplicaremos para hacer que la hoja de cálculos devuelva el PVP. Colocaremos el puntero sobre la celda C8 y utilizaremos nuevamente la función buscar con los mismos argumento que en el caso anterior salvo el “Indicador de columnas” que en este caso tendrá un valor de 3 (la columna que contiene el PVP). La función sería: =BUSCARV(A8; Hoja2!A2:C12;3) Tema 1: Control de Stock. Ed. 1.0 8 Casos prácticos de Excel aplicados a la gestión empresarial Para finalizar deberíamos arrastrar la fórmula para que se copiase en el resto de descripciones y PVP respectivamente. El problema sería que al arrastrar la fórmula cambiarían las coordenadas correspondientes al argumento “Matriz buscar en” para conseguir que estas coordenadas no cambien al arrastrar la fórmula podemos “fijarlas”. El proceso para fijar las coordenadas es seleccionarlas con el ratón y pulsar la tecla F4, con lo cual Excel colocará signos $ delante de las filas y columnas. El resultado en el primero de los casos debería ser: Hoja2!$A$2:$C$12. En el caso explicado no cambiarán las celdas comprendidas entre A2 y C12 al copiar la fórmula en otras celdas, pero si cambiarán el resto de la fórmula si procede. Deberá repetirse el mismo procedimiento con la “Matriz buscar en” del PVP. Ahora ya podremos arrastrar las fórmulas sin problemas. En las filas donde no se haya introducido ningún código se visualizará el código #N/A, lo cual indica que no hay argumentos, lo cual significa que no se visualizará ninguna información hasta que se introduzca un código de artículo. Para evitar la visualización de los mensajes #N/A podemos recurrir a la función SI, la cual permite establecer una condición en la hoja de cálculos. La sintaxis es: SI(condición; valor si verdadero; valor si falso) En la función deberíamos especificar que sólo aplicase la función BUSCARV para aquellos casos en los que se introdujese un valor en la celda que contiene el código del producto.La condición sería que el valor de la celda que contiene el código fuese cero. Así en la primera de las filas sería: A8<>0. El argumento “valor si verdadero” sería la aplicación de la función BUSCARV propiamente. El argumento “valor si falso” sería un valor en blanco, por ejemplo “”. Las dos comillas haría que la celda apareciese en blanco cuando no estuviese introducido ningún código. La función al completo sería: =SI(A8<>0;BUSCARV(A8;Hoja2!$A$2:$C$12;2);””) En el caso del PVP, la fórmula sería la misma pero cambiando el argumento “valor si falso”, ya que al tratarse de un dato numérico que posteriormente será utilizado en una fórmula no podemos especificarlo como un valor nulo. El argumento sería en este caso 0. La función resultante quedaría: =SI(A8<>0;BUSCARV(A8;Hoja2!$A$2:$C$12;3);0) Ed. 1.0 Tema 1: Control de Stock. Casos prácticos de Excel aplicados a la gestión empresarial 9 Al arrastrar ambas fórmulas el resultado que se vería sería el siguiente: Al visualizar el presupuesto observamos que se aprecian ceros en las filas donde no se han introducido códigos ni en las fórmulas que están faltas de valores para poder calcularse. Esto puede corregirse seleccionando Opciones del menú Herramientas y a continuación desmarcando la opción Valores cero. Tema 1: Control de Stock. Ed. 1.0 10 Casos prácticos de Excel aplicados a la gestión empresarial El resultado de esta última opción será que Excel deje de visualizar ceros en las celdas con dicho valor. El siguiente paso será conseguir que el usuario sólo pueda introducir información en aquellas celdas que permitan y no pueda modificar el resto de la hoja de cálculos. Así por ejemplo en nuestro presupuesto el usuario sólo podrá introducir los datos de la cabecera del presupuesto, el código del artículo, la cantidad y el porcentaje de descuento. El funcionamiento del sistema de protecciones de celdas en Excel funciona en dos fases: • Indicamos a Excel las celdas en las que se podrá escribir. • Protegemos la hoja o libro, no bloqueándose las seleccionadas en el 1º paso. En nuestro presupuesto marcaremos las celdas comprendidas entre la A2 y la B5. A continuación seleccionaremos la Opción Celdas del Menú Formato. Y dentro de las diferentes pestañas que aparecen seleccionaremos la pestaña Proteger, en la cual, deberemos quitar la marca en la casilla con la denominación Bloqueada. Este proceso deberá repetirse con el resto de celdas en las que deba escribirse información: Nº de presupuesto (E2), Cantidad (E4), Código (A8 a A27), Cantidad (D8 a D27) y Descuento (D29). Ed. 1.0 Tema 1: Control de Stock. Casos prácticos de Excel aplicados a la gestión empresarial 11 Una vez que todas las celdas han sido desprotegidas deberemos proteger la hoja para que no pueda escribirse en el resto de las celdas. El procedimiento en este caso será seleccionar el menú Herramienta, seguido de la opción Proteger. En el menú que aparece seleccionaremos la opción Proteger hoja o Proteger libro según las necesidades que tengamos. Tanto en un caso como en otro se pedirá una clave que debería ser introducida para desproteger la hoja. En caso de no ser introducida, cualquier persona podría desproteger el libro u hoja protegido siguiendo el mismo procedimiento. Una vez protegida la hoja, si se intenta introducir un contenido en una celda protegida, se visualizará el siguiente mensaje desde Excel y se bloqueará el acceso. Podríamos añadir que la fecha se colocase de forma automática en la celda cuando abrimos el presupuesto. Basta con introducir la función =HOY(), la cual, recoge la fecha del sistema y coloca el valor en la celda. Al tratarse de una celda desprotegida, bastaría con escribir una fecha diferente encima para borrar la que aparece por defecto. En la siguiente imagen se muestra un presupuesto realizado siguiendo las explicaciones dadas en este apartado: Tema 1: Control de Stock. Ed. 1.0 12 Casos prácticos de Excel aplicados a la gestión empresarial El problema que se plantea con lo realizado hasta ahora es que no podemos utilizar el modelo de presupuesto creado sin que se modifique el original, a menos que a la hora de grabar el presupuesto realizado le demos un nombre diferente. Para evitar este problema, deberemos almacenar la hoja de cálculos como una plantilla de Excel. La grabación de un archivo como plantilla evita que se utilice el archivo y cada vez que se guarde lo haga como un archivo nuevo. El procedimiento para grabar la hoja de cálculos como una plantilla es el siguiente: • En el Menú Archivo seleccione la opción Guardar como. • En la lista desplegable Guardar como Tipo seleccione la opción Plantilla. Ed. 1.0 Tema 1: Control de Stock. Casos prácticos de Excel aplicados a la gestión empresarial 13 • Asigne un nombre al libro. Excel le asignará de forma automática la extensión .XLT (de plantilla). • Puede guardar la plantilla en la carpeta deseada, aunque lo más correcto (para automatizar el uso de la misma) es guardarla en la carpeta C:\windows\application data\microsoft\plantillas (esta carpeta la utiliza Excel por defecto al guardar el archivo como “Plantilla” • Finalmente pulse el botón Guardar. A partir de este momento, cada vez que desee usar la plantilla deberá seleccionar la opción Nuevo del menú Archivo. En la ventana que se abre al lado derecho de la pantalla deberá escoger Plantillas generales y aparecerá una ventana en la cual podrá seleccionar cual es la plantilla que desea usar. En nuestro caso la plantilla Presupuesto. Tema 1: Control de Stock. Ed. 1.0 14 Casos prácticos de Excel aplicados a la gestión empresarial Cuando realice el presupuesto y lo almacene, lo hará como un archivo normal y esperará que le introduzca un nombre. 1.3. CONTROL DE STOCK. Nuestro siguiente desarrollo pretende controlar el stock de almacén e informarnos en todo momento del valor de los artículos que están en almacén, los beneficios generados por cada producto, los artículos que están bajo mínimos, etc. Vamos a partir de una tabla muy simple en la que se recogen los artículos en stock (en nuestro ejemplo sólo 12 pero podrían ser miles), el precio de compra y la sección a la que pertenecen dentro de nuestro negocio. Hemos considerado que se trata de un pequeño supermercado pero podría ser una ferretería, un kiosco de prensa, una tienda de ropa o cualquier otro negocio. Los datos de partida son los siguientes: A partir de esta información vamos a calcular: • El PVP en función de una tabla que indica que porcentaje hay que aplicar sobre el precio de compra. • El Beneficio que se obtiene por unidad. • El Beneficio obtenido por la venta de cada artículo hasta el momento. • El Valor del producto almacenado. Ed. 1.0 Tema 1: Control de Stock. Casos prácticos de Excel aplicados a la gestión empresarial 15 En primer lugar vamos a registrar los valores indicados en la hoja de cálculo, prestando especial atención a los precios de los productos, los cuales deben aparecer en formato de Euros y con dos decimales cada uno. Una vez escritos los precios, marcaremos la columna de los mismos y seleccionaremos la opción Celda del menú Formato. En la ventana que aparece escogeremos la pestaña Número y dentro de ella la categoría Moneda de la lista que se visualiza en el lado izquierdo. Nos aseguraremos igualmente de que la opción Posiciones decimales se encuentra en 2 y en la opción Símbolo aparece el €. Como resultado, todos nuestros números, tendrán dos decimales y llevarán a la derecha el símbolo del €. Tema 1: Control de Stock. Ed. 1.0 16 Casos prácticos de Excel aplicados a la gestión empresarial El PVP de nuestros artículos debe calcularse en función de una tabla en la que se indican el margen de beneficio que debe aplicarse a cada producto en función de la sección a la que pertenece. De esta forma, una variación en dicha tabla, variaría el precio de todos los productos de una sección. La tabla es la siguiente: Esta tabla la vamos a registrar en la Hoja 2 de la celdilla A1 a la celdillaB6. Esto significa que los embutidos se venderán un 20% más caro del precio al que los compramos. En el caso de las verduras será un 30%, etc. Por ejemplo en el caso de la bandeja de Salami que cuesta 1,12€ su precio de venta se calcularía sumando: el precio de compra de la bandeja de salami más el 20% del precio de compra de la bandeja de salami. P.V.P.= 1,12+1,12*20%=1,12+0,22=1,34€ Al igual que ya se explicó en el apartado anterior, haremos que Excel lea la sección del producto y busque el margen que le corresponde en la hoja 2. Para ello volveremos a recurrir a la función Buscarv. =BUSCARV(B2;Hoja2!$A$2:$B$6;2) En la fórmula, B2 es la celda que contiene la sección del primer producto, Hoja2!$A$2:$B$6 son las celdas que contienen la tabla de márgenes en la hoja 2 y además se colocan en valor absoluto para poder copiarlas depués y que no se muevan las referencias a la misma, por último el 2 indica la columna de la tabla anterior que devolverá el valor buscado, en este caso, el porcentaje. La fírmula completa una vez terminada sería la siguiente: =C2+C2*BUSCARV(B2;Hoja2!$A$2:$B$6;2) En la fórmula C2 es el precio de compra del producto, al que se le suma el precio de compra del producto multiplicado por el porcentaje. El Beneficio unitario lo calcularemos restando al P.V.P. el Precio de compra. En nuestro caso la fórmula será : =D2-C2 Notará que tanto el PVP calculado como el Beneficio unitario salen en Euros y con dos decimales sin necesidad de aplicar ningún formato. Esto es debido a que los cálculos en Excel heredan el formato original aplicado en los valores de las celdas del tipo numérico. Los Beneficios globales por artículo se obtienen multiplicando el Beneficio unitario por las Unidades vendidas. Este dato no existe pero en un principio puede calcularse restando a las unidades compradas las unidades en almacén. Este dato es muy teórico pues supone que en ningún momento se han producido ni mermas ni robos de productos. Ed. 1.0 Tema 1: Control de Stock. Casos prácticos de Excel aplicados a la gestión empresarial 17 La fórmula se aplicaría sobre la celda H2 y sería la siguiente: =(E2-F2)*G2 La resta correspondiente a las Uds. Compradas y las Uds. en almacén debe ir entre paréntesis ya que Excel ejecuta antes las multiplicaciones que las sustracciones y por tanto debe obligarse a que primero haga la resta y después multiplique el resultado por el Beneficio unitario. Si no se colocase el paréntesis, se multiplicaría el Beneficio por las Udes. en almacén y esa cantidad se restaría a las Udes. Compradas. Finalmente, el valor de almacén de un producto, se obtiene multiplicando el Precio de compra por las Udes. en almacén. La fórmula sería: =F2*C2 Nuestro siguiente paso consistirá en crear una herramienta para que al introducir el nombre una sección, Excel nos devuelva el número de artículos diferentes que hay en la misma, la cantidad total de artículos en stock, los beneficios obtenidos y el valor de almacén. Para calcular el número de artículos utilizaremos la función CONTAR.SI, la cual tiene la siguiente sintaxis: =CONTAR.SI(Rango;Criterio) El argumento Rango hace referencias a las celdas que se utilizaran para ser contadas si se cumple el criterio y se añadirá 1 a la cuenta total por cada una que satisfaga el criterio. El criterio es la condición que debe darse para que se cuente el valor de una celda como positivo. En primer lugar colocaremos los títulos de los datos que se van a introducir o al calcular. En la celda A15 colocaremos el título SECCIÓN, en la celda B15 el título Nº DE ARTÍCULOS, en E15 UDS. COMPRADAS, en F15 UDS. EN ALMACÉN, en H15 BENEFICIOS Y en I15 VALOR DE ALMACÉN. En la fila 16 Introduciremos la sección buscada y las fórmulas que deben aplicarse sobre la misma. En la celda B16 introduciremos la fórmula: =CONTAR.SI(B2:B13;A16) Con esta fórmula estamos indicando a Excel que lea la columna que contiene las secciones de cada artículo y me cuente las que concuerden con la introducida en la celda A16. Tema 1: Control de Stock. Ed. 1.0 18 Casos prácticos de Excel aplicados a la gestión empresarial El siguiente dato a calcular será el total de unidades compradas en la sección. La formula a utilizar será: SUMAR.SI(Rango;Criterio;Rango a sumar) El funcionamiento es análogo al de la función CONTAR.SI añadiendo el argumento Rango a sumar el cual permite indicar donde se encuentran los valores a sumar. En nuestro caso la función completa sería: =SUMAR.SI(B2:B13;A16;E2:E13) El primer argumento hace referencia a las celdas conde se encuentran los valores buscados, el segundo es el valor buscado y el tercero el valor que debe sumarse en caso de cumplirse la condición. Las fórmulas aplicadas para calcular las Uds en almacén, los Beneficios totales y el Valor de almacén son respectivamente: =SUMAR.SI(B2:B13;A16;F2:F13) =SUMAR.SI(B2:B13;A16;H2:H13) =SUMAR.SI(B2:B13;A16;I2:I13) Para probar introduciremos el valor CAFÉS en la celda A16. El resultado será que aparecerán todos los totales referentes a la sección CAFÉS. Si a continuación introducimos en A16 LIMPIEZA, los valores cambiaran por los totales de esta sección. Otras de las opciones que podemos aplicar sobe nuestro almacén consiste en la posibilidad de filtrar los datos para que sólo aparezcan en pantalla los de una determinada sección. Para esto es necesario crear una lista y aplicar filtros sobre ella. Una Lista en Excel es una serie de datos con un encabezado para cada columna utilizada. Para que Excel considere a los datos introducidos como una lista debemos cumplir algunas normas: • No deben dejarse celdas en blanco. • Es recomendable destacar (sombreado, bordes, colores…) la fila de encabezados. Ed. 1.0 Tema 1: Control de Stock. Casos prácticos de Excel aplicados a la gestión empresarial 19 • La lista debe estar en una hoja independiente, y es recomendable no introducir otros datos en la misma. En nuestra hoja de cálculos, ya hemos creado la lista y comprende las celdas que van desde la A1 hasta la celda J13. Para poder aplicar filtros seleccionaremos toda la lista y a continuación desplegaremos el menú Datos seguido de Filtro. Dentro del nuevo menú que aparece escogeremos la opción Autofiltro. El resultado será que en cada título de columna se visualiza un símbolo de desplegable y al pulsarlo, podemos ver una lista con los diferentes valores recogidos en cada columna. Así por ejemplo si escogemos Sección, aparecerán todas las secciones de nuestra hoja: Supongamos que escogemos la sección Bebidas, en este caso la lista se vería reducida a los artículos de esa sección únicamente: Note que en la lista, la sección aparece con el triángulo en azul, esto indica que esta columna tiene un filtro aplicado. Para volver a visualizar todos los elementos de la columna, debe desplegar de nuevo las opciones y escoge (todas). Para poder desactivar los filtros desplegaremos el menú Datos seguido de Filtro y volveremos a escoger la opción Autofiltro. Tema 1: Control de Stock. Ed. 1.0 20 Casos prácticos de Excel aplicados a la gestión empresarial IIDDEEAASS CCLLAAVVEE • La opción Celda del Menú Formato permite cambiar diferentes aspectos relacionados con la presentación de los datos en las celdas. Es de especial atención la pestaña número en la que se indican el formato en que deben visualizarse los mismos. • CONTAR.SI cuenta las celdas si cumplen una determinada condición. La sintaxis de la función es: =CONTAR.SI(Rango;”condición”). El argumento “condición” no irá entre comillas si se trata de un número. • REDONDEAR redondea un número al número de decimales especificado. Sintaxis: =REDONDEAR(número;número de decimales). Así, por ejemplo, si el contenido de C5 es 4,75386 y en D5 introducimos la función: =REDONDEAR(C5;4), el resultado que se obtiene es 4,7539. • SUMAR.SI suma, en un rango de celdas, sólo aquellos valores que cumplen una determinada condición. La sintaxis de la función es la siguiente: =SUMAR.SI(rango de lectura;”criterio”;rango de suma). El argumento“criterio” no irá entre comillas cuando la condición sea un número. • SI función lógica esencial, cuando el resultado de una celda(fórmula) dependa del contenido de otra u otras. La sintaxis inicial de la función es: =SI(Condición;Valor si verdad;Valor si falso). Es decir, en el caso de que se cumpla la condición se aplicará el “Valor si verdad” y en el caso de que no se cumpla, se aplicará el “Valor si falso”. • HOY inserta la fecha actual. En aquellas hojas donde nos interese que aparezca la fecha del sistema introduciremos la función de la siguiente forma:=HOY(). • BUSCARV devuelve texto o valor de una tabla según el Valor buscado. La sintaxis de la función es la siguiente: =BUSCARV(Valor buscado; matriz/rango; Nº Columna; orden). • Una Lista en Excel es una serie de datos con un encabezado para cada columna utilizada. Ed. 1.0 Tema 1: Control de Stock. Casos prácticos de Excel aplicados a la gestión empresarial 21 AAUUTTOOEEVVAALLUUAACCIIÓÓNN DDEELL TTEEMMAA 11.. 1. Para trazar dibujar la línea exterior de un grupo de celdillas seleccionamos “todos los bordes”. V F 2. El botón que permite calcular la suma total de un grupo de celdillas se denomina Autosuma. V F 3. Para modificar el número de decimales que se visualizan en una celdilla debemos utilizar la opción Celda del menú Formato. V F 4. La función Buscarv funciona siempre y cuando la lista de datos donde debe buscar este ordenada. V F 5. La función SI pertenece al grupo de funciones de búsqueda. V F 6. Para proteger una hoja de cálculos indicamos a Excel en primer lugar que proteja toda la hoja y a continuación indicamos lo que no debe proteger. V F 7. Las plantillas de Excel tienen la extensión XLS. V F 8. Para sumar los valores de una columna que cumplan una determinada condición utilizamos la función CONTAR.SI. V F 9. Una Lista en Excel es una serie de datos con un encabezado para cada columna utilizada. V F 10. Para acceder a la opción Autofiltro hay que realizarlo a través del Menú Datos. V F Tema 1: Control de Stock. Ed. 1.0 22 Casos prácticos de Excel aplicados a la gestión empresarial TEMA 2 GESTIÓN FINANCIERA 2.1. PRÉSTAMOS. 2.2. PAGOS. 2.3. AMORTIZACIÓN. 2.4. VAN / TIR. 2.4.1. VAN. 2.4.2. TIR. 2.1. PRÉSTAMOS. Uno de los casos con los que se encuentran las empresas es el pago de préstamos hipotecarios. Supongamos que una empresa quiere comprar un local comercial por importe de 240.000 €, y desea saber cuanto tiene que pagar cada mes para cancelar este préstamo. Se va a calcular la cuota mensual que esta empresa tendría que pagar durante los próximos 20 años a un tipo de interés del 2% anual. Partimos de la siguiente hoja de Excel donde se ha introducido los datos del enunciado: Para resolver este caso usamos la función PAGO. Dicha función calcula el pago de un préstamo basado en cuotas de pagos constantes y una tasa de interés también constante. Esta función se encuentra dentro de las funciones financieras. Para insertar una función podemos utilizar el asistente para funciones. Para ello nos colocamos en la celda B7 y seleccionamos el símbolo que aparece en la barra de fórmulas. Aparecerá el siguiente cuadro donde seleccionamos la categoría de la función que queremos insertar, en nuestro caso es Financieras. Dentro de esta categoría seleccionamos la función PAGO. Ed. 1.0 Tema 2: Gestión Financiera. Casos prácticos de Excel aplicados a la gestión empresarial 23 Una vez seleccionada dicha función aparece el siguiente cuadro donde están todos los argumentos de esta función: Tema 2: Gestión Financiera. Ed. 1.0 24 Casos prácticos de Excel aplicados a la gestión empresarial La sintaxis de esta función es la siguiente: PAGO(Tasa;Nper;Va;Vf;Tipo). Otra forma de insertar una función es escribiendo su sintaxis. Tenemos que tener en cuenta que los cálculos pueden ser anuales, semestrales, mensuales, etc. Por tanto, los datos que nos ofrecen hay que convertirlos según lo que queremos calcular. En nuestro caso, debemos transformarlo todo para calcular la cuota mensual: • Tasa: es la tasa de intereses por período. Dado que se va a calcular el pago mensual, hay que dividir la tasa anual entre 12 que son los meses del año. De esa forma se calcularía la tasa mensual • Nper: es el número de períodos. Igual que antes se va a calcular de forma mensual, luego se multiplica los años por 12 meses de cada año, calculándose así los períodos de liquidación. • Va: significa Valor actual, es decir, el préstamo. • Vf: la cantidad restante al finalizar el tiempo (normalmente 0). • Tipo: es 1 o 0 (verdadero o falso) en función de lo que establece para sus pagos. 1 significa que paga al principio del período, y 0 (u omisión) que paga al final del período. Puesto que se va a devolver la totalidad del préstamo y se establece los pagos al final del cada período, solo se deben rellenar los tres primeros campos, los dos últimos argumentos se puede omitir, no son necesarios. Vamos a introducir los valores correspondientes para cada argumento. Para el primero de ellos, Tasa, hacemos un clic en el botón y se abrirá el siguiente cuadro. Ahora seleccionamos la celda B4, y automáticamente aparecerá en el cuadro, pero recuerde que hay que dividir entre 12 meses. A continuación se hace clic en el botón y se cerrará el cuadro. Procedemos de la misma forma con todos los argumentos que necesita la función. Al final si todo se ha hecho correctamente el cuadro con los argumentos quedaría de la siguiente forma: Ed. 1.0 Tema 2: Gestión Financiera. Casos prácticos de Excel aplicados a la gestión empresarial 25 Ya solo queda presionar el botón Aceptar. El resultado se presenta en formato de moneda y es una cantidad negativa. Es lógico, cuando recibe dinero prestado, tiene que ser en algún tipo de moneda y Excel selecciona el euro según la configuración de idioma. La cifra es negativa ya que cuando recibe dinero prestado, la devolución es dinero que sale de su bolsillo. Se puede convertir el número negativo en uno positivo multiplicando todo por -1, es decir, colocando un signo – delante de la función. Con lo que ya habríamos calculado la cuota mensual que la empresa tiene que pagar al comprar el local comercial. Tema 2: Gestión Financiera. Ed. 1.0 26 Casos prácticos de Excel aplicados a la gestión empresarial 2.2. PAGOS. En nuestra vida cotidiana seguro que nos encontramos en la siguiente situación, nos compramos un coche y pedimos un préstamo para hacer frente al pago de las letras. En el siguiente caso práctico pensemos que hemos comprado un coche por un valor de 14.500 € a pagar en 4 años a un interés del 6.5% anual. Como sabemos al inicio de un préstamo lo que se paga es casi todo intereses, y muy poco capital. Cuando va pasando el tiempo se van igualando las cuotas, y al final del préstamo se paga mas capital que intereses. Nosotros vamos a realizar una tabla donde se especifica la cantidad de capital e intereses en cada cuota. Las funciones que se utilizan en este caso son PAGOINT que calculará el interés y PAGOPRIN la devolución del capital. En realidad, solo se necesita una de las dos funciones, porque cuando se haya calculado el interés, para calcular la devolución de capital solo hay que restar el interés del pago mensual. El interés disminuye y la devolución de capital aumenta según el período en el que se encuentren. Ed. 1.0 Tema 2: Gestión Financiera. Casos prácticos de Excel aplicados a la gestión empresarial 27 Vamos a rellenar todos los períodos, que serían 4 años x 12 meses = 48 cuotas o períodos. Partiendo de la hoja anterior donde hacemos notar que en C8 hemos introducido el nº 1. Podemos introducir en la celda de abajo el nº 2 y arrastrar hacia abajo hasta que se haya rellenado las 48 celdas. Pero vamos a hacerlo de una manera más fácil. Vaya a la celda C9. Mantenga presionadas las teclas CTRL+Mayúsculas y presione la tecla de dirección hacia abajo. De esta manera se ha seleccionado la columna entera desde la celda C9 hasta elfinal. Hacer clic en el menú Edición luego en el submenú Rellenar y seleccionar Series: En el cuadro de diálogo que aparece se seleccionan los siguientes parámetros. La series son en Columnas y el límite es 48. Hacemos clic en Aceptar. Todas las celdas se rellenan desde la 1 hasta la 48. Pasamos a resolver el caso práctico. La función que vamos a insertar también se encuentra dentro de las funciones Financieras. En este caso vamos a insertar la función PAGOINT, que devuelve el interés pagado por una inversión durante un período determinado, basado en pagos periódicos y constantes, y una tasa de interés constante. Tema 2: Gestión Financiera. Ed. 1.0 28 Casos prácticos de Excel aplicados a la gestión empresarial Ahora nos colocamos en la celda D9 e insertamos la función como ya se ha explicado anteriormente. La sintaxis de esta función es: PAGOINT(Tasa;Período;Nper;Va;Vf) Algunos de los argumentos que tiene esta función son los mismos que en la función PAGO. • Tasa: es la tasa de intereses por período. Dado que se va a calcular el pago mensual, hay que dividir la tasa anual entre 12 que son los meses del año. De esa forma se calcularía la tasa mensual • Período: es el período para el que se desea encontrar el interés, que deberá estar en el rango de 1 a Nper. • Nper: es el número de períodos. Igual que antes se va a calcular de forma mensual, luego se multiplica los años por 12 meses de cada año, calculándose así los períodos. • Va: significa Valor actual, es decir, el préstamo. • Vf: la cantidad restante al finalizar el tiempo (normalmente 0). Ed. 1.0 Tema 2: Gestión Financiera. Casos prácticos de Excel aplicados a la gestión empresarial 29 Ahora introducimos los argumentos. Hay que recordar que Tasa, Nper y Va deben ser direcciones absolutas. Período no debe serlo porque queremos copiar la fórmula en 48 celdas y queremos que cada período de devolución de interés y de capital se modifique. Si se han introducido todos los parámetros correctamente, el cuadro debe quedar de la siguiente forma: Ya solo queda presionar el botón Aceptar. Al igual que en el ejercicio anterior, el resultado se presenta en formato de moneda y es una cantidad negativa. Hay que poner un signo menos delante de la fórmula: Una vez hecho esto, solo queda copiar la fórmula en las 47 celdas restantes. Para ello hay que hacer doble clic en el asa negra de la parte inferior derecha de la celda, con esto se copiará la fórmula hacia abajo: Tema 2: Gestión Financiera. Ed. 1.0 30 Casos prácticos de Excel aplicados a la gestión empresarial Como se puede ver, se ha calculado la parte de cada cuota que se dedica a pagar los intereses del préstamo. Ahora vamos a calcular la devolución de capital. Podemos hacerlo de dos formas, utilizando la función PAGOPRIN, que tiene exactamente los mismos argumentos que PAGOINT o restando el pago de intereses al pago mensual. Vamos a utilizar la función PAGOPRIN, que devuelve el capital pagado por una inversión durante un período determinado, basado en pagos periódicos y constantes, y una tasa de interés constante.La sintaxis de esta función es PAGOPRIN(Tasa;Período;Nper;Va;Vf). Siguiendo los pasos explicados en otras ocasiones para introducir una función e introduciendo los valores de los argumentos correspondientes, el cuadro donde aparecen los argumentos quedaría: Ed. 1.0 Tema 2: Gestión Financiera. Casos prácticos de Excel aplicados a la gestión empresarial 31 Al igual que en el ejercicio anterior, el resultado se presenta en formato de moneda y es una cantidad negativa. Hay que poner un signo menos delante de la fórmula: Una vez hecho esto, solo queda copiar la fórmula en las 47 celdas restantes. Para ello hay que hacer doble clic en el asa negra de la parte inferior derecha de la celda, con esto se copiará la fórmula hacia abajo, como ya se explicó en el anterior ejercicio. Sólo queda por calcular la deuda pendiente para el próximo período, es decir, la deuda pendiente cada vez que pagamos una cuota. Para ello solo tenemos que restar a la deuda la cuota pagada. Escribimos la fórmula que calcula la deuda pendiente en la celda F9: B3-D9 (la deuda menos el primer pago de capital). En F10 colocamos la fórmula para el siguiente período: F9-E10. Una vez hecho esto, solo queda copiar la fórmula en las celdas restantes. Hacemos doble clic en el asa negra de la parte inferior derecha de la celda, con esto se copiará la fórmula hacia abajo: En la figura anterior se ve como quedaría la tabla. Se ha desglosado la cuota a pagar en intereses y capital, viendo también la deuda pendiente en cada período. En la pantalla del ordenador no vemos todos los períodos. Lo que se puede hacer es inmovilizar los paneles para que al bajar por la hoja de cálculo no se pierda la cabecera de cada columna. Esto se consigue situándose en la celda que hay debajo de la fila que queremos fijar y a la derecha de las columnas que queremos fijar. En nuestro caso la celda de corte sería G9. Situándose en esa celda, seleccionamos el menú Ventana dentro de éste escogemos Inmovilizar paneles. Tema 2: Gestión Financiera. Ed. 1.0 32 Casos prácticos de Excel aplicados a la gestión empresarial Así al desplazarnos hacia abajo siempre tenemos visibles los encabezados de cada columna. En esta figura observamos los períodos correspondientes al último año, período 37 al 48. Como vemos en la columna de Resto deuda la última cantidad aparece en rojo y negativo, la deuda ya está saldada. CALCULADORA DE PRÉSTAMOS. Partiendo del caso visto con anterioridad en el que una empresa compra un local comercial a pagar 20 años deberíamos saber que cantidad de intereses o de capital vamos a pagar en un determinado período. Como el préstamo lo vamos a devolver en un plazo tan largo, hacer la tabla como en el caso anterior no tiene sentido, sería demasiado grande, ya que serían 20*12= 240 períodos. Lo que si tendría sentido sería calcular la cantidad que se dedica a intereses y a capital en unas cuotas determinadas por nosotros. Vamos a introducir un año (del 1 al 20) y automáticamente nos calcula los períodos, intereses y capital de esas cuotas. Los datos de los que disponemos son los mismos que en el ejercicio de Pagos. Una empresa compra un local comercial por un importe de 240.000 €, a devolver en 20 años a un tipo de interés del 2% anual. Ed. 1.0 Tema 2: Gestión Financiera. Casos prácticos de Excel aplicados a la gestión empresarial 33 Partimos de la siguiente hoja: Escribimos las formulas adecuadas en cada una de las celdas. En B11 colocamos la función PAGOINT, en C11 la función PAGOPRINT. Esto si hemos entendido los dos ejercicios anteriores no nos debe costar ningún trabajo, además nos va a servir de repaso. Por si queda alguna duda vamos a escribir como quedarían las fórmulas: Cuando escribimos en la celda B9 el año que queremos conocer automáticamente se deben calcular los períodos que corresponden a ese año. En B11 debemos escribir una fórmula que determine el período correspondiente. Dicha fórmula sería la siguiente (n-1)*12+1, siendo n el año que queremos conocer. Para los siguientes períodos la fórmula es la misma pero hay que incrementar en uno mas, es decir, para el segundo período la fórmula sería (n-1)*12+2, y así sucesivamente hasta llegar al período número 12 el que la fórmula sería (n- 1)*12+12. Por tanto hay que escribir estas fórmulas desde la celda B11 hasta la celda B23. La celda B9 va a ser donde siempre vamos a escribir el año que queremos conocer. Tenemos que preparar la celda para que solo en ella podamos escribir y ninguna otra se pueda modificar. Posicionados en B9, seleccionamos Formato de la barra de menú y el submenú Celdas y en la pestaña Proteger deseleccionamos la opción Bloqueada. Con esto nos aseguramos que la celda no esté bloqueada, ya que ahora vamos a proteger la hoja contra escritura. Tema 2: Gestión Financiera. Ed. 1.0 34 Casos prácticos de Excel aplicados a la gestión empresarial Para proteger la hoja tenemosque seleccionar el menú Herramientas, dentro seleccionamos Proteger y del desplegable seleccionamos Proteger Hoja. En el cuadro que nos aparece podemos incluir una contraseña o dejarla en blanco. Al proteger la hoja nos aseguramos que nadie pueda modificar la hoja en uso. Solo queda pulsar Aceptar. Ed. 1.0 Tema 2: Gestión Financiera. Casos prácticos de Excel aplicados a la gestión empresarial 35 Ahora solo podemos escribir en la celda B9. Aquí introducimos el año que queremos conocer y directamente se calcula la cantidad de Intereses y de Capital que pagamos en las 12 cuotas correspondientes a ese año. Así, para el año 1 el cálculo se refleja en la siguiente figura. Tema 2: Gestión Financiera. Ed. 1.0 36 Casos prácticos de Excel aplicados a la gestión empresarial El cálculo para el año 5 sería: 2.3. AMORTIZACIONES. En la empresa hay ocasiones en las que es interesante saber el cálculo de la amortización de alguna máquina u otro elemento para saber si es rentable. En esta ocasión vamos a pensar en una panadería que compra un horno. El horno cuesta 12.000 € y se va a calcular la amortización en 5 años, sabiendo que al final de este tiempo tendrá un valor residual de 600 €. Hay muchos tipos de amortizaciones como vamos a ver en este ejemplo. Todas estas funciones se encuentran en la categoría de Financieras. • Amortización lineal o simple. Se utiliza la función SLN que devuelve la depreciación por método directo de un bien en un período dado. La sintaxis de esta función es SLN(costo;valor_residual;vida). Ed. 1.0 Tema 2: Gestión Financiera. Casos prácticos de Excel aplicados a la gestión empresarial 37 Siendo sus argumentos : � Costo: costo o valor inicial del bien. � Valor_residual: es el valor al final de la vida de un bien. � Vida: número de períodos durante los que se produce la depreciación del bien o vida útil. • Amortización de saldo fijo. Para ello se utiliza la función DB que devuelve la depreciación de un bien durante un período específico usando el método de depreciación de saldo fijo. La sintaxis de esta función es DB(costo;valor_residual;vida;período;mes). Algunos de sus argumentos coinciden con la función anterior. � Costo: costo inicial del bien. � Valor_residual: valor al final de la vida del bien. � Vida: número de períodos durante los que se produce la depreciación del bien o vida útil. � Período: es el período del que se desea calcular la depreciación. El período debe usar las mismas medidas que las usadas en la vida. � Mes: es el número de meses que tiene el primer año, si se omite se asume que es 12. • Amortización de doble disminución. Se utiliza la función DDB que devuelve la depreciación de un bien en un período específico mediante el método de depreciación por doble disminución de saldo. La sintaxis de esta función es: DBB(costo;valor_residual;vida;período;factor). Los argumentos son los mismos que la función DB excepto: � Factor: es la tasa a la que disminuye el saldo. Si se omite el factor, se asumirá el valor 2. • Amortización de anualidades. Se utiliza la función SYD que devuelve la depreciación por método de anualidades de un bien durante un período específico. La sintaxis de la función es SYD(costo;valor_residual;vida;período). Los argumentos de esta función ya se conocen. Partimos de la siguiente hoja donde se expresan los valores del enunciado: Tema 2: Gestión Financiera. Ed. 1.0 38 Casos prácticos de Excel aplicados a la gestión empresarial En la celda B9 se coloca la función con los argumentos necesarios: =SLN($B$3;$B$5;$B$4). Se ponen referencias absolutas para que al copiar la fórmula en las celdas contiguas no haya que modificar ninguna referencia. En la celda B10 se coloca la función con sus argumentos: =DB($B$3;$B$5;$B$4;B8). En este caso el argumento que se refiere al período no se pone como referencia absoluta para que al copiar la fórmula en las siguientes celdas se vaya modificando. En la celda B11 se escribirá la función =DDB($B$3;$B$5;$B$4;B8). Ahora solo hay que copiar la función a las celdas contiguas. En la celda B12 se escribirá la función =SYD($B$3;$B$5;$B$4;B8). Solo hay que copiar la función en las celdas contiguas. Al final el resultado que nos queda se refleja en esta figura. Ed. 1.0 Tema 2: Gestión Financiera. Casos prácticos de Excel aplicados a la gestión empresarial 39 2.4. VAN Y TIR. Hay diferentes modelos dinámicos para asignar una medida de la rentabilidad de un proyecto de inversión. Por un lado está el Valor Actual Neto (VAN) y por otro lado está El Tipo Interno de Rendimiento (TIR) o Tasa de Retorno. Vamos a realizar un ejercicio donde se refleje estos métodos de valoración de inversiones. 2.4.1. VAN. Supongamos que una empresa quiere hacer una inversión y piensa en un proyecto que supone un desembolso inicial de 12.000 €, con una tasa de descuento del 10%. Los ingresos que genera esta inversión en los tres siguientes años son 3.000 €, 4.500 € y 6.200 € respectivamente. Se va a estudiar la rentabilidad de este proyecto a 3 años. Para resolver este caso vamos a utilizar la función VAN que devuelve el Valor actual neto de una inversión a partir de una tasa de descuento y una serie de pagos futuros (valores negativos) y entradas (valores positivos). Esta función se encuentra dentro de las funciones Financieras y se inserta como ya se ha visto en anteriores ejercicios. La sintaxis de esta función es VNA(tasa;valor1;valor2;…) donde: • Tasa: es la tasa de descuento durante un período. • Valor 1: valor1;valor2;…Son de 1 a 29 pagos y entradas igualmente espaciados y que ocurren al final de cada período. Partiendo de la siguiente hoja donde se reflejan los datos del enunciado. Procedemos a insertar la función en la celda B8. Destacar que el desembolso inicial es una cantidad negativa, ya que es dinero que sale de la empresa. Tema 2: Gestión Financiera. Ed. 1.0 40 Casos prácticos de Excel aplicados a la gestión empresarial El resultado es positivo con un Valor Actual Neto de 1.393,35 €, lo que indica que la inversión es rentable y que daría ese beneficio en tres años. 2.4.2. TIR. La Tasa de Retorno se define como el tipo de actualización que anula el Valor Actual Neto. Ahora vamos a calcular la Tasa Interna de Rendimiento o Tasa de Retorno sobre este mismo ejercicio. La función a introducir es TIR y se encuentra dentro de las funciones Financieras. La sintaxis de esta función es TIR(valores;estimar) donde: • Valores: es una matriz o referencia a celdas que contengan los números para los cuales se desea calcular la tasa interna de retorno • Estimar: es un número que el usuario estima que se aproximará al resultado de TIR. Se asume 0,1 (10 %) si se omite. Procedemos a insertar la función TIR en la celda B9. Ed. 1.0 Tema 2: Gestión Financiera. Casos prácticos de Excel aplicados a la gestión empresarial 41 La Tasa de Retorno daría un resultado del 17%. Tema 2: Gestión Financiera. Ed. 1.0 42 Casos prácticos de Excel aplicados a la gestión empresarial IIDDEEAASS CCLLAAVVEE • Para insertar una función se puede utilizar el asistente para funciones. • La función PAGO calcula el pago de un préstamo basado en cuotas de pagos constantes y una tasa de interés también constantes. • La función PAGOINT devuelve el interés pagado por una inversión durante un período determinado, basado en pagos periódicos y constantes y una tasa de interés constante. • La función PAGOPRIN devuelve el capital pagado por una inversión durante un período determinado, basado en pagos periódicos constantes y una tasa de interés constante. • Para inmovilizar los paneles hay que seleccionar el menú Ventana y dentro de éste se escoge Inmovilizar paneles. • Hay varios tipos de amortizaciones: � Lineal o simple: SLN. � De saldo fijo: DB. � De doble disminución: DDB. � De anualidades: SYD. • La función VNA devuelve el valor actual neto de una inversión a partir de una tasa de descuento y una serie de pagos futuros (valores negativos) y entradas(valores positivos). • La función TIR calcula la tasa interna de retorno de una inversión para una serie de valores en efectivo. Ed. 1.0 Tema 2: Gestión Financiera. Casos prácticos de Excel aplicados a la gestión empresarial 43 AAUUTTOOEEVVAALLUUAACCIIÓÓNN DDEELL TTEEMMAA 22.. 1. Para insertar una función sólo se puede utilizar el asistente para funciones. V F 2. Al utilizar el asistente para funciones aparecen todos los argumentos de la función. V F 3. La función PAGO se encuentra dentro de la categoría Financieras. V F 4. La función PAGOINT devuelve el capital de una inversión V F 5. El interés disminuye y la devolución de capital aumenta según el período en el que se encuentren. V F 6. Cuando se inmovilizan los paneles al bajar por la hoja de cálculo se pierden los encabezados de las filas o columnas. V F 7. Para proteger una hoja contra escritura tenemos que seleccionar el menú Herramientas, dentro seleccionamos Proteger y del desplegable se escoge Proteger Hoja. V F 8. La función SLD devuelve la depreciación por método directo o lineal de un bien en un período dado. V F 9. Para calcular la amortización por el método de saldo fijo se utiliza la función DDB. V F 10. Las funciones VNA y TIR calculan la rentabilidad de un proyecto de inversión. V F Tema 2: Gestión Financiera. Ed. 1.0 44 Casos prácticos de Excel aplicados a la gestión empresarial TEMA 3 GESTIÓN CONTABLE 3.1. DIARIO DE CAJA. 3.2. COSTES DE PRODUCCIÓN. 3.1. DIARIO DE CAJA. En este apartado vamos a desarrollar un pequeño diario de caja que recoja los ingresos y gastos que se producen de forma habitual en un negocio. El propósito de la hoja será proporcionar al usuario una completa herramienta que le permita analizar de diferentes formas las fluctuaciones de la caja. En ningún momento nos proponemos emular un programa de contabilidad ya que esto estaría totalmente lejos de nuestro alcance y posibilidades. Ed. 1.0 Tema 3: Gestión Contable. Casos prácticos de Excel aplicados a la gestión empresarial 45 El diario recogerá partidas diarias con su correspondiente concepto, fecha e indicación de si se trata de un gasto o ingreso. En nuestro ejemplo ceñiremos el diario a 20 líneas por motivos de visualización en pantalla, pero dicha limitación no debe tenerse en cuenta cuando se desarrolle el diario personalizado. En nuestra hoja comenzaremos creando los títulos de las columnas que formarán nuestro diario. Los títulos y su ubicación son los siguientes: • CANTIDAD (A1) • CONCEPTO (B1) • FECHA (C1) • INGRESOS/GASTOS (D1) Las celdas comprendidas entre la A2 y la D21 se reservarán para introducir la información correspondiente a los apuntes de caja. Entre las celdas indicadas estableceremos un rayado como ya se explico en temas anteriores. Para mejorar la presentación de la hoja podemos eliminar igualmente la visualización del las líneas por defecto de la hoja de cálculo. En la celda A23 colocaremos el texto “TOTAL INGRESOS” y reservaremos la celda B23 para introducir la fórmula que recoja la suma de los ingresos. La celda A24 recogerá el texto “TOTAL GASTOS” y nuevamente reservaremos la celda B24 para la fórmula que sume los gastos. En esta primera fase sólo pretenderemos introducir los asientos y que Excel nos devuelva el total de gastos e ingresos. El procedimiento es muy simple y sólo necesita de dos formulas para su realización. Cuando hemos introducido los asientos hemos incluido una clave para reflejar si se trata de un gasto (G) o un ingreso (I), por tanto podemos recurrir a la función SUMAR.SI cuya sintaxis es: SUMAR.SI(rango;criterio;rango suma) En este caso pretendemos que sume las cantidades sólo en el caso de que sea un ingreso o un gasto. En el primero de los casos (celda B23), la fórmula sería: =SUMAR.SI(D2:D21;”I”;A2:A21) • D2:D21 es la columna de datos que se utilizará para buscar el criterio, en nuestro caso indica si se trata de un ingreso (I) o un gasto (G). Tema 3: Gestión Contable. Ed. 1.0 46 Casos prácticos de Excel aplicados a la gestión empresarial • “I” es la condición propiamente dicha, en este caso el indicativo de un Ingreso. • A2:A21 es la columna que contiene los valores que se deben sumar, en el caso que nos atañe son las cantidades. En el segundo de los casos (celda B24), la fórmula sería: =SUMAR.SI(D2:D21;”G”;A2:A21) Similar al caso anterior pero cambiando la condición por la “G” de gasto. Conforme vayamos introduciendo cantidades en la hoja estos totales se irán modificando. Una variante útil que podemos aplicar sobre nuestro diario, es la posibilidad de seleccionar la visualización de sólo parte del mismo en función de un concepto, una fecha y/o que se trate de un ingreso o gasto. Para aplicar esta variación, debemos marcar todo el diario (A1:D21) y seleccionar la opción Filtros del menú Datos seguido de Autofiltro, lo cual, dará como resultado que al lado del título de cada columna aparezca un desplegable que permite seleccionar los diferentes elementos que se encuentran en cada columna. Así por ejemplo, podrá seleccionar todos los gastos, o todos los movimientos con fecha “06/06/2005” o todos los apuntes con el concepto de ventas. También podemos introducir criterios múltiples como por ejemplo: gastos realizados el seis de junio. En este caso aplicaremos uno de los filtros y a continuación, sobre los datos filtrados aplicaremos el segundo de los filtros. En todos los ejemplo los totales de las líneas 23 y 24 seguirían dando los valores globales ya que el hecho de aplicar un filtro sólo afecta a la visualización de los datos pero no a las fórmulas que los incluyen. Quizás el hecho de filtrar los datos no sea una buena solución para su estudio, necesitándose una mayor perduración de los datos e incluso la posibilidad de poder trabajar con ellos aisladamente. La alternativa a los Autofiltros son los Filtros avanzados. Éstos permiten obtener una copia de los datos seleccionados sin necesidad de trabajar sobre los originales y en caso de error basta con volver a aplicar el filtro. Ed. 1.0 Tema 3: Gestión Contable. Casos prácticos de Excel aplicados a la gestión empresarial 47 Este tipo de filtro basa su funcionamiento en la creación de tres áreas en nuestra hoja de cálculos: • Rango de lista. • Rango de criterios. • Rango de salida. El primero de ellos comprende la tabla que contiene todos los datos incluyendo la fila con los títulos de las columnas. Esta fila es muy importante ya que se utilizará de forma idéntica en las dos siguientes áreas. Es conveniente que esté remarcada de alguna forma, por ejemplo, en negrita. El rango de criterios comprende una copia de la fila de títulos y al menos una fila en blanco. En las celdas de esta fila y bajo el/los título/s del dato/s correspondiente se establecerá el/los criterios que servirán de filtro. El rango de salida lo compone una nueva copia de la línea de títulos y bajo ella aparecerán los resultados de aplicar un proceso de filtrado. En nuestra ejemplo el rango de lista que se encuentra ubicado en la hoja 1 lo vamos a designar con un nombre de rango, lo cual, facilitará que posteriormente en lugar de tener que escribir la referencia de las celdas sólo tengamos que escribir el nombre del rango. Parta crear un nombre de rango seleccione todas las celdas que lo componen. En nuestro diario las celdas que van desde la A1 a la D21 y haga un “clic” con el puntero en el cuadro de nombres (se encuentra a la izquierda de la barra de fórmulas). Escriba el nombre que desea darle y pulse la tecla Enter. En el ejemplo le daremos el nombre de DATOS. A partir de este momento puede escribir DATOS en lugar de poner A1:D21. Tema 3: Gestión Contable. Ed. 1.0 48 Casos prácticos de Excel aplicados a la gestión empresarial El rango de criterios lo vamos a establecer en la hoja 2, de esta forma tanto los criterios que usemos como los datos que filtremos no se mezclarán con el diario original. Como ya dijimos anteriormente, ambos criterios incluyen una copia de los títulos de lascolumnas que componen la lista. En nuestro caso, el rango de celdas a copiar iría desde la celda A1 a la celda D1. Una vez seleccionado y copiado pulsaremos sobre la pestaña de la hoja dos y pegaremos ambas copias en las filas 1 y 6. La fila 1 será el comienzo del área de criterios y la 6 el comienzo del área de salida. El siguiente paso será establecer el criterio que vamos a utilizar antes de aplicar los filtros. Por ejemplo, supongamos que deseamos filtrar todos los totales de ventas realizados en el mes. Al tratarse de un concepto colocaremos el cursor bajo el título “CONCEPTO” y escribiremos la expresión VENTAS (B2). El siguiente paso será la aplicación del filtro propiamente. Desde la hoja 2 despliegue el menú Datos, marque la opción Filtros y seleccione Filtro avanzado. Aparecerá una ventana similar a la siguiente: En primer lugar seleccione la opción Copiar a otro lugar para que se active el recuadro Copiar a. Si no lo hace el filtro avanzado funcionará como un autofiltro y plegará la tabla de datos original. Este paso deberá repetirlo cada vez que aplique un filtro avanzado. Introduzca ahora las referencias correspondientes a la tabla de datos en la casilla Rango de la lista. Recuerde que denominamos DATOS a dicho rango, con lo cual bastará con escribir la palabra. Si lo desea puede indicarlo de forma normal A1:D21. En Rango de criterios debe indicar la fila de títulos de criterio más una fila más que contiene el criterio que introdujo anteriormente (En la hoja 2, las celdas comprendidas de la A1 a la D2. (A1:D2) Finalmente en Copiar a sólo debe indicar la fila que contiene los títulos a partir de los cuales se copiaran los datos que se filtren. En nuestro ejemplo A6:D6. Por último pulse Aceptar y deberá obtener un resultado similar al siguiente en la hoja 2 de su libro de Excel: Ed. 1.0 Tema 3: Gestión Contable. Casos prácticos de Excel aplicados a la gestión empresarial 49 Los resultados obtenidos pueden a continuación copiarse en otras hojas de su libro (recuerde que Excel puede soportar hasta 255 hojas en un único libro) y operar sobre ellos sin miedo a modificar los originales. Si desea aplicar un nuevo filtro bastará con borrar el criterio usado y colocar uno nuevo en otra o sobre la misma celda. Adicionalmente puede aplicar varios criterios de forma simultánea colocando cada uno de ellos bajo el título correspondiente en el rango de criterios. Así por ejemplo, si escribe I bajo el epígrafe “INGRESOS/GASTOS” y 06/06/2005 bajo el epígrafe “FECHA”, estará estableciendo un criterio doble, es decir, que sean ingresos y que sean de la fecha 6 de junio de 2005. Todo lo anterior permite visualizar de una forma más clara los apuntes realizados en una fecha y/o por un concepto, pero no permite obtener totales. Así por ejemplo, usted puede visualizar todas las ventas realizadas en ese mes pero no puede saber a cuanto han ascendido o cuanto son los gastos realizados en concepto de representación. Para solucionar este problema, vamos a realizar una pequeña calculadora que introduciendo un concepto nos devuelva el total de las partidas que se corresponden con dicho concepto. En la fila 30 utilizaremos la celda A30 para introducir el título “CONCEPTO” y la celda B30 para introducir el título “TOTAL”. En la fila 31 reservaremos la celda A31 para introducir el concepto que deseamos totalizar y la celda B31 para aplicar la fórmula que calcule el total. Para realizar esta tarea usaremos la función BDSUMA, cuya sintaxis es: BDSUMA(base de datos;nombre de campo;criterios) Tema 3: Gestión Contable. Ed. 1.0 50 Casos prácticos de Excel aplicados a la gestión empresarial Base de datos es el rango de celdas que componen la lista. En nuestro caso las celdas que van desde A1 hasta D21, o lo que es lo mismo el rango denominado DATOS. El nombre de campo es el título de la columna que se sumara (BDSUMA) de los que componen la lista. En nuestro ejemplo “CANTIDAD” o lo que es lo mismo A1, celda en la que se encuentra dicho título. Criterios lo componen una celda con el nombre de una columna y una segunda celda con el dato que servirá de criterio. El nombre de la columna debe coincidir con una de las especificadas en la lista. En nuestro caso A30:A31. La función quedaría: =BDSUMA(DATOS;A1;A30:A31) Si introducimos la palabra VENTAS en la celda A31, el resultado sería similar al siguiente: Si escribimos el concepto RECIBO LUZ, el resultado será: Este procedimiento podría ser realizado también usando la función SUMAR.SI, en cuyo caso la fórmula sería la siguiente: =SUMAR.SI(B1:B21;A31;A1:A21) El siguiente procedimiento que desarrollaremos, nos permitirá consultar el total de gastos o ingresos que se produjeron en una fecha. En este caso nuestra condición será doble, teniendo que introducir en una celda la fecha y en otra diferente si es un gasto o ingreso. En este caso utilizaremos la fila 26 para ubicar los títulos de los datos. Así en la celda A26 escribiremos “INGRESOS/GATOS”, en la celda B26 colocaremos “FECHA” y en la C26 “TOTAL”. La fila 27 la reservaremos para los datos y la fórmula. Esta última se ubicará en la celda C27. Para desarrollar la misma necesitaremos hacer uso de la función DBSUMA. En este caso no es posible realizarla con SUMAR.SI ya que se desea establecer una doble condición. La formula resultante en este caso sería: =BDSUMA(DATOS;A1;A26:B27) Nuevamente utilizamos el nombre de rango DATOS para referirnos al conjunto del diario y utilizamos la celda A1 (CANTIDAD) para señalar el campo que debe sumarse. La variación con respecto al uso anterior de la función es la inclusión de dos títulos y dos criterios, lo que obligará a que deban cumplirse ambos de forma simultanea. Ed. 1.0 Tema 3: Gestión Contable. Casos prácticos de Excel aplicados a la gestión empresarial 51 Este elemento generado permite calcular por ejemplo los ingresos generados el seis de junio de 2005. Para obtener esta información deberemos escribir Un “I” en la celda A27 y “06/06/2005” en la celda B27. El resultado obtenido al aplicar los datos anteriores sería: Finalmente recordaremos que estos valores no varían aunque simultáneamente realice filtrado de datos. 3.2. COSTES DE PRODUCCIÓN. El coste de un producto o servicio es el valor de los factores que intervienen en el proceso de producción: mano de obra, materiales, desgaste de maquinaria, energía, etc. Así, por ejemplo, en la fabricación de pan hay que tener en cuenta: • La harina y levadura empleada (materia prima). • El coste laboral (mano de obra). • El papel en que se envasa (material). • La energía consumida. • El desgaste de la maquinaria y las herramientas empleadas. Es decir, los costes de todos los elementos y factores implicados en su fabricación. Estos costes pueden clasificarse en tres grupos: • Materias primas. Consumidas en el proceso productivo. • Mano de obra. Interviene en la fabricación del producto. • Gastos de fabricación. Resto de gastos que se producen en el lugar que se fabrica el producto. Existen diversos criterios de imputación de costes a los productos o servicios. El sistema de costes utilizado en cada empresa dependerá de: • Los objetivos. • Las características. • La complejidad o sencillez que se desee. Tema 3: Gestión Contable. Ed. 1.0 52 Casos prácticos de Excel aplicados a la gestión empresarial Se diferencian tres sistemas para el cálculo de costes: • Direct-Costing: se trata de un método que imputa a los productos únicamente los costes variables. Todos los costes de estructura van contra la cuenta de resultados. Este sistema es aconsejable cuando la mayor parte de los costes de la empresa son directos y/p existe una gran dificultad para asignar el resto de costes a los productos. • Coste Total: se imputan todos los costes de la empresa a los productos. Los costes directos se imputan directamente a los productos y los indirectos se reparten entre los productos. • Coste Industrial: Imputa a los productos lo que cuesta su fabricación llevando el resto de costes de laempresa contra la cuenta de resultados, sin entrar a formar parte del valor de los productos. Partamos de un ejemplo para la generación de una hoja de cálculos que nos permita calcular los costes según los tres sistemas. Nuestro modelo será muy sencillo y partiremos de unos totales ya calculados, es decir, supondremos que previamente a los cálculos que vamos a desarrollar se ha realizado una labor de cálculo de costes por sección y que los datos de partida son el resultado de dichos cálculos. El modelo se reducirá a sólo dos productos, pero podríamos realizarlo con tantos como deseemos. Situación Inicial: Una empresa ha fabricado dos productos (A y B) de los cuales ha producido respectivamente 2.000 y 3.000 unidades. Las ventas de ambos productos suponen respectivamente 120.000€ y 180.000€. Para la producción de A se han gastado 24.000€ en materias primas y 60.000€ en la mano de obra. Para la producción de B se han gastado 36.000€ en materias primas y 72.000€ en la mano de obra. Los gastos de fabricación que no pueden imputarse directamente a ninguno de los productos asciende a 16.000€. Los costes fijos directos ascienden a 3.600€ en el caso del producto A y a 8.400€ en el producto B. Finalmente los gastos de administración suponen 48.000€. Nuestro primer paso será situar toda la información en forma de una tabla que facilite el trabajo de análisis que tendremos que realizar con los datos. La forma elegida es la siguiente: Ed. 1.0 Tema 3: Gestión Contable. Casos prácticos de Excel aplicados a la gestión empresarial 53 Deberemos tener en cuenta que hay gastos que son imputables a cada producto y que por tanto se han recogido en la columna correspondiente. Por otro lado, existen gastos que no pueden atribuirse directamente a cada producto, y que por tanto, se consideran en la columna de totales. Empezaremos a colocar los datos a partir de la celda A1 de nuestra hoja de cálculos y por tanto la distribución final será la siguiente: CALCULO DEL DIRECT-COSTING. En primer lugar realizaremos el cálculo del Direct-Costing. Sobre la fila 11 y a partir de la columna A de nuestra hoja de cálculo situaremos el nombre del método y los nombres de los dos productos: En la fila 12 reflejaremos las cantidades producidas de cada producto y que serán necesarias en algunos de los cálculos. Costes fabricación variables por producto. En la siguiente fila calcularemos los costes fabricación variables por producto. Estos costes son la suma de los costes de Materias primas y de Mano de obra. Tema 3: Gestión Contable. Ed. 1.0 54 Casos prácticos de Excel aplicados a la gestión empresarial En la celda A13 escribimos la etiqueta de texto, Costes de fabricación variables. En la celda B13 escribiremos la fórmula que sumará el contenido de las celdas B4 y B5. La formula sería: =B4+B5 La fórmula creada en B13 puede ser copiada en la celda C13 para calcular el total del Producto B. Costes de fabricación. Los costes de fabricación variables deben ser repartidos en función del importe de las cantidades producidas. Haciendo una simple regla de tres podríamos decir que sí al total de productos producidos le corresponde unos gastos de fabricación por un importe de 16.000€, al producto que queremos calcular le corresponden X. (2000 + 3000) à 16.000€ 2000 à X La fórmula para implementar el procedimiento anterior podría ser la siguiente: =$D$7*B12/($B$12+$C$12) D7 es la celda que contiene el total de los gastos de fabricación. B12 es el total de productos A y C12 el total de productos B. Fíjese que en la fórmula se han convertido en referencias absolutas los contenidos de las celdas que no deben modificarse al copiar la fórmula, de ahí que aparezcan con signos $ delante de la referencia a la fila o columna. Esta fórmula iría colocada en la celda B14. Una vez creada la fórmula puede copiarla sobre la celda C14. Por último, sólo quedará introducir el texto “Gastos de fabricación” en la celda A14. Total costes directos. Para calcular el total de costes directos de cada producto, sumaremos los costes de fabricación variables con los gastos de fabricación. En la celda A15 colocaremos la etiqueta de los datos: “Total coste directo”. La fórmula de la celda B15, donde se calcula el total del producto A es: =SUMA(B13:B14) o =B13+B14 Puede arrastrar la fórmula y copiarla sobre la celda C15 para calcular el producto B. Ed. 1.0 Tema 3: Gestión Contable. Casos prácticos de Excel aplicados a la gestión empresarial 55 Margen bruto. El cálculo del margen bruto se obtiene restando a las ventas del producto A, el total del coste directo de dicho producto. Colocamos el título “Margen bruto” en la celda A16 y en la celda B16 colocamos la siguiente fórmula. =B2-B15 B2 es las ventas del producto A y B15 es el margen bruto del mismo producto. Margen bruto empresa. El margen bruto de empresa será la suma de todos los márgenes brutos de productos. En la celda A18 colocaremos la etiqueta “Margen bruto empresa” y en la celda B18 introduciremos la fórmula que sume todos los totales calculados en el punto anterior. La fórmula será: =SUMA(B16:C16) Resultado empresa. Por último calcularemos los resultados que deben obtenerse. Este dato se obtiene al restar al margen bruto de empresa los costes fijos (costes fijos directos y gastos de administración). Nuevamente colocaremos la etiqueta, en este caso en la celda A19. En este caso “Resultado empresa” y en la celda B19 escribiremos la fórmula: =B18-SUMA(B8:C8)-D9 B18 es el margen bruto de empesa. B8:C8 son los costes fijos directos de cada producto. D9 son los gastos de administración. El resultado final debería ser similar al siguiente: Tema 3: Gestión Contable. Ed. 1.0 56 Casos prácticos de Excel aplicados a la gestión empresarial CALCULO DEL COSTE TOTAL. A continuación desarrollaremos los cálculos correspondientes al sistema de Coste total. Sobre la fila 21 y a partir de la columna A de nuestra hoja de cálculo situaremos el nombre del método y los nombres de los dos productos: En la fila 22 nuevamente reflejaremos las cantidades producidas de cada producto y que serán necesarias en algunos de los cálculos. Partimos de la base que el coste total del producto es igual a los costes variables más los costes fijos más los costes de administración. Reparto de los costes de admón. Nuestro primer objetivo será calcular el reparto de los costes de administración, los cuales están indicados en forma de total y deben ser repartidos entre todos los productos producidos. Al igual que en el apartado anterior, se trata de una simple regla de tres, donde las ventas del producto A es al total de ventas como X es a los Gastos de Administración. 120.000€ à (120.000 + 180.000€) 120.000€ à 300.000€ X à 48.000€ X à 48.000€ Colocamos el título “Reparto de los costes de admón” en la celda A23 y en la celda B23 introduciremos la fórmula correspondiente al producto A. =$D$9*B2/($B$2+$C$2) Se han fijado los valores de aquellas celdas que no deben moverse al copiar la fórmula. Una vez introducida la fórmula podrá copiarse a la celda contigua: C23. Coste total. Los costes totales se realizan sobre cada producto, y será la suma de: las materias primas empleadas (B4), la mano de obra (B5), los costes fijos directos (B8), los gastos de fabricación (B14) y los costes de administración (B23). Sobre la celda A24 colocamos la etiqueta “Coste total”. Posicionamos en cursor en la celda B24 y sumamos todas las partidas indicadas. La fórmula quedaría de la siguiente manera: =B4+B5+B14+B8+B23 Ed. 1.0 Tema 3: Gestión Contable. Casos prácticos de Excel aplicados a la gestión empresarial 57 Resultado del producto. El resultado del producto saldrá de restar a las ventas del producto el coste total del mismo. En la celda A25 colocamos la etiqueta “Resultado producto” y en la celda B25 introduciremos la fórmula: =B2-B24 Resultado empresa. Finalmente, el resultado
Compartir