¿Cómo realizar un análisis del punto de equilibrio en Excel?

Imagine que va a lanzar un nuevo producto y ya conoce el coste variable por unidad y el coste fijo total. Ahora quiere prever los posibles volúmenes de ventas y establecer el precio del producto en función de ellos.

1. Cree una tabla sencilla y rellénela con los datos proporcionados. Vea la siguiente captura de pantalla:

2. Introduzca las fórmulas adecuadas para calcular los ingresos, los costes variables y el beneficio. Consulte la captura de pantalla anterior:
Ingresos = Precio unitario × Unidades vendidas
Costes variables = Coste por unidad × Unidades vendidas
Beneficio = Ingresos – Costes variables – Costes fijos

3. Haga clic en Datos > Análisis de hipótesis > Buscar objetivo.

4. En el cuadro de diálogo Buscar objetivo que se abre, proceda como sigue (vea la captura de pantalla anterior):
(1) Especifique la Celda objetivo como la celda de Beneficio; en nuestro caso, es la celda B7;
(2) Especifique el Valor para la celda como 0;
(3) Especifique la Celda variable como la celda del Precio unitario; en nuestro caso, es la celda B1.
(4) Haga clic en el botón Aceptar.

5. A continuación, aparecerá el cuadro de diálogo Estado de Buscar objetivo. Haga clic en el botón Aceptar para aplicarlo.

Ahora cambia el Precio unitario de 40 a 31.579, y el beneficio neto pasa a ser 0. Por lo tanto, si prevé un volumen de ventas de 50, el precio unitario no puede ser inferior a 31.579, ya que de lo contrario se produciría una pérdida.

Descargue una plantilla compleja de punto de equilibrio

http://office.microsoft.com/en-us/templates/breakeven-analysis-TC001116512.aspx
http://office.microsoft.com/en-us/templates/break-even-analysis-TC001017515.aspx

En comparación con la función Buscar objetivo, también podemos aplicar una fórmula para realizar fácilmente el análisis del punto de equilibrio en Excel.

1. Cree una tabla sencilla y rellene los elementos con los datos proporcionados. En este método, partimos del supuesto de que el beneficio es 0, y ya hemos previsto las unidades vendidas, el coste por unidad y los costes 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, introduzca la fórmula =B1*B2 en la celda B3 para calcular los ingresos e introduzca la fórmula =B2*B4 en la celda B5 para los costes variables. Vea la siguiente captura de pantalla:

A continuación, al modificar cualquiera de los valores previstos (unidades vendidas, coste por unidad o costes fijos), el valor del precio unitario cambiará automáticamente. Vea la captura de pantalla anterior:

Consejo : Guarde un rango como entrada de texto automático (conservando el formato de celda y las fórmulas) para reutilizarlo en el futuro
Le resultará muy tedioso tener que hacer referencia a celdas y aplicar múltiples fórmulas para el análisis del punto de equilibrio cada vez. Kutools para Excel ofrece una solución sencilla mediante la utilidad de Texto automático para guardar el rango como una entrada de texto automático, que conserva el formato de celda y las fórmulas del rango. Así podrá reutilizar este rango con un solo clic en cualquier libro.

¡Obtenga ahora una prueba gratuita de 30 días con todas las funciones!

Si ya ha registrado los datos de ventas, también puede realizar el análisis del punto de equilibrio con un gráfico en Excel. Este método le guiará para crear fácilmente un gráfico de punto de equilibrio.

1. Prepare una tabla de ventas como la que se muestra en la siguiente captura de pantalla. En nuestro caso, suponemos que las unidades vendidas, el coste por unidad y los costes fijos son fijos, y necesitamos realizar el análisis del punto de equilibrio en función del precio unitario.

2. Complete la tabla tal como se muestra a continuación:
(1) En la celda E2, introduzca la fórmula =D2*$B$1 y arrastre su controlador de autorrelleno hasta el rango E2:E13;
(2) En la celda F2, introduzca la fórmula =D2*$B$1+$B$3 y arrastre su controlador de autorrelleno hasta el rango F2:F13;
(3) En la celda G2, introduzca la fórmula =E2-F2 y arrastre su controlador de autorrelleno hasta el rango G2:G13.

¡Ya tenemos listos los datos de origen para el gráfico de punto de equilibrio que crearemos a continuación! Consulte la siguiente captura de pantalla:

3. En la tabla, seleccione simultáneamente las columnas de Ingresos, Costes y Beneficio y, a continuación, haga clic en Insertar > Insertar gráfico de líneas o de áreas > Líneas. Vea la captura de pantalla:

4. Ahora se crea un gráfico de líneas. Haz clic con el botón derecho en el gráfico y selecciona Seleccionar datos en el menú contextual. Consulta la siguiente captura de pantalla:

5. En el cuadro de diálogo Origen de Seleccionar Datos, realice lo siguiente:
(1) En la sección Rango de nombres de la serie, seleccione una de las series según sus necesidades. En mi ejemplo, selecciono la serie de Ingresos;
(2) Haga clic en el botón Editar en la sección Etiquetas del eje horizontal (categoría);
(3) En el cuadro de diálogo emergente Rango de etiquetas del eje, especifique la columna de Precio unitario (excluyendo el nombre de la columna) como Rango de Etiquetas de Ejes;
(4) Haga clic en Aceptar > Aceptar para guardar los cambios.

Ahora, en el gráfico de punto de equilibrio, verá que el punto de equilibrio se produce cuando el precio es igual a 36, tal como se muestra en la siguiente captura de pantalla:

De forma similar, también puede crear un gráfico de punto de equilibrio para analizar el punto de equilibrio según las unidades vendidas, tal como se muestra en la siguiente captura de pantalla:

Exporte/guarde fácilmente la selección como PDF/CSV/TXT/Archivo HTML independiente en Excel

Por ejemplo, cuando finalice su análisis del punto de equilibrio en Excel, es posible que desee incluirlo en una presentación. En ese caso, puede utilizar la utilidad Exportar Rango a Archivo de Kutools para Excel para exportar rápidamente la selección como un archivo independiente en formato PDF, HTML, CSV, archivo de texto, etc.