Excelente software para la conciliación bancaria – EPSA gestión

Quiero dedicar unas líneas a un software de uso para plataformas contables como la que uso en el despacho (Contanet), entre otras, y que me ha sorprendido gratamente.

Se trata de  Epsa43, una aplicación que instalada en el programa contable como plug-in, importa de forma automática extractos bancarios formato Q43 y, tras realizar una serie de parametrizaciones, exporta a contabilidad con muchísima fiabilidad. Tiene en cuenta los movimientos ya importados con lo que podemos realizar esta tarea de forma diaria incluso sin temor a machacar o importar movimientos ya contabilizados.

Pantalla del software con acceso directo desde Contanet

Pantalla del software con acceso directo desde Contanet

Posteriormente en otro blog analizaremos en profundidad este complemento que, reitero, es altamente recomendable y sin duda aligerará las arduas conciliaciones bancarias desde extracto.

Permítanme, eso si, añadir un enlace al mismo, para aquellos que quieran descargarse una demo gratuita

http://www.epsagestion.com/epsa43-conciliacion-bancaria-automatica/?utm_source=portada-blog&utm_medium=banner-blog&utm_campaign=promocion-epsa

Claves para el éxito en la implantación de un Cuadro de Mando Integral (CMI)

La implementación de un Cuadro de Mando Integral (en adelante, CMI), exige una serie de etapas y procedimientos en las Organizaciones que exige un compromiso global, tanto a nivel departamental, como a nivel operativo o  gerencial.

Como breve apunte histórico, comentar que  fueron Robert S. Kaplan y David P. Norton los que han ido desarrollando en diferentes publicaciones de la Harvard Business School esta filosofía,   y que la misma ha evolucionado desde un Sistema de Indicadores mejorado hasta convertirse en un Sistema Global de Gestión de la Información:

Define y transmite la visión estratégica a toda la organización

Comunica y vincula los objetivos organizacionales a las actividades de los departamentos

Vincula los Planes, Metas y Proyectos

Contribuye a la alineación de los empleados con la estrategia y al feedback continuo

Induce al cambio a través del liderazgo

(Fuente: The Strategy-focused organization, Kaplan & Norton, Harvard Business School Press,2000)

captura palo

No voy a cometer la osadía de  resumir la filosofía y claves de la implantación de un CMI en un blog, primero porque es algo inasumible en extensión, y en segundo lugar porque es un servicio que ofrecemos  en Herranz Jurídico Fiscal para nuestros clientes. No obstante quiero aportar mi grano de arena  y comentarles, de modo resumido, en qué puede ayudarles en su negocio un CMI, así como compartir mi visión del mismo.

Les diré primeramente lo que no es:

*No es un documento rígido que se elabore, se presente, y se guarde en un cajón. Se va a adaptando a medida que evoluciona la empresa y sus objetivos. Y necesita un feedback continuo.

*No se trata de un conjunto de indicadores clave (financieros, operativos, etc…) desplegados a modo de ratios en uno o varios Excel, o en una sección con gráficos de su ERP, por mucho SAP o Navision que tenga.

*No es un documento único que se implante en todo tipo de empresas. Al igual que cada empresa o negocio tiene una estrategia diferente, diferente será el CMI de la misma. Y no hay Cuadro de Mando que no necesite para su elaboración de un estudio detallado y pormenorizado de los procesos en su empresa y sus objetivos estratégicos.

*No es (o no debe ser)  un documento elaborado por una única persona o departamento, necesita interacción de toda la Organización. En grandes multinacionales, de hecho, hay una Oficina  de Alineamiento Estratégico, que redirige y centra los objetivos departamentales hacia los objetivos  estratégicos, fin último del CMI. En organizaciones medianas o pequeñas puede ser el Controller o el Departamento  Financiero quien se encargue de su diseño.

*El CMI No está acotado a las grandes empresas: desde Empresas de Reducida Dimensión (ERD) hasta micropymes  o profesionales pueden confeccionar un CMI que les sirva para optimizar sus recursos y generar valor.

product & sales trend

