Manual Excel PAD - 2016.pdf

Ms. Excel Producción y Administración 2016

Contenido 1. Análisis Estadístico de datos: a. Uso de funciones estadísticas: PROMEDIO, MAX, MIN, CONTAR, CONTARA, SUMAR.SI, CONTAR.SI, SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO, FRECUENCIA, ESTIMACION.LINEAL, TENDENCIA, JERARQUIA, PRONOSTICO, REPETIR, ALEATORIO, ALEATORIO.ENTRE

2. Calculo de Planillas de Salarios a. Cálculos de tiempos de trabajo, horas extras y tardanzas b. Uso de funciones condicionales anidadas c. Obtención de boletas de pago 3. Cuadro de Programas de Producción a. Cálculo de tiempos de trabajo de máquinas b. Cuadro de la distribución de cargas de trabajo de las máquinas. Buscar Objetivo c. Gráficas del avance de la producción diaria y acumulada d. Calculo y grafica de la diferencia de la producción programada y ejecutada 4. Análisis de Encuestas para Estudio de Mercado a. Tablas de frecuencias b. Histogramas de frecuencias 5. Análisis de hipótesis: (para cálculos de costos, y de producción) a. Buscar Objetivo b. Tabla de datos c. Administrador de escenarios i. Comparación de múltiples resultados en cuadros de costos ii. Creación de cuadros de resultados para la toma de decisiones 6. Gráficas o Diagramas para la planificación y la toma de decisiones a. Diagrama Spider b. Gráfica del Punto de Equilibrio c. Diagrama de Gantt d. Diagrama de Pareto 7. Análisis de las Ventas a. Cálculo de las ventas mensuales por producto i. Usando la Función SUMAR.SI.CONJUNTO ii. Usando Tablas dinámicas iii. Usando Fórmulas matriciales b. Análisis gráfico i. De los volúmenes de ventas, de las ventas valorizadas, y de la variación mensual de las ventas. ii. Calculo de la posición de ventas con la función JERARQUÍA iii. Uso de gráficos de líneas y Minigráficos para la variación mensual de las ventas 8. Calculo de Pronósticos a. Usando la función ESTIMACION.LINEAL b. Usando la función PRONOSTICO Elaborado por Daniel Zegarra y William Chuquin

Pág. 1


c. Usando la función TENDENCIA d. Usando un gráfico con línea de tendencia e. Ejercicios: Pronostico de la demanda de servicios; de las ventas; de accidentes de trabajo. 9. Simulación de negocios (en producción y comercialización, y empresas de servicio) a. Uso de funciones ALEATORIO y ALEATORIO.ENTRE para la simulación de eventos b. Creación de modelos de simulación para transporte público, con las funciones Aleatorio y Pronóstico, y uso de tabla de datos. 10. Planteamiento y solución de problemas en programación lineal a. Uso de la herramienta Solver para optimizar la función objetivo

Pág. 2

Elaborado por Daniel Zegarra y William Chuquín


Capítulo 1 1. Usar Funciones avanzadas para calcular valores Conociendo ya las funciones básicas de Excel, como son: Suma, Promedio, Max, Min, Contar, Contara, Entero, Redondear, Si, Y, O, Buscarv, Buscarh, Subtotales, Indirecto. Aquí una explicación breve de otras funciones más avanzadas avanzadas como: Sumar.si, Contar.si, K.esimo.mayor, K.esimo.menor, Redondear.mas, Redondear.menos, Aleatorio, Esblanco, Esnumero, Estexto, Eserror, BdMax, Hoy, Ahora.

