El análisis de equilibrio es una herramienta para evaluar las oportunidades de ganancias de un modelo de negocio y varias estrategias de precios. Puede encontrar costos fijos, costos variables y opciones de precios en Excel para determinar el punto de equilibrio de un producto. El punto de equilibrio es la cantidad de unidades que deben venderse a un precio fijo para alcanzar el punto de equilibrio (cubrir los costos de producción).
Paso
Parte 1 de 5: Creación de una tabla de costos variables
Paso 1. Abra Excel y cree un libro de trabajo
Necesitará algunas hojas de trabajo en el libro de trabajo para realizar un seguimiento de todos sus costos.
Paso 2. Haga clic en el botón "+" junto a "Hoja1" en la parte inferior de la pantalla
Esto abrirá un nuevo documento de trabajo.
Paso 3. Nombra la nueva hoja de trabajo "Costo variable"
Esta hoja de trabajo contendrá todas las tablas que monitorean todos los costos variables, como costos de envío, comisiones, etc.
Paso 4. Cree una etiqueta de título para la nueva hoja de trabajo
Para crear una tabla de costo variable estándar, ingrese "Descripción" en la celda A1 y "Cantidad" en la celda B1.
Paso 5. Ingrese el nombre de su costo variable comercial en la columna A
Bajo el encabezado “Descripción”, ingrese el tipo de costos variables del producto que se manejarán.
Paso 6. Deje la columna B ("Cantidad") en blanco por ahora
Esta columna luego se llenará con los costos que realmente ocurrieron durante el proceso.
Paso 7. Cree una tabla a partir de los datos ingresados
Convierta los datos en tablas para que pueda ingresarlos fácilmente en fórmulas:
- Seleccione todos los datos, incluida la fila del título y las celdas en blanco, haciendo clic y arrastrando el cursor del mouse por todas las celdas.
- Haga clic en el botón "Formatear como tabla". Lo encontrará debajo de la etiqueta Inicio. Si está utilizando Excel para Mac, haga clic en la etiqueta Tablas, haga clic en el botón "Nuevo" y seleccione "Insertar tabla con encabezados".
- Marque la casilla "Mi tabla tiene encabezados". Por lo tanto, la etiqueta de la primera fila se establecerá como etiqueta de título.
- Haga clic en el cuadro "Nombre de la tabla" en la esquina superior derecha y cambie el nombre a "Costo variable".
Parte 2 de 5: Creación de una tabla de costos fijos
Paso 1. Haga clic en el botón "+" junto a "Costo variable" en la parte inferior de la pantalla
Esto abrirá un nuevo documento de trabajo.
Paso 2. Cambie el nombre de la nueva hoja de trabajo a "Costos fijos"
Esta hoja de trabajo contendrá todos los costos fijos del producto, como el alquiler, el seguro y otros costos que no cambian.
Paso 3. Cree una etiqueta de título
Al igual que con la hoja de cálculo Costo variable, cree una etiqueta "Descripción" en la celda A1 y una etiqueta "Cantidad" en la celda B1.
Paso 4. Ingrese los nombres de los costos fijos de su negocio en la columna A
Complete la primera columna con una descripción de los costos fijos, por ejemplo, “Alquiler”.
Paso 5. Deje la columna B ("Cantidad") en blanco por ahora
Completará estos costos después de completar toda la documentación.
Paso 6. Cree una tabla a partir de los datos ingresados
Seleccione todo lo que ha creado en esta hoja de trabajo, incluido el título:
- Haga clic en el botón "Formatear como tabla" en la etiqueta Inicio.
- Marque "Mi tabla tiene encabezados" para cambiar la fila 1 al título de la tabla.
- Haga clic en el cuadro "Nombre de la tabla" e ingrese el nombre de la tabla "Costos fijos".
Parte 3 de 5: Creación de una hoja de trabajo de equilibrio
Paso 1. Cambie el nombre de Sheet1 a "BEP" y selecciónelo
Esta hoja de trabajo contendrá su cuadro principal de BEP (punto de equilibrio). No es necesario que cambie el nombre a "BEP", pero facilitará su trabajo si lo hace.
Paso 2. Establezca el diseño de la hoja de trabajo de equilibrio
Por el bien de este ejemplo, cree una hoja de trabajo con el siguiente diseño:
- A1: Ventas: esta es la etiqueta de la sección Ventas de la hoja de trabajo.
- B2: Precio por unidad: este es el precio que se cobra por cada artículo vendido.
- B3: Unidades vendidas: este es el número de unidades vendidas a un precio fijo en un período de tiempo determinado.
- A4: Costos: esta es la etiqueta de la sección Costos de la hoja de trabajo.
- B5: Costos variables: estos son los costos del producto que puede controlar (envío, tasa de comisión, etc.)
- B6: Costos fijos: estos son los costos del producto sobre los que no tiene control (alquiler de instalaciones, seguros, etc.)
- A7: Ingresos: es la cantidad de dinero generada por la venta del producto antes de deducir los costos.
- B8: Margen unitario: es la cantidad de dinero que se gana por unidad después de deducir los costos.
- B9: Margen de utilidad bruta: esta es la cantidad total de dinero generado por la venta de unidades de producto después de deducir los costos.
- A10: BEP: esta es la etiqueta de la sección de punto de equilibrio de la hoja de trabajo.
- B11: Unidades: este es el número de unidades que deben venderse para cubrir los costos de producción.
Paso 3. Cambie el formato numérico de las celdas de entrada y salida
Debe cambiar el formato numérico de celdas específicas para que los datos aparezcan correctamente:
- Resalte C2, C5, C6, C8 y C9. Haga clic en el menú desplegable en la sección "Número" de la etiqueta de inicio y seleccione "Moneda".
- Resalte C3 y C11. Haga clic en el menú desplegable y seleccione "Más formatos de números". Seleccione "Número" y establezca "Lugares decimales" en "0".
Paso 4. Cree un rango para usar en fórmulas
Seleccione y cree los siguientes rangos para que funcione la fórmula. De esta manera, se pueden crear variables para ingresarlas en fórmulas para que pueda rastrear y actualizar fácilmente sus valores.
- Seleccione B2: C3 y luego haga clic en la etiqueta "Fórmulas". Haga clic en "Crear a partir de la selección" y continúe con "Aceptar".
- Seleccione B5: C6 y luego haga clic en la etiqueta "Fórmulas". Haga clic en "Crear a partir de la selección" y continúe con "Aceptar".
- Seleccione B8: C9 y luego haga clic en la etiqueta "Fórmulas". Haga clic en "Crear a partir de la selección" y continúe con "Aceptar".
- Seleccione B11: C11 y luego haga clic en la etiqueta "Fórmulas". Haga clic en "Crear a partir de la selección" y continúe con "Aceptar".
Parte 4 de 5: Introducción de fórmulas
Paso 1. Ingrese la fórmula de costo variable
Esta fórmula calculará el costo variable total de los bienes vendidos. Haga clic en C5 e ingrese la siguiente fórmula:
= SUMA (costo variable) * Unidades_Vendidas
Paso 2. Inserte los costos fijos en la fórmula
Excel calculará el costo fijo total de su producto. Haga clic en C6 e ingrese la siguiente fórmula:
= SUM (costo fijo)
Paso 3. Ingrese la fórmula del margen unitario
Esta fórmula calculará el margen resultante después de incluir los costos variables. Haga clic en C8 e ingrese la siguiente fórmula:
= Price_Per_Unit-SUM (Variable de costo)
Paso 4. Ingrese la fórmula del margen de beneficio bruto
Esta fórmula determina el monto total de la ganancia para todas las unidades después de deducir los costos variables. Haga clic en C9 e ingrese la siguiente fórmula:
= Margin_Unit * Unit_Sold
Paso 5. Ingrese la fórmula BEP
Esta fórmula toma los costos fijos y los compara con el margen para que sepa cuántas unidades necesita vender para alcanzar el punto de equilibrio. Haga clic en C11 e ingrese la siguiente fórmula:
= SIERROR (Costo_Fijo / Margen_Unitario, 0)
Parte 5 de 5: Determinación del punto de equilibrio
Paso 1. Ingrese los costos variables de su empresa
Regrese a la tabla Costos variables y complete todos los costos asociados con el producto. Cuanto más precisos sean los costes registrados, más precisos serán los cálculos.
Cada costo en la tabla de costo variable debe ser por unidad vendida
Paso 2. Ingrese los costos fijos de su empresa
Ingrese estos costos en la tabla de costos fijos. Estos son los costos de funcionamiento de una empresa, todos establecidos en intervalos iguales (por ejemplo, tarifas mensuales).
Paso 3. Ingrese el precio por unidad
En la hoja de trabajo de BEP, ingrese el precio estimado por unidad. Podrá ajustarlo a medida que avanza el cálculo.
Paso 4. Ingrese la cantidad de unidades que desea vender
Este es el número deseado de ventas en el mismo período de tiempo que los costos fijos. Por ejemplo, si sus costos fijos incluyen el alquiler y el seguro mensuales, Unidades vendidas es la cantidad de unidades vendidas durante el mismo período de tiempo.
Paso 5. Lea la salida "Unidad"
La celda de salida de Unidades (C11) mostrará el número de unidades que deberán venderse para alcanzar el punto de equilibrio. Esta cifra cambiará según el precio por unidad, así como las tablas de costo variable y costo fijo.
Paso 6. Realice ajustes a precios y costos
Cambiar el precio por unidad también cambiará la cantidad de unidades necesarias para alcanzar el punto de equilibrio. Intente cambiar el precio de venta de la unidad y vea el impacto en el valor de BEP.
