138

4.12. SERIES
Una serie es un conjunto de datos que se incrementan automáticamente al arrastrar desde el cuadro
de llenado, las series se crean en columnas o filas y con cierto tipo de datos.

Diversos ejemplos de series, como los que se muestra a continuación, se pueden desarrollar en Excel. Hay
series de fechas, de números, de texto, etc.

Lunes de
agosto,
Días de Días laborales septiembre y Primer día de Días de la
agosto de agosto octubre cada mes Meses semana
1/8/2019 1/8/2019 5/8/2019 1/8/2019 enero Lunes
2/8/2019 2/8/2019 12/8/2019 1/9/2019 febrero Martes
3/8/2019 5/8/2019 19/8/2019 1/10/2019 marzo Miércoles
4/8/2019 6/8/2019 26/8/2019 1/11/2019 abril Jueves
5/8/2019 7/8/2019 2/9/2019 1/12/2019 mayo Viernes
6/8/2019 8/8/2019 9/9/2019 1/1/2020 junio Sábado
7/8/2019 9/8/2019 16/9/2019 1/2/2020 julio Domingo
8/8/2019 12/8/2019 23/9/2019 1/3/2020 agosto
9/8/2019 13/8/2019 30/9/2019 1/4/2020 septiembre
10/8/2019 14/8/2019 7/10/2019 1/5/2020 octubre
11/8/2019 15/8/2019 14/10/2019 1/6/2020 noviembre
12/8/2019 16/8/2019 21/10/2019 1/7/2020 diciembre

Series de
porcentajes Tasas de interés 2% 4% 6% 8% 10% 12% 14% 16% 18% 20%
Progresión lineal 1 2 3 4 5 6 7 8 9 10
Series de
números Progresión aritmética 1 3 5 7 9 11 13 15 17 19
Progresión geométrica 1 2 4 8 16 32 64 128 256 512

4.12.1. Primera Actividad a Realizar:

Elabore todas las series que se presentaron en el cuadro inmediatamente precedente

4.12.1.1. Procedimiento

Para todas las series que aparecen en el ejercicio, con las excepciones que se indican más abajo:

1. Cree un nuevo libro llamado Ejercicio 4-12

2. Introduzca el primer dato de la serie
3. Seleccione todas las celdas que deba ocupar la serie
4. Vaya a “Modificar | Rellenar | Series”. Aparecerá un cuadro de diálogo como el siguiente (el apartado
Unidad de tiempo sólo estará activo cuando los datos seleccionados tengan formato de fecha):

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 139

5. Debe configurar este cuadro en cada caso, de manera que la serie se llene correctamente, teniendo en
cuenta que lo que introduzca en Incremento será lo que se añadirá de una celda a otra (sean unidades,
días, años, etc, según el caso). Tenga en cuenta que el paso del 2% al 4% supone un incremento de
0,02 (use la coma del teclado alfanumérico).
6. Una vez configuradas las opciones correctas, pulse Aceptar.

Excepciones a lo anterior:
• Series predeterminadas: como hemos visto en el cuadro anterior, las series de meses del año y días de
la semana tienen un procedimiento especial de relleno.
• Serie numérica lineal: la serie 1, 2, 3, 4, 5… se llena de la misma manera que las de meses del año y
días de la semana, con la diferencia de que se ha de pulsar la tecla ctrl. antes de hacer clic y mientras
se arrastra el ratón.
• Serie de progresión aritmética: introduzca los dos primeros elementos de la serie (1 y 2). Luego,
seleccione todas las celdas de la serie salvo la primera y especifique un incremento de 2.
• Serie de progresión geométrica: introduzca los dos primeros elementos de la serie (1 y 2). Luego,
seleccione todas las celdas de la serie (incluida la primera) y vaya a Edición, Rellenar, Series….Aquí
tendrá que activar la casilla Tendencia del cuadro de diálogo indicado más arriba y, lógicamente,
seleccionar la opción Geométrica en el apartado Tipo.

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 140

4.13. MANEJO DE FECHAS Y HORAS POR SEPARADO

En Excel los datos de tipo fecha son almacenados internamente como números de serie que representan la
cantidad de días transcurridos desde el 01/01/1900, es decir para Excel la fecha 01/02/1900 sería el nº de
serie 32 (31 días de enero + 1 día de febrero). Debido a esta característica, los cálculos que realicemos
entre fechas siempre devolverán el intervalo de días entre esas dos fechas.

Como decíamos, Excel asigna un número correlativo a cada día desde el 01/01/1900. A este número se le
conoce como “número de serie”. Esta nomenclatura permite que se puedan hacer cálculos de cualquier
tipo de complejidad.

Observe la siguiente tabla. La columna de la izquierda muestra una fecha y la columna de la derecha, su
número de serie correspondiente.

Por ejemplo, el 1º de enero de 2018 (01/01/2018) es el número 43.101 porque han pasado 43.101 días
desde el 1 de enero de 1900.

4.13.1. Introducción de Fechas

A la hora de introducir una fecha, Excel es capaz de reconocer varios formatos diferentes, que convierte
en un número de serie y le da aspecto de fecha cuando pulsa Intro. Presentamos algunos ejemplos de
datos que puede introducir. Observe lo que ocurre con cada uno de ellos al pulsar Intro:

Dato introducido Fecha reconocida

1-1-2018 01/01/2018
1-1-18 01/01/2018
1/1/2018 01/01/2018
1/1/18 01/01/18
1-ene-18 01-ene-18
1-ene 18 01-ene-18
1-ene-2018 01-ene-18
1 ene 2018 01-ene-18
1/1 1-ene

4.13.2. Algunos cálculos con fechas en Excel

Como se comentó, las fechas son números de serie, ergo, números y por tanto es posible realizar
operaciones matemáticas con fechas y horas. A continuación, se presentan algunos ejemplos sencillos

4.13.2.1. Sumar o restar días de una fecha determinada

Sumar días

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 141

Como puede ver en la tabla anterior, puede agregar o quitar un número de días a una fecha determinada
directamente en la fórmula. Se trata de una suma normal y corriente.

Restar dos fechas

Para averiguar los días de diferencia que hay entre dos fechas, lo mejor es hacer una resta. Mire la tabla.

Es posible que el formato de la celda donde introduce la fórmula no sea el adecuado y por ende el resultad
(normalmente cuando hace operaciones con dos fechas, el resultado también es una fecha). Por tanto, para
ver el número de días, cambie el formato de la celda a General o a Número.

Como en estos ejemplos no utilizamos horas, las fechas comienzan a contar desde las 00:00 del primer
día hasta las 23:59:59 del último día.

4.13.3. Curiosidades sobre fechas en Excel

• Excel no reconoce fechas anteriores a 01 de enero de 1900.
• Se mostrará en la celda ############# cuando contenga una fecha negativa.

Días laborables
Ya ha visto que cuando resta dos fechas, se obtienen los días que han pasado entre una y otra. Pero, claro,
hablamos de días naturales ¿y los días laborables? Para ello existe la función =DIAS.LAB(Fecha inicio;
Fecha final; Festivos).

Pruebe el siguiente ejemplo:

La función DIAS.LAB calcula los días transcurridos entre dos fechas quitando los fines de semana
(sábados y domingos) y los festivos (feriados) indicados en el rango del tercer argumento.
Aquí hay una diferencia de opiniones porque hay quien dice que el sábado es laborable y hay quien dice
que el fin de semana entero no es laborable, la función DIAS.LAB considera esto último.

