Cómo realizar pronósticos (de ventas, precios, etc) con Excel
Excel posee varias facultades que pueden facilitar el trabajo de realizar pronósticos de diferente índole. A continuación se nombran algunas.
Primero que todo, está la función Estimacion.Lineal, que realiza una estimación por Mínimos Cuadrados Ordinarios.Devuelve estadísticas que describen una tendencia lineal que coincide con puntos de datos conocidos, usando el método anteriormente mencionado (MCO). Debido a que esta función devuelve una matriz de valores, debe ser introducida como una fórmula de matrices.
La ecuación para la línea es:
y = mx + b o
y = m1x1 + m2x2 + ... + b (si hay varios rangos de valores X)
donde el valor Y dependiente es función de los valores X independientes. Los valores m son coeficientes que corresponden a cada valor X, y b es un valor constante. Observe que Y, X y m pueden ser vectores. La matriz que devuelve ESTIMACION.LINEAL es {mn,mn-1,...,m1,b}. ESTIMACION.LINEAL también puede devolver estadísticas de regresión adicionales.
Sintaxis
ESTIMACION.LINEAL(conocido_y,conocido_x,constante,estadística)
Conocido_y es el conjunto de valores de y que se conocen en la relación y = mx+b.
Si la matriz definida por el argumento conocido_y ocupa una sola columna, cada columna de conocido_x se interpreta como una variable separada.
Si la matriz definida por el argumento conocido_y ocupa una sola fila, cada fila de conocido_x se interpreta como una variable separada.
La matriz definida por el argumento conocido_x puede incluir uno o varios conjuntos de variables. Si se usa una sola variable, conocido_y y conocido_x pueden ser rangos con cualquier forma, siempre y cuando sus dimensiones sean iguales. Si se usa más de una variable, conocido_y tiene que ser un vector (es decir, un rango compuesto por una fila o por una columna).
Si se omite conocido_x, se asume que ésta es la matriz {1;2;3;...} que tiene el mismo tamaño que conocido_y.
Si el argumento constante es VERDADERO o se omite, b se calcula normalmente.
Si constante es FALSO, b se establece como igual a 0 y los valores m se ajustan para encajar en y = mx.
Si estadística es VERDADERO, ESTIMACION.LINEAL devuelve las estadísticas de regresión adicionales, de forma que la matriz devuelta es {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.
Si estadística es FALSO o se omite, ESTIMACION.LINEAL sólo devuelve los coeficientes m y la constante b.
Conocido_x es un conjunto opcional de valores x en la relación y = mx+b.
Constante es un valor lógico que especifica si se ha de forzar a la constante b a ser igual a 0.
Estadística es un valor lógico que especifica si se deberán devolver estadísticas de regresión adicionales.
Las estadísticas de regresión adicional son las que se indican a continuación.
| ESTADÍSTICA | DESCRIPCIÓN |
|---|---|
| se1,se2,...,sen | Los valores de error estándar para los coeficientes m1,m2,...,mn. |
| seb | El valor de error estándar para la constante b (seb = #N/A cuando constante es FALSO). |
| r2 | El coeficiente de determinación. Compara los valores y estimados y reales, y los rangos con valor de 0 a 1. Si es 1, hay una correlación perfecta en la muestra, es decir, no hay diferencia entre el valor y estimado y el valor y real. En el otro extremo, si el coeficiente de determinación es 0, la ecuación de regresión no es útil para predecir un valor y. Para obtener información sobre el cálculo de r2, consulte la sección de "Observaciones" más adelante en este mismo tema. |
| sey | El error estándar para la estimación y. |
| F | La estadística F o valor F observado. Utilice la estadística F para determinar si la relación observada entre las variables dependientes e independientes se produce por azar. |
| df | Grados de libertad. Utilice los grados de libertad para encontrar valores F críticos en una tabla estadística. Compare los valores que encuentre en la tabla con la estadística F devuelta por ESTIMACION.LINEAL para determinar un nivel de confianza para el modelo. Para obtener información sobre el cálculo de df, consulte el apartado "Observaciones" más adelante en este mismo tema. El ejemplo 4 muestra el uso de F y df. |
| ssreg | La suma de regresión de los cuadrados. |
| ssresid | La suma residual de los cuadrados. Para obtener información sobre el cálculo de ssreg y ssresid, consulte la sección "Observaciones" más adelante en este mismo tema. |
Observaciones
Puede describir cualquier línea recta con la pendiente y la intercepción Y:
Pendiente (m):
Para hallar la pendiente de una línea, frecuentemente indicada por m, tome dos puntos de la línea, (x1,y1) y (x2,y2): la pendiente es igual a (y2 - y1)/(x2 - x1).
Intercepción Y (b):
La intercepción Y de una línea, frecuentemente indicada por b, es el valor de Y en el punto en que la línea cruza el eje X.
La ecuación de una línea recta es y = mx + b. Cuando conozca los valores de m y b podrá calcular cualquier punto de la línea insertando el valor Y o el valor X en esa ecuación. También puede utilizar la función TENDENCIA.
Si sólo tiene una variable X independiente, puede obtener los valores de la pendiente y de la intercepción Y directamente utilizando las fórmulas siguientes:
Pendiente:
=INDICE(ESTIMACION.LINEAL(conocido_y,conocido_x),1)
Intersección Y:
=INDICE(ESTIMACION.LINEAL(conocido_y,conocido_x),2)
La exactitud de la línea calculada por ESTIMACION.LINEAL depende del grado de dispersión de los datos. Cuanto más lineales sean los datos, más exacto será el modelo ESTIMACION.LINEAL. ESTIMACION.LINEAL utiliza el método de mínimos de los cuadrados para determinar el mejor ajuste para los datos. Si sólo tiene una variable X dependiente, los cálculos para m y b se basan en las fórmulas siguientes:
Ejemplo 1 Pendiente e intercepción Y
|
|
Ejemplo 2 Regresión lineal simple
|
|
En general, SUMA({m,b}*{x,1}) igual a mx + b, el valor Y estimado para un valor X dado. También puede utilizar la función TENDENCIA.También está la función Estimacion.Logaritmica, que hace básicamente lo mismo que la primera, sólo que para una función logarítmica.
Por otro lado, está la función Pronóstico, que calcula o pronostica un valor futuro a través de los valores existentes. La predicción del valor es un valor y teniendo en cuenta un valor x. Los valores conocidos son valores x y valores y existentes, y el nuevo valor se pronostica utilizando regresión lineal. Esta función se puede utilizar para realizar previsiones de ventas, establecer requisitos de inventario o tendencias de los consumidores.
Sintaxis
PRONOSTICO(x;conocido_y;conocido_x)
X es el punto de datos cuyo valor desea predecir.
Conocido_y es la matriz o rango de datos dependientes.
Conocido_x es la matriz o rango de datos independientes.
Observaciones
Si x no es numérico, PRONOSTICO devuelve el valor de error #¡VALOR!
Si no se ha especificado ningún valor para conocido_y o conocido_x, o si contienen un número diferente de puntos de datos, PRONOSTICO devuelve el valor de error #N/A.
Si la varianza de conocido_x es igual a cero, PRONOSTICO devuelve el valor de error #¡DIV/0!
La ecuación de la función PRONOSTICO es a + bx, donde:
y:
y donde x e y son las medias de muestra PROMEDIO(conocido_x) y PROMEDIO(conocido y).
Ejemplo
|
Fuente: office.microsoft.com
|
No hay comentarios:
Publicar un comentario