Función SUMAR.SI Suma selectivamente las celdas que coinciden con el argumento criterio. Sintaxis SUMAR.SI(rango,criterio,rango SUMAR.SI(rango,criterio,ran go a sumar) Dónde: Es el rango de celdas que se desea evaluar. Rango Es el criterio en forma de número, expresión o texto, que Criterio determina qué celdas se van a sumar. Por ejemplo, el argumento criterio puede expresarse como 32; "32"; ">32"; "manzanas"; D4. Rango a sumar Son las celdas que se van a sumar. Las celdas contenidas en Rango a sumar se suman sólo si las celdas correspondientes del Rango coinciden con el Criterio. Si Rango a sumar se omite, se suman las celdas contenidas en el argumento Rango.

E jerci cio ci o Nº 1: C álculos c ondiciona ondici onalles en bienes bienes i nmuebl nmuebles es En una hoja de cálculo en las columnas A, B y C se tiene una relación de las valorizaciones de varios bienes inmuebles pertenecientes a tres personas. Al lado derecho se quiere calcular a cuánto ascienden los bienes acumulados de cada propietario. Para calcular la valorización acumulada de las casas de cada propietario haga usted lo siguiente: 1.- En F4 escribir la fórmula: fórmula: =SUMAR.SI(A4:A10,E4,C4:C10) 2.- Luego copie esta fórmula en F5 y F6. F6. 3.- En F7 use Autosuma para para sumar las tres celdas. Elaborado por Daniel Zegarra y William Chuquin

Pág. 3


Si también se desea calcular la suma de las valorizaciones que son inferiores a 100,000 soles, entonces: 4.- La fórmula en F11 sería: =SUMAR.SI(C4:C10,"<100000") Note que se ha omitido el Rango a sumar. 1 2 3 4 5 6 7 8 9 10 11

A B C Valorizacion de varias casas Propietario Carlos Sonia Jorge Sonia Jorge Carlos Sonia TOTAL

Zona Sur Norte Sur Oeste Norte Oeste Norte

Valorización S/. 114,000.00 S/. 120,000.00 S/. 186,000.00 S/. 75,000.00 S/. 222,000.00 S/. 150,000.00 S/. 90,000.00 S/. 957,000.00

D

E F Totales por Propietario Propietario Carlos Jorge Sonia TOTAL

S/. S/. S/. S/.

Valorización 264,000.00 408,000.00 285,000.00 957,000.00

Total de casas inferiores a 100,000:

S/.

165,000.00

Como hemos podido ver, esta función puede seleccionar en un rango cuales valores se van a sumar, teniendo en cuenta una cierta condición. Sin embargo, la función solo puede evaluar una sola condición de criterio: Todas las casas que sean de un determinado propietario: =SUMAR.SI(A4:A10,"Jorge",C4:C10) o; todas las casas que se encuentren en una determinada zona: =SUMAR.SI(B4:B10,"Norte",C4:C10)

La Función CONTAR.SI Cuenta las celdas en el rango que coinciden con el argumento criterio. Sintaxis CONTAR.SI(rango,criterio) Es el rango de celdas que se desea evaluar. Rango Es el criterio en forma de número, expresión o texto, que Criterio determina qué celdas se van a contar. Por ejemplo, el argumento criterio puede expresarse como 32; "32"; ">32"; "manzanas";D4. La función CONTARSI cuenta el número de veces que aparece un valor en un rango de celdas, por ejemplo: Contar el número de celdas en el rango B4:B10 que contienen el texto "Sur". =CONTAR.SI(B4:B10,"Sur ")

Pág. 4



Pero, ¿cómo haríamos si se desea sumar todas las valorizaciones de casas ubicadas en una determinada zona y que además pertenezcan a un determinado propietario? Para resolver cálculos condicionales que requieren más de un criterio, entonces se puede hacer uso de la combinación de la función SUMA y la función SI, o también la función CONTAR y la función SI; pero escritas dentro de una fórmula matricial.

2. Fórmulas Matriciales : a. Cómo crear un total basándose en varias condiciones Nota.- En Excel algunas fórmulas pueden escribirse como fórmulas matriciales. Para esto hay que escribir la fórmula y al finalizar hay que presionar la combinación de teclas Ctrl+Shift+Enter, esto hace que la formula se encierre entre llaves al momento de ingresar a la celda, quedando de esta forma: {=formula}

Primer Caso (para condiciones del tipo Y) Ahora veremos la siguiente fórmula matricial que permitirá calcular el valor total de las valorizaciones de las celdas C4:C10, donde el rango A4:A10 contiene al propietario "Sonia", y el rango B5:B10 contiene la zona denominada "Norte". {=SUMA(SI((A4:A10="Sonia")*(B4:B10="Norte"),C4:C10))} Las dos condiciones están encerradas entre paréntesis y entre ellas se escribe el operador de multiplicación, lo cual indica que las dos condiciones deben cumplirse a la vez. (Condición del tipo Y) Segundo Caso (para condiciones del tipo O) Para calcular el valor total de las valorizaciones de las celdas C4:C10, donde A4:A10 contiene "Carlos" o "Jorge", utilice la siguiente fórmula. {=SUMA(SI((A4:A10 ="Carlos")+(A4:A10 ="Jorge"),C4:C10))} En este caso las dos condiciones encerradas entre paréntesis se escribe con el operador de suma entre ellas, lo cual estaría indicando que es suficiente que se cumpla una de las dos condiciones para que se realice el cálculo. (Condición del tipo O) No olvide que ambas son fórmulas matriciales (están encerradas entre llaves) y deben introducirse presionando al final CTRL+SHIFT+ENTER. Otros ejemplos: Contar el número de veces que aparecen varias condiciones En la siguiente fórmula, cada vez que Excel encuentre "Sonia" en el rango A4:A10, comprobará la presencia del texto "Norte" en la misma fila en la columna B (el rango B4:B10). A continuación, Excel calculará de varios modos, el número de filas que contienen ambos textos. En los tres casos las formulas obtienen el mismo resultado. {=CONTAR(SI((A4:A10="Sonia")*(B4:B10="Norte"),1;0))} o sino así:

{=SUMA(SI((A4:A10="Sonia")*(B4:B10="Norte"),1;0))} Elaborado por Daniel Zegarra y William Chuquin

Pág. 5


o también así: {=SUMA(SI(A4:A10="Sonia ",SI(B4:B10="Norte",1,0)))} En las primeras dos fórmulas el operador de multiplicación indica que las condiciones son del tipo Y; y en la tercera formula se está utilizando dos funciones SI anidadas, o sea una función SI dentro de otra función SI. Se trata en estos casos de fórmulas matriciales y deben introducirse presionando al final CTRL+SHIFT+ENTER.

Las Funciónes K.ESIMO.MENOR, y K.ESIMO.MAYOR Encuentra los primeros menores o primeros mayores valores de una lista. Sintaxis K.ESIMO.MENOR(rango,posición) K.ESIMO.MAYOR(rango,posición) Rango es el rango de celdas que se desea analizar. Posición es ubicación del número menor o mayor que se desea encontrar. Esto es, para el menor de todos seria 1, para el segundo menor sería 2, etc.. Si se quisiera averiguar cual es la valorización que ocupa el segundo lugar entre las mayores, Y cual es la tercera valorización mas pequeña, las fórmulas serían: =K.ESIMO.MAYOR(C4:C10,2) 186,000.00 =K.ESIMO.MENOR(C4:C10,3) 114,000.00

Las Funciones REDONDEAR.MENOS, y REDONDEAR.MAS Redondea un valor numérico al inmediato inferior o al inmediato superior según la cantidad de decimales que se desee obtener. Sintaxis REDONDEAR.MENOS(valor,posición decimal) REDONDEAR.MAS(valor,posición decimal) Es la cantidad numérica que se desea redondear. Valor Posición decimal Es la cantidad de decimales a los que se desea redondear la cantidad numérica. Si en posición se escribe un número negativo se estará redondeando no los dígitos decimales sino los dígitos enteros. Si en la celda E4 estuviera escrito el número 3267.283, las funciones arrojarían los siguientes resultados: =REDONDEAR.MENOS(E4,1) 3267.2 =REDONDEAR.MAS(E4,1) 3267.3 =REDONDEAR.MENOS(E4,0) 3267 =REDONDEAR.MAS(E4,0) 3268

Pág. 6



=REDONDEAR.MENOS(E4,-1) =REDONDEAR.MAS(E4,-1) =REDONDEAR.MENOS(E4,-3) =REDONDEAR.MAS(E4,-3)

3260 3270 3000 4000

La Función ALEATORIO Genera un número completamente al azar entre 1 y 0. Sintaxis ALEATORIO() Esta función carece de argumentos. El siguiente ejemplo simula el lanzamiento de un dado =ENTERO(ALEATORIO()*6)+1 La función Aleatorio multiplicada por 6 generará un numero entre 0 y 5.9999. La función Entero truncará la parte decimal de éstos números quedando así enteros del 0 al 5, y al aumentarles 1 éstos finalmente serian números entre 1 y 6. Escrita esta fórmula, bastara con presionar la tecla de función [F9] para recalcular la fórmula y entonces ALEATORIO generará otro número al azar.

La Función HOY Da como resultado la fecha del sistema. Sintaxis HOY() Esta función carece de argumentos.

=HOY()

daría como resultado:

07/06/2015

La Función AHORA Da como resultado la fecha y hora del sistema. Sintaxis AHORA() Esta función carece de argumentos.

=AHORA() daría como resultado:

Elaborado por Daniel Zegarra y William Chuquin

07/06/2015 03:21

Pág. 7


E jercicio Nº 2: C álculos de S tock con la función Suma En una hoja de cálculo se tiene el siguiente cuadro de adquisiciones y consumos en papelería de una oficina.

En el primer cuadro calcule el stock mensual con las siguientes formulas: =C5-D5 En E5: =E5+C6-D6 luego copie esta segunda fórmula para los En E6: demás meses. En el segundo cuadro calcule el stock con esta única fórmula: En J5: =SUMA(H$5:H5)-SUMA(I$5:I5) Luego copie también esta fórmula para los demás meses. El resultado en ambos cuadros se ve igual. Sin embargo si en el primer cuadro se ingresan datos del tipo texto en las celdas de adquisición o consumo, las formulas fallarán y ya no podrán sumar el stock. En el segundo cuadro en cambio, los datos tipo texto no afectan el resultado de las formulas como podrá apreciarse en la siguiente imagen:

Esto es debido a que la función Suma ignora los datos tipo texto en el rango a sumar.

Pág. 8



E jercicio Nº 3: C ontrol de E mbarques con la función B us carV e indirecto En una hoja de cálculo se tiene el siguiente cuadro de Embarques, y se desea calcular los pagos por Flete para cada una de las Empresas Navieras.

Para obtener la distancia recorrida por cada embarcación: 1.Para nombrar cada fila y columna del cuadro de distancias; seleccionar el rango J10:Q16 y en la Ficha formulas elegir Crear desde la selección, y al aparecer la siguiente ventana hacer clic en Aceptar

.


Pág. 9


2.- En la celda G21 escribir la fórmula: =INDIRE CTO(C21) INDIRE CTO(D21) Para obtener el pago por Flete para cada embarcación: 3.- Nombrar la tabla de tarifas. Para esto seleccione el rango G12:H16 y dele el nombre TARIFAS. 4.- En la celda H21 escribir la fórmula: =F21*G21*BUSCARV(E21,TARIFAS,2) , Luego copie ambas fórmulas hacia abajo hasta el final del cuadro.

Para validar el ingreso de datos en la celda B11: 4.- Previamente seleccionar el rango A21:H246 y nombrarlo como DOC. Nos ubicamos en la celda B11, y en la ficha Datos/Validación de datos elegir en la lista desplegable de Permitir la opción Lista, y en la casilla de texto Origen escribir =DOC, y luego Aceptar. 3.- Dar nombre al cuadro de embarques. Para esto seleccione el rango A20:H246 y dele el nombre EMBARQUES. 6.- En la celda B12 escribir la formula siguiente: =BUSCARV(B11,LISTA,FILA(A2),0) y luego cópiela hacia abajo, y dele formato a las celdas tal como se aprecia en la siguiente figura:

Pág. 10



3. Validación en el Ingreso de datos a. Que es la validación Es la forma en que Excel impide que ingresen los datos que escribimos en las celdas (números, textos, fechas y horas, etc.) verificando que estos de encuentren dentro de límites especificados previamente. De este modo se impide que se cometan errores al momento de digitar la información. Por ejemplo: Impedir ingresar edades de personas que sean mayores a 100 años. Impedir que se ingresen votos en una mesa de sufragio mayores a 200 electores Impedir que se escriban códigos de artículos que no existen en la lista de precios

b. Cómo validar el ingreso de datos en un rango de celdas 1. Seleccione las celdas que desee restringir. 2. En la ficha Datos, haga clic en Validación de datos / Validación de datos y elija la ficha Configuración. 3. En el cuadro Permitir, haga clic en la lista desplegable. Para especificar sólo números, haga clic en Número entero o Decimal. Para especificar fechas u horas, haga clic en Fecha u Hora.


Pág. 11


4. Haga clic en el operador que desee en el cuadro Datos y especifique el límite inferior o superior para los datos, o bien ambos límites, dependiendo del operador que se seleccione. Pueden introducirse valores, referencias de celda o fórmulas para los límites. Si desea permitir que la celda que se está restringiendo esté en blanco o si desea definir límites que utilicen una referencia de celda, o una fórmula que dependa de celdas que inicialmente están en blanco, compruebe que la casilla de verificación Omitir blancos está activada. Para hacer obligatorias las restricciones definidas en las celdas en blanco, tratándolas como si tuvieran ceros, desactive la casilla de verificación Omitir blancos. 5. Para que aparezcan mensajes que pidan entradas y que expliquen o impidan las entradas incorrectas, especifique los tipos de mensajes que desee en las fichas Mensaje entrante y Mensaje de error. Para omitir la presentación de mensajes, desactive las casillas de verificación Mostrar mensaje al seleccionar la celda en la ficha Mensaje entrante y Mostrar mensaje de error si se introducen datos no válidos en la ficha Mensaje de error.

Ejemplo: Se desea validar un cuadro para que en la columna Cantidad solo permita escribir números enteros y positivos. Entonces, cuando se trate de escribir en una celda una cantidad como 7.3 que es un numero decimal, deberá aparecer un mensaje de error como el que se ve a la derecha, impidiendo ingresar este número.

1 2 3 4 5 6 7 8

A

B

Personal

Cantidad

Empleados Vigilantes Secretarias Gerentes Técnicos 7.3 Supervisores Vendedores

138 11 24 8

9

Para esto seleccionar el rango B2:B8, elija el comando Datos/Validación de datos, y elegir las opciones como se muestran en la siguiente ventana de diálogo:

Pág. 12



Nota.- Cuando se especifique el tipo de datos que se permiten, esto no afectará al formato. Para dar formato a las celdas, como números, fechas u horas, haga clic en el menú Formato/Celdas y, a continuación, haga clic en la ficha Número. Seleccione el formato que desee en el cuadro Categoría y luego seleccione las opciones que desee. Una fórmula para un límite puede evaluar los datos únicamente en la hoja de cálculo en que se hayan configurado las restricciones. Para utilizar en una fórmula los datos de otra hoja de cálculo, o de otros libros, introduzca una referencia de celda para los datos externos en una celda de la hoja de cálculo activa o defina un nombre para los datos externos en la hoja de cálculo activa. La fórmula puede hacer referencia a la celda o al nombre en la misma hoja de cálculo. Por ejemplo, si los datos que desea utilizar en una fórmula están en la celda A6 de la primera hoja de cálculo de un libro denominado Presupuesto.xls, puede definir el nombre DatosVálidos en la hoja de cálculo activa para que sea =[Presupuesto.xls]Hoja1!$A$6 y, posteriormente, introducir una referencia =DatosVálidos cuando se especifiquen los límites para los datos.

c. Realizar una auditoría en un rango validado Puede buscar entradas de celdas que no se ajusten a sus criterios y rodearlas automáticamente con un círculo haciendo clic en el comando Rodear con un círculo datos no válidos de la ficha Datos/Validación de datos.

Ejemplo1 1.- En una hoja de cálculo escriba una lista de varios nombres de niños y sus respectivas edades, tal como se muestra en la figura siguiente. Observe que las edades de Laura y Miguel fueron escritas erróneamente.


Pág. 13


En este cuadro se desea verificar que las edades ya ingresadas de los niños, no sean mayores de 12 años. Para esto, haga usted lo siguiente:

2.- Seleccionar el rango B2:B8 y con el comando Datos/Validación de datos , elegir Permitir Números enteros entre un mínimo de 0, y un máximo de 12.

3.- Luego de validar, haga un clic en el comando Rodear con un círculo datos no válidos , entonces aparecerán marcadas con círculos rojos las cantidades incorrectas. 4.- Corrija entonces las edades de ambos niños. A medida que corrija estas cantidades,

Pág. 14



observe que irán desapareciendo los círculos rojos que encerraban a las edades equivocadas.

Ejemplo2 En otro caso; suponga que se tiene un registro de ventas con la relación de las facturas emitidas en un mes y se desea verificar que los números de factura no aparezcan repetidos, en cuyo caso habría que analizar el motivo del error y corregirlo, ya sea cambiando el número de la factura por el correcto, o eliminando el registro de la factura por estar duplicada. 1.- En una hoja en blanco escriba los datos del Registro de Ventas que se muestra en la imagen siguiente:

A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

B

C

REGISTRO DE VENTAS Fecha

Nº Factura

Monto

02-Oct 02-Oct 03-Oct 03-Oct 03-Oct 03-Oct 03-Oct 04-Oct 04-Oct 04-Oct 05-Oct 06-Oct

001-0124 001-0126 001-0126 001-0127 001-0128 001-0129 001-0129 001-0130 001-0131 001-0132 001-0132 001-0134

S/. 1,306.00 S/. 510.00 S/. 1,364.00 S/. 432.00 S/. 935.00 S/. 399.00 S/. 399.00 S/. 220.00 S/. 1,199.00 S/. 778.00 S/. 257.00 S/. 384.00

Esta factura debería ser la 001-0125

Esta factura se repite y deberá eliminarse

La factura de abajo debería ser la 001-0133

16

2.- En este cuadro seleccionar todas las celdas con los números de factura de la columna B, y con el menú Datos/Validación se valida según como se muestra en la ventana de dialogo a continuación: La fórmula =B4<>B5 verificará cuando dos celdas consecutivas se repitan.


Pág. 15


3.- Luego de validar, haga clic en el comando Datos/Validación de datos/ Rodear con un c írculo datos no válidos .

A medida que se vayan corrigiendo los errores en los números de las facturas, al igual que en el ejemplo anterior, los círculos rojos también se irán desapareciendo.

Pág. 16



4. Formato Personalizado de Celdas a. Cuando crear formatos personalizados La apariencia que pueden adquirir los números en las celdas, pueden variar según el formato que se les asigne y para esto existen varias categorías de formatos. Pero si estas no satisfacen nuestras necesidades, uno mismo puede crear sus propios formatos de números personalizados. Ejemplo de formatos tradicionales existentes en Excel:

A 1

3,473.50

Estilo millares

#,##0.00

2

S/. 672.25

Estilo moneda

S/. #,##0.00

3

13.7%

Estilo porcentual

0.0%

4

(79.5)

Negativos entre paréntesis

(0.00)

5

4-Jun

Fechas con día y mes

d-mmm

Ejemplo de formatos personalizados creados por el usuario:

A 6

64.5 Kg

Para pesos en kilogramos

0.0 “Kg”

7

37.4 º C

Grados centígrados

0.0”ºC”

8

000254

Para códigos

000000

9

IGV 19%

Texto y porcentaje

“IGV” 0%

10

4 puntos

Número y texto

0 “puntos”

Tenga en cuenta que en todos los ejemplos anteriores las celdas tienen datos numéricos y aunque el formato ha cambiado su apariencia, “ el contenido de ellas sigue siendo numérico” y puede operarse matemáticamente en cualquier fórmula. Nota.- Cuando se crean formatos personalizados, estos formatos solo tendrán validez dentro del libro en el cual se crearon.

Para poder crear formatos personalizados hay que seguir unas cuantas reglas, las cuales pasamos a indicar a continuación:


Pág. 17


b. Como crear formatos personalizados En el menú contextual de una la celda , elegir la opción Formato de celda y en la categoría Personalizada:

Escribir aquí el formato personalizado

En la casilla de texto llamada Tipo se pueden definir formatos personalizados o elegir los ya existentes en la lista inferior.

Crear un formato de número personalizado 1. 2. 3. 5.

Seleccione las celdas a las que desea dar formato. En el menú Formato de celda, haga clic en la ficha Número. En la lista Categoría, haga clic en Personalizada. En el cuadro Tipo, modifique los códigos de formato de número para crear el formato que desee. Puede especificar hasta cuatro secciones de códigos de formato. Las secciones, separadas por caracteres de punto y coma, definen los formatos de los números positivos, números negativos, valores cero y texto, en ese orden. Si especifica sólo dos secciones, la primera se utiliza para los números positivos y ceros, y la segunda se utiliza para los números negativos. Si especifica sólo una sección, todos los números utilizan ese formato. Si omite una sección, incluya el punto y coma que separa esa sección.

Pág. 18



Formato de números positivos

Formato de ceros

#,##0.00_) ; [ROJO](# ,##0.00) ; 0.00 ; “Factura” @ Formato de números negativos

Formato de textos

Nota.- El símbolo de arroba @ representa el contenido de la celda cuando este contenido es del tipo texto.

Utilice códigos de formato que describan cómo desea mostrar un número, una f echa u hora, una moneda, porcentajes o notación científica y un texto o espacio.

Ejemplo: A un grupo de celdas con diferentes datos se le da el siguiente formato personalizado: [AZUL]#,##0.00_) ; [ROJO](# ,##0.00) ; “Sin Valor ; @ “anulada”

A 1

-8734.9

2

0

3

Factura

4

6254.258

5

Boleta

Número negativo Cero Texto Número positivo Texto

El resultado final en el rango formateado es el siguiente:

A 1

(8,734.90)

Entre paréntesis y color rojo

2

Sin Valor

Cero cambia a Sin Valor

3

Se le agrega la palabra anulada

4

Factura anulada 6,254.26

5

Boleta anulada

Se le agrega la palabra anulada

Redondea y de color azul

Como se puede ver, cada celda adquiere el formato que le corresponde según el contenido que esta tenga.


Pág. 19


c. Códigos de formato de número para fechas y horas Días, meses y años Para presentar días, meses y años, incluya en una sección los siguientes códigos de formato. Si utiliza una "m" inmediatamente detrás del código "h" o "hh", o bien inmediatamente delante del código "ss", Microsoft Excel presentará los minutos en lugar de presentar el mes.

Para ver

Use este código

Los meses como 1 –12

m

Los meses como 01 –12

mm

Los meses como ene. –dic.

mmm

Los meses como enero –diciembre

mmmm

Los meses como la inicial de cada mes

mmmmm

Los días como 1 –31

d

Los días como 01 –31

dd

Los días como lun. –sáb.

ddd

Los días como lunes –sábado

dddd

Los años como 00 –99

aa

Los años como 1900 –9999

aaaa

Horas, minutos y segundos Para presentar horas, minutos y segundos, incluya en una sección los siguientes códigos de formato.

Para ver

Pág. 20

Use este código

Las horas como 0 –23

h

Las horas como 00 –23

hh

Los minutos como 0 –59

m

Los minutos como 00 –59

mm

Los segundos como 0 –59

s

Los segundos como 00 –59

ss

Las horas como 4 a.m.

h a.m./p.m.

La hora como 4:36 p.m.

h:mm a.m./p.m.



Para ver

Use este código

La hora como 4:36:03 p.

h:mm:ss a/p

El tiempo transcurrido en horas; por ejemplo, 25:02

[h]:mm

El tiempo transcurrido en minutos; por ejemplo, 63:46

[mm]:ss

El tiempo transcurrido en segundos

[ss]

Fracciones de segundo

h:mm:ss.00

Si el formato contiene la indicación a.m. o p.m., la hora se basará en el formato de 12 horas, donde "a.m." o "a" indica las horas desde la medianoche hasta el mediodía y "p.m." o "p" indica las horas desde el mediodía hasta la medianoche. En caso contrario, el reloj se basará en el formato de 24 horas. La letra "m" o las letras "mm" deben aparecer inmediatamente detrás del código "h" o "hh", o bien inmediatamente delante del código "ss"; de lo contrario, Microsoft Excel presentará el mes en lugar de presentar los minutos.

d. Códigos de formato de número para posiciones decimales, espacios, colores y condiciones Utilice los códigos de formato de número para crear un formato de número personalizado.

Decimales y dígitos significativos Para dar formato a las fracciones o los números con decimales, incluya los siguientes dígitos marcadores en una sección. Si un número tiene más dígitos a la derecha del separador que marcadores en el formato, se redondeará para que tenga tantos decimales como marcadores. Si hay más dígitos a la izquierda del separador que marcadores, se presentarán los dígitos adicionales. Si el formato contiene solamente signos de número (#) a la izquierda del separador, los números menores que la unidad comenzarán por el separador. # 0 ?

muestra únicamente los dígitos significativos y no muestra los ceros sin valor. (cero) muestra los ceros sin valor si un número tiene menos dígitos que la cantidad ceros en el formato. agrega los espacios de los ceros sin valor a cada lado del separador decimal, para alinear los decimales con formato de fuente de ancho fijo, como Courier New. También puede utilizarse ? para las fracciones que tengan un número de dígitos variable.


Pág. 21


Para ver

Use este código

1234.59 como 1234.6

####.#

8.9 como 8.900

#.000

.631 como 0.6

0.#

12 como 12.0 y 1234.568 como 1234.57

#.0#

44.398. 102.65 y 2.8 con decimales alineados

???.???

5.25 como 5 1/4 y 5.3 como 5 3/10, con los símbolos de división alineados

# ???/???

Separador de millares Para ver una coma como separador de los millares o para ajustar la escala de un número mediante un múltiplo de mil, incluya una coma en el formato de número. Para ver

Use este código

12000 como 12,000

#,###

12000 como 12

#,

12200000 como 12,2

0,0,,

Color Para definir el color de una sección del formato, escriba en la sección el nombre de uno de los siguientes ocho colores entre corchetes. El color debe ser el primer elemento de la sección. [NEGRO]

[AZUL]

[AGUAMARINA] [VERDE]

[FUCSIA]

[ROJO]

[BLANCO]

[AMARILLO]

Condiciones Para definir los formatos de número que se aplicarán únicamente si coinciden con las condiciones que se hayan especificado, encierre la condición entre corchetes. La condición consta de un operador de comparación y un valor. Por ejemplo, el siguiente formato muestra los números iguales o inferiores a 100 en color rojo y los números superiores a 100 en color azul. [Rojo][<=100];[Azul][>100]

Pág. 22



En cambio, para aplicar formatos condicionales a las celdas (por ejemplo, el sombreado de color que depende del valor de una celda) utilice el comando Formato condicional en el menú Formato.

e. Códigos de formato de número para texto y espaciado Agregar caracteres Para ver texto y números en una celda, encierre los caracteres de texto entre comillas ("") o ponga delante de un solo carácter una barra invertida (\). Incluya los caracteres en la sección correspondiente de los códigos de formato. Por ejemplo, introduzca el formato $ 0.00 "Exceso";-$ 0.00 "Defecto" para ver un importe negativo como "-$125.74 Defecto". El espacio y los siguientes caracteres se muestran sin comillas: $ - + / ( ) : ! ^ & ' (comilla simple izquierda) ' (comilla simple derecha) ~ { } = <> Incluir una sección de texto Si se incluye una sección de texto, siempre será la última sección en el formato de número. Incluya el carácter @ en la sección en que desee presentar el texto escrito en la celda. Si se omite el carácter @ en la sección de texto, no se verá el texto que se haya escrito. Si desea ver siempre caracteres de texto específicos con el texto escrito, encierre el texto adicional entre comillas dobles (" "), por ejemplo, "facturación bruta de "@ Si el formato no incluye una sección de texto, el texto que escriba no se verá afectado por el formato. Agregar espacio Para crear un espacio con el ancho de un carácter en un formato de número, incluya un subrayado _ seguido del carácter. Por ejemplo, si después de un subrayado se cierra un paréntesis _ ) entonces los números positivos se alinearán correctamente con los números negativos que estén entre paréntesis. Repetir caracteres Para repetir el carácter siguiente en el formato para llenar el ancho de la columna, incluya un asterisco (*) en el formato de número. Por ejemplo, introduzca $*=#,##0.00 para incluir suficientes signos = después del signo monetario para llenar una celda. El resultado sería: $=====2,716.25


Pág. 23


f. Eliminar un formato de número personalizado 1.- En el menú Formato, haga clic en Celdas y haga clic en la ficha Número. 2.- En la lista Categoría, haga clic en Personalizada. 3.- En la parte inferior del cuadro Tipo, haga clic en el formato personalizado que desee eliminar. 4.- Haga clic en Eliminar. Solamente pueden eliminarse los formatos personalizados. Microsoft Excel aplicará el formato predeterminado (General) a todas las celdas del libro a las que se haya dado formato mediante el formato personalizado que se ha eliminado.

g. Si las fechas introducidas en la hoja de cálculo no tienen el mismo aspecto Cuando se introduce una fecha en una celda, dicha fecha aparece en un formato de fecha predeterminado o en un formato aplicado a la celda antes de introducir la fecha. El formato de fecha predeterminado se basa en la configuración de la ficha Fecha del cuadro de diálogo Configuración regional, al que se tiene acceso desde el Panel de control de Windows. Si estos valores de fecha han cambiado, cualquier fecha existente en los libros no formateados con el comando Formato de celdas también cambia.

Pág. 24



Capítulo 2 1. Calculo de Remuneraciones del Personal Los cálculos de los pagos de sueldos y salarios son una tarea ineludible en las empresas, y como hay que hacerla todos los meses o quincenas en el caso de empleados, y todas las semanas en el caso de los obreros, pues; esta labor se convierte en algo muy tedioso para el que está encargado de esta labor. Felizmente hoy en día, las computadoras han aliviado enormemente este trabajo, y el cálculo de remuneraciones se ha convertido en algo bastante sencillo para aquel que sabe cómo decirle a la computadora que nos dé una mano en esto. Lo que vamos a ver a continuación es el cálculo de remuneraciones para los obreros de una fábrica, o sea vamos a calcular parcialmente una planilla de salarios. Digo parcialmente, porque una planilla real, tiene mucho más columnas y rubros de los que aquí vamos a ver, pero la idea de este ejercicio es que veamos, para el caso de los cálculos más importantes, como son los cálculos de los tiempos de trabajo y los descuentos por tardanza, y como es que se hace con Excel para manejar las fechas y horas en el pago de estas remuneraciones.

Calculo de la Planilla de Salarios en una fábrica de confecciones de lana de alpaca: La fábrica cuenta en una de sus secciones con 5 obreros cuyos horarios de trabajo normal son desde las 8:00 am hasta las 5:00 pm, con una hora de descanso para almorzar. La labor se realiza de lunes a sábado. Las tarifas de pago del personal son las siguientes: Pago de horas normales: S/. 5.30 la hora Pago de horas extras: S/. 6.00 la hora Descuento por tardanza: S/. 0.20 por minuto Cada vez que el obrero ingresa a la fábrica debe marcar su hora de ingreso, y al salir igualmente marca su hora de salida. Con esta información, se desea calcular cuánto gana cada obrero al final de la semana.


Pág. 25


Para que sea más sencillo ver como se calculan los pagos y descuentos del primer obrero, veamos este croquis:

En el cuadro de planillas, se van a escribir las fórmulas para calcular los pagos y descuentos para el primer obrero, pero estas fórmulas deberán ser escritas de modo que cuando se copien para el resto del personal, también calculen correctamente sus pagos y descuentos. Para los siguientes cálculos que vamos a ver, no voy a escribir la formula final que obtendría el cálculo definitivo de cada columna de la planilla, sino que voy a ir escribiendo poco a poco las operaciones que supuestamente se necesitan para cada calculo, y así según las respuestas obtenidas, corregir las formulas hasta obtener el cálculo final que se desea. Pág. 26



1. Calculo de las horas normales en F8:

Para este cálculo habría que restar la hora oficial de salida (5:00 pm) menos la hora de ingreso del trabajador (8:35 am): =C$5-D8 Rpta: 8:25 Pero a este cálculo hay que descontarle la hora de refrigerio del medio dia: =C$5-D8-1 Rpta: ######### La respuesta da error porque cuando Excel realiza cálculos con el tiempo, los tiempos para Excel se miden siempre en días, no en horas o minutos. Entonces; cuando en la formula anterior le restamos 1 para quitarle la hora de refrigerio, en realidad Excel le está restando no 1 hora sino 1 día (o sea 24 horas). La fórmula entonces debería ser entonces así: =C$5-D8-1/24 Rpta: 7:25 Ahora parece que la formula ya estuviera correcta, pero cuando la copiamos para los demás obreros, al tercer obrero le calcula un tiempo de trabajo de 16:00, y para evitar esto la formula debería ser así: =SI(D8=””,””,C$5 -D8-1/24) Si volvemos a copiar la formula hacia abajo el tercer obrero ya no tendría horas trabajadas, lo cual es correcto. Pero algunos obreros acumulan más de 8 horas de trabajo normal al día, lo cual es un error ya que como máximo un dia tiene solo 8 horas normales de trabajo. Esto ocurre así porque algunos trabajadores que llegaron temprano marcaron su hora de ingreso unos minutos antes de las 8:00 am, y la formula estaría considerando esos minutos para el pago de sus horas normales, lo cual es un error ya que los obreros encienden sus máquinas e inician su labor diaria exactamente a las 8:00 de la mañana. Entonces, la formula habría que corregirla finalmente de la siguiente manera: =SI(D8=””,””,C$5 - SI(D8>C$4,D8,C$4)-1/24)

2. Pago de las horas normales en G 8:

El pago de horas normales seria la multiplicación de las horas de trabajo normal por la tarifa de pago de horas normales: =F8*K$3 Rpta: 1.64 Pero este cálculo no le va a gustar nadita al obrero Fernando; mas ganaría limpiando las lunas de los autos en el semáforo. Lo que pasa aquí es que aparentemente en la celda F8 se encuentra el numero 7:28 o sea 7 horas con 25 minutos, y lo vemos así porque esa celda tiene el formato de horas y minutos hh:mm, sin embargo la formula en esa celda ha


Pág. 27


calculado el tiempo de trabajo normal de Fernando pero lo ha hecho en días, no en horas y minutos (recuerde que Excel siempre maneja el tiempo en días) Si le damos el formato general a la celda F8, veremos que allí se encuentra el numero: 0.30902778 lo que en días representa un tiempo de más o menos un tercio de día de trabajo normal. Por esta razón al multiplicar F8*K$3 se está multiplicando en realidad 0.309027 por K$3. Pero si K3 contiene la tarifa de pago por hora, entonces el tiempo de trabajo también debería estar calculado en horas, no en días como la está ahora. Lo que hay que hacer entonces, es convertir el tiempo de trabajo de días a horas, y eso se hace sencillamente multiplicando los días por 24, dado que un día tiene 24 horas. O sea que la fórmula del pago debería ser así: =F8*24*K$3 Rpta: 39.31 Ahora si, este pago ya es más razonable. Sin embargo cuando copiamos la fórmula para el resto de obreros, para aquellos que no vinieron a trabajar la formula da error. Esto pasa así porque al multiplicar su tiempo de trabajo en la columna F que es el texto doble comilla, la multiplicación de un texto por K$3 da error pues. Pero esto se arregla finalmente con la siguiente corrección en la fórmula: =SI(F8=””,0,F8*24*K$3)

3. Calculo de las horas extras en H8:

El cálculo de las horas extras seria la resta de la hora de salida menos la hora oficial de salida,es decir las 5:00 pm: =E8-C$5 Rpta: 1:15

Pág. 28



Para el obrero Fernando el cálculo es correcto; ya que Fernando se quedó a trabajar 1:15 una hora y 15 minutos después de la 5 de la tarde. Pero al copiar esta fórmula hacia abajo resulta que todo el personal tiene horas extras, y eso no es cierto. Los reglamentos de trabajo en cada fábrica son diferentes, y estos reglamentos deben acomodarse según las leyes laborales que existen en cada país. Así por ejemplo, hay empresas que pagan horas extras a su personal, siempre y cuando estas horas extras sean horas cumplidas, es decir no se aceptan pagos de fracción de horas, estas deben ser siempre números enteros. Ot ras empresas en cambio si pagan las fracciones de hora trabajadas. Supongamos que en la empresa de nuestro ejemplo, para el pago de horas extras si se considerarán las fracciones de hora y se deben cumplir las siguientes condiciones: 





A las 5:00 pm termina la hora de trabajo normal, y todos los obreros en la planta deben apagar sus máquinas, guardar sus herramientas, cambiarse la ropa de trabajo y salir de la fábrica marcando su hora de salida. El plazo para marcar su hora de salida es hasta las 5:30 de la tarde, luego de esta hora, nadie debe quedarse dentro de la planta, salvo el personal que tiene autorización de quedarse a trabajar horas extras. Ellos no han apagado sus máquinas y siguen trabajando hasta que al finalizar su labor marcaran también su hora de salida. Solo se considerará el tiempo de horas extras a aquellos trabajadores que marcaron su hora de salida después de las 5:30 pm.

El cálculo para obtener las horas extras de trabajo seria finalmente: =SI(E8>C$5+”00:30”,E8 -C$5,””)

4. Pag o de las horas extras en I8:

El pago de horas extras sería muy semejante al pago de horas normales pero con la tarifa de la celda K4: =SI(H8=””,0,H8*24*K$4)

Rpta: 7.50


Pág. 29


5. Calculo de la tardanza en J 8:

El tiempo de tardanza seria la resta de la hora de ingreso menos la hora oficial de ingreso, o sea las 8:00 am. Pero esta resta se haría solo para aquellos obreros que ingresen después de la 8:00 am: =SI(D8>C$4,D8-C$4,””) Rpta: 00:35 6. Des cuento por tardanza en K 8:

Este cálculo del descuento por tardanza seria también semejante al pago de horas normales y horas extras, es decir hay que multiplicat el tiempo de tardanza por la tarifa de descuento por tardanza. Pero aquí hay que considerar que la tarifa de descuento es de 0.20 céntimos por minuto, y no por hora. O sea que el tiempo de tardanza que Excel lo calcula como ya sabemos en días, hay que convertirlo previamente a minutos. Entonces; finalmente la formula sería la siguiente: =SI(J8=””,0,J8*24*60*K$5)

Rpta: 7.00 7. Calculo del Pag o Total en L8:

Sería la suma del pago de horas normales, más el pago de horas extras, menos el descuento por tardanza: =G8+I8-K8

Rpta: 39.81

Pág. 30



De este modo ya se tendrían calculados los pagos diarios de cada trabajador. Pero los obreros no cobran todos los días, sino cada fin de semana, entonces; es necesario calcular cuánto cobraría cada obrero por semana, es decir hay que sumar los pagos de los 6 días de lunes a sábado Para lograr esto, no es necesario que se escriba ninguna fórmula, pues esto se puede obtener simplemente ordenando la planilla alfabéticamente por obrero, y luego utilizando el comando Subtotal que se encuentra en la ficha Datos de la cinta de opciones de Excel. 8. Calculo del Pago S emanal de cada obrero:

Realizar los siguientes pasos:   

Seleccionar la celda B7 Ficha Datos/Subtotal

En la lista desplegable “Para cada cambio en” elegir la opción “Apellidos y Nombres”; y luego Aceptar.


Pág. 31


Entonces la planilla deberá verse así finalmente:

Para obtener la Boleta de Pago de cada trabajador: Una vez calculada toda la planilla de Salarios, ahora vamos a calcular la Boleta de Pago de los trabajadores. Aunque una boleta real tiene muchos más datos de los

Pág. 32



que aquí se van a presentar, este ejemplo nos va a servir para tener una idea de cómo se puede podría armar un documento de este tipo. Por lo general, en las boletas de pago no se muestra la foto del trabajador, pero en esta ocasión nuestra boleta va a mostrar en la pantalla la foto del obrero del cual se esté calculando su pago, pero al momento de imprimir esta boleta, la fotografía no saldrá impresa en el papel. La explicación detallada de cómo resolver esta tarea se muestra a continuación:

En la hoja “Boleta” del libro actual diseñe la siguiente boleta de pago:

Definir los siguientes nombres de rango: En la hoja Planilla: Codigo Extra HorasExtras HorasTarde HorasTrab Ingreso Normal Planilla Tardanza

A8:A57 I8:I57 H7:H57 J8:J57 F8:F57 D8:D57 G8:G57 A8:L57 K8:K57

En la hoja Boleta: Cod

D5

Escribir las siguientes formulas en la hoja Boleta: En D7: =BUSCARV(D5,PLANILLA,2,0) En E9: {=SUMA(SI((Codigo=D$5)*(Ingreso>0),1,0))} En E11: =SUMAR.SI(Codigo,Cod,Normal) En E12: =SUMAR.SI(Codigo,Cod,Extra)


Pág. 33


En E13: En E14:

=SI(E9>0,(E9/6)*8*Planilla!K3,0) =SUMAR.SI(Codigo,Cod,Tardanza)

En el botón “Control de número” que se encuentra a la derecha de la celda D5 hacer un clic derecho y elegir la opción “Formato de control”. Aparecerá la siguiente ventana:

Ingrese los datos que se muestran en la figura anterior, y luego Aceptar. Finalmente presione ESC para que se libere la selección del botón. Ahora, cada vez que haga clic en el botón, y cambie el código del trabajador, aparecerán todos los datos de este trabajador en la boleta de pago, incluyendo la fotografía del trabajador. Para no imprimir la foto en la boleta:

Para que no se imprima la foto, haga un clic derecho en la foto y en el menú

contextual que aparece, elija la opción “Tamaño y propiedades”. Luego en la ficha Propiedades, desactivar la casilla “Imprimir objeto”, y luego clic en el botón “Cerrar”.

La macro que hace cambiar la foto del trabajador: Sub VerFoto() Ruta = ActiveWorkbook.Path On Error Resume Next NN = "Obrero" & Range("Cod") ActiveSheet.Shapes("Foto").Fill.UserPicture Ruta & "\" & NN & ".jpg" ActiveCell.Select End Sub

Pág. 34



2. Programación de la Producción Una empresa fabricante de piezas de plástico recibe el encargo de producir 7000 bancos de plástico, los cuales debe entregarlos en el plazo de 2 semanas a su cliente. La empresa cuenta para esta tarea con 4 máquinas inyectoras de plástico que se turnarán diariamente para cumplir con la producción. Estas máquinas inyectoras tienen una velocidad de producción en piezas por hora, tal como se indica a continuación: Máquina RPA72 RPX90 SPX34 WPA80

Piezas/hora 104 125 110 92

RPA72

RPX90

WPA80

SPX34

En la hoja de cálculo construya el siguiente cuadro de producción:


Pág. 35


Calculo del tiempo diari o de trabajo de cada máquina: 1. En E5 escriba: =D5-C5, y copie esta fórmula hacia abajo. Calculo del tiempo total de trabajo de las máquinas: 2. En E16 escriba: =SUMA(E5:E15)

Luego dele a la celda el formato personalizado del tipo [hh]:mm Calculo del número total de piezas producidas diariamente: En la Hoja2 del libro escriba el siguiente cuadro:

3. Al rango A3:B7 dele el nombre de rango: TABLA1 En la Hoja1 del libro escriba el siguiente cuadro: 4. En F5 escriba: =BUSCARV(b5,TABLA1,2,0)*E5*24 5. Luego copie la formula hacia abajo. 6. En F16 escriba: SUMA(F5:F16) Se tendrá lo siguiente en la pantalla:

Pág. 36



Como la suma total de piezas producidas en F16 no alcanzan las 7000 que ha pedido el cliente, entonces; se va a aumentar el tiempo de trabajo de la maquina WPA80 en el día 8 de junio a fin de que la producción l legue a 7000 piezas. Para

lograr esto vamos a utilizar la opción “Buscar Objetivo”:

7. Seleccione la celda F16 8. Ficha Datos/, Grupo Previsión / Análisis e hipótesis i /Buscar objetivo:

9. Ingrese los datos que se observan en la ventana anterior y luego clic en Aceptar. La máquina WPA80 deberá entonces trabajar el día 8 de junio hasta las 14:32 de la tarde para cumplir con el pedido del cliente. El cuadro de Producción se verá entonces así:


Pág. 37


A pesar que con este ajuste del tiempo de trabajo, ya se logró alcanzar la meta de la producción requerida, pero; la distribución de la carga de trabajo diaria no es muy uniforme que digamos: hay días que las maquinas producen mucho y otros que casi producen poco. En la columna de Producción esta diferencia entre lo que se produce un día u otro día no es muy notoria, es decir no se aprecia muy bien cuanto más se produce en un día respecto a los demás. Si se tuviera un gráfico de estos valores de la producción entonces si se podría apreciar cuan diferente es la cantidad que se produce cada día. A continuación, vamos a crear un gráfico (o mejor dicho un pseudo gráfico) que nos permita darnos cuenta que tan bien estamos distribuyendo la carga de trabajo entre las maquinas día a día. G ráfica y porcentaje de avance de la producc ión diaria:

10. En G5 escribir la siguiente formula:

=REPETIR(“█”,F5/200)&” “&REDONDEAR(F5/F$16*100,1)&”%” 11. Luego copiar esta fórmula hacia abajo y reducir el tamaño de letra a 8 puntos. La fórmula anterior repite el rectangulito negro (este carácter se escribe con el código ASCII: Alt+219) tantas veces como resulte de dividir la producción del día entre 200.El cuadro de Producción deberá verse entonces así:

Efectivamente; ahora nos damos cuenta que hay días que se trabaja casi 5 veces lo que en otro día. Lo cual no es nada recomendable, pues una máquina que se esfuerza mucho tiene más posibilidades que empiece a fallar o a malograrse más prontamente. La carga de trabajo entonces debería haberse distribuido más uniformemente durante estos 15 días.

Pág. 38



Por otro lado, también nos gustaría saber si todas las máquinas están haciendo la misma cantidad de trabajo, o es que tal vez hay una o dos máquinas que producen la mayor cantidad de piezas mientras que las otras no hacen casi nada. Para poder darnos cuenta de todo esto más fácilmente, vamos a dar formato personalizado a las barras de la columna G, a fin de que cada barra se pinte de un color diferente dependiendo de la máquina que trabajo ese día; de este modo, si un color aparece mayor número de veces que los demás, nos daremos mejor cuenta de que esa máquina está cargando con todo el trabajo de producción. Dar formato condici onal a la columna de la produc ci ón: Vamos a definir los siguientes colores para cada máquina: Máquina Color . SPX34 Verde RPX90 Rojo WPA80 Azul RPA72 Naranja 12. Seleccionar el rango G5:G15 13. Ficha Inicio/Formato condicional/Administrar reglas. 14. Hacer un clic en el botón “Nueva regla” 15. Elegir la opción: “Utilice una fórmula que determine las celdas para aplicar

formato”

16. Tal como se muestra en la figura anterior, escriba la fórmula: =B5=”SPX34” 17. Haga clic en el botón “Formato…”, y en la ficha Fuente elija el color Verde. Luego clic en Aceptar, y Aceptar otra vez.


Pág. 39


18. Vuelva a hacer clic en el botón “Nueva regla” y repita los pasos anteriores para dar color a cada una de las maquina utilizando las siguientes formulas: =B5=”RPX90” Formato… color Rojo. =B5=”WPA80” Formato… color Azul. =B5=”RPA72” Formato… color Naranja.

Finalmente Aceptar.

Entonces al ver los colores de las barra en cada día, nos damos cuenta que todo el trabajo lo estamos cargando a una sola máquina, la RPX90 de color rojo. La máquina verde SPX34 solo trabaja un día, la azul WPA80 aparece en tres días solamente, y la maquina RPA72 naranja no se la ve en ningún dia de trabajo. Entonces; ya viendo lo que ocurre, vamos a hacer una reprogramación de las cargas de trabajo para la producción de las piezas de plástico que necesitamos: 19. Seleccione la celda B15 y elija en la lista de validación la maquina naranja RPA72. 20. Seleccione la celda B8 y también elija la maquina RPA72. 21. En la celda B11 elija la maquina SPX34. Pág. 40



22. En las celdas D13:D14 escribir: 13:00 23. Finalmente seleccione la celda F16 y en la ficha Datos/Análisis Y si/Buscar objetivo escribir:

Entonces se tendrá el siguiente cuadro:

G ráfica y porcentaje acumulado de la producción:

24. En H5 escribir la siguiente formula: =REPETIR(“█”,SUMA(F$5:F5)/700)&” “&TEXTO(SUMA(F$5:F5)/$F$16,” 0.0 %” ) 25. Luego copiar esta fórmula hacia abajo y reducir el tamaño de letra a 8 puntos.


Pág. 41


C ontrol ontrol de la la P roducción roducc ión E jecutada jecutada::

26. En J5 escribir la siguiente formula: =SI(I5=””,””,I5-F5) 27. En K5 escribir la siguiente formula: =SI(I5=””,””,REPETIR(“█”,SUMA(F$5:F5)/60 =SI(I5=””,””,REPETIR(“█”,SUMA(F$5:F5)/600)&” 0)&” “&TEXTO(SUMA(F$5:F5)/$F$1 “&TEXTO(SUMA(F$5:F5)/$F$16,”0.0%” 6,”0.0%”)) )) Para apreciar cuando se atrasa o se adelanta la producción respecto a lo programado le daremos formato condicional a la columna columna de Diferencia. 28. Seleccionar el rango J5:J16 y en la Ficha Inicio/Formato condicional/Barra de datos/Más reglas:

Ahora solo restará ingresar las cantidades cantidades producidas producidas día a día en la columna I, y se verá el siguiente cuadro:

Pág. 42



3. Estudio de Mercado, y Análisis de Encuestas Un negocio de ventas de computadoras quiere incrementar sus ventas y ya no quiere esperar a que los clientes vengan a la tienda a comprar computadoras, sino va a implementar una campaña de ventas agresiva con sus clientes. Dado que los clientes que más compran computadoras portátiles LapTop son los estudiantes, entonces va a air a los centros de estudios de los estudiantes y allí m ismo ofrecerles las computadoras. Para esto se va a solicitar a las autoridades de estas casas de estudio un permiso a fin de que nos dejen ingresar con una mesa y un toldo donde poder realizar una demostración de los últimos y mejores modelos de computadoras y así poder venderle a la mayor cantidad de alumnos de estas universidades o centros de estudio. Pero el permiso que tendremos quizás solo sea por unos pocos días, y en ese pequeño tiempo tendríamos que hacer la demostración a la mayor cantidad de clientes posible y ofrecerles las computadoras de las marcas y a los precios que ellos están deseando, y para que esto se haga así, es necesario conocer de antemano lo siguiente: 





¿Qué grupo de alumnos son los que más nos comprarían computadoras; computador as; los alumnos de los primeros ciclos de estudio, de los ciclos intermedios, o de los últimos ciclos? ¿Qué marcas de computadoras son las que prefieren los alumnos: Toshiba, Lenovo, Vaio, Hp, etc? ¿Cuáles serían los precios que estarían dispuestos a pagar por una computadora, querrían las computadoras caras, las de precio intermedio, o las más baratas?

Dependiendo al centro de estudio al cual se vaya, cada grupo de estudiantes deseará diferentes características de la computadora que desean adquirir. Para averiguar las respuestas a estas preguntas, se van a realizar previamente unas encuestas a los alumnos de las diferentes universidades a las cuales vamos a realizar esta campaña de ventas. Para la encuesta se buscarán alumnos que hayan comprado recientemente una LapTop y se tomara una muestra muestra representativa de 35 alumnos en en cada centro de estudio, a los cuales se les preguntaría lo siguiente:   

¿En qué ciclo de estudios te encuentras? ¿Cuánto te costó la computadora que recién has comprado? ¿De qué marca es tu computadora? computadora?

Pero antes de realizar las encuestas reales, vamos a diseñar un modelo de simulación de encuesta, y analizarla mediante tablas de frecuencia y graficas de Histograma de Frecuencias, tal como se detalla a continuación:


Pág. 43


Simulación de una Encuesta para averiguar las predilecciones de los posibles clientes en una campaña de ventas: Construya los siguientes Cuadros en la hoja de cálculo:

Luego realice los siguientes pasos: 1. En B5 escriba: =ALEATORIO.ENTRE(1,10) 2. En C5 escriba: =ALEATORIO.ENTRE(800,700 =ALEATORIO.ENTRE(800,7000) 0) 3. En D5 escriba: =ELEGIR(ALEATORIO.ENTRE(1,7),"Toshiba","Lenovo","Vaio","HP","Acer","Dell", "Otras")

Pág. 44



4. Copiar estas 3 formulas hasta la fila 39. De este modo cada vez que presione [F9] se estará simulando las respuestas tomadas a los alumnos de un centro de estudios diferente. 5. Nombrar los siguientes rangos: 

Al rango B5:B39 nombrarlo como: Ciclos



Al rango C5:C39 nombrarlo como: Precios



Al rango D5:D39 nombrarlo como: Marcas



Al rango F5:F14 nombrarlo como: GrupoCiclos



Al rango F19:F26 nombrarlo como: GrupoPrecios

Calculo de la Frecuencia y Porcentaje de alumnos por C iclo:

6. Seleccionar G5:G14 y escribir la formula matricial: {=FRECUENCIA(Ciclos,GrupoCiclos)} 7. En H5 escribir: =G5/SUMA(G$5:G$14) Y luego copiar esta fórmula hasta la fila 14, y darle el formato de porcentaje con cero decimales. De este modo se tiene una tabla de frecuencias con la cantidad de alumnos de la muestra que están cursando cada uno de los ciclos de estudio, así como también el porcentaje de estos alumnos en cada ciclo.


Pág. 45


Ahora vamos a graficar los datos obtenidos de la encuesta: 8. Seleccionar el rango F4:H14 9. Ficha Insertar/Grafico/Columnas/Columna agrupada. 10. Seleccionar en la gráfica la 1ra columna de la serie Ciclo, y presione Suprimir para borrar esta serie.

11. Seleccionar la leyenda y borrarla con Suprimir 12. Ficha Diseño/ Agregar elemento del grafico/ Titulo del grafico/Encima del

Gráfico, y escribir “Histograma por Ciclos”

Pág. 46



13. Seleccionar la serie de Porcentajes (columnas pequeñas de color verde) y en la ficha Diseño/Cambiar tipo de gráfico, elegir en la pestaña del % Línea con marcadores y Aceptar.


Pág. 47


14. Seleccionar las líneas de la segunda serie (Porcentajes) y haga un clic derecho/Dar formato a serie de datos, y en la ventana que aparece seleccionar

el botón de opción “Eje secundario”, y Cerrar 15. Seleccionada la serie porcentaje, y elegir la Ficha Diseño/Agregar elemento del grafico / Etiqueta de datos/Encima.

16. Seleccionar las columnas y hacer un clic derecho/Dar formato a serie de datos, y en la ventana buscar la opción Ancho del intervalo y escribir 20%, luego clic en Cerrar.

17. Ficha Diseño/Seleccionar datos, en la ventana que aparece al lado derecho bajo

la opción “Etiquetas del eje horizontal” haga un clic en el botón Editar, y al

aparecer otra ventana que solicita por un rango de rótulos del eje, seleccionar con el mouse el rango F5:F14 (en la ventana se verá como: =Hoja1!$F$5:$F$14 , y clic en Aceptar, y Aceptar otra vez.

Pág. 48



18. Seleccione las columnas rojas, y en la ficha Formato/Estilos de forma, haga clic en el botón Más de los botones Abc, Abc y elija uno de los colores de la últi ma fila. 19. Luego seleccione la línea verde con triángulos, y dele formato como se muestra a continuación:

Histograma por Ciclos 8

25% 20%

7

20%

6

14%

5

15%

11%

4 3

14%

9%

9%

9%

6%

6%

2

3%

10% 5%

1 0

0% 1º

2º

3º

4º

5º

6º

7º

8º

9º

10º

Este Histograma por ciclos nos va a decir en que ciclo de estudios se encuentran los clientes que nos comprarían más computadoras.


Pág. 49


Calculo de la Frecuencia y P orcentaje de alumnos por precios :

20. Seleccionar G20:G26 y escribir la formula matricial: {=FRECUENCIA(Precios,GrupoPrecios)} 21. En H20 escribir: =G20/SUMA(G$20:G$26) Y luego copiar esta fórmula hasta la fila 26, y darle el formato de porcentaje con cero decimales.

De este modo se tiene una tabla de frecuencias con la cantidad de alumnos según el precio de la computadora, así como también el porcentaje de estos alumnos según el precio.

De forma similar siguiendo los mismos pasos del grafico anterior, crear un gráfico de

“Histograma por Precios”, el cual deberá quedar tal como se muestra a continuación:

Pág. 50



Calculo de la Frecuencia y P orcentaje de alumnos por Marcas:

22. Seleccionar G32:G38 y escribir la fórmula: =CONTAR.SI(Marcas,F32) 23. En H32 escribir: =G32/SUMA(G$32:G$38) Y luego copiar esta fórmula hasta la fila 38, y darle el formato de porcentaje con cero decimales. De este modo se tiene una tabla de frecuencias con la cantidad de alumnos según la marca que prefieren de computadora, así como también el porcentaje de estos alumnos según la marca.

De forma similar siguiendo los mismos pasos de los gráficos anteriores, crear un

gráfico de “Histograma por Marcas”, el cual deberá verse tal como se muestra en la siguiente figura:

Terminado los cálculos y gráficos, y ordenada toda la hoja de cálculo deberá verse así:


Pág. 51


Una vez que se realicen las encuestas reales en cada centro de estudios, bastara con ingresar los resultados de estas en las columnas C,B y D, y automáticamente se calcularán las tablas de frecuencias y se graficarán los histogramas, y de este modo podríamos saber en cada universidad a que grupo de alumnos deberíamos ofrecer nuestras computadoras, y que precios y marcas deberíamos ofrecer en la demostración. De esta manera nuestra campaña de ventas sería mucho más exitosa.

Pág. 52



4. Buscar Objetivo Cuando se desea obtener un resultado deseado en una formula y para ello es necesario ajustar el valor de un dato que necesita la fórmula para calcularse, entonces se puede utilizar la opción Buscar objetivo, que se ubica en Datos, Grupo Previsión / Análisis de hipótesis.

E jercicio Nº 1: C álculo de Utilidades en un Proyecto de Producci ón y Venta En una hoja de cálculo se desean calcular los costos y utilidades para el siguiente caso: Se proyecta la fabricación de 125 carteras. Si los gastos de materiales y mano de obra son de 15 y 10 soles respectivamente, cuál sería la utilidad obtenida si las carteras se venden a un precio de 45 soles cada una. Otros datos: El 5% de la producción sale defectuosa Solo se logra vender el 90% de la producción Los vendedores ganan una comisión de S/.4 por cada cartera

En B17 escribir: En D16 escribir: En D17 escribir:

=ENTERO (B16*95%) =B16*C16 =B17*C17

En D18escribir: En B21 escribir: En D21 escribir:

=SUMA (D16:D17) =ENTERO (B17*95%) =B21*C21


Pág. 53


=B21*C22 En D22 escribir: Entonces el cuadro de costos se vería así:

Una vez obtenido los cálculos, se desea incrementar las utilidades sin incrementar el monto de la inversión. Para ello sería necesario aumentar el precio de venta, o en todo caso reducir los costos de producción o de ventas. Si la meta es obtener una utilidad de 1000 soles, y para ello se decide cambiar el precio de venta; entonces es cuando se puede utilizar la función buscar objetivo de Excel. Para ello deberá hacer lo siguiente: 1.- Seleccionar la celda D27 donde se encuentra el cálculo de la utilidad. 2.- En la ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis de Hipótesis y después en Buscar objetivo. 3.- En el cuadro Definir la celda, verifique que este escrita la referencia de la celda que contenga la fórmula que desea resolver. (En el ejemplo estará la celda D27) 4.- En el cuadro Con el valor, especifique el resultado que desee. (En el ejemplo sería -1000) 5.- En el cuadro Cambiando la celda, indique la referencia de la celda que contenga el valor que desee ajustar. (En el ejemplo sería la celda C21) 6.- Al hacer clic en Aceptar aparecerá en la celda C21 el valor del precio de venta de 50-28 soles que nos haría llegar al objetivo deseado de 1000 soles en la utilidad.

Pág. 54



Si no se quiere cambiar el precio de venta, sino que se decide mejor reducir los costos comprando el material a un precio más barato, o cambiando el pago de la mano de obra, entonces; vuelva a escribir 45 soles en el precio de venta y seleccionando la celda D27 de la utilidad, llame al comando Buscar objetivo y elija como celda cambiante la del precio del material (la celda C16), o la del costo de mano de obra (celda C17), En cuyo caso Excel volverá a obtener la utilidad de 1000 soles pero en esta ocasión cambiando los valores del costo de material o del pago de la mano de obra respectivamente.

5. Tablas para Análisis de sensibilidad: Otra forma de analizar los resultados de las diferentes utilidades que generarían las variaciones en los costos, es haciendo uso del análisis de sensibilidad, creando para ello tablas de una o de dos variables de entrada, que se ubica en Datos, Grupo Previsión / Análisis de hipótesis.

a. Tabla de una variable de entrada Para poder darnos cuenta cómo es esto, en la misma hoja de cálculo anterior escriba la siguiente tabla:

Lo que se desea es calcular los diferentes totales para Materiales, Utilidades y % de Rentabilidad, cuando se adquiera el material a los diferentes costos unitarios mostrados en la primera columna de la tabla. Para ello haga usted lo siguiente: 1.- Escriba las siguientes formulas: En la celda C37: =D16


Pág. 55


En la celda D37: =D27 En la celda E37: =D28 2.- Seleccionar el rango B37:E41 y en la ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis de Hipótesis y después, en Tabla de datos…:

3 . - En la casilla Celda de entrada columna escribir: C16, que es la celda donde se desea reemplazar los diferentes costos unitarios del material, y luego clic en Aceptar. Entonces aparecerán los siguientes resultados en la tabla de sensibilidad: Costo Unit.

S/.

15.00 13.00 12.00 10.00

Materiales

S/. S/.

1,875.00 1,875.00 1,625.00 1,500.00 1,250.00

Utilidad

% Rentab.

S/. 1,000.00 S/. 1,000.00 1,250.00 1,375.00 1,625.00

33% 33% 45% 51% 67%

b. Tabla de dos variables de entrada La otra opción para analizar la sensibilidad de los resultados es variando dos diferentes datos del cuadro de costos, por ejemplo, el costo unitario del material y el precio de venta de las carteras. En la misma hoja de cálculo anterior escriba ahora tal como se muestra la siguiente tabla:

Pág. 56



Luego haga usted lo siguiente: 1.- Escriba la siguiente formula en la celda A55: =D27 2.- Seleccionar el rango A55:E59 y en la ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis de Hipótesis y después, en Tabla de datos…:

2.- En la casilla Celda de entrada fila escribir: C21, que es la celda donde se desea reemplazar los diferentes precios de venta de las carteras, y en la, Celda de entrada columna escribir: C16, que es la celda donde se desea reemplazar los diferentes costos unitarios del material; luego haga clic en Aceptar. Entonces se tendrán los siguientes resultados en esta otra tabla de sensibilidad: Precio Unit

S/. S/.

529.40 15.00 13.00 12.00 10.00

Precios de Venta

S/. 45.00 529.40 779.40 904.40 1,154.40

S/. 46.00 618.48 868.48 993.48 1,243.48

S/. 47.00 707.55 957.55 1,082.55 1,332.55

S/.

48.00 796.63 1,046.63 1,171.63 1,421.63

Para identificar claramente cuáles son las combinaciones de costo y precio de venta que más nos convienen podemos dar formato condicional a las diferentes utilidades. Para lo cual haga usted lo siguiente: 1.- Seleccionar el rango B56:E59 y elija la ficha Inicio/Formato condicional/Resaltar

reglas de celda/Es mayor que…

2.- En la ventana que aparece, escribir la cantidad 1000, y en la casilla desplegable elegir Formato personalizado.


Pág. 57


Luego en la Ficha Relleno elegir el color celeste, y Aceptar.

Luego Aceptar otra vez. Y entonces se obtendrá el siguiente resultado en la pantalla:

Pág. 58



6. Administrar Escenarios Los escenarios en Excel, son una forma de presentar en pantalla a través de un solo cuadro, varias combinaciones de datos y resultados, sin necesidad de tener múltiples copias del mismo cuadro. Esta herramienta se ubica en Datos, Grupo Previsión / Análisis de hipótesis. Para entender mejor esto, veamos en el análisis de costos anterior como podríamos comparar los resultados para tres combinaciones de datos según siguientes opciones:

Opción Pesimista: Compra de material a: Venta del producto a:

15 soles 42 soles

Opción Optimista: Compra de material a: Venta del producto a:

10 soles 50 soles

Opción Probable: Compra de material a: Venta del producto a:

13 soles 46 soles

Entonces haga usted lo siguiente: 1.- Elegir la Ficha Datos/Análisis de hipótesis / Administrador de Escenarios:

2.- En la ventana de dialogo que aparece, hacer clic en el botón Agregar, escriba el nombre del primer escenario y elija las celdas de datos cambiantes, y luego Aceptar:


Pág. 59


3.- Escriba los valores de las dos celdas cambiantes, 15 soles para el costo del material, y 42 soles para el precio de venta del producto, y luego Aceptar.

4.- Repita los pasos 2 y 3 para los otros dos escenarios: el de la Opción Probable, y el de la Opción Optimista. Entonces se tendrá lo siguiente en la ventana del Administrador de Escenarios:

Para ver los es cenarios en la pantalla:

5.- A continuación, para ver en la hoja de cálculo la combinación de datos y resultados de cada uno de los escenarios, bastará con que seleccione en la ventana Administrador de escenarios el un nombre de uno de ellos, y luego haga clic en el botón Mostrar. Para ver un cuadro res umen comparativo de todos los escenarios :

6.- En la ventana Administrador de escenarios haga clic en el botón Resumen… y en la ventana de dialogo que aparece, elija o ingrese las coordenadas de celda donde se encuentran los resultados que desea comparar en el cuadro resumen. Luego clic en Aceptar.

Pág. 60



Entonces se insertará una nueva hoja de cálculo en el libro actual, y allí aparecerá el cuadro resumen siguiente:

E jercicio Nº 2: Obtención de diferentes escenarios para un mis mo cuadro En la hoja de cálculo Escenarios del ejemplo anterior, crear los siguientes 3 escenarios: En Lima: Compra de material a: 14 soles Mano de obra: 10 soles % de impuesto IGV : 18% Venta del producto a: 48 soles


Pág. 61


En Bogotá: Compra de material a: Mano de obra: % de impuesto IGV: Venta del producto a: En Caracas: Compra de material a: Mano de obra: % de impuesto IGV: Venta del producto a:

Pág. 62

8 soles 11 soles 16% 51 13.5 soles 6 soles 12% 44 soles



7. Diagrama Spider para analizar porcentajes de desviación Son curvas de sensibilidad donde se grafica la variación de los diferentes indicadores en función de la desviación respecto de los valores básicos asumidos. Permiten visualizar las variables a las que se deberá prestar mayor atención, siendo éstas las que producen mayor variación en los resultados debido a relativamente pequeños desvíos en sus valores. Para obtener estos diagramas se asumen como constante todos los parámetros, con excepción de aquel cuya influencia quiere estudiarse, el cual se va variando en forma proporcional de modo tal que todos sus valores quedan multiplicados por un mismo factor mayor o menor que 1 según sea el caso de desviación en mas o en menos que se desee.

E jercicio Nº 3: Obtención de un Diag rama S pider 1. Abrir el archivo Diagrama Spider de desviación.xls el cual mostrará un análisis de costos parecido al anterior ejercicio.

2. En la parte inferior del cuadro de costos encontrará 4 tablas de análisis de sensibilidad donde se deberán calcular las utilidades haciendo variar individualmente cada uno de los siguientes elementos del cálculo de costos: Producción

Material


Mano de Obra

Precio de Venta

Pág. 63


3. Escriba las siguientes formulas:

a. En B26 : =B8 b. En B24: =B$26*(1+A24) y cópiela en las celdas B25, B27 y B28 4. Copie el rango B24:B28 en los rangos G24:G28, B32:B36, y G32:G36. 5. Seleccione el rango B24:B28 y cópielo como valores en el rango C24:C28 (utilice la orden copiar y luego pegado especial, valores) 6. Haga lo mismo anterior con los rangos G24:G28, B32:B36, y G32:G36., luego de lo cual se tendrá en pantalla lo siguiente:

7. Seguidamente se deben obtener los cálculos de las utilidades en cada tabla de sensibilidad, empezando por la de Producción: a. Seleccione el rango C23:D28 y elija la Fi cha Datos, G rupo Previs ión / A nális is de hipótes is / Tabla de datos Pág. 64



b. En la casilla Celda de entrada (columna) escriba la coordenada de celda B8, y luego Aceptar. Con lo cual se habrán calculado las diferentes utilidades en la tabla al variar las unidades producidas.

8. Repita los mismos pasos anteriores para las tablas de Material, Mano de Obra y Precio, poniendo como Celda de entrada columna las celdas C7, C8 y C12 respectivamente. Entonces se tendrá lo siguiente en la pantalla:

9. En las siguientes filas de la hoja de cálculo se tiene ya armado un cuadro que recoge los datos de las tablas de sensibilidad y las grafica tal y como se aprecia a continuación:

10. El diagrama Spider que se obtiene al seleccionar el rango C39:G44 es el siguiente: a. Tipo de gráfico: Dispersión XY


Pág. 65


b. Eje vertical cruza en:

-0.3

Diagrama Spider

2000

Precio

1800 1600

Producción 1400 1200 d a d i l i t U

1000 Mano Obra 800 600

Material

400 200 0 -30%

-20%

-10%

0%

10%

20%

30%

Desvío

Como puede verse en el gráfico, suponiendo que bajo las condiciones actuales de costos se viene recaudando una utilidad de 1000 soles (punto 0% de desviación), entonces se puede afirmar que: 



Pág. 66

Un porcentaje de aumento en el Precio de venta del producto, produciría mayor utilidad que el mismo porcentaje de aumento en la Producción. Por ejemplo, aumentar 10% al Precio de venta nos daría más utilidades que aumentar 10% en las unidades producidas. Asimismo, un porcentaje en la disminución de los gastos de Mano de Obra, produciría menos incremento en la utilidad que el mismo porcentaje de disminución en el costo del material. Esto es por ejemplo, reducir un 10% en el pago de Mano de Obra, nos daría menos utilidad que reducir un 10% en la compra de materiales.



8. Cálculo y Gráfica del Punto de Equilibrio En una empresa de producción o de servicios, se desea calcular cual es la cantidad mínima de artículos a producir, o unidades de servicio impartidas mínima, para cubrir los costos y a partir de las cuales se empezaría a percibir utilidades.

E jercicio Nº 4: Obtención del Punto de E quilibrio 1. Abrir el archivo Punto de Equilibrio.xls se verá el siguiente análisis de costos con el cálculo y gráfica del punto de equilibrio de una empresa de producción.

Nombrar los siguientes rangos: D3 D8 D6 B6 D11 C6 B15 B11



 

    

GF GTotal GV GVU IngNeto Prod PtoEq PVU

Calculo del Punto de Equilibrio 2. Las fórmulas que existen en las celdas son las siguientes: Elaborado por Daniel Zegarra y William Chuquin

Pág. 67


En el Cuadro de Costos: D6: =B6*C6 D8: =D3+D6 C11: =C6 D11: =B11*C11

D13: =D11-D8 C15: =D3/(B11-B6) B15: =REDONDEAR.MAS(C15,0)

En la tabla de datos para graficar: En A17: ="Prod: "&Prod&" Unid" En A18: 0 En A19: =SI(B15*2>Prod,B15*2,Prod*1.25) En A20: =B15 En A21: =B15 En A22: =Prod En A23: =Prod En A24: =Prod En A25: =Prod En B18: =GF En B19: =GF En C18: =GVU*A18+B18 En C19: =GVU*A19+B19 En D18: 0 En D19: =PVU*A19 En E20: 0 En E21: =PtoEq*GVU+GF En F22: =GTotal En F23: =IngNeto En G24: 0 En G25: =GTotal 3. Seleccionar el rango de la tabla de datos A17:G25, y elija la Ficha Insertar/ Gráficos/ Dispersión/ Dispersión con líneas rectas.

Pág. 68



4. Se obtendrá entonces el siguiente gráfico:

5. Ahora solo hay que seleccionar cada elemento del gráfico y darle el formato adecuado hasta obtener el siguiente resultado:


Pág. 69

Ms. Excel Producción y Administración 2016 PUNTO DE EQUILIBRIO Ingresos Netos; 250; S/. 22,689.08 S/. 5,651.26 GFijo + G.Variable; 250; S/. 14,500.00 Utilidad; 200; S/. 12,500.00

s e l o S

P.Eq: 89 S/. -

G.Fijo; 250; S/. 4,500.00 Prod: 200 S/. -

Unidades

6. Adicionalmente en las celdas I18:I21 se creará un grupo de datos que también se adjuntará al gráfico anterior. Las formulas en estas celdas son las siguientes: En I19: ="G.Var.Unit.: "&B6 En I20: =" Precio Vta.: "&B11 En I21: =" U.Vendidas: "&C11 7. Para adjuntar estos datos al gráfico, primeramente seleccione el rango I18:I21 y presione las teclas Ctrl+C para copiar. 8. Luego seleccione la celda k18 y elija la Ficha Inicio/ Pegar/ Imagen Vinculada(M).

Pág. 70



9. Aparecerá una copia del rango seleccionado, tal como se ve a continuación.

10. Seguidamente arrastre esta imagen y ubíquela sobre el grafico del punto de equilibrio, tal como se muestra en la siguiente figura, con lo cual se tendrá así terminada la gráfica del punto de equilibrio.

11. Los valores de este grafico dependen de los datos del análisis de costos calculados en las celdas de la hoja de cálculo. Entonces, si variamos los datos del análisis de costos, inmediatamente se podrán apreciar estos nuevos resultados en la gráfica del punto de equilibrio. Para realizar estos cambios más cómodamente se podrán agregar botones de formulario del tipo “Control de número” como se pueden apreciar junto a algunas celdas del cuadro de costos.


Pág. 71


9. Gráfica del Diagrama de Gantt El diagrama de Gantt es una herramienta gráfica que nos muestra mediante barras horizontales el tiempo previsto que tendrán diferentes actividades a lo largo de un tiempo total cuyos límites están determinados. Este diagrama se compone de un eje vertical que muestra las actividades que se van a efectuar para llevar a cabo un proyecto, y un eje horizontal que nos muestra mediante fechas del calendario la duración de cada una de estas actividades en unidades de tiempo.

E jercicio Nº 5: C reación de un Diag rama de G antt 1. Abrir el archivo Diagrama de Gantt.xlsx el cual mostrará una tabla de actividades, fecha de inicio de las actividades, tiempo de ejecución y fecha final de cada actividad.

2. Seleccionar las tres primeras columnas del rango A4:C14 3. Elegir la Ficha Ins ertar/G rafic o de columnas o de barr as/B arra apilada.

Pág. 72



4. Mueva el grafico para ubicarlo sobre el rango E1:M17

5. Elegir la Ficha Di s eño/S elecci onar datos ; y luego haga clic en el botón Editar .


Pág. 73


6. Con el mouse seleccionar el rango de las actividades A5:A14 y Aceptar.

7. Luego haga clic en el botón A g reg ar y en la ventana que aparece seleccione estos rangos:

Cuando haga clic en Aceptar, aparecerá la siguiente ventana:

8. Seleccione del lado izquierdo la serie Inicio y hágalo subir encima de la serie Dias haciendo clic en el botoncito de triángulo que se encuentra a la derecha del botón Quitar. Luego Aceptar. Cuando haga clic en Aceptar, la gráfica deberá verse así:

Pág. 74



9. Haga doble clic sobre el eje vertical de actividades y en el panel de tareas de la derecha de la pantalla active la casilla C ategorí as en orden invers o .

10. Luego seleccione las barras horizontales de color naranja y elija la Ficha Formato/R elleno de forma/S in relleno.

11. Escriba en las celdas lo siguiente: a. En A16: Limites b. En B16: =B5-2 c. En D16: =D14+2 12. Cuadricule las celdas con la herramienta de Bordes/ Todos los bordes , y a B16 y D16 dele el formato General, tal como se muestra en la figura de la derecha. 13. En el gráfico, haga doble clic sobre la escala de fechas de la parte superior, y en el panel de tareas de la derecha de la pantalla Elaborado por Daniel Zegarra y William Chuquin

Pág. 75


escriba los Límites Mínimo y Máximo calculados en las celdas B16 y D16, para que las barras del grafico queden centradas, tal como se ve en la siguiente figura.

14. Con la Ficha Di s eño/A g reg ar elemento de g ráfico/Titulo del g ráfico/E ncima del gráfico, escriba el título: DIAGRAMA DE GANTT y de 18 ptos de tamaño.

15. Seleccione la escala superior de fechas en el gráfico y elija en la Ficha Inicio/Orientación ab/Girar texto hacia arriba. 16. Señale la escala superior de fechas y haga clic derecho/Dar formato al eje… para que aparezca el panel de tareas a la derecha de la pantalla 17. Escriba en el panel de tareas, Opciones del eje/Unidades/Mayor : 1

Pág. 76



18. Escriba en el panel de tareas, Número/C ódig o de formato : dd mmm, y luego clic en el botón Agregar. 19. Luego elija en la Ficha Dis eño/A g reg ar elemento de grafico/Líneas de cuadrícula/Horizontal pri nci pal pri mario .

20. Señale las barras horizontales, clic derecho/D ar formato a serie de datos… 21. En el panel de tareas de la derecha, en Opciones del eje/A ncho del intervalo, poner 40% y presionar Enter. 22. Elegir en la Ficha Formato/E fectos de forma/Prees tablecer , y escoja un modelo en Preestablecidos. 23. Señale las barras horizontales, haga clic derecho y elija A g reg ar etiquetas de datos . 24. Seleccione las etiquetas, y dele formato de 10 ptos y negrita. 25. Finalmente, de formato de color al fondo del gráfico y al área de trazado.


Pág. 77


Macro para centrar las barras del grafico al cambiar los tiempos y fechas: Si se modifican en el cuadro de datos las fechas y los tiempos de duración de las actividades entonces, las barras horizontales del grafico se desfasan respecto a la escala superior del tiempo saliéndose del área del gráfico. Para corregir esto, hay que modificar los límites del grafico adecuándolos a las nuevas fechas de inicio y fin de las actividades; pero esta acción hay que hacerlo manualmente en el panel de tareas. Para evitar que tengamos que hacer esta labor manualmente, se puede crear la siguiente macro: 1. Seleccionar una celda vacía en la hoja de cálculo (por ejemplo B18) 2. Ficha Desarrollador/Grabar macro 3. Nombre de la macro: Limites 4. Luego clic en el botón Aceptar. Con esto se encenderá la grabadora de macros. 5. Entonces realice lo siguiente: a. Clic derecho en la escala superior y elija Dar formato al eje… b. En el panel de tareas de la derecha escribir nuevos límites Mínimo y Máximo y presione Enter. c. Haga clic en la celda vacía B18 d. Clic en el botón Detener grabación. 6. Ingrese a Visual Basic con Alt+F11 y modifique la macro como se indica a continuación, escribi endo Range(“B16”) y Range(“D16”) a la derecha de las dos escalas: Sub Limites() ' ' Limites Macro ActiveSheet.ChartObjects("Gráfico 2").Activate ActiveChart.Axes(xlValue).Select ActiveChart.Axes(xlValue).MinimumScale = Range(“B16”) ActiveChart.Axes(xlValue).MaximumScale = Range(“D16”) Range("B18").Select End Sub

7. Regrese a Excel con Alt+F11 8. Dibuje una figura en la celda C16 y asígnele la macro Limites con Clic derecho/Asignar macro. Si ahora se modifica la fecha en la celda B5, o se cambian los días en la columna C, entonces, solo hay que hacer clic en el botón de la macro para centrar el grafico de Gantt.

Pág. 78



Capítulo 3 1. Líneas de Tendencia en los Gráficos a. Utilidad de las líneas de tendencia tendencia Las líneas de tendencia se usan para mostrar gráficamente las tendencias de los datos y analizar los problemas de predicción. Este análisis también se denomina análisis de regresión. Mediante el uso del análisis de regresión, puede representarse una línea de tendencia en un gráfico más allá de los datos actuales para predecir los valores futuros. Por ejemplo, en el siguiente gráfico se utiliza una línea de tendencia simple que muestra la previsión para cuatro trimestres indicando una clara tendencia de aumento en los ingresos. Media móvil. - También se puede crear una media móvil, que suaviza las fluctuaciones en los datos y muestra la trama tr ama o tendencia con más claridad.

b. Tipos de gráfico gráfico que admiten líneas de tendencias Pueden agregarse líneas de tendencia a las series de datos en los siguientes gráficos: De áreas 2D no apiladas, • De barras horizontales y columnas verticales • De líneas, • De cotizaciones, • De tipo XY (Dispersión), y • De burbujas. • No pueden agregarse líneas de tendencia a las series de datos en los gráficos 3D, radiales, circulares, de superficie o de anillos.


Pág. 79


Si se cambia un gráfico o una serie de datos de modo que ya no permita la línea de tendencia asociada (por ejemplo, si se cambia el tipo de gráfico por un gráfico de áreas 3D o si se cambia la vista de un informe de gráfico dinámico o de un informe de tabla dinámica asociado), se perderán las líneas de tendencia.

c. Agregar una línea de tendencia tendencia a una serie de datos datos 1.- En el gráfico, haga clic en la serie de datos en la que desea agregar la línea de tendencia o la media móvil. 2.- En el Ficha Presentación/Análisis/Línea de tendencia, elija la clase de línea de tendencia que desee o haga clic en la l a media móvil que desee. Si se selecciona Polinomial, adicionalmente introduzca en el cuadro Orden el valor potencial que corresponda. Si se selecciona Media móvil, introduzca en el cuadro Período el número de períodos que va a utilizarse utilizarse para calcular la media móvil. Ejemplo Para analizar las proyecciones en un gráfico de líneas; también puede agregar una línea de tendencia de la siguiente forma: una vez creado el grafico señalar con el puntero del mouse la línea y llamar al menú contextual con un clic derecho del mouse, luego elegir la opción Agregar línea de tendencia.

Pág. 80



En la ventana de diálogo elegir en Tipo de tendencia la opción Lineal y marcar la casilla Presentar ecuación en el gráfico, luego [Cerrar]. Se verá entonces el grafico anteriormente mostrado. (Si se reemplaza “x” en la ecuación por el número del mes se obtienen los

pronósticos de las ventas de esos meses.) Si luego de tener la línea de tendencia en el gráfico, se ingresan los valores de venta de los meses faltantes, la línea de tendencia, así como la ecuación se ajustarán en el gráfico actualizándose a estos nuevos valores. Posteriormente se puede agregar formato a los elementos del gráfico, así como a la línea de tendencia y a la ecuación en el gráfico, tal como se ve en la f igura. Otras formas en las que se puede mostrar el mismo análisis de ventas con la línea de tendencia se muestran a continuación en los gráficos siguientes:


Pág. 81


En un gráfico de Áreas:

En un gráfico de Barras:

Tenga en cuenta lo siguiente: Si se agrega una media móvil a un gráfico XY (Dispersión), la media móvil se basará en el orden de los valores X trazados en el gráfico. Para obtener el resultado deseado, puede ser necesario ordenar los valores X antes de agregar una media móvil. Las líneas de tendencia no se mantienen en los informes de gráfico dinámico si se modifica la vista del gráfico o de su informe de tabla dinámica asociado. Antes de agregar líneas de tendencia u otros elementos de formato a un informe de gráfico dinámico, asegúrese de que el diseño es satisfactorio.

Pág. 82



2. Análisis de Regresión Cuando se tengan datos o resultados históricos de ciertos eventos realizados, y se desee conocer cuáles serán los resultados futuros para eventos semejantes, entonces; se puede utilizar el método gráfico, o sino el método matemático para llegar a esos resultados deseados de manera estimada. 

Se pueden pueden graficar graficar los datos y luego agregar agregar una una línea de tendencia. tendencia.



Se pueden utilizar las funciones: Estimacion.Lineal, Pronostico, o Tendencia

a. Cálculo Grafico de Pronóstico con las líneas de tendencia Si se tienen los datos; lo que se hace es graficarlos, y luego agregar una línea de tendencia y mostrar la ecuación en el gráfico. Seguidamente calcular los datos del pronóstico reemplazando los valores de x en la ecuación obtenida en la gráfica de la línea de tendencia. Para entender mejor este método veamos el siguiente ejercicio.

E jerci cio ci o Nº 5: C álculo g ráfic ráfico o de de V enta entas Futuras 1. Crear el siguiente cuadro cuadro de ventas ventas para el año 2009, e ingrese ingrese los datos de las ventas en la columna B solo hasta el mes de Junio. 2. Seleccione el rango rango A3:B15 y cree cree el grafico de tipo Dispersión tal como como se muestra en la figura. Luego agregue una línea de tendencia y muestre la ecuación en el gráfico. 3. Teniendo la ecuación ecuación de la línea de tendencia, escriba en la celda celda C10 la fórmula: =657.1*A10+3033 =657.1*A10+3033 y luego copie copie esta fórmula para los demás meses. meses. 4. Finalmente seleccione seleccione el rango C3:C15 C3:C15 y presione Ctrl+C, luego haga un clic dentro del gráfico y presione Ctrl+V. Entonces el grafico se verá como en la siguiente figura.


Pág. 83


b. Cálculo Matemático de Valores Futuros Abrir el archivo Estimación Lineal, Pronostico y Tendencia.xls. En este archivo se tendrá lo siguiente:

Calculo de la Ecuación de la recta de línea de tendencia: La ecuación de la recta tiene la siguiente forma:

y = mx + b . Para calcular el valor de la pendiente de la recta (m) y el valor de la constante independiente (b), se utilizan matemáticamente las siguientes fórmulas

matemáticas: Pero si se cuenta con Excel, el uso de estas fórmulas personalmente ya no es necesario, al menos no directamente, ya que existen varias funciones que realizaran el cálculo por nosotros, como veremos a continuación.

Pág. 84



c. La función ESTIMACION.LINEAL Calcula los valores de m y b, de la ecuación de una recta. Esta función es una formula matricial, por lo que al terminar de escribirla se deberá presionar Ctrl+Shift+Enter. Sintaxis: ESTIMACION.LINEAL(y_conocido,x_conocido)

y_conocido x_conocido

Es el rango de valores históricos ya conocidos de Y. Es el rango de valores históricos ya conocidos de X.

1. Seleccionar el rango B8:C9 y escribir la función: =ESTIMACION.LINEAL(C13:C16,B13:B16) y luego presionar Ctrl+Shift+Enter para que ingrese como una formula matricial. 2. Teniendo los valores ya calculados de m y b, entonces; en la celda D17 escribir la fórmula: =B$9*B17+C$9 y luego copiarla hacia abajo hasta la fila 20. Con esto se habrán calculado los valores desconocidos de y.

d. La función PRONOSTICO Calcula los valores futuros de Y para nuevos valores de X, basándose en valores conocidos de Y, y valores conocidos de X. Es probable que esta definición de la función les haya parecido un trabalenguas, pero la esta función calcula los mismos valores de Y que se obtuvieron con la ayuda de la función Estimacion.Lineal. Para poder entender mejor lo que hace la función veamos lo que sigue a continuación:


Pág. 85


Sintaxis:

PRONOSTICO(x,y_conocido,x_conocido) x y_conocido x_conocido

Es el nuevo valor de x. Es el rango de valores históricos ya conocidos de Y. Es el rango de valores históricos ya conocidos de X.

E jercicio Nº 6: Calculo del pronós tico de acci dentes de transito 1. Crear el siguiente cuadro de accidentes de tránsito en una determinada ciudad, e ingrese los datos de las veces que ocurrieron accidentes en los primeros días del mes solo hasta el 13 de Agosto.

2. En la celda C4 escriba la siguiente fórmula: =SI(ESBLANCO(B4),PRONOSTICO(A4,B$4:B$15,A$4:A$15),NOD()) y luego copiarla hacia abajo hasta la fila 15. Con esto se habrán calculado los valores de pronóstico de accidentes hasta fin de año. 3. Con el fin de que no se vean los valores de error #N/A, seleccione el rango C4:C15 y dele formato condicional siguiente: Ficha Inicio/ Formato condicional/ Nueva regla/ Aplicar formato únicamente a las celdas que contengan. En la casilla de lista desplegable elegir Errores, y en el botón Formato elegir color de fuente blanco.

Pág. 86



4. En la columna D escribir las siguientes formulas: =B4 En D4: =D4+SI(B5="",C5,B5) luego copiar esta fórmula hacia En D5: abajo.

Para crear el grafico de pronóstico, línea de tenencia, y acumulado 5. Seleccionar el rango A3:D15, elegir la ficha Insertar/ Gráficos/ Dispersión/ Dispersión con líneas y marcadores.


Pág. 87


6. En la gráfica hacer clic derecho en la línea del Acumulado, y elegir la opción Dar formato a serie de datos, luego elegir la opción Eje secundario, y Cerrar. 7. Seleccionada la línea de Acumulado, elegir en la ficha Diseño/ Cambiar tipo de gráfico/ Columna/ Columna agrupada. Luego en la ficha Presentación/Leyenda elegir la opción Mostrar leyenda en la parte inferior.

8. En la línea Fallecidos hacer clic derecho/ Agregar línea de tendencia/ Lineal. Estilo de línea/ Tipo de guión/ Guión. 9. Seleccionar la línea Pronóstico, y en la ficha Diseño/Cambiar tipo de gráfico/ Dispersión/ Dispersión solo con marcadores. 10. En la leyenda seleccionar Lineal (fallecidos) y borrarlo con la tecla Suprimir. 11. Seleccionar la serie Acumulado y en la ficha Formato/Estilos de forma, hacer clic en el botón Más y elegir uno de los colores de la última fila Efecto intenso – Énfasis. 12. Clic derecho en el Eje Secundario, elegir Dar formato a eje, luego marcar la opción Máxima/Fija y escribir el valor 4500. 13. Clic derecho en el Eje Horizontal, elegir Dar formato a eje, luego marcar Mínima/Fija y escribir el valor 39814, y en Máxima/Fija escribir el valor 40178.

Pág. 88



14. Finalmente con la Ficha Diseño/ Agregar elemento del gráfico y utilizar los comandos Titulo del gráfico, y Títulos del eje, para escribir el título al gráfico, y los títulos de los ejes Principal y Secundario. Además darle formato de color al área del fondo del gráfico y ponerle esquinas redondeadas.

Entonces el gráfico se vería así:

Pronóstico de Accidentes en Lima - Año 2009 400

4,500

350

4,000

s 300 e M r 250 o p s o 200 d i c e l l 150 a F 100

3,500 3,000 2,500 2,000 1,500

o ñ a l e d o d a l u m u c A

1,000

50

500

0

0 Ene.

Feb.

Mar.

Abr.

May.

Jun.

Acumulado


Jul.

Ago.

Fallecidos

Set.

Oct.

Nov.

Dic.

Pronóstico

Pág. 89


Capítulo 4 10. Simulación de Negocios Si se tiene información histórica de los resultados de un negocio, y se desea saber cómo nos podría ir en el futuro, entonces ya vimos que se puede recurrir a los análisis de regresión con las funciones ESTIMACION.LINEAL, PRONOSTICO y TENDENCIA. Pero qué pasa si se quiere emprender un negocio y no se cuenta con información histórica, pues nunca antes se había realizado un negocio de esta clase, o al menos es la primera vez que usted lo va a realizar, pero desea saber cómo nos podría ir, y cuanto se podría ganar con él. Entonces lo que se puede hacer es crear un modelo de simulación de ese negocio. Lograr que Excel simule que se está llevando a cabo la realización de ese negocio simulando que vienen los clientes día a día, que a veces se vende más y otros días menos, que los precios varían en el mercado, y que los ingresos oscilan entre tal y cual cantidad. Es decir llevar a cabo el negocio de manera virtual a través de la computadora para ver cómo nos podría ir, sin necesidad que hagamos realmente la inversión del tiempo y del dinero. De este modo y según como se desenvuelva usted con este negocio en la pantalla, podremos saber si este conviene llevarlo a cabo o no. Es algo así como el juego de Monopolio que simula un negocio de Bienes Raíces, o los juegos de computadora que simulan que usted esta piloteando un avión o está en una guerra con naves extraterrestres, o los juegos de estrategia. Para lograr esto con Excel, se va a recurrir a una función llamada ALEATORIO, la cual se encargara de simular las diferentes situaciones que podrían darse en los negocios, creando valores al azar entre determinados límites según la clase de negocio que se desea realizar. Por ejemplo, yo no sé cuantos clientes podrían venir a mi negocio en un día, o cuantos productos saldrán defectuosos, o cual será la variación del precio día a día; pero si se que estas cantidades oscilancada una entre un mínimo y un máximo estimado. Entonces, que la función ALEATORIO genere estos números al azar dentro de esos márgenes, y de este modo crearemos modelos de simulación.

a. La función ALEATORIO y ALEATORIO.ENTRE La función ALEATORIO crea un numero al azar entre un valor mínimo de cero hasta un máximo de casi 1. Sintaxis: ALEATORIO() Esta función carece de argumentos. Una vez escrita esta función, cada vez que presione la tecla [F9] la función generara otro número al azar. Veamos cómo se usa esta función:

Pág. 90





Para crear un numero al azar entre 0 y 10:

=ALEATORIO()*10 

Para crear un numero entero entre 0 y 10:

=ENTERO(ALEATORIO()*10) 

Para crear un numero entero entre 0 y 350

=ENTERO(ALEATORIO()*350) 


=ENTERO(ALEATORIO()*30)+40 


=ENTERO(ALEATORIO()*1500)+2500 La función ALEATORIO.ENTRE crea un numero entero al azar entre un valor mínimo y un máximo. Sintaxis: ALEATORIO.ENTRE(mínimo, máximo) 


=ALEATORIO.ENTRE(1500, 4000)

SIMULACIÓN CON LA FUNCIÓN ALEATORIO Simulación del lanzamiento de un dado: =ENTERO(ALEATORIO()*6)+1 La función Aleatorio multiplicada por 6 generará un número entre 0 y 5.9999. La función Entero truncará la parte decimal de éstos números quedando así enteros del 0 al 5, y al aumentarles 1 éstos finalmente serian números enteros entre 1 y 6. Escrita esta fórmula, bastara con presionar la tecla de función [F9] para recalcular la fórmula y entonces ALEATORIO generará otro número al azar, simulando que usted vuelve a lanzar los dados. La función ALEATORIO.ENTRE daría el mismo resultado anterior:

=ALEATORIO.ENTRE(1, 6)


Pág. 91


Simulación de la compra de un boleto de lotería de la TINKA:

En una celda escriba la siguiente fórmula: =ENTERO(ALEATORIO()*55)+1 O también puede utilizar esta otra fórmula: =ALEATORIO.ENTRE(1, 55) Luego copiar esta fórmula en 6 celdas, y ya tenemos los 6 números para comprar un boleto de la Tinka.

Simulación de los ingresos de un negocio de transporte público: Se desea averiguar cuánto serían los ingresos que se podrían recaudar en un negocio de transporte público de pasajeros. Para lo cual se cuenta con una unidad de transporte (una combi) con capacidad para 35 pasajeros. Nuestra “combi” recorrerá una ruta en la cual se podrían dar al día de 3 a 7 vueltas. Además se tiene un estimado de los límites máximo y mínimo de pasajeros por vuelta, así como los pasajes que se cobrarían según las distancias recorridas. Con esta información, se va a construir un modelo de simulación del recorrido del vehículo haciendo cuenta que suben y bajan pasajeros cada día durante varios meses, a fin de averiguar cuánto seria la utilidad mensual en este negocio. En una hoja de cálculo, escriba los datos y formulas tal como se muestra en la siguiente imagen. Luego defina la tabla de sensibilidad como se indica allí para este modelo de simulación.

Pág. 92



Una vez calculada la tabla de sensibilidad, ingrese las formulas en las celdas C15, C16 y C17, como se muestra en la figura siguiente:

Y una vez terminado el modelo, por cada vez que presione la tecla [F9], Excel calculará en la celda C17 las utilidades que obtendríamos en los próximos meses.


Pág. 93


Simulación y Pronóstico de las Ventas en un negocio: Construya el siguiente Cuadro de Ventas: 1. En A4 escriba: 1/1/2012 2. En A5 escriba: 1/2/2012 3. Seleccione ambas celdas y cópielas arrastrando el cuadro de relleno hasta la fila 15. Luego deles a las 12 celdas el formato personalizado: mmm 4. En B4 escriba una fórmula que genere un numero al azar entre 1000 y 16000 soles: =ALEATORIO.ENTRE(1000, 16000) 5. Luego copiar esta fórmula hasta el mes de Junio. 6. En C10 escribir: =PRONOSTICO(A10,B$4:B$15,A$4:A$15) 7. Luego copiar esta fórmula hasta el mes de Diciembre. 8. Seleccione el rango B4:C15 y dele el formato estilo millares con cero decimales.

9. Seleccione el rango A3:C15 y cree un grafico como el que se muestra en la siguiente figura.

Pág. 94



10. Seleccione el rango B4:B15 y con la ficha Inicio/Formato condicional/Conjunto de iconos elija el modelo de 3 flechas de color como se puede apreciar en la figura anterior. 11. Si se presiona la tecla [F9] vera como se recalculan las formulas Aleatorio y de Pronóstico, con lo cual la gráfica cambiara también: 

La línea quebrada de color azul indica como serían los ingresos por ventas en los primeros meses del año.

Los puntos naranjas al lado derecho muestran los pronósticos de ventas que se esperan hasta el mes de diciembre Este modelo de simulación muestra cuales serían los pronósticos de ingresos por ventas, dependiendo de cuáles son los valores de ventas ya obtenidos en los primeros meses del año. Observe que el nivel de ingresos por ventas de los últimos meses del año, dependen de cuáles fueron los ingresos desde principio del año. Entonces; dependiendo de cómo se administra un negocio en los meses iníciales, tales serán los resultados obtenidos a finales del año. Si se presionase la tecla [F9] unas 10 veces consecutivas, observe usted cuantas de esas 10 veces su negocio alcanza excelentes ingresos a fin de año, y cuantas de las veces, el negocio se va a la quiebra con ingresos negativos a fin de año. Si lo hace, podrá ver que un negocio cuyos ingresos por ventas oscilan entre 1000 y 16000 soles, no es tan riesgoso que digamos, pues la mayor de las veces los resultados pueden ser satisfactorios, aunque hay la posibilidad como habrá visto que el negocio sea un desastre (pero esta posibilidad es muy pequeña). Sin embargo; el que un negocio nos vaya muy bien, regular, o muy mal, no solo depende de las probabilidades, sino que tiene mucho que ver, el cómo administramos ese negocio. Si una vez vista esta simulación, se decide por emprender realmente este negocio, entonces; este mismo cuadro y su gráfico con pronóstico, nos va a servir para poder observar los resultados que se obtengan ingresando información real de las ventas. Ejercicio: Creando un cuadro semejante al anterior, se desea averiguar cómo nos iría en un negocio de “ Cabinas de Locutorio” (cabinas de teléfono público) sabiendo que se espera unos ingresos mensuales entre 700 soles mínimo y 2100 soles máximo. 

Simulación y Pronóstico de Ingresos en un Locutorio: Construya el siguiente Cuadro: 1. En A4 escriba: 1/1/2012 2. En A5 escriba: 1/2/2012 3. Seleccione ambas celdas y cópielas arrastrando el cuadro de relleno hasta la fila 15. Luego deles a las 12 celdas el formato personalizado: mmm 4. En B4 escriba una fórmula que genere un numero al azar entre 700 y 2100 soles: =ALEATORIO.ENTRE(700, 2100) Elaborado por Daniel Zegarra y William Chuquin

Pág. 95


5. Luego copiar esta fórmula hasta el mes de Junio. 6. En C10 escribir: =PRONOSTICO(A10,B$4:B$15,A$4:A$15) 7. Luego copiar esta fórmula hasta el mes de Diciembre.

8. Seleccione el rango A3:C15 y elija la ficha Insertar/Grafico/Líneas Se tendrá el siguiente gráfico:

9. Ingrese a la Ficha Presentación/Leyenda/Mostrar leyenda en la parte Inferior.

Pág. 96



10. Luego, a la Ficha Presentación/Titulo de grafico/Encima del gráfico, y escriba

como título “Ingresos del Locutorio”

Acomode el grafico como se muestra en la siguiente figura. Entonces cada vez que presione la tecla [F9] los datos del cuadro de ingresos cambiaran, y también cambiara el grafico.

Si 10 personas distintas abrieran este negocio de Cabinas de Locutorio y quisiéramos simular cuales serían los resultados obtenidos por cada uno de ellos al finalizar el primer año de gestión, entonces; podría presionar 10 veces la tecla [F9] y nos fijaríamos en la gráfica que nos dice el pronóstico a fin de año en cada uno de esos 10 diferentes resultados. Pero de este modo, solo se podría simular y ver los resultados a fin de año uno por uno, no los 10 a la vez. Si se quisiera ver los 10 resultados de estas pruebas simultáneamente, entonces construya la siguiente tabla de sensibilidad con una celda de entrada fila:

1. En la celda B18 escriba =C15 Esto hará que aparezca allí el ingreso alcanzado a fin de año. 2. Seleccionar el rango B18:K18 3. Ficha Datos/Análisis Y si/Tabla de datos 4. En la casilla Celda de entrada (Fila) escribir: B16, y Aceptar. La tabla de sensibilidad mostrará entonces lo ingresos a fin de año de cada una de las 10 pruebas.


Pág. 97


Adicionalmente, para poder ver gráficamente cada uno de estos resultados, vamos a insertar un minigráfico debajo de la tabla de sensibilidad. 5. Seleccione el rango A19:K19 y con la herramienta de bordes póngale “todos los

bordes”. Y en A19 escriba: Minigráfico.

6. Luego modifique la altura de la fila a 40 píxeles de alto.

7. Ahora seleccione el rango B18:K18, para ingresar en este rango un “minigráfico

de ganancia o pérdida”.

8. Ficha Insertar/Minigraficos/( +/- )

Aparecerá la siguiente ventana de diálogo:

9. En la casilla Ubicación escribir: B19:K19, y Aceptar.

El minigráfico mostrará en azul y en la parte superior los ingresos positivos, y en la parte inferior y de color rojo los ingresos negativos. Se tendrá entonces un modelo de simulación que permite ver los resultados obtenidos por 10 personas diferentes que abrieron un negocio de locutorio y en

Pág. 98



el cual se puede apreciar la simulación de los 10 resultados simultáneamente. Todo el conjunto se deberá ver así:

Bastará con presionar la tecla [F9] para calcular una nueva simulación cada vez. Como podrá apreciar al presionar varias la tecla [F9], el número de veces que los resultados a fin de año sales negativos es muy bajo comparado con el número de veces en que el negocio es positivo. Esto quiere decir que si abriésemos un negocio de Cabinas de Locutorio, la posibilidad de que nos vaya bien es mucho mayor de que no vaya a ir mal, o sea que este negocio no es de mucho riesgo. Pero todo depende como ya sabemos de cómo administramos el negocio. Viendo entonces este modelo de simulación, supongamos que decidimos abrir realmente un negocio como este, y realizamos la inversión necesaria para llevarla a cabo, adquirimos las cabinas, los teléfonos, la computadora que controle las llamadas y el software respectivo, asi como las licencias municipales correspondientes, contratamos a una señorita para que se encargue de la atención de los clientes y entonces cuando todo ya esté listo inauguramos el local, y comenzamos a atender al público. Entonces veremos que este mismo cuadro que nos sirvió para hacer las simulaciones, también nos puede servir para hacer un seguimiento de como estaríamos administrando el negocio, ingresando en el cuadro mes a mes los ingresos reales de nuestro negocio y viendo simultáneamente el pronóstico de resultados hasta fin de año. De este modo será sencillo darnos cuenta que tan bien estamos administrando nuestro negocio.

Seguimiento Real de Ingresos y Pronóstico del Locutorio: Construya el siguiente Cuadro: 1. Seleccione el rango B4:B9 y bórrelo con la tecla Suprimir. Elaborado por Daniel Zegarra y William Chuquin

Pág. 99


2. Ahora escriba en B4 y B5 los ingresos reales de Enero y Febrero

A medida que vamos ingresando los ingresos reales, en la gráfica se puede apreciar el pronóstico hasta fin de año. Pero como se puede ver no hay pronósticos de marzo a junio, así que vamos a copiar la fórmula del pronóstico del mes de julio en los anteriores meses: 3. Seleccione la celda C10 y arrastre el cuadro de relleno hacia arriba hasta la celda C6 4. Ahora escriba en B6 y B7 los ingresos reales de Marzo y Abril. En la gráfica podrá ver como se cruzan las líneas de los ingresos reales (color azul) y la línea de los pronósticos (color rojo).

Si no quisiéramos que pase esto, porque ya no tiene sentido que se calculen los pronósticos para aquellos meses en que se tienen los ingresos reales; entonces desearíamos que a medida que se vayan escribiendo los ingresos reales de cada mes, simultáneamente se vayan borrando los pronósticos de esos meses. Para lograr esto hagamos lo siguiente: 5. En la celda C6 modifique la formula escribiendo lo siguiente: =SI(B6=””,PRONOSTICO(A6,B$4:B$15.A$4:A$15),””)

6. Luego copiar esta fórmula hasta el mes de diciembre.

Pág. 100



Según la formula anterior, cuando aún no haya ingresos en la columna B, se deberá calcular el pronóstico correspondiente en la columna C, pero cuando ya exista ingreso real en ese mes entonces el pronóstico de ese mes ya no deberá calcularse. Sin embargo, aunque en la columna C se ven que los pronósticos desaparecen al ingresar datos en la columna B, pero; en la gráfica los pronósticos aparecen

graficados como que valen cero. Esto se debe a que la doble comilla “” tiene el

valor cero al momento de graficarse. Para evitar que el pronóstico se grafique como cero, hagamos lo siguiente: 7. Vuelva a corregir la formula en la celda C6 de esta manera: =SI(B6=””,PRONOSTICO(A6,B$4:B$15.A$4:A$15),ND())

Nota.- Lafunción No DispoinibleND(), dará como respuesta el mensaje #N/A que es un mensaje de error. En las versiones anteriores de Excel, hasta la versión 2007, esta función se escribía como NOD(), pero en la versión 2010 su nombre fue cambiado por ND(). Sin embargo para aquellas computadoras que tienen instalada la Revisión Nº2 de la versión 2010 de Excel, entonces ya pueden volver a usar los antiguos nombres de las funciones de Excel.

El grafico ya se habrá arreglado, pero en la columna C de los pronósticos han aparecido mensajes de error #N/A (en realidad no es un error de verdad, ni quiere


Pág. 101


esto decir que el cálculo del pronóstico estuviera incorrecto, sino que es un error que lo escribimos adrede allí, solo para que la gráfica se vea bien). Ahora, si no deseamos que se vea el error #N/A en la columna C, entonces vamos a darle formato condicional para que en caso de error este se pinte de color blanco y de ese modo se vuelva transparente. 8. Seleccione el rango C6:C15 9. Ficha Inicio/Formato condicional/Nueva regla 10. En la ventana de dialogo elija la opción: Aplicar formato únicamente a las celdas que contengan…

11. Y en la casilla desplegable “ V alor de la celda”, elegir la opción “ E rrores ” 12. Haga clic en el botón “Formato” 13. En la nueva ventana que aparece, en la ficha Fuente elija el color “Blanco” , y luego Aceptar, y Aceptar otra vez.

El cuadro se deberá ver entonces así:

Pág. 102



14. Escriba los ingresos reales de los siguientes meses Mayo y Junio. 15. Haga un clic derecho en la línea azul de los ingresos reales, y elija “Agregar línea de tendencia”, luego clic en “Cerrar”. y dele formato al gráfico. 16. Seleccione el rango B4:B15 use la ficha Inicio/Formato condicional/ Conjunto de iconos, y elija los iconos de flechas. 17. Vuelva a llamar a la ficha Inicio/Formato condicional/Barra de datos, y elija uno de los colores de la lista de opciones que aparece. Finalmente la hoja de cálculo deberá verse así:


Pág. 103


9. Consolidado de las Ventas Mensuales y Análisis gráfico Una empresa distribuidora tiene el reporte de sus ventas diarias durante los 9 primeros meses del año en un cuadro ubicado en A3:L500, y quiere obtener un cuadro resumen de sus ventas mensuales por cada uno de los productos vendidos, y además analizar gráficamente los resultados de estas ventas. En la columna A se encuentran los nombres de las marcas de los productos vendidos. En la columna B están las fechas de venta. Y en la columna C la cantidad vendida en miles de paquetes. El resto de las columnas de la D a la L con títulos de Ene a Set se encuentran vacías.

Como se puede apreciar en el siguiente cuadro, en la celda O4 se desea obtener la suma de todos los paquetes vendidos del producto Huggies en el mes de Enero.

Esta suma se puede obtener de varias formas:    

Con la función SUMAR.SI.CONJUNTO Utilizando una formula matricial Creando una tabla dinámica Consolidando las ventas mensuales de cada producto

Analicemos cada método: Los dos primeros, son formulas condicionales rápidas de escribir, pero que si los datos analizar son muy grandes (por ejemplo de más de 50,000 registros) entonces la hoja de cálculo se volvería muy lenta.

Pág. 104



La función SUMAR.SI.CONJUNTO es una función nueva que solo funciona a partir de la versión 2007 de Excel, la formula matricial en cambio puede utilizarse en cualquier versión de Excel sin inconvenientes. Los dos siguientes métodos no utilizan formulas, y por esta razón son 10 veces más rápidas para sumar y obtener los resultados deseados. Crear una tabla dinámica ya no utilizaría el mismo cuadro de la figura anterior pues la tabla dinámica crea su propio cuadro con diseño propio y botones con filtros y listas desplegables que quizás no se desean y además la tabla dinámica tiene sus propios formatos rígidos. Pero eso sí; la tabla dinámica es muy fácil de hacer. Por último tenemos la posibilidad de obtener el consolidado de las ventas mensuales de cada producto, pero para ello es necesario que en el cuadro origen de ventas, las cantidades vendidas de cada producto se encuentres escritas en diferentes columnas según el mes en que se vendieron. Esto significa que hay que copiar previamente las cantidades de la columna C en las columnas de la D a la L, y luego recién poder consolidar estas ventas en el cuadro ubicado en N3:W8 . Para esta ocasión vamos a solucionar este caso utilizando dos de los 4 métodos. El primero utilizando una formula matricial, y como segundo método vamos a Consolidar las ventas. 1er Método: C on fórmulas matrici ales 1. Nombrar los siguientes rangos: 



A4:A50  Producto B4:B50  Fecha

C4:C50  Cantidad 2. En la celda O4 escribir la formula matricial: 

{=SUMA(SI((PRODUCTO=$N4)*(MES(FECHA)=MES(O$3)),CANTIDAD))}

3. Finalmente copiar esta fórmula en todo el resto del cuadro.

2do Método: C ons olidando el cuadro de ventas

1. Saque una copia del rango N3:W8 en Y3:AH8, y luego borre el contenido del rango Z4:AH8.


Pág. 105


2. En el cuadro grande de ventas diarias vamos a copiar las cantidades vendidas de la columna C en las columnas D a la L utilizando la siguiente formula en la celda D4: =SI(MES($B4)=MES(D$3),$C4,””)

3. Luego copiar esta fórmula en el resto del cuadro para todos los meses. 4. Nombrar el rango A3:L500 como VENTAS.

5. Seleccionar el rango Y3:AH8 6. Ficha Datos/Consolidar: en la casilla de texto Referencia escriba VENTAS y

luego haga clic en el botón “Agregar”. 7. Marque las dos casillas: “Fila superior” y “Columna izquierda”, y finalmente “Aceptar”.

Pág. 106



Deberán aparecer en el cuadro los mismos resultados de ventas mensuales obtenidos con el 1er método, tal como se muestra a continuación:

A nális is G rafico de las Ventas Mens uales

Habiendo calculado los totales de ventas mensuales de cada producto, ahora en la parte inferior vamos a crear un gráfico de columnas como se muestra en la siguiente figura: 1. Seleccionar el rango N3:W8 2. Ficha Insertar/Gráficos/Columnas/Cilindro agrupado 3. Ficha Presentación/Titulo de gráfico/Titulo superpuesto centrado, y escribir

“Miles de Paquetes”


Pág. 107


En este grafico como los meses están en el eje horizontal es fácil ver que el mes de mayor venta fue el mes de Setiembre. Pero si se quisiera saber cuál fue el producto de mayor venta entonces habría que tener en el eje horizontal a los productos. Esto se logra de la siguiente manera:

4. Seleccionar el grafico. 5. Ficha Diseño/Cambiar entre filas y columnas

Entonces el grafico se verá así:

Pág. 108



Ahora se puede apreciar que Familia y Suave no son los de mayor venta, pero aún no se sabe cuál es el producto más vendido. La pelea estaría entre Huggies, Scott y Kotex, y para averiguarlo hay que hacer lo siguiente: 6. Seleccionar el grafico 7. Ficha Diseño/Cambiar tipo de gráfico 8. Elegir el grafico de Cilindros apilados, y Aceptar

El producto con mayor número de paquetes producidos es el de servilletas Scott. Pero eso no significa que las servilletas sean el mejor producto de la empresa. En realidad el mejor producto seria el que nos reporta los mejores ingresos, y para saber eso sería necesario que se obtenga una gráfica valorizada de las cantidades Elaborado por Daniel Zegarra y William Chuquin

Pág. 109


vendidas, esto significa que deberíamos multiplicar las unidades vendidas de cada producto por el precio unitario de cada producto. Cálculo de las ventas valori zadas de cada produc to

9. En la celda O25 escribir: =BUSCARV($N25,VentasMes,Columna(B1),0)*BUSCARV($N25,Precios,2,0)*100 0

10. Luego copiar esta fórmula para los demás meses y productos de este cuadro.

11. Para obtener los totales, seleccione el rango O25:X30 y haga un click en el botón de Autosuma

12. Para crear el grafico valorizado, seleccione el rango N24:W29 13. Ficha Insertar/Grafico/Cilindro apilado El grafico obtenido se deberá ver así:

Pág. 110



Seguidamente hay que intercambiar el eje horizontal con la l eyenda: 14. Ficha Diseño/Cambiar entre filas y columnas El grafico finalmente se verá así:

Cálculo de la pos ic ión jerárquica de ventas valori zadas de cada producto

15. En la celda Y25 escribir la siguiente formula: =JERARQUIA(X25,X$25:X$29)

16. Luego copiar la formula hacia abajo para los 5 productos. 17. Seleccionar el rango Y25:Y29 y darle el formato personalizado: 0”º”


Pág. 111


10. Control de cuentas por cobrar y cálculo de intereses y moras Una tienda comercial que vende artículos con tarjeta de crédito desea llevar el control de las deudas de los clientes que se atrasan en el pago de sus cuotas, cuyas fechas de vencimiento se encuentran en la columna C. Todos los clientes de esta lista tienen dos deudas contraídas con la empresa comercial, una de las deudas esta en moneda nacional (nuevos soles) y la otra en moneda extranjera (dólares), y tienen un atraso en sus pagos de 0 a 10 días como máximo. Nuestra labor consiste en llamar a estos clientes y hacerles recordar que deben venir a hacer efectivo sus pagos cuyos montos ya debemos tener calculados con los intereses del mes y la mora por los días de atraso. En la hoja “Contratos” se puede apreciar la lista de estos clientes y el estado de sus

cuentas vencidas.

En la Hoja Tablas se tiene una tabla con los tipos de cambio del dólar de los últimos diez días, y otra tabla con las tasas de interés mensual según el monto de las deudas de los clientes, y las tasas de interés moratorio por cada día de atraso en los pagos.

Pág. 112



A estas dos tablas hay que darles los siguientes nombres de rango: 

A1:B13  TCAMBIO

D4:F8  TASAS Con estas tablas se deberán calcular las columnas en blanco del cuadro de cuentas vencidas. 

Calculo de la s uma de ambas deudas en moneda nacional: En G4: =E4+F4*BUSCARV(D4,TCAMBIO,2,0) Calculo del Interés Mens ual de la deuda: En H4: =G4*BUSCARV(G4,TASAS,2) Calculo del Interés Moratorio de la deuda: En I4: =G4*(1+BUSCARV(G4,TASAS,3))^(HOY()-D4)-G4 Calculo del S aldo Deudor: En K4: =SUMA(G4:I4)-J4

Para calcular el Cuadro Resumen por Días de Atraso , previamente hay que dar nombre de rango a las columnas del cuadro de Cuentas Vencidas: 1. Seleccionar todo el cuadro de Estado de Cuentas Vencidas (puede usar Ctrl + *) 2. Ficha Formulas/Crear desde la selección 3. Desactivar la casilla “Columna izquierda”. Solamente debe quedar activada la casill a “Fila superior”. 4. Clic en el botón Aceptar.


Pág. 113


Ahora; en la hoja Resumen se deberá escribir las siguientes formulas: Calculo del número de clientes por días de atras o: En C4: =CONTAR.SI(Fecha_Vencimiento,HOY()-N4) Calculo del Monto de deuda en S oles de los clientes por días de atras o: En D4: =SUMAR.SI(Fecha_Vencimiento,HOY()-N4,Soles)

Si además del monto de la deuda en soles de los clientes, también se quisiera obtener el cálculo de la deuda en Dólares, y también la suma total de ambas deudas, y así todas las demás columnas del cuadro de Cuentas Vencidas, entonces; habría que crear un cuadro resumen por días de atraso con 7 columnas para cada uno de los rubros a calcular. Pero eso no será necesario, si es que realizamos lo siguiente: Validar la celda D3 para que muestre los rubr os del cuadro de deudas : 1. 2. 3. 4. 5. 6.

Pág. 114

En la Hoja “Contratos” nombrar el rango E3:K3  Rubros Regresar a la hoja “Resumen” y seleccionar la celda D3 Ficha Datos/Validación de datos: En Permitir, elegir la opción: Lista En Origen, escribir el rango: =Rubros Clic en Aceptar.



En la celda D3 que ya está validada, elija el rubro Soles.

Modificar la formula en D4: En D4 modificar la formula como se muestra a continuación:

=SUMAR.SI(Fecha_Vencimiento,HOY()-N4,INDIRECTO(D$3)) Luego copiar esta fórmula en D4:D14 Ahora podrá elegir en la celda D3 cualquiera de los rubros y el cuadro resumen mostrara los resultados de la columna que hayamos elegido.

Para obtener el Cuadro Resumen por Distritos, que se muestra en la siguiente figura; se va a crear una tabla dinámica tomando como base los datos del cuadro de Cuentas Vencidas:


Pág. 115


Para crear esta tabla, haga lo siguiente: 1. En la hoja Contratos seleccionar una celda del cuadro de cuentas vencidas 2. En la Ficha Insertar/Tabla dinámica

3. Marque con un punto la opción Hoja de cálculo existente 4. En la casilla Ubicación seleccione la celda B22 de la hoja Resumen, tal y como se muestra en la ventana de dialogo anterior; y luego haga clic en el botón Aceptar. 5. En la hoja Resumen, utilice el Panel de Tareas de Tabla Dinámica ubicado a la derecha de la pantalla, y arrastre los campos a las áreas correspondientes tal y como se muestra en la siguiente figura.

Pág. 116



6. Al aparecer la tabla dinámica, seleccionar todos los montos dentro de la tabla y darle el formato estilo millares. 7. En la ficha Diseño de Herramientas de tabla dinámica, elegir un color para darle a su tabla dinámica. C onsultas divers as s obre el cuadro de C uentas Vencidas :


Pág. 117


a) Suma del Monto en Soles del distrito de Surco, con más de 3 días de atraso.

