aprendiendo excel en 1 hora - haciendo un flujo de caja

Recomendaciones para la elaboración de EE.FF, Flujo de Caja y Presupuestos en Excel

Dr. CPC Francisco Villón Bustamante [email protected]

Miguel Checa 458 Piso 3 Sta. Catalina Lima 13 . +(511) 265-4629 Celular +(511)9540-0748 RPM #216567

www.tecnologiadegestion.com

e-mail: [email protected]


1. Conceptos de Excel

Como toda hoja electrónica, se basa en una matriz de celdas organizadas en coordenadas (columnas y filas).

Las celdas muestran datos formateados. O, resultados de fórmulas referenciadas a otras columnas. Hay direcciones absolutas y relativas.

COLUMNAS

FILAS

CELDA: H19






2. Dirección Absoluta y Relativa Dirección Relativa:

B4: 500 B5: 300 B6: =SUMA(B4:B5) Si se mueve la celda B6 siempre seguirá sumando las dos celdas superiores.

Dirección Absoluta:

B10: 500 B11: 300 B12: =SUMA($B$4:$B$5) Si se mueve celda B12 siempre seguirá sumando las celdas B10 y B11.

Nota: Aproveche los asistentes de Excel para formato, gráficos, funciones, son muy buenos.

SE COPIO FORMULA DE B6 SUMA CELDAS E4 Y E5

SE COPIO FORMULA DE B6 SUMA CELDAS B10 Y B11






3. Concepto de Base de Datos Conjunto de registros que tienen la misma organización lógica. Nombre de Campo: denominación arbitraria del elemento lógico. Atributos: característica del campo.

NOMBRE DE LOS CAMPOS, CADA CAMPO ES UNA COLUMNA

CADA FILA ES UN REGISTRO, TIENE LA MISMA ORGANIZACIÓN LOGICA QUE TIENE OTRO REGISTRO

UN CONJUNTO DE REGISTROS FORMAN LA BASE DE DATOS






4. Generando Tablas Dinámicas

4.1. Usando el Menú Datos / Informe de tablas y gráficos dinámicos: Ponga el cursor en la primera celda de una hoja de cálculo con datos

ordenados como se mostró en “3. Concepto de Base de Datos”. Haga clic en el menú Datos / Informe de tablas y gráficos dinámicos, de la

parte superior del Excel. El asistente lo guiará paso a paso. Por defecto se pone en Lista de Base de

Datos de Microsoft Excel y en Tabla Dinámica. Haga clic en siguiente.

OPCION POR DEFECTO EN “Lista de Base de Datos de Microsoft Excel, “ SIGNIFICA QUE LOS DATOS ESTAN EN LA HOJA DE CALCULO.

POR DEFECTO VA A GENERAR CUADRO DE TABLA DINÁMICA

PRIMERA CELDA DEL AREA DE DATOS






4.2. Confirme los datos que va a usar:

Si puso el cursor en la primera celda del área de datos, el asistente

automáticamente marcará toda el área de datos. Es importante que no deje líneas en blanco entre los registros, ni tenga

columnas en blanco entre los campos. El determinar el área de datos es como marcar el área a imprimir.

RANGO QUE EL ASISTENTE ESTA TOMANDO COMO AREA DE DATOS. VERIFÍQUELO DESPLAZÁNDOSE EN LA HOJA. HAGA CLIC EN SIGUIENTE SI EL AREA DE DATOS ES CORRECTA






4.3. Escoja Hoja Nueva:

Siempre escoja en Hoja Nueva, de lo contrario tratará de reemplazar la hoja del

área de datos, o poner la tabla en un costado. En una hoja nueva podrá formatear con libertad el reporte y deshacer la tabla

dinámica con la seguridad de no afectar los datos. Haga clic en finalizar.






4.4. Construyendo el cuadro:

Haga clic y arrastre los campos a filas y columnas como requiera clasificar el

reporte. Puede poner varias filas para clasificar los datos, pero tenga en cuenta la

jerarquía lógica de la clasificación, por ejemplo: primero el año, luego, el mes. Excel genera automáticamente combo box desplegables para que escoja el dato.

En el centro debe poner el dato a ser tabulado, generalmente numérico.

HAGA CLIC SOBRE ESTE TABLERO PARA ARRASTRAR LOS CAMPOS Y USARLOS PARA CLASIFICAR EN FILAS O COLUMNAS

COLUMNAS PARA CLASIFICACION

FILAS PARA CLASIFICACION

AQUÍ ARRASTRE UN CAMPO NUMERO, POR EJEMPLO MONTO U.S.$






4.5. Reporte final:

En nuestro ejemplo, para obtener un reporte de los importes comprados por materia prima, clasificados por año, mes y linea hacemos lo siguiente:

a) Para nuestro ejemplo estamos clasificando un registro de compras que tiene los siguientes campos:

ANNO: año al que corresponde el registro

COMP_ID: Nro. O/C

IT: Item

FECHA: Fecha de la O/C

DESCRIPCION_CORTA: Nombre del Proveedor

ESTADO: Estado

MONEDA_ID: Moneda

PRODUCTO_ID: Código de la Materia Prima

PRODUCTO: Nombre de la Materia Prima

CANTIDAD: Cantidad de la Materia Prima

PRECIO: Precio Unitario de la Materia Prima

IMPORTE_ME: Monto total en US$

LINEA: Línea de la Materia Prima

MES: Mes de la O/C

b) Arrastramos año a la celda A1, la primera posición para clasificar el cuadro.

c) Arrastramos línea a la celda A2. Nótese que este orden sigue una clasificación lógica






