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

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

вся произведенная продукция за рассчитываемый период (месяц) будет продана;
постоянные издержки остаются неизменными при определенном объеме продаж;
переменные издержки на единицу продукции не изменяются при изменении объема продаж.

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

Анализ и поведение точки безубыточности для одного, отдельно взятого продукта, в составе всей изготовляемой продукции, не представляется сложным. Для этого на рабочем листе Анализ в диапазоне ячеек С8:Е20, для классификации издержек для отдельного продукта, создайте таблицу (рисунок 1) с отображением общих сумм по издержкам этого продукта.

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

Для этого в ячейках D11:D16 переносятся числовые значения из таблицы на рабочем листе РаспределениеЗатрат, а во всех остальных ячейках находятся простые формулы расчета (рисунок 2).

Рис.2. Таблица классификации издержек для отдельного продукта с формулами

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

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

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

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

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

Текст названий данных в ячейках С11:С16 должен в точности соответствовать тексту соответствующих статей на рабочем листе. Для этого, последовательно переходя на лист РаспределениеЗатрат, скопируйте содержимое ячеек, в которых он содержится и, возвращаясь на лист Анализ, произведите вставку.

Для переноса самих данных нет необходимости переходить на рабочий лист РаспределениеЗатрат и производить копирование данных и последующую вставку на рабочем листе Анализ.

Введите в ячейку D11 формулу:

  =ВПР(C11;РаспределениеЗатрат!$C$8:$G$46;$A$1+1;ЛОЖЬ)

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

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

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

введите формулы в диапазон ячеек D11:D16 и начните запись макроса;
выделите диапазон ячеек D11:D16 и, выполнив нажатия на клавиши F2 и Ctrl+Enter, запишите одновременный ввод формул в выделенный диапазон ячеек;
выделите диапазон ячеек E12:Е13 и введите формулу:
```
   =D12/$D$11
```
которая производит деление суммы реализации выбранного продукта на количество объема реализации и запишите ввод формул в эти ячейки. Эта же формула в ячейке Е13 производит деление общей суммы переменных издержек для этого продукта на количество;
замените введенные формулы на вычисленные значения;
выделите ячейки D12:D13 и запишите ввод формул аналогичным способом:
```
   =$D$11*E12
```
которая в ячейке D12 производит умножение цены реализации продукта на количество объема реализации, а в ячейке D13 определяет общую сумму переменных издержек;
остановите запись макроса и отредактируйте код VBA;
назначьте всем элементам управления Переключатель записанный макрос.

Обратите внимание на созданную подпрограмму ПереносДанных. Основная ее особенность - это последовательность выполнения операций. Выполнение макроса начинается с активизации одного из элементов управления, при котором происходит обновление содержания ячейки А1. По значению в ячейке А1 происходит поиск данных из таблицы на рабочем листе РаспределениеЗатрат, после чего формулы заменяются на вычисленные ими значения. Далее, по введенным формулам происходит определение цены реализации и переменных издержек за единицу продукции, после чего эти формулы также заменяются на вычисленные ими значения. После этого вводятся формулы расчета общего объема реализации и всей суммы переменных издержек на весь объем выпущенной продукции.

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

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

выделялась ячейка (диапазон) - Select;
производилось копирование содержимого этой ячейки (диапазона) - Selection.Copy;
затем осуществлялась вставка значений в выделенный диапазон - Selection.PasteSpecial Paste:=xlValues.

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

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

Поиск точки безубыточности с помощью инструмента Excel Подбор параметра

Формула в ячейке Е22 производит вычисление точки безубыточности (рисунок 2) для каждого выбранного вида производимой продукции.

Точка безубыточности показывает тот объем реализации продукции, при котором предприятие имеет нулевую рентабельность. При этом нужно учитывать тот фактор, что постоянные издержки не изменяются. Поиск точки безубыточности можно осуществить используя инструмент Excel Подбор параметра - для определения в ячейке D11 значения объема реализации продукции, при котором полученная от реализации прибыль (значение ячейки D20) равна нулю.

Для этого выделите ячейку D20 (Прибыль) и выполните команду Сервис | Подбор параметра. В диалоговом окне Подбор параметра в поле Значение введите значение 0, а в поле Изменяя значение ячейки ссылку на ячейку D11 (Объем реализации). После нажатия на кнопку ОК в ячейке D20 должно быть значение, равное значению в ячейке Е22. Это подтвердит правильность вычислений с помощью введенной в ячейку Е22 формулы.

Можно произвести эти вычисления и не прибегая к диалоговому окну Подбор параметра. Для этого запишите макрос НулеваяПрибыль присвоения ячейке D11 значения, вычисленного формулой в ячейке D22. Весь код подпрограммы будет состоять из одной строки:

  Range("D11").Value = Range("E22")

На следующем шаге мы рассмотрим определение точки безубыточности одного вида продукции в составе завода.

Предыдущий шаг Содержание Следующий шаг