Para contestar esta pregunta, escriba la siguiente formula matricial: {=SUMA(SI((Distrito=”Surco”)*(HOY()-Fecha_Vencimiento>3),Soles))} b) Suma del Monto Total en el distrito de La Victoria, superiores a 15,000.

Para contestar esta pregunta, escriba la siguiente formula matricial: {=SUMA(SI((Distrito=”La Victoria”)*(Total>15000),Total))}

c) Cuantos clientes cancelaron más de la mitad de su deuda.

Para contestar esta pregunta, escriba la siguiente formula matricial: {=CONTAR(SI((Pago>Saldo),Pago))} d) Cuantos clientes tienen su deuda en soles mayor que la deuda en dólares.

Para contestar esta pregunta, escriba la siguiente formula matricial: {=CONTAR(SI((Soles>Total-Soles),Soles))}

Pág. 118



Capítulo 5 1. La Herramienta Solver de Excel b. La herramienta Solver Solver es una herramienta para resolver y optimizar ecuaciones mediante el uso de métodos numéricos. Solver se puede utilizar para optimizar funciones de una o más variables, sin o con restricciones. Microsoft Excel Solver utiliza diversos métodos de solución, dependiendo de las opciones que se seleccionen: 

Para los problemas de programación lineal, Solver utiliza el Método Simplex.



