График точки безубыточности в Excel
Теперь изучим как построить график точки безубыточности, чтобы наглядно увидеть где она находится. На основе исходных данных рассчитаем итоговые доходы, общие издержки (сумма постоянных и переменных расходов) и итоговую прибыль (разница между доходами и общими издержками) в зависимости от объема производства (установим шаг в 100 единиц продукта).
Выделим диапазон данных с доходами и расходами (B9:L11), переходим на вкладку Вставка -> Диаграмма -> Точечная и выбираем точечную диаграмму с прямыми отрезками (также можно построить с помощью стандартного линейного графика):
Спасибо за внимание! Если у вас остались вопросы — жду ваших комментариев
Главный вопрос — как снизить?
Существует два пути, хотя может быть есть ещё какие-нибудь, о которых я не знаю.
1. Уменьшать издержки. Экономия на электроэнергии, может быть снижение арендной платы, заработной платы или других постоянных расходов, могут помочь в уменьшении этого показателя.
2. Увеличивать наценку на товар. Не популярный способ уменьшения точки безубыточности, но, тем не менее, в современных торговых реалиях без него никуда. Мы в магазине так же планируем довести среднюю торговую наценку до 55%. Сейчас она составляет 40-43%, но такая низкая из-за того, что мы месяц как открылись и пока начинаем завоёвывать свою долю рынка.
Если проще всего увеличить продажи, то надо заняться именно этим вопросом и увеличить оборотные средства, тем самым повысив чистую прибыль. Я думаю, это самый оптимальный способ и после того, как достигнут максимальный охват среди потенциальной аудитории, можно переходить на оптимизацию постоянных и переменных издержек.
Расчет в Excel
Для удобства вычисления можно использовать офисное приложение Excel. В ней легко устанавливать связь между данными и строить таблицы с графиками для сравнения.
Составление таблицы
Начинать расчет точки безубыточности необходимо с занесение данных по затратам и стоимости товара. К примеру постоянные затраты равны 150 рублей, переменные – 50 рублей, а стоимость единицы товара – 70 рублей.
На основании введенных данных и производственного объема формируется таблица, где должна прослеживаться динамика изменения чистой прибыли или убытка. Это необходимо для определения точки безубыточности.
Составим вторую таблицу со столбцами:
- Объем производства.
- Постоянные затраты.
- Переменные затраты.
- Общие затраты.
- Выручка.
- Маржинальный доход.
- Чистая прибыль.
Объем производства выбирают самостоятельно. В примере взято 0-20 штук. Постоянные затраты переносят с первой таблицы ячейки D3. Так как постоянные затраты не зависят от объема произведенной продукции, то их значение остается неизменным во всем столбике. Для сохранения значения ячейки при распространении значений перед адресом ставят значок $ — $D$3.
Переменные затраты определяют по формуле:
Зпер = Q*Зпер на 1 ед. = A9*$D$4
Общие затраты, их еще называют валовыми, равны сумме переменных и постоянных затрат – В9+С9.
Выручка или доход равна произведению объема на стоимость единицы товара – А9*$D$5, а маржинальный доход – Е9-С9.
Чистая прибыль, как говорилось выше, равна маржинальному доходу за вычетом постоянных затрат: Е9-С9-В9.
С восьмой единицы организация начнет получать прибыль. При меньшем объеме выручка не может перекрыть общие издержки. Первая прибыль составляет 10 рублей, то есть это не совсем точка безубыточности, когда общие издержки равны выручке. Точное значение можно определить по формуле:
ТБнат = 150/(70-50) = 7,5 шт.
Математическое значение точки безубыточности равно 7,5, но произвести какую-то часть от целого товара не возможно. Экономисты округляют значение в большую сторону – 8 шт. Выручка составит 560 рублей.
В таблицу можно добавить два дополнительных показателя – маржа безопасности (запас прочности) в денежном и процентном исчислении (КБ%, КБден). Это поможет определить, сколько выручки не хватает до достижения точки безубыточности и на сколько необходимо снизить общие затраты, чтобы достичь точки безубыточности при определенном объеме. По этим коэффициентам можно определить безопасное финансовое положение – при достижении 30%.
КБден = Вфакт – Втб = Е9-$Е$14
КБ% = КБден*100%/Вфакт = Н10/Е10*100
Втб – выручка для точки безопасности;
Вфакт – фактическая выручка.
Построение графика
Для понимания строят график. В Execel существует несколько тип диаграмм. Наилучшее восприятия информации в этом примере будет точечная диаграмма, которая находится во вкладке «Вставка». По горизонтальной оси располагают объем произведенной продукции, а вертикальной – выручка и сумма затрат. Данные для построения:
- общие затраты;
- чистая прибыль;
- выручка.
На графике три прямые. Пересечение общих затрат и выручки будет в точке безубыточности. Она показывает нулевое значение чистой прибыли по горизонтали и минимальную выручку для покрытия понесенных затрат по вертикали.
Для построения подробного графика с маржинальным доходом и переменными затратами необходимо расширить количество данных.
Визуальное отображение расчета в Excel
На примере расчета точки безубыточности хлебного магазина, который мы рассчитывали ранее, построим график расчета и рассчитаем тот же параметр с помощью средств Excel. Вот как это будет выглядеть:
На рисунке видно, что мы сделали расчет точки безубыточности с помощью четырех клеток. Нижняя же таблица расчета прибыли по магазину показывает, что он выходит из убытков только когда объем продаж становится равным 13 000 штук (что больше расчетного 12 368).
Формулы, которыми мы пользовались при расчете показателя, вы можете посмотреть на следующем рисунке:
А график ниже показывает логику расчета показателя. Чтобы выйти в прибыль, наша выручка (синяя линия на графике) должна быть больше, чем постоянные (темно-голубая заливка) и переменные расходы (светло-голубая заливка) вместе взятые. Точка пересечения этих двух графиков – равна точке безубыточности.
Что такое точка безубыточности
Точка безубыточности – это когда разница полученных и затраченных денег равна нулю.
Это тот момент в бизнесе ради которого все и начиналось, когда компенсируются расходы фирмы на получение продукции. Последующие полученные деньги переходят в прибыль.
Показатель ТБУ вычисляет двумя способами, получая результат: в денежном или натуральном значении. Показатель позволяет выяснить минимальный объем продукции или полученной дохода для покрытия расходов.
Если компания не пересекла границу окупаемости, это означает, что она работает в убыток и необходимо оптимизировать бизнес-процессы, уменьшать издержки или закрывать бизнес. Так как это приведёт по понятным причинам к разорению.
Расчет точки безубыточности
Теперь, когда мы выяснили, зачем нужна точка безубыточности, давайте научимся ее находить. Допустим, у нас есть следующие данные:
- Постоянные затраты;
- Переменные затраты на единицу готового товара (шт., партий и т.д.);
- Стоимость продажи единицы товара.
Приступим к выполнению поставленной задачи.
- На основе данных выше на нужно построить новую таблицу:
- В первом столбце должны содержаться данные о количестве произведенных единиц продукции (шаг определяем на свое усмотрение, в нашем случае мы выбрали цифру 1).
- Во втором столбце нужно отразить сумму постоянных затрат, которая будет оставаться неизменной с ростом объема произведенной продукции.
- В третьем столбце показываем суммы переменных затрат, которые считаются путем перемножения количества произведенных единиц товара на 4100 руб (переменные затраты на 1 ед. товара).
- В четвертом столбце считаем итоговую сумму затрат (переменные плюс постоянные).
- В пятом столбце рассчитываем общий доход, перемножив количество единиц на стоимость продажи товара (6850 руб.).
- В шестом столбце считаем прибыль путем вычитания итоговых затрат из дохода.
Как мы видим из полученных данных, результат, равный нулю, получается при объеме производства в 12 единиц. Это и есть точка безубыточности, когда общие суммы доходов и затрат равны. Все, что меньше объема в этой точке, приносит организации отрицательную прибыль, другими словами, убыток. Начиная с 13 единицы появится прибыль, которая будет расти по мере наращивания объемов производства.
Назначение точки безубыточности
Итог деятельности предприятия за определенный промежуток времени – это доход и издержки. Чтобы узнать уровень прибыли, расходы вычитают из доходов, но результат не всегда положительный, особенно если организация недавно появилась на рынке. Точка безубыточности – это экономическая ситуация, когда доходы покрывают расходы, однако предприятие еще не добилось прибыли. Значения координат нулевые.
Выведение точки безубыточности приносит понимание, сколько необходимо произвести и продать, чтобы обеспечить стабильное развитие. Этот показатель высчитывают, чтобы определить состояние предприятия. Если над точкой безубыточности находятся высокие показатели производства и продаж, компания работает стабильно, риски минимальны. Также оценка ситуации по нулевой точке помогает менеджерам принимать серьезные решения – например, о расширении производства и внедрении новых методов. Полученные данные предоставляют инвесторам и кредиторам, чтобы подтвердить устойчивость организации.
Модель точки безубыточности в Excel
Многие экономисты и финансовые аналитики используют самый известный табличный процессор для различных расчетов и прогнозов. Главное преимущество этой программы заключается в том, что при любых корректировках результаты вычислений пересчитываются автоматически. Это очень удобно для определения оптимального результата по продажам, регулирования затрат и других функций финансового планирования.
Приведенный выше график порога рентабельности был построен на основании таблицы с данными о расходах и объеме продаж. С помощью формулы мы вычислили выручку и прибыль для каждого количества единиц товара.
| Постоянные расходы Cconst, $ | 35000 |
|---|---|
| Переменные расходы Cvar на ед., $ | 0,85 |
| Цена за единицу товара P, $ | 2 |
| Объем продаж, шт. | Постоянные расходы, $ | Переменные расходы, $ | Всего расходов, $ | Доход, $ | Прибыль, $ |
|---|---|---|---|---|---|
| 10000 | 35000 | 8500 | 43500 | 20000 | -23500 |
| 20000 | 35000 | 17000 | 52000 | 40000 | -12000 |
| 30000 | 35000 | 25500 | 60500 | 60000 | -500 |
| 40000 | 35000 | 34000 | 69000 | 80000 | 11000 |
| 50000 | 35000 | 42500 | 77500 | 100000 | 22500 |
| 60000 | 35000 | 51000 | 86000 | 120000 | 34000 |
| 70000 | 35000 | 59500 | 94500 | 140000 | 45500 |
| 80000 | 35000 | 68000 | 103000 | 160000 | 57000 |
| 90000 | 35000 | 76500 | 111500 | 180000 | 68500 |
| 100000 | 35000 | 85000 | 120000 | 200000 | 80000 |
График точки безубыточности в Excel
Теперь изучим как построить график точки безубыточности, чтобы наглядно увидеть где она находится.
На основе исходных данных рассчитаем итоговые доходы, общие издержки (сумма постоянных и переменных расходов) и итоговую прибыль (разница между доходами и общими издержками) в зависимости от объема производства (установим шаг в 100 единиц продукта).
При анализе данных в таблице видно, что прибыль становится равной нулю, при объеме производства в 700 единиц продукта, и при доходах в 350 000 в денежном выражении, что совпадает с расчетами по формуле, произведенными в предыдущем пункте.
Выделим диапазон данных с доходами и расходами (B9:L11), переходим на вкладку Вставка -> Диаграмма -> Точечная и выбираем точечную диаграмму с прямыми отрезками (также можно построить с помощью стандартного линейного графика):
Пересечение двух данных прямых (доходов и расходов) и будет искомой точкой, где координата по горизонтальной оси графика отвечает за расчет в единицах продукции, по вертикальной оси — в денежном выражении.
Чтобы добавить точку безубыточности на график можно воспользоваться либо макросом поиска точек пересечения нескольких графиков, либо просто поместить ее на диаграмму с помощью добавления нового ряда данных, формулу которой мы уже подсчитывали выше:
Можно варьировать любой из параметров (цена и себестоимость продукции, уровень постоянных затрат) — расчет будет меняться исходя из введенных данных:
Поэкспериментировать с расчетами и подробно освоить построение графика безубыточности в Excel можно скачав файл с примером.
Спасибо за внимание!
Если у вас остались вопросы — жду ваших комментариев. Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!
Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!
Что такое точка безубыточности и для чего её рассчитывать
Точка безубыточности — минимальная выручка, при которой бизнес полностью покрывает свои расходы.
Если компания достигла точки безубыточности — значит, она отработала «в ноль» и её доходы равны расходам. Всё, что она заработает сверх этой точки, станет её прибылью.
По-другому точку безубыточности могут называть порогом или точкой рентабельности, критической точкой продаж, безубыточным объёмом производства, точкой нулевой прибыли или ценой безубыточности.
Компании могут использовать эти показатели:
- чтобы определить минимальный объём продаж — сколько товаров или услуг нужно произвести и продать, чтобы не уйти в убыток;
- чтобы определить оптимальную цену товаров и услуг;
- чтобы определить, когда окупится проект — когда полученные доходы станут больше расходов;
- чтобы проанализировать финансовое состояние бизнеса — отследить, как меняется точка безубыточности, и принять меры, чтобы достичь её быстрее. Например, оптимизировать затраты или повысить цены на товары и услуги.
Преимущества и недостатки модели точки безубыточности
Из достоинств модели можно выделить следующие:
- Простота. В расчетах используются несложные формулы, а также данные легко собрать из бухгалтерского и управленческого учета.
- Разделение издержек на постоянные и переменные очень полезно для финансового анализа. Правила бухгалтерского учета не подразумевают обязательность такой классификации, а эта несложная работа поможет получить более достоверные данные о финансовых результатах. Ведь нужно планировать объем прибыли не только в целях налогообложения, но и для повышения инвестиционной привлекательности компании.
Недостатки модели:
- Не учитывает сезонность, колебания курсов, внедрение новых технологий и другие внешние факторы.
- При большом ассортименте продукции могут изменяться цены на отдельные единицы. В связи с этим возможно искажение расчетов.
- Увеличение постоянных затрат происходит неравномерно. Например, арендная плата может увеличиваться каждый год, а заработная плата – раз в квартал. Из-за такого ступенчатого увеличения постоянных издержек расчеты могут быть неактуальны.
График порога рентабельности
Теперь построим график порога рентабельности, используя исходные данные:
| Постоянные расходы Cconst, $ | 35000 |
|---|---|
| Переменные расходы Cvar на ед., $ | 0,85 |
| Цена за единицу товара P, $ | 2 |
График точки безубыточности (рентабельности продаж) нам показывает:
- по горизонтальной оси – количество проданных изделий;
- по вертикальной оси – деньги.
Линии на графике:
- фиолетовая – общие расходы (переменные и постоянные затраты);
- голубая – доход (выручка);
- коричневая – прибыль.
Как видим, на графике точка безубыточности находится на пересечении линий доходов и расходов и соответствует объему продаж примерно 30 435 шт. Линия прибыли пересекает нулевую отметку и переходит из зоны убытков в зону прибыли.
Точка безубыточности в Excel
Разберемся как рассчитывать точку безубыточности в Excel для проекта (продукта, компании), и на примере подробно изучим как построить график безубыточности.
Приветствую всех, дорогие читатели блога TutorExcel.Ru.
Перед там как рассчитать формулу точки и построить график безубыточности, вспомним немного о теоретической части вопроса. Как непосредственно следует из названия, точка безубыточности показывает момент, когда проект начинает работать без убытков, т.е. приносить прибыль. Другими словами, это такой объем производства товара (или услуг) и реализации продукции при котором доходы от реализации товаров (или услуг) равны совокупным издержкам:
В целом результаты практически любого коммерческого проекта можно упрощенно представить следующим образом:
- Доходы. Выручка складывается из объема производства умноженного на цену продажи продукта;
-
Расходы. Издержки разделяются на постоянные и переменные затраты.
- Постоянные затраты. Расходы не зависящие от объема производства. К данному типу затрат обычно относятся расходы на аренду, на оплату труда персонала и т.д.;
- Переменные затраты. Расходы на себестоимость, складываются из объема производства помноженного на удельную себестоимость продукта.
Итоговая прибыль проекта выражается как разность доходов и расходов, соответственно, так как постоянные издержки остаются неизменными, то за счет изменения объема производства изменяются доходы и переменные затраты, вследствие чего меняется итоговая прибыль.
Наша задача состоит в поиске момента, когда итоговая прибыль станет нулевой, т.е. доходы и расходы будут равны друг другу в абсолютном выражении и производство перестанет приносить убытки, следовательно, формула точки безубыточности примет следующий вид:
P * X (доходы) — FC — VC * X (общие расходы) = 0, где
P — цена продажи 1 единицы продукта, X — объем продукции, FC — постоянные затраты, VC — переменные затраты на 1 единицу продукта.
Формула расчёта точки безубыточности для магазина
Поработав немного с вычислениями, я построил себе формулу.
В данном материале я постарался максимально упросить описываемый вопрос, ведь мы не диплом пишем по экономической дисциплине и для нашего маленького магазина не нужен расчёт сложных показателей, вроде коэффициента маржинального дохода.
Имеем:
- Среднюю торговую наценку по магазину 55%;
- Необходимую прибыль, чтобы покрыть все расходы — 75 380 рубля;
Под расходами я подразумеваю все операционные затраты. Это все платежи, которые не входят в оборотные средства. Сюда входит арендная плата за помещение, кассовый аппарат, транспортные расходы, хозяйственные траты и так далее.
Сопоставление этих двух величин и даст нам определение точки рентабельности магазина.
В основу своего вычисления я взял формулу пропорций:
Классический способ вычисления маржинальной точки безубыточности можно сделать через составление пропорции из трёх величин. Одна из них (необходимая прибыль) и торговая наценка известна нам.
Прибыль составляет 55% (торговая наценка) в пропорции от выручки. Себестоимость в свою очередь составляет 100%. Соответственно общая выручка, это сумма прибыли и себестоимости проданных товаров. В процентном эквиваленте ровняется 155%.
Пример расчёта
Получаем выручку через себестоимость
Чтобы получить значение необходимой выручки, нам надо к прибыли (Z) прибавить себестоимость, которую мы можем получить путём решения пропорции (прибыль, умноженная на торговую наценку).
В итоге мы получаем 212 434 рубля. Эта сумма и является тем необходимым минимумом, который позволит магазину оставаться рентабельным. Всё деньги, полученные более этой суммы, будут чистой прибылью (за вычетом себестоимости конечно).
Вот так это выглядит в таблице Excel:
Получаем выручку без себестоимости
Ещё проще можно узнать необходимый доход для работы магазина в «0» по такой формуле:
Прибыль умножаем на пропорцию выручки и делим на пропорциональный коэффициент прибыли. В итоге получаем опять сумму 212 434 рубля.
| Объем в процентах | Коэффициент | Объем в рублях | |
| Выручка | 155% | 1.55 | 212434 руб. |
| Себестоимость | 100% | 1 | 137054 руб. |
| Прибыль | 55% | 0,55 | 75380 руб. |
Все расчёты я прокомментировал в видео, которое вы можете посмотреть ниже. Так же приведен алгоритм расчётов.
2 способ
Взял его из файла Excel, который автоматически считает необходимую выручку, по известным параметрам — прибыль и торговая наценка. Но присмотревшись внимательней, он оказался таким же, как моя предыдущая формула расчёта выручки без себестоимости.
Плюс такой записи в том, что её очень легко добавить в Ексель, где автоматически меняются значения. Фактически вам надо задать только одну величину — торговую наценку в формате 1,55 и всё, а для предыдущей формулы потребовалось бы ещё указывать и 0,55.
Файлик в Excel c формулой определения точки безубыточности — скачать.
Запас прочности — что это за показатель?
Ещё этот показатель называют как кромка безопасности или маржа безопасности. Измеряют его в разнице фактической выручки от той, которая необходима для работы в ноль. Показывает, насколько далеко компания находится от точки безубыточности. Простыми словами, по этому показателю можно судить о запасе прочности предприятия.
Расчёт по формуле:
Подробности
Для понимания можно построить график в программе Excel
Расширив количество исходных данных, можно построить график с маржинальным доходом.
Самые простые расчеты – для предприятия, производящего однотипные товары. Расчеты, когда предприятие, изготавливает многотипную продукцию имеют некоторые тонкости, которые нужно учитывать при определении ТБУ.
Разберем пример предпринимательской выручки – рыночную торговлю. Реализуемым продуктом будут арбузы с установленной по всему району ценой. Закупают арбузы на юге страны и транспортируют в северные области. Данный вид бизнеса имеет сезонный характер, но приносящий неплохую прибыль.
Главные показатели:
— цена 1 арбуза — 200 рублей,
— переменные затраты (Зпер) на 1 ед. составляют 100 руб, сюда входит оплата работы продавца, оптовая закупка,
— постоянные затраты (Зпост) — составляют 10 000 рублей в месяц (учитывая налоговые отчисления, аренда точки на рынке, расходы на перевозку, тара),
— месячный доход – 32 000 рублей.
Для выгодного планирования
необходимо произвести расчет минимального объема продаж и определить значение дохода, чтобы перекрыть понесенные расходы.
Для расчета в денежном выражении объем проданных арбузов и значение переменных расходов на данный объем:
Q мес = 32 000/200 = 160 шт;
Зпер на объем =100*160 = 16 000 рублей.
Определяем рентабельность в денежном выражении, применяя разные формулы:
ВЕРден = 32 000*10 000/(32 000-16 000) = 20 000 руб.
ВЕРден = Зпост/((200-100)/200) = 20 000 рублей
ВЕРден+100*200 = 20 000 рублей.
Первые два значения в расчетах показывают ТБУ, при доходе равном 0. При этом объем продаж составляет 100 арбузов.
Можно смело говорить о прибыли мероприятия, так как значение месячной выручки больше точки безубыточности. Рассчитаем кромку безопасности:
КБден = 32 000-20 000 = 12 000 рублей,
КБ % = 12 000/32 000*100 % = 37,5 %
В данном случае уровень превышает 30 %, из чего следует утверждение, что бизнес спланирован правильно.
Что такое точка безубыточности и какие показатели нужны для её расчёта
Точка безубыточности — это такой объём продаж, при котором бизнес начинает работать «в ноль», то есть когда выручка и общие затраты равны.
Представьте, что фирма «Комфорт» делает стулья. Один стул стоит 1 000 рублей, а материалы для него обходятся в 600 рублей.
Это не значит, что с одного стула бизнесмен получит прибыль в 400 рублей, а с 10 стульев — 4 000 рублей.
Материалы для стула — это переменные затраты. Для 10 стульев их нужно на 6 000 рублей, а для 100 — на 60 000.
Переменные затраты напрямую зависят от выручки. Чем больше выручка, тем больше таких затрат, меньше выручка — меньше переменных затрат. Это затраты на материалы, сырье, закупку товаров для перепродажи, зарплату рабочих или менеджеров, если она зависит от выпуска или объёмов продаж.
Но кроме этого есть еще и постоянные затраты. У фирмы из нашего примера есть еще офис, склад и мастерская, за аренду которых нужно платить. Есть сотрудники на окладе, расходы на охрану, оргтехнику и т.д. Все это никак не зависит от выручки. Даже если фирма не продаст ни одного стула, всё равно придётся платить аренду, зарплату и т.д.
Точка безубыточности нужна для того, чтобы знать, на какую сумму нужно продать стульев, чтобы доходы полностью покрыли и постоянные, и переменные затраты, после чего бизнес начнет приносить прибыль.
Если фирма наймёт еще одного сотрудника или поднимут арендную плату, точка безубыточности сдвинется и это будет уже другая сумма.
Чтобы рассчитать точку безубыточности, понадобятся следующие показатели:
- Постоянные затраты.
- Маржинальная прибыль. Это разность между выручкой и переменными затратами. У бизнеса обязательно есть постоянные затраты, поэтому чистая прибыль всегда будет меньше, чем маржинальная.
- Рентабельность по маржинальной прибыли. Это отношение маржинальной прибыли к выручке.
Оптимизировать налоги — легко!
Максимально полные рекомендации по снижению расходов от команды специалистов сервиса «Моё дело»
Узнать подробнее
Заключение
Точка безубыточности в экономике предприятия нужна для определения критического объема производства, при котором происходит покрытие издержек валовым доходом, а при производстве или продаже каждой следующей единицы продукции (товара) компания начинает получать прибыль.
В планировании безубыточности важную роль играет маржинальный доход, который представляет собой разницу между выручкой и переменными издержками. Так как постоянные расходы и прибыль включены в состав маржинального дохода, этот показатель позволяет определить влияние именно переменных затрат на объем реализации. Организация раньше приходит в точку безубыточности при сокращении затрат, а оптимизировать переменные затраты проще, чем постоянные.
Модель точки безубыточности позволяет оценить финансовое состояние предприятия на данный момент, а также определить минимальный уровень продаж для принятия мер по увеличению рентабельности.