Casos prácticos de excel aplicados a la gestión empresarial - Oscar Rojas

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