¿Cómo hacer un análisis de equilibrio en Excel?
El análisis de equilibrio puede ayudarlo a determinar cuándo la ganancia neta es cero, lo que significa que los ingresos totales son iguales a los gastos totales. Es muy útil fijar el precio de un producto nuevo cuando puede pronosticar sus costos y ventas.
- Realice un análisis de equilibrio con la función de búsqueda de objetivos
- Haga un análisis de equilibrio con la fórmula
- Haga un análisis de equilibrio con el gráfico
Realice un análisis de equilibrio con la función de búsqueda de objetivos
Supongamos que va a vender un producto nuevo y conoce el costo variable de por unidad y el costo fijo total. Ahora va a pronosticar los posibles volúmenes de ventas y fijar el precio del producto en función de ellos.
1. Haga una tabla fácil y complete los elementos con los datos dados en la tabla. Vea la siguiente captura de pantalla:
2. Ingrese fórmulas adecuadas para calcular ingresos, costos variables y ganancias. Vea la captura de pantalla anterior:
Ingresos = Precio unitario x Unidad vendida
Costos variables = Costo por unidad x unidad vendida
Profit = Ingresos - Costo variable - Costos fijos
3. Haga clic en Datos > Y si el análisis > Buscar objetivo.
4. En el cuadro de diálogo de apertura de búsqueda de objetivos, haga lo siguiente (vea la captura de pantalla anterior):
(1) Especifique el Establecer celda como celda de Beneficio, en nuestro caso es la celda B7;
(2) Especifique el Valorar as 0;
(3) Especifique el Cambiando de celda como la celda Precio unitario, en nuestro caso es la celda B1.
(4) Click OK del botón.
5. Y luego aparece el cuadro de diálogo Estado de búsqueda de objetivos. Haga clic en el OK botón para aplicarlo.
Ahora cambia el precio unitario de 40 a 31.579 y la ganancia neta cambia a 0. Por lo tanto, si pronostica que el volumen de ventas es 50 y el precio unitario no puede ser inferior a 31.579, de lo contrario se producirán pérdidas.
Descargue una plantilla de equilibrio compleja
http://office.microsoft.com/en-us/templates/breakeven-analysis-TC001116512.aspx
http://office.microsoft.com/en-us/templates/break-even-analysis-TC001017515.aspx
Demostración: realice un análisis de equilibrio con la función Buscar objetivos en Excel
Combine fácilmente varias hojas de trabajo / libros de trabajo / archivos CSV en una hoja de trabajo / libro de trabajo
Puede resultar tedioso combinar docenas de hojas de diferentes libros de trabajo en una sola hoja. Pero con Kutools para Excel Combinar (hojas de trabajo y libros de trabajo) utilidad, puede hacerlo con solo varios clics.
Haga un análisis de equilibrio con la fórmula
En comparación con la función Buscar objetivo, también podemos aplicar la fórmula para realizar el análisis de equilibrio fácilmente en Excel.
1. Haga una tabla fácil y complete los elementos con los datos dados en la tabla. En este método, suponemos que la ganancia es 0 y ya hemos pronosticado la unidad vendida, el costo por unidad y los costos fijos. Vea la siguiente captura de pantalla:
2. En la tabla, escriba la fórmula = B6 / B2 + B4 en la celda B1 para calcular el precio unitario, escriba la fórmula = B1 * B2 en la celda B3 para calcular los ingresos y escriba la fórmula = B2 * B4 en la celda B5 para costos variables. Vea la siguiente captura de pantalla:
Y luego, cuando cambie y un valor de la unidad vendida proyectada, el costo por unidad o los costos fijos, el valor del precio unitario cambiará automáticamente. Vea la captura de pantalla anterior:
Consejo: Guarde un rango como entrada de Autotexto (formatos de celda y fórmulas restantes) para reutilizar en el futuro
Debe ser muy tedioso referir celdas y aplicar múltiples fórmulas para el análisis de equilibrio cada vez. Kutools para Excel proporciona una linda solución de Texto automático utilidad para guardar el rango como una entrada de Autotexto, que puede seguir siendo los formatos de celda y fórmulas en el rango. Y luego puede reutilizar este rango con solo un clic en cualquier libro de trabajo.
Haga un análisis de equilibrio con el gráfico
Si ya ha registrado los datos de ventas, también puede realizar el análisis de equilibrio con el gráfico en Excel. Este método lo guiará para crear fácilmente un gráfico de equilibrio.
1. Prepare una tabla de ventas como se muestra a continuación. En nuestro caso, asumimos que las unidades vendidas, el costo por unidad y los costos fijos son fijos, y necesitamos hacer el análisis de equilibrio por precio unitario.
2. Termine la tabla como se muestra a continuación:
(1) En la celda E2, escriba la fórmula = D2 * $ B $ 1y arrastre su controlador de Autocompletar hasta RangeE2: E13;
(2) En la celda F2, escriba la fórmula = D2 * $ B $ 1 + $ B $ 3y arrastre su controlador de Autocompletar hacia abajo hasta el rango F2: F13;
(3) En la celda G2, escriba la fórmula = E2-F2y arrastre su controlador de Autocompletar hacia abajo hasta el rango G2: G13.
Hasta ahora, hemos terminado los datos de origen del gráfico de equilibrio que crearemos más adelante. Vea la siguiente captura de pantalla:
3. En la tabla, seleccione la columna Ingresos, la columna Costos y la columna Beneficio simultáneamente y luego haga clic en recuadro > Insertar gráfico de líneas o áreas > línea. Ver captura de pantalla:
4. Ahora se crea un gráfico de líneas. Haga clic con el botón derecho en el gráfico y seleccione Seleccionar datos en el menú contextual. Vea la siguiente captura de pantalla:
5. En el cuadro de diálogo Seleccionar fuente de datos, por favor:
(1) En el Entradas de leyenda (serie) sección, seleccione una de las series que necesite. En mi ejemplo, selecciono el Ingresos serie;
(2) Haga clic en el Editar botón en el Etiquetas horizontales (de categoría) del eje sección;
(3) En el cuadro de diálogo emergente Etiquetas de eje, especifique la columna Precio unitario (excepto el nombre de la columna) como rango de etiquetas de eje;
(4) Click OK > OK Para guardar los cambios.
Ahora, en el gráfico de equilibrio, verá que el punto de equilibrio se produce cuando el precio es igual a 36, como se muestra a continuación:
De manera similar, también puede crear un gráfico de equilibrio para analizar el punto de equilibrio por unidades vendidas como se muestra a continuación:
Demostración: haga un análisis de equilibrio con un gráfico en Excel
Exporte / guarde fácilmente la selección como archivos PDF / CSV / TEXT / HTML separados en Excel
Por ejemplo, cuando termine su análisis de equilibrio en Excel, es posible que desee hacer su presentación con este análisis de equilibrio. En esta condición, puede aplicar Kutools para Excel Exportar rango a archivo utilidad para exportar rápidamente la selección como un archivo PDF, archivo HTML, archivo CSV, archivo de texto, etc.
Las mejores herramientas de productividad de oficina
Mejore sus habilidades de Excel con Kutools for Excel y experimente la eficiencia como nunca antes. Kutools for Excel ofrece más de 300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haga clic aquí para obtener la función que más necesita...
Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Habilite la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
- ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!