5.6: Apéndice- Realización de Análisis de Regresión con Excel
- Page ID
- 65982
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)
( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\id}{\mathrm{id}}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\kernel}{\mathrm{null}\,}\)
\( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\)
\( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\)
\( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)
\( \newcommand{\vectorA}[1]{\vec{#1}} % arrow\)
\( \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow\)
\( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vectorC}[1]{\textbf{#1}} \)
\( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)
\( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)
\( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
Objetivos de aprendizaje
- Realizar análisis de regresión usando Excel.
Pregunta: A menudo se realiza análisis de regresión para estimar costos fijos y variables. Muchos paquetes de software diferentes tienen la capacidad de realizar análisis de regresión, incluido Excel. Este apéndice proporciona una ilustración básica de cómo usar Excel para realizar análisis de regresión. Los cursos de estadística cubren este tema con mayor profundidad. ¿Cómo se utiliza el análisis de regresión para estimar costos fijos y variables?
- Contestar
-
Como se señala en el capítulo, el análisis de regresión utiliza una serie de ecuaciones matemáticas para encontrar el mejor ajuste posible de la línea a los puntos de datos. Para los efectos de este capítulo, el objetivo final del análisis de regresión es estimar costos fijos y variables, los cuales se describen en forma de ecuación de\(\text{Y} = \mathcal{f} + \mathcal{v} \text{X}\). Recordemos que la siguiente salida de Excel se proporcionó anteriormente en el capítulo con base en los datos presentados en la Tabla 5.4 para Bikes Unlimited.
Coeficientes interceptar en y 43,276 x variable 53.42 La ecuación resultante para estimar los costos de producción es Y = $43,276 + $53.42X. Ahora describimos los pasos a realizar en Excel para obtener esta ecuación.
Paso 1. Confirme que el paquete Análisis de datos está instalado.
Vaya a la pestaña Datos en la barra de menú superior y busque Análisis de datos. Si aparece Análisis de datos, está listo para realizar un análisis de regresión. Si no aparece Análisis de datos, vaya al botón de ayuda (denotado como signo de interrogación en la esquina superior derecha de la pantalla) y escriba Herramientas de análisis. Busque la opción Cargar la herramienta de análisis y siga las instrucciones dadas.
Paso 2. Ingresa los datos en la hoja de cálculo.
Usando una nueva hoja de cálculo de Excel, ingrese los puntos de datos en dos columnas. Los datos mensuales del Cuadro 5.4 incluyen los Costos de Producción Total y las Unidades Producidas. Por lo tanto, utilice una columna (columna A) para ingresar los datos de Costos de Producción Total y otra columna (columna B) para ingresar datos de Unidades Producidas.
Paso 3. Ejecutar el análisis de regresión.
Usando la misma hoja de cálculo establecida en el paso 2, seleccione Datos, Análisis de datos y Regresión. Aparece un cuadro que requiere la entrada de varios ítems necesarios para realizar la regresión. El rango Y de entrada requiere que resalte los datos del eje y, incluido el encabezado (celdas B1 a B13 en el ejemplo que se muestra en el paso 2). Rango X de entrada requiere que resalte los datos del eje x, incluido el encabezado (celdas C1 a C13 en el ejemplo que se muestra en el paso 2). Marque la casilla Etiquetas; esto indica que la parte superior de cada columna tiene un encabezado (B1 y C1). Seleccione Nuevo libro de trabajo; esto colocará los resultados de regresión en un nuevo libro de trabajo. Por último, marque la casilla Gráficas de ajuste de línea y luego seleccione Aceptar. El resultado es el siguiente (tenga en cuenta que hicimos algunos cambios menores de formato para permitir una mejor presentación de los datos).
Paso 4. Analizar la salida.
Aquí, discutimos los ítems clave mostrados en el resultado de regresión proporcionado en el paso 3.
- Ecuación de Costo: La salida muestra que los costos fijos estimados (mostrados como el coeficiente de Intercepción en la celda B17) suman $43,276, y el costo variable estimado por unidad (mostrado como el coeficiente Unidades Producidas en B18) es de $53.42. Así la ecuación de costos es: $$\ text {Y} =\ $43,276 +\ $53.42\ text {X} $$o $$\ text {Costos totales de producción} =\ $43,276 + (\ $53.42\ times\ text {Unidades Producidas}) $$
- Line Fit Plot y R-Squared: La gráfica muestra que los costos de producción totales reales están muy cerca de los costos de producción totales previstos calculados usando la ecuación de costos. Por lo tanto, la ecuación de costos creada a partir del análisis de regresión probablemente sea útil para predecir los costos totales de producción. Otra forma de evaluar la precisión del resultado de regresión es revisar el estadístico R-cuadrado que se muestra en la celda B5. R-cuadrado 13 mide el porcentaje de la varianza en la variable dependiente (costos totales de producción, en este ejemplo) explicada por la variable independiente (unidades producidas, en este ejemplo). Según la producción, 96.29 por ciento de la varianza en los costos totales de producción se explica por el nivel de unidades producidas, evidencia adicional de que los resultados de regresión serán útiles para predecir los costos totales de producción.
La discusión del análisis de regresión en este capítulo pretende servir de introducción al tema. Para mejorar aún más sus conocimientos sobre el análisis de regresión y proporcionar un análisis más exhaustivo de los datos, debe seguir el tema en un curso introductorio de estadística.
Llave para llevar
Las aplicaciones de software, como Excel, pueden usar análisis de regresión para estimar costos fijos y variables.
- Una vez instalado el paquete de análisis de datos, los datos históricos se ingresan en la hoja de cálculo y se ejecuta el análisis de regresión.
- Los datos resultantes se utilizan para determinar la ecuación de costos, que incluye costos fijos y variables estimados.
La gráfica de ajuste de línea y el estadístico R-cuadrado se utilizan para evaluar la utilidad de la ecuación de costos en la estimación de costos.
REVISAR PROBLEMA
Consulte los datos de costos de producción mensuales para Alta Production, Inc., en la Nota 5.21 “Revisar Problema 5.5". Utilice los cuatro pasos del análisis de regresión descritos en este apéndice para estimar los costos fijos totales y el costo variable por unidad. Indique sus resultados en forma de ecuación\(\text{Y} = \mathcal{f} + \mathcal{v} \text{X}\).
- Contestar
-
El análisis de regresión realizado con Excel da como resultado el siguiente resultado:
Coeficientes interceptar en y 703 x variable 1,442.97 Así, la ecuación del costo total es:
\[\text{Y} = \$ 703 + \$ 1,442.97 \text{X}\]
Definición
- Mide el porcentaje de varianza en la variable dependiente explicada por la variable independiente.