Y ahora le haré una serie de preguntas:

  1. En su empresa o negocio, ¿están definidos los objetivos estratégicos? ¿y están cuantificados?
  2. ¿Existe o se ha planteado realizar alguna vez un Mapa Estratégico? ¿tienen plasmado en papel los flujos de documentos, o un diagrama de procesos en su empresa?
  3. ¿Los reportes que existen a nivel gerencial en su Organización, o los que le llegan a usted si es  un Gerente,  están en concordancia con los Indicadores de Gestión?
  4. Estos indicadores, si existen, ¿están en consonancia con los objetivos? ¿están bien diseñados? ¿son indicadores realmente? ¿tienen asignado un responsable?

Si la mayoría de sus respuestas han sido no, hay mucho camino por recorrer. Es positivo no obstante tomar conciencia de estos aspectos, y ya el sólo hecho de planteárselos será el punto de partida de las mejoras.

No se deje engañar, puede que su empresa vaya bien sin  plantearse estas cuestiones, pero si  implantase los procesos que vamos a analizar en  esta serie de post iría mucho mejor. ¿Usted ve lógico que un deportista de fondo no controle sus tiempos para ver su progresión? ¿Entonces por qué en el caso de las organizaciones no va a ser lo mismo?; En la misma línea, ¿por qué no analizar los procedimientos existentes en la empresa, si probablemente nos encontremos con que  hay diferentes trabajadores con funciones solapadas, o que imputan en sistema la misma información duplicando las tareas? ¿Sabía que en los ERP más modernos el contable no introduce ningún asiento, sino que ya han sido introducidos los datos relevantes para la contabilidad en transacciones previas, y puede dedicar más tiempo así a labores de supervisión y elaboración de reportes?

Lo anterior le habrá hecho replantearse la utilidad de controlar sus objetivos, o de contar con un mapa de procesos… mi objetivo será demostrarle que controlando el resto de variables podremos elaborar un excelente CMI.

blog11003

Próximos post de la serie Implantación de un CMI:

1. CMI, ¿es posible plantearlo sin las herramientas adecuadas?. Los cubos de información.

2. CMI y la estrategia

3. CMI y los indicadores de gestión

4. CMI y su seguimiento

5. CMI y su relación con el presupuesto

Crear una tabla de antigüedad de facturas a cobrar (ageing) en PowerPivot

Vamos a empezar una nueva línea de aprendizaje sobre el Excel PowerPivot.

En este caso diseñaremos una base de datos para llevar el control de las facturas emitidas, cobradas, y pendientes de cobrar en función de la fecha de vencimiento.

Lo que queremos obtener no es ni más ni menos que esto:

1imagendelageing

Para obtener este informe necesitaremos vincular las siguientes tablas: Movimientos bancarios, Facturas y Clientes, que previamente habremos creado en Excel.

 

2

Empecemos creando la tabla de Clientes, importantísimo crear una columna IdCliente donde le asignaremos un identificador numérico no repetitivo a cada uno de nuestros clientes, tal como se ve en la imagen de abajo (hacer click sobre la imagen para agrandar)

Los campos a incluir son muy diversos, en función de la información que queramos obtener:3tabla clientesok

Crearemos otra tabla llamada Facturas, que será la tabla “matriz” del conjunto, y donde situaremos prácticamente la totalidad de las medidas en el PowerPivot.

Importante:

1. Fijaremos un identificador numérico a las facturas, (IdFacturas), un Código de factura (nuestra enumeración, por ejemplo si nuestra serie de facturas empieza por 2013_001 añadiremos 20103001, siempre un código diferente para cada factura.

2. Incluiremos Fecha de emisión y Fecha de vencimiento

3. Añadiremos el identificador del cliente al que hemos girado la factura (IdCliente). Esta columna la relacionaremos posteriormente con la columna de idéntico nombre de la tabla Clientes

4. Importes de la factura, desglose de IGIC o IVA, retenciones, líquido, etc…

4tabla facturasok

Ya por último crearemos la tabla Movimientos Bancarios, donde añadiremos las siguientes columnas:

 

5tabla movimientos bancarios

 

Es importante saber que la columna que se relacionará aquí con la tabla Facturas es la de IDFactura.O sea, la columna IdFactura de la tabla Movimientos Bancarios estará relacionada con la columna CódigoFactura de la tabla Facturas, tal como se ve en la imagen inferior.

4_1 realcion facturas MovBancarios

Esto tiene sentido ya que al introducir los datos del banco cazaremos lo cobrado con el número de factura que cobramos, sin tener que saber que IdFactura es. Esto facilitará la labor.

Si se reciben diferentes cobros de una misma factura añadirlos igualmente, al calcular las medidas se sumarán todos los cobros obtenidos por una factura.

Acto seguido pasamos a vincular las tablas del Excel con PowerPivot, bastará con seleccionar los datos de cada hoja, incluyendo encabezados, y pulsar el botón de PowerPivot, tal como se ilustra debajo:

creartablavinculada

Una vez tengamos las tres tablas creadas, es momento de crear las medidas dentro del mismo Excel (no de la base de datos PowerPivot).

El procedimiento es en una nueva hoja, añadir dentro de la ventana de PowerPivot, “Crear Tabla dinámica”. ¡Ojo! Es dentro del complemento PowerPivot, no desde  Insertar / Tabla Dinámica.

creartabladinamica

Acto seguido vamos a empezar a crear las Medidas, que es la forma que tiene PowerPivot de llamar a unas fórmulas que pueden contener otras y que potencian las posibilidades de este software al integrar nuevas funciones.

Pulsamos “Nueva Medida”

nuevamedida

Y vamos a añadir esta serie de medidas, en las tablas correspondientes:

1. Total Cobrado

6medida totalcobrado

2. Total Facturado

 

7medida totalfacturado

 

3. Pendiente de cobro hasta 30 días (fecha de vencimiento de la factura no pagada hasta hoy, de ahí que utilicemos el comando TODAY) 8 medida pdte 30 dias

 

4. Pendiente de cobro (antigüedad de deuda de 30 a 60 días)

9 medida pendiente 3060 dias

 

Y así sucesivamente hasta llegar hasta los 365 días o con posterioridad.

Vemos a continuación como las medidas se ven reflejadas en las diferentes tablas:

– En Movimientos bancarios 10visual movim bancarios

– En Facturas 11visual movim facturas

 

Ahora sólo nos queda colocar las medidas ya creadas en los campos de valores,  y los campos Razón Social y Código de Factura como filas, dentro de la tabla dinámica. 12 segmentacion

 

Con esto ya obtenemos nuestro ageing

1imagendelageing

Powerpivot en excel:llegó la revolución

Voy a hacer una introducción breve de este revolucionario plug-in de Excel, y lo haré como sigue siendo habitual en mis blogs: de forma visual.

Comentarles que es una sencilla aplicación, que es acoplable al Excel versión 2010 y superiores, versiones 32 y 64 bits.

Al ejecutar este plug-in (en realidad es un complemento “.com” de excel) y ejecutarlo, se nos abre una nueva interfaz que incluye las tablas que se analizarán posteriormente, permitiendo la realización de cientos de operaciones con miles de filas (si, he dicho bien, miles de filas) filtrados en segundos, y permitiendo la ejecución de fórmulas, “medidas” o “measures”, y gráficos con una rapidez increíble. Es la mejor introducción que conozco al llamado “Big Data”.

Para ser más ilustrativo, les voy a ofrecer una serie de pantallazos que analizan una serie de parámetros de una determinada empresa, y que caben, todos ellos, en un único Excel de aproximadamente de ¡¡¡ 6 MB !!! Me gustaría que analizasen al final de este blog el tiempo y las hojas de cálculo que emplearían para obtener los mismos datos con sólo tablas dinámicas.

Agradecer los ejemplos y las enseñanzas a Rob Collie, desarrollador de este software.

Vamos a partir de una determinada empresa, de los datos de venta, y empezaremos a obtener datos:

blogppivot000

Aquí tenemos una tabla dinámica sencilla, de ventas totales por meses.

blogppivot002

Arriba observamos una segmentación de datos por años, de Ventas Totales. Nada espectacular, lo podemos hacer con cualquier Excel 2010.

blogppivot003

Ya esto se complica más: hemos añadido “medidas” de beneficio en u.m. y %, para cada año y mes

blogppivot005

A través de las fórmulas COUNTROW y DISTINCTCOUNT calculamos tanto las transacciones por dia de venta como los días de venta en sí por año

blogppivot007

Calculamos ahora más datos, ventas media por transacción y por día, en los últimos 4 años (ver arriba) blogppivot009

Ahora el % de beneficio que obtenemos por mes (en 2004 sólo hay datos hasta julio, por eso acaba en 7 este listado)blogppivot011

Ya nos introducimos en los productos, calculamos margen y porcentaje sobre el precio de venta de cada Categoría blogppivot014

En el gráfico superior obtenemos un gráfico dinámico con información del margen por categoría blogppivot015

Asignando un código diferente a las distintas operaciones podemos estratificarlas: cuales son ventas normales, en periodo promoción, y cuáles devoluciones. Así como el procentaje de ventas en promoción.

blogppivot016

En este apartado observamos el total de clientes activos en cada año, y el incremento % desde el año 2003 (por eso aparece en negativo los datos de 2002 y 2001) blogppivot018

Aquí analizamos que meses contribuyen más al global de ventas netas. EN 2004 sólo se analizan datos de  enero a julio (por eso llega a 7). Está claro que Junio fue el mejor mes en este 2004.blogppivot020

Ya que hemos calculado la contribución al margen de cada categoría, ¿por qué no hacerlo con cada producto? Arriba aparecen marcados  los productos con código del 228 al 341. Estos productos, durante el 2003 y 2004 contribuyeron en su totalidad con el 1.71% del total de las ventas netas, siendo el mes  de mayo el que mayor porcentaje aportó al total, un 2.81% del total de ventas netas. Intenten calcular estos datos con dinámicas… en esto se tardó un minuto.blogppivot022

Ofrecemos arriba un poco más análisis sobre el periodo de ventas: son Mayo, Junio y Diciembre los meses que concentran nuestras ventas…la información es poder: sabemos cuando  reforzar nuestras plantillas (Mayo, Junio, Diciembre) o para cuando dejar los periodos de vacaciones (Agosto y Septiembre).

blogppivot025

Esto de arriba es un consolidado, por productos, categorías  y subcategorías. Todo en una misma pestaña de Excel. blogppivot027

Un compendio de las medidas utilizadas hasta ahora pero por subcategoría. blogppivot029Datos de ventas por categoría y talla de textil (desde la S a la XL)blogppivot030

Si tenemos bien estructurada nuestra base de datos y recopilamos datos de nuestros consumidores (lugar de procedencia, si son padres o no, con estudios, etc…)podemos descubrir datos interesantes… ya sabemos a quien dirigir nuestras campañas! blogppivot032

En el caso de que las divisas sean una variable importante en nuestras finanzas,¿ por qué no obtener los datos de ventas seleccionando diferentes valores en divisas  para predecir su impacto en nuestras Cuentas de Resultados?

blogppivot034

¿Y por qué no comparamos nuestras ventas por categoría con nuestro presupuesto de ventas estimado? ¿En qué categorías vamos por debajo del presupuesto?blogppivot036

Un poco más de Big Data, en qué días de la semana vendemos más…. Estos americanos venden hasta en domingo.blogppivot038

Ya por último, un acumulado de ventas (Ventas YTD).

En resumen, ¿ es revolucionario o no el PowerPivot?

Análisis de software: Up Planning

Analizamos en este post un software “en la nube” para la realización de previsiones de cash flow, principalmente enfocado a profesionales, pequeñas empresas o comercios que desean tener un control exhaustivo de sus finanzas, previendo el cash flow futuro en función de multitud de variables.

Es posible su descarga gratuita, por un periodo de prueba, en www.upsoluciones.com

Sobra decir que hay muchísimos empresarios que no saben que gastos en comisiones bancarias tienen al final del año, o si el gasto de telefonía se ha duplicado respecto al año anterior, o si simplemente si con el ritmo actual de ventas pueden afrontar con solvencia los salarios o seguros sociales del próximo año. Como se suele decir en nuestro mundillo “lo que no se puede medir no se puede controlar, y lo que no se puede controlar no se puede mejorar”. Es por ello que recomiendo que aquellos que reconozcan ineficiencias en sus sistemas de información empresarial,  (ya sea por falta de tiempo, de personal cualificado, por desconocimiento de software contable o de hojas de cálculo, o por cualquier otro motivo) dediquen unos minutos a leer este análisis.

De antemano querría decirles que es una de las aplicaciones más intuitivas que conozco, y al mismo tiempo más completa. La interface es muy amigable, sencilla de usar, y basta dedicar unos minutos a planificar la entrada de datos para hacerse con él y obtener resultados e informes muy interesantes.

Voy a hacer una presentación donde predomine lo visual, ya que creo que de esta forma se asimilarán mejor los conceptos. Recomiendo encarecidamente, antes de la introducción de datos, coger lápiz y papel para planificar la misma, y tirar de la contabilidad, facturas de proveedores, de datos de ventas pasados, o de los extractos bancarios para acertar en los datos que introducimos: gastos en teléfono, ADSL, consumo eléctrico, alquileres, nóminas, préstamos bancarios, etc. En la planificación previa que menciono debemos tener en cuenta:

  • ¿Puedo dividir mi empresa en diferentes departamentos para su mejor control? Por ejemplo en un Hotel es habitual la división en Alojamiento, Mantenimiento, F&B (Alimentos y Bebidas que se sirven en los bares o restaurantes del Hotel), Comercial, y Administración. O por ejemplo podemos dividir nuestra empresa en diferentes locales en los que se presta la actividad, o por ramo de actividad si somos un grupo de profesionales de la medicina. Puedo hasta incluso asociar un usuario específico a cada departamento para que sea el encargado de gestionar e introducir la información.
Creación de Departamentos

Creación de Departamentos

  • ¿Qué grupos de productos o servicios de los que presto puedo asociar a esos departamentos? Por ejemplo en un Hotel, aparte de la venta de habitaciones, podemos alquilar salones, facturar por llamadas telefónicas, cambio de divisa, excursiones, alquiler de caja de seguridad o venta de bebidas en el bar. Pues bien, crearemos primeramente todos los grupos de productos:
Asignación grupos de productos

Asignación grupos de productos

  • ¿Qué grupos de gastos puedo asociar a mis respectivos departamentos? Por ejemplo puedo tener un grupo de gasto Telecomunicaciones con sus respectivos gastos de ADSL, Télefono fijo, móvil. Y así para el resto de gastos. Sugiero una agrupación similar al Plan General Contable para así poder extraer datos de la contabilidad de forma más eficiente.
Asignación de grupos de gastos

Asignación de grupos de gastos

  • ¿Qué impuestos gravan mis operaciones?  Lo podemos configurar a nuestro antojo, al margen de que después es posible establecer diferentes impuestos a cada producto o gasto específico
Submenú Impuestos

Submenú Impuestos

  • Por tanto ya tenemos configurada, en un primer estadio, nuestra empresa. Quedaría tal y como enlazamos debajo. El ejemplo escogido es para un Hotel de tamaño pequeño.
Pantalla de visión general de la empresa, una vez introducidos los datos

Pantalla de visión general de la empresa, una vez introducidos los datos

Ahora debemos pasar a la definición del resto de las diferentes pestañas con que cuenta el programa:

menu

No vamos a comentar cada una de ellas, pero si nos detendremos en la de definición de productos que creemos más importante a la hora de estimar nuestro cash flow futuro. Comentarles que en el menú Financiación podemos incorporar los datos de préstamos bancarios para calcular la carga financiera y de amortización del capital del mismo:

Menú Financiación

Menú Financiación

En Personal podremos configurar nuestra plantilla, por temporadas y departamentos y definiendo el coste en seguros sociales a cargo de la empresa:

Menú Personal

Menú Personal

Y en la pestaña  cash flow obtendremos una visualización gráfica o en modo tabla de datos del cash flow futuro. Esta pestaña la comentaremos en un último apartado ya que es en la que realmente se obtiene el resultado de todo el trabajo previo y creemos más novedosa y completa de las que hay en el mercado.

  • Ahora vamos a comentar, de forma resumida, la pantalla de introducción de productos, la que creo esencial a la hora de planificar nuestro cash flow. Nos centramos en la pestaña Productos

pestanaproductos

  • Es la hora de crear los productos en sí, asignándolos a los departamentos ya creados:
Creación de productos

Creación de productos

A la hora de crear los productos, debemos asociarlos también a los grupos de productos creados en el paso anterior, y establecer cómo vamos a introducir las ventas, si por unidades vendidas (luego habrá que establecer el coste de cada unidad para determinar el margen) o por cifra de ventas netas. Fíjense como en la imagen superior se ven claramente los campos en los que debemos introducir datos, señalados con un icono de un lápiz, que al hacer click nos aparecen submenús,  y que serán los que en definitiva configurarán nuestras ventas: precio de compra o coste del artículo o servicio (si lo tiene), evolución de estas compras, (tenemos además la opción de establecer periodicidad de compra diferente a la de la venta), precio de venta y valores de venta. Si observan en la imagen vemos:

Submenú de introducción de productos, Precio de venta

Submenú de introducción de productos, Precio de venta

Observemos que tenemos la opción de agregar un escenario normal u optimista (o cualquiera otro) a la hora de establecer nuestros valores de ventas para nuestros productos (posteriormente asignaremos qué valores comprende uno u otro escenario), así como establecer diferentes temporadas o fecha de ventas de nuestros productos (ideal para ventas por temporada),  además de los valores de venta, periodicidad de la venta (diaria, semanal, etc.) o un porcentaje de ganancia sobre el coste.

  • Ahora es el paso de definir los gastos (excepto financiación bancaria y personal, que como comentamos anteriormente se definen en menús diferentes)

pestanagastos

La interfaz es muy similar a la introducción de productos, por lo que seremos breves en la exposición. Nos permite asociar una serie de valores de compra, con una periodicidad determinada (mensual, bimensual, semestral,…) y por periodos de tiempo determinados por nosotros, y asociarles una carga impositiva específica y un escenario.

Submenú de introducción de gastos, Precio de compra

Submenú de introducción de gastos, Precio de compra

  • Ya por último nos centramos en el menú Cash Flow, dónde obtendremos nuestros informes, y como comenté resulta novedosa y muy completa.

pestanacashflow

Podremos establecer tanto el escenario a determinar (optimista, normal), como el Departamento al que hace referencia, o si es a la totalidad de la empresa:

Detalle de Menú Cash Flow, elección por escenario y departamento

Detalle de Menú Cash Flow, elección por escenario y departamento

Podremos establecer el inicio y fin del periodo a analizar, así como  la periodicidad del informe, esto es, si queremos ver la información por semanas, meses, trimestres, etc.:

Detalle de Menú Cash Flow, periodicidad en la visualización de datos

Detalle de Menú Cash Flow, periodicidad en la visualización de datos

Visualización informe Cash Flow completo (hacer click en él para agrandar)

Visualización informe Cash Flow completo (hacer click en él para agrandar)

Y podremos incluso seleccionar un informe más detallado, desglosando no solo por grupos de productos y de costes, sino por productos y costes individuales:

Detalle de Menú Cash Flow, tabla detallada

Detalle de Menú Cash Flow, tabla detallada

Y ver su respectiva presentación gráfica:

Detalle de Menú Cash Flow, informe gráfico

Detalle de Menú Cash Flow, informe gráfico

También existe un menú de detalles de Compras y Ventas, muy útil para complementar la información del Cash Flow:

Detalle de Menú Compras y Ventas

Detalle de Menú Compras y Ventas

A modo de resumen, un excelente software, con posibilidad de pago mensual y periodo de prueba,  que desde Herranz Jurídico Fiscal  nos mostramos predispuestos para ampliarle la información, responder sus dudas y asesorar en la contratación del mismo.

Funciones de excel para la gestión de datos (I)

Vamos a introducir una serie de post destinados a mejorar nuestras habilidades como gestores de bases de datos.

Es frecuente que nos encontremos en nuestro dia a dia bases de datos que tengamos que consultar frecuentemente, lo cuál en ocasiones es un engorro. Incluso para mostrar informes el exceso de datos es negativo para el usuario final.Las tablas dinámicas, filtros, inmovilización de paneles suponen cierto alivio, pero no están al alcance de todos. Vamos a comentar una serie de funciones que nos van a ser útiles:

Sumar.si.conjunto

Empecemos con esta base de datos, sacada de la web del Banco de España, que da información sobre el euribor y tipos de interés.Al final del post incluiremos el excel con los datos.

blogssc1

Como vemos es un listado muy extenso y necesitamos introducir un pequeña pantalla de selección de datos para que sea más sencilla la obtención de datos.

 

blogssc2

Vamos ahora a crear en el mismo excel estos tres campos, que es lo que deseamos obtener: seleccionar el indice a consultar, seleccionar el periodo, y que nos ofrezca el tipo de interés porcentual.

 

blogssc5

Nos situamos en la casilla debajo de Indice. Seleccionamos en Excel el menú Datos / Herramientas de Datos / Validación de datos.

blogssc6

Elegimos como Criterio de validación “Lista”, y seleccionamos el Origen, que en este caso es el rango de columnas que incluye los títulos de cada índice. Podemos copiar y pegar (en este caso trasponer) para aislarlos de la tabla de datos, como hemos hecho debajo, o directamente sobre los títulos. Hacer click para agrandar.

blogssc7

Damos a aceptar y tenemos ya en el Indice un desplegable con los títulos de las columnas de la base de datos.

blogssc8

Vamos ahora a hacer lo mismo con el periodo, en este caso el rango incluirá las filas tipo fecha desde ene-05 a la fecha final del informe, ago-13:

blogssc9

Nos aparece otro desplegable para seleccionar el periodo:

blogssc10

Ahora vamos a poner a prueba lo hecho hasta ahora seleccionando dos valores al azar en cada campo. Vemos que  el periodo fecha nos aparece en formato número (38412):

blogssc11

Ahora  cambiamos su formato a fecha tipo mm-aa.

blogssc12

Nos queda ahora introducir la fórmula para que introducidos diferentes datos en los campos Indice y Periodo nos muestre el resultado.

Seleccionamos la tabla entera:

blogssc13

Ahora vamos al menú Fórmulas / Nombres definidos / Crear desde la selección

blogssc14

Marcamos que de nombre a los rangos de la fila superior, donde se encuentran los títulos de cada índice:

blogssc15

Ya se han creado los diferentes nombres de rango. Lo comprobamos tal como se muestra en la imagen de debajo. Esto es importante para en las fórmulas posteriores sea más sencilla la introducción de datos.

blogssc16

Nos situamos ahora en la casilla correspondiente, donde queremos que se nos muestre el resultado,  e introducimos la fórmula SUMAR.SI.CONJUNTO. Esta fórmula, aunque se utiliza para sumar datos dados diferentes rangos y criterios, también se puede usar para la visualización de datos como en la tabla, ya que una vez el operario introduzca el periodo no hace falta que se realice ninguna suma.

blogssc17

Sólo se desea que al seleccionar un periodo concreto (jun-05 por ejemplo), y un índice (Interés legal por ejemplo), nos ofrezca el dato correspondiente. Por tanto introducimos =SUMAR.SI.CONJUNTO(Interes_legal;PERIODO;J14)

Tengan en cuenta que hemos introducido, en vez de rangos, los nombres de rangos que establecimos en el paso anterior. Creamos nombres de rango con el título de cada fila, con la salvedad que si el título incluye un espacio (como en Interes legal), Excel le asigna como nombre Interes_legal.

Y la fórmula funciona de esta forma: (rango_suma; rango_criterios1; criterio1;…)

blogssc18

Esta fórmula ha buscado dentro del  rango Interés legal el rango de criterio Periodo, y  el criterio J14, que en este caso es jun-05.

Vemos como nos da el resultado correcto, 4.000 % de interés. Si cambiamos el periodo se nos modifica el resultado.

blogssc19

No obstante, si modificamos el índice en su desplegable, seleccionando por ejemplo Interés de demora, no nos actualiza el resultado. Es por ello que debemos introducir una nueva función.

Buscarv

 

Vamos primero a modificar los títulos de las filas de cada índice para hacerlos más sencillos:

blogssc20

Ahora creamos otro cuadro similar, esta vez con fondo azul; nos basta con copiar y pegar desde el que hemos estado trabajando,  y tal como hicimos en el paso anterior nos situamos bajo el campo Indice, introducimos en Excel el menú Datos / Herramientas de Datos / Validación de datos, lista, y seleccionamos los nuevos nombres, más sencillos.

blogssc21

 Y ahora vamos a introducir esta función, BUSCARV: (click para agrandar)

blogssc22

Básicamente esta función nos ofrece, dentro de un rango específico (la totalidad de la base de datos, $A$2:$G$105, siempre hay que dejar fijo este rango con el simbolo $), el valor a buscar, que en este caso se encuentra en la casilla J24 (la fecha seleccionada), siempre y cuando la casilla I24 contenga el  nombre del Indice que queremos buscar (para ello hemos incluido una función SI anidada, SI(I24=”Euribor 12m”), y nos ofrezca el valor que se encuentre dos filas a la derecha del valor a buscar, la fecha, contando como la primera de estas filas la propia de la fecha.

Ya tenemos construido el cuadro, para cualquier fecha que introduzcamos, y seleccionamos el índice que seleccionemos, nos dará el resultado correcto.

blogssc23

 

Descargue el archivo siguiente para descargar el contenido (haga click en el enlace):

HerranzJF gestiondatosI