Методы повышения экономической эффективности ПКС на основе моделирования и оптимизации внутрикорпоративных потоков

Решение задачи планирования функционирования предприятия методом имитационного моделирования и оптимизации.На основании приведенной формализованной постановки задачи возможно ее численное решение методом имитационного моделирования и оптимизации. Для этой цели разрабатывается электронная таблица, содержащая три основных листа: лист исходных данных (параметров - ПА), лист расчета производственнофинансовых потоков (Потоки) и лист графиков (Графики) для визуализации получаемых решений.

Лист "Параметры".На листе ПА ( рис. 5.35), в верхней части таблицы находятся параметры, значения которых можно изменять с клавиатуры (за исключением , изменение которого требует корректировки формул расчета потоков). В нижней части таблицы находятся рассчитываемые параметры, значение которых получаются с помощью Excel-формул по значениям параметров из верхней части таблицы.

Рис. 5.35. Лист "Параметры"

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

Перейдем к рассчитываемым параметрам. Начальный запас товаров рассчитывается в соответствии с (5.89) как произведение начальной интенсивности потоков, то есть интенсивности потока продаж, на норму оборачиваемости запаса:

Начальная величина денежных средств равна произведению начальной величины запаса товаров на долю денежных средств от него:

( 5.107)

Начальная величина кредиторской задолженности определяется в соответствии с (5.103):

( 5.108)

Начальная величина собственных средств находится из уравнения баланса (5.102):

( 5.109)

Лист "Потоки".На листе "Потоки" ( рис. 5.36) выполняется расчет происходящих процессов при помощи механизма формул. Формулы - суть рабочего листа Excel. Формулы выполняют работу (то есть производят вычисления), которая раньше выполнялась вручную или на калькуляторе. Без формул использование электронного рабочего листа, подобного Excel, не давало бы никаких преимуществ.

Рис. 5.36. Лист "Потоки"

Формулы могут выполнять как простые действия, включая сложение, вычитание, умножение и деление, так и сложные вычисления. С помощью формул можно также работать с текстом. После того как пользователь введет формулу в какуюлибо ячейку, на рабочем листе обычно сразу появляется результат. Чтобы просмотреть саму формулу, необходимо выделить соответствующую ячейку. Тогда формула появится в строке формул. Чтобы получить возможность просмотра и редактирования формулы на рабочем листе, следует два раза щелкнуть соответствующую ячейку или выделить ее и нажать клавишу < F 2>.

Формулы в Excel всегда начинаются со знака равенства ( = ) и могут включать числовые и буквенные величины (константы), знаки арифметических операций, операций сравнения, операций с текстом, функции, скобки, данные ячеек и имена, а также встроенные формулы, называемые функциями, например, СУММ() или SUM().

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

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

В Excel можно указать положение ячейки с помощью относительной или абсолютной ссылки. По умолчанию для указания адресов ячеек в Excel применяются относительные ссылки. Это означает, что ссылки на ячейки изменяются при копировании формулы на новое место. Например, на листе "Потоки" в ячейку D6 введена формула = C6+ D3-D5. Все ссылки, находящиеся в ней, относительны. Обычно в формулах желательно применять относительные ссылки на ячейки.

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

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

При использовании стиля А1 абсолютные ссылки обозначаются знаком доллара ($) перед буквой столбца и перед цифрой строки. В стиле R1C1 номер строки и столбца не заключается в квадратные скобки. Можно задавать ссылки, в которых не меняется только столбец или только строка.

На листе "Потоки" в формулах в строке 5 для ссылки на оборачиваемость запаса используются абсолютные ссылки, записываемые как ПА!$E$7 (стиль А1). Формула в ячейке D5 введена вручную ( =МИН(C6/ПА!$E$7;D4) ), затем она была скопирована в ячейку D6. При копировании изменились только два члена, а ссылка на ячейку оборачиваемости запаса осталась неизменной.

