Анализ безубыточности в среде Excel Исполнитель: студентка III курса ВЗФЭИ специальности ФК (бюджет) Савушкина Елена Владимировна Руководитель: доцент, к.т.н. Уродовских Виктор Николаевич

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

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

Постановка задачи. Предприятие планирует начать выпуск n новых изделий И 1, И 2,..., И n. Для выпуска изделий каждого типа необходимы начальные вложения в 1, в 2,..., в n соответственно, которые составляют постоянные затраты, не зависящие от количества выпускаемых в будущем изделий (например, покупка, установка и наладка оборудования, отладка технологических процессов, обучение персонала и пр.) При этом предварительно рассчитываются затраты (себестоимость) з 1, з 2,..., з n на произ- водство одной единицы продукции, и удельные прибыли (или цены продажи) с 1, с 2,..., с n по каждому виду изделия И 1, И 2,..., И n. На основе известный данных требуется провести анализ безубыточности. Далее возможны два варианта расчета.

Если предприятие спланировало объемы вы- пуска х 1, х 2,..., х n штук изделий каждого вида за месяц (квартал или год), то уравнение безубыточ- ности, как равенство доходов и общих затрат за k временных периодов примет следующий вид: kc i x i = k з i x i + в о + в i. Тогда можно рассчитать k - срок достижения точки безубыточности: k = (в о + в i ) / (с i – з i )х i. Здесь k з i x i + в о + в i – общие затраты или финансовые вложения, которые необходимо сделать для достижения точки безубыточности, k з i x i - переменные затраты на производство продукции. Или Общие = переменные + постоянные затраты затраты затраты. затраты затраты затраты.

Чаще решается задача определения плана выпуска х 1, х 2,..., х n, при котором достигается точка безубыточности, тогда значение k в уравнение безубыточности не входит, а само уравнение безубыточности имеет вид: c i x i = з i x i + в о + в i, или в виде (c i - з i ) x i = в о + в i. Это уравнение имеет единственное решение в однопродуктовых задачах и бесчисленное множество – в многопродуктовых. В многопродуктовые задачи кроме уравнения безубыточности, вводятся дополнительные условия, например, условие минимума переменных затрат до достижения точки безубыточности, или условие максимума прибыли, полученной в срок до достижения точки безубыточности. Во втором случае уменьшается срок достижения точки безубыточности, но увеличиваются финансовые затраты, которые необходимо сделать для достижения этой точки. После определения оптимального производственного плана вычисляется срок достижения точки безубыточности, как срок выполнения этого плана.

Построим математическую модель определе- ния оптимального производственного плана для достижения точки безубыточности. Если производственный план вычисляется из условия минимума переменных затрат, то целевая функция минимизируется: min z = з i x i. Если производственный план вычисляется из условия максимума прибыли, то целевая функция максимизируется: max z = c i x i. В качестве ограничения вводится уравнение безубыточности (c i - з i ) x i = в о + в i. Реальные задачи могут содержать дополни- тельные условия (ограничения), например, ограни- ченности производственных мощностей, ресурсов, либо потребностей рынка.

Пример. Анализ безубыточности нового производства с помощью ЗЛП Компания планирует начать производство трех новых видов продукции – изделий А, Б и В. Данные о соответствующих затратах и доходах на первый плановый период производства представлены в таблице. Изделия Планируем ая цена за ед. продукции, тыс.руб. Переменные затраты на производство ед. продукции, тыс. руб. Постоянны е затраты, тыс. руб. АБВАБВ

ВИз таблицы видны значительные фиксированные затраты на организацию производства каждого вида продукции, при отсутствии общих затрат в о. Фиксированные затраты – это расходы, которые не зависят от количество производимой продукции. Цель задачи – определить план производства, при котором компания выйдет на точку безубыточности, при условии, что она уже заключила контракты на поставку 310 штук изделий А и 200 штук изделий В, и руководство заинтересовано в обязательном выполнении этих контрактов. Маркетинговые исследования рынка, показали, что рынок может «поглотить» не более 350 штук изделий Б.

Составляем ЭММ задачи. Обозначим через х А, х Б, х В - количество производимых изделий типа А, Б и В. 1. Запишем первое ограничение в виде уравнения безубыточности 15х А +12х Б +18х В =8х А +7х Б +10х В х А +5х Б +8х В = 4400 и преобразуем его к виду 7х А +5х Б +8х В = Запишем два ограничения по контрактам х А 310, х В 200, х В 200, 3. Запишем «маркетинговое» ограничение х Б 350. х Б 350. х Б Зададим условие неотрицательности только для переменной х Б : х Б 0.

В этой модели имеется две целевые функции. Первая целевая функция Z = 15х А +12х Б +18х В => max, определяет производственный план, максими- зирующий доход, полученный в период до достижения точки безубыточности. Вторая целевая функция Z = 8х А +7х Б +10х В => min, определяет производственный план, миними- зирующий затраты в период до достижения точки безубыточности. Далее построенную модель решим в среде Excel, для чего создаем табличную модель на рабочем листе.

Значение первой целевой функции в ячейке Е9 вычисляется по формуле:=СУММПРОИЗВ(B$4:D$4;B$5:D$5) Значение второй целевой функции в ячейке Е11 вычисляется по аналогичной формуле:=СУММПРОИЗВ(B$4:D$4;B$6:D$6) Подобные формулы, использующие ту же функцию СУММПРОИЗВ, применяются для вычисления левых частей ограничений в ячейках Е14:Е17.=СУММПРОИЗВ(B$4:D$4;B$15:D$15)=СУММПРОИЗВ(B$4:D$4;B$16:D$16)=СУММПРОИЗВ(B$4:D$4;B$17:D$17) Коэффициенты для уравнения безубыточ- ности в диапазоне В14:Е14 вычисляются как разности соответствующих значений цен и переменных затрат. Например, в ячейке В14 записана формула = В5 - В6.

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

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

Z = 15х А +12х Б +18х В => max Найденное решение для Z = 15х А +12х Б +18х В => max Вывод. для достижения точки безубыточности надо произвести 310 штук изделий А, 126 штук изделий Б и 200 штук изделий В. При этом прибыль составит 9762 тыс. руб., а суммарные переменные затраты – 5362 тыс. руб.

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

Найденное решение для этой целевой функции: Z = 8хА+7хБ+10хВ => min

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