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

Supongamos que vas a vender un nuevo producto y conoces el coste variable por unidad y el coste fijo total. Ahora quieres prever los posibles volúmenes de ventas y fijar el precio del producto en función de ellos.

11. Crea una tabla sencilla y rellena los elementos con los datos proporcionados. Consulta la siguiente captura de pantalla:

22. Introduce las fórmulas adecuadas para calcular los ingresos, el coste variable y el beneficio. Consulta la captura de pantalla anterior:
Ingresos = Precio unitario x Unidades vendidas
Costes variables = Coste por unidad x Unidades vendidas
Beneficio = Ingresos – Coste variable – Costes fijos

33. Haz clic en la pestaña Datos > Análisis Y si > Buscar objetivo.

44. En el cuadro de diálogo Buscar objetivo que se abre, realiza lo siguiente (consulta la captura de pantalla anterior):
(1) Especifica la opción Definir celda como la celda de Beneficio, en nuestro caso es la Celda B7;
(2) Especifica la opción A valor como 0;
(3) Especifica la opción Cambiando la celda como la celda de Precio unitario, en nuestro caso es la Celda B1.
(4) Haz clic en Aceptar para continuar.

55. A continuación, aparecerá el cuadro de diálogo Estado de Buscar objetivo. Por favor, haz clic en el botón Aceptar para aplicar los cambios.

Ahora el Precio unitario cambia de40 a31,579 y el beneficio neto pasa a ser0. Por lo tanto, si prevés un volumen de ventas de50, el Precio unitario no puede ser inferior a31,579; de lo contrario, se producirán pérdidas.

Descargar plantilla de punto de equilibrio avanzada

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

Además de la función Buscar objetivo, también podemos utilizar una fórmula para realizar fácilmente el análisis de punto de equilibrio en Excel.

1. Crea una tabla sencilla y rellena los elementos con los datos proporcionados. En este método, suponemos que el beneficio es0 y que ya hemos previsto las unidades vendidas, el coste por unidad y los costes fijos. Consulta la siguiente captura de pantalla:

22. En la tabla, escribe la fórmula =B6/B2+B4 en la Celda B1 para calcular el Precio unitario, escribe la fórmula =B1*B2 en la Celda B3 para calcular los ingresos, y escribe la fórmula =B2*B4 en la Celda B5 para los costes variables. Consulta la siguiente captura de pantalla:

A partir de aquí, cuando modifiques cualquiera de los valores previstos de unidades vendidas, coste por unidad o costes fijos, el valor del Precio unitario se actualizará automáticamente. Consulta la captura de pantalla anterior:

Consejo: Guarda un rango como entrada de AutoTexto (manteniendo los formatos de celda y fórmulas) para reutilizarlo en el futuro
Puede resultar muy tedioso referenciar celdas y aplicar varias fórmulas cada vez que realices el análisis de punto de equilibrio. Kutools para Excel ofrece una solución práctica con la función AutoTexto que te permite guardar el rango como una entrada de AutoTexto, manteniendo los formatos de celda y las fórmulas del rango. Así, podrás reutilizar este rango con un solo clic en cualquier libro de trabajo.

¡Obtenga una prueba gratuita completa de 30 días ahora!

Si ya tienes registrados los datos de ventas, también puedes realizar el análisis de punto de equilibrio mediante un gráfico en Excel. Este método te guiará para crear fácilmente un gráfico de punto de equilibrio.

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

22. Completa la tabla como se muestra a continuación:
(1) En la Celda E2, escribe la fórmula =D2*$B$1y arrastra el controlador de Autorrelleno hacia abajo hasta el Rango E2:E13;
(2) En la Celda F2, escribe la fórmula =D2*$B$1+$B$3y arrastra el controlador de Autorrelleno hacia abajo hasta el Rango F2:F13;
(3) En la Celda G2, escribe la fórmula =E2-F2y arrastra el controlador de Autorrelleno hacia abajo hasta el Rango G2:G13.

Hasta aquí, hemos preparado los datos de origen para el gráfico de punto de equilibrio que crearemos a continuación. Consulta la siguiente captura de pantalla:

33. En la tabla, selecciona simultáneamente las columnas de Ingresos, Costes y Beneficio, y luego haz clic en Insertar > Insertar gráfico de líneas o de áreas > LíneaConsulta la captura de pantalla:

44. Ahora se ha creado 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 Seleccionar origen de datos, realiza lo siguiente:
(1) En la sección Rango de nombres de la serie selecciona una de las series según lo necesites. En mi ejemplo, selecciono la serie Ingresos ;
(2) Haz clic en el botón Editar en la sección Etiquetas del eje horizontal (categoría) ;
(3) En el cuadro de diálogo de Etiquetas de eje que aparece, especifica la columna de Precio unitario (excepto el nombre de la columna) como rango de etiquetas de eje;
(4) Haz clic en Aceptar > Aceptar para guardar los cambios.

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

De manera similar, también puedes crear un gráfico de punto de equilibrio para analizar el punto de equilibrio en función de las unidades vendidas, como se muestra en la siguiente captura de pantalla:

Exporta/guarda fácilmente una selección como archivos PDF/CSV/TEXT/HTML independientes en Excel

Por ejemplo, cuando termines tu análisis de punto de equilibrio en Excel, es posible que quieras presentar este análisis. En este caso, puedes utilizar la función Exportar rango a archivo de Kutools para Excel para exportar rápidamente la selección como un archivo PDF independiente, archivo HTML, archivo CSV, archivo de texto, etc.