Para problemas lineales enteros, utiliza “Branch and Bound”.

Para problemas no lineales, utiliza el Código de Optimización no lineal (GRG2). Con Solver, se puede buscar el valor óptimo para una celda, denominada celda objetivo, en donde se escribe la fórmula de la función objetivo: f(x1 , x2 , ..., xn ). Solver cambia los valores de un grupo de celdas, denominadas celdas cambiantes, y que estan relacionadas, directa o indirectamente, con la fórmula de la celda objetivo. En estas celdas se encuentran los valores de las variables de decisión: x1, x2, ..., xn. Los modelos más realistas tienen factores de restricción que es necesario aplicar a ciertos valores. Estas restricciones se pueden aplicar a las celdas de las variables de decisión (celdas cambiantes) o a cualquier otra celda que tenga una función (fórmula) de estas celdas. Se puede agregar restricciones a Solver, escribiendo una fórmula gj(x1 , x2 , ..., xn ) en una celda, y especificando que la celda deberá ser mayor o igual, igual, o menor o igual que otra celda que contiene la constante bj. También, si fuese el caso, se puede especificar que los valores sean enteros, para evitar resultados absurdos en algunos problemas. 

c. Optimización con restricciones. En un problema de optimización con restricciones se buscan los valores de ciertas variables que optimizan una función objetivo, sujetas a ciertas condiciones. Matemáticamente: Optimizar f(x1 , x2 , ..., xn )

