OFIMÁTICA EMPRESARIAL SEMIPRESENCIAL
OFIMÁTICA EMPRESARIAL I
Competencia
Al nalizar esta Asignatura usted será capaz de “Analizar y construir hojas de cálculo como soluciones a los procesos y tareas habituales en la gesón empresarial y comercial de forma creava e innovadora ofreciendo una presentación de calidad en sus trabajos y en la automazación de cálculos, asumiendo una actud innovadora, responsable y compromeda a trabajar en equipo” equipo”..
OFIMÁTICA EMPRESARIAL
UNIVERSIDAD PRIVADA TELESUP
ÍNDICE DE CONTENIDO I. PREFACIO
03
II. DESARROLLO DE LOS CONTENIDOS Unidad de Aprendizaje 1: EXCEL APLICADO A LA ELABORACION DE PLANILLAS DE SUELDOS Y BOLETA DE PAGOS 1. Introducción a. Presentación y contextualización b. Competencia (logro) c. Capacidades d. Actitudes e. Ideas básicas y contenido 2. Desarrollo de los temas a. b. c. d.
Tema 01: Elaboración de una Planilla de Sueldos Tema 02: Elaboración de Boletas de Pago. Tema 03: Elaboración de cuadros de consolidados y resúmenes de datos. Tema 04: Análisis Estadístico de Datos.
3. Lecturas recomendadas 4. Actividades y ejercicios 5. Autoevaluación 6. Resumen Unidad de Aprendizaje 2: EXCEL APLICADO A LA ELABORACIÓN DE INVENTARIOS 1. Introducción a. Presentación y contextualización b. Competencia (logro) c. Capacidades d. Actitudes e. Ideas básicas y contenido 2. Desarrollo de los temas a. b. c. d.
Tema 01: Control de Stock e Inventario I. Tema 02: Control de Stock e Inventario II. Tema 03: Análisis de Datos. Tema 04: Gráficos para la planificación y la toma de decisiones.
3. Lecturas recomendadas 4. Actividades y ejercicios 5. Autoevaluación 6. Resumen Unidad de Aprendizaje 3: EXCEL APLICADO A LOS CALCULOS FINANCIEROS 1. Introducción a. Presentación y contextualización b. Competencia (logro) c. Capacidades d. Actitudes e. Ideas básicas y contenido 2. Desarrollo de los temas a. b. c. d.
Tema 01: Razones y proporciones directas e inversas. Tema 02: Cálculos financieros I. Tema 03: Cálculos financieros II. Tema 04: Cálculos financieros III.
3. Lecturas recomendadas 4. Actividades y ejercicios 5. Autoevaluación 6. Resumen Unidad de Aprendizaje 4: PROGRAMACION DE MACROS EN EXCEL - VBA 1. Introducción a. Presentación y contextualización b. Competencia (logro) c. Capacidades d. Actitudes e. Ideas básicas y contenido 2. Desarrollo de los temas a. b. c. d.
Tema 01: Introducción a la programación en VBA Tema 02: Manejo de Estructuras Lógicas Condicionales Tema 03: Manejo de Estructuras Lógicas Repetitivas Tema 04: Manejo de formularios
OFIMÁTICA EMPRESARIAL 2
04-100 04 - 35 05 05 05 05 05 05 06 - 31 6 13 19 26 32 32 33 35 36 - 61 37 37 37 37 37 37 38 - 57 38 44 48 54 58 58 58 61 62 - 86 63 63 63 63 63 64 - 81 65 71 75 78 82 82 82 86 91 – 118 92 92 92 92 92 93 – 102 93 96 106 102
Página 2
OFIMÁTICA EMPRESARIAL
UNIVERSIDAD PRIVADA TELESUP
1. PREFACIO
La asignatura es de formación general y de carácter práctico-teórico cuyo propósito general es otorgar al futuro administrador de negocios globales la capacidad del manejo efectivo de la hoja de cálculo de Excel para la automatización de cálculos aplicados en la contabilidad, estadística, finanzas y en la gestión empresarial y comercial, permitiendo la creación de gráficos estadísticos y operaciones con base de datos en Excel.
Los temas a trabajar en la asignatura son: Excel aplicado a la elaboración de Planilla de Sueldos y boleta de pagos, Excel aplicado a la elaboración de Inventarios, Excel aplicado a los cálculos financieros y Programación de Macros en Excel - VBA.
UNIDAD DE APRENDIZAJE I: EXCEL APLICADO A LA ELABORACION DE PLANILLAS DE SUELDOS Y BOLETA DE PAGOS Elaboración de una Planilla de Sueldos
Elaboración de Boletas de Pago.
Elaboración de cuadros de consolidados y resúmenes de datos.
Análisis Estadístico de Datos.
UNIDAD DE APRENDIZAJE II: EXCEL APLICADO A LA ELABORACIÓN DE INVENTARIOS
Control de Stock e Inventario I.
Control de Stock e Inventario II.
Análisis de Datos.
Gráficos para la planificación y la toma de decisiones.
UNIDAD DE APRENDIZA III: EXCEL APLICADO A LOS CALCULOS FINANCIEROS Razones y proporciones directas e inversas.
Cálculos financieros I.
Cálculos financieros I I.
Cálculos financieros II I.
UNIDAD DE APRENDIZAJE IV: PROGRAMACION DE MACROS EN EXCEL - VBA Introducción a la programación en VBA.
OFIMÁTICA EMPRESARIAL
Manejo de Estructuras Lógicas Condicionales.
Manejo de Estructuras Lógicas Repetitivas.
Manejo de formularios.
Página 3 3
UNIDAD DE APRENDIZAJE
#
1
EXCEL APLICADO A LA ELABORACIÓN DE PLANILLAS DE SUELDOS Y BOLETA DE PAGOS Al finalizar esta asignatura usted será capaz de “Construir y plantear fórmulas y funciones para automatizar los cálculos en una planilla de sueldos y aplicar las funciones lógicas para plantear fórmulas con criterios lógicos para decidir entre múltiples alternativas”.
OFIMÁTICA EMPRESARIAL
UNIVERSIDAD PRIVADA TELESUP
1. INTRODUCCIÓN: a) Presentación y contextualización El alumno desarrolla una actitud analítica y crítica que le permita valorar
la
importancia de las funciones básicas para automatizar los cálculos en una planilla de sueldos.
b) Competencias Conoce las funciones básicas para automatizar los cálculos en una planilla de sueldos.
c) Capacidades Construye y plantea fórmulas y funciones para automatizar los cálculos en una planilla de sueldos. Aplica las funciones lógicas para plantear fórmulas con criterios lógicos para decidir entre múltiples alternativas.
d) Actitudes a) Desarrolla una actitud emprendedora mediante la toma de iniciativas. b) Actúa con responsabilidad personal, al cumplir con los horarios establecidos. c) Respeto a las normas de convivencia. d) Cumple con la presentación de los trabajos encomendados de manera individual y en equipo.
e) Presentación de ideas básicas y contenido esenciales de la Unidad La presente Unidad de Aprendizaje, comprende los siguientes temas:
Elaboración de una Planilla de Sueldos. Elaboración de Boletas de Pago. Elaboración de cuadros de consolidados y resúmenes de datos. Análisis Estadístico de Datos.
OFIMÁTICA EMPRESARIAL
Página 5 5
TEMA
#
1
ELABORACIÓN DE UNA PLANILLA DE SUELDOS Al finalizar este tema usted será capaz de “Construir y plantear fórmulas para automatizar los cálculos en una planilla de sueldos”.
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
2. DESARROLLO DE LOS TEMAS
Tema 1: Elaboración de una Planilla de Sueldos
Previo al desarrollo de una Planilla de sueldos, desarrollaremos una introducción al manejo de Hoja de Cálculo Excel y desarrollo de funciones.
1.- INTRODUCCIÓN A LA HOJA DE CÁLCULO
Excel:
Es
una
hoja
electrónica
que
permite
la
automatización de cálculos aplicados en la contabilidad, estadística,
finanza.
Excel brinda a los usuarios principiantes, intermedios y avanzados más confianza para poder desarrollar trabajos importantes, teniendo como apoyo las herramientas y la ayuda inteligente para evitar errores.
Excel trabaja con Datos de la Web o de Base de Datos corporativas, está diseñado para facilitar a un grupo de trabajadores compartir datos con otras personas, conectarse a datos de la Web o dentro de su Organización y analizar esos datos para la toma de decisiones.
Excel proporciona a los usuarios una alternativa más fácil para revisar, comparar y fusionar documentos con otras personas a través del correo electrónico.
OFIMATICA EMPRESARIAL I
Página 8
7
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Excel
proporciona
a
las
organizaciones
las
Herramientas que necesitan para crear soluciones Web. Con el soporte para XML y los componentes Web de Office mejorado.
Iniciar Excel:
Hacemos Clic en Inicio
Seleccionamos Programas Seleccionamos el programa Microsoft Office Hacemos
clic
en
Excel
2007. A continuación aparece la siguiente ventana.
Presentación de la Interfaz de usuario usuario::
Office Excel 2007 ha cambiado de aspecto, tiene una nueva interfaz de usuario (IU) que reemplaza los menús, las barras de herramientas y la mayoría de los paneles de tareas de las versiones anteriores de Excel por un mecanismo sencillo eintuitivo.
OFIMATICA EMPRESARIAL I
8
Página 9
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Los grupos dentro de El diseño de las fichas: fichas: orientado a las
cada ficha
dividen una tarea en subtareas.
tareas.
Los botones de comando de cada grupo ejecutan un comando o muestran un menú de comandos.
FICHAS QUE SE MUESTRAN SÓLO CUANDO SE NECESITAN
Microsoft Office Excel 2007, tiene dos clases de fichas que aparecen sólo cuando cuando son pertinentes para el tipo de tarea que se esté realizando.
Herramientas contextuales: Permiten trabajar con algún objeto seleccionado en la
página, como una tabla, una una imagen o un dibujo. Al hacer clic en uno de estos estos objetos, junto a las fichas estándar estándar aparece el conjunto conjunto pertinente de herramientas contextual conte xtuales es en un color color destaca destacado. do.
OFIMATICA EMPRESARIAL I
Página 10
9
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Siga los siguientes pasos:
Seleccione un elemento del documento.
El
nombre
de
las
contextuales
herramientas aplicables
aparece en un color destacado y las fichas contextuales aparecen junto al conjunto conju nto de fichas están estándar. dar.
Las fichas contextuales contienen controles para trabajar con el elemento seleccionado.
Fichas del programa:
Las fichas del programa reemplazan al conjunto estándar de fichas cuando se cambia a un modo modo de creación o vista determinada, como puede ser la Vista preliminar.
OFIMATICA EMPRESARIAL I
10
Página 10
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
MENÚS, BARRAS DE HERRAMIENTAS Y OTROS ELEMENTOS CONOCIDOS: Los elementos siguientes son parecidos a los menús y las barras de herramientas.
Botón de Microsoft Office
:
botón ubicado en la esquina superior izquierda de la ventana de Excel y abre el menú que se muestra en la figura.
Barra de herramientas de acceso rápido aparece
de forma predeterminada en la parte superior de la ventana de Excel y proporciona acceso rápido
a
herramientas que se utilizan con frecuencia. Puede personalizarla agregándole comandos.
Iniciadores de
cuadros
de diálogo: los
iniciadores de cuadros de diálogo son pequeños iconos que aparecen en ciertos grupos. Al hacer clic en uno de estos iniciadores, se abre un cuadro de diálogo o un panel de tareas relacionado que contiene más opciones relacionadas con dicho grupo.
OFIMATICA EMPRESARIAL II
Página 11
11
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP Presentación de la Hoja de Cálculo:
Cuadro de Nombre: Muestra la celda donde se encuentra el indicador de celdas.
Clic en una celda, y en el cuadro de nombres aparece el Rango (Columna y Filas).
Para cambiar el Nombre, hacemos doble Clic Escribimos el Nombre y Enter.
La Barra de Fórmulas : muestra el contenido real de la celda activa. Si la celda tiene una Fórmula, en la hoja de Cálculo muestra el resultado y en la barra mostrará el resultado de la Función empleada.
OFIMATICA EMPRESARIAL II
12
Página 12
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
La Hoja de Cálculo: es el área donde el usuario trabaja, contiene Columnas y Filas.
La Barra de Hojas: muestra el número de hojas del Libro activo. Cuando se
ingresa a Excel se muestran 3 hojas por defecto, pero se puede insertar un sinnúmero de hojas.
Diferencia entre libros y hojas: Un libro de Excel tiene hojas los cuales forman un único Archivo. Una
hoja de cálculo es parte de un libro y un libro tiene extensión XLS que almacena todas las hojas incluidas en él. La forma de identificar un archivo hecho en Microsoft Excel 2007 es con la extensión .xlsx , en versiones anteriores .xls.
2. INGRESO DE DATOS: EN UNA CELDA SE PODRÁ INTRODUCIR DOS (2) TIPOS DE FORMACIÓN: VALORES Y LITERAL Valores: Es cualquier información que se puede evaluar numéricamente donde se incluyen números,
fechas y fórmulas matemáticas. Las fechas se consideran como valores porque se almacenan como tal, aceptan también algunos símbolos como separador decimal (,). Los signos +, -, ( ) y el porcentaje (%). Cuando se ingresa un valor por defecto se justificará hacia la derecha.
Literal: Es una cadena de caracteres que generalmente se utilizan como texto descriptivo, tales como título de informes, cabecera de columnas; se puede incluir cualquier combinación de letras, números y signos especiales. Cuando se ingresa texto, se justificará hacia la izquierda.
OFIMATICA EMPRESARIAL II
Página 13
13
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Desplazamiento en la Hoja de Cálculo:
CUANDO SE PULSA
EXCEL DESPLAZA EL INDICADOR
Flecha Arriba / Abajo
Fila anterior / siguiente
Flecha Izquierda / derecha
Columna anterior / siguiente
TAB / Shift TAB
Pantalla derecha / izquierda
Re Pág. / Av Pág.
Pantalla arriba / abajo
CTRL + Av Pág.
Página siguiente
CTRL + Re Pág.
Pagina anterior
Shift + Flecha arriba / abajo
Shift + Flecha derecha / izquierda
Se desplaza a la ultima celda con información arriba o abajo Se desplaza a la ultima celda con información derecha o izquierda
F5 Celda
Permite ir a la celda especificada
CTRL + Inicio
Va a la primera celda de su rango
CTRL + Fin
Va a la última celda de su rango
COPIAR, GENERAR SERIES Y GENERAR LISTAS: Pasos para generar una serie: El cuadro de relleno le permitirá a través de un arrastre copiar el
valor o texto y generar correlativamente números y fechas. Para eso se debe apuntar el cuadro de relleno y el puntero del Mouse debe estar en modo de Copiar.
Cuando se
ha seleccionado las
correspondientes,
lleve
el
celdas
puntero
del
Mouse a la intersección de las celdas y vera el puntero del Mouse cambia, y aparece el signo de la suma (+)
OFIMATICA EMPRESARIAL II
14
Página 14
OFIMÁTICA EMPRESARIAL
Para realizar o generar una series de fechas,
digite una fecha, arrastre el cuadro de
relleno y genere genere una lista.
3. Funci Funciones ones Lógica Lógicass Las Funciones lógicas nos permiten "preguntar" sobre el valor de otras y actuar según la respuesta obtenida. Estas funciones cuestionan el valor de una función o celda y realizan una acción en respuesta al valor obtenido.
1. FU FUNC NCIÓ IÓN N COND CONDIC ICIO IONA NAL L SI SI Devuelve un valor si la condición especificada especificada es VERDADERO y otro valor si dicho argumento es FALSO. Sintaxis :
=SI(prueba_lógica;valor_si_verdadero;valor_si_falso)
Prueba_lógica : es cualquier valor o expresión que pueda evaluarse como VERDADERO o FALSO.
Valor_si_verdadero: es el valor que se devuelve si el argumento prueba_lógica es VERDADERO.
Valor_si_falso : es el valor que se devuelve si el argumento prueba_lógica es FALSO.
2. FUNCIÓN Y Devuelve VERDADERO si todos los argumentos se evalúan como VERDADERO; devuelve FALSO FALSO si uno o más argumentos se evalúan evalúan como FALSO.
15
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP Un uso común de la función Y es expandir la utilidad de otras funciones que realizan pruebas lógicas. lógicas. Por ejemplo, ejemplo, la función SI realiza una prueba lógica y, y, luego, devuelve devuelve un valor si la prueba se evalúa como VERDADERO VERDADERO y otro valor si la prueba se evalúa como FALSO. Con la función Y como argumento prueba_lógica de la función SI, puede probar varias condiciones condiciones diferentes diferentes en vez vez de sólo una. una.
Sintaxis:
=Y(valor_lógico1; [valor_lógico2]; ...)
La sintaxis sintaxis de la función función Y tiene los siguientes argumentos:
valor_lógico1 Obligatorio. La primera condición que desea probar se puede evaluar
como VERDADERO o FALSO. valor_lógico2; ... Opcional. Las condiciones adicionales que desea probar se pueden
evaluar como VERDADERO o FALSO, hasta un máximo de 255 condiciones.
3. FUNCIÓN O Devolverá VERDADERO si alguno de los argumentos es VERDADERO; devolverá FALSO si todos los argumentos son FALSO.
Sintaxis:
=O(valor_lógico1;valor_lógico2; ...)
Valor_lógico1, Valor_lógico2, ... son de 1 a 255 condiciones que se desea comprobar y que pueden tener el resultado de VERDADERO o FALSO.
4. FUNC FUNCIÓ IÓN N FALS FALSO O Devuelve el valor lógico FALSO.
OFIMATICA EMPRESARIAL II
16
Página 16
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP Sintaxis:
=FALSO() Observación También puede escribir la palabra FALSO directamente en la hoja de cálculo o en la fórmula y Microsoft Excel la interpreta como el valor lógico FALSO.
5. FUNC FUNCIÓ IÓN N VERD VERDAD ADER ERO O Devuelve el valor lógico VERDADERO. VERDADERO.
Sintaxis:
=VERDADERO()
Observación:
El valor VERDADERO puede especificarse directamente en las celdas y fórmulas sin necesidad de usar esta función. La función VERDADERO se proporciona principalmente por su compatibilidad con otros programas para hojas de cálculo.
6. FUNC FUNCIÓ IÓN N SI.E SI.ERR RROR OR Devuelve un valor que se especifica si una fórmula lo evalúa como un error; de lo contrario, devuelve el resultado de la fórmula. Utilice la función SI.ERROR para interceptar y controlar errores en una fórmula.
Sintaxis
=SI.ERROR(valor,valor_si_error)
Valor
es el argumento en el que se busca un error.
Valor_si_error
es el valor que se devuelve si la fórmula lo evalúa como error. Se
evalúa evalúan n los tipos tipos siguie siguiente ntess de error: error: #N/A, #N/A, #¡VALO #¡VALOR!, R!, #¡REF! #¡REF!,, #¡DIV/ #¡DIV/0!, 0!, #¡NUM! #¡NUM!,, #¿NOMBRE? o #¡NULO!
Observaciones
OFIMATICA EMPRESARIAL II
Página 17
17
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP •
Si valor o valor_si_error están en una celda vacía, SI.ERROR los trata como un valor de cadena vacía ("").
•
Si valor es una fórmula de matriz, SI.ERROR devuelve una matriz de resultados para cada celda del rango especificado en el valor.
Ejemplo: Interceptar errores de división utilizando una fórmula regular. Es más sencillo comprender el ejemplo si se copia en una hoja de cálculo en blanco.
A
B
1 2 3 4
Cuota
Unidades vendidas
210 55
35
5
Fórmula
6
=SI.ERROR(A2/B2;
Comprueba si hay un error en la fórmula en el primer
7
"Error en el cálculo")
argumento (divide 210 por 35), no encuentra ningún error y devuelve los resultados de la fórmula (6).
8
=SI.ERROR(A3/B3;
Comprueba si hay un error en la fórmula en el primer argumento (divide 55 por 0), encuentra un error de división por 0 y devuelve valor_si_error (Error en el
9
"Error en el cálculo")
cálculo).
=SI.ERROR(A4/B4;
Comprueba si hay un error en la fórmula en el primer
"Error en el cálculo")
argumento (divide "" por 23), no encuentra ningún error y devuelve los resultados de la fórmula (0).
10
23
OFIMATICA EMPRESARIAL II
18
0
Descripción (resultado)
Página 18
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP 4. Elaboración de una Planilla de Sueldos En la elaboración de una planilla de sueldos, consideraremos dos partes. La primera considera los parámetros a registrar en la hoja de cálculo. La segunda parte considera el desarrollo de una planilla considerando los parámetros y las funciones de Excel.
1. Definición de parámetros. Consignar los siguientes valores en una hoja de cálculo, respetando la celda donde se encuentre. Por ejemplo la tasa de la ONP, según la figura se consigna en la celda F2.
Nombre de la empresa: Empresa Exportadora "Expo S.A."
Tasa de la ONP: Se obtiene de la página WEB de la ONP.
Comisión AFP: El porcentaje de comisión y prima AFP vigente lo obtenemos de https://www.sbs.gob.pe/app/spp/empleadores/comision_prima.asp
Impuesto de Renta de Quinta Categoría: El cálculo de la renta considera el mes, la proyección de ingresos, y la renta imponible al trabajo considerando el valor de la UIT vigente. Esta información se obtiene en coordinación con la oficina de Contabilidad. En el presente ejemplo se considera porcentaje de descuento mensual vinculado al DNI del personal, valores que la oficina de Contabilidad calcula según lo mencionado.
OFIMATICA EMPRESARIAL II
Página 19
19
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
2. Desarrollo de la planilla de Sueldos. Se definen los siguientes valores, considerando la ubicación de la celda mostrada en la imagen. Periodo de la Planilla:
Datos del Trabajador:
OFIMATICA EMPRESARIAL II
20
Página 20
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP Ingresos del empleado: Considerando: •
El sueldo básico del empleado.
•
Asignación familiar vigente a 85 soles.
•
Formula del Total de Ingresos:
Se logra lo siguiente:
BASE ESSALUD/AFP: El monto a considerar para el cálculo del descuento por AFP y el pago de ESSALUD se calcula con la siguiente formula.
Obteniendo lo siguiente:
OFIMATICA EMPRESARIAL II
Página 21
21
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Total de descuentos:
Primero consignamos el Sistema de aportación al cual se encuentra adscrito el empleado:
Para los empleados que pertenecen a una AFP, se calcula los siguientes descuentos:
OFIMATICA EMPRESARIAL II
22
Página 22
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Para los empleados que pertenecen a la ONP, se calcula el siguiente descuento:
OFIMATICA EMPRESARIAL II
Página 23
23
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Calculamos el impuesto de quinta categoría, considerando los porcentajes definidos por cada empleado:
OFIMATICA EMPRESARIAL II
24
Página 24
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Considerando lo anterior obtenemos el total de descuento:
Total de descuentos: Considerando lo anterior obtenemos el total de descuento:
OFIMATICA EMPRESARIAL II
Página 25
25
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP APORTE DE ESSALUD POR EL EMPLEADOR: Lo obtenemos de la siguiente forma:
Finalmente obtendremos la siguiente planilla de Sueldos:
OFIMATICA EMPRESARIAL II
26
Página 26
TEMA
#
2
ELABORACIÓN DE BOLETAS DE PAGO Al finalizar este tema usted será capaz de “elaborar una boleta de pago consultando una planilla de Sueldos y aplicando las funciones de Excel”.
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Tema 2: Elaboración de Boletas de Pago 1. FUNCIONES DE BÚSQUEDA Son funciones sumamente útiles para buscar datos en listados de información. Este tipo de funciones permiten automatizar, por ejemplo, las tareas que se realizan en planillas de Facturación de ventas; donde el artículo a facturar, su precio y descripción, se extrae de un listado de precios previamente ingresado, simplemente insertando el código del mismo.
Función BUSCAR
Esta función busca un valor en un rango de una columna o una fila o una matriz. Debes indicar el valor a buscar, dónde quieres que busque y de dónde obtendrás el resultado. Sintaxis:
=buscar(valor buscado,matriz)
BUSCARV:
Esta función nos permite buscar un valor en una primera columna de una matriz, una vez localizado nos muestra dentro de la misma fila el valor que contiene la columna que deseamos obtener. Sintaxis: =BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado) Valor_buscado : Valor que se va a buscar en la primera columna de la matriz (matriz:
utilizada para crear fórmulas sencillas que producen varios resultados o que funcionan en un grupo de argumentos que se organizan en filas y columnas. Un rango de matriz comparte una fórmula común; una constante de matriz es un grupo de constantes utilizadas como un argumento) de tabla. Si valor_buscado es inferior al menor de los valores de la primera columna de matriz_buscar_en, BUSCARV devuelve al valor de error #N/A. Matriz_buscar_en : Dos o más columnas de datos. Use una referencia a un rango o un
nombre de rango. Los valores de la primera columna de matriz_buscar_en son los valores que busca valor_buscado. Estos valores pueden ser texto, números o valores lógicos. Las mayúsculas y minúsculas del texto son equivalentes.
Indicador_columnas : Número de columna de matriz_buscar_en desde la cual debe devolverse el
valor coincidente. Si el argumento indicador_columnas es igual a 1, la función devuelve el valor de la primera columna del argumento matriz_buscar_en; si el argumento indicador_columnas es igual a
OFIMATICA EMPRESARIAL II
28
Página 28
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP 2, devuelve el valor de la segunda columna de matriz_buscar_en y así sucesivamente. Si indicador_columnas es: Si es inferior a 1, BUSCARV devuelve al valor de error #VALUE!
Si es superior al número de columnas de matriz_buscar_en, BUSCARV devuelve el valor de error #REF!
Ordenado : Valor lógico que especifica si BUSCARV va a buscar una coincidencia exacta o
aproximada: Si se omite o es VERDADERO, se devolverá una coincidencia exacta o aproximada. Si no localiza ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior a valor_buscado. Los valores de la primera columna de matriz_buscar_en deben estar clasificados según un criterio de ordenación ascendente; en caso contrario, es posible que BUSCARV no devuelva el valor correcto. Si es FALSO, BUSCARV sólo buscará una coincidencia exacta. En este caso, no
es
necesario ordenar los valores de la primera columna de matriz_buscar_en. Si hay dos o
más
valores
en
la
primera
columna de
matriz_buscar_en, se utilizará el primer valor encontrado. Si no se encuentra una coincidencia exacta, se devolverá el valor de error #N/A. Observaciones:
•
Al buscar valores de texto en la primera columna de matriz_buscar_en, asegúrese de que los datos de ésta no tienen espacios al principio ni al final, de que no hay un uso incoherente de las comillas rectas ( ' o " ) ni tipográficas ( ‘ o“), y de que no haya caracteres no imprimibles. En estos casos, BUSCARV puede devolver un valor inesperado o incorrecto..
•
Al buscar valores de fechas o números, asegúrese de que los datos de la primera columna de matriz_buscar_en no se almacenen como valores de texto, ya que, en ese caso, BUSCARV puede devolver un valor incorrecto o inesperado. Si Ordenado es FALSO y valor_buscado es un valor de texto, se pueden utilizar los caracteres comodín de signo de interrogación (?) y asterisco (*) en el argumento valor_buscado. El signo de interrogación corresponde a un solo carácter cualquiera y el asterisco equivale a cualquier secuencia de caracteres. Si lo que desea buscar es un signo de interrogación o un asterisco, escriba una tilde (~) antes del carácter.
OFIMATICA EMPRESARIAL II
Página 29
29
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
BUSCAR H Esta función realiza lo mismo que la función anterior, pero con la diferencia que busca los valores en la primera fila de la matriz de forma horizontal y nos devuelve un valor que está dentro de la misma columna del valor encontrado.
Sintaxis:
=BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas; ordenado)
2. ELABORACIÓN DE BOLETAS DE PAGO En la hoja de cálculo donde se encuentra la planilla desarrollamos el siguiente cuadro considerando el número de fila y columna mostrada en la imagen:
Solo se registrará el DNI del empleado, lo demás se obtiene de la planilla utilizando la función buscarv
Obteniendo el nombre de la planilla.
OFIMATICA EMPRESARIAL II
30
Página 30
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP Fecha de Ingreso:
De la misma forma obtenemos los demás datos:
Los sub totales aplicando la función suma():
OFIMATICA EMPRESARIAL II
Página 31
31
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Finalmente obtenemos la boleta de pago par a el empleado con DNI 09080808, consultando sus datos en la planilla de sueldos:
OFIMATICA EMPRESARIAL II
32
Página 32
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
OFIMATICA EMPRESARIAL II
Página 33
33
TEMA
#
3
ELABORACIÓN DE CUADROS DE CONSOLIDADOS Y RESÚMENES DE DATOS Al finalizar este tema usted será capaz de “elaborar cuadros de consolidados y resúmenes de datos utilizando fórmulas que incluyen datos de diversas hojas”.
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
TEMA 03: Elaboración de cuadros de consolidados y resúmenes de datos Insertamos una nueva hoja de cálculo que denominaremos “Consolidado”. Con los siguientes campos:
Obtenemos los datos de cada periodo de la siguiente forma:
OFIMATICA EMPRESARIAL II
Página 35
35
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
OFIMATICA EMPRESARIAL II
36
Página 36
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
De la misma forma se puede obtener para los siguientes meses:
OFIMATICA EMPRESARIAL II
Página 37
37
TEMA
#
4
ANÁLISIS ESTADÍSTICO DE DATOS Al finalizar este tema usted será capaz de “comprender y utilizar las funciones de análisis estadístico de datos que proporciona el Excel ”.
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
TEMA 04: Análisis Estadístico de Datos 1. FUNCIÓN MÁXIMA La función de MAX devuelve el valor máximo de una lista de valores o argumentos. La función pide los siguientes datos: =MAX (number1, number2,…) Si la matriz A1:A10 lleva el nombre de "Peso" y contiene los números 10, 6, 7, 24, 27, 12, 11, 16, 18 y 9 el valor máximo de los siguientes ejemplos serán:
=MAX(A1:A10) es igual a 27
=MAX(Peso) es igual a 27
2. FUNCIÓN MÍNIMA Devuelve el valor mínimo de un conjunto de valores. =MIN(número1;número2;…) Número1, número2,...son entre 1 y 30 números de los que desea encontrar el valor mínimo.
3. FUNCIÓN CONTAR La función CONTAR cuenta la cantidad de celdas que contienen números y cuenta los números dentro de la lista de argumentos. Use la función CONTAR para obtener la cantidad de entradas en un campo de número de un rango o matriz de números.
Por ejemplo, puede escribir la siguiente fórmula para contar los números en el rango
OFIMATICA EMPRESARIAL II
A1:A20
Página 39
39
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
4. FUNCION MODA Valor que más se repite en un rango =MODA(Números)
5. FUNCIÓN PROMEDIO.SI Devuelve el promedio (media aritmética) de todas las celdas de un rango que cumplen unos criterios determinados
=PROMEDIO.SI (rango;criterio;rango_promedio)
Rango es la celda o las celdas cuyo promedio se desea obtener; deben contener números, o nombres, matrices o referencias que contengannúmeros. Criterio es el criterio en forma de número, expresión, referencia de celda o texto, que determina las celdas cuyo promedio se va a obtener. Por ejemplo, los criterios pueden expresarse como 32, "32", ">32", "manzanas" oB4. Rango_promedio es el conjunto real de celdas de las que se va a obtener el promedio. Si se omite, se utiliza el rango.
OFIMATICA EMPRESARIAL II
40
Página 40
OFIMÁTICA EMPRESARIAL
3. AUTOEVALUACIÓN (Est a Autoevaluación se debe desarrollar en el Campus Virt ual)
1.
¿Qué hace la función BUSCARV?
a) Busca texto que contenga la letra "v". b) Comprueba si el texto contenido en una celda es igual que el de lasiguiente. c) Busca registros relacionados. d) Busca valores en una columna. e) Muestra los valores encontrados sólo en una misma fila.
2.
¿Qué hace la función O?
a) Devuelve VERDADERO si todos sus argumentos son VERDADEROS b) Devuelve FALSO si todos sus argumentos son VERDADEROS. c) Devuelve VERDADERO si algún argumento es VERDADERO. d) Devuelve VERDADERO si todos sus argumentos son FALSOS. e) Devuelve FALSO si algún argumento es VERDADERO.
3.
¿Qué hace la función Y?
a) Devuelve VERDADERO si todos sus argumentos son VERDADEROS b) Devuelve FALSO si todos sus argumentos son VERDADEROS. c) Devuelve VERDADERO si algún argumento es VERDADERO. d) Devuelve VERDADERO si algún argumento es FALSO. e) Devuelve FALSO si ningún argumento es FALSO.
4.
¿Cuál es la función que devuelve la hora exacta en el que utilizamos esta acción?
a) Función hoy b) Función ahora c) Función hora d) Función time e) Función día
5.
Dada la siguiente fórmula: =SI(A1>=18;"Mayor de edad";"Menor de edad"); conociendo que el valor de la celda A1 es 20. ¿Cuál es el resultado de aplicar la fórmula?
a) Menor de edad b) Igual
41
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP c) Mayor de edad d) ERROR e) NULL
6.
¿Qué nos permite realizar las fórmulas de búsqueda?
a) b) c) d) e)
Buscar datos en listados de información Buscar valores sólo en columnas Extraer datos de una fila Buscar sólo datos numéricos Buscar sólo valores de cadena
OFIMATICA EMPRESARIAL II
42
Página 42
UNIDAD DE APRENDIZAJE
#
2
EXCEL APLICADO A LA ELABORACIÓN DE INVENTARIOS Al finalizar esta unidad usted será capaz de “desarrollar un control de inventarios utilizando las funciones de excel”.
OFIMÁTICA EMPRESARIAL
1. INTRODUCCIÓN: a) Presentación y contextualización El alumno desarrolla una actitud analítica y crítica que le permita valorar la importancia en el manejo de archivos y creación de documentos aplicados a la gestión comercial y académica.
b) Competencia “Reconoce
las funciones del Excel aplicables a la el aboración de inventarios . ”
c) Capacidades 1. Desarrolla tablas en Excel para el control de stock de inventarios. 2. Realiza el análisis de datos con las herramientas de excel 3. Desarrolla gráficos para la planificación y la toma de decisiones.
d) Actitudes 1. Desarrolla una actitud emprendedora mediante la toma de iniciativas, promoción de actividades y toma de decisiones en relación a la actividad asignada.
2. Actúa con responsabilidad personal, al cumplir con los horarios establecidos y el respeto a las normas de convivencia.
3. Cumple con la presentación de los trabajos encomendados de manera individual y en equipo, respetando la iniciativa y aportes de l os integrantes.
4. Desarrolla la creatividad, la innovación, la actitud emprendedora y el respeto a la honestidad intelectual.
e) Presentación de ideas básicas y contenido esenciales de la Unidad. La presente Unidad de Aprendizaje, comprende los siguientes temas: Tema 01: Control de Stock e Inventario I. Tema 02: Control de Stock e Inventario II. Tema 03: Análisis de Datos. Tema 04: Gráficos para la planificación y la toma de decisiones.
44
TEMA
#
1
CONTROL DE STOCK E INVENTARIO I Al finalizar este tema usted será capaz de “desarrollar una tabla de stock de inventario”.
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
1. DESARROLLO DE LOS TEMAS:
TEMA 01: Control de Stock e Inventario I
Operaciones de Base de Datos: Crear una Tabla Una tabla en Excel es un conjunto de datos organizados en filas o registros, donde la primera fila contiene las cabeceras de las columnas (los nombres de los campos) y las demás filas contienen los datos almacenados. Es como una tabla de base de datos, por lo que también se denominan listas de base de datos.
Las tablas son muy útiles porque además de almacenar información, incluyen una serie de operaciones que permiten analizar y administrar esos datos de forma muy cómoda. Entre las operaciones más interesantes que podemos realizar con las listas tenemos: •
Ordenar
los
registros,
en
forma ascendente
o
descendente. •
Filtrar el contenido de la tabla por algún criterio.
•
Utilizar fórmulas para la lista añadiendo algún tipo de filtrado.
•
Crear un resumen de los datos. Aplicar formatos a todos los datos.
Las tablas de datos, ya se usaban en versiones anteriores de Excel, pero bajo el término datos.
Listas de
Incluso se encontrará, que en algunos cuadros de diálogo, se refieren a las tablas como
listas
CREAR UNA TABLA
OFIMATICA EMPRESARIAL II
46
Página 46
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP Para crear una lista tenemos que seguir los siguientes pasos:
OFIMATICA EMPRESARIAL II
Página 47
47
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
.
Elaboración de un Inventario de Productos A fin de crear un registro de los productos que se encuentran en el inventario de la empresa, desarrollaremos una tabla realizando los siguientes pasos:
OFIMATICA EMPRESARIAL II
48
Página 48
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Al seguir estos pasos hemos generado la tabla denominada inventario.
OFIMATICA EMPRESARIAL II
Página 49
49
TEMA
#
2
CONTROL DE STOCK E INVENTARIO II Al finalizar este tema usted será capaz de “desarrollar una tabla entrada y una tabla de salida de productos”.
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
TEMA 02: Control de Stock e Inventario II Operaciones con Base de Datos: de Consulta y Filtros de Datos. Autofiltros, Filtros Avanzados, Subtotales
OFIMATICA EMPRESARIAL II
Página 51
51
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
OFIMATICA EMPRESARIAL II
52
Página 52
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
OFIMATICA EMPRESARIAL II
Página 53
53
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
OFIMATICA EMPRESARIAL II
54
Página 54
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP Elaboración de formularios de entrada y de salida de productos
OFIMATICA EMPRESARIAL II
Página 55
55
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
OFIMATICA EMPRESARIAL II
56
Página 56
TEMA
#
3
ANALISIS DE DATOS Al finalizar este tema usted será capaz de “analizar los datos utilizando funciones de excel”.
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
TEMA 03: Análisis de Datos.
Elaboración de Tablas Dinámicas
OFIMATICA EMPRESARIAL II
58
Página 58
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
OFIMATICA EMPRESARIAL II
Página 59
59
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
OFIMATICA EMPRESARIAL II
60
Página 60
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Vinculación de la tabla Inventario con las tablas salidas y entradas. A fin de aplicar el análisis de datos en el inventario, lo primero es que vinculen las tres tablas de la siguiente forma:
OFIMATICA EMPRESARIAL II
Página 61
61
TEMA
#
4
GRÁFICOS PARA LA PLANIFICACIÓN Y LA TOMA DE DECISIONES Al finalizar este tema usted será capaz de “desarrollar Diagrama de spider, grafica del punto de equilibrio, Diagrama de Pareto, Diagrama de Gantt”.
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
TEMA 04: Gráficos para la planificación y la toma de decisiones
Luego de vincular la tabla de inventario a la tabla de salida y entrada de productos, podemos generar los siguientes gráficos:
OFIMATICA EMPRESARIAL II
Página 63
63
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
OFIMATICA EMPRESARIAL II
64
Página 64
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Los gráficos de Diagrama de Pareto y Diagrama de Gantt se aplican a las tablas, en el primer caso considerando la frecuencia de movimiento de un producto y en el segundo caso considerando las fechas de las entradas o salidas de productos.
OFIMATICA EMPRESARIAL II
Página 65
65
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
2. AUTOEVALUACIÓN (Est a Autoevaluación debe desarrollarse en el Campus Virt ual)
1. Qué contiene la “cinta de opciones”? a) Sólo menús y las barras de herramientas. b) El contenido de cuadros de dibujo c) Sólo botones d) Fichas organizadas de acuerdo con escenarios u objetos específicos e) Tareas y subtareas.
2. ¿Cuáles son los tipos de gráficos que en general maneja Excel? a) Barras b) Cónico c) Pirámide d) Cilíndrico e) Anillo
3. ¿Qué es una tabla en Excel? a) Son los datos ubicados en una hoja de Excel. b) Es un conjunto de datos organizados en filas o registros. c) Es una serie de operaciones que permiten analizar y administrar datos. d) Es un resumen de datos. e) Es el administrador de los datos.
4. ¿Qué operación no se puede realizar con las tablas de Excel? a) Crear un resumen de los datos. b) Utilizar fórmulas para la lista añadiendo algún tipo de filtrado. c) Aplicar formatos a todos los datos.
OFIMATICA EMPRESARIAL II
66
Página 66
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP d) Filtrar el contenido de la tabla por algún criterio. e) Filtrar aplicando fórmulas.
5. ¿Cuáles son los tipos de filtros que se pueden realizar en Excel? a) El filtro en tabla y en columna b) El filtro con criterios y filtro avanzado. c) El Autofiltro y filtros avanzados d) Filtro la columna por celda seleccionada. e) Filtro la columna por colores.
6. En Microsoft Excel 2007, la pestaña de diseño muestra dos opciones muy útiles. ¿Cuáles son? a) Seleccionar rango. b) Cambiar los colores de filas y columnas c) Crear nuevo gráfico. d) Modificar datos. e) Cambiar entre filas y Columnas.
7. ¿Para qué tipo de gráfico, la opción Área de trazado estará disponible? a) Gráfico Unidimensional. b) Todo tipo de gráfico. c) Sólo para gráfico de barra. d) Gráfico bidimensional. e) Gráfico con cambios en la posición de ejes.
8. ¿Para qué principalmente se utilizan los filtros? a) Almacenar registros de una tabla. b) Seleccionar registros que correspondan con a lgún criterio fijado por nosotros. c) Realizar operaciones con fórmulas mixtas. d) Realizar gráficos con respecto a los registros seleccionados. e) Diseñar una lista desplegable de datos.
9. ¿Qué se tiene que hacer para establecer criterios de filtro avanzado? a) Reservar una zona en la hoja de calcula para dichas condiciones. b) Crear una lista desplegables de condiciones.
OFIMATICA EMPRESARIAL II
Página 67
67
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP c) Crear condiciones en otra hoja de cálculo. d) Colocarlos en el formulario que proporciona el filtro avanzado. e) Establecer encabezados iguales a la tabla filtrada.
10.¿Qué es el formulario de datos? a) Herramienta utilizada para búsqueda de datos enlistas no muy grandes. b) Cuadro de diálogo que permite al usuario escribir o mostrar con facilidad una fila entera de datos.
c) Cuadro de diálogo que sólo permite agregar nuevos datos a la tabla. d) Herramienta que se posiciona en la lista que esté activa. e) Cuadro de diálogo que permite haces una búsqueda de datos más ágil
OFIMATICA EMPRESARIAL II
68
Página 68
UNIDAD DE APRENDIZAJE
#
3
EXCEL APLICADO A LOS CÁLCULOS FINANCIEROS Construye fórmulas basadas en cálculos de razones y proporciones aplicados a los negocios y la gestión comercial.
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
1. INTRODUCCIÓN: a)Presentación y contextualización Los temas que se tratan en la presente Unidad, tienen por finalidad, que el estudiante conozca y aplique las fórmulas basadas en cálculos de capitalización aplicados a los negocios y la gestión comercial. .
b)Competencia (Logros) Define, planifica y desarrolla trabajos utilizando las fórmulas basadas en cálculos de capitalización aplicados a los negocios y la gestión comercial.
d)Actitudes 1. Disposición emprendedora. 2. Respeto a las normas de convivencia. 3. Sentido de Organización. 4. Perseverancia en las tareas.
e)Presentación de ideas básicas y contenido esenciales de la Unidad. La presente Unidad de Aprendizaje, comprende los siguientes temas: Tema 01: Razones y proporciones directas e inversas. Tema 02: Cálculos financieros I. Tema 03: Cálculos financieros II. Tema 04: Cálculos financieros III.
OFIMATICA EMPRESARIAL II
70
Página 70
TEMA
#
1
RAZONES Y PROPORCIONES DIRECTAS E INVERSAS Al finalizar este tema usted será capaz de “Desarrollar casos prácticos orientado a los negocios y la gestión empresarial”.
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP 2. DESARROLLO DE LOS TEMAS:
TEMA 01: Razones y proporciones directas e inversas Una proporción directa, es cuando aumenta un factor el otro factor relacionado aumenta. A continuación desarrollaremos un ejemplo. Considerando los siguientes datos de costos por almacenamiento de productos, se tiene como dato que se paga 800 soles por el almacenamiento del producto B. Se pide calcular el costo de almacenamiento del producto A y producto C. Para ello tenemos la siguiente tabla:
Agregamos la columna constante
El valor de la constante se debe mantener en todos los cálculos de los costos del producto A y Producto B.
Con ese criterio calculamos el costo del producto A
OFIMATICA EMPRESARIAL II
72
Página 72
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Y luego el costo de almacenamiento del producto C
La lógica inversa se aplica en los casos en que cuenta con mayor maquinaria a fin de reducir los tiempos de entrega, para ello en lugar de dividir deberá multiplicar.
OFIMATICA EMPRESARIAL II
Página 73
73
TEMA
#
2
CÁLCULOS FINANCIEROS I Al finalizar este tema usted será capaz de “Construye fórmulas basadas en cálculos de interés simple aplicados a los negocios y la gestión comercial”.
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Tema 02: Cálculos financieros I. Las funciones financieras de Excel permiten realizar cálculos de amortización, la tasa de interés anual efectiva, el interés acumulado, la tasa nominal entre otros cálculos necesarios en el desarrollo de un modelo financiero
PAGO Calcula el pago de un préstamo basándose en pagos constantes y en una tasa de interés constante. PAGO(tasa, nper, va, vf, tipo)
✓
Tasa
Es el tipo de interés del préstamo.
✓
Nper Es el número total de pagos del préstamo.
✓
Va Es el valor actual, o la cantidad total de una serie de futuros pagos. También se conoce como valor bursátil.
✓
Vf Es el valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si omite el argumento vf, se supone que el valor es 0 (es decir, el valor futuro de un préstamo es 0).
✓
Tipo Opcional. Es el número 0 (cero) o 1 e indica cuándo vencen los pagos. 0 al final del mes 1 al inicio del mes
TASA Devuelve la tasa de interés por período de una anualidad. TASA(nper, pago, va, [vf], [tipo], [estimar]) ✓
Nper
Es el número total de períodos de pago en una anualidad.
✓
Pago Es el pago efectuado en cada período, que no puede variar durante la vida de la anualidad.
✓
Va. Es el valor actual, es decir, el valor total que tiene actualmente una serie de pagos futuros.
✓
Vf. Es el valor futuro o saldo en efectivo que desea lograr después de efectuar el último pago.
✓
Tipo Es el número 0 o 1 e indica cuándo vencen los pagos.
OFIMATICA EMPRESARIAL II
Página 75
75
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP ✓
Estimar Es la estimación de la tasa de interés, si omite el argumento estimar, se supone que es 10 por ciento
NPER Devuelve el número de periodos de una inversión basándose en los pagos periódicos constantes y en la tasa de interés constante. ✓
NPER(tasa, pago, va, [vf], [tipo])
✓
Tasa Es la tasa de interés por período.
✓
Pago Es el pago efectuado en cada período.
✓
Va Es el valor actual o la suma total de una serie de futuros pagos.
✓
Vf Es el valor futuro o saldo en efectivo que desea lograr después de efectuar el último pago.
✓
Tipo Es el número 0 o 1 e indica cuándo vencen los pagos.
OFIMATICA EMPRESARIAL II
76
Página 76
TEMA
#
3
CÁLCULOS FINANCIEROS II Al finalizar este tema usted será capaz de “Construye fórmulas basadas en cálculos de interés compuesto aplicados a los negocios y la gestión comercial”.
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Tema 03: Cálculos financieros II
VA Calcula el valor actual de un préstamo o una inversión a partir de una tasa de interés constante. VA(tasa, nper, pago, [vf], [tipo])
✓
Tasa Es la tasa de interés por período.
✓
Nper Es el número total de períodos de pago en una anualidad.
✓
Pago Es el pago efectuado en cada período.
✓
Vf Es el valor futuro o saldo en efectivo que desea lograr después de efectuar el último pago.
✓
Tipo Es el número 0 o 1 e indica cuándo vencen los pagos.
VF calcula el valor futuro de una inversión a partir de una tasa de interés constante. VF(tasa, nper, pago, [va], [tipo])
✓
Tasa Es la tasa de interés por período.
✓
Nper Es el número total de períodos de pago en una anualidad.
✓
Pago Es el pago efectuado en cada período.
✓
Va Es el valor actual o el importe total de una serie de pagos futuros.
✓
Tipo Es el número 0 o 1 e indica cuándo vencen los pagos.
TASA.NOMINAL Devuelve la tasa de interés nominal anual si se conocen la tasa efectiva y el número de períodos de interés compuesto por año. TASA.NOMINAL(tasa_efectiva, nper_año)
✓
Tasa_efectiva La tasa de interés efectiva.
✓
Nper_año El número de períodos de interés compuesto por año.
INT.EFECTIVO Devuelve la tasa de interés anual efectiva, si se conocen la tasa de interés anual nominal y el número de períodos compuestos por año. INT.EFECTIVO(tasa_nominal, nper_año)
✓
Tasa_nominal Es la tasa de interés nominal.
✓
Nper_año El número de períodos de interés compuesto por año.
OFIMATICA EMPRESARIAL II
78
Página 78
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
PAGOINT Devuelve el interés pagado en un período específico por una inversión basándose en pagos periódicos constantes y en una tasa de interés constante. PAGOINT(tasa, período, nper, va, [vf], [tipo])
✓
Tasa
✓
Período Es el período para el que desea calcular el interés; debe estar comprendido
Es la tasa de interés por período.
entre 1 y el argumento nper. ✓
Nper Es el número total de períodos de pago en una anualidad.
✓
Va Es el valor actual
✓
Vf
✓
Tipo Es el número 0 o 1
Es el valor futuro
PAGOPRIN Devuelve el pago sobre el capital de una inversión durante un
período determinado basándose en pagos periódicos y constantes, y en una tasa de interés constante. PAGOPRIN(tasa, período, nper, va, [vf], [tipo]) ✓
Tasa
✓
Período Es el período para el que desea calcular el interés; debe estar comprendido
Es la tasa de interés por período.
entre 1 y el argumento nper. ✓
Nper Es el número total de períodos de pago en una anualidad.
✓
Va Es el valor actual
✓
Vf
✓
Tipo Es el número 0 o 1
Es el valor futuro
PAGO.INT.ENTRE Devuelve la cantidad de interés pagado de un préstamo entre los argumentos per_inicial y per_final. PAGO.INT.ENTRE(tasa; nper; va; per_inicial; per_final; tipo) ✓
Tasa
✓
Nper Es el número total de períodos de pago en una anualidad.
✓
Va Es el valor actual
✓
Per_inicial
Es la tasa de interés por período.
El primer período del cálculo. Los períodos de pago se numeran
comenzando por 1
OFIMATICA EMPRESARIAL II
Página 79
79
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
✓
Per_final El último período del cálculo.
✓
Tipo Es el número 0 o 1
PAGO.INT.ENTRE
Devuelve la cantidad acumulada de capital pagado de un
préstamo entre los períodos (per_inicial y per_final). PAGO.PRINC.ENTRE(tasa; nper; va; per_inicial; per_final; tipo)
✓
Tasa
✓
Nper Es el número total de períodos de pago en una anualidad.
✓
Va Es el valor actual
✓
Per_inicial
Es la tasa de interés por período.
El primer período del cálculo. Los períodos de pago se numeran
comenzando por 1 ✓
Per_final El último período del cálculo.
✓
Tipo Es el número 0 o 1
OFIMATICA EMPRESARIAL II
80
Página 80
TEMA
#
4
CÁLCULOS FINANCIEROS III Al finalizar este tema usted será capaz de “Construye fórmulas basadas en el uso de funciones financieras aplicados a los negocios y la gestión comercial.”.
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
TEMA 04: Cálculos financieros III A continuación aplicaremos las funciones descritas en la presente unidad. Consideremos el siguiente enunciado:
Un persona planea adquirir un auto y desea gastar 50,000 soles, debe pagar una cuota inicial del 15%. Para ello solicita un préstamo con una Tasa Efectiva mensual de 1.85% para pagarlo en 5 años.
¿Cuánto es la cuota mensual a pagar?
OFIMATICA EMPRESARIAL II
82
Página 82
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Si la cuota fuera 1 399.72 ¿en cuantos meses cancelaría la deuda?
OFIMATICA EMPRESARIAL II
Página 83
83
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
A continuación otro ejercicio de aplicación de las funciones financieras descritas en la presente unidad.
Una persona realiza un préstamo bancario de S/. 80,000.00, para la compra de una camioneta, pero le piden una cuota inicial del 10% y le prestan la diferencia. Le cobran una tasa efectiva anual del 12% para pagarlo en 4 años. ¿Cuánto es la cuota mensual a pagar?
OFIMATICA EMPRESARIAL II
84
Página 84
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
¿Cuánto es el importe de la deuda, después de cancelar la cuota 24?
OFIMATICA EMPRESARIAL II
Página 85
85
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
OFIMATICA EMPRESARIAL II
86
Página 86
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
¿Cuánto es el interés pagado en la cuota 29?
OFIMATICA EMPRESARIAL II
Página 87
87
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
OFIMATICA EMPRESARIAL II
88
Página 88
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
OFIMATICA EMPRESARIAL II
Página 89
89
UNIDAD DE APRENDIZAJE
#
4
PROGRAMACION DE MACROS EN EXCEL - VBA Al finalizar esta Unidad usted será capaz de “construir macros para encapsular una serie de procedimientos”
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
1. INTRODUCCIÓN: a) Presentación y contextualización El alumno desarrolla una actitud analítica que le permita entender los mecanismos y aplicación de la programación en Excel utilizando el lenguaje VBA
b) Competencias Elabora, diseña e implementa Macros en Excel.
c) Actitudes 1. Desarrolla una actitud emprendedora mediante la toma de iniciativas. 2. Actúa con responsabilidad personal, al cumplir con los horarios establecidos. 3. Respeto a las normas de convivencia. 4. Cumple con la presentación de los trabajos encomendados de manera individual y en equipo.
d) Presentación de ideas básicas y contenido esenciales de la Unidad La presente Unidad de Aprendizaje, comprende los siguientes temas: Tema 01: Introducción a la programación en VBA Tema 02: Manejo de Estructuras Lógicas Condicionales Tema 03: Manejo de Estructuras Lógicas Repetitivas Tema 04: Manejo de formularios
OFIMATICA EMPRESARIAL II
Página 91
91
TEMA
#
1
INTRODUCCIÓN A LA PROGRAMACIÓN EN VBA Al finalizar este tema usted será capaz de “construir macros para encapsular una serie de procedimientos.”
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP 2. DESARROLLO DE LOS TEMAS:
Tema 01: Introducción a la programación en VBA Lo primero que se debe realizar a fin de programar una Macro en Excel es habilitar la opción de programador ejecutando los siguientes pasos:
OFIMATICA EMPRESARIAL II
Página 93
93
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Luego desarrollemos un primer código, que considera actualizar una celda al pulsar un botón que ubicaremos en la hoja de cálculo.
OFIMATICA EMPRESARIAL II
94
Página 94
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
OFIMATICA EMPRESARIAL II
Página 95
95
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Probando el código programado:
OFIMATICA EMPRESARIAL II
96
Página 96
TEMA
#
2
MANEJO DE ESTRUCUTRAS LÓGICAS CONDICIONALES Al finalizar este tema usted será capaz de “Construir macros basado en el uso de las estructuras lógicas condicionales”.
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Tema 02: Manejo de Estructuras Lógicas Condicionales Sintaxis If ExpresionCondicional Then
[ Sentencias 1 ] Else [ Sentencias 2 ] End If
Ejemplo:
Sub Potencia x = InputBox("Ingresa un número entre 0 y 500") If x < 10 Then ValorCalc =x^2 Else ValorCalc = x^3 End If MsgBox(ValorCalc) End Sub
OFIMATICA EMPRESARIAL II
98
Página 98
TEMA
#
3
MANEJO DE ESTRUCTURAS LÓGICAS REPETITIVAS. MANEJO DE EVENTOS Al finalizar este tema usted será capaz de “Construir macros basado en el uso de las estructuras lógicas repetitivas”.
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Tema 03: Manejo de Estructuras Lógicas Repetitivas. Manejo de Eventos . Sintaxis FOR VarIndice = ValInit TO ValFInal [ STEP Incr ] [Sentencias] NEXT
Ejemplo: Sub Suma01() Dim I As Variant Dim Suma As Double Suma = 0 For I = 1 To 20 Suma = Suma + I^2 Next MsgBox("La suma de los primeros 20 números es: " & Suma) End Sub
OFIMATICA EMPRESARIAL II
100
Página 100
TEMA
#
4
MANEJO DE FORMLULARIOS Al finalizar este tema usted será capaz de “Construir macros basado en el uso de formularios”.
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Tema 04: Manejo de formularios Un formulario es una ventana o cuadro de diálogo que contiene un conjunto de controles insertados por nosotros desde la barra de herramientas o cuadro de control al cual se le denomina también conjunto de Controles Activex. Se realiza los siguientes pasos para insertar un formulario en la ventana del editor VB:
OFIMATICA EMPRESARIAL II
102
Página 102
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Para ejecutar un formulario se pulsa el botón verte que se encuentra en la cinta de opciones:
OFIMATICA EMPRESARIAL II
Página 103
103
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP
Puede considerar los siguientes elementos:
Botón Etiqueta Cuadro de texto Botón de comando 1 Botón de comando 2
Name
Caption
Lbl01 TxtElemento CmdAceptar CmdFin
Ingrese el nombre del producto y haga clic en
Aceptar Terminar
A fin de poder ejecutar el siguiente código:
OFIMATICA EMPRESARIAL II
104
Página 104
OFIMÁTICA EMPRESARIAL UNIVERSIDAD PRIVADA TELESUP Private Sub CmdAceptar_Click() CboLista.AddItem TxtElemento.Text TxtElemento.Text = "" TxtElemento.SetFocus End Sub Private Sub CmdFin_Click() End End Sub Private Sub UserForm_Click() TxtElemento.SetFocus End Sub
OFIMATICA EMPRESARIAL II
Página 105
105