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

=$D$11*E12

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

остановите  запись макроса и отредактируйте код VBA;

назначьте всем элементам управления Переключатель  записанный макрос

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

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

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

выделялась  ячейка (диапазон) — Select;

производилось копирование содержимого этой ячейки (диапазона) - Selection.Copy;

затем осуществлялась вставка значений в  выделенный диапазон - Selection.PasteSpecial Paste:=xlValues.

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

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

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

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

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

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

 

4.2 Поиск точки безубыточности с переменой номенклатурой 

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

прибыль до налогообложения = 0;

рентабельность  продаж = 0;

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

вклад на покрытие = удельным постоянным затратам;

запас финансовой прочности = 0;

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

присвоение  свойству Calculation (вычисления) объекта Excel значения xlManual (ручной режим вычислений);

присвоения  ячейкам строки 8 значения определенных точек безубыточности, после чего производится перерасчет формул. Эта  операция повторяется 100 раз.  

   

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

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

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

While условие

[инструкции]

Wend

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

условие - обязательный аргумент. Числовое или строковое выражение, которое имеет значение True или False.

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

   

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

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

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

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

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

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

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

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