• Author / Uploaded
• Brian Leyva

• Categories
• Hoja de cálculo
• Histograma
• Simulación
• Muestreo (Estadísticas)
• Point and Click

Citation preview

UNIVERSIDAD AUTÓNOMA DE SINALOA FACULTAD DE INGENIERÍA MOCHIS (FIM) INGENIERÍA CIVIL

“Lab. De Ingeniería de Sistemas Aplicada”

Práctica #5: “Simulación de Sistemas (Aplicaciones Generales).

”

Grupo: 3 – 04 Brigada: 13

Profesor: Ing. Jaime Edilberto Rabago Aguirre

Alumno: Ruiz Rivera Manuel Alberto 16 de Abril de 2018 Los Mochis, Sin

PRACTICA No.5 MODELO DE SIMULACIÓN OBJETIVO: Que el alumno elabore un modelo de simulación de sistemas EQUIPO Y MATERIAL: Computadora y Software Office. FUNDAMENTO TEÓRICO SIMULACIÓN CON HOJA DE CÁLCULO La mayor parte de las simulaciones se llevan a cabo en hojas de cálculo, debido a que el número de cálculos requeridos sobrepasa pronto las capacidades humanas. Las simulaciones se pueden llevar a cabo con la hoja de cálculo sola (sin la ayuda de software complementario especial), como se demostrará en esta sección. Aquí presentaremos un ejemplo del presupuesto de capital para mostrar el uso de una hoja de cálculo en la simulación y para establecer algunos puntos importantes sobre los resultados de una simulación en una hoja de cálculo. EJEMPLO DE PRESUPUESTO DE CAPITAL: ADICIÓN DE UN PRODUCTO A LA LÍNEA DE PROTAC June Wilson es la administradora de desarrollo de nuevos productos y está considerando las implicaciones financieras de una posible adición a la línea de equipo pesado de PROTRAC. Los costos de la puesta en marcha para el modelo G-9 propuesto (que incluyen la compra de nuevo equipo, capacitación personal, etc.) están estimados en $150,000. El nuevo producto será vendido a un precio de $35,000 la unidad. Los costos fijos están estimados en $15,000 al año, mientras que el costo variable seria de aproximadamente 75% de los ingresos de cada año. La depreciación fiscal sobre el nuevo equipo sería de $10,000 por año durante los cuatro años de vida productiva del G-9. El valor de salvamento del equipo final de los cuatro años es incierto, de modo que June lo estima de manera conservadora en cero. El costo de capital de PROTRAC es de 10% y su tasa de impuestos es de 34%. El aspecto más incierto de la propuesta es la demanda por el nuevo producto. Si June conociera la demanda, calcularía fácilmente el valor neto actual (VNA; también conocido como el valor neto presente) de la propuesta utilizando un programa de hoja de cálculo. Por ejemplo, si June supone la demanda de los G-9 en 10 unidades en cada uno de los siguientes cuatro años, la hoja de cálculo en la figura 11.9 (WILSON.XLS) muestra que el VNA sería de $12,455.60. EL MODELO CON DEMANDA ALEATORIA Sin embargo, es poco probable que la demanda sea exactamente igual cada año. June siente que sería más realista modelar la demanda cada año no como un valor constante común, sino como una secuencia de variables aleatorias. Este modelo de la demanda es apropiado cuando hay un nivel constante básico de demanda que está sujeta a fluctuaciones aleatorias de un año a otro. Cuando el nivel básico de demanda es de 10 unidades, la demanda real para los siguientes cuatro años puede resultar que sea de 12, 9, 8 y 10, debido a los factores aleatorios que afectan la demanda.

Como hacer una muestra de la demanda con una hoja de cálculo: June decide generar la demanda aleatoria para cada uno de los cuatro años para ver cuál es el efecto que tiene la variación de la demanda del VNA. Ella supuso inicialmente que la demanda en un año será de 8, 9, 10, 11 o 12 unidades con una misma posibilidad de que ocurra cada valor. Este es un ejemplo de una distribución discreta uniforme. Mediante su nuevo conocimiento de la sección 11.2, ella utiliza la formula =ENTERO(8+5*ALEATORIO( )) para hacer una muestra de la distribución discreta uniforme de los cinco enteros 8, 9, 10, 11, 12. Debido a que el valor de ALEATORIO( ) cambiara cada vez que se recalcule la hoja de cálculo, June puede llevar a cabo fácilmente ensayos múltiples; esto es, obtener una nueva muestra de la demanda oprimiendo simplemente la tecla de recalculo de su hoja de cálculo (F9 por lo general). Después de hacer esto varias veces se sorprendió de encontrar que en algunos ensayos obtenía un VNA negativo. La figura 11.10 muestra que el VNA correspondiente a una secuencia aleatoria de demandas es de $2,515.71, aproximadamente 80% menos que el VNA si la demanda fuera constante en 10 años. Si –June volviera a presionar la tecla F9, obtendría una muestra diferente de la demanda, y por tanto la posibilidad de un VNA diferente. Debido a que la demanda puede variar de una muestra a otra, también puede variar el VNA. Dicho de manera más técnica, la demanda consiste en variables aleatorias, por lo tanto el VNA también es una variable aleatoria. Capítulo 11. Simulación Monte Carlo.

Hoja de cálculo inicial Figura 11.9

Celda C10 C11 C12 C13 C14 C15 C16 B17 C17 B19

Fórmula =C9*$B$3 =$B$4 =C10*$D$2 =$B$5 =C10-SUMA(C11:C13) =$D$4*C14 =C14-C15 =-$B$2 =C16+C13 =VNA($D$3,C17:F17)+B17

Cópiese a D10:F10 D11:F11 D12:F12 D13:F13 D14:F14 D15:F15 D16:F16 --------D17:F17 --------

Figura 11.10

Hoja de cálculo de Wilson, con demandas seleccionadas aleatoriamente Celda C9

Formula =ENTERO(8+5*ALEATORIO( ))

Cópiese a D9:F9

EVALUACIÓN DE LA PROPUESTA June se da cuenta de que necesita elaborar un modelo de simulación para que le ayude a responder dos preguntas acerca de la distribución del VNA: (1) ¿Cuál es la media o el valor esperado del VNA? Y (2) ¿Cuál es la probabilidad de que el VNA asuma valor negativo? Entre más grande sea el valor de VNA y –quizás más importante- entre menos posible sea el VNA logre ser negativo, más atractiva será la propuesta de añadir el G-9 a la línea de producción de PROTRAC. Así que el siguiente paso es ejecutar automáticamente la simulación cierto número de veces y capturar el VNA resultante en una hoja de cálculo por separado. Esto se puede hacer con bastante facilidad con el comando Tabla de datos de la hoja de cálculo, como sigue: 1.- Dar clic derecho en la ceja de la parte inferior izquierda 2.- Seleccione Insertar, después Hoja de Calculo 3.- Hacer doble clic en la nueva ceja de la parte inferior que tenga el nombre de la hoja de cálculo (por lo general un nombre predeterminado como “Hoja 3”) y poner el nombre de “100 iteraciones”. 4.- Escriba el valor de comienzo (1) en la celda A2 y oprima Intro. 5.- Haga clic de nuevo en la celda A2. 6.- En el menú Inicio seccionar rellenar donde aparece la flecha de modificar y seleccione Series.

7.- Seleccione la opción Series en columnas y escriba el incremento de 1 y el límite un valor final de 100.

8.- Haga clic en aceptar. Excel llenara automáticamente la columna de la celda seleccionada (A2) con valores ascendentes en 1 (el valor inicial) hasta que alcance el valor de 100. Afín de encontrar el VNA, escribimos la siguiente formula en la celda B2 de la nueva hoja de cálculo: poner = e irse a la hoja de cálculo de demanda aleatoria y posicionarse en B19 y darle enter. Ahora utilizaremos el comando Tabla de datos. Esto se hace como sigue: 1.- Seleccione el rango A2:B101 2.- Haga clic en el menú Datos y después en análisis “y si” y luego Tabla de datos como se muestra a continuación.

3.- En el cuadro de dialogo Tabla, determine que la celda C1 sea la columna de entrada.

4.- Haga clic en Aceptar. Excel entonces sustituirá cada valor en el rango de A2 a A101 en la celda C1 (que no tiene ningún efecto real), recalcularla la hoja de cálculo y guardara los VNA resultantes en las

celdas adyacentes de la columna B. Después de hacer esto, usted deberá tener una lista de los valores en la columna B respectivamente 100 valores posibles del VNA, similares a los que se muestran en la figura 11.11. Los números que usted generen no corresponderán con los que aparecen en la figura 11.11. Recuerde que este procedimiento produce una muestra aleatoria de 100 ensayos a partir de un número infinito de posibles resultados. Con un poco de suerte, las características generales de su muestra deberían ser similares a las que aparecen aquí. A fin de enfocarnos en estas 100 observaciones, vamos a convertir las formulas en los valores siguiendo un procedimiento simple: 1. 2. 3. 4.

Seleccione el rango B2:B101. Dar clic derecho y después en Copiar. Dar clic derecho, después en Pegado especial. Seleccione la opción Valores, después haga clic en Aceptar.

A fin de obtener un resumen de nuestras 100 iteraciones, podemos utilizar la herramienta de análisis datos predeterminada Excel. (Si no aparece la opción de análisis de datos en su menú Datos, en la sección herramienta de datos dar clic derecho después seleccionar “personalizar barra de herramientas de acceso rápido” Seleccione la opción Complementos del menú Herramientas, después seleccione la opción Complementos de Excel a lado de Administrar después dar clic en el botón Ir, en el siguiente paso seleccionar la herramienta “Herramientas para Análisis” y después clic en aceptar). Esta herramienta genera numerosas estadísticas descriptivas (por ejemplo, la media, la desviación estándar, el mínimo, el máximo, etc.) automáticamente. Para utilizarlo, haga lo siguiente:

Cuadro de complementos

Cuadro de complementos 1. Menú Datos, después análisis de datos (se encuentra en la parte superior derecha).

2. Haga Clic en Estadísticas descriptivas, y complete el cuadro de dialogo que aparece en la figura 11.12. 3. Haga clic en Aceptar.

Resultados de Simulación de Wilson

Cuadro de dialogo Estadísticas Descriptivas Con base en esta muestra limitada, los resultados aparecen en la figura 11.13, e indican que la media estimada de VNA es de $12,100.37 y la desviación estándar es más bien grande, de $12,351.69.

Riesgo hacia abajo y riesgo hacia arriba June también desea saber cuál es el mejor y el peor resultado posible. E n esta muestra, podemos ver en la figura 11.13 que el VNA más grande es de $39,955.98 y el menor es de -$11,100.37. Esto le da una mejor idea del rango del VNA posible que puede ocurrir. Distribución de resultados Aunque los datos de la figura 11.13 ofrecen más información que en el caso básico VNA, existen otros factores que debemos considerar. ¿Qué tan posible es que ocurran estos resultados extremos (mejor caso, peor caso)? A fin de responder a esta pregunta, necesitamos saber algo acerca de la distribución de VNA. Afortunadamente, Excel también tiene algunas características predeterminadas para ayudarnos. Para generar un histograma (una distribución grafica de los VNA) así como una tabla de frecuencia numérica, solo siga estos pasos: 1. Menú Datos, después en Análisis de datos. 2. Escoja Histograma y complete su cuadro de dialogo como se muestra en la figura 11.14. 3. Haga clic en Aceptar.

MODELOS PROBABILÍSTICOS

FIGURA 11.13 Resumen de las estadísticas Descriptivas.

Celda E21 E22

Formula =$E$4-1.96*$E$8/RAIZ($E$16) =$E$4+1.96*$E$8/RAIZ($E$16)

Cuadro de dialogo del histograma En este caso, hemos guardado los resultados en una hoja de cálculo llamada “distribución el VNA”. Esta (que se refiere al valor neto de presente) aparece en la figura 11.15. La columna frecuencia mostrada en la columna B indica el número de nuestros 100 ensayos que cayeron dentro de los parámetros definidos por Excel en la columna. Por ejemplo, una observación fue menor que o igual a ($11,100.37); 10 observaciones fueron mayores que ($5,994.73) y menores que o iguales a ($889.10). El mayor número de observaciones (16) ocurrió en el intervalo de mayores que $9,322.17 y menores que o iguales a $14,427.81. El histograma que se muestra a la derecha de la figura 11.15 de una representación visual de los resultados posibles para el VNA. Tiene una forma más o menos de campana. La columna % acomunado que se muestra (la columna C) indica que más de 15% de las observaciones VNA fueron negativas (menores que 0). Estos datos pueden resultar útiles para responder a otras preguntas que podría formular June o su jefe financiero. ¿Qué tan confiable es la simulación? June ahora tiene las respuestas a sus dos preguntas acerca de la distribución del VNA: (1) ¿cuál es el valor medio del VNA? (A:$12,100), y (2) ¿Cuál es la probabilidad de que el VNA asuma un valor negativo? (A: > 15%). Ahora haremos más preguntas: ¿Cuánta confianza tenemos en las respuestas que obtuvo June? ¿Tendríamos más confianza si ella hiciera más ensayos?

Histograma y tabla de frecuencias Con certeza, se piensa que entre más ensayos hagamos, más confianza tendremos en nuestras respuestas. Pero ¿Qué tanta confianza podemos tener en las 100 iteraciones que ya hicimos? Desde el mundo de la estadística, recordemos que podemos construir intervalos de confianza con base en los resultados obtenidos. Por ejemplo, podemos tener 95% de confianza en que el verdadero valor medio VNA está dentro del intervalo De 1.95 desviación estándar reportada, dividida entre la raíz cuadrada del número de ensayos. Este intervalo de 95% de confianza en la media fue calculado por June y dado a conocer en la figura 11.13 como ($9.679.44, $14,521.30 dicho de otra manera podemos tener 95% de confianza en que la verdad media del VNA está en algún punto entre $9.679 y $14,521 siendo nuestra mejor aproximación $12,100. Debemos tener cuidado con no caer en la trampa del “valor esperado”! muchos estudiantes creen que la media real del VNA puede calcularse siempre estableciendo todas las variables alectorias por sus valores medios ¡( establecer todas las demandas en 10 en este ejemplo).esto es lo que June tenían en su hoja de cálculo inicial (véase la figura 11.9) pero no hay garantía alguna del que VNA obtenido en esta manera sea también simulada verdadera aunque puede parecer lógico hay algunas debilidades potenciales en este razonamiento. Primero la manera en que se utiliza de la demanda para calculas los flujos de efectivo anule para después convertidos en un solo número de VNA (o cualquier otra media de desempeño) puede ser altamente no lineal. Existen casos donde este razonamiento intuitivo funcionar pero esto no ocurrirá siempre Un simple ejemplo de una de las expresiones de la vida diaria debería convencerlo de que el VNA(o cualquier otra media de desempeño) calculada con el valor esperado de las variables alectorias no es necesario igual al VNA esperado considera una fila o cola

De espera en la tienda de comestible suponga que el tiempo esperado para atender a un cliente es de 0.9 minutos y que el tiempo promedio entre la llegada de los clientes es de 1 minuto. Al examinar la situación utilizando los valores esperados usted presidiría que el tiempo de espera será en promedio de 0 minutos (evito a que los clientes son atendidos más rápido de lo que llegan) pero todos sabemos que el tiempo de espera promedio es motor que 0 minutos a pesar de que a veces somos afortunados y no tenemos que esperar. En resumen June necesita más de 100 ensayos si desea respuesta más precisas a sus preguntas ¿Qué es lo que hemos aprendido? 1. Aumentar el número de ensayo puede dar una mejor estimación del rendimiento esperado pero incluso con un mayor número de ensayos puede haber entre el promedio simulado y el rendimiento esperado real Observe que el VNA esperado real en el ejemplo de June resulta ser de $ 12455.6; el VNA promedio de 100 muestras fue de 12100.37 (un error de 2.85 %). 2. Las simulaciones pueden proporcionar información útil sobre la distribución de resultados incluso con una muestra pequeña de 100 ensayos hubo una indicación (por probabilidad de mayor de 0.15) de que el proyecto puede resultar en un VNA negativo. Esta información valiosa y es algo que no se podría haber determinado con solo el análisis del costo básico, o incluso con un análisis de riesgo hacia arriba/hacia abajo. 3. los resultados de la simulación son sensibles a las hipótesis que afectan los parámetros de entrada. La sección siguiente nuestra que si June cambiar sus suposiciones acerca de la distribución de la demanda (a una distribución de POISSON con la misma medida de 10) abría un impacto importante en la probabilidad de que se produjera un VNA negativa (se incrementa de aproximadamente 0.15 a cerca de 0.27). Quizás el impacto más importante de la simulación ocurre en el proceso de toma de decisiones. Si June no hubiera llevado a cabo un análisis de simulación, habría dado una entusiasta recomendación a la propuesta de adición a la línea de equipo pesado con base en la media del VNA. Sin embargo, después de llevar acabo la simulación, ella piensa que el proyecto es muy arriesgado como para recomendarlo. Si bien este es un juicio cualitativo de su parte, ella puede apoyarlo con los resultados cuantitativos del modelo de simulación. Como hemos observado anteriormente, los modelos no liberan a los administradores de la responsabilidad de tomar decisiones, pero si proporcionan información adicional para tomar esas decisiones de una manera bien informada. ENTREGAR DE REPORTE 1. El alumno elabore un problema de simulación de sistemas y lo envié al correo [email protected] a) Archivo de Word y PDF con sus datos corregidos de la simulación.

b) Archivo de Excel con los datos de su simulación.