Автоматизация и моделирование бизнес-процессов в Excel

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

В принципе моделью можно назвать любую электронную таблицу, если она может быть применена для получения какого-либо результата, способного повлиять на принятие любого решения. Значит модель — это не просто таблица. Модель — это совокупность данных, собранных определенных образом и умение применить эти данные для получения какого-либо результата. А результатом является процесс изучения того, что уже есть и того как может быть. В нашем случае результат — это определение такой загрузки производственных мощностей, при которой будет получена максимально возможная прибыль. А для определения такой максимальной загрузки необходимо распределить издержки таким образом, чтобы получить (вычислить) наиболее реальную себестоимость и рентабельность каждого вида продукции, и, как следствие, определение точки безубыточности каждого вида продукции.

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

Цена реализации х Х = Переменные издержки + Постоянные издержки

или

2,30 х Х = 0,41 (переменные издержки) х Х + 150000 (амортизация линии) + 2742541 (косвенные затраты)

, где переменные издержки на единицу продукции определяются как сумма значений определенных на листе Нормы (рис. 13.2.). Откуда,

(2,30 — 0,41) х Х = 150000 (амортизация линии) + 2742541 (косвенные затраты)

откуда

Х = (150000 (амортизация линии) + 2742541 (косвенные затраты)) : 0,41 = 1530445 шт.

Но это классическая точка безубыточности , при определении которой предполагается, что постоянные издержки не будут изменяться. И зависит она в таком случае только от цены реализации и переменных издержек на единицу продукции. Соответствует ли она действительности при ее расчете в составе многономенклатурного производства. Для анализа измените в таблице (рис. 13.6.) количество реализации стандартных бутылок с 4 млн. до 5 млн. При этом сумма косвенных издержек на производство продукции чашки изменится с 2742541 рублей до 2583045. Подставьте это значение в расчеты и тогда изменится точка безубыточности производства чашек с 1530445 штук до 1446056 штук. Вывод — точка безубыточности в совокупности ее расчета с учетом производства другой продукции, величина не постоянная и зависит от объемов производства другой продукции.

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

Выводы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рис. 14.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 производит вычисление точки безубыточности (рис. 14.2.) для каждого выбранного вида производимой продукции.

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

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

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

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

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

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

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

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

Таблица с данными для создаваемого графика показана на рис. 14.4. и расположена в области Н1:L30 рабочего листа Анализ.

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

Для создания таблицы возможны три метода ввода в нее данных:

• изменяя содержимое ячейки D11, полученные результаты вычислений из ячеек D11, Е12, Е13, Е17 и Е19 вводить в соответствующие ячейки создаваемой таблицы с клавиатуры;
• использовать для создания формулы;
• воспользоваться инструментом Excel — Таблица подстановки.

Первый метод довольно распространен на практике, но при большом объеме данных займет довольно много времени и мало чем отличается от механических расчетов.

Применение формул при создании таблицы

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

Рис. 14.5. Формулы для создания таблицы определения точки безубыточности

В связи с тем, что объем изготавливаемой продукции колеблется в довольно широком диапазоне: от сотен тысяч единиц (фигурные бутылки — 800 тысяч) до миллионов (4 млн. — стандартные бутылки), а в таблице всего 29 строк, то шаг изменения объема реализации для первого вида продукции, не подойдет для второго. Поэтому в ячейке М1 задается шаг изменения объема реализации для каждой следующей строки таблицы. Кроме того, наличие такого количества формул увеличивает физические размеры файла и уменьшает быстродействие проведения вычислений.

Использование инструмента Excel Таблица подстановки

Третий метод создания таблицы — использование инструмента Excel Таблица подстановки.

Предварительный этап применения таблицы подстановки заключается во вводе в диапазон ячеек Н2:L2 ссылок на ячейки исходной таблицы (рис. 14.1.) и указания диапазона изменения объема реализации продукции.

Для вызова диалогового окна Таблица подстановки выделите диапазон Н2:L30 и выполните команду Данные/Таблица подстановки. В этом диалоговом окне Таблица подстановки (рис. 14.6.) имеются два поля:

Подставлять значения по столбцам в: при расположении заполняемой таблицы горизонтально;

