Как провести анализ безубыточности в Excel?

Допустим, вы планируете запустить продажи нового продукта и уже знаете переменные затраты на единицу, а также общие постоянные затраты. Теперь осталось спрогнозировать возможные объёмы продаж и установить цену на их основе.

1. Создайте простую таблицу и заполните её имеющимися данными. См. скриншот ниже:

2. Введите соответствующие формулы для расчёта выручки, переменных затрат и прибыли. См. скриншот выше:
Выручка = Цена за единицу × Продано единиц
Переменные затраты = Затраты на единицу × Продано единиц
Прибыль = Выручка – Переменные затраты – Постоянные затраты

3. Нажмите Данные > Анализ «что если» > Подбор параметра.

4. В открывшемся диалоговом окне «Подбор параметра» выполните следующие действия (см. скриншот выше):
(1) В поле Установить в ячейке укажите ячейку Прибыли; в нашем случае это ячейка B7;
(2) В поле Значение укажите 0;
(3) В поле Изменяя значение ячейки укажите ячейку Цена за единицу; в нашем случае это ячейка B1.
(4) Нажмите кнопку OK.

5. После этого откроется диалоговое окно «Результат подбора параметра». Нажмите кнопку OK, чтобы применить изменения.

Теперь цена за единицу снизилась с 40 до 31,579, и чистая прибыль составила 0. Следовательно, если вы прогнозируете объём продаж на уровне 50 единиц, цена за единицу не должна опускаться ниже 31,579 — в противном случае возникнет убыток.

Скачать сложный шаблон анализа безубыточности

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

По сравнению с функцией «Подбор параметра», для анализа безубыточности в Excel можно с такой же лёгкостью использовать формулу.

1. Создайте простую таблицу и заполните её имеющимися данными. В этом методе мы исходим из того, что прибыль равна нулю, а объём продаж, затраты на единицу продукции и постоянные затраты уже спрогнозированы. См. скриншот ниже:

2. Введите в таблицу следующие формулы: =B6/B2+B4 — в ячейку B1 для расчёта цены за единицу, =B1*B2 — в ячейку B3 для расчёта выручки и =B2*B4 — в ячейку B5 для расчёта переменных затрат. См. скриншот ниже:

После этого при изменении любого из прогнозируемых значений — объёма продаж, затрат на единицу или постоянных затрат — цена за единицу будет автоматически обновляться. См. скриншот выше:

Совет : Сохраните диапазон как элемент автотекста (включая формат ячеек и формулы) для быстрого повторного использования в будущем
Каждый раз вручную ссылаться на ячейки и применять несколько формул для анализа безубыточности — утомительно и неэффективно. Kutools для Excel предлагает удобное решение с помощью Автотекста: сохраните нужный диапазон как элемент автотекста, и он сохранит всё — от формата ячеек до используемых формул. После этого вы сможете вставить этот диапазон в любую книгу всего одним щелчком!

Получите бесплатную пробную версию с полным функционалом на 30 дней прямо сейчас!

Если у вас уже есть данные о продажах, вы также можете выполнить анализ безубыточности с помощью диаграммы в Excel. Данный метод поможет вам легко создать диаграмму безубыточности.

1. Подготовьте таблицу продаж, как показано на скриншоте ниже. В нашем случае объём продаж, затраты на единицу и постоянные затраты считаются фиксированными, а анализ безубыточности необходимо выполнить по цене за единицу.

2. Завершите заполнение таблицы, как показано ниже:
(1) В ячейку E2 введите формулу =D2*$B$1 и протяните маркер автозаполнения до диапазона E2:E13;
(2) В ячейку F2 введите формулу =D2*$B$1+$B$3 и протяните маркер автозаполнения до диапазона F2:F13;
(3) В ячейку G2 введите формулу =E2-F2 и протяните маркер автозаполнения до диапазона G2:G13.

На этом подготовка данных для будущей диаграммы безубыточности завершена. См. скриншот ниже:

3. В таблице одновременно выделите столбцы «Выручка», «Затраты» и «Прибыль», затем нажмите Вставка > Диаграммы «График» или «Область» > График. См. скриншот:

4. График готов! Щёлкните по нему правой кнопкой мыши и в контекстном меню выберите «Выбрать данные». См. скриншот ниже:

5. В диалоговом окне «Выбрать данные источника» выполните следующие действия:
(1) В разделе Диапазон имени ряда выберите нужный ряд данных. В моём примере я выбираю ряд Выручка;
(2) Нажмите кнопку Изменить в разделе Горизонтальные метки оси;
(3) В появившемся диалоговом окне «Диапазон меток оси» укажите столбец «Цена за единицу» (без заголовка столбца) как диапазон меток оси;
(4) Нажмите OK > OK, чтобы сохранить изменения.

Теперь на диаграмме безубыточности вы увидите точку безубыточности, которая достигается при цене, равной 36, как показано на скриншоте ниже:

Аналогично вы можете создать диаграмму безубыточности для анализа точки безубыточности по объёму продаж, как показано на скриншоте ниже:

Легко экспортируйте/сохраняйте выделенный фрагмент как отдельный PDF/CSV/TEXT/HTML файл в Excel

Например, после завершения анализа безубыточности в Excel вы, возможно, захотите использовать его в презентации. В таком случае воспользуйтесь утилитой Экспорт диапазона в файл из набора Kutools для Excel, чтобы быстро сохранить выделенный фрагмент как отдельный файл в формате PDF, HTML, CSV, текстового файла и других.