Para solucionar este dilema, a partir de la la versión 2010 de Excel se ha sacado la función
=DIAS.LAB.INTL(fecha_inicial; fecha_final; fin_de_semana; festivos) donde en el argumento “fin de
semana” se puede elegir qué consideramos como festivo siguiendo los siguientes códigos:

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 142

4.13.3.1. Calcular Años entre dos Fechas

La resta de dos fechas, dividido entre 365,25 días de un año (porque cada 4 años, uno de ellos es bisiestos
y tiene 366 días) devolverá los años entre esas dos fechas. Como este cálculo nos dará un resultado con
decimales, tendremos que despreciarlos en el cálculo utilizando la función ENTERO.

AÑOS -> ENTERO((Fecha2-Fecha1)/365,25)

4.13.3.2. Calcular Meses entre dos Fechas

Para realizar este cálculo tendremos que restar las dos fechas (obtenemos días) y el resultado dividirlo por
la cantidad de días de un mes. Para este cálculo utilizamos el valor aproximado de 30,437 días del mes
(porque no todos los meses son de 30 días), este valor lo obtenemos del promedio de días mensuales
durante un periodo de 4 años.

MESES -> ENTERO((Fecha2-Fecha1)/30,437)

4.13.4. La función SIFECHA

Para realizar cálculos con funciones, utilizaremos la función SIFECHA.

La función SIFECHA es una función de Excel a la cual no tenemos acceso desde el catálogo de
funciones y sin embargo ha estado presente en las últimas 7 versiones del programa incluyendo la versión
2010 de Excel.

Esta función nos ayuda a obtener la diferencia entre dos fechas y además nos permite elegir el tipo de
dato que deseamos como resultado y que pueden ser días, meses o años. La sintaxis de la función
SIFECHA es la siguiente:

=SIFECHA(fecha1, fecha2, tipo)

El primero y segundo argumentos son fechas en donde la fecha1 es más antigua que fecha2. Dicho de otra
manera, la fecha2 debe ser la fecha más reciente. Si estos parámetros se especifican al revés, entonces
obtendremos un error del tipo #¡NUM!

El tercer argumento de la función SIFECHA es de suma importancia porque es donde especificaremos la

unidad de medición de tiempo que deseamos utilizar. A continuación, la tabla de posibles valores para
este tercer argumento:

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 143

Valor Significado Descripción

"d" Días Número de días entre la fecha1 y la fecha2
"m" Meses Número de meses entre la fecha1 y la fecha2
"y" Años Número de días años la fecha1 y la fecha2

El uso de la función SIFECHA es de gran utilidad especialmente para obtener las diferencias en meses o
años entre dos fechas ya que dicho cálculo requiere de mucha pericia para ejecutarlo correctamente
debido a las diferencias en el número de días que tiene cada mes del año.

Ejemplos:

A continuación, un ejemplo utilizando la función SIFECHA:

El mismo ejemplo, comparando los cálculos realizados por medio de fórmula con los que utilizan la
función SIFECHA.

4.13.5. Tratamiento de las Horas

En Excel las horas son tratadas como la parte proporcional de un día en formato de 24h y se representan
internamente como un número entre 0 y 1, siendo el valor cero las 00:00:00 y el 1 las 23:59:59.

Las horas tienen un formato un poco más estricto que las fechas. Como mínimo, para que Excel
reconozca la hora, se debe introducir al menos los valores h:mm (horas y minutos separados por dos
puntos, sin espacios).

En caso de que quiera hacer referencia también a los segundos el formato deberá ser h:mm:ss.
Excel también admite las siglas AM/PM para hacer referencia a la mañana o la tarde. Simplemente
introduzca AM o PM después de la hora. También puede aplicar este formato a la celda después de haber
introducido el valor.

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 144

En resumen, cada 24 horas habría que sumar 1 al número de serie. Si, por ejemplo, queremos representar
el mediodía, se lo hace con el número 0,5 porque ha pasado medio día desde las 0:00 h.
Un resumen:

Según este criterio de representación interna de las horas el valor 0 representa las 00:00 (12 de la noche),
el valor 0,25 representa las 06:00, el valor 0,5 representa las 12:00 (12 del mediodía), el valor 0,75
representa las 18:00.

4.13.6. Cálculos con horas en Excel

Cuando realizamos cálculos con horas obtendremos un valor entre 0 y 1 que representa el intervalo de
tiempo transcurrido entre ambas horas.

Tiempo transcurrido entre dos horas

Horas -> (hora2-hora1)*24

Pasar Horas a Minutos

Minutos -> hora*24*60 (obtenemos el valor de la hora y lo pasamos a minutos)

Pasar Minutos a Días

Días -> ENTERO(TotalMinutos/60/24)

Pasar Minutos a Horas

Horas -> ENTERO(TotalMinutos/60), el resto de los minutos sobrantes en el cálculo los obtendremos de
la siguiente manera -> RESIDUO(TotalMinutos;60)

4.13.6.1. Sumar tiempo a una hora

La operación de añadir tiempo (horas, minutos o segundos) a una hora se la realiza también mediante el
signo operador suma (+), tal cual se puede observar en la siguiente tabla:

Se aprecia en la tabla que las horas que exceden de un día natural están representadas por un número de
serie superior a 1. Tal es el caso del cálculo de la hora de la fila 4. Como la celda tiene formato de hora,
no se muestra la diferencia real de horas, sino el resultado de sumar cuatro horas a las 10 de la noche…lo
que obviamente es las 2 de la mañana.

Sin embargo, si deseáramos que se muestre que las 22:00 horas más 4:00 horas son 26:00 horas, se debe
cambiar el formato de la celda a uno parecido a este: [h]:mm:ss, que es lo que se presenta en la fila 5.

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 145

4.13.6.2. Restar tiempo de una hora

De la misma forma que puede sumar tiempo a una hora, también se puede restar. El resultado también
será una hora. Observe cómo se muestra el resultado negativo en la siguiente tabla:

Nótese que en la tabla no se muestra ninguna hora negativa. Excel no puede mostrar horas negativas por
lo que el resultado que se obtendría sería ####################.

4.13.6.3. Restar una hora de otra

Si en vez de restar tiempo de una hora, lo que quiere es restar dos horas, la operación es la misma que la
anterior: sólo cambian los datos que se van a restar.

4.13.6.4. Curiosidades sobre horas en Excel

Excel no puede almacenar horas sin una fecha asociada. Cuando introduce en una celda, por ejemplo
17:30:00, se considera como las cinco y media del día 0 de enero de 1900

4.13.6.5. Extracción de partes de una hora:

En esta sección presentamos las funciones HORA(), MINUTO() Y SEGUNDO(). Estas funciones extraen
de una hora (h:mm:ss) las horas, los minutos y los segundos respectivamente.

Por ejemplo, si en la celda A1 hay escrito 19:25:30 la función =HORA(A1) dará 19, la función
=MINUTO(A1) dará 25 y la función =SEGUNDO(A1) devolverá 30.

Esto resultará muy útil en aquellos casos en los que hay que pasar todo a minutos para multiplicar por
algún importe en concreto, aspecto muy usual en el cálculo de tarifas telefónicas. Por ejemplo:

Explicando la fórmula de la celda C4 de la tabla precedente. Por un lado, se sacan los días de la celda B4,
los multiplica por 24 horas que tiene el día y por 60 minutos que tiene la hora. Luego, se sacan las horas

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 146

de la celda B4, las multiplica por 60 para pasarlas a minutos HORA(B4)*60 y por último se extraen los
minutos con MINUTO(B4).

4.13.7. Actividad a Realizar

Deseamos realizar una planilla que permita gestionar las horas trabajadas y las horas extras de cada
trabajador en una empresa según los registros que tenga cada día de su entrada, de su salida y de sus
descansos. El formato de la planilla deberá ser similar al que a continuación sigue:

En las celdas D5 y D 6 se deberán obtener los resultados finales producto del cálculo y/o análisis de todos
los datos que se ingresen a partir de la fila 9

4.13.7.1. Procedimiento

1. Introduzca, en las mismas celdas que se ven en la figura anterior, los respectivos
rótulos y encabezados:
2. Inserte los siguientes valores (horas) en cada una de las celdas de la figura. Nótese que
para el caso de las horas se introduce la hora, dos puntos (:), los minutos. Si se quiere
introducir segundos, también se debe separar con dos puntos (:).

3. (Cálculos columna total horas) En la celda H9, escriba =(E9-D9)-(G9-F9) y luego

copie hacia abajo.
4. (Cálculos columna horas trabajo) En la celda I9, escriba =SI(H9>=$G$5;$G$5;H9) y
luego copie hacia abajo
5. (Cálculos columna horas extras) En la celda J9, escriba =H9-I9 y luego copie hacia
abajo
6. (Cálculos celda horas trabajadas) En la celda d5, escriba =SUMA(I9:I13)
7. (Cálculos celda horas extras) En la celda d6, escriba =SUMA(J9:J13)

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 147

Los resultados lucirán así:

Sin embargo, este resultado tiene un problema. Como se puede ver en la celda D5 aparecen sólo 14:58
horas, resultado notoriamente equivocado, por cuanto se ve que las horas del rango I9:I13 son muchas más
que sólo 14:58.

El problema se genera cuando el resultado es más de 24 horas, para ello haga clic en las celdas D5 y D6
• En el grupo Celdas de la pestaña Inicio, elija Número y luego Formato de celdas.
• En la lista Categoría del cuadro Formato de celdas, elija Personalizada.
• En el cuadro tipo, en la parte superior de la lista de formatos, escriba [h]: mm; @ y, después, haga
clic en Aceptar.
Con esto, ahora el resultado será el correcto:

4.14. MANEJO DE FECHAS Y HORAS JUNTAS

En los apartados anteriores hemos visto como trabajar con datos que sólo representan fechas o sólo
representan horas. Cuando el dato representa una fecha y hora los cálculos que tenemos que realizar hay
que condicionarlos a que se tengan en cuenta las dos partes del dato: la fecha y la hora.

Una vez que hemos visto que tanto las fechas como las horas pueden representarse con un número entero
y decimal respectivamente, no le costará mucho imaginar que es posible darle un número a cualquier
instante.

Por ejemplo, el día 10/10/19 a las 10:12 de la mañana tiene el número de serie 43.748,425. 43.748
corresponde al día y la parte decimal 0,425 corresponde a la hora.

Ejemplos:

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 148

Los cálculos con fechas siempre se realizan utilizando como unidad de medida de tiempo el día. Según
esta regla, si el 1 representa una unidad de tiempo “día” y si quisiéramos trabajar con unidades de tiempo
que representen horas dividiríamos el 1 entre las 24 horas que lo componen, y si quisiéramos trabajar con
unidades de tiempo que representen minutos dividiríamos el 1 entre los 1440 minutos que lo componen.

TABLA DE UNIDADES PARA EL CÁLCULO CON DATOS DE TIPO FECHA/HORA

UNIDAD DE TIEMPO UNIDAD DE CÁLCULO SE CALCULA
1 Día 1
1 Hora 0,041666667 UNIDAD DÍA / 24
1 Minuto 0,000694444 UNIDAD HORA / 60

4.14.1. Introducir fechas y horas en Excel juntas

Ahora que ya sabe introducir fecha y horas por separado, es el momento de hacerlo conjuntamente en la
misma celda.

Fíjese que puede introducir primero la fecha y luego la hora y viceversa.

4.14.1.1. Calcular Horas entre dos Fechas

Para realizar este cálculo tendremos que restar las dos fechas (obtenemos días) y el resultado multiplicarlo
por las 24 horas de un día.

HORAS -> (Fecha2 – Fecha1) * 24

4.14.2. Separar día, mes, año, horas, minutos con funciones.

Emplearemos las siguientes funciones:

• Función DIA. Devuelve el día del mes (número de 1 a 31).
• Función MES. Devuelve el mes (número de 1 enero a 12 diciembre).
• Función AÑO. Devuelve el año como número entero entre 1900 y 9999.
• Función HORA. Devuelve la hora como número 0 a 23.
• Función MINUTO. Devuelve el minuto como número de 0 a 59.
• Función SEGUNDO. Devuelve el segundo como número de 0 a 59.

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 149

En la columna A disponemos de valores de fecha/hora donde deseamos extraer los diferentes valores
individuales.

Y a partir de la columna B, hasta la columna G, obtendremos de forma separada, el día, mes, año, hora,
minuto y segundo de la fecha especificada en la columna A.

4.14.3. Obtener desglosados los Días, Horas y Minutos entre dos

datos de tipo Fecha/Hora
Vamos a ver el caso inverso a los anteriores, donde a partir de dos datos de tipo fecha/hora obtendremos el
total de horas, minutos y segundos transcurridos entre ambas fechas.

Obtener los días: Se calculan con la diferencia entre las dos fechas y despreciando la parte decimal que
representarían horas y minutos.
Días = ENTERO(Fecha2-Fecha1)

Obtener las horas: Se calculan sobre los decimales obtenidos en el cálculo de los días, multiplicándolos
por 24 y quedándose con la parte entera del cálculo.

Horas= ENTERO(RESIDUO(Fecha2-Fecha1;1)*24)

Obtener los minutos: Se calculan sobre los decimales obtenidos en el cálculo de las horas,
multiplicándolos por 60 y redondeando el resultado a 0 decimales.
Minutos= REDONDEAR(RESIDUO((Fecha2-Fecha1)*24;1)*60);0)

4.14.4. Convertir de una unidad de tiempo a otra (mayor o menor)

con la función CONVERTIR.

Emplearemos la función CONVERTIR. Convierte un valor en número, con un formato de número

específico.

Las unidades de tiempo que podemos convertir en la función son:

Unidad Argumento función
Año "yr"
Día "day" o "d"
Hora "hr"
Minuto "mn" o "min"
Segundo "sec" "s"

Un ejemplo de las diferentes posibilidades de conversión se presenta a continuación:

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 150

4.14.5. Primera Actividad a Realizar

Supongamos que disponemos las fechas horas de salida de una serie de mercancías de un almacén y
deseamos obtener la fecha hora prevista de llegada de la mercancía al destino conociendo los días, horas y
minutos que dura el viaje en cada caso.

4.14.5.1. Procedimiento

1. Si deseamos obtener una fecha/hora a partir de otra fecha/hora sumándole unidades de día, hora y
minuto, tendremos que sumar a la fecha/hora inicial el total de días por su unidad de cálculo, más
el total de horas por su unidad de cálculo, más el total de minutos por su unidad de cálculo.

Nueva fecha/hora = fecha/hora inicial + días*und.día + horas*und.hora + minutos*und.minutos

2. Para ello, primero ponemos los conversores en las siguientes celdas:
En la celda C1=1
En la celda D1=1/24
En la celda E1=1/(24*60)
3. Posteriormente, ponemos en la celda F3: =B3+C3*C$1+D3*D$1+E3*E$1
4. Copiamos esa fórmula al rango F4:F7
5. Si por alguna razón no aparece el resultado esperado, nos aseguramos que el formato sea el que
corresponde mediante Inicio | Número | Personalizada | d/m/yyyy hh:mm

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 151

4.14.6. Segunda Actividad a Realizar

Si por el contrario a la anterior actividad, lo que disponemos son las fechas/horas de salida y las fechas/horas
de llegada y lo que deseamos obtener son los días, horas y minutos que dura el viaje en cada caso,
tendríamos la siguiente situación:

4.14.6.1. Procedimiento

1. Para calcular los días del primer transporte, la fórmula utilizada sería: =ENTERO(C3-B3)
2. Para calcular las horas del primer transporte la fórmula utilizada sería:
=ENTERO(RESIDUO(C3-B3;1)*24)
3. Para calcular los minutos del primer transporte, la fórmula utilizada sería:
=REDONDEAR(RESIDUO((C3-B3)*24;1)*60;0)
4. Copiamos ess fórmula al rango D4:F7

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 152

4.15. ESCENARIOS Y PROTECCIÓN DE CELDAS

4.15.1. Primera Actividad a Realizar, Escenarios:

Los escenarios son especialmente útiles en los llamados análisis “What if (Y Si...”`o “¿Qué pasaría
si...?”). Es decir, aquellos análisis en los que se desea comparar los resultados obtenidos al cambiar
algunos factores del problema.

Por ejemplo, queremos hacer un seguimiento de las ventas de un determinado producto a lo largo del
primer semestre, comparando el importe de las ventas de mes en mes.

En este caso, existen 3 factores que no varían:

• El nombre del artículo.
• El precio unitario del artículo.
• El descuento comercial aplicado en la venta.
En cambio, existen dos factores que sí varían:
• El mes analizado.
• La cantidad de unidades vendidas del artículo: de este factor depende el resultado final (el
importe de las ventas del mes).

La pregunta aquí es: ¿cuál será el importe de las ventas si cambia la cantidad vendida?

4.15.1.1. Procedimiento
1. Abra un nuevo Libro en Excel.
2. En la celda A1 introduzca el texto Enero. En B1, Venta de Charques Jacarandá.
3. Introduzca los siguientes rótulos:
• A3: Cantidad.
• A4: Precio unitario.
• A5: Subtotal.
• A6: % IVA.
• A7: Importe IVA.
• A8: TOTAL.
4. Las cantidades y fórmulas a introducir son las siguientes:
• B3: 87.
• B4: 3 Bs.
• B5: =B3*B4.
• B6: 13%.
• B7: =B8-B5.
• B8: =B5/(1-B6).
5. Seleccione “Datos | Herramientas de Datos | (Análisis Y si) | Administrador de escenarios”). En
el cuadro de diálogo que aparezca, pulse Agregar.
6. En el cuadro de texto Nombre del escenario escriba Enero.
7. En el cuadro de texto celdas cambiantes, seleccione, en primer lugar, la celda A1; luego, pulsando la
tecla Control, seleccione la celda B3 (observe que, por defecto, Excel considera las celdas como
referencias absolutas). A continuación, pulse Aceptar.
8. En el cuadro de diálogo que aparece, nos solicita que introduzcamos un valor para las celdas
cambiantes (A1 y B3): aparecen por defecto los valores correspondientes al mes de enero, es decir,
Enero y 87. Pulse Aceptar.
9. Repita la operación para los siguientes cinco meses, cambiando el nombre del escenario; cuando le
pidan valores para las celdas cambiantes (A1 y B3) escriba, para A1, los nombres de los diferentes
meses (uno para cada escenario) y, para B3, valores distintos de 87.
10. Una vez creado el escenario para Junio, en el cuadro de diálogo inicial aparecerán los nombres de los
seis escenarios creados. Para alternar entre ellos, seleccione el escenario que quiera consultar y pulse
Mostrar. Finalmente, pulse Cerrar. A partir de ahora, podrá consultar en cualquier momento cualquier
escenario, agregar otros nuevos o modificar los ya existentes yendo a “Datos | Herramientas de Datos
| (Análisis Y si) | Administrador de escenarios”)
11. Guarde el ejercicio con el nombre Ejercicio 4-12.

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 153

Nota: los análisis “Y si...” también pueden plantearse en términos de previsión (de hecho, el caso más
frecuente de esto es el que pretende tomar decisiones sobre “futuros escenarios”). En el caso anterior, por
ejemplo, podría tratarse de una previsión de ventas más que de un seguimiento.

4.15.2. Segunda Actividad a Realizar, Protección de Hojas y Celdas:

Es muy posible que las hojas de cálculo que realicemos sean tan buenas que otros usuarios también
querrán utilizarlas. Sin embargo, la complejidad de las fórmulas introducidas tiene sus ventajas y
desventajas. Las desventajas aparecen cuando usuarios que no saben la lógica de construcción de nuestra
hoja de cálculo hacen cambios en las fórmulas que no corresponden. Por ello, nos interesa que dichos
usuarios sólo puedan cambiar aquellos datos que intervienen en las fórmulas pero no las propias fórmulas
ni el resto de información de la hoja (p.ej,, las etiquetas).

Se pide: Proteger todas las celdas de una hoja, salvo las que, por la función misma de la hoja, deban
cambiar.

4.15.2.1. Procedimiento
1. Abra el archivo correspondiente al Ejercicio 4-5.
2. Seleccione aquellas celdas que contienen cantidades pero no fórmulas. Para seleccionar celdas no
contiguas:
a. Seleccione la primera de las celdas.
b. Manteniendo pulsada la tecla Ctrl seleccione las demás celdas (o rangos de celdas).
3. Vaya a “Inicio | Celdas | Formato | Formato de celdas” y a la ficha Proteger. Si es que la casilla
Bloqueada estuviera activada, desactívela y si no, déjela desactivada y luego pulse Aceptar.
4. Vaya a “Inicio | Celdas | Formato | Proteger hoja”. El cuadro de diálogo que aparece nos da la opción
de proteger las celdas con una contraseña. De momento no introduzca ninguna; sólo pulse Aceptar.
5. Observe cómo a partir de ahora sólo puede cambiar aquellas celdas que contienen las cantidades
cambiantes (las que marcó en el punto 2), pero no las celdas con fórmulas o con texto.
6. A partir de ahora, en los ejercicios sucesivos, acostúmbrese a proteger de la misma forma las celdas
que sean necesarias.

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 154

4.16. ANÁLISIS CON SOLVER

4.16.1. Primera Actividad a Realizar, Análisis con Solver:

Existen muchos problemas complejos de maximización, minimización, etc. normalmente resueltos
con lo que se llama programación lineal. Si bien la herramienta Solver puede servirnos para estos
propósitos, es posible también utilizar esta herramienta para problemas más sencillos.

En resumen, la herramienta Solver es útil para aquellos casos en los que queremos averiguar cómo
deberán variar determinados factores (independientes) para que otro factor, dependiente de aquéllos,
adquiera un determinado valor. Además, podemos establecer que los factores a variar sólo puedan hacerlo
dentro de ciertos límites o restricciones.

La actividad a realizar para explicar este concepto está vinculada con la obtención del beneficio
más óptimo bajo un conjunto de restricciones. Sabemos que los beneficios (utilidad) de una empresa vienen
dados por la diferencia entre sus ingresos y sus costos.

Por tanto:
Beneficio = Ingresos – Costos
y si dividimos los Costos en Fijos y Variables.
Beneficio = Ingresos – (Costos fijos + Costos Variables)
y como los ingresos y costos variables dependen de la Cantidad vendida.
Beneficio = Precio unitario de venta * Cantidad vendida - (Costos fijos + Costo variable unitario *
Cantidad vendida)

Tenemos, por tanto, un factor, el beneficio, que depende de otros factores (todos los de la fórmula
a la derecha del signo =).

Supongamos un caso en el que los valores de dichos factores sean los siguientes:

20Bs=1,25 Bs/Ud * 1.000 Uds―(230 Bs + 1 Bs/Ud * 1.000 Uds).

Nuestro objetivo, es aumentar el beneficio de 20 Bs a 50 Bs sin variar el precio de venta, lo cual exigirá
disminuir los costos. Sin embargo, no podemos bajar los costos de manera indiscriminada; tenemos
ciertas restricciones y es que sabemos que podemos conseguir una reducción del costo fijo como máximo
hasta 200 Bs y una reducción del costo variable unitario como máximo hasta 80 cts (es decir 0,80 Bs).

Deseamos averiguar cuál será, dentro de estas restricciones, la reducción necesaria de costos para
alcanzar la nueva cifra de beneficios.

Resumiendo el Problema:
• Objetivo: Beneficio de Bs 50.
• Restricciones:
o No se puede variar el precio de venta.
o Una reducción del costo fijo como máximo hasta 200 Bs.
o Una reducción del costo variable unitario como máximo hasta 80 cts.

4.16.1.1. Procedimiento
1. Abra un nuevo Libro de Excel y guárdelo con el nombre de Ejercicio 4-13.xls:
• En el rango A1:A5 introduzca los nombres de los factores en juego (Precio unitario de venta, etc.).
• En el rango B1:B4 introduzca los valores de esos factores.
• En B5 introduzca la fórmula necesaria para calcular el beneficio: =B1*B2-(B3+B4*B2).

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 155

2. Una vez introducidos los datos, vaya a “Datos | Análisis | (Solver)” (Si esta opción no aparece en
el menú, deberá habilitarla previamente siguiendo la siguiente secuencia: “Archivo | Opciones |
Complementos | ).
3. En el cuadro de diálogo que aparezca:
• Como Celda objetivo seleccione B5.
• En Valor de escriba 50.
• En Cambiando las celdas de variables, haga clic en el cuadro de texto y luego seleccione las celdas
B3 y B4.
• En Sujeto a las siguientes restricciones pulse el botón Agregar.
• En el siguiente cuadro de diálogo, en Referencia de la celda seleccione B3. En la lista desplegable,
seleccione el signo <=. En Restricción, escriba 230 (el costo fijo no podrá ser superior a 230).
Luego pulse Aceptar.
• Pulse otra vez el botón Agregar. En Referencia de la celda seleccione B3. En la lista desplegable,
el signo >=. En Restricción, 200 (el costo fijo no podrá ser inferior a 200). Pulse Aceptar.
• Pulse Agregar. En Referencia de la celda seleccione B4. En la lista desplegable, el signo <=. En
Restricción, 1. Pulse Aceptar.
• Pulse Agregar. En Referencia de la celda seleccione B4. En la lista desplegable, el signo >=. En
Restricción, 0,8. Pulse Aceptar. A estas alturas, Excel estará mostrando la siguiente pantalla:

4. Pulse el botón Resolver. Aparecerá un cuadro de diálogo en el que se muestra activado el botón de
opción Conservar solución de Solver.
5. Pulse Aceptar y observe los nuevos valores de B3 y B4. Si comprueba el resultado por medio de la
fórmula del Beneficio, verá como son valores correctos.

4.16.2. Segunda Actividad a Realizar: Análisis con Solver, con Escenarios

o con Tablas:

Calcular el valor líquido que se obtiene al descontar una letra de 500 Bs al 15%, durante 36 días, si el
banco nos aplica una comisión del 5 por mil y nos cobra gastos de 2 Bs.

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 156

4.16.2.1. Procedimiento con Solver

 =B1-B1*B2*B3/360-B1*B4/1000-B5

Con Solver se quiere averiguar cómo deben cambiar determinadas variables (en su caso, con
ciertas restricciones) para conseguir un resultado determinado.

1.- El portador de la letra ha encontrado otro banco que le ofrece una tasa de descuento del 10%, por lo
que quiere averiguar con cuántos días de anticipación puede descontar la letra para obtener el mismo
valor líquido que le ofrecía el banco anterior. El resultado sería:

2.- El Banco, por su parte, se propone subir los gastos, aunque no por encima de Bs 2,60,. No obstante,
para el mismo nº de días de descuento (36) quiere ofrecer un valor líquido de 485 Bs. ¿De qué forma
deberá variar la tasa de descuento si no quiere que ésta baje del 15%?). El resultado sería:

4.16.2.2. Procedimiento con Escenarios

Con Escenarios se quiere averiguar cuál será el resultado cambiando determinadas variables
(siempre las mismas), siempre que esas variables sean más de dos.

Se desea saber qué valor líquido se obtendrá en los siguientes supuestos:

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 157

Supuesto 1: Tasa de descuento: 7%. El resultado sería:

Días de descuento: 60.
Comisión: 8 por mil.
Supuesto 2: Tasa de descuento: 9%.
Días de descuento: 60.
Comisión: 6 por mil.
Supuesto 3: Tasa de descuento: 8%.
Días de descuento: 58.
Comisión: 9 por mil.

4.16.2.3. Procedimiento con Tablas

Con Tablas se quiere averiguar cuál será el resultado cambiando un máximo de dos variables. Resulta
más práctico que los escenarios en aquellos casos en que el nº de valores distintos que se quiere dar a las
variables es muy alto.

Se quiere saber el valor líquido que se obtendrá:

1. Con tasas de descuento entre 10% y 20% (en 2. Variando la comisión entre el 5 y el 10 por
intervalos de un punto porcentual) y variando los mil y el gasto entre 2 y 7 Bs.
días de descuento entre 30 y 40 (en intervalos de
2).
El resultado sería:
El resultado sería:

=$B$1-$B$1*$E3*F$2/360-$B$1*$B$4/1000-$B$5 =$B$1-$B$1*$B$2*$B$3/360-
$B$1*$E3/1000-F$2

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 158

4.17. REPASO GENERAL

Un empresario dueño de 4 atracciones de la Feria Internacional de Cochabamba quiere hacer un estudio

sobre la recaudación obtenida a lo largo de una semana. Para ello confecciona en Excel un libro (llamado
“Ejercicio 4-14”) cuya hoja 1 presenta la siguiente estructura y datos:

4.17.1. Primera Actividad a Realizar:

1. Termine de llenar los rangos C3:C8, E3:E8, G3:G8, I3:I8.

2. Con la fórmula SUMA, llene las celdas C9, E9, G9 e I9.
3. Con la fórmula PROMEDIO, llene las celdas B10, D10, F10 y H10.
4. Puesto que el empresario pretende realizar este análisis para las cuatro semanas del mes siguiente,
deberá crearse un escenario para cada semana (invente los datos de las demás semanas que deban
variar).
5. Haga un gráfico de líneas de la evolución del Nº de usuarios de las diferentes atracciones a lo largo de
la semana.
6. Haga un gráfico de columnas, comparando el Nº de usuarios de las diferentes atracciones a lo largo de
la semana.
7. Haga dos gráficos circulares, mostrando la distribución de clientes entre las diferentes atracciones de
los días viernes y sábado.

4.17.2. Segunda Actividad a Realizar:

El empresario quiere averiguar si, con el número medio de personas que utilizan la atracción “Explorer”
cada vez que ésta se pone en marcha, obtiene algún beneficio o sufre pérdidas. Para el cálculo dispone de
los siguientes datos:

Costo de electricidad por minuto de funcionamiento: Bs. 6.

Alquiler del terreno (parte proporcional por minuto de funcionamiento): Bs. 0,06.
Reparaciones y mantenimiento (parte proporcional por minuto de funcionamiento): Bs. 0,07.
Duración de la atracción: 5 minutos.
Nº de puestas en marcha al día: 50.
Promedio de usuarios en cada puesta en marcha: promedio de usuarios por día (1.128) dividido
entre el nº de puestas en marcha.
Precio: 3 Bs.

1. En la hoja 2 del Ejercicio 4-14 calcule por separado el costo y la recaudación y obtenga luego el saldo
en otra celda.
2. El empresario desea saber si puede conseguir un beneficio de 1.500 Bs, reduciendo la duración de la
atracción (con un mínimo de 3 minutos) y el número de funcionamientos diarios (con un mínimo de
40). Utilizando Solver, responda la inquietud del empresario.
3. El empresario también desea averiguar la recaudación que obtendría variando el promedio de usuarios
en cada puesta en marcha (entre 10 y 30, a intervalos de 5) y el precio de la atracción (entre 2 y 4 Bs,
a intervalos de 50 cts.). Utilizando tablas, responda la inquietud del empresario.

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 159

4.17.3. Tercera Actividad a Realizar:

Para completar su estudio, el empresario ha distribuido entre los usuarios de sus atracciones un formulario
en el que figuran las cuatro atracciones y se solicita al usuario que ponga una cruz al lado de cada una cada
vez que la utilice y que, antes de abandonar el recinto ferial, deposite el impreso en un buzón habilitado al
efecto frente a la taquilla del “Explorer”. Al acabar la semana, abre el buzón y encuentra cinco formularios
rellenados. A fin de introducir los datos, en la hoja 3 del libro Ejercicio 4-14 elabore una tabla con la
estructura presentada en la página siguiente, en el rango A1: F21. Asimismo, elabore otra tabla para permitir
el uso de la función BUSCARV (rango H1:K5).

1. En la columna B, haga que el nombre de la atracción aparezca automáticamente al introducir el Nº de

la atracción en la columna A.
2. Al introducir el número de usos (columna C), haga que se calculen, automáticamente, tanto el gasto
como el tiempo invertido en la atracción (columnas D y E): Se trata de utilizar la función BUSCARV
para buscar el precio o la duración de la atracción y, luego, multiplicar el resultado por el número de
usos.
3. Haga que en la columna “SUBIR PRECIO” (F) aparezca “SI” en el caso de que el número de usos
haya sido igual o superior a 5, y nada en otro caso.
4. Mediante las posibilidades que permite una tabla dinámica, analice para cada atracción lo siguiente:
o El número total de veces que ha sido usada.
o El gasto total realizado por los cinco usuarios.
o El tiempo total invertido.

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 160

4.18. FUNCIONES FINANCIERAS EN EXCEL

Presenta las funciones que sirven para resolver problemas en los cuales entre el valor inicial y el valor
final de un negocio existen pagos de cuotas o valores recibidos.

(-) Valor Inicial (-) Valor final

(+) Pago 1 (+) Pago 2……………… (+) Pago n

En todas las funciones de series uniformes suponemos que los valores recibidos o pagados durante el
tiempo del negocio son reinvertidos por el tiempo restante del plazo total, en las mismas condiciones
existentes para la inversión original. Por tanto, se considera que un problema es de series uniformes,
cuando reúne las siguientes condiciones en su totalidad:
a) El monto de los pagos efectuados dentro del tiempo de la inversión es constante
b) La periodicidad de los pagos efectuados dentro del tiempo de la inversión es constante
c) La tasa de interés (de liquidación de los pagos efectuados dentro del tiempo de la inversión) es
constante.

Los argumentos que utilizan las funciones financieras de series uniformes son los siguientes:

• Va Es el valor actual de una serie de pagos futuros iguales. Si este argumento es omitido, se
considerará 0.
• Pago Es el pago efectuado en cada período y que no cambia durante la vida de la anualidad. El
Pago incluye el capital y el interés, pero no incluye ningún otro cargo o impuesto. Este
argumento debe tener signo contrario al de Va, para conservar las condiciones del flujo de caja:
los ingresos van con signo positivo y los egresos con signo negativo.
• Nper Es la cantidad total de períodos en una anualidad, es decir el plazo total del negocio.
• Tasa (i) Es la tasa de interés por período. Tener en cuenta que no es la tasa anual, si no la tasa
nominal del período de pago expresada en términos decimales. Es importante la uniformidad en
el uso de las unidades cuando especifiquemos Tasa y Nper. Así, por ejemplo, si tenemos una tasa
del 1% mensual, los periodos deberán ser expresados en meses.
• Vf Es el valor futuro o el saldo en efectivo que desea lograr después de efectuar el último pago.
Si el argumento Vf es omitido, asumimos que el valor es 0.
• Tipo Es el número 0 ó 1 e indica la forma de pago de la cuota entre al final o al inicio.
o 0 u omitido, si es que los pagos se realizan al final del período (por ejemplo, el 30 de
c/mes)
o 1 si es que los pagos se realizan al inicio del periodo (por ejemplo, el 1ro de c/mes)
• Período. Especifica el número ordinal de la cuota en estudio, que debe encontrarse en el
intervalo comprendido entre 1 y Nper.
• Per_inicial y Per_final Especifican el número ordinal de la primera y la última cuota de un
período para el análisis respectivo de las cuotas pagadas.
• Estimar Es una tasa de interés estimada para que el Excel empiece las iteraciones en el cálculo
de la tasa de interés de una serie uniforme (Ver cálculo del TIR). Si el argumento Estimar es
omitido, suponemos que es 10%.

4.18.1. Función Financiera VF (Valor futuro)

Permite calcular el “Valor futuro (VF)” a partir del “pago” o del “valor Actual (VA)”. También sirve para
calcular el valor de VF indicando si es cuota anticipada (tipo=1) o vencida (tipo=0). Si lo que queremos
calcular es VF a partir de “VA” omitimos el valor de “pago”; si la cuota es vencida, omitimos el valor
tipo.

Devuelve el valor futuro de la inversión, equivalente a los pagos periódicos uniformes a una tasa de
interés constante.

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 161

Sintaxis: VF(tasa;nper;pago;va;tipo)

1 1
El resultado proporcionado por esta función lo obtenemos también con la siguiente fórmula:

EJERCICIO (Aplicación de la función VF)

Si ahorramos 350 Bs mensuales durante 3 años en un banco que paga el 5% de interés anual y deseamos
saber cuánto dinero tendremos ahorrado al final de los 3 años:

Solución:
pago = 350; n = (3*12) = 36; i = (0,05/12); VF = ?
Si lo hacemos manualmente, tenemos:

1 0,05/12 1
350 13.563,67
0,05/12

Si lo hacemos en el Excel, tenemos:

Tres aspectos a considerar en este caso:

• El interés incluido en el argumento “Tasa” debe estar en la misma unidad de tiempo utilizada para el
argumento “Nper”. En este caso, como son cuotas mensuales, la tasa de interés es mensual y por lo
tanto se divide entre doce la tasa anual.
• “VA” puede omitirse como dijimos en el asistente para funciones.
• Si deseamos que las cifras en la hoja de cálculo sean positivas, introducimos el argumento “Pago”
con signo negativo, como apreciamos en el asistente para funciones (-350, en C2).

4.18.2. Función Financiera VA (Valor Actual)

Devuelve el valor actual de la inversión. El valor actual es la suma de una serie de pagos a futuro. Por
ejemplo, cuando pedimos dinero prestado, la cantidad del préstamo es el valor actual para el prestamista.
Permite calcular VA a partir de “pago” o de “VF”. Al igual que la anterior función, se puede calcular
indicando si es “pago” anticipado (tipo=1) o vencido (tipo=0). Para calcular VA a partir de VF, se debe
omitir el valor de “pago”.

Sintaxis: VA(tasa;nper;pago;vf;tipo)
El resultado proporcionado por esta función lo obtenemos también con la siguiente fórmula:

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 162

1 1
1

Ejemplo:
Si ahorramos Bs 350 mensuales durante 3 años en un banco que paga el 18% de interés anual y deseamos
saber cuánto representan estas mensualidades al día de hoy.

Solución:
pago = 350; n = (3*12) = 36; i = (0.18/12); VA = ?
Si lo hacemos manualmente, tenemos:
1 0,05/12 1
11.677,99 350 ! "
0,05
12 1 0,05/12
Si lo hacemos en el Excel, tenemos:

4.18.3. Función Financiera PAGO

Calcula el pago de un préstamo basándose en pagos constantes y con la tasa de interés constante.
Sintaxis
PAGO(tasa;nper;va;vf;tipo)

Sugerencia: Para encontrar la cantidad total pagada durante el período del préstamo, multiplique el valor
devuelto por PAGO por el argumento nper.

El resultado proporcionado por esta función lo obtenemos también con la siguiente fórmula:

1
# $
1 1
EJERCICIO 4 (Aplicación de la función PAGO)
Obtenemos un crédito de Bs10.000 para su pago en 24 cuotas trimestrales iguales, a una tasa de interés
anual de 12% por trimestre vencido. Calcule el pago trimestral que debe hacer.

Solución:
VA = 10.000; n = 24; i = (0,12/4) = 0,03; pago = ?

Si lo hacemos manualmente, tenemos:

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 163

0,03 1 0,03 &'

590,47 10.000 # $
1 0,03 &' 1

Si lo hacemos en el Excel, tenemos:

En algunos casos puede darse la necesidad de requerir tanto del VA como el VF; como en el caso
del leasing, en el cual, además del valor inicial de un equipo tenemos cuotas mensuales iguales y al final
del pago existe la opción de compra para que el usuario adquiera el bien.

Por ejemplo:
En un leasing de Bs 50.000 a 24 meses con la tasa de interés del 1 % mensual y la opción de compra del
12% (es decir 6.000), la función Pago para calcular la cuota mensual a pagar operaría de la siguiente
forma:

Solución:
VA = 50,000; i = 0.01; n = 24; VF = 6.000 pago = ?...La respuesta es de 693,18

4.18.4. Función Financiera TASA

Devuelve la tasa de interés por período de la anualidad. La TASA es calculada por iteración y puede tener
cero o más soluciones. Si los resultados sucesivos de TASA no convergen dentro de 0,0000001 después
de 20 iteraciones, TASA devuelve el valor de error #¡NUM!.
Con esta función es posible calcular la tasa de interés, combinando
• VA con VF
• VA con PAGO
• VF con PAGO
Sintaxis

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 164

TASA(nper;pago;va;vf;tipo;estimar)

Por ejemplo:
VA = 5,000; n = 5; PAGO = 1,250; i = ? (Tenga la precaución de poner signos opuestos a pago y VA)

4.18.5. Función Financiera NPER

Devuelve la cantidad de períodos que debe tener la inversión para que sea equivalente a la serie de pagos
periódicos iguales.
Sintaxis
NPER(tasa, pago, va, vf, tipo)
La unidad de tiempo consignada en la función Nper debe ser la misma que la utilizada en la tasa de
interés.

El resultado proporcionado por esta función lo obtenemos también con las siguientes fórmulas, según los
casos:

,- ,. ,-
)*+ )*+2/3456+*781 )*+2/3456+*7∗180/
( ,.
( / (
)*+ /01 )*+2 8 )*+ /01
/91

Por ejemplo:
i = 0.06; PAGO = 14.000; VA = 93.345,50; n = ? (Recuerde que pago y VA tienen signos opuestos)

4.18.6. Función Financiera VNA o VAN

Calcula el valor actual neto de la inversión a partir de la tasa de descuento y pagos futuros (valores
negativos) e ingresos (valores positivos).
Sintaxis

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 165

VNA(tasa;valor1;valor2; ...)

; ∑FGC
=>?@AB
C0D>E> B
Con la siguiente fórmula:

Los valores incluidos en el flujo de caja no tienen que ser constantes. Esta es la principal diferencia con
la función VA. Sin embargo, todo el flujo de caja se descuenta a la misma tasa (constante) y los valores
incluidos en él ocurren a intervalos iguales.

Dentro del rango del flujo de caja excluimos el valor presente ubicado en el período cero (0) (que además
debe estar con el signo negativo). Este valor, llamado inversión inicial, no ingresa en el rango de valores,
debe ser restado del resultado que arroje la función.

Para interpretar los resultados, debe tenerse en cuenta que la respuesta esta expresada en unidades
monetarias del período cero y su significado puede interpretarse de la siguiente manera:
• VNA > 0, un resultado positivo indica que el negocio estudiado arroja rentabilidad superior a la
exigida por el inversionista, deducida la inversión, luego es conveniente llevar a cabo el negocio.
• VNA = 0, en caso de presentarse, un resultado igual a cero indica que el negocio arroja rentabilidad
igual a la exigida por el inversionista, la ejecución del proyecto es opcional.
• VNA < 0, valor presente neto negativo, indica que la rentabilidad es inferior a la exigida por el
inversionista y para él, particularmente, no es conveniente el negocio. (Por si acaso, un resultado así
no significa que el negocio estudiado arroje pérdidas.)

De lo anterior concluimos cuando anunciemos el VNA de un proyecto debe aclararse cuál fue la tasa de
descuento utilizada para calcularlo, es decir, cuál fue el valor ingresado en el argumento Tasa.

Por ejemplo:
En una inversión de Bs 40.000, el primer año se obtiene un retorno de Bs 8.000, el segundo Bs 9.200, el
tercero Bs 10.000, el cuarto Bs 12.000 y el quinto Bs 14.500, el inversionista desea evaluar si esta
inversión es conveniente al 8% de interés anual.

4.18.7. Función Financiera TIR

Devuelve la tasa interna de retorno (la tasa de rentabilidad) de los flujos de caja representados por los
números del argumento valores. Estos flujos de caja no son constantes, como en las anualidades. Sin
embargo, los flujos de caja deben ocurrir en intervalos regulares, como meses o años. La tasa interna de
retorno equivale a la tasa de interés producida por un proyecto de inversión con pagos (valores negativos)
e ingresos (valores positivos) que ocurren en períodos regulares.
Sintaxis
TIR(valores;estimar)

Para el cálculo de la función TIR incluimos en el rango de valores todo el flujo de caja y es necesario que
existan valores positivos y negativos. El argumento Estimar es opcional. En caso de omitirse, el Excel
asume la tasa inicial del 10%.

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 166

La TIR sólo involucra las condiciones particulares de un proyecto y no está afecta por la subjetividad del
inversionista.
Por ejemplo:
En una inversión de Bs 40.000, el primer año se obtiene un retorno de Bs 8.000, el segundo Bs 9.200, el
tercero Bs 10.000, el cuarto Bs 12.000 y el quinto Bs 14.500
El inversionista desea saber cuál es la tasa de rentabilidad que ofrece esta inversión. (Respuesta=9,63%)

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 167

4.19. HERRAMIENTAS DE DATOS Y PREVISIONES FINANCIERAS

4.19.1. Buscar Objetivo

La herramienta de Buscar Objetivo ayuda a descubrir el valor que se necesita para obtener un resultado
específico. (Se debe tener en cuenta que la funcionalidad de Buscar Objetivo funciona únicamente con un
solo valor de entrada variable).

En el cuadro de diálogo de Buscar Objetivo, marcando la celda que contiene la formula (llamada “Definir
la Celda”), escribir el valor objetivo que se busca (llamada “Con el Valor”), y luego especificar la celda
variable (llamada “Para cambiar la celda”) – Es la celda que varía su valor según el cálculo que realiza
Excel para cumplir con el valor objetivo definido.

Por ejemplo, para calcular el precio mínimo al que se pueden vender 6.500 unidades de un producto y
llegar a un objetivo de ingresos de Bs 85,000.00, se establece una hoja de cálculo con el número de
unidades en una celda, se mantiene la celda de Precio en blanco, y en la celda de los Ingresos se escribe la
fórmula =unidades*precio.

Seleccionar la celda con la fórmula, a continuación, hacer Clic en el Menú “Datos | Herramientas de
Datos | Análisis de hipótesis | Buscar Objetivo”.

En el cuadro de diálogo, Excel rellena la celda seleccionada como Definir la Celda. En el campo Con el
Valor, especificar 85.000 (o cualquier valor objetivo que se quiere alcanzar), y luego seleccione la celda
Precio en el campo Para cambiar la celda.

Cuando se da clic en Aceptar, Buscar Objetivo calcula el valor para la celda cambiante que se requiere
para lograr el Objetivo.

4.19.2. Tabla de Datos

Una tabla de datos es un rango de celdas que se utiliza para mostrar el resultado de cambiar una o dos
variables en una fórmula. Por ejemplo, usted podría utilizar una tabla de datos de una variable para ver el
efecto que tienen diferentes tasas de interés en un pago mensual o ver cómo un cambio en el precio afecta
a su rentabilidad.

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 168

Si desea analizar cómo los cambios en las tasas de interés y la cantidad de un pago inicial afectan los
pagos mensuales –o para probar cómo los cambios en el precio y el costo unitario afectan su rentabilidad
–se utiliza una tabla de datos de dos variables.

Para utilizar una tabla de datos de cualquier tipo, es necesario establecer una hoja de cálculo con fórmulas
de Excel. Por ejemplo, si utilizamos la función PAGO para calcular pagos de préstamos o usamos la
fórmula =ingresos-costos para calcular la rentabilidad debemos proporcionar los valores o referencias
necesarias para los cálculos (Por ejemplo, monto del préstamo, tasa de interés, monto de los ingresos, y
tipos de datos similares).

También es necesario ingresar los valores para la variable (o variables) que desea analizar, –por ejemplo,
para poner a prueba una gama de precios, las diferentes alternativas de precios deberán estar en una sola
fila o columna y las fórmulas en la fila o columna adyacente. Para una tabla de datos de una variable, la
hoja de cálculo podría ser algo como lo siguiente:

Algunos detalles respecto a esta tabla:

• Se trata del cálculo de los ingresos y la ganancia, tal cual se detalla en la columna F. Primero se
calcula el Ingreso (multiplicando la cantidad vendida por el precio actual). Luego se calcula el
costo total (multiplicando la cantidad vendida por el costo unitario). Por último, se calcula la
Ganancia restándole al ingreso el Costo total.
• Nótese que en las celdas B3 y C3 respectivamente han sido copiados los Ingresos de F3 y la
Ganancia de F6. Nótese que también era posible hacer el cálculo de Ingresos y Ganancias
directamente en las celdas B3 y C3.
• En la columna están todos los posibles datos de la variable a analizar (en este caso diferentes
precios)

Cuando la tabla de datos está configurada, se debe seleccionar el rango de celdas para los resultados de la
tabla de datos, incluyendo la fila que contiene las formulas. Es decir se selecciona el rango A3:C12

A continuación, hacer clic en el Menú “Datos | Herramientas de Datos | Análisis de hipótesis | Tabla de
Datos (En el Excel 2016 se hace “Datos | Previsión | Análisis de hipótesis | Tabla de Datos). En el cuadro
de diálogo, en Celda de Entrada (Columna), especificar la celda que contiene los valores a evaluar (en el
ejemplo, la celda Precio F1), y presionar en Aceptar.

Excel genera los diferentes Ingresos y Ganancias para cada uno de los posibles precios:

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019

 169

En la situación anterior, una sola variable era la que cambiaba, es decir los precios. Si queremos hacer
más complejo nuestro análisis, haciendo que la variación sea a partir de dos variable (por ejemplo, el
precio y el costo unitario), debemos hacer uso de una tabla de dos variables. Una tabla de datos de dos
variables incluye la lista de valores para la primera variable en una columna de la tabla (en este caso en
la columna B), y la lista de valores para la segunda variable en una fila de la tabla (en este caso en la fila
2).

A su vez, una tabla de datos de dos variables tiene una celda que contiene la fórmula que Excel necesita
para el cálculo (en este caso la celda B2) mediante el uso de los valores de las 2 variables de la tabla. Se
debe colocar la fórmula en la celda superior izquierda de la tabla (por encima de la variable de la columna
y a la izquierda de la variable de la fila).

Una vez que tenemos la tabla de datos, abrimos el cuadro de diálogo e ingresamos tanto la celda de
entrada de fila (la celda que contiene el valor que se está evaluando mediante el uso de los valores de la
fila superior de la tabla, en este caso J4) y la celda de entrada de la columna (la celda que contiene el
valor que se está evaluando mediante el uso de los valores de la primera columna de la tabla, en este caso
J1). Al hacer clic en Aceptar, Excel rellena la Tabla de Datos a partir de los valores de las 2 variables
proporcionados.

©® Jorge Guevara Espinoza-Cochabamba Bolivia-Año 2019