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

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

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

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

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

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

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

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

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

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

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



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

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

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

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

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

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

Точка безубыточности в Excel. Инструкция по нахождению точки безубыточности в Excel

Стабильный успех компании необходимо поддерживать, а для этого не обойтись без определения безопасных границ объема продаж. Эту информацию можно получить с помощью точки безубыточности. Разберемся, что это такое, в чем ее польза, и как провести вычисления инструментами Microsoft Excel.

Назначение точки безубыточности

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

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

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

Высчитать значения в нулевой точке можно с помощью этой формулы: P*X – FC – VC*X = 0. Значения переменных:

• P – стоимость продукта для покупателя;
• X – объем производимого;
• FC – постоянные издержки;
• VC – переменные затраты, которые несет компания при производстве единицы продукта.

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

X = FC/(P – VC). Постоянные издержки (FC) делятся на разность цены (P) и непостоянных расходов (VC), чтобы получилось количество продаваемого продукта, нужное для стабильности.

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

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

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

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

Помните!

При определении нулевого экономического момента берутся идеальные числа и суммы.

Выведение точки безубыточности – идеальная модель развития организации, в реальности итоги могут меняться из-за непредвиденного роста затрат или падения спроса. Рассмотрим допущения, применимые в ходе подсчета:

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

Этапы расчета точки безубыточности по А.Д. Шеремету

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

1. Получение информации о количестве производимой продукции, доходах и расходах, уровне продаж.
2. Определение постоянных и нерегулярных расходов, а после – нулевой точки и диапазона, в котором работа организации безопасна.
3. Выявление подходящего для конкретной компании количества производимого и продаваемого товара.

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

Видоизменив формулу нулевой точки, вычислим цену продукта, установив которую можно будет достигнуть нейтрального значения. Чтобы начать вычисление, нужно получить данные о постоянных потерях организации, себестоимости товара и запланированных продажах. Формула пишется так: P = (FC + VC(X))/Х. VC(X) означает, что необходимо умножить себестоимость на количество проданного товара.

Результаты в форме таблицы окажутся примерно в таком виде:

1

Известные данные выделены красным. Путем вставки их в формулу получаем количество продаваемого товара в рублях или другой валюте.

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

Самый популярный способ узнать расчета точки безубыточности, им пользуются в организациях с большим производством. Необходимо выяснить, какое количество проданного товара выведет организацию к нулевым убыткам и прибыли. Для определения этого числа применяют формулу натурального эквивалента точки безубыточности: X = FC/(P — VC).

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

2

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

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

Целевая рентабельность – это норма наценки, получаемой при продаже товара. Чтобы рассчитать необходимый объем выручки (S), нужно знать ее значение (R) и информацию о постоянных затратах (FC). Выручка – это целевой объем продаж в рублях. Формула такова: S = FC/R.

Составим таблицу с известными значениями и попробуем определить нужную для стабильности выручку. Чтобы в будущем узнать и объем продаж в натуральном эквиваленте, добавим предполагаемую цену товара. Для этого используется такая формула: Sn=S/P. Путем деления одного значения на другое получаем необходимый результат:

3

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

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

4

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

5

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

• переменные затраты = объем производства*постоянные затраты;
• общие издержки = постоянные + переменные;
• выручка = объем производства*общие затраты;
• маржинальный доход = выручка – переменные издержки;
• чистая прибыль/убыток = выручка – общие затраты.

Итоговая таблица принимает такой вид:

6

Если в результате ни одна строка не заканчивается нулем, придется провести еще несколько вычислений – выяснить значение кромки безопасности/запаса в процентах и в деньгах. Эта величина показывает, насколько компания далека от точки безубыточности. Создайте два дополнительных столбца в таблице.

Согласно формуле кромки безопасности в денежном выражении, нужно вычесть из каждого значения выручки то ее положительное значение, которое ближе всего к нулю. В упрощенном виде это пишется так: КБден = Вфакт (фактическая выручка) – Втб (выручка в точке безопасности).

Чтобы узнать процент безопасности, следует разделить значение денежной кромки безопасности на объем фактической выручки и умножить получившееся число на 100: КБ% = (КБден/Вфакт)*100%. По кромке безопасности можно с большей точностью определить точку безубыточности, как это показано на изображении ниже:

7

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

На графике визуально отражается, в какой момент прибыль становится больше убытков. Чтобы составить его, воспользуемся инструментами Excel. Для начала необходимо выбрать вкладку «Вставка» и найти на ней пункт «Диаграммы». При нажатии на кнопку с этой надписью появится список шаблонов. Выбираем точечную диаграмму – их тоже несколько, нужна схема с кривыми без резких изгибов.

8

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

9

В окне выбора данных находим кнопку «Добавить» и кликаем по ней. Она находится в левой части.

10

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

Превратить данные в график можно следующим образом: нужно кликнуть по строке «Значения X», зажать верхнюю ячейку столбца и протянуть курсор вниз до конца. То же самое делаем со строкой «Значения Y». В первом случае нужно выделить столбец «Количество товаров», во втором – «Общие затраты». Когда все поля заполнены, можно нажимать «ОК».

11