Можно ссылаться на другие листы рабочей книги включением в формулу ссылки на лист. Например, чтобы сослаться на ячейку E8 листа ПА, необходимо ввести в формулу ПА!$E$8. Если имя листа содержит пробелы, нужно заключить ссылку на лист в кавычки.

Можно также воспользоваться мышью для ввода ссылки в ячейку или диапазон на другом листе рабочей книги. Для этого следует начать вводить формулу в ту ячейку, где должен быть результат, а затем щелкнуть по ярлычку листа, содержащего ячейку или диапазон, на которые нужно сослаться. Затем необходимо выделить ячейку или диапазон. В строке формул появляется полная ссылка, включая ссылку на лист. Если в имени листа имеются пробелы, Excel заключает ссылку на лист в одиночные кавычки. Далее следует закончить формулу и нажать клавишу <Enter>. Таким образом вводятся формулы на листе "Потоки", ссылающиеся на ячейки в листе ПА.

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

Чтобы заполнить смежные ячейки данными или формулами с помощью мыши, нужно уметь пользоваться маркером заполнения (fill handle). Маркер заполнения - это черный квадрат в нижнем правом углу выделенной ячейки или диапазона.

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

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

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

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

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

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

Общий вид формул расчета величин на листе "Потоки" согласно методу имитационного моделирования:

( 5.110)

где - интенсивность -го потока в -ю неделю, - величина -го "запаса" (то есть разновидности средств или источников средств) в -ю неделю,

( 5.111)

где - количество наименований "запасов", - количество потоков, - множество индексов потоков, входящих в или выходящих из -го "запаса".

Выражение (5.110) означает, что значение потока в -ю неделю рассчитывается по значению "запасов" и потоков в предыдущую неделю. Выражение (5.111) означает, что значение "запаса" рассчитывается по значению этого же запаса в предыдущую неделю и значениям потоков в данную неделю.

Компьютерная программа составлена для случая функции сбыта, когда выражение расчета потока продаж в системе уравнений записывается в виде уравнения (5.88), то есть, применяя Excel-формулу, получим

( 5.112)

где - оборачиваемость запаса товаров, постоянная величина.

В компьютерной программе, на листе "Потоки" величины получены следующим образом:

• рынок - по формуле , где - номер недели в соответствующем столбце в строке порядковых номеров недель, - номер недели, в начале которой резко изменяется рынок, и - интенсивности рыночного спроса до и после его изменения;
• реализация товаров - по формуле (5.112);
• запас товаров, денежные средства и кредиторская задолженность - по формуле (5.111);
• оплата поставок - по формуле (5.85), т. е. значения в этой строке представляют собой значения из строки поставок товаров в кредит со сдвигом вправо на недель (значения строки в первые недель равны начальной интенсивности потоков );
• собственные средства - по формуле (5.102).

Значения средств и потоков на первом шаге, то есть в первую неделю, взяты из листа ПА, из , , , и .

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

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

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

При создании диаграммы можно использовать Мастера диаграмм (Chart Wizard), который осуществляет поэтапное руководство процессом построения диаграммы. Во многих случаях Excel может строить диаграмму по выделенным данным. Для этого существуют определенные правила размещения данных, которые определяют, в каких ячейках находятся данные для оси категорий (category axis) X, в каких - названия, какие ячейки используются для меток легенды (legend). В большинстве случаев правила соответствуют стандартному размещению данных, поэтому Excel может строить диаграммы без постороннего вмешательства. Настроить диаграмму можно с помощью многочисленных команд для диаграмм.

Диаграммы в Excel включают много объектов, каждый из которых можно выделять и изменять отдельно.

В Excel можно построить два типа диаграмм: внедренные диаграммы и диаграммы на отдельных листах диаграмм. Внедренные диаграммы создаются на рабочем листе рядом с таблицами, данными и текстом ( рис. 5.37). Внедрение диаграмм имеет смысл при создании отчетов, для демонстрации диаграмм рядом с данными, по которым они построены.

