На этом шаге мы приведем реализацию указанного алгоритма.
Довольно интересно определить точки безубыточности одновременно для всех видов продукции завода в многономенклатурном производстве. И помогут в этом не сложные формулы, а правильно выбранная последовательность выполнения этой процедуры.
Подпрограмма НайтиНулевуюТочкуБезубыточности, показанная на рисунгке 1, использует инструкцию For-Next для присвоения ячейкам объема реализации каждого вида продукции в строке 8, значения рассчитанной при исходных параметрах точки безубыточности в строке 35 для этих видов продукции при исходных параметрах таблицы.
Рис.1. Подпрограмма НайтиНулевуюТочкуБезубыточности с применением инструкции For-Next
А после этого производится перерасчет всех формул. Параметры таблицы изменяются в связи с изменением объема реализации, значения точек безубыточности меняются и уже эти новые значения служат исходными данными для последующего изменения параметров всей таблицы.
Следовательно, основой для определения единственно правильного результата является ручной режим вычислений. Если использовать автоматический режим вычислений, то после изменения содержимого в каждой ячейке в строке 8 произойдет перерасчет всех формул, а, следовательно, и изменение всех параметров таблицы, и эта последовательность действий не позволит прийти к решению задачи в связи с непредсказуемостью поведения параметром таблицы..
Несмотря на обилие взаимосвязанных параметров, решение возможно только одно для всех видов продукции многономенклатурного производства. И с помощью создаваемой подпрограммы осуществляется постепенное приближение к истинному значению объема реализации каждого вида продукции при нулевой рентабельности каждого из них.
Последовательность выполнения подпрограммы следующая:
- перед выполнением цикла расчетов приближения произвести перерасчет всех формул. Это необходимо для того, что если Excel находится в режиме ручного вычисления и при изменении каких-либо параметров, в любой из ячеек формула вернет значение ошибки, то подпрограмма откажется производить выполнение операций;
- присвоение свойству Calculation (Вычисления) объекта Excel значения xlManual (Ручной режим вычислений);
- присвоения ячейкам строки 8 значения определенных точек безубыточности, после чего производится перерасчет формул. Эта операция повторяется 100 раз.
Подпрограмма НайтиНулевуюТочкуБезубыточности с использованием инструкции For-Next для 4-х видов изделий за 100 циклов перерасчета довольно быстро найдет решение. Но в этих то 100 циклах и заключается ее недостаток. А, если, предположим, изделий будет пятьдесят, то сколько циклов необходимо для поиска решения? Количество циклов определяется только экспериментальным путем, а для этого требуется дополнительное время. Для того, чтобы Excel сам определял количество этих циклов существует инструкция While-Wend, которая выполняет последовательность инструкций, пока заданное условие имеет значение True.
Синтаксис инструкции:
While условие
[инструкции]
Wend- условие - обязательный аргумент. Числовое или строковое выражение, которое имеет значение True или False.
- инструкции - необязательный аргумент. Одна или несколько инструкций, выполняемых, пока условие имеет значение True.
Рис.2. Подпрограмма НайтиНулевуюТочкуБезубыточности2 с применением инструкции While-Wend
Эта подпрограмма мало чем отличается от предыдущей и создается копированием с последующим редактированием. Редактирование заключается в замене наименования инструкции цикла, вводе переменной Х и формулы в первой строке цикла.
Переменная Х предназначена только для читаемости текста и увеличения быстродействия подпрограммы. До выполнения цикла переменной Х присваивается значение 1 (можно присвоить любое другое отличное от нуля). Это необходимо только для "обмана" инструкции While-Wend, которая проверяет значение переменной Х на равенство нулю и будет выполнять цикл до тех пор, пока значение переменной Х не станет равным нулю.
Далее в первой строке цикла переменной Х присваивается значение суммы абсолютных значений содержимого ячеек в строке 32 (прибыль до налогообложения). Это необходимо по следующим причинам:
- подбор объема реализации на уровне точки безубыточности каждого вида продукции подразумевает нулевую рентабельность каждого вида продукции в составе завода;
- необходимость суммирования только абсолютных значений получаемой прибыли до налогообложения объясняется "ленью" Excel и способностью инструкции While-Wend, которая найдет решение за два-три цикла определив, что решение может быть найдено, если прибыль одного из изделий отнести в отрицательную область, компенсировав этим положительную прибыль по другим изделиям. Таким образом, суммарная прибыль будет равна нулю, а прибыль по каждому изделию отличаться от нулевой.
Создание этой строки довольно просто, если вначале эту формулу ввести в любую ячейку рабочего листа, применив Мастер функций (рисунок 3):
=СУММ(ABS($D$32);ABS($E$32);ABS($F$32);ABS($G$32))
Рис.3. Создание формулы суммирования абсолютных значений получаемой прибыли по каждому изделию
Далее запишите макрос ввода этой формулы. На рисунке 4 показаны этапы преобразования записанного кода макроса ввода формулы в выделенную ячейку, до строки кода VBA присвоения переменной свойства объекта Excel:
- первый этап заключается в замене текста кода ActiveCell.FormulaR1C1, на переменную Х и последующем удалении кавычек и знака равно после первой кавычки. Редактор сразу же изменит текст функций и воспримет их как свойства;
- свойства должны относиться к чему-то. Поэтому в начале строки вставьте имя объекта: Application.
Рис.4. Этапы преобразования созданной формулы в ячейке в строку кода VBA присвоения переменной Х вычисленного значения
На следующем шаге мы рассмотрим анализ данных.