Снова нажимаем «Добавить» в окне выбора данных – появится такое же окно, как и предыдущее. Теперь имя ряда – «Общий доход». К значениям X относятся данные в ячейках столбца «Количество товаров». Поле «Значения Y» нужно заполнить, выделив столбец «Общий доход».

12

Теперь можно нажать кнопку «ОК» в окне «Выбор источника данных», тем самым закрыв его. В области диаграммы появится график с пересекающимися линиями. Место пересечения – это точка безубыточности.

13

Где нужны подробные расчеты, практика использования

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

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

Преимущества и недостатки модели точки безубыточности

Главное достоинство этой модели – ее простота. Три способа определения точки безубыточности под силу каждому, у кого на устройстве есть программа Microsoft Excel. Проблема в том, что модель условна и ограничена. На практике могут произойти неожиданные изменения в одном из показателей, из-за чего результаты расчетов можно будет считать бесполезными. Если спрос на продукцию нестабилен, невозможно определить точный объем продаж наперед. На него влияют и другие факторы – например, качество работы маркетингового отдела.

Заключение

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

Оцените качество статьи. Нам важно ваше мнение:

Финансовые отчеты. Методика анализа безубыточности

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

Алгоритм выполнения анализа безубыточности

1. Анализ безубыточности выполняется из плана «отчет по продуктам». Для этого нужно выбрать период, для которого будет выполняться анализ.

2. В главном меню выберите «отчёты → экспорт в Excel → и пункт — детализированные отчеты«.

3. В открывшейся форме выберите период, для которого будет выполнен анализ и нажмите OK, и анализ безубыточности будет построен в новой книге Excel. В одной вкладке будет построена таблица безубыточности, а в другой –график безубыточности.

☛ Важно! Для выполнения анализа безубыточности в таблице «отчет по продуктам», в выбранном периоде, должны быть представлены данные о продажах (объем, цена, стоимость), а также – переменные постоянные издержки. Если в таблице недостаточно данных, например, не указаны постоянные издержки, анализ безубыточности выполняться не будет.

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

Также смотрите далее в этом разделе пункт «Методика расчета анализа безубыточности».

Маржинальный анализ себестоимости и прибыли

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

Все затраты организации можно разделить по отношению к объему производства на условно-переменные и условно-постоянные. Разделение затрат на постоянные и переменные традиционно используется в системы директ-костинг (см. Раздел справки «Метод учета затрат при расчете себестоимости»).

Программа генерирует часть прямых затрат из уже рассчитанных планов (план продаж, план закупок и нормативных затрат). При это, важно понимать, другую часть затрат по выбранному продукту программа не сможет определить, если предварительно они (затраты) не будут разнесены по продуктам. Иными словами, прежде, чем выполнять маржинальный анализ, нужно разнести постоянные затраты и затраты на ресурсы (из диаграммы Ганта) на конкретные продукты. Это делается очень просто, в формах редактирования нужно указать лишь процент, который будет отнесен к конкретному продукту (см. Разделы справки «Разнесение издержек плана производства по продуктам» и «Разнесение издержек плана постоянных затрат по продуктам»). Именно этот метод разнесения издержек (указание процента) позволит выполнить полный маржинальный анализ – по каждому продукту и в каждом периоде.

Выполнение расчета «отчет по продуктам»

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

 

Таблица «отчет по продуктам»

Структура и содержание таблицы «отчет по продуктам»:

№	Название	Исходные и расчетные данные
01	Объем продаж, ед.	Количество продаж продукта, ед. (Таблица планирования продаж → План продаж)
02	Цена продаж	Цена продаж, с НДС (Таблица планирования продаж → План продаж)
03	Поступления от продаж	Поступления от продаж, с НДС (Таблица планирования продаж → План продаж)
04	В том числе -НДС	НДС, включенный в продукт (Таблица планирования продаж → План продаж)
05	Выручка от реализации	Чистая выручка (без НДС) от реализации за период (Таблица планирования продаж → План продаж)
06	Материальные расходы	Нормативные издержки (План закупок и нормативных затрат → Расчет расхода) + Стоимость ресурсов или этапов (План затрат на реализацию этапов производства)
07	Прямые затраты на оплату труда	Нормативные издержки (План закупок и нормативных затрат) + Стоимость ресурсов или этапов (План затрат на реализацию этапов производства)
08	Прочие расходы	Нормативные издержки (План закупок и нормативных затрат) + Стоимость ресурсов или этапов (План затрат на реализацию этапов производства)
09	Итого переменные издержки	= [06] + [07] + [08]
10	Коммерческие расходы	Расчет постоянных затрат (План постоянных затрат)
11	Административные расходы	Расчет постоянных затрат (План постоянных затрат)
12	Заработная плата сотрудников	Заработная плата [в составе постоянных затрат] (План постоянных затрат)
13	Прочие расходы	Расчет постоянных затрат (План постоянных затрат)
14	Итого постоянные издержки	= [10] + [11] + [12] + [13]
15	Себестоимость продукта	= [09] + [14]
16	Операционная (маржинальная) прибыль	= [05] — [15]

Фрагмент результата расчета по продукту:

 

Анализ безубыточности

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

