CC BY
14
В1СНИК ПРИАЗОВСЬКОГО ДЕРЖАВНОГО ТЕХН1ЧНОГО УН1ВЕРСИТЕТУ 2007р. Вип.№17
УДК 658.004.12:004
Волощук В.А.1, Пинько С.Ф.2
ПОВЫШЕНИЕ КАЧЕСТВА ПЛАНИРОВАНИЯ ЗА СЧЕТ ИСПОЛЬЗОВАНИЯ ИНФОРМАЦИОННЫХ ТЕХНОЛОГИЙ
Определены экономические задачи, решение которых возможно автоматизировать с помощью программы Excel. Построена схема последовательности операций оптимизации экономической задачи с помощью программы Microsoft Excel и описаны на примере ее основные элементы
Научно-технический прогресс последних десятилетий развивается под знаком информатизации, ставшей поистине важнейшим фактором развития мирового общества. Электронно-вычислительная техника и базирующиеся на ее основе информационные технологии прочно вошли во все сферы жизнедеятельности человека, способствуя внедрению и развитию наукоемкого производства, реализации новых видов информационного обмена. В связи с увеличением объемов обрабатываемой информации и развитием компьютерного рынка, особенно актуальным становится применение пакетов прикладных программ для обработки и анализа экономической информации.
Одним из таких програмно-инструментальных средств, которое может быть применено при решении широкого класса задач финансово-экономического характера, является доступный практически любому пользователю табличный процессор Microsoft Excel. Для массового пользователя написано множество пособий [1, 2, 3], которые учат обращаться с компьютером и показывают новые сферы его применения. Анализ вышеуказанных источников показал, что уже написанные пособия формируют теоретические знания и практические навыки использования инструментальных средств табличного процессора Excel для решения экономических задач.
Несмотря на это, «качество» использования многочисленными специалистами вычислительной техники остается на довольно низком уровне. Компьютеры нередко выступают в роли «продвинутого» калькулятора или пишущей машинки.
В связи с этим авторами изучен и адаптирован под пользователя, не обладающего специальными навыками, один из важных разделов программы Excel - решение задач оптимизации, которые широко применяются в планировании на различных уровнях.
Вопросы оптимизации занимают очень важное место в бизнесе и деятельности современных организаций и предприятий. Такие проблемы присутствуют в самых различных процессах, которые грубо можно разделить на следующие категории:
- оптимизация перевозок грузов;
- оптимизация распределения ресурсов (от распределения производственных мощностей для выпуска нескольких видов продукции с различной прибыльностью до оптимизации состава стада крупного рогатого скота для наиболее прибыльного производства молока и мяса);
- оптимизация расхода / раскроя материала.
Важнейшим направлением повышения качества планирования в современных условиях является использование компьютерной техники и рост профессионализма персонала. В некотором смысле эти два фактора взаимосвязаны, так как использование компьютерной техники влияет на повышение профессионализма.
Решение оптимизационных задач малой и средней степени сложности с помощью Excel -одно из направлений повышения качества планирования. Эти операции требует минимальных навыков и усилий, поэтому такой подход является полностью оправданным. Для решения задач оптимизации используется инструмент «Поиск решения».
_Такие задачи обычно представляют собой систему уравнений с несколькими
ПГТУ. аспирант 2ПГТУ, аспирант
неизвестными и набор ограничений на решения. Решение задачи необходимо начинать с построения соответствующей модели, а именно размещение на рабочем листе исходных данных и формул, корректно отражающих зависимости различных величин.
Оптимизацию искомого решения следует начать с организации рабочего листа в соответствии с пригодной для поиска решения моделью, для чего нужно хорошо понимать взаимосвязи между переменными и формулами. Хотя постановка задачи представляет основную сложность, время и усилия, затраченные на подготовку модели, вполне оправданы, поскольку полученные результаты могут уберечь от излишней траты ресурсов при неправильном планировании, помогут увеличить процент прибыли за счет оптимального управления финансами или выявить наилучшие величины объемов производства, запасов.
Обычными задачами, решаемыми с помощью надстройки «Поиск решения», являются:
1. Ассортимент продукции. Максимизация выпуска товаров при ограничениях на сырье (или другие ресурсы) для производства изделий.
2. Штатное расписание. Составление штатного расписания для достижения наилучших результатов при наименьших расходах.
3. Планирование перевозок. Минимизация затрат на транспортировку.
4. Составление смеси. Получение заданного количества смеси при наименьших расходах.
5. Оптимальный раскрой материалов (ограничения - количество деталей различной формы и размера).
6. Оптимизация финансовых показателей (например, максимизация доходов за счет оптимизации средств на разные инвестиционные проекты).
С работой надстройки «Поиск решения» можно ознакомится в любом учебнике по Excel или с помощью файла Solvsamp.xls. Этот файл входит в комплект поставки Excel, содержит модели для типичных задач, решаемых с помощью надстройки «Поиск решения».
В файл Solvsamp.xls включены примеры следующих моделей:
1. модель сбыта;
2. структура производства;
3. транспортная задача;
4. график занятости;
5. управление капиталом;
6. портфель ценных бумаг;
7. проектирование цепи, состоящей из N элементов с различными характеристиками.
Каждая из этих моделей включает постановку реально встречающейся задачи и описание
ее решения.
Рассмотрим на примере модели структуры производства все шаги решения оптимизационных задач с помощью программы Excel и составим схему последовательности операций (рис. 1).
В исследуемом примере рассматривается определение оптимальной структуры производства. Предприятие выпускает товары (телевизоры, стерео- и акустические системы), использующие общий склад комплектующих. В связи с ограниченность запаса необходимо найти оптимальное соотношение объемов выпуска изделий. Размещение данных примера на рис. 2.
Рассмотрим более подробно каждый пункт предложенной схемы.
1. Постановка задачи.
Как уже говорилось выше, постановка задачи представляет основную сложность в процессе оптимизации экономического решения. Основное время и усилия тратятся на определение и описание целевой функции и ограничений для ее переменных. В рассматриваемом примере переменными являются количество выпускаемых изделий каждого вида (ячейки D9:F9). Необходимо найти такую структуру производства при которой предприятие получит максимальную прибыль. Целевая функция имеет вид: з
= (1)
i=1
Рис. 1 - Последовательность операций оптимизации экономического процесса с помощью
надстройки «Поиск решения»
Структура производства
Ваше предприятие выпускаеттеливизоры, стерео- и акустические системы, используя общий склад комплектующих. В связи с ограниченностью запаса необходимо найти оптимальное соотношение объемов выпуска изделий_
Телевизор Стерео А к сист.
Количество-* 316 5 158
Наш. изд. Склад Мслопьз.
Шасси 530 321 1 1 0
Кинескоп 360 316 1 0 0
Динамик 1020 484 1 2 1
Блок пит. 570 321 1 1 0
Эпвк. плата 800 800 2 2 1
Прибыль:
По sидам изделий 23 700р. 250р. 5 530р.
Всего 29 480|>.
Рис. 2 - Расположение данных примера на рабочем листе Excel
где х; - количество выпускаемых изделий i-oro вида,
77; - прибыль по i-ому изделию.
На рабочем листе целевая функция записана в ячейках D17:F17 (произведение каждого слагаемого) и D18 (сумма произведений) - окончательный результат.
Исходными данными нашего примера являются имеющиеся на складе комплектующие к,, где j = 1;5 (ячейки В11:В15) и их расход на каждый вид изделий пм. где i = 1;3, j=1;5 (ячейки D15:F15). Очевидно, что количество использованных ко м п л с кт у ю щ и х на производство не должно превышать их запаса на складе (1-е ограничение):
(2)
Расчет использованного количества комплектующих щ * xi находится в ячейках С11:С15.
Вторым ограничением является неотрицательность количества выпускаемых изделий:
хг <0 (3)
Таким образом, у нас получилась задача с оптимизируемой линейной функцией и системой ограничений из двух неравенств.
2. Если надстройка «Поиск решения» не была установлена при первоначальной установке Excel, то следует запустить процесс установки Excel повторно и выбрать только эту надстройку. После установки «Поиск решения» появится в списке доступных надстроек Excel.
3. Размещение целевой функции, исходных данных и ограничений уже было рассмотрено в пункте 1. Остановимся более подробно на цветовом оформлении.
В рассматриваемом примере на рабочих листах целесообразно использовать цветовое оформление в соответствии с таблицей 1.
Таблица 1 - Цветовое оформление рабочего листа
Назначение ячейки Цветовая заливка
Исходные данные, задающие требования и ограничения. Значения этих ячеек можно изменять, задавая другие условия, требования и ограничения. светло-желтая
Изменяемые значения (подбираемые переменные для получения оптимального решения) светло-голубая
Оптимизируемая ячейка светло-розовая
Ячейки на значения которых наложены ограничения светло-зеленая
Такое оформление помогает легче ориентироваться в данных на рабочем листе.
4. Работа в диалоговом окне «Поиск решения» ведется следующим образом:
- запускается диалоговое окно «Поиск решения» - команда «Сервис», «Поиск решения»;
- в поле установить целевую ячейку задаем ссылку на $Б$18;
- устанавливаем тип взаимосвязи между целевой ячейкой и решением путем выбора переключателя «максимальному значению»;
- в поле «Изменяя ячейки» укажем наш искомый объем производства (изменяемые в процессе поиска решения ячейки - $Б$9:$Р$9);
- для ввода ограничений нажмем кнопку «Добавить». После этого откроется диалоговое окно «Добавление ограничения». В этом окне необходимо ввести определенные нами при постановки задачи ограничения.
После ввода ограничений нажать кнопку «ОК». В результате появится заполненное диалоговое окно «Поиск решения».
- нажмем кнопку «Выполнить». По окончании поиска решения появится диалоговое окно «Результаты поиска решения».
- Выберем переключатель «Сохранить найденное значение» и нажмем кнопку «ОК».
Теперь решение, найденное с помощью надстройки «Поиск решения» находится в
ячейках $Б$9:$Р$9 рабочего листа.
5.1. Для выбора типа отчетов необходимо вернуться в окно «Поиск решения» и повторно нажать кнопку «Выполнить». В появившемся окне «Результаты поиска решений» можно выбрать три типа отчетов: результаты, устойчивость и пределы. Такие отчеты полезны для сравнения влияния на решение различных ограничений или исходных данных. Можно одновременно выбрать 2 или 3 типа отчетов с помощью мыши при нажатой клавише <СЧг1>.
Если на рабочем листе нет нагромождения информации и нет необходимости сравнивать результаты, отчет можно не создавать.
5.2. Сохранение параметров модели осуществляется с помощью кнопки «Сохранить модель» в диалоговом окне «Параметры поиска решения»:
- заполнить поля в диалоговом окне «Параметры поиска решения» значениями, которые следует сохранить;
- нажать кнопку «Сохранить модель» - при этом появится окно для указания диапазона ячеек, в котором будут сохранены параметры модели;
- на рабочем листе выделить диапазон ячеек, задающих область модели. Число выделенных ячеек должно равняться числу ограничений модели плюс три. Если выделено
недостаточное количество ячеек Excel посоветует выбрать другой диапазон. Если выделена одна ячейка, диапазон будет выбран автоматически;
- нажмите кнопку «Закрыть» в диалоговом окне «Поиск решения». Выбранный диапазон ячеек будет заполнен параметрами модели.
5.3. Еще один способ сохранить варианты решения при различных исходных данных -использовать сценарии Excel.
Сценарий Excel - это инструмент, позволяющий моделировать различные физические, экономические, математические задачи. Создав сценарий, пользователь получает возможность узнать, что произойдет с результатом, если поменять исходные значения в некоторых ячейках листа.
6. «Поиск решения» позволяет экспериментировать с различными параметрами задачи, для нахождения наилучшего результата. Эти изменения вносятся на рабочем листе решаемой задачи либо в диалоговом окне «Поиск решения». После чего запускается надстройка «Поиск решения» и оптимальное значение пересчитывается.
Если надстройка «Поиск решения» прекратила работу не найдя оптимального решения, то причинами этому могут быть:
- процесс поиска решения был прерван пользователем;
- в диалоговом окне «Параметры поиска решения» установлен флажок «Показывать результаты итераций»;
- пользователь нажал кнопку «Стоп» в режиме пошагового выполнения итераций;
- количество итераций или время поиска решения превысило максимально допустимое;
- при решении нелинейной задачи в диалоговом окне «Параметры поиска решения» установлен флажок «Линейная модель»;
- значение целевой ячейки неограниченно возрастало или убывало;
- при использовании условия целочисленности задано слишком маленькое «Допустимое отклонение» (параметр в диалоговом окне «Параметры поиска решения»);
- при решении нелинейной задачи с медленной сходимостью параметр «Сходимость» в диалоговом окне «Параметры поиска решения» имеет слишком большое значение.
Дальнейшее исследование в данном направлении предполагает использование предложенной схемы работы с надстройкой «Поиск решений» при оптимизации экономических решений и, в частности, для планирования производственной программы промышленного предприятия.
Выводы
1. Определены экономические задачи, решение которых возможно автоматизировать с помощью программы Excel. Использование для решения этих задач Microsoft Excel позволит существенно повысить качество планирования.
2. Составлена схема последовательности операций оптимизации экономических задач в программе Excel и на стандартном примере описаны ее основные элементы. Эта схема позволит пользователю, не обладающему специальными навыками работы в Excel, применять в планировании этот табличный редактор.
Перечень ссылок
1. Плакунов М.К. Планирование на малых и средним предприятиях средствами Excel / М.К.Плакунов. - СПб.: Питер, 2004. - 160с.
2. Гобарева Я.Л. Технология экономических расчетов средствами MS Excel: Учебное пособие / Я.Л.Гобарева, О.Ю. Городецкая, А.В. Золотарюк. - М.: КНОРУС, 2006. - 344с.
3. Excel для экономистов и менеджеров / А.Г. Дубина, С.С. Орлова, И.Ю. Шубина, А.В.Хромов. - СПб.: Питер, 2004. - 295с.
Рецензент: Н.Г. Белопольский
д-р экон. наук, проф., ПГТУ Статья поступила 19.02.2007
