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

Структура программы:

Разделение накладных издержек на переменные и постоянные: листы «Overheads» и «Cost_Chart».
Расчет переменных издержек на единицу продукции: «Var.».
Локализация постоянных издержек по видам продукции: «Fix.».
Анализ безубыточности продукции: «CVP», «BEP_Chart».
Планирование сбыта и цен: «Planning».

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

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

Разделение накладных издержек на переменные и постоянные

На листе «Overheads» в столбце «Параметр базовой активности» необходимо в каждой ячейке со списком выбрать соответствующий данной статье накладных издержек параметр базовой активности (базовый параметр) или опцию «Не разделять издержки!». Далее производится разделение каждой статьи на переменную и постоянную часть либо статья принимается полностью постоянной (опция «Не разделять издержки!»).

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

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

На листе «Var.» в соответствующих ячейках, помеченных зеленым цветом, необходимо ввести исходные данные для расчета переменных издержек на каждый вид продукции:

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

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

На листе «Fix.» производится локализация постоянных издержек по видам продукции. Необходимо в ячейки, помеченные зеленым цветом, ввести объем продаж каждого вида продукции за 12 месяцев. Программа автоматически выполнит расчет постоянных затрат по каждому виду продукции в месяц.

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

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

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

Планирование сбыта и цен

Лист «Planning» используется для оптимизации товарного портфеля завода, а также для сценарного анализа. Для планирования вводятся 3 параметра:

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

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

Для сценарного анализа можно воспользоваться встроенной функцией Excel. Для этого нужно выбрать меню СЕРВИС/СЦЕНАРИИ. В открывшемся диалоговом окне ДИСПЕТЧЕР СЦЕНАРИЕВ нажать кнопку ДОБАВИТЬ, написать название сценария выбрать изменяемые ячейки C8:C12 (планируемые постоянные расходы) и нажать кнопку ОК. В открывшемся диалоговом окне ЗНАЧЕНИЕ ЯЧЕЕК СЦЕНАРИЯ ввести новые значения и нажать ОК. Созданный сценарий появится в диалоговом окне ДИСПЕТЧЕР СЦЕНАРИЕВ. При нажатии кнопки ВЫВЕСТИ, данные, введенные в изменяемые ячейки, выводятся на лист и происходит автоматический расчет операционной прибыли. Для создания сценария изменения цен на продукцию или объемов продаж необходимо повторить описанную выше операцию, только выбрать изменяемые ячейки B18:B22;B25:B32;B35:B41;B44:B46 (цена) или С18:С22;С25:С32;С35:С41; С44:С46 (объем продаж). Каждый сценарий развития ситуации будет состоять из 3 или 2 (если постоянные расходы не изменяются) сценариев, созданных при помощи функции Excel. Создав несколько сценариев, можно поочередно выводить их на экран и наблюдать изменение операционной прибыли.