Программа создаст файл Excel, в котором построит таблицу безубыточности и график точки безубыточности для выбранного периода.  Процедура анализа безубыточности вызывается из главного меню: «Главное меню → Отчеты → Экспорт в Excel → Детализированные отчеты«. После чего на экране появится окно, в котором нужно выбрать период: год, квартал или конкретный месяц.

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

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

1. шаг. Перейдем на страницу «Отчет по продуктам» с помощью меню навигации (стрелки или список планов).
2. шаг. Сделаем расчет расходов и доходов по продукту: кликнем курсором мыши по активной ссылке, выберем продукт из списка и нажмем «ОК».


3. шаг. Вызовем процедуру расчета анализа безубыточности: «Главное меню → Отчеты → Экспорт в Excel → Детализированные отчеты«.
4. шаг. Выберем период из списка, как показано на рисунке, и нажмем «ОК»:

 

В результате, программа создала файл экземпляра Excel с двумя страницами: «График безубыточности» (см. рисунок) и «Таблица безубыточности».

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

 

Переключайтесь между книгами Excel с помощью клавиатуры

☛ Воспользуйтесь переключением между книгами Excel с помощью клавиатуры (Ctrl + Tab), если в процессе выполнения экспорта данных или открытия файла Excel, окно проекта «Budget-Plan Express» стало не доступным.

При выполнении экспорта в Excel создается новый экземпляр книги Excel, то есть файл должен открываться в новом окне. Однако, в некоторых установках или в устаревших версиях Excel (например, в 2010) для открытия файлов в новых окнах, необходимо изменить настройки в реестре Windows. В большинстве случаях, этот недостаток считают недоработкой старых (Excel 2010 и более ранних) версий.

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

1. Ctrl + Tab или Ctrl + F6 – переключение между активными книгами в прямом порядке;
2. Ctrl+Shift+Tab или Ctrl+Shift+F6 – переключение между всеми активными книгами в обратном порядке.

Методика расчета анализа безубыточности

Анализ безубыточности (Break-Even Analysis), достаточно популярный метод, который позволяет компании принять решение относительно того, сколько необходимо произвести и реализовать продукции, чтобы полностью покрыть свои постоянные и переменные затраты. Периодическое проведение анализа безубыточности дает возможность компании принимать решения, позволяющие сохранить определенный уровень рентабельности и обосновать возможности устойчивого расширения бизнеса.  Анализ безубыточности предполагает использование простого набора основных показателей деятельности компании.

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

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

Точка безубыточности = Постоянные расходы за весь период / Маржинальная прибыль на единицу

Где периодом расчета точки безубыточности может быть год, квартал или месяц .
Маржинальная прибыль (Marginal Revenue) на единицу, рассчитывается по формуле:

Маржинальная прибыль на единицу = P — VC

Где:

P — средневзвешенная чистая цена (без НДС) в периоде, рассчитывается путем деления чистой выручки за период на количество продаж за период.

VC — переменные затраты (Variable Costs) на единицу продукции, рассчитываются – как переменные затраты за весь период (год, квартал или месяц), отнесенные на количество продаж в периоде.

Пример. Воспользуемся данными предыдущего примера и рассчитаем точку безубыточности за январь 2015, для продукта Сыр твердый «Российский».

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

№	Статья	Данные
[01]	Объем продаж, ед.	42 000
[05]	Выручка от продаж (без НДС)	8 542 373
[09]	Итого переменные издержки	6 695 060
[14]	Итого постоянные издержки	231 727



1. шаг. Посчитаем средневзвешенную чистую цену (без НДС) за период: [05] / [01] = 8 542 373 / 42 000 = 203,39
2. шаг. Посчитаем переменные затраты на единицу продукции за период: [09] / [01] = 6 695 060 / 42 000 = 159,41
3. шаг. Посчитаем маржинальную прибыль на единицу продукции: средневзвешенная чистая цена (шаг 1) — Переменные затраты на единицу (шаг 2) = 203,39 — 159,41 = 44,98
4. шаг. Наконец, посчитаем точку безубыточности: [14] / маржинальную прибыль на единицу (шаг 3) = 231 727 / 44,98 = 5 268,92 ≈ 5 269

Чтобы построить график, нужна таблица, где затраты отражаются пропорционально масштабу точки безубыточности: пусть BE — точка безубыточности, тогда в первой колонке строки «Объем продаж, ед.» должно быть значение 1/4 × BE, во второй — 2/4 × BE, во третьей — 3/4 × BE, в четвертой — 4/4 × BE = BE, в пятой — 5/4 × BE. Также, пропорционально рассчитываются строки «выручка от продаж» и «переменные издержки»: Выручка от продаж = n × BE × Средневзвешенную цену (шаг 1), Переменные издержки = n × BE × Переменные затраты на единицу (шаг 2).

Фрагмент страницы «Таблица безубыточности»:

 

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

Фрагмент страницы «График безубыточности»:

 

По вертикали расположена шкала «сумм», по горизонтали — шкала «единиц продаж». Точка безубыточности – это точка пересечения выручки от продаж и суммарных издержек (соответственно, красная и синяя линии). [Суммарные издержки] = [переменные] + [постоянные издержки] (соответственно, желтая и голубая линии). Проекция точки безубыточности на шкалу «сумм» показывает выручку от продаж, при которой окупаемость равна нулю. Проекция точки безубыточности на шкалу «количество» показывает тот объем продаж, при котором окупаемость равна нулю. Значения, расположенные выше точки безубыточности, между линиями выручки и суммарными издержками, находятся в зоне прибыли.

