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

Содержание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

X = FC / (P — VC),

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

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

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

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

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

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

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

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

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

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

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

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

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

Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!

Поделиться с друзьями:

Поиск по сайту:

tutorexcel.ru

Точка безубыточности в Excel — Денис Мушинский

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

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

В моем примере формула ЧП выглядит так:

Выручка х Рентабельность продаж — Расходы

Будем считать, как должны измениться элементы уравнения, чтобы бизнес стал работать в НОЛЬ (точка безубыточности)

Начнем с выручки

Итак, если выручка упадет до 3 341 157 чистая прибыль будет равна 0

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

Очередь рентабельности продаж

Если рентабельность сократится до 20%, ЧП будет равна 0

Финалочка, расходы

Если расходы вырастут до 1 497 013, бизнес получит безубыток.

Для кого это полезно?!

Конкуренция растет, денег у клиентов все меньше, что приводит к падению выручки, рентабельности продаж, при этом расходы как правило стоят на месте и не думают сокращаться. Чистая прибыль тает тает, но тревоги это не вызывает, потому что сокращается по чуть чуть.

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

Расчет:

Текущая рентабельность продаж — 32%

Рентабельность продаж в безубытке — 20%

Дельта — 12%

Тенденция падения — 2% в год

Осталось лет до безубытка: 12/2 = 6 (можете перепроверить)

Отрезвляет и заставляет искать решения.

Подписывайтесь в Instagram

https://www.instagram.com/denismushinskiy/

mushinsky.ru

Пример формулы для расчета точки безубыточности BEP в Excel

В бизнесе часто возникает необходимость определения размера дохода необходимого для достижения чистой прибыли равной нулю, чтобы выйти из минусов на 0. Другими словами, выйти на точку безубыточности – показатель BEP (на английском Break-Even Point). Так вычисляемый размер дохода называется еще порогом рентабельности. Чтобы его определить, необходимо оценить постоянные расходы и процентную часть переменных расходов. На основе эти данных можно вычислить размер дохода устанавливающий порог рентабельности BEP.

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

Ниже на рисунке представлен пример расчета точки безубыточности. В столбце B в некоторых ячейках находится буква «П», которая значит «Постоянные расходы». Или же там находятся ячейки с процентным значением, определяющим какую долю составляют расходы от доходов. Например, затраты на анализ и развитие будут понесены согласно принятого бюджета и не будут изменяться, если доход вырастет или упадет. Если же вместо этого будут выплачены премии, расходы продажи будут расти или снижаться в зависимости от уровня дохода.

Показатели использование для расчета порога рентабельности (числа напротив пустых ячеек столбца B) вычисляются следующим образом:

Формула расчета операционной прибыли.
Формула расчета чистой прибыли после вычета переменных издержек.
Расчет валовой прибыли.

Два вида переменных расходов, изображенных на рисунке:

Себестоимость продаж (стоимость реализованных товаров COGS).
Стоимость продажи.

Вычисляются через умножение значения дохода на процентное значение расходов.

Показатели для расчета точки безубыточности BEP в Excel

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

В ячейке C17 содержащую значение – чистая прибыль, введите значение 0.
В столбце C в ячейках содержащих постоянные расходы введите согласно с описанием в столбце … соответствующие им показатели.
В ячейке B6 введите размер премии в процентах, выплаченной фирмой (в данном примере 8%).
В ячейке B3 введите процентное значение вычисленное путем вычитания от единицы ожидаемой валовой прибыли в процентах. В данном примере фирма ожидает валовую прибыль в размере 60%. Поэтому в ячейке B3 находится только результат вычисления формулы 40%: =1-C4/C2. Не стоит эту формулу вводить в ячейку B3, чтобы не зациклить ссылки в других формулах, достаточно ввести только результат вычисления.
В ячейке C12 введите формулу для расчета операционной прибыли. Операционная прибыль должна быть равна сумме позиции «Расходы на проценты» и «Другие затраты и расходы». Как показано на рисунке, если оцененные расходы на проценты будут равны 465, а другие затраты и расходы равны 1368, операционная прибыль должна быть равна 1833, чтобы чистая прибыль была равна 0.
В ячейке C7 формулу для вычисления чистой прибыли после вычитания переменных издержек. Это сумма операционной прибыли и постоянных операционных расходов. Данное значение будет использовано для вычисления дохода.
В C6 введите формулу для вычисления расходов продаж. Пока не будет еще введен результат вычисления формулы расчета дохода, результат этой формулы будет равен нулю. После введения формулы вычисления доходов в ячейке появится правильное значение.
В C3 введите формулу для вычисления стоимости реализованных товаров (себестоимость продаж COGS). Подобно как в случае с формулой для вычисления расходов продажи, пока не будет рассчитанная прибыль, формула будет возвращать значение 0.
На конец в ячейке С2 введите формулу для вычисления дохода. Доход вычисляется путем деления позиции «Чистая прибыль после вычета переменных издержек» на значение, возникшее после вычитания от единицы суммы переменных расходов в процентах. На рисунке в данном примере переменные расходы составляют 48% – от значения дохода (40%+8%). Показатель ячейки C7 следует разделить на число, полученное после вычитания 48% от 100% и таким способом вычисляется размер дохода:

