Aprendiendo Excel en 1 hora - Haciendo un Flujo de Caja
-
Upload
francisco-villon -
Category
Documents
-
view
117 -
download
1
description
Transcript of 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]
Recomendaciones para la elaboración de EE.FF, Flujo de Caja y Presupuestos en Excel
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
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]
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
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]
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
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]
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
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]
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
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]
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.
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]
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.$
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]
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
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]
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.
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]
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.
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]
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
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]
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
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]
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
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]
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