Применяемые и рекомендуемые процедуры и функции

1. Если вы удалили часть данных по ошибке или выполнили действие, наносящее ущерб данным, нужно выйти из программы без сохранения: «Главное меню → Файл проекта → Выход без сохранения«.


2. Если вы завершаете работу с программой, сохраните изменения: «Главное меню → Файл проекта → Сохранить» (или Ctrl+S). Или используйте выход из программы с сохранением.


3. Периодически выполняйте резервное копирование данных: «Главное меню → Файл проекта → Сохранение и резервирование» или Ctrl+Shift+S. Файл проекта сохраниться в папке «backup», имя проекта будет отображать дату и время сохранения. Для восстановления файла, просто скопируйте его в системную папку («BPlanExpress»).


4. Если у вас несколько проектов (или вариантов расчета) создайте файл-шаблон, и, начиная новый проект, открывайте файл-шаблон, в котором сохранены ваши настройки или предварительные расчеты проекта, выберите пункт меню: «Главное меню → Файл проекта → Создать новый проект«. Это позволит сократить время разработки проектов.
☛ Заметьте, новый проект создается в системной папке («BPlanExpress»), которая открывается по умолчанию.
5. Выполнить итоговый расчет: «Главное меню → Данные → Рассчитать«. Тоже самое – F9.


6. Очистить таблицу (обнулить значения): «Главное меню → Данные → Очистить таблицу«.


7. Удалить таблицу (удалить все данные и параметры настроек таблицы): «Главное меню → Данные → Удалить таблицу«.


8. Очистить строку (обнулить значения): «Контекстное меню (правая кнопка мыши) → Очистить строку.«


9. Транслит: «Главное меню → Настройки → Транслит текста таблиц«. Выберите язык перевода.


10. Экспорт: «Главное меню → Отчеты → Экспорт в Excel«. Там же – экспорт в Word.


11. Финансовый и инвестиционный анализ. Настройки параметров показателей эффективности проекта: «Главное меню → Настройки → Инвестиционный анализ«.


12. Быстрый график: «Контекстное меню (правая кнопка мыши) → График строки.» Перед вызовом, установите курсор в любом месте строки с нужными данными. Смотрите также раздел «Подготовка отчетов и бизнес-планов. Графики».

---

Справка о программе «Budget-Plan Express», www.strategic-line.ru | Содержание справки

Поделиться:

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

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

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

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

, где Маржа вклада для продукта (или услуги) — это цена за вычетом различные цены.

Например, первая компания имеет следующие оценки в своем бизнес-плане:

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

Итак, чтобы покрыть все постоянные затраты, первые менеджеры компании должны продать более 6,667 единиц продукта или привлечь к услуге 6,667 клиентов.

Чтобы создать график для BEP в Excel, выполните следующие действия:

• Создание диаграммы выручки, фиксированных, переменных и общих затрат
• Добавить точку безубыточности
• Добавьте линии точки безубыточности

Создание диаграммы выручки, фиксированных, переменных и общих затрат

1. Подготовьте данные для диаграммы:

Для этого примера создайте новую таблицу данных:

где:

• the Постоянные затраты значения постоянны и равны C3 ( = 3 канадских доллара ),
• the Переменные затраты значения = Средние переменные затраты * Единицы ( = 4 канадских доллара * A11 ),
• Общие затраты значения = Постоянные затраты + Переменные затраты ( = B11 + C11 ),
• Выручка значений = Цена продажи единицы * Единицы ( = 5 канадских долларов * A11 ).

2. Выберите диапазон данных о затратах и ​​доходах (в этом примере A10: E20 ).

3. На вкладке Insert в группе Charts , щелкните раскрывающийся список Insert Scatter (X, Y) или Bubble Chart :

В раскрывающемся списке Insert Scatter (X, Y) или Bubble Chart выберите Точечная диаграмма , которую вы предпочитаете, например Точечная диаграмма с прямой линией .

Excel создает новую диаграмму для данных:

4. Внесите любые другие необходимые изменения:

Добавить точку безубыточности

5. Добавьте новые данные:

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

• Точка безубыточности в объеме продаж была рассчитана выше ( = C8 ),
• Точка безубыточности в долларах продаж может быть рассчитана по следующей формуле:

Для этого примера:

6. Добавьте новую серию данных в диаграмму:

6.1. Выполните одно из следующих действий:

• В разделе Инструменты диаграммы на вкладке Design в группе Data выберите Select Data :
• Щелкните правой кнопкой мыши в области диаграммы и выберите Выбрать данные … во всплывающем меню:

6.2. В диалоговом окне Select Data Source , нажмите кнопку Добавить .

6.3. В диалоговом окне Редактировать серию c

Анализ точки безубыточности | Формула | Калькулятор

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

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

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

---

Формула

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

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

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

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

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

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

---

Пример

