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

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

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

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

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.

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

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 файла, текстового файла и т. д.