Optimización de Cálculos Financieros y Visualización de Datos en Excel

Configuración Inicial

En el ejercicio: Ir a Fórmulas – Administrador de Nombres – Borrar todo.
En la hoja de datos: Pinchar el cuadro de la esquina que selecciona todo – Crear desde selección – solo Fila superior – Verificar en Administrador de Nombres que se haya actualizado.

Pregunta 1: Cálculos Financieros y Gráficos

Tabla 1: Montos en Millones ($M)

Total Ingreso Operacional (TIO): +SUMAR.SI(aux; celda_año_arriba; ingresos_venta_total) / 1000
Nota: Donde dice «tabla 1» se cambiará por «MONTOS $M».
Costos de Ventas Operacionales: +SUMA(marcar_todos_los_costos_de_primera_columna_aqui_6)
Costo de Productos Clínicos: Misma función que TIO. Copiar y pegar aquí, cambiando en la fórmula «ingresos por venta total» por «costos producto clínico».
Repetir el proceso, cambiando el penúltimo dato hasta antes del Margen Bruto.
Margen Bruto Operacional: +(celda_Total_Ingreso_Operacional - celda_Costo_Venta_Operacional) de la columna actual.
Gastos Operacionales: +SUMA(marcar_todos_los_gastos_primera_columna)
Gastos de Remuneración Administrativa: Copiar la misma primera función hasta antes del Resultado Operacional, e ir cambiando el penúltimo dato.
Resultado Operacional: +(celda_Margen_Bruto_Operacional - celda_Gastos_Operacionales) de la columna actual.
Intereses: +(celda_Resultado_Operacional * celda_porcentaje_del_lado) de la columna actual.
Margen Antes de Impuestos: +(celda_Resultado_Operacional - celda_Intereses) de la columna actual.
Impuesto: +SI(celda_Margen_Antes_Impuestos > 0; celda_Margen_Antes_Impuestos * celda_porcentaje_del_lado; "0") de la columna actual.
Resultado del Ejercicio: +(celda_Margen_Antes_Impuestos - celda_Impuesto) de la columna actual.
EBITDA: +(celda_Resultado_Operacional - celda_Gastos_Depreciaciones) de la columna actual.
Una vez terminada la primera columna, seleccionar los datos creados y arrastrarlos hacia la derecha para completar las demás columnas.

Gráfico de Datos Financieros

Con la tecla Control presionada, seleccionar los siguientes datos:

Montos ($M) y sus años correspondientes.
Total Ingreso Operacional.
Costos de Ventas Operacionales.
Margen Bruto Operacional.
Gastos Operacionales.

(Todos con sus datos adyacentes). Luego, ir a Insertar -> Gráfico de Columnas 2D (el primero).

Hacer clic en cualquier barra – Cambiar Tipo de Gráfico – Combinado.
- Montos y Gastos: Columnas Apiladas.
- Total: Líneas.
Hacer clic sobre la barra – Seleccionar Datos – Editar – seleccionar los años de la primera fila, excluyendo «Montos $M», solo los años.
Título del Gráfico: «Ingresos Totales, Costo de Venta y Gasto de Administración y Venta».
En el signo ‘+’, activar: Ejes, Títulos del Eje, Título del Gráfico, Etiquetas de Datos, Tabla de Datos, Líneas de Cuadrícula, Leyenda.
En Títulos del Eje: «Montos en Millones» | «Meses».

1.3 Tabla de Variación

Costo de Productos Clínicos: Copiar los datos de los años solicitados desde la Tabla 1 y pegarlos en la columna del año actual. Solo cambiar el segundo dato (después de ‘aux’) por el año actual solicitado.
Variación (2022/2023): +(celda_primer_dato_2023 - celda_mismo_dato_2022) / celda_mismo_dato_2022.
Arrastrar el resultado hacia abajo.
Con los porcentajes seleccionados: ir a Inicio – Formato Condicional – Borrar Reglas; luego, Reglas para Resaltar Celdas – Es Mayor Que: 5% (color rojo oscuro).

Pregunta 2: Análisis por Región y Comuna

2.1 Tabla de Incremento

En este caso, el mayor incremento fue «Costo de Productos Clínicos». Copiar «Costo de Productos Clínicos» y pegarlo en la parte superior de la nueva tabla.

Primera Región y Comuna: +SUMAR.SI.CONJUNTO(costo_productos_clinicos; región; celda_región_solicitada_columna1; comuna; celda_comuna_solicitada_columna2; aux; celda_año_solicitado_fila1)
Arrastrar la fórmula hacia la columna siguiente.
Variación (2022/2023): +(celda_primer_dato_2023 - celda_mismo_dato_2022) / celda_mismo_dato_2022.
Arrastrar la fórmula hacia toda la tabla y destacar en verde las 5 menores en porcentaje.

2.2 Tabla de Datos Geográficos

Ir a la Hoja de Datos: Copiar los datos de Región y Comuna (con sus títulos) y pegarlos en una nueva hoja. Eliminar los títulos haciendo clic en el número ‘1’ de la fila y seleccionando ‘Eliminar’.
Con todos los datos seleccionados: ir a Datos -> Quitar Duplicados para ambas columnas (A y B).
Seleccionar la Columna A – Ordenar – Ampliar Selección. Verificar que no existan espacios.
Seleccionar solo las comunas de Araucanía. En el cuadro de la esquina, escribir «Araucanía» tal cual. Repetir para todas las regiones.
Copiar y pegar la Columna A en la Columna E. Luego, ir a Datos – Quitar Duplicados. Copiar estos datos.
En la Hoja de Ejercicio: Pegar los datos al lado de la mini-tabla.
Hacer clic donde se debe ingresar el dato de Región -> ir a Datos – Validación de Datos – Permitir: Lista (omitir blancos y celda con lista desplegable) – seleccionar los datos recién pegados – Aceptar. Dejar «Araucanía» seleccionada.
Hacer clic donde debe ir el dato de Comuna. Repetir el proceso, pero en Origen: =INDIRECTO(celda_Araucanía_arriba) – Aceptar – dejar cualquier comuna seleccionada.

Ahora, con la tabla…

Costo de Productos Clínicos: =Costo_Producto_Clínicos & " " & celda_región_desplegable & " " & celda_comuna_desplegable.
Luego, +SUMAR.SI.CONJUNTO(Costo_Productos_Clínicos; aux; celda_primer_año_fila_arriba; región; celda_región_desplegable; comuna; celda_comuna_desplegable) / 1000.
Arrastrar la fórmula hacia la derecha.

Gráfico de Costos por Región/Comuna

Seleccionar toda la tabla – Insertar – Líneas 2D (primera opción).
Hacer clic en los números del eje inferior -> Seleccionar Datos – Editar – seleccionar los años de la tabla, solo los años, no la primera columna.

2.3 Tabla de Concatenación y Búsqueda

En la Hoja de Datos: En la columna después de ‘Comuna’, hacer clic derecho – Insertar.

Nombrar la nueva columna como «Aux2».

Primer dato: =celda_región_fila & " " & celda_comuna_fila. Arrastrar hacia abajo.

Ahora, con la tabla en el ejercicio…

Verificar que las regiones y comunas sean las correctas, es decir, las que se destacaron en verde en la PREGUNTA 2, Tabla 2.1 (los porcentajes menores indican mayor reducción en costo o gasto).
Seleccionar la columna «Médicos» y moverla una posición hacia la derecha.
En la columna vacía resultante, el título será: «Concatenado Comuna – Región».
Primer dato a colocar en la columna «Concatenado»: =celda_región_fila & " " & celda_comuna_fila.
Arrastrar hacia abajo.
Primer dato a colocar en la columna «Médico»: =BUSCARV(celda_concatenado_fila; Hoja_Datos!$D$2:$F$9001; 3; FALSO).
Nota: El ‘3’ indica que «Director Médico» se encuentra en la tercera columna del rango seleccionado.

Pregunta 3: Premios y Bonificaciones

3.1 Tabla de Premios

Copiar los nombres de los médicos, región y comuna (sin títulos) de la Tabla 2.3 y pegarlos en la tabla actual. Asignar un color distinto a toda la información (región, comuna, médicos).

ABS(Variación (2022/2023)): =ABS(celda_porcentaje_tabla_destacada_correspondiente_a_región_y_comuna_solicitada).
Repetir el proceso celda por celda hacia abajo.
Premio Eficiencia: =SI(Y(celda_porcentaje_lado > celda_0_Tabla1; celda_porcentaje_lado < celda_5_Tabla1); celda_200000_Tabla1; SI(Y(...); ...; FALSO)).
Nota: Continuar con las condiciones de los demás premios de la Tabla 1. Al final, agregar ;FALSO para cerrar la función SI.
Arrastrar hacia abajo.
Premio Complejidad: =SI(celda_región_fila = celda_dato1_región_Tabla2; celda_dato1_premio_complejidad; SI(celda_región_fila = celda_dato2_región_Tabla2; celda_dato2_premio_complejidad; FALSO)).
Nota: Repetir para todas las condiciones de la Tabla 2, cambiando el dato de la Tabla 2 en cada SI anidado. Al final, agregar ;FALSO).
Total Premio: =celda_premio_eficiencia + celda_premio_complejidad.

4. Cálculo Adicional

=SUMAR.SI.CONJUNTO(ingreso_venta_total; servicios; celda_primer_dato_fila_titulo; aux; celda_año_arriba) / 1000.