Давайте посмотрим на пример каждой из этих формул. Барбара — старший бухгалтер, отвечающий за производственные линии и цепочки поставок большой мебельной фабрики. Она не уверена, что модели диванов текущего года принесут прибыль и что измерить количество единиц, которые им придется произвести и продать, чтобы покрыть свои расходы и получить прибыль в размере 500 000 долларов. Вот статистика производства.

• Общие постоянные расходы: 500 000 долл. США
• Переменные затраты на единицу: 300 $
• Цена продажи за единицу: $ 500
• Желаемая прибыль: 200 000 долларов США

Сначала нам нужно рассчитать точку безубыточности на единицу, поэтому мы разделим 500 000 долларов фиксированных затрат на маржу вклада 200 долларов на единицу (500–300 долларов).

Как видите, фабрике Барбары придется продать не менее 2 500 единиц, чтобы покрыть постоянные и переменные затраты. Все, что он продает после отметки 2,500, пойдет прямо в CM, поскольку фиксированные расходы уже покрыты.

Затем Барбара может перевести количество единиц в общий объем продаж, умножив 2500 единиц на общую продажную цену каждой единицы в 500 долларов.

Теперь Барбара может вернуться к совету директоров и сказать, что компания должна продать не менее 2500 единиц или эквивалент 1 250 000 долларов, прежде чем будет получена какая-либо прибыль.Она также может пойти дальше и с помощью калькулятора точки безубыточности вычислить общее количество единиц, которое должно быть произведено, чтобы достичь своей цели рентабельности в 200000 долларов, разделив желаемую прибыль 200000 долларов на маржу вклада, а затем сложив общее количество. единиц точки безубыточности.

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

---

Анализ

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

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

Одним из важнейших понятий здесь является запас прочности. Это разница между количеством единиц, необходимых для достижения цели по прибыли, и количеством единиц, которые необходимо продать, чтобы покрыть расходы. В нашем примере Барбаре пришлось произвести и продать 2500 единиц, чтобы покрыть расходы фабрики, и пришлось произвести 3500 единиц, чтобы достичь своих целей по прибыли.Этот спред в 1000 единиц — это запас прочности. Это объем продаж, который компания может позволить себе потерять, но при этом покрывает свои расходы.

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

---

Найдите, пометьте и выделите определенную точку данных на точечной диаграмме Excel

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

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

Исходные данные

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

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

Как вы знаете, на диаграмме рассеяния коррелированные переменные объединены в одну точку данных. Это означает, что нам нужно получить значения x ( Advertising ) и y ( Items sold ) для интересующей точки данных. А вот как их можно извлечь:

1. Введите текстовую метку точки в отдельной ячейке.В нашем случае пусть в ячейке E2 будет май месяц. Важно, чтобы вы вводили метку точно так, как она отображается в исходной таблице.
2. В F2 вставьте следующую формулу ВПР для извлечения количества проданных товаров за целевой месяц:

   = ВПР ($ E $ 2, $ A $ 2: $ C $ 13,2, ЛОЖЬ)

3. В G2 извлеките стоимость рекламы за целевой месяц по следующей формуле:

   = ВПР ($ E $ 2, $ A $ 2: $ C $ 13,3, ЛОЖЬ)

   На этом этапе ваши данные должны выглядеть примерно так:
   

Добавить новую серию данных для точки данных

Когда исходные данные готовы, давайте создадим средство обнаружения точек данных.Для этого нам нужно будет добавить новую серию данных в нашу точечную диаграмму Excel:

1. Щелкните правой кнопкой мыши любую ось на диаграмме и выберите Выбрать данные… .
   
2. В диалоговом окне Select Data Source нажмите кнопку Add .
   
3. В окне Edit Series выполните следующие действия:
   • Введите значащее имя в поле Название серии , например Целевой месяц .
   • В качестве значения серии X выберите для точки данных независимую переменную .В этом примере это F2 (Реклама).
   • В качестве значения Y для серии выберите зависимый В нашем случае это G2 (Проданные товары).
4. По завершении нажмите ОК .

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

Конечно, поскольку серия графиков обновляется автоматически, выделенная точка изменится, как только вы введете другое имя в ячейку Целевой месяц (E2).

Настроить целевую точку данных

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

Изменить внешний вид точки данных

Для начала поэкспериментируем с цветами. Выберите эту выделенную точку данных, щелкните ее правой кнопкой мыши и выберите Форматировать ряд данных… в контекстном меню. При этом убедитесь, что выбрана только одна точка данных:

На панели Формат Data Series перейдите к Fill & Line > Marker и выберите любой цвет для маркера Fill и Border .Например:

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

Добавьте метку точки данных

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

1. Щелкните выделенную точку данных, чтобы выбрать ее.
2. Нажмите кнопку Элементы диаграммы .
3. Выберите поле Data Labels и выберите, где разместить метку.
   
4. По умолчанию Excel показывает одно числовое значение для метки, в нашем случае значение y. Чтобы отобразить значения x и y, щелкните правой кнопкой мыши метку, выберите Форматировать метки данных… , выберите поля X Value и Y value и установите разделитель по вашему выбору:
   

Обозначьте точку данных именем

В дополнение к значениям x и y или вместо них на этикетке можно указать название месяца.Для этого установите флажок Value From Cell на панели Format Data Labels , нажмите кнопку Select Range… и выберите соответствующую ячейку на листе, E2 в нашем случае:

Если вы хотите, чтобы на этикетке отображалось только название месяца, снимите флажки X Value и Y Value .

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

Определите положение точки данных по осям x и y

Для лучшей читаемости вы можете отметить положение важной для вас точки данных на осях x и y.Вот что вам нужно сделать:

1. Выберите целевую точку данных на диаграмме.
2. Нажмите кнопку Элементы диаграммы > Полосы ошибок > Процент .
   
3. Щелкните правой кнопкой мыши горизонтальную полосу погрешностей и выберите во всплывающем меню «Полосы ошибок формата… ».
   
4. На панели панели ошибок формата перейдите на вкладку Параметры панели ошибок и измените направление на минус и в процентах на 100 :
   
5. Щелкните вертикальную полосу ошибок и выполните ту же настройку.

   В результате горизонтальные и вертикальные линии будут проходить от выделенной точки до осей y и x соответственно:
   

6. Наконец, вы можете изменить цвет и стиль полосок ошибок, чтобы они лучше соответствовали цветам вашей диаграммы. Для этого перейдите на вкладку Fill & Line панели Format Error Bars и выберите желаемый Color и Dash type для текущей выбранной полосы ошибок (вертикальной или горизонтальной).Затем сделайте то же самое для другой панели ошибок:
   

И вот последняя версия нашего точечного графика с выделенной целевой точкой данных, помеченной и размещенной на осях:

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

Показать позицию средней или контрольной точки

Тот же метод можно использовать для выделения средней, контрольной, наименьшей (минимальной) или высшей (максимальной) точки на диаграмме разброса.

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

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

Вас также может заинтересовать

Заполнить ряды ниже или между рядами в XY-диаграмме Excel

Затенение между нанесенными линиями светлым цветом может улучшить некоторые диаграммы. Затенение может помочь указать целевой диапазон для данных.

Я писал более ранние руководства по этой теме, но мне пришлось изменить последовательность шагов в протоколе, потому что более поздние версии Excel не были такими гибкими в отношении порядка операций, как Excel 2003 (RIP).

Комбинированные диаграммы XY-области

Недавно в разделе «Фон затененного квадранта для диаграммы XY-точечной диаграммы Excel» я показал, как создать фоновую сетку из цветных прямоугольников.

Этот метод позволяет нанести данные диаграммы XY на первичные оси, а данные диаграммы с областями — на вторичные оси. Он также воспользовался трюком с использованием оси категорий диаграммы с областями (или строками, или столбцами): при использовании в качестве оси дат точки с одинаковой датой отображаются на одной и той же вертикальной линии, что позволяет отображать смежные цветные области. разделены как вертикальными, так и горизонтальными линиями.

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

Fill Under One XY серии

Начнем с простого случая заливки цветом под графиком XY. Простые данные, простая диаграмма типа Scatter с прямыми линиями и маркерами.

Нам нужно выполнить некоторые вычисления, чтобы получить данные для серии диаграмм с областями (см. Ниже). Минимальные и максимальные значения X для оси X диаграммы XY вводятся в B11 и B12 (это автоматические пределы шкалы Excel на диаграмме XY выше).Чтобы обеспечить разумное разрешение, мы масштабируем данные X диаграммы с областями от 0 до 1000. Формулы для значений X диаграммы с областями в столбце D представляют собой простые формулы интерполяции для правильного изменения масштаба данных.

Значения X расширяются так, что первое вычисленное значение X в D5 повторяется в D4, а ноль помещается в D3; также последнее вычисленное значение X в D9 повторяется в D10, а X max, равное 1000, вводится в D11. Эти повторяющиеся значения X будут обеспечивать вертикальные края заштрихованной области.

Формулы для значений Y диаграммы с областями в столбце E предоставляют одинаковые значения Y для обеих серий диаграмм. Значения Y расширены двумя ячейками над и под вычислениями, которые содержат нули.

Диаграмма уже содержала данные XY в первых двух столбцах диапазона данных (B4: C9). Скопируйте следующие два столбца (D2: E11), выберите диаграмму, затем щелкните раскрывающееся меню «Вставить» на вкладке «Главная» и выберите «Специальная вставка» (внизу слева). Появится диалоговое окно «Специальная вставка»; убедитесь, что выбраны такие же настройки, как показано ниже справа.

Вот расчетные данные и диаграмма. Добавленная серия представляет собой тип диаграммы XY, как и первая. Пока не меняйте.

Выберите только что добавленную серию, нажмите Ctrl + 1 (цифра один), чтобы открыть панель задач «Форматировать серию» (Excel 2013) или диалоговое окно «Форматировать серию» (2010 или 2007), и в разделе «Параметры серии» выберите «Вторичная ось» (внизу слева). Excel добавляет дополнительную вертикальную ось вдоль правого края диаграммы (внизу справа).

Excel добавляет дополнительную вертикальную ось вдоль правого края диаграммы.

В Excel 2013 щелкните значок плюса рядом с диаграммой, щелкните стрелку вправо рядом с осями и установите флажок «Вторичный горизонтальный». В Excel 2010 или 2007 вам нужно будет подняться на вкладку «Работа с диаграммами»> «Макет», щелкнуть раскрывающийся список «Оси», «Вторичный горизонтальный» и, наконец, нажать «Показать ось слева направо».