Подставлять значения по строкам в: при вертикальном расположении таблицы.

Рис. 14.6. Диалоговое окно Таблица подстановки

В поле Подставлять значения по строкам в укажите ссылку на ячейку D11 и нажмите кнопку ОК.

После заполнения таблицы в диапазоне ячеек I3:L30 будет находиться формула:

{=ТАБЛИЦА(;D11)}

в качестве аргумента которой указана ячейка D11. То есть, Excel самостоятельно произвел подстановку данных указанных в области Н2:Н30 в ячейку D11 и вычислил результат. Фигурные скобки говорят о том, что это формула массива.

Рис. 14.7. Формулы полученные при использовании Таблицы подстановки

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

На рис. 14.7. в столбце Н с данными объема реализации для изменения шага применена другая формула. Она основана на том, что по значению числа, введенного в ячейку А2, производит поиск шага изменения объема реализации в области Z1:АА30 (рис. 14.8.) и добавляет его к ячейке, находящейся выше. Это нововведение мало чем отличается от второго случая создания таблицы. Для изменения шага объема реализации придется вводить его значение в ячейку А2, но с тем отличием, что с клавиатуры вводится не само значения шага, а порядковый номер, который присвоен значению этого шага в дополнительно созданной таблице (рис. 14.8.). Но этот метод имеет преимущество — возможность автоматизации процесса задания шага.

Рис. 14.8. Таблица ввода шага изменения объема реализации

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

В качестве инструмента для изменения содержимого ячейки А2 используйте элемент управления Поле со списком.

Но просто внедрение элемента Поле со списком не позволит до конца завершить автоматизацию процесса создания таблицы, потому что при создании таблицы применение только элемента управления Переключатель предполагает перенос фактических данных из таблицы на листе РаспределениеЗатрат. И при этом переносе данных в ячейке D11 находится значение предполагаемого объема реализации. Это значение попадая в область I2:L30 нарушит гармоничность таблицы определения точки безубыточности, а соответственно и график.

Поэтому элементу управления Поле со списком назначьте макрос Обнуление, производящий обнуление значение объема реализации в таблице (рис. 14.1.), состоящий из одной строки:

Range("D11").ClearContents

При вводе шага изменения объема реализации, для того чтобы увидеть на графике — найдена точка безубыточности или нет, нужно перейти в режим просмотра этого графика и убедиться в том, что данный шаг изменения объема реализации не позволяет увидеть на графике точку безубыточности или она будет находиться на графике очень низко. Тогда необходимо вернуться на лист Анализ, изменить шаг и опять перейти в режим просмотра графика. Это довольно утомительный процесс. Для того чтобы не делать при работе "лишних" движений в ячейке С24 создайте формулу:

=ЕСЛИ(K30>L30;"Точка безубыточности не определена!";ЕСЛИ(K16<L16;"Точка безубыточности НИЗКО!";""))

Задача этой формулы — определение шага изменения объема реализации в зависимости от:

• значения, когда точка безубыточности не определена на графике, о чем говорит сравнение крайнего значения суммарных затрат в ячейке К30, по которой создается график с такой точкой объема выручки со значением в ячейке L30 — полученная выручка от реализации этого объема. Если значения суммарных затрат выше, то пересечения этих значений на графике нет и поэтому формула в ячейке С24 формирует текст: Точка безубыточности не определена!.
• значения, когда точка безубыточности находится очень низко. В этом случае возвращается текст: Точка безубыточности НИЗКО!. Для этого пользователь определяет самостоятельно — масштабируемость графика, и в зависимости от этого определяет номер строки сравнения во второй функции ЕСЛИ.

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

Создание графика

Для создания графика выделите область Н1:L30 на листе Анализ и нажмите кнопку Мастер диаграмм на Стандартной панели инструментов. Мастер диаграмм поможет создании графика (рис. 14.9.).

Рис. 14.9. Определение точки безубыточности графическим способом

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

Таблица распределения накладных расходов на листе РаспределениеЗатрат (рис. 13.6.) по видам продукции, создание которой было подробно описано в прошлой главе, довольно сложна для анализа. Причина — любое изменение данных в рабочих листах Нормы, Цех или Администрация при различных методиках разнесения косвенных затрат повлечет за собой изменение всех параметров таблицы в целом. Например, увеличение в два раза норм оплаты труда основных производственных рабочих линии стандартных бутылок повлечет за собой:

• увеличение постоянных общецеховых издержек по линии стандартных бутылок на 21,6% при уменьшении издержек по второй линии на 39%;
• увеличение постоянных общезаводских издержек по линии стандартных бутылок на 54% при уменьшении на 33% по остальным линиям;
• как следствие — точка безубыточности возрастет на 47%, в то время как по всем остальным производственным линиям она снизится от 25 до 18%.

Увеличение же объема реализации по той же линии на 10% влечет за собой увеличение точки безубыточности этой продукции на 5,3% и снижение ее про другим видам продукции на 1,0% — 2%.

Насколько взаимозависимы все параметры таблицы? Попытайтесь определить нулевую рентабельность производства продукции по изготовлению стандартных бутылок. С исходными данными в ячейке F35 — точка безубыточности по линии стандартных бутылок (рис. 13.6.) возвращено значение 4622083. Применение инструмента Подбор параметра для поиска точки безубыточности реализации стандартных бутылок в общей таблице вычислит значение равное 5430282. Для этого вызвав диалоговое окно Подбор параметра найдите значение 0 в ячейке F33 (рентабельность) изменяя объем реализации. Обратите внимание — изменились и все параметры таблицы.

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

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

Корректировка созданной таблицы

В связи с тем, что для последующего анализа поведения затрат, объем реализации в диапазоне ячеек D8:G8 будет изменяться, зафиксируем его и цену ее реализации в области ячеек D1:G2 (рис. 14.10.). Для этого скопируйте диапазон ячеек D8:G9 и выделив ячейку D1 произведите вставку.

Рис. 14.10. Таблица для исследования поведения затрат с зафиксированными значениями исходных данных: объем и цена реализации

Для последующего обновления исходных данных в ячейках D8:G8 напишите макрос ВводИсходныхДанных (рис. 14.11.). Конечно, наиболее простой вариант заключался бы в копировании диапазона D1:G2, и последующей вставке в таблицу. Но, если обновление исходных данных потребуется задать, находясь на другом рабочем листе, то потребуется написать строки кода VBA, для перехода на этот рабочий лист и возврата в исходный. Это не совсем удобно. Исходя из этого, подпрограмма (рис. 14.11.) производит присвоение значений ячейкам с исходными данными не переходя на рабочий лист РаспределениеЗатрат.

Рис. 14.11. Подпрограмма обновления исходных данных

Поведение затрат отдельного продукта в составе общих затрат

Для анализа поведения различных затрат и определения точки безубыточности на выпускаемую продукцию в составе всей продукции выпускаемым заводом, произведите вставку нового рабочего листа ТаблицаЗавод (рис. 14.12.). Анализ проведем на примере продукции линии производства стандартных бутылок.

Рис. 14.12. Рабочий лист ТаблицаЗавод

Методология создания таблицы будет отличаться от создания подобной таблицы на листе Анализ. Таблица расположена в области В1:F50. В верхней части ее в диапазоне ячеек В1:F1 последовательно слева направо введите формулы ссылки на ячейки рабочего листа РаспределениеЗатрат:

• =РаспределениеЗатрат!F8
• =РаспределениеЗатрат!F12
• =РаспределениеЗатрат!F16+РаспределениеЗатрат!F18
• =РаспределениеЗатрат!F28
• =РаспределениеЗатрат!F30

В ячейку А1 введите шаг, с которым будет изменяться объем реализации продукции. В ячейку F8 рабочего листа РаспределениеЗатрат введите ссылку на ячейку А2 рабочего листа ТаблицаЗавод:

• =ТаблицаЗавод!A2

то есть все вычисления в таблице на рабочем листе РаспределениеЗатрат будут зависеть от значения введенного в ячейку А2 рабочего листа ТаблицаЗавод. А значение в ячейке А2 будет увеличиваться на шаг указанный в ячейке А1 рабочего листа ТаблицаЗавод. С изменением содержимого ячейки А2 будет соответственно изменяться и данные таблицы на рабочем листе РаспределениеЗатрат, что в свою очередь изменит и содержимое диапазона ячеек В1:F1. Содержимое значений этого диапазона ячеек надо последовательно разместить в таблице В3:F50 на рабочем листе ТаблицаЗавод.