Sujeta a: g1(x1 , x2 , ..., xn ) =(ó ≤, ≥) b1 g2(x1 , x2 , ..., xn ) =(ó ≤, ≥) b2 ………… gm(x1 , x2 , ..., xn ) =(ó ≤, ≥) bm


Pág. 119


Los modelos más sencillos corresponden a modelos de Programación Lineal, donde tanto la función objetivo como las restricciones son funciones lineales, las variables no deben ser negativas, y pueden tomar cualquier valor real, no necesariamente entero.

d. Para habilitar la herramienta Solver En la ficha Datos hay que fijarse si aparece el comando Solver. Si no aparece, se deberá habilitar el complemento o macro automática Solver: Usar el botón Office/ Opciones de Excel/ Complementos, luego haga clic en el botón Ir situado a la derecha de la casilla Complementos de Excel, activar con un check la casilla Solver y luego Aceptar.

e. Consideraciones previas a tener en cuenta Antes remarquemos ciertos aspectos que pueden suceder al intentar resolver un problema con Solver: • Si Solver no encuentra los valores, de las variable de de cisión y de la función objetivo óptimos, hay que tener en cuenta que los métodos numéricos para problemas no lineales encuentran el óptimo sólo si: o Existe, y/o se parte de una solución inicial "apropiada". • Es conveniente siempre probar con diferentes soluciones iniciales, para confirmar que la solución de Solver es realmente la mejor, o para evitar que se "atasque" en puntos de inflexión o en óptimos locales. • Las inestabilidades que pueden aparecer (por malas soluciones iniciales) en los algoritmos de optimización no lineales, no se presentan en casos de Programación Lineal, dado que Solver utiliza el Método Simplex para estos casos.

