Как сделать график точки безубыточности в excel онлайн?

Точка безубыточности в Excel

Разберемся как рассчитывать точку безубыточности в Excel для проекта (продукта, компании), и на примере подробно изучим как построить график безубыточности.

Приветствую всех, дорогие читатели блога TutorExcel.Ru.

Перед там как рассчитать формулу точки и построить график безубыточности, вспомним немного о теоретической части вопроса.
Как непосредственно следует из названия, точка безубыточности показывает момент, когда проект начинает работать без убытков, т.е. приносить прибыль. Другими словами, это такой объем производства товара (или услуг) и реализации продукции при котором доходы от реализации товаров (или услуг) равны совокупным издержкам:


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

В целом результаты практически любого коммерческого проекта можно упрощенно представить следующим образом:

  • Доходы. Выручка складывается из объема производства умноженного на цену продажи продукта;
  • Расходы. Издержки разделяются на постоянные и переменные затраты.
    • Постоянные затраты. Расходы не зависящие от объема производства. К данному типу затрат обычно относятся расходы на аренду, на оплату труда персонала и т.д.;
    • Переменные затраты. Расходы на себестоимость, складываются из объема производства помноженного на удельную себестоимость продукта.

Итоговая прибыль проекта выражается как разность доходов и расходов, соответственно, так как постоянные издержки остаются неизменными, то за счет изменения объема производства изменяются доходы и переменные затраты, вследствие чего меняется итоговая прибыль.

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

P * X (доходы) — FC — VC * X (общие расходы) = 0, где

P — цена продажи 1 единицы продукта, X — объем продукции, FC — постоянные затраты, VC — переменные затраты на 1 единицу продукта.

Расчет точки безубыточности в Excel

Точку безубыточности можно рассчитать в 2 вариантах: в денежном эквиваленте (т.е. минимальный объем выручки, при котором прибыль равна нулю) и в натуральном (количественном) эквиваленте (т.е. минимальный объем производства в единицах продукции, при котором достигается нулевая прибыль).

Таким образом, в натуральном эквиваленте формула записывается как:

X = FC / (P — VC),

а если перевести с языка формул на более простые определения, то получается:

Объем продукции = Постоянные затраты / (Цена — Переменные затраты).

Соответственно, для получения денежного эквивалента, нужно полученный объем продукции умножить на цену продукта (P * X).

Идём дальше. Самое время перейти к практике: расчету формулы и построению графика.
Рассмотрим следующий пример: перед нами стоит задача определить минимальный уровень продаж нового продукта компании для выхода на рынок.
В итоге, чтобы рассчитать точку безубыточности в Excel нам понадобятся данные по 3 параметрам — данные по цене и себестоимости продукта, а также уровне постоянных затрат, которые мы запишем в таблицу:


Применяем полученные формулы к данным в таблице и получаем расчет в 2 вариантах:

График точки безубыточности в Excel

Теперь изучим как построить график точки безубыточности, чтобы наглядно увидеть где она находится.
На основе исходных данных рассчитаем итоговые доходы, общие издержки (сумма постоянных и переменных расходов) и итоговую прибыль (разница между доходами и общими издержками) в зависимости от объема производства (установим шаг в 100 единиц продукта).


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

Выделим диапазон данных с доходами и расходами (B9:L11), переходим на вкладку Вставка -> Диаграмма -> Точечная и выбираем точечную диаграмму с прямыми отрезками (также можно построить с помощью стандартного линейного графика):


Пересечение двух данных прямых (доходов и расходов) и будет искомой точкой, где координата по горизонтальной оси графика отвечает за расчет в единицах продукции, по вертикальной оси — в денежном выражении.
Чтобы добавить точку безубыточности на график можно воспользоваться либо макросом поиска точек пересечения нескольких графиков, либо просто поместить ее на диаграмму с помощью добавления нового ряда данных, формулу которой мы уже подсчитывали выше:

Читать еще:  Как сделать сложные формулы в excel?


Можно варьировать любой из параметров (цена и себестоимость продукции, уровень постоянных затрат) — расчет будет меняться исходя из введенных данных:


Поэкспериментировать с расчетами и подробно освоить построение графика безубыточности в Excel можно скачав файл с примером.

Спасибо за внимание!
Если у вас остались вопросы — жду ваших комментариев.

Как сделать график точки безубыточности в excel онлайн?

Send a Message

