Se constata que el precio se va encareciendo a un ritmo superior en el extranjero, por lo que, si la evolución continúa al mismo ritmo, llegará un momento en que ambos precios se igualarán y, a partir de entonces, será más económica la compra en el mercado nacional.
Como es lógico, interesa calcular cuándo se produce el encuentro. Para ello, trazaremos el gráfico de la evolución de precios, obtendremos las líneas de regresión (líneas de tendencia en Excel) y calcularemos dónde se cruzan.
Seleccionamos B2:D11 y accedemos a Insertar + Dispersión + Dispersión con líneas rectas y marcadores.
Hacemos doble clic en la leyenda y, en Opciones de leyenda, ponemos una marca en Inferior.
Para hallar la línea de tendencia, hacemos clic con el botón derecho en la línea de la serie Nacional (marrón), elegimos Agregar línea de tendencia y ponemos:
- Opciones de línea de tendencia → Tipo de tendencia o regresión: Lineal / Nombre de la línea de tendencia → Personalizado: Lin_Nac / Extrapolar → Adelante: 20 / Presentar ecuación en el gráfico: (activado).
Repetimos la operación con la línea de la serie Extranjero (azul), poniendo los mismos valores excepto el nombre:
- Opciones de línea de tendencia → Tipo de tendencia o regresión: Lineal / Nombre de la línea de tendencia → Personalizado: Lin_Ext / Extrapolar → Adelante: 20 / Presentar ecuación en el gráfico: (activado).
Podemos cambiar el tamaño del gráfico, los colores de las líneas, marcadores, fondos..., y dejarlo a nuestro gusto. También conviene arrastrar las ecuaciones a lugares que permitan asociarlas fácilmente con sus correspondientes líneas de regresión.
Se aprecia que entre los meses 19 y 20 se cruzan las dos líneas. A partir de ese momento será más rentable comprar en el mercado nacional. Aunque esto es una previsión, y no tiene mucho sentido conocer el momento exacto del encuentro, podemos calcular matemáticamente cuándo se producirá usando la herramienta de análisis de hipótesis Buscar objetivo. Lo haremos en F2:I3.
Ponemos 1 en F3 (vale cualquier cifra). En el mes 1, los precios en el Extranjero y en el mercado Nacional son 1,1 y 35,5 respetivamente pero las ecuaciones de las líneas de regresión nos darán valores ligeramente diferentes (no pasan exactamente por los puntos del gráfico).
En G3:
=3,7033*F3-4,0944 [Resultado: -0,3911]
En H3:
=1,7433*F3+34,017 [Resultado: 35,7603]
En I3:
=G3-H3 [Resultado: -36,1514]
Con Buscar objetivo calcularemos el valor de hay que poner en G3 para que la diferencia de precios obtenida en I3 sea 0.
Accedemos a Datos + Análisis Y si + Buscar objetivo. En Definir la celda, ponemos I3; en Con valor, escribimos 0; en Para cambiar la celda, seleccionamos F3. Pulsando Aceptar Excel nos indica que ha encontrado la solución.
Cuando transcurran 19,4445918 meses se prevé que se produzca la coincidencia de precios.
El ejercicio puede resolverse sin crear el gráfico. La función PRONOSTICO devuelve los mismos valores que los obtenidos usando las ecuaciones de las líneas de regresión.
PRONOSTICO(x;conocido_y;conocido_x)
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.
- x: Obligatorio. El punto de datos cuyo valor se desea predecir.
- conocido_y: Obligatorio. La matriz o rango de datos dependientes.
- conocido_x: Obligatorio. La matriz o rango de datos independientes.
Vamos a repetir el ejercicio en otra hoja usando la función PRONOSTICO.
En F3 escribimos 1.
En G3:
=PRONOSTICO(F3;C3:C11;B3:B11) [Resultado: -0,39111111]
En H3:
=PRONOSTICO(F3;D3:D11;B3:B11) [Resultado: 35,76]
En I3:
=G3-H3 [Resultado: -36,1511111]
Los valores no coinciden exactamente con los obtenidos por el método de las líneas de tendencia debido al número limitado de decimales empleados. PRONOSTICO utiliza todos los decimales posibles, de modo que su resultado es más preciso.
Ahora, accedemos a Datos + Análisis Y si + Buscar objetivo y volvemos a poner los mismos datos:
El resultado es:
Que buena nota.
ResponderEliminarMe pregunto si puedo utilizar la función pronóstico invirtiendo el valor a predecir, es decir que en lugar de de predecir y, prediga un valor de x usando un valor de y. No se si me explico correctamente. Gracias
existe alguna función que prediga pero con funciones exponenciales ?
ResponderEliminarfelicitaciones por esta explicacion mas completa de pronostico, trabajo en un call center como analista.
ResponderEliminarcomo hago si la opcion de agregar una linea de tendencia me sale bloqueada? Gracias
ResponderEliminarHOLA
ResponderEliminarme gustaría saber si es posible tener varias datos de excel que forman varias curvas y poder encontrar la unica linea de tendencia que represente a todas??