f. Problemas de Programación Lineal 

Maximizar Ganancias:

E jercicio Nº 7: Maxi mizar g anancias en la producción de tortas En una pastelería se hacen dos tipos de tortas: de chantilly, y de chocolate. Cada torta de chantilly necesita un cuarto de kg de relleno y produce una ganancia de 25 soles, mientras que una torta de chocolate necesita medio Kg. de relleno y produce una ganancia de 40 soles. Si en la pastelería solo se pueden hacer diariamente hasta 150 tortas en total, aunque por problemas de maquinaria solo se pueden preparar hasta 90 tortas de cada tipo, y además la cantidad de relleno máxima que se puede preparar en un día es de 50 Kg. Se desea saber ¿cuántas tortas de chantilly, y cuantas de chocolate se tendrán que hacer al día para que la ganancia sea la máxima posible?

Pág. 120



1. Abrir el archivo Producción de Tortas.xlsx, e ingrese los datos que se dan en el problema para la cantidad de relleno y ganancia en cada tipo de torta: celdas D15, D16, F15, y F16. 2. En D19 y D20 escriba 1 como valor inicial para cada tipo de torta. 3. Escriba la fórmula de la función objetivo la cual se desea maximizar. Esta fórmula es la suma de la cantidad de tortas producidas de cada tipo por su respectiva ganancia. En D23:

=D19*F15+D20*F16


Pág. 121


4. Luego escriba las fórmulas para las restricciones: La cantidad de relleno de tortas de chantilly + cantidad de relleno de tortas de chocolate no deben exceder los 50 kg. =D15*D19+D16*D20 En B26: y en D26 escribir 50 La cantidad de tortas de chantilly y chocolate en total no deben exceder las 150 unidades. En B27: =D19+D20 y en D27 escribir 150 5. Una vez escritos los datos y formulas en las celdas de Excel, usaremos la herramienta Solver: Elija la ficha Datos/ Solver, y en la ventana que aparece ingresar los datos tal como se muestra a continuación:

6. Para definir las restricciones haga clic en el botón “Agregar”, y en las casillas seleccione las celdas que desea relacionar, así como el tipo de relación entre ella; luego haga clic en Aceptar. Repita este paso para cada restricción que se desee definir.

Pág. 122



Las restricciones a definir son las siguientes: $B$26<=$D$26 Relleno <= 50 kg $B$27<=$D$27 Tortas <= 150 unidades $D$19<=90 Tortas Chantilly <= 90 unidades $D$19=integer Tortas Chantilly es un numero entero $D$19>=0 Tortas Chantilly es un numero positivo $D$20<=90 Tortas Chocolate <= 90 unidades $D$20=integer Tortas Chocolate es un numero entero $D$20>=0 Tortas Chocolate es un numero positivo 7. Una vez definida todas las restricciones haga clic en el botón Resolver, y en la siguiente ventana haga clic en Aceptar.