PARA ARRASTRAR UN CAMPO HAGA CLIC SOBRE EL NOMBRE DEL CAMPO, Y CON EL BOTON IZQUIERDO DEL MOUSE APRETADO LLEVE EL PROMPT DEL MOUSE A DONDE QUIERE QUE APAREZCA EL CAMPO EN EL REPORTE, LUEGO SUELTE EL BOTON IZQUIERDO.

LOS CAMPOS PARA CLASIFICACIÓN SE PONEN EN ORDEN JERARQUICO UNO DEBAJO DE OTRO EN ESTA AREA. AUTOMÁTICAMENTE APARECERA UN COMBO BOX

AQUÍ SE ESTA ARRASTRANDO EL CAMPO LÍNEA, AL HACER EL ARRASTRE, SE GENERA ESTA LINEA DONDE VA A APARECER EL CAMPO. PARA COMPLETAR LA OPERACIÓN SUELTE EL BOTON IZQUIERDO DEL MOUSE.






d) Arrastre el campo producto al área de campos de fila.

e) Arrastre el campo producto al área de campos de columna.

f) Arrastre el campo importe_me al área de coloque datos aquí.

g) Puede poner más de un campo en el área de filas y de columnas siguiendo un orden lógico, por ejemplo: proveedor, producto, etc.






h) Los datos, se pueden contar, promediar, etc.

i) Puede que excel, interprete que usted quiere solo contar las datos y no sumar el campo importe_me .

j) Para corregir esto, ponga el prompt del mouse sobre el área de datos y haga clic con el botón derecho, aparecerá un menú de propiedades.

k) Haga clic sobre la opción suma y aparece el dato como lo queremos.

l) Este menú de contexto, nos permite formatear el campo a dos decimales, hacer operaciones sobre el campo, quitar subtotales, etc.

HAGA CLIC CON EL BOTON DERECHO DEL MOUSE SOBRE ESTA AREA PARA QUE APAREZCA LAS OPCIONES DE CONFIGURACIÓN DE CAMPO

HAGA CLIC AQUÍ PARA QUE EXCEL MUESTRE LA SUMA DEL CAMPO IMPORTE_ME

EXPLORE LAS OTRAS OPCIONES DE CONFIGURACIÓN DEL CAMPO






m) Use los combo box para filtrar información si es necesaria.

n) Automáticamente excel hace las sumas verticales, horizontales y sub-totales.

o) Si necesita saber como excel agrupo una cifra, haga doble clic sobre el dato, excel automáticamente creara una nueva hoja mostrando los registros que dieron lugar al dato que esta mostrando.

UN CLIC AQUÍ DESPLIEGA LAS OPCIONES DE CLASIFICACION






5. Consejos sobre Tablas Dinámicas: Haga en papel un esquema del reporte que desee obtener. Acomode sus datos en una base de datos en excel que permita generar el

reporte con facilidad, incorporando campos que permitan la clasificación por mes, año, vendedor, etc.

Los datos pueden ser llevados a excel desde cualquier otra aplicación, access, sql, oracle, etc. En estos casos pida al personal de sistemas que genere la base de datos en excel con los campos que Ud. Solicita, haga el reporte Ud. mismo, es una forma práctica de verificar que no haya errores de cálculo en los sistemas de la empresa.

6. Ejemplo de Flujo de Caja con Tablas Dinámicas: Se utiliza una base de datos con los siguientes campos:

o FECHA_DIG: Fecha de digitación del registro. o GRUPO: Ingresos / Egresos. o CONCEPTO: Agrupa las operaciones, por Ventas, Planillas, etc. o DESCRIPCIÓN: Glosa del registro. o MONTO US$: Los ingresos en positivo, los egresos en negativo. o VCTO.: Fecha de vencimiento o de cobro. o SEMANA: Semana para la que se clasifica el movimiento. o PENDIENTE: S/N Permite separar lo pendiente. o FCH_PAGADO: Fecha de pago, se usa para control.

Esta es una muestra de los registros:

FECHA_DIG GRUPO CONCEPTO DESCRIPCION MONTO US$ VCTO. SEMANA PENDIENTE FCH_PAGADO

01/07/2003INGRESOS LETRAS X COBR EMP.XXXX 6594.19 29/08/200325/07 S

01/07/2003EGRESOS CHQ. DIFERIDOS TUXXX -4224.40 10/08/200308/08 S

01/07/2003EGRESOS CHQ. DIFERIDOS RAXXX -9440.00 05/08/200308/08 S

01/07/2003EGRESOS LETRAS X PAGAR CLAUXXXX -266.79 03/08/200301/08 S

01/07/2003EGRESOS CHQ. DIFERIDOS CXXX -1073.05 01/08/200301/08 S

01/07/2003Ingresos FACT. POR COBRAR OXIGENO XXXX 2500.00 01/08/200301/08 S






CONSULTAS:

Dr. CPC Francisco Villón Bustamante Teléfono (511) 265-4629, 99-966-8601

[email protected] www. tecnologiadegestion.com

Lima, 09 de Febrero del 2010.

MIENTRAS TENGA EL PROMPT DENTRO DEL CUADRO DE TABLAS

BARRA DE TABLAS DINAMICAS, SE ARRASTRAN A ESTA POSICIÓN PARA TRABAJAR MEJOR

BOTÓN PARA ACTUALIZAR LOS DATOS

BOTÓN QUE HACE APARECER EL CUADRO CLASIFICADOR DE CAMPOS. SI DESAPARECE VAYA POR EL MENU DATOS / INFORME DE

TABLAS Y GRAFICOS DINAMICOS

aprendiendo excel en 1 hora - haciendo un flujo de caja

Documents