Рис. 14.13. Подпрограмма ЦиклТаблица

Подпрограмма ЦиклТаблица является отдельным элементом подпрограммы для создания таблицы. Его задачей является:

присвоение переменной Row номера определенной первой пустой строки; копирование диапазона ячеек B1:F1; вставка значений ячеек скопированного диапазона в первой пустой строке в ячейку столбца В; увеличение значения ячейки А2 на шаг, заданный в ячейке А1.

Подпрограмма ЦиклТаблица в свою очередь входит в состав другой подпрограммы — НачальныеУсловия (рис.14.14.), задачей которой является ввод исходных данных на листе РаспределениеЗатрат, а также очистка содержимого области таблицы и ввод формул, с помощью которых будет создаваться таблица поиска точки безубыточности отдельного продукта составе всей продукции выпускаемым заводом.

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

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

• создаваемая таблица необходима только для одного — получения графика поведения затрат и определения точки безубыточности графическим путем;
• по мере формирования таблицы, а, следственно, и графика, желательно наблюдать — весь процесс формирования кривых этого графика;
• для наблюдения за процессом формирования графика, желательно чтобы он находился в центре окна Excel и на экране не должны присутствовать лишние элементы рабочего листа.

Опишем процедуры выполняемые макросом.

Первая строка кода VBA предназначена для перевода объекта Excel (Application) в полноэкранный режим и для ее записи выполните команду Вид/Во весь экран. Тогда свойству DisplayFullScreen (экран) присваивается значение True.

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

Следующие две строки изменяют ширину столбцов ColumnWidth и высоту строк RowHeight до указанного в подпрограмме размера. Это необходимо для того, чтобы диаграмма (рис. 14.18 .), расположенная справа от таблицы занимала большее место. Скрыть эти столбцы (строки) нельзя, потому что данные находящиеся в скрытых областях не отображается на диаграмме. Для записи этих строк кода VBA последовательно выполните следующие действия:

• выделите столбцы A:F и выполните команду Формат/Столбец/Ширина и в диалоговом окне Ширина столбца в поле Ширина столбца введите значение;
• выделите строки 14:50 и выполните команду Формат/Строка/Высота и в диалоговом окне Высота строки в поле Высота строки введите значение;
• после механической записи макроса отредактируйте код.

Скопируйте наименование подпрограмм НачальныеУсловия и ЦиклТаблица и вставьте в тело макроса, причем текст имени ЦиклТаблица — 48 раз. Код повторения выполнения подпрограммы ЦиклТаблица записывается довольно быстро но не является корректным с позиции программирования, и это может быть как один из вариантов быстрой записи повторяющихся операций. Пока этот цикл будет выполняться, пользователь может наблюдать весь процесс формирования графиков на диаграмме. Своего рода мультфильм, показывающий процесс создания графика.

После создания графика следующая строка кода VBA присваивает свойству DisplayFullScreen объекта Excel значение False и для записи этой строки выполните команду Вид/Во весь экран.

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

Рис. 14.15. Подпрограмма ТочкаИзделиеЗавод

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

Записанный макрос ТочкаИзделиеЗавод обладает рядом недостатков, один из которых — отсутствие быстродействия. Перепишите текст подпрограммы выполнения 48 циклов заполнения таблицы. Подпрограмма ТочкаИзделиеЗаводЦикл (рис. 14.16.) использует инструкцию For-Next для выполнения операции заданное количество раз и вместо операции копирования и вставки диапазона ячеек В1:F1 в пустую строку, присваивает этим ячейкам значения из ячеек, вычисленных формулами этого диапазона.

Рис. 14.16. Подпрограмма ТочкаИзделиеЗаводЦикл с использованием инструкцию For-Next

Синтаксис инструкции For-Next:

For счетчик = начало To конец [Step шаг]
[инструкции]
Next [счетчик]

Синтаксис инструкции For-Next содержит следующие элементы:

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