Теперь все оси присутствуют и учтены.

Щелкните правой кнопкой мыши серию «Область» (которая все еще имеет тип XY) и выберите «Изменить тип диаграммы серии».В Excel 2013 откроется диалоговое окно «Изменить тип диаграммы». Щелкните раскрывающийся список Тип диаграммы в строке ряда областей и выберите Область или Область с накоплением (не имеет значения, что в данном случае, поскольку существует только одна серия областей). На самом деле вы могли бы переключить серию областей на вторичную ось в этом диалоговом окне (если вы это сделаете до изменения типа диаграммы), но я обычно забываю, что это стало настолько простым.

В Excel 2007 и 2010 выберите «Площадь» или «Область с накоплением» во всплывающем окне.

Теперь серия преобразована в серию площадей.

Выберите вторичную горизонтальную ось (вверху диаграммы) и нажмите Ctrl + 1, чтобы открыть панель задач оси формата или диалоговое окно оси формата. В разделе «Тип оси» выберите «Ось даты» (внизу слева). Возможно, вам также придется изменить базовую единицу оси на дни.

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

Удалите дополнительную вертикальную ось (правый край диаграммы) и дополнительную горизонтальную ось (верх диаграммы). Заливка переместится в положение под графиком XY.

Заливка между двумя сериями XY

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

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

Формулы для вычисления значений X и Y диаграммы с областями аналогичны тем, которые используются в более простом примере выше. Обратите внимание, что данные Bottom Area совпадают с данными Bottom Line, а данные Delta Fill — это разница между данными Top Line и Bottom Line.

Скопируйте данные диаграммы с областями (E2: G11), выберите диаграмму, затем щелкните раскрывающееся меню «Вставить» на вкладке «Главная», выберите «Специальная вставка» и убедитесь в правильности настроек.Новые данные добавляются в виде дополнительных серий XY, и это нормально.

Выберите одну из серий данных Area (все еще отображенных как XY), нажмите Ctrl + 1, чтобы открыть панель задач или диалоговое окно Format Series, и выберите Secondary Axis. Excel показывает нам вторичную вертикальную ось вдоль правого края диаграммы. Повторите эти действия для других серий данных по площади.

В Excel 2013 щелкните значок плюса рядом с диаграммой, щелкните стрелку вправо рядом с осями и установите флажок «Вторичный горизонтальный».В Excel 2010 или 2007 вам нужно будет подняться на вкладку «Работа с диаграммами»> «Макет», щелкнуть раскрывающееся меню «Оси», нажать «Вторичный горизонтальный» и, наконец, нажать «Рисовать слева направо».

Щелкните правой кнопкой мыши серию «Область» (которая все еще имеет тип XY) и выберите «Изменить тип диаграммы серии». В Excel 2013 откроется диалоговое окно «Изменить тип диаграммы». Щелкните раскрывающееся меню Тип диаграммы в каждой строке серии «Площадь» и выберите «Область с накоплением». Вы могли бы переключить ряд областей на вторичную ось в этом диалоговом окне (если вы это сделаете до изменения типа диаграммы).

В Excel 2007 и 2010 выберите «Площадь» или «Область с накоплением» во всплывающем окне. График, вероятно, сейчас выглядит так, будто он сломан, но не паникуйте. Щелкните правой кнопкой мыши другую серию области, выберите «Изменить тип диаграммы серии» и снова выберите «Область с накоплением».

Выберите вторичную горизонтальную ось (вверху диаграммы) и нажмите Ctrl + 1, чтобы открыть панель задач оси формата или диалоговое окно оси формата. В разделе «Тип оси» выберите «Ось даты». Серии диаграммы с областями теперь идеально выровнены с сериями XY.Поскольку они нанесены на вспомогательную ось, ряды нижней области заполняют пространство между вспомогательной горизонтальной осью в верхней части диаграммы и точками данных нижней линии. Так что это выше данных, а не ниже. Область Delta Fill заполнила пространство между линиями XY.

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

Отформатируйте серию «Нижняя область», чтобы в ней не было заливки.

Наконец, удалите легенду.

Заливка между перекрывающимися областями

Этот подход можно расширить еще больше. Предположим, мы хотим заполнить между двумя парами линий. Следующие простые данные имеют четыре ряда, min и max для набора A и min и max для набора B. Мы хотим заполнить между Amin и Amax и между Bmin и Bmax. Если эти области совпадают, мы хотели бы видеть и то, и другое.

Мы можем настроить данные диаграммы с областями ниже, где у нас есть область под Amin («Нижняя часть»), одна между Amax и Amin («Afill»), одна между Bmin и Amax («Blank») и одна между Bmax. и Bmin («Bfill»).«Пустая» область между Bmin и Amax простирается от Bmin до Amax, если Bmin больше, или от Bmin UP до Amax, если Bmin меньше.

Я не буду повторять всю процедуру, потому что это действительно то же самое, что и заполнение между двумя строками выше. Линии показаны слева, A — синим, B — оранжевым. Готовые перекрывающиеся закрашенные области показаны справа, снова A синим цветом и B оранжевым. Я использовал настройку прозрачности 50%, чтобы мы могли видеть линии сетки и другие данные за заливкой области.