This message will be pushed to the admin’s iPhone instantly.

Добрый день, сегодня мы рассмотрим такую фундаментальную вещь как построение графика безубыточности (нахождение точки безубыточности). Сначала немного теории, чтобы было понятно, что и как нам делать:
Точка безубыточности простыми словами – это такой объем производства, при котором общие издержки равны общим доходам и таким образом нет прибыли. Эта точка нужна, чтобы понять когда начнутся прибыли. Также это означает, что компания прошла период окупаемости. Хотя, надо сделать сразу замечание, что в реальной жизни такой график трудно будет построить, т.к. он включает в себя предположение, что все затраты имеют только два типа поведения: постоянный и переменный. Но об этом в конце.
Итак, чтобы построить данный график, нам понадобятся следующие данные:

  1. Постоянные затраты (одна цифра, не зависит от объема производства);
  2. Переменные затраты – прямо пропорциональны объему производства. Достаточно стоимости единицы товара, переменные рассчитаются автоматически.
  3. Выручка, но, как и в случае с переменными затратами, нам достаточно цены за единицу.

Дальше, нам нужно найти точку безубыточности. Она находится на пересечении кривых общих издержек и выручки.
Математику все изучали, так что

Цена*Объем=Себестоимость единицы*Объем+Постоянные затраты
Объем*(Цена-Себестоимость)=Постоянные затраты
Объем = Постоянные затраты / (Цена – Себестоимость)

В денежном выражении = Цена * Постоянные затраты / (Цена – Себестоимость)
Итак, вот таблица, по которой мы и будем работать:

Данные для графика нам предстоит высчитывать. Объем производства берем произвольно, от 100 с шагом по 100 штук.
Выручка и Переменные затраты считаются как умножение объема производства на цену и удельную себестоимость.
Общие затраты – это сумма постоянных и переменных затрат.
Точка безубыточности считается по формуле, которую мы вывели ранее:

Теперь данные для графика готовы:

Обратите внимание, что хотя нам понадобятся только три кривые, я поставил сюда объем производства и кривую точки безубыточности с одной цифрой. Данные объема производства мне понадобятся для данных оси Х, а точка безубыточности нужна для наглядности.
Итак, выделяем данные четырех кривых (объем производства понадобится нам позже). Затем Вставка/Диаграммы/График/График с маркерами.

Диаграмма появится сразу здесь, но для лучшей наглядности мы сделаем следующее:
— Переместим ее на отдельный лист. Правая кнопка мыши на диаграмме/Переместить диаграмму/на отдельном листе.
— Поменяем подписи Оси Х – Правая кнопка мыши на диаграмме/Выбрать данные/Подписи горизонтальной оси/Изменить и выделяем ряд Объем производства (только цифры!)

— Выделяем точку безубыточности правой кнопкой/Формат ряда данных/Параметры маркера/Встроенный (точка и размер 10) плюс можно поменять цвет: Заливка маркера/Сплошная заливка/Цвет (черный)
В принципе, можно конечно поиграться с параметрами диаграммы, поставить название диаграммы, название оси, это можно сделать через вкладку Макет.

Итак, основная тема пройдена, теперь хотелось бы затронуть те допущения, из-за которых затруднительно построить этот график в реальной жизни:
Допущения, принятые в процессе нахождения точки безубыточности и построения графика безубыточности:

  • один временной период, т.е. постоянные затраты даны один раз и не меняются, шкала Х – это не временная ось.
  • затраты либо прямо пропорциональны объему производств, либо вообще не зависят от него. Но только сдавая экзамены на АССА F2, я столкнулся с описанием тех затрат, которые хоть и существуют, почему-то не упоминались в университете: ступенчатые постоянные затраты (stepped fixed costs) и смешанные (semi-variable costs).
  • цена не меняется, как и себес на единицу.
  • нет одноразовых (капитальных, например) затрат, например, в начале производства надо закупать оборудование, здания и т.д.
  • что произвели, то и продали.

Пример для скачивания:
Скачать пример

Учимся определять точку безубыточности

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

Читать еще:  Как сделать чтоб в excel вместо rc были буквы?

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

В данной статье мы рассмотрим подробно понятие «точки безубыточности производства», покажем как найти и определить точку безубыточности предприятия на простом примере, а также раскроем особенности расчета точки безубыточности для непроизводственных видов деятельности: торговли и сферы услуг.

Как рассчитать?

Универсальная формула для расчета точки безубыточности выглядит следующим образом: P*X = FC+VC(X) и означает равенство выручки от реализации фактическим затратам компании. Описание основных показателей для расчета точки безубыточности:

Существует несколько вариантов расчета точки безубыточности по данной формуле. Рассмотрим наиболее часто применяемые способы.

Первый вариант расчета: знаем затраты и объем продаж

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

P = (FC+VC(X)) / Х, где VC(x) — это произведение себестоимости 1 единицы продукции и целевого объема продаж.

Приведем пример того, как рассчитать точку безубыточности первым способом: Мы разработали продукт с себестоимостью 5 руб и в ходе исследования рынка определили, что спрос на него составит 200 штук в месяц при условии, что стоить он будет 19 рублей. Именно такая цена является привлекательной для нашей целевой аудитории. Но нам необходимо понять, сможем ли получать доход от такой продажи? Нам необходимо рассчитать цену в точке безубыточности для проекта, зная что постоянные затраты будут составлять 1500 рублей.

Подставив в формулу имеющиеся данные мы получим: Минимальная цена = (1500+5*200)/200 = 12,5 рублей. Таким образом, при цене в 12,5 рублей наш проект станет безубыточным. Это значит, что цена в 19 рублей позволит получать нам дополнительную прибыль с продаж и будет выгодной для компании.

Второй вариант расчета: знаем цену и затраты

Данный вариант является наиболее распространенным в мировой практике. Чаще всего компания при запуске проекта знает цену, по которой рынок готов купить товар и по которой товар будет соответствовать своему рыночному позиционированию; а также знает постоянные и переменные затраты на развитие проекта. В таком случае нам необходимо определить объем, который позволит выйти на нулевую прибыль при имеющемся уровне затрат.Найти точку безубыточности в таком случае поможет формула:

X = FC / (P-VC), где VC — себестоимость 1 единицы продукции, Р — цена 1 единица товара, а FC — постоянные затраты проекта.

Приведем пример, как провести расчет точки безубыточности по второму способу в Excel. Предположим, мы открываем компанию по производству яблок. Мы знаем, что постоянные затраты нашего производства составят 1500 рублей, себестоимость 1 единицы будет 7 рублей, а цена отгрузки — 15 рублей. Нам необходимо рассчитать объем производства в точке безубыточности проекта — используем для этого формулу выше.

Подставив в формулу имеющиеся данные, мы получим: Объем производства в натуральном выражении= 1500 / (15-7) = 188 единиц. Умножив получившееся значение на цену — мы получим выручку, при которой наш проект станет безубыточным.

Третий вариант расчета: для сферы услуг и торговли

Данный вариант поможет определить точку безубыточности для сферы торговли (оптового, розничного, интернет — магазина) или для отрасли услуг. Если компания не является производственным предприятием, то рассчитать себестоимость 1 единицы товара для нее либо сложно, либо эти значения слишком разные, чтобы брать по ним среднее значение. Когда у торгового предприятия 1000 наименований по совершенно разной входящей цене, удобнее учитывать в расчетах целевую рентабельность продаж, а не фактические показатели себестоимости. Для отрасли услуг также удобнее рассчитывать точку безубыточности на основе целевой рентабельности работы.

Рентабельность продаж — это установленное соотношение между ценой товара и его себестоимостью. Другими словами компания определяет норму прибыли, которую он планирует зарабатывать с продажи товара (наценку на его себестоимость). Зная целевую рентабельность и постоянные затраты, можно определить необходимый размер выручки в точке безубыточности по следующей формуле:

S = FC / R, где S — выручка от продаж за период, R — целевая рентабельность продаж, а FC — постоянные затраты проекта.

Разберем на примере такой вариант расчета. Предположим мы решили открыть компанию по оказанию консалтинговых услуг. В связи с широтой понятия наших услуг нам очень сложно определить себестоимость 1 проекта, поэтому нам проще заложить в бизнес план целевую рентабельность по проекту. Мы решили установить норму рентабельности продаж на уроне 57%. Мы прогнозируем, что наши постоянные затраты в месяц будут составлять 280 000 рублей, а средняя цена услуги будет составлять 8 000 рублей.

Читать еще:  Как сделать таймер обратного отсчета в excel?

Таким образом, используя нашу формулу мы можем определить точку безубыточности проекта: Целевой объем продаж = 280 000 / 57% = 491 228 рублей. Предположив, что средняя цена нашей услуги будет составлять 8 000 рублей, нам для окупаемости проекта необходимо получать по 61 клиенту в месяц (491228 / 8000 = 61).

График точки безубыточности

Определить точку безубыточности проекта можно также графическим методом. Для того, чтобы построить график для нахождения точки безубыточности вам необходимо знать: уровень постоянных затрат, себестоимость 1 единицы товара и цену продажи 1 единицы товара. Затем вы должны построить 2 кривых. Первая кривая будет являться суммой всех издержек компании (и зависеть от объема производства): y = FC+VC(x). Вторая кривая будет означать выручку от продажи товара, которая будет зависеть от объема производства компании: y = px.

Пересечение двух данных кривых и будет являться точкой безубыточности проекта.

Готовые решения

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

Расчет точки безубыточности в Excel с примерами

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

Чем ниже точка безубыточности объема производства и продаж, тем выше платежеспособность и финансовая стабильность фирмы.

Формула точки безубыточности в Excel

Существует 2 способа расчета точки безубыточности в Excel:

  1. В денежном эквиваленте: (выручка * постоянные издержки) / (выручка – переменные издержки).
  2. В натуральных единицах: постоянные затраты / (цена – средние переменные затраты).

Внимание! Переменные затраты берутся из расчета на единицу произведенной продукции (не общие).

Чтобы найти безубыточность, нужно знать:

Постоянные издержки (не зависящие от производственного процесса или реализации) – арендные платежи, налоги, заработная плата для руководящего состава, лизинговые выплаты и т.д.

Переменные издержки (зависят от производственных объемов) – стоимость сырья и материалов, коммунальные платежи в производственных помещениях, зарплата рабочих и т.п.

Цену продажи единицы товара.

Внесем данные в таблицу Excel:

  1. Найти объем производства, при котором предприятие получит чистую прибыль. Установить взаимосвязь между этими параметрами.
  2. Рассчитать объем реализации товара, при котором наступит точка безубыточности.

Для решения этих задач составляем следующую таблицу с формулами:

  1. Переменные затраты зависят от объема производимой продукции.
  2. Общие затраты – это сумма переменных и постоянных издержек.
  3. Доход – произведение объема производства и цены товара.
  4. Маржинальный доход – это общий доход без переменных затрат.
  5. Чистая прибыль – доход без постоянных и переменных издержек производства.

Заполним таблицу и посмотрим, на каком выпуске продукции предприятие выйдет «в плюс».

Начиная с 13 выпуска продукции, чистая прибыль стала положительной. А в точке безубыточности она равняется нулю. Объем производства – 12 единиц товара. А доход от продаж – 120 000 рублей.

Как построить график точки безубыточности в Excel

Для наглядной демонстрации экономического и финансового состояния предприятия составим график:

  1. Определяемся с внешним видом графика и нажимаем кнопку «Выбрать данные».
  2. Для демонстрации нам нужны столбцы «Общие затраты», «Доход», «Чистая прибыль». Это элементы легенды – «Ряды». Вносим вручную «Имя ряда». А в строке «Значения» делаем ссылку на соответствующий столбец с данными.
  3. Диапазон подписей горизонтальной оси – «Объем производства».

Получаем график вида:

Немного изменим график (макет диаграммы).

Такая демонстрация позволяет увидеть, что чистая прибыль в точке безубыточности действительно равняется «нулю». И именно после двенадцатого выпуска продукции «пошел плюс».

Где нужны подобные расчеты

Показатель «точка безубыточности» востребован в экономической практике для решения следующих задач:

  1. Вычисление оптимальной цены за изделие.
  2. Расчет объема затрат, при котором фирма еще остается конкурентоспособной.
  3. Составление плана реализации товара.
  4. Нахождение объема выпуска продукции, при котором появится рентабельность.
  5. Анализ финансового состояния и платежеспособности предприятия.
  6. Нахождение минимального объема изготовления.

Готовые расчеты и шаблоны для анализа выхода предприятия на безубыточность:

Результаты подобных расчетов востребованы как внутренними, так и внешними пользователями. Безубыточность учитывается при принятии управленческих решений, дает представление о финансовом состоянии фирмы. Применение подобной модели – способ оценки критического уровня производственного объема и реализации товаров и услуг.

Ссылка на основную публикацию
Adblock
detector