Для создания таблиц определения точки безубыточности для каждого изделия в автоматическом режиме, напишите макросы формирования таблиц. Один из них, для формирования таблицы для линии изготовления стандартных бутылок ТаблицаБутылкаСтандарт показан на рис. 14.17.

Рис. 14.17. Подпрограмма ТаблицаБутылкаСтандарт

Отличия теста макросов для различных видов продукции заключается только в ссылках в формулах вводимых в ячейки диапазона В1:F1.

График поведения затрат и точка безубыточности по одному изделию в составе завода

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

Рис. 14.18. График поведения затрат и точка безубыточности по одному изделию в составе завода

Снятие с производства неприбыльной продукции

На рис. 13.6. видно, что производство стандартных бутылок убыточно, при этом рентабельность завода в целом равна 12,1%. А если снять с производства убыточную продукцию? К чему это приведет? Для проверки удалите введенное в ячейку F8 значение объема реализации. Результаты показанные на рис. 14.19. получились довольно неожиданными. Вместо увеличения рентабельности по заводу в целом произошло следующее:

• рентабельность производства чашек изменилась с плюс 12,31% до минус 11,35%;
• рентабельность производства тарелок изменилась с плюс 19,61% до минус 1,83%;
• рентабельность производства фигурных бутылок изменилась с плюс 24,05% до минус 10,72%;
• рентабельность завода в целом переместилась в отрицательную область и стала равна минус 9,07%;
• точка безубыточности по трем оставшимся видам продукции увеличилась от 30 до 100 процентов.

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

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

Рис. 14.19. полученные результаты при снятии с производства убыточного изделия.

Поиск точки безубыточности для всех изделий завода

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

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

Рис. 14.20. Таблица с нулевым уровнем рентабельности по каждому изделию

При заданных параметрах расчета — величиной постоянных и переменных и принятой базе и методологии распределения постоянных затрат, то есть при множестве неизвестных, может быть получено только одно решения. Решение это показано на рис. 14.20. Для решения задачи не могут быть использованы стандартные инструменты Excel, которые были рассмотрены ранее, например инструмент Поиск решения.

Алгоритм определения нулевой точки рентабельности для всех изделий предприятия

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

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

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

Алгоритм определения нулевой точки рентабельности для всех изделий предприятия в многономенклатурном производстве

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

Подпрограмма НайтиНулевуюТочкуБезубыточности показанная на рис. 14.21. использует инструкцию For-Next для присвоения ячейкам объема реализации каждого вида продукции в строке 8, значения рассчитанной при исходных параметрах точки безубыточности в строке 35 для этих видов продукции при исходных параметрах таблицы. А после этого производится перерасчет всех формул. Параметры таблицы изменяются в связи с изменением объема реализации, значения точек безубыточности меняются и уже эти новые значения служат исходными данными для последующего изменения параметров всей таблицы.

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

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

Последовательность выполнения подпрограммы следующая:

• перед выполнением цикла расчетов приближения произвести перерасчет всех формул. Это необходимо для того, что если Excel находится в режиме ручного вычисления и при изменении каких-либо параметров, в любой из ячеек формула вернет значение ошибки, то подпрограмма откажется производить выполнение операций;
• присвоение свойству Calculation (вычисления) объекта Excel значения xlManual (ручной режим вычислений);
• присвоения ячейкам строки 8 значения определенных точек безубыточности, после чего производится перерасчет формул. Эта операция повторяется 100 раз.

Рис. 14.21. Подпрограмма НайтиНулевуюТочкуБезубыточности с применением инструкции For-Next

Подпрограмма НайтиНулевуюТочкуБезубыточности с использованием инструкции For-Next для 4-х видов изделий за 100 циклов перерасчета довольно быстро найдет решение. Но в этих то 100 циклах и заключается ее недостаток. А, если, предположим, изделий будет пятьдесят, то сколько циклов необходимо для поиска решения. Количество циклов определяется только экспериментальным путем, а для этого требуется дополнительное время. Для того, чтобы Excel сам определял количество этих циклов существует инструкция While-Wend, которая выполняет последовательность инструкций, пока заданное условие имеет значение True.

Синтаксис инструкции:

While условие
[инструкции]
Wend

