Как сделать анализ безубыточности в Excel?
Анализ безубыточности может помочь вам получить точку, когда чистая прибыль равна нулю, что означает, что общие доходы равны общим расходам. Очень полезно устанавливать цену на новый продукт, когда вы можете прогнозировать свои затраты и продажи.
- Проведите анализ безубыточности с функцией поиска цели
- Анализ безубыточности с помощью формулы
- Анализ безубыточности с помощью графика
Проведите анализ безубыточности с функцией поиска цели
Предположим, вы собираетесь продать новый продукт и знаете переменную стоимость единицы и общую постоянную стоимость. Теперь вы собираетесь спрогнозировать возможные объемы продаж и на их основе оценить продукт.
1. Составьте простую таблицу и заполните элементы данными в таблице. См. Снимок экрана ниже:
2. Введите правильные формулы для расчета дохода, переменных затрат и прибыли. Смотрите скриншот выше:
Выручка = Цена за единицу x Проданная единица
Различные цены = Стоимость единицы x Проданная единица
Прибыль = Доход - Переменная стоимость - Фиксированные затраты
3, Нажмите Данные > Что-Анализ > Поиск цели.
4. В открывшемся диалоговом окне Goal Seek сделайте следующее (см. Снимок экрана выше):
(1) Укажите Установить ячейку в качестве ячейки прибыли в нашем случае это ячейка B7;
(2) Укажите Ценить, оценивать as 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
Легко объединяйте несколько листов / книг / файлов CSV в один рабочий лист / книгу
Объединение десятков листов из разных книг в один может оказаться утомительным занятием. Но с Kutools for Excel's Объединить (рабочие листы и рабочие тетради) утилиту, вы можете сделать это всего за несколько кликов!
Анализ безубыточности с помощью формулы
По сравнению с функцией поиска цели, мы также можем применить формулу, чтобы легко провести анализ безубыточности в Excel.
1. Составьте простую таблицу и заполните элементы данными в таблице. В этом методе мы предполагаем, что прибыль равна 0, и мы уже спрогнозировали проданную единицу, стоимость единицы и постоянные затраты. См. Снимок экрана ниже:
2. В таблице введите формулу = B6 / B2 + B4 в ячейку B1 для расчета цены за единицу введите формулу = B1 * B2 в ячейку B3 для расчета дохода и введите формулу = B2 * B4 в ячейку B5 для переменных затрат. См. Снимок экрана ниже:
А затем, когда вы измените любое значение прогнозируемой проданной единицы, стоимости единицы или постоянных затрат, значение цены единицы изменится автоматически. Смотрите скриншот выше:
Наконечник: Сохраните диапазон как запись автотекста (остальные форматы ячеек и формулы) для повторного использования в будущем
Должно быть очень утомительно ссылаться на ячейки и каждый раз применять несколько формул для анализа безубыточности. Kutools for Excel предоставляет симпатичное обходное решение Авто Текст Утилита для сохранения диапазона в виде записи автотекста, в которой могут оставаться форматы ячеек и формулы в диапазоне. И затем вы можете повторно использовать этот диапазон одним щелчком мыши в любой книге.
Анализ безубыточности с помощью графика
Если вы уже записали данные о продажах, вы также можете выполнить анализ безубыточности с помощью диаграммы в Excel. Этот метод поможет вам легко создать диаграмму безубыточности.
1. Подготовьте таблицу продаж, как показано на скриншоте ниже. В нашем случае мы предполагаем, что проданные единицы, стоимость единицы и постоянные затраты являются фиксированными, и нам необходимо провести анализ безубыточности по цене единицы.
2. Завершите таблицу, как показано ниже:
(1) В ячейке E2 введите формулу = D2 * $ B $ 1, и перетащите его дескриптор автозаполнения в RangeE2: 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, как показано ниже:
Точно так же вы также можете создать диаграмму безубыточности для анализа точки безубыточности по проданным единицам, как показано ниже:
Демонстрация: анализ безубыточности с помощью диаграммы в Excel
Легко экспортируйте / сохраняйте выбранные объекты в виде отдельных файлов PDF / CSV / TEXT / HTML в Excel
Например, когда вы закончите анализ безубыточности в Excel, вы можете захотеть провести презентацию с этим анализом безубыточности. В этом состоянии вы можете применить Kutools for Excel's Диапазон экспорта в файл утилита для быстрого экспорта выделенного фрагмента в отдельный файл PDF, файл HTML, файл CSV, текстовый файл и т. д.
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!