увеличить изображение
Рис. 5.37. Средства, источники и потоки средств

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

Оба типа диаграмм легко строятся с помощью Мастера диаграмм. Мастер диаграмм руководит процессом создания диаграммы шаг за шагом и дает возможность перед завершением процесса просмотреть диаграмму и внести в нее необходимые изменения.

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

После закрытия последнего диалогового окна Excel внедряет созданную диаграмму в выделенную область рабочего листа (рис. 5.37).

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

Применение поискового оптимизатора.Надстройка Excel Поиск решения (Solver) позволяет решать нелинейные задачи оптимизации. Программа не только находит решение, но и гарантирует, что оно будет наилучшим. При этом можно указать набор ячеек с изменяемыми значениями, множество имеющихся ограничений, а также одну ячейку, значение которой должно быть максимальным, минимальным или равным некоторой величине. Надстройка добавляется с помощью команды Сервис, Надстройки (Tools, Add-Ins), а используется при выборе команды Сервис, Поиск решения (Tools, Solver).

Задачи, лучше всего решаемые данным средством, имеют три аспекта.

Вопервых, имеется единственная цель, например максимизация прибыли или минимизация расходов.

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

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

Можно изменить параметры работы Поиска решения, например, поменять метод поиска ответа, ограничить время поиска, задать другую точность вычислений. При нажатии в диалоговом окне Поиск решения (Solver Parameters) на кнопку Параметры (Options) появляется диалоговое окно Параметры поиска решения (Solver Options). Установки по умолчанию подходят для большинства задач.

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

1. Выделить оптимизируемую ячейку (суммарные продажи за плановый период).
2. Выбрать команду Сервис, Поиск решения (Tools, Solver). Загружается надстройка (если не была загружена при запуске Excel), и появляется диалоговое окно Поиск решения.
3. В поле Установить целевую ячейку (Set Target Cell) уже находится ссылка на выделенную на первом шаге ячейку.
4. Установить тип взаимосвязи между целевой ячейкой и решением путем выбора переключателя в группе Равной (Equal To) (в данном случае Максимальному значению - Max).
5. Перейти в поле Изменяя ячейки (By Changing Cells) и указать смежные ячейки, которые должны изменяться в процессе поиска наилучшего решения. В данном случае это ячейки, соответствующие поставкам товаров в кредит на интервале , то есть ячейки $D$3:$BF$4.
6. Нажать кнопку Добавить (Add), чтобы ввести ограничения задачи. Откроется диалоговое окно Добавление ограничения (Add Constraint).
7. Ввести ограничение неотрицательности денежных средств. Находясь в поле Ссылка на ячейку (Cell Reference), следует указать ячейки $D$8:$BF$8, нажать клавишу <Tab> или щелкнуть по стрелке раскрывающегося списка и выбрать знак отношения, то есть > =.
8. Таким же образом добавить остальные ограничения.
9. Нажать кнопку Параметры (Options), в появившемся диалоговом окне Параметры поиска решения (Solver Options) установить флажок Неотрицательные значения для задания соответствующего ограничения для значений интенсивностей поставок и нажать кнопку ОК.
10. Нажать кнопку Выполнить (Solve). По окончании поиска решения появится диалоговое окно результатов.
11. Выбрать переключатель Сохранить найденные значения (Keep Solver Solution), чтобы сохранить найденные значения или переключатель Восстановить исходные значения (Restore Original Values), чтобы вернуть значения, которые были на рабочем листе. С помощью этого диалогового окна можно сформировать также отчет о найденных результатах.

В данном случае следует выбрать переключатель Сохранить найденные значения (Keep Solver Solution) и нажать кнопку ОК. Полученное решение представлено на рис. 5.37. Значение суммарной реализации за плановый период составило 1468 тыс. руб.

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

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

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