содержит следующие элементы:

• условие — обязательный аргумент. Числовое или строковое выражение, которое имеет значение True или False.
• инструкции — необязательный аргумент. Одна или несколько инструкций, выполняемых, пока условие имеет значение True.

Рис. 14.22. Подпрограмма НайтиНулевуюТочкуБезубыточности2 с применением инструкции While-Wend

Эта подпрограмма мало чем отличается от предыдущей и создается копированием с последующим редактированием. Редактирование заключается в замене наименования инструкции цикла, вводе переменной Х и формулы в первой строке цикла.

Переменная Х предназначена только для читаемости текста и увеличения быстродействия подпрограммы. До выполнения цикла переменной Х присваивается значение 1 (можно присвоить любое другое отличное от нуля). Это необходимо только для обмана инструкции While-Wend, которая проверяет значение переменной Х на равенство нулю и будет выполнять цикл до тех пор пока значение переменной Х не станет равным нулю.

Далее в первой строке цикла переменной Х присваивается значение суммы абсолютных значений содержимого ячеек в строке 32 (прибыль до налогообложения). Это необходимо по следующим причинам:

• подбор объема реализации на уровне точки безубыточности каждого вида продукции подразумевает нулевую рентабельность каждого вида продукции в составе завода;
• необходимость суммирования только абсолютных значений получаемой прибыли до налогообложения объясняется "ленью" Excel и способностью инструкции While-Wend, которая найдет решение за два-три цикла определив что решение может быть найдено, если прибыль одного из изделий отнести в отрицательную область компенсировав этим положительную прибыль по другим изделиям. Таким образом суммарная прибыль будет равна нулю, а прибыль по каждому изделию отличаться от нулевой.

Создание этой строки довольно просто если вначале эту формулу ввести в любую ячейку рабочего листа применив Мастер функций (рис. 14.23.):

=СУММ(ABS($D$32);ABS($E$32);ABS($F$32);ABS($G$32))

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

Рис. 14.23. Создание формулы суммирования абсолютных значений получаемой прибыли по каждому изделию

Далее запишите макрос ввода этой формулы. На рис. 14.24. показаны этапы преобразования записанного кода макроса ввода формулы в выделенную ячейку, до строки кода VBA присвоения переменной свойства объекта Excel:

• первый этап заключается в замене текста кода ActiveCell.FormulaR1C1, на переменную Х и последующем удалении кавычек и знака равно после первой кавычки. Редактор сразу же изменит текст функций и воспримет их как свойства;
• свойства должны относиться к чему то. Поэтому в начале строки вставьте имя объекта — Application.

Рис. 14.24. Этапы преобразования созданной формулы в ячейке в строку кода VBA присвоения переменной Х вычисленного значения

Анализ полученных данных

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

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

Корректировка распределения накладных расходов

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

Поэтому внесите изменения в распределение общецеховых расходов, активизировав переключатели на рабочем листе Цех (рис. 13.4. ) следующим образом:

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

При распределении общезаводских расходов принято во внимание, что затраты коммерческого отдела распределяются по производственным линиям в следующих пропорциях: 6/11/1/2, а затраты отдела исследований связаны только с изготовлением продукции второго цеха в соотношении: 13 и 7.

Поэтому в распределении общезаводских расходов, активизировав переключатели на рабочем листе Администрация (рис. 13.5. ) следующим образом:

• коммунальные расходы — по прямым издержкам;
• коммерческий отдел — по статье Иначе, или в произвольном порядке в соотношении 30%, 55%, 5% и 10%;
• отдел исследований- по статье Иначе, или в произвольном порядке в соотношении 0%, 0%, 65% и 35%.

В таком случае распределение затрат и все соответствующие финансовые показатели при запланированном объеме реализации поменяют свои значения (рис. 14.25.)

Рис. 14.25. Таблица при изменении распределения накладных расходов

При поиске нулевой точки рентабельности для всех изделий при изменении распределения накладных расходов изменится и точка их безубыточности (рис. 14.26.).

Рис. 14.26. Таблица с измененными параметрами распределения накладных расходов с нулевым уровнем рентабельности по каждому изделию

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

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

Итоги

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

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

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

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