= C7/(1-СУММ(B3:B6)

В результате расчетов точка безубыточности составляет – 16 935$.

Если валовая прибыль в процентах для данной фирмы составляет 60%, будут выплаченные премии в размере 8% от дохода, а принятые постоянные расходы будут детально оцениваться. Чтобы фирма преодолела порог рентабельности, она должна будет выйти на доход в размере 16 935$.

exceltable.com

пошаговая инструкция. Экономика предприятия :: BusinessMan.ru

Известно, что выпуск продукции подразумевает вложение средств в ее производство и реализацию. Каждый предприниматель, намереваясь создать благо, преследует цель – это получение прибыли от продажи товаров/услуг. График безубыточности помогает увидеть в стоимостном и натуральном выражении выручку и объем продукции, при которых прибыль равна нулю, но все издержки уже покрыты. Соответственно, перешагнув точку безубыточности, каждая последующая проданная единица блага начинает приносить прибыль предприятию.

Данные для графика

Для составления последовательных действий и получения ответа на вопрос: «Как построить график безубыточности?» требуется понимание всех составляющих, необходимых для создания функциональной зависимости.

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

Аренда помещения, страховые взносы, амортизация оборудования, оплата труда, управления – это составляющие постоянных издержек. Их объединяет одно условие: все перечисленные расходы оплачиваются вне зависимости от объемов производства.

Приобретение сырья, транспортные расходы, оплата труда производственного персонала – элементы переменных расходов, размер которых обусловлен объемом производимого блага.

Выручка также является исходной информацией для нахождения точки безубыточности и выражается произведением объема продаж на цену.

Аналитический метод

Существует несколько способов определения критического объема. Аналитическим методом, то есть через формулу, тоже может быть найдена точка безубыточности. График в этом случае не требуется.

Прибыль = Выручка – (Постоянные расходы + Переменные расходы * Объем)

Определение безубыточности выполняется при условии, что прибыль равна нулю. Выручка является произведением объема продаж на цену. Получается новое выражение:

0 = Объем*Цена – (Постоянные расходы + Переменные * Объем),

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

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

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

Графический метод

Спрогнозировать основные показатели деятельности предприятия, учитывая неизменные условия на рынке, способен такой экономический инструмент, как график безубыточности. Основные шаги:

Строится зависимость объемов продаж от выручки и издержек, где по оси Х отражаются данные по объему в натуральном выражении, а по У – выручка, издержки в денежном эквиваленте.
Строится в полученной системе прямая, параллельная оси Х и соответствующая постоянным издержкам.
Откладываются координаты, соответствующие переменным издержкам. Прямая устремляется вверх и начинается с нуля.
Наносится на график прямая валовых издержек. Она параллельна переменным и свое начало берет по оси ординат от точки, из которой начиналось построение постоянных расходов.
Построение в системе (Х, У) прямой, характеризующей выручку анализируемого периода. Выручка рассчитывается при условии, что цена продукции не меняется в этот период и выпуск производится равномерно.

Пересечение прямых выручки и валовых расходов, спроецированных на ось Х, и есть искомая величина – точка безубыточности. График на примере будет рассмотрен ниже.

Пример: как построить график безубыточности?

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

Первое, что необходимо сделать – это свести данные о выручке, издержках и объемах продаж в единую таблицу.

Далее следует вызвать функцию «График с маркерами» через панель инструментов с помощью вкладки «Вставка». Появится пустое окно, правой кнопкой мыши выбирается диапазон данных, который включает в себя ячейки всей таблицы. Изменяется подпись оси Х через выбор данных, относящихся к объему выпуска. После чего в левой колонке окна «Выбор источника данных» можно удалить объем выпуска, так как он совпадает с осью Х. Пример представлен на рисунке.

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

Пример по формуле

Исходные данные задачи берутся из таблице в «Экселе». Известно, что производство продукции циклично и составляет 150 единиц. Выпуску соответствуют: постоянные издержки – 20 000 ден.ед; переменные расходы – 6000 ден. ед; выручка – 13 500 ден. ед. Необходимо произвести расчет безубыточности.

Определение переменных затрат на выпуск одной единицы: 6000 / 150 = 40 ден. ед.
Цена одного реализуемого блага: 13 500 / 150 = 90 ден. ед.
В натуральном выражении критический объем: 20 000 / (90 — 40) = 400 единиц.
В стоимостном выражении, или выручка при этом объеме: 400 * 90 = 36 000 ден. ед.

График безубыточности и формула привели к единому решению поставленной задачи – определению минимального объема производства, покрывающего затраты на выпуск продукции. Ответ: 400 единиц необходимо выпустить, дабы покрыть все затраты, выручка при этом будет составлять 36 000,00 ден. ед.

Ограничения и условия построения

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

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

Из-за перечисленных условий точка безубыточности, пример нахождения которой был рассмотрен, считается теоретической величиной в проекции классической модели. На практике расчеты при многономенклатурном производстве гораздо сложнее.

Недостатки модели

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

Практическое использование точки безубыточности

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

Основные цели использования модели:

Расчет цены продукции.
Определение объема выпуска, обеспечивающего рентабельность предприятия.
Определение уровня платежеспособности и финансовой надежности. Чем выпуск продукции находится дальше от точки безубыточности, тем запас финансовой прочности выше.
Инвесторами и кредиторами — оценка эффективности развития и платежеспособности фирмы.

businessman.ru

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

30.07.2013 Григорий Цапко Калькуляторы, шаблоны, форматы, Малая автоматизация, Малый бизнес, Управленческий учет

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

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

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

Основная идея здесь следующая:

Расходы на предприятия делятся на переменные – полностью зависящие от объемов производства продукции, и постоянные — не зависящие от объемов производства.

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

Это достаточно упрощенная модель разделения затрат. В действительности затраты могут быть условно-постоянными, например стоимость аренды производственных помещений может лишь до определенного предела оставаться постоянной, однако с ростом объемов производства могут потребоваться большие площади, и затраты изменяться скачкообразно. Это необходимо всегда иметь ввиду.

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

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

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

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

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

Скачать

excel-training.ru