Четыре диаграммы ниже показывают каждую область по очереди, а остальные скрыты. Сначала «Bottom» заполняется от оси до Amin, затем «Afill» заполняется от Amin до Amax, «Blank» заполняется от Amax вверх (или вниз) до Bmin, и, наконец, «Bfill» заполняется от Bmin до Bmax.

Заливка под нанесенной линией: стандартная нормальная кривая

Пример этой техники можно найти в другом написанном мною руководстве «Заливка под нанесенной линией: стандартная нормальная кривая».

График

Box and Whisker в Excel

График простой коробки и усов | Выбросы | Расчет коробчатой ​​диаграммы

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

График с простой коробкой и усами

1. Например, выберите диапазон A1: A7.

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

2. На вкладке «Вставка» в группе «Диаграммы» щелкните символ «Диаграмма статистики».

3. Щелкните Box and Whisker .

Результат:

Пояснение: средняя линия прямоугольника представляет собой медианное или среднее число (8). X в рамке представляет собой среднее значение (также 8 в этом примере). Медиана делит набор данных на нижнюю половину {2, 4, 5} и верхнюю половину {10, 12, 15}. Нижняя линия прямоугольника представляет собой медианное значение нижней половины или 1-го квартиля (4). Верхняя линия прямоугольника представляет собой медианное значение верхней половины или 3-го квартиля (12). Усы (вертикальные линии) проходят от краев прямоугольника до минимального значения (2) и максимального значения (15).

Выбросы

1. Например, выберите диапазон A1: A11.

Примечание: среднее или среднее число (8) делит набор данных на две половины: {1, 2, 2, 4, 5} и {10, 12, 15, 18, 35}. Первый квартиль (Q ₁ ) — это медиана первой половины. Q ₁ = 2. 3-й квартиль (Q ₃ ) — это медиана второй половины. Q ₃ = 15.

2. На вкладке «Вставка» в группе «Диаграммы» щелкните символ «Диаграмма статистики».

3.Щелкните Box and Whisker.

Результат:

Пояснение: межквартильный размах (IQR) определяется как расстояние между 1-м квартилем и 3-м квартилем. В этом примере IQR = Q ₃ — Q ₁ = 15 — 2 = 13. Точка данных считается выбросом, если она превышает расстояние, в 1,5 раза превышающее IQR ниже 1-го квартиля (Q ₁ — 1,5 * IQR = 2 — 1,5 * 13 = -17,5) или в 1,5 раза больше IQR выше 3-го квартиля (Q ₃ + 1.5 * IQR = 15 + 1,5 * 13 = 34,5). Поэтому в этом примере 35 считается выбросом. В результате верхний ус достигает наибольшего значения (18) в этом диапазоне.

4. Измените последнюю точку данных на 34.

Результат:

Пояснение: все точки данных находятся в диапазоне от -17,5 до 34,5. В результате усы расширяются до минимального значения (2) и максимального значения (34).

Расчеты коробчатой ​​диаграммы

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

1. Например, выберите ниже четное количество точек данных.

2. На вкладке «Вставка» в группе «Диаграммы» щелкните символ «Диаграмма статистики».

3. Щелкните Box and Whisker.

Результат:

Объяснение: Excel использует функцию КВАРТИЛЬ.ИСКЛ для вычисления 1-го квартиля (Q ₁ ), 2-го квартиля (Q ₂ или медиана) и 3-го квартиля (Q ₃ ). Эта функция интерполирует между двумя значениями для вычисления квартиля.В этом примере n = 8 (количество точек данных).

4. Q ₁ = 1/4 * (n + 1) -ое значение = 1/4 * (8 + 1) -ое значение = 2 1/4 значение = 4 + 1/4 * (5-4) = 4 1/4. Вы можете проверить это число, используя функцию КВАРТИЛЬ.ИСКЛ или посмотрев на график прямоугольников и усов.

5. Q ₂ = 1/2 * (n + 1) -ое значение = 1/2 * (8 + 1) -ое значение = 4 1/2-е значение = 8 + 1/2 * (10-8) = 9. В этом есть смысл, медиана — это среднее двух средних чисел.

6. Q ₃ = 3/4 * (n + 1) -ое значение = 3/4 * (8 + 1) -ое значение = 6 3/4 значение = 12 + 3/4 * (15-12) = 14 1/4.Опять же, вы можете проверить это число, используя функцию КВАРТИЛЬ.ИСКЛ или посмотрев на график с квадратами и усами.

Как создать линейный график в Excel: пошаговое объяснение

Из всех визуальных элементов в Excel, зачем выбирать линейный график? Множество других диаграмм показывают ту же информацию, так почему бы не использовать их?

Вот несколько существенных преимуществ линейных графиков:

1. Они очень хорошо показывают изменения во времени.

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

Есть и другие полезные применения линейных диаграмм, но отображение значения с течением времени — один из лучших.

2. Они позволяют легко просматривать несколько наборов данных.

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

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

3. Легко читается.

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

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

4. Дополнительную информацию легко увидеть.