Pág. 123


8. En las celdas D19 y D20 aparecerán las cantidades que se deben producir (90 tortas de chantilly y 55 tortas de chocolate) con lo cual se obtendría la máxima ganancia, cuyo monto aparecerá calculado en la celda D23 (4,720 soles). Con lo cual Solver habría terminado de calcular la solución óptima para este problema.



Minimizar Gastos:

E jercicio Nº 8: Minimizar g as tos en transporte de alumnos Una escuela prepara una excursión para 400 alumnos. La empresa de transporte tiene 8 ómnibus pequeños de 40 asientos y 10 ómnibus grandes de 50 asientos, pero solo dispone de 9 choferes. El alquiler de un ómnibus pequeño cuesta 60 soles y el de uno grande 80 soles. Calcular cuántos ómnibus de cada tipo hay que contratar para que la excursión resulte lo más económica posible para la escuela.

Pág. 124



1. Abrir el archivo Excursión de alumnos.xlsx, e ingrese los datos que se dan en el problema: celdas D15, D16, F15, y F16. 2. En D19 y D20 escriba 1 como valor inicial para cada tipo de ómnibus. 3. Escriba la fórmula de la función objetivo la cual se desea minimizar. Esta fórmula es la suma de la cantidad de ómnibus contratados de cada tipo por su respectivo costo. =D19*F15+D20*F16 En D23: 4. Luego escriba las fórmulas para las restricciones:


Pág. 125


La cantidad de asientos de ómnibus pequeño + cantidad de asientos de ómnibus grande no deben ser menores a 400. =D15*D19+D16*D20 En B26: y en D26 escribir 400 La cantidad de ómnibus pequeño y grandes en total no deben exceder las 9 unidades que es la cantidad de choferes disponibles. En B27: =D19+D20 y en D27 escribir 9 5. Elija la ficha Datos/ S olver , y en la ventana que aparece ingresar los datos tal como se muestra a continuación:

6. Para definir las restricciones haga clic en el botón “ A g reg ar ”, y en las casillas seleccione las celdas que desea relacionar, así como el tipo de relación entre ellas; luego haga clic en Aceptar. Repita este paso para cada restricción que se desee definir.

Pág. 126



Las restricciones a definir son las siguientes: $B$26>=$D$26 Asientos >= 400 $B$27<=$D$27 Ómnibus <= 9 $D$19=integer numero de ómnibus es entero $D$19>=0 numero de ómnibus es positivo $D$20=integer numero de ómnibus es entero $D$20>=0 nu

mero de ómnibus es positivo $D$19<=8 máximo 8 ómnibus pequeños $D$20<=10 máximo 10 ómnibus grandes 7. Cuando se haga tenga una restricción de solo números enteros en las respuestas, entonces será necesario ingresar al botón “ Opciones ” del lado derecho de la ventana, y allí desactivar la casilla “ Omitir res tricciones de enteros ” y luego Aceptar. De no hacerlo así las respuestas que nos de Solver podrían salir con decimales. 8. Una vez definida todas las restricciones haga clic en el botón “ R esolver ” , y en la siguiente ventana haga clic en Aceptar.


Pág. 127


9. En las celdas D19 y D20 aparecerán las unidades que se deben contratar (5 ómnibus pequeños y 4 ómnibus grandes) con lo cual se realizaría el mínimo gasto en el transporte de los alumnos, cuyo monto aparecerá calculado en la celda D23 (620 soles).

Pág. 128



E jercicio Propues to:

Respuesta de este ejercicio:


Pág. 129


g. Problemas No Lineales

E jercicio Nº 9: C ons truir un almacén minimizando los cos tos de construcción Se va a construir un almacén industrial con una capacidad de 1.500 m3. Los costos de construcción por m2 son de 4 u.m./m2 para los muros, 6 u.m./ m2 para el techo y 12 u.m./m2 para el suelo. Por razones estéticas, la anchura del almacén debe ser el doble de su longitud. Encontrar las dimensiones óptimas del almacén, si se desea minimizar el costo de construcción. 

Dimensiones: ancho, alto y largo; éstas serán las incógnitas o variables de decisión (celdas cambiantes)



Superficie del suelo: largo*ancho



Superficie de las paredes: 2*largo*alto+2*ancho*alto



Superficie del techo: largo*ancho



Costo de construcción de las paredes: 40*superficie de los muros



Costo de construcción del suelo: 120*superficie suelo



Costo de construcción del techo: 60*superficie techo



Costo de construcción total: Costo de construcción de las paredes + Costo de construcción del suelo+ Costo de construcción del techo



Restricción estética: largo = 2*ancho



Restricción de volumen: largo*ancho*alto = 1500

largo es doble del ancho

Pág. 130



1. Ingrese los datos que se dan en el problema para los costos por m2 del suelo, muros y techo: en las celdas C9, D9, y E9. 2. En C12, D12 y E12 escriba 1 como valor inicial para cada dimensión. 3. Escriba la fórmula de la función objetivo la cual se desea minimizar. Esta fórmula es la suma de las superficies del suelo, muros y techo, por sus respectivos costos por m2. C12*D12*C9+2*C12*E12*D9+2*D12*E12*D9+C12*D12*E9 En C15: 4. Luego escriba las fórmulas para las restricciones: El largo es el doble del ancho: En B18: y en D18 escribir =2*C12 =D12 El almacén debe tener un volumen total de 1500 m3. En B19: y en D19 escribir 1500 =C12*D12*E12 5. Una vez escritos los datos y formulas en las celdas de Excel, usaremos la herramienta Solver: Elija la ficha Datos/ Solver, y en la ventana que aparece ingresar los datos tal como se muestra a continuación:


Pág. 131


6. Definidas todas las restricciones, haga clic en el botón Resolver, y en la siguiente ventana haga clic en Aceptar.

7. En las celdas C12, D12 y E12 aparecerán las dimensiones optimas del almacén con lo cual se obtendría el mínimo costo de construcción, cuyo monto aparecerá calculado en la celda C15 (42,859.83 soles).

largo es doble del ancho

Pág. 132



S olver y P rog ramación Li neal Aunque Solver puede identificar automáticamente si el problema es lineal o no lineal, también es posible indicarle expresamente de que se trata de un problema de programación lineal. Para hacer esto, haga clic en el cuadro de diálogo Parámetros de Solver se hace clic en el botón “Opciones…”, con lo que aparecerá el cuadro de diálogo Opciones de Solver y se marca la casilla para que adopte un modelo lineal. Si las variables de decisión fuesen no negativas (algo bastante habitual, también se m arcaría la opción correspondiente).

Ejercicios adicionales:


Pág. 133


Pág. 134



Luego de hacer clic en el botón “Resolver”, Solver habrá encontrado la siguiente solución:


Pág. 135


ANEXOS Teclas de Función en Micros oft E xcel Tecla

Función

F1

Abrir ventana de ayuda

F2

Modificar la celda activa

F3

Pegar un nombre en una formula

F4

Repetir la ultima acción

F5

Shift

Ctrl Abrir o cerrar el Panel de Tareas

Alt Insertar una hoja de grafico

Modificar el comentario de la celda activa Pegar una función en una formula Repetir el ultimo Buscar (Buscar siguiente)

Definir un nombre de rango

Menú Insertar/Nombre/Crear

Cerrar la ventana del libro actual

Cerrar la ventana de Excel

Ir a

Menú Edición/Buscar

Restaurar el tamaño de la ventana

F6

Ir al siguiente Panel

Ir al Panel Anterior

Ir a la siguiente ventana del libro

F7

Menú Herramientas/ Ortografía

Mover la ventana del libro actual

F8

Ampliar una selección Agregar selecciones de celdas múltiples de celdas Calcular todas la hojas de todos los libros Calcular la hoja activa abiertos

Cambiar el tamaño de la ventana

F9 F10

Activar la barra de menú

Presentar un menú contextual

F11

Crear un grafico

Insertar una nueva hoja de calculo

F12

Menú Archivo/ Guardar como

Menú Archivo/ Guardar (Ctrl+G)

Tecla

Pág. 136

Función

Shift

Menú Archivo/Guardar como

Ir a la primera ventana del libro anterior

Mostrar la ventana de diálogo Macro

Minimizar la ventana del libro activo Maximizar o restaurar la ventana del libro actual Insertar una hoja de Macros (Excel 4.0)

Abrir el Editor de Visual Basic

Menú Archivo/ Abrir

Ctrl

Alt


Manual Excel PAD - 2016.pdf

Recommend Documents