jueves, 21 de junio de 2018

Calculadora Horas Anuales Trabajadas

Seguro te has preguntando muchas veces cuántas horas trabajas al año y si cumples con lo establecido con el convenio que tengas...
Hoy explicaremos como construir una calculadora de horas anuales trabajadas.


Necesitaremos pocas cosas:
1-Tabla con los 14 festivos anuales (lo que tengas en tu convenio reconocidos).
2-Tabla con los 365/366 días del año.
3-Tabla con el número de horas a trabajar por semanas.



Para el ejemplo tomaré los 14 festivos anuales de Madrid para el 2018.
En una Tabla que llamaré: 'TblFestivos'

Calculadora Horas Anuales Trabajadas



Una segunda tabla, que llamaré 'TblTipos', que recogerá la distribución por semanas de las horas trabajadas por cada día (lunes, martes, etc.), según semanas:

Calculadora Horas Anuales Trabajadas



Esta tabla es fundamental para nuestra calculadora.
Por lo que debemos verificar algunos puntos:
1- el primer campo 'Desde' debe estar ordenado en sentido ascendente
2- NO es posible solapar fechas en los distintos periodos definidos (i.e., las fechas deben ser un continuo entre intervalos).


La tercera y última tabla nos determinará día por día, de todo el año, cuantas horas hemos trabajado.
Llamaremos a la tabla 'Tbl_2018'.

Comenzaremos listando la serie de fechas desde el 1/1/2018 hasta el 31/12/2018 en el campo 'Días'.
Este campo no tiene fórmula alguna...

El resto de campos se componen de fórmulas diferentes que aportan algo más de información.

Campo 'Día_semana':
insertamos la fórmula:
=ELEGIR(DIASEM([@Dias];2);"lun";"mar";"mie";"jue";"vie";"sab";"dom")
que nos permite convertir en texto el día de la semana para los 365 días del año.

Este campo se empleará posteriormente para localizar las horas de la semana en la 'TblTipos'.


Campo 'Tipo Jornada':
con la fórmula:
=BUSCARV([@Dias];TblTipos[[#Todo];[Desde]:[Tipo]];3;1)

que nos retornará, de manera informativa, a qué tipo de semana corresponde cada día del año a partir de la 'TblTipos'.


Campo 'Lab/No lab':
con la fórmula:
=SI(O(DIASEM([@Dias];2)>=6;NO(ESERROR(BUSCARV([@Dias];TblFestivos[Festivos];1;0))));"No laborable";"laborable")
con esta fórmula condicional verificamos los dos casos en que un día del año es 'no laborable', o bien que sea sábado o domingo:
DIASEM([@Dias];2)>=6
o bien que esté entre los 14 festivos de la tabla 'TblFestivos'
NO(ESERROR(BUSCARV([@Dias];TblFestivos[Festivos];1;0)))

si se cumple alguna de las dos condiciones el día será 'No laborable', en caso contrario 'laborable'.


Finalmente, el último campo 'Horas', con el que asignaremos las horas determinadas para cada día del año.
Insertamos la fórmula:
=SI([@[Lab/No Lab]]="laborable";SUMAR.SI.CONJUNTO(INDIRECTO("TblTipos["&[@[Día_Semana]]&"]");TblTipos[Desde];"<="&[@Dias];TblTipos[Hasta];">="&[@Dias]);0)

Donde condicionamos que si el día fuera 'laborable' recuperaríamos, según fechas y qué día de la semana fuera, el número de horas correspondiente... en cualquier otro caso devolvemos cero horas trabajadas.

Como se observa para recuperar las horas del día a partir de la 'TblTipos' empleamos la función SUMAR.SI.CONJUNTO, ya que se dan las condiciones necesarias:
1- el dato a recuperar es numérico (se puede 'sumar')
2- NO se solapan fechas en los distintos periodos definidos.

Por otra parte el rango a sumar se genera con la función INDIRECTO, que nos aporta el dinamismo necesario para recuperar el dato de una columna u otra...


Solo nos queda sumar el campo de las 'Horas'.
Así en la celda F1 añadimos:
=SUMA(Tbl_2018[Horas])

dando en mi ejemplo un total de 1.764 horas al año trabajadas..
Si se compara con el convenio de donde se han tomado los datos, que indica que el máximo de horas es de 1.765 horas, vemos que se ajusta perfectamente a un calendario de una persona a jornada completa.

Otro caso sería si tuviéramos jornadas parciales ;-)


Salvo opinión en contra de algún experto laboralista, esta calculadora nos permitirá saber si cumplimos o no con la legislación laboral actual.

Espero te sirva.

martes, 19 de junio de 2018

Excel y la Maquina de Galton

Recientemente, leyendo el blog del maestro Adolfo Aparicio encontré un post sobre la máquina de Galton, donde desarrollaba mediante macros el experimento de Francis Galton.

Me pareció interesante replicar mediante funciones el comportamiento de esta caja de Galton (ver Wikipedia)


La idea es demostrar como un comportamiento aleatorio sobre la posibilidad de tomar dos caminos conduce a una estructura de campana, tendente a la forma de una campana de Gauss o de una distribución normal.



Inicialmente generaremos una estructura de pirámide de 11 niveles, comenzando en la cúspide de esta, celda L4 con la fórmula:
=ALEATORIO.ENTRE(0;1)
esta función nos aporta el componente de azar necesario...

Desde de esa celda L4, siguiendo la pendiente izquierda, celdas:
$K$5,$J$6,$I$7,$H$8,$G$9,$F$10,$E$11,$D$12,$C$13 y $B$14
añadimos la fórmula:
=SI(L4=0;ALEATORIO.ENTRE(0;1);"")
es decir, si la celda anterior fuera 0 generamos un nuevo aleatorio 0-1

Excel y la Maquina de Galton



Desde la celda L4 y siguiendo la pendiente derecha, celda:
$M$5,$N$6,$O$7,$P$8,$Q$9,$R$10,$S$11,$T$12,$U$13 y $V$14
añadimos la fórmula:
=SI(L4=1;ALEATORIO.ENTRE(0;1);"")
es decir, si la celda anterior fuera 1 generamos un nuevo aleatorio 0-1

Excel y la Maquina de Galton



Completamos el interior de nuestra pirámide aleatoria.
En las celdas alternas:
$L$6,
$K$7,$M$7,
$J$8,$L$8,$N$8,
$I$9,$K$9,$M$9,$O$9,
$H$10,$J$10,$L$10,$N$10,$P$10,
$G$11,$I$11,$K$11,$M$11,$O$11,$Q$11,
$F$12,$H$12,$J$12,$L$12,$N$12,$P$12,$R$12,
$E$13,$G$13,$I$13,$K$13,$M$13,$O$13,$Q$13,$S$13,
$D$14,$F$14,$H$14,$J$14,$L$14,$N$14,$P$14,$R$14,$T$14
con la fórmula en L6:
=SI(O(K5=1;M5=0);ALEATORIO.ENTRE(0;1);"")

Excel y la Maquina de Galton



Ahora vamos a convertir los 0 y los 1 resultantes en una bolita roja que simula el camino hasta la base.

Seleccionamos el rango $B$4:$V$14 y aplicamos un formato condicional tipo Conjunto de iconos de tres bolas con la siguiente configuración:
+presionamos el botón de 'Invertir criterio de ordenación'
+marcamos la opción para 'Mostrar el icono únicamente' (no mostrar el valor de la celda)
+cambiar a tipo número ambos tramos y asignarles valoro cero en los dos casos

Excel y la Maquina de Galton



En el siguiente paso obtendremos el dato acumulado de llegadas de las 'bolitas' a cada punto de la base de la pirámide.
Para ello tendremos que cambiar las opciones de Excel respecto al cálculo (a Manual) y las opciones de iteración.
Si accedemos a la ficha Archivo > Opciones de Excel> menú Fórmulas en la primera sección de Opciones de cálculo configuramos:
1-Cálculo de libro: Manual
2-Habilitar cálculo iterativo: marcado
3-Iteraciones máximas: 1

Excel y la Maquina de Galton



Tras aceptar, podremos incorporar la fórmula que nos permite acumular llegadas...
En las celdas B17,D17,F17,H17,J17,L17,N17,P17,R17,T17 y V17 añadimos la fórmula:
=SI(B14<>"";B17+1;B17)
que nos permite autosumar a la cantidad existente esas celdas, las nuevas llegadas de las bolitas a la base...

Excel y la Maquina de Galton



Finalmente seleccionando las celdas anteriores de la fila 17 podemos insertar un gráfico de líneas, con la propiedad de línea suavizada para mejorar su aspecto.

Y estamos listos para comprobar la teoría de Francis Galton... puedes empezar a presionar la tecla de función F9 para recalcular el libro de trabajo.
Comprobaremos que según aumentamos el número de simulaciones, el gráfico toma apariencia de una distribución normal, i.e., concentro en los puntos interiores la mayoría de las llegadas...

En mi ejemplo, con unas 7.000 simulaciones, el aspecto es el siguiente:

Excel y la Maquina de Galton

jueves, 14 de junio de 2018

Modelo de Montecarlo sobre un proyecto de inversión

En la entrada anterior del Blog hablamos de qué era y como operaba el Método de Montecarlo.
Hoy la daremos un uso más financiero, usándolo para analizar un proyecto de inversión...
en el ejemplo supondremos vamos a invertir en un pequeño hotel rural... y vamos a analizar nuestra inversión a cinco años.


Nuestros datos relevantes (y muy simplificados) son los siguientes:

Modelo de Montecarlo sobre un proyecto de inversión



A continuación desplegamos algunos tramos calculados para los cinco años de estudio:
+Ingresos
+Costes Variables
+Costes Fijos
+RDI (Resultados después de impuestos)
+Flujos de efectivos generados


Lanzaremos 10.000 simulaciones para nuestro proyecto (recuerda siempre que a mayor número de simulaciones más certeza con el resultado).

Nuestro primer bloque servirá para calcular los ingresos esperados de los cinco años.
Asi en el rango C16:G10015 añadimos la fórmula:
=$C$8*$C$10*ALEATORIO.ENTRE(45;75)/100

fórmula que contiene el componente de aleatoriedad necesario para el correcto funcionamiento de este modelo de Montecarlo

Modelo de Montecarlo sobre un proyecto de inversión



En el segundo bloque calcularemos los Costes variables que, de acuerdo a nuestras hipótesis serán del 35% de los ingresos.
Así pues, en el rango H16:L10015 añadimos la fórmula:
=C16*$C$6

Modelo de Montecarlo sobre un proyecto de inversión



En el tercer bloque trabajaremos sobre los Costes Fijos.
En el rango M16:Q10015 añadimos la fórmula/vínculo:
=$C$7
que recupera los costes fijos anuales estimados.

Modelo de Montecarlo sobre un proyecto de inversión



Otro bloque nos servirá para obtener el dato calculado del Resultado de la empresa después de impuestos.
En el rango R16:V10015 añadimos:
=(C16-H16-M16)*(1-$C$5)

esto es, (ingresos-costes variables-costes fijos)x(1-tasa Impuesto)

Modelo de Montecarlo sobre un proyecto de inversión



Llegamos al paso final.
Proyectamos los flujos de tesorería esperados para los cinco años, contando el desembolso inicial de la inversión, que según nuestros datos de partida es de 200.000,00 euros.
Los flujos de los cinco años corresponderán a los Resultados (RDI) de esos años.
En el rango W16:W10015 añadimos la fórmula/vínculo:
=-$C$4
A continuación seleccionamos X16:AB10015 y en X16 añadimos
=R16
y presionamos Ctrl+Enter

Modelo de Montecarlo sobre un proyecto de inversión



Solo nos queda el cálculo del Valor Actual Neto (función VNA en Excel).
OJO!! para el cálculo del VAN hay que usar la función VNA de los flujos positivos más la celda del desembolso inicial.
Por tanto en el rango AC16:AC10015 añadimos:
=VNA($C$11;X16:AB16)+W16

Modelo de Montecarlo sobre un proyecto de inversión



Y finalmente para representar el modelo, y obtener un resultado global, aplicamos un PROMEDIO corrido sobre esos VAN calculados...
En AD16:AD1015 añadimos
=PROMEDIO($AC$16:AC16)


Para la muestra gráfica de hacia donde tiende nuestro VAN, para nuestro proyecto, creamos un gráfico de dispersión.
Seleccionamos nuestro numeral de 10.000 celdas (rango B16:B10015) y también seleccionamos este último rango de promedio corrido (rango AD16:AD10015) e insertamos un gráfico de dispersión:

Modelo de Montecarlo sobre un proyecto de inversión



El gráfico muestra la estabilización hacia un VAN del proyecto cercano a los 12.000 euros... lo que indica que sería nuestro VAN más probable, de acuerdo al modelo de Montecarlo.