practica informatica excel PDF

Preview

Summary

practica estadistica informatica excel, practica con enunciados y sus respectivas respuestas...

Description

FACULTAD DE CIENCIAS EMPRESARIALES ASIGNATURA: ESTADÍSTICA EMPRESARIAL I

1ª Práctica de Informática Temas 1 y 2: Distribuciones de frecuencias unidimensionales. Distribuciones de frecuencias bidimensionales. Regresión. Tema 1. Distribuciones de Frecuencias Unidimensionales EJERCICIO 1 La siguiente tabla muestra la evolución de la tasa de actividad (media anual) en Andalucía expresadas en porcentajes: Año

Tasa de actividad 1990 47,2 1991 47,2 1992 47,2 1993 47,1 1994 47,4 1995 47,4 1996 48,2 1997 48,7 1998 48,7 1999 49,1 2000 49,5 2001 49,7 FUENTE:EPA, (INE)

Responda a las siguientes cuestiones a) Clasifique la variable que representa a la tasa de actividad. b) Represente los datos mediante un diagrama de barras. c) Halle la media aritmética, geométrica y armónica, así como la mediana, la moda. d) Comente la representatividad de la media. e) Halle: - Los puntos que dividen a la distribución en 4 grupos de igual frecuencia. - Los puntos que dividen a la distribución en 10 grupos de igual frecuencia. - El percentil 15. f) Calcule e interprete los coeficientes de asimetría y de curtosis de la distribución.

Solución del Ejercicio 1 a) La variable que representa a la tasa de actividad es de carácter cuantitativa. Concretamente, corresponde a una variable cuantitativa en escala de razón o proporción. b) Lo primero que debemos hacer es escribir los datos que disponemos en una hoja en blanco en Ms Excel. En nuestro caso, podemos copiar y pegar en la celda B2 la tabla proporcionada, quedando así:

1

Para la representación gráfica que se nos pide, seleccionamos la columna que contienen los datos que queremos presentar (C2:C14), incluido el título de ésta. A continuación nos iremos al a la pestaña Insertar

Elegimos la categoría de gráfico que deseemos, en nuestro caso “Columnas”. Y dentro de dicha categoría la opción concreta que nos interese, en nuestro caso en 2-D la primera “Columna agrupada”. Para elegir entre las diversas opciones, podemos leer el comentario que aparece cuando pasamos el cursor sobre cada una de las opciones (en cualquier caso dependerá de los datos que dispongamos y de lo que queramos mostrar) y pulsamos..

Una vez que ha salido la representación gráfica, aparece la pestaña diseño, donde aparecen las siguientes opciones: tipo, datos, diseño gráfico, estilos de diseño y ubicación. 2

Para seleccionar los “Rótulos del eje de categorías (X):” pulsamos el botón derecho del ratón sobre el gráfico, y pulsamos “Seleccionar datos”. En la nueva pantalla, podemos editar tanto el eje de categorías (X) como el eje de las Y.

Pulsamos en el boton editar seleccionamos los años, es decir B3:B14, y pulsamos “Aceptar”:

Pulsamos “Aceptar”:

3

Ahora podemos (aunque es opcional) escribir o modificar los distintos campos de la ventana para mejorar la apariencia del gráfico, pulsando en las etiquetas correspondientes.

c), d), e) y f) Para calcular los distintos estadísticos que se piden procederemos siempre de la misma forma. En primer lugar preparamos una tabla que contenga los distintos apartados del problema para ir rellenando cada una de las celdas deseadas mediante la pestaña ”Fórmulas”..

En esta ventana podemos seleccionar “Estadística” o “Todas” dentro de “Seleccionar una categoría”. En la lista de funciones que aparece más abajo buscamos la función deseada: 4

1. 2. 3. 4. 5. 6. 7. 8. 9. 10.

Promedio() para la media aritmética. Media.armo() para la media armónica. Media.geom() para la media geométrica. Moda.uno() para la moda. Mediana() para la mediana. Cuartil() para un cuartil. Percentil.inc() para calcular un percentil. Coeficiente.asimetría() para el coeficiente de asimetría. Curtosis() para el coeficiente de curtosis. Var.p() y Desvest.p() para la varianza y la desviación típica respectivamente.

Así, el contenido de las celdas vacías debe quedar como sigue: a)

b)

e)

f)

Media aritmética = Media geométrica =

=PROMEDIO(C3:C14) =MEDIA.GEOM(C3:C14)

Media armónica =

=MEDIA.ARMO(C3:C14)

Mediana =

=MEDIANA(C3:C14)

Moda =

=MODA.UNO(C3:C14)

Varianza =

=VAR.P(C3:C14)

Des. Típica =

=DESVEST.P(C3:C14)

CV =

=F9/F2

Q1 =

=PERCENTIL.INC(C3:C14;0,25)

Q2 = Mediana=

=PERCENTIL.INC(C3:C14;0,50)

Q3 =

=PERCENTIL.INC(C3:C14;0,75)

Deciles 10% 20% 30% 40% 50% 60% 70% 80% 90%

Tasa =PERCENTIL.INC(C3:C14;E17) =PERCENTIL.INC(C3:C14;E18) =PERCENTIL.INC(C3:C14;E19) =PERCENTIL.INC(C3:C14;E20) =PERCENTIL.INC(C3:C14;E21) =PERCENTIL.INC(C3:C14;E22) =PERCENTIL.INC(C3:C14;E23) =PERCENTIL.INC(C3:C14;E24) =PERCENTIL.INC(C3:C14;E25)

Percentil 15 =

=PERCENTIL.INC(C3:C14;0,15)

Asimetría = Curtosis =

=COEFICIENTE.ASIMETRIA(C3:C14) =CURTOSIS(C3:C14)

Cuyos resultados son:

5

Comentarios: 1. Nótese que la media armónica es menor que la geométrica y que ésta a su vez es menor que la aritmética. 2. La mediana no coincide con la media aritmética. Esto es debido a la asimetría de la distribución. 3. Para analizar la representatividad de la media hemos calculado CVPearson como:

CVPearson 

S 0,939192   0,0195 X 48,15

lo que indica que la media es muy

representativa. 4. El coeficiente de asimetría es positivo luego la distribución es asimétrica positiva o asimétrica a la derecha. 5. El coeficiente de curtosis es negativo lo que indica que la distribución es platicúrtica.

6

Tema 2. Distribuciones de Frecuencias Bidimensionales. Regresión. EJERCICIO 2 La siguiente tabla proporciona 10 valores correspondientes a las siguientes variables tomadas de cierta cadena de supermercados: Y = Ventas mensuales (en miles de €) X1 = Número de horas que permanece abierto el supermercado al día X2 = Inversión mensual en publicidad (en miles de €) Y 12 5 25 7 6 14 50 21 33 5

X1 8 10 11 8 7 9 12 8 10 9

X2 0,5 0,2 2,0 0,1 0,2 1,0 5,0 2,0 2,0 0,3

Dado que estamos interesados en estudiar la relación de la variable ventas mensuales en función, por un lado, del número de horas que permanece abierto el supermercado, y por otro lado, de la inversión realizada en publicidad con el objetivo de realizar predicciones fiables. Responda a las siguientes cuestiones: a) Estimar los diferentes modelos de regresión lineal bidimensional posibles para predecir las ventas mensuales. b) Estudiar la fiabilidad de los ajustes propuestos. c) Pronosticar el volumen de ventas de un supermercado que permanece abierto 8 horas diarias. d) Pronosticar el volumen de ventas de un supermercado que dedica mensualmente 3.000 € a publicidad. e) Calcular la varianza debida a la regresión y la varianza residual de los modelos utilizados para hacer las predicciones. Solución del Ejercicio 2 En primer lugar copiaremos los datos disponibles en una hoja en blanco de Ms Excel:

7

Resolveremos los distintos apartados para el ajuste lineal en el que se calcula el nivel de ventas (Y) en función de la Horas que permanece abierto el establecimiento (X1), esto es la regresión: Y  a  bX 1 y, posteriormente, procederemos de la misma forma a calcular el segundo ajuste lineal en el que se calcula el nivel de ventas (Y) en función de la Inversión mensual en publicidad (X2). En primer lugar, aunque no lo piden, siempre es recomendable representar la nube de puntos para confirmar visualmente si el ajuste lineal es apropiado. Seleccionamos los datos de las variables Y y X1 con sus correspondientes títulos (B1:C11) y accedemos a la pestaña “Inserta”, y en la opción gráfico, pulsamos “dispersión”. y el primer subtipo de gráfico (viene seleccionado por defecto):

Pinchamos dos veces para poder ponerle título al eje de valores (Y), eliminar el título del gráfico y colocar la leyenda abajo:

8

Se obtiene el gráfico que aparece a continuación:

X1 14 12 10 8 X1

6

4 2

0 0

10

20

30

40

50

60

Sin embargo, observamos que la primera variable de la hoja de datos es situada en el eje de abscisas, mientras que la segunda es situada en el eje de ordenadas. Para obtener la representación gráfica correcta tendríamos que cambiar el orden de las variables ya que nuestra primera variable en la hoja de datos es la variable dependiente (ventas), mientras que la segunda es la variable independiente (horas abierto). Para realizar ello, debemos picar dos con el botón derecho del ratón sobre el área de trazado del gráfico anterior y pulsar en seleccionar datos y luego en entradas de leyenda (series) / editar, apareciendo la siguiente ventana:

En dicha ventana tenemos que modificar el rango de valores de cada serie eligiendo para la variable X los valores de la variable “horas abierto” y para la variable Y los valores de la variable “ventas”. De este modo cada variable estará situada en el orden correcto, con lo que el gráfico obtenido será el siguiente:

9

En dicha representación podemos cambiar el nombre del gráfico simplemente picando con el botón izquierdo del ratón sobre el mismo. Teniendo seleccionado el gráfico, también podemos cambiar el rótulo de los ejes picando en el menú desplegable en Herramientas de gráficos / Presentación / Rótulos del eje. El gráfico definitivo queda del siguiente modo:

En dicha nube de puntos se puede apreciar una clara tendencia lineal y creciente de las ventas a medida que aumenta el número de horas que permanece abierto el establecimiento. Además, si deseásemos representar la recta de regresión en el gráfico anterior podemos picar sobre alguno de los puntos con el botón izquierdo del ratón y elegir la opción de agregar línea de tendencia. En dicha ventana debemos tener seleccionadas las opciones lineal, presentar ecuación en el gráfico y presentar el valor R cuadrado en el gráfico.

10

El gráfico quedaría del siguiente modo:

Pasemos pues a resolver el apartado a). Para ello usaremos la herramienta Regresión que está incluida en las Herramientas para análisis. Herramientas para análisis es un programa complementario de MS Excel. Está disponible al instalar Microsoft Office o Excel pero antes de utilizar la herramienta Regresión en Excel, se debe cargar Herramientas para análisis. Para hacerlo en Excel 2010, siga estos pasos: 1. En la pestaña Archivo, haga clic en Opciones.

11

2. En el cuadro de Opciones pulsa Complementos. 3.

4. Pulsar en el botón ir.

12

5. En el cuadro Complementos, haga clic para seleccionar Herramientas para análisis y, a continuación, haga clic en Aceptar (si Herramientas para análisis no aparece en el cuadro Complementos disponible, haga clic en Examinar para buscarlo) 6. Una vez realido lo anterior, nos debemos ir a la pestaña “Datos” y debe de aparecer una nueva opción “Análisis de Datos”

NOTA: Para hacerlo en Excel 2003 y en versiones anteriores de Excel, siga estos pasos: 1. En el menú Herramientas, haga clic en Complementos. 2. En el cuadro Complementos, haga clic para seleccionar Herramientas para análisis y, a continuación, haga clic en Aceptar (si Herramientas para análisis no aparece en el cuadro Complementos disponible, haga clic en Examinar para buscarlo) 7. Una vez que aparece debemos de pulsar “Análisis de datos”

8.

En dicha ventana, seleccionaremos de menú la opción Regresión y aceptamos, apareciendo la siguiente ventana:

13

9. En Rango Y de entrada incluiremos el rango de nuestra variable ventas. Para ello picharemos en su cuadrado con una flecha roja. De igual manera procederemos con Rango X de entrada. En Opciones de salida podemos elegir “En una hoja nueva” o en algún lugar de la hoja sobre la que estamos trabajando (Rango de salida). En nuestro caso seleccionaremos en una hoja nueva que titularemos RegresiónYX1:

10. Tras aceptar, iremos a la hoja que se acaba de crear con el título RegresiónYX1 donde nos encontraremos lo siguiente:

En la primera tabla encontramos el coeficiente de correlación lineal R  0.7306 que nos indica una dependencia moderada y positiva entre las ventas y las horas que permanece abierto. De hecho, el coeficiente de determinación lineal R 2  0.5338 nos confirma que la bondad del ajuste es del 53.38%, valor muy inferior al deseado para considerarlo un ajuste fiable. De la segunda tabla nos interesa la columna Suma de cuadrados pues en ella encontramos los datos necesarios para calcular la varianza explicada y la varianza residual. Antes hay que notar que Excel no proporciona directamente estos valores sino que nos indica el valor correspondiente a la suma de los cuadrados de las desviaciones respecto a la media. Podremos obtener las varianzas pedidas dividiendo por el número total de datos, en nuestro caso N = 10, las Sumas de los cuadrados anteriormente presentadas. Obteniendo las Varianzas Total, Explicada y Residual de la siguiente manera: N

VARIANZA TOTAL: s 2y 

 (y

i

 y) 2

i 1

 N 14

1961,6  196,16 10

N

(y

ti

2 yt

VARIANZA EXPLICADA: s 

 N

N

 (y

i

VARIANZA RESIDUAL: sry2 

 y)2

i 1

 yti ) 2

i1

 N

1047,22  104,72 10

914,37  91,437 10

También, de la tercera tabla nos interesa la columna Coeficientes en donde encontramos la pendiente y la ordenada del origen de la recta de regresión. Así, la recta de regresión que explica Y en función de X1 es Y  -46,2593 6,9630X 1 . A partir de ella, y con idea de pronosticar el volumen de ventas de un supermercado que permanece abierto 8 horas diarias, bastará con sustituir X 1  8 en la recta. Por ejemplo, en la celda B20 podemos escribir =B17+8*B18 y obtener yˆ(8)  9.44 millones de euros. Para realizar la regresión lineal entre las Ventas mensuales (Y) y la Inversión mensual en publicidad (X2) procedemos de igual forma obteniendo los siguientes resultados:

El coeficiente de correlación lineal en esta ocasión es mucho mejor que el anterior pues R  0.97 que nos indica una dependencia alta y positiva entre las ventas y la inversión en publicidad. El coeficiente de determinación lineal R 2  0.9409 nos confirma que la bondad del ajuste es del 94.09%. Ahora, las Varianzas Total, Explicada y Residual valen: N

VARIANZA TOTAL: s 2y 

 ( y  y)

2

i

i 1

 N N

VARIANZA EXPLICADA: s y2t 

(y

ti

i 1

 y)2 

N N

2 VARIANZA RESIDUAL: sry 

1961,6  196,16 10

 (y

i

 yti )2

i1

 N

1845,7565  184,58 10

115,8434  11,58 10

La recta de regresión que explica Y en función de X2 es Y  5,2535  9,4335X2 . A partir de ella, y con idea de pronosticar el volumen de ventas de un supermercado que invierte 3000 euros en publicidad, bastará con sustituir X 2  3 en la recta. Por ejemplo, en la celda B20 podemos escribir =B17+3*B18 y obtener yˆ (3)  33.55 millones de euros.

15

Observación. Nótese que en Herramientas/Análisis de datos podemos encontrar otros paquetes muy útiles. Cabe destacar el llamado Estadística Descriptiva, que nos calcula los principales estadísticos descriptivos de una variable (media, mediana, moda, varianza, desviación estándar y los coeficientes de asimetría y curtosis, entre otros). Por tanto, parte del ejercicio 1 se podía haber resuelto con esta herramienta. No obstante, es importante notar que la varianza y la desviación estándar que calcula esta herramienta es la muestral (var.s(-) y desvest.s(-)) mientras que lo que se pidió en el ejercicio 1 eran la varianza y la desviación típica poblacionales (var.p(-) y desvest.p(-)).

PROBLEMAS PARA PROFUNDIZAR EJERCICIO 1 Para estudiar la cuantía de los salarios de los trabajadores de una empresa se seleccionan aleatoriamente 15 trabajadores de la misma, obteniendo los siguientes valores expresados en cientos de euros: 15, 18, 20, 13’5, 17’5. 22, 10, 12’3, 13’2, 19, 14’5, 20, 16’6, 18, y 23’2. a) Calcular la media, la varianza y la mediana. b) Calcular el coeficiente de asimetría y el de curtosis, interpretando los resultados obtenidos. c) Calcular la media y la varianza para el caso en el que el empresario decida aumentar los salarios de todos los trabajadores en 100 euros. d) Calcular la media y la varianza para el caso en el que el empresario decida aumentar los salarios de todos los trabajadores un 10%. EJERCICIO 2 La siguiente tabla refleja el gasto familiar y el nivel de ingresos (en €) de varias economías domésticas de cierta localidad: GASTOS INGRESOS

610 1120

720 1200

800 1310

840 1470

950 1500

1000 1750

a) Realizar la nube de puntos, representando en el eje de abscisas los ingresos y en el eje de ordenadas los gastos. b) Obtener un modelo de regresión lineal que explique los gastos en función de los ingresos. Dar una medida de la fiabilidad del modelo. c) Estimar los gastos que tendría una familia con unos ingresos de 1.650 €. d) Calcular la varianza debida a la regresión y la varianza residual del modelo.

16...