WWW.DISSERS.RU

БЕСПЛАТНАЯ ЭЛЕКТРОННАЯ БИБЛИОТЕКА

   Добро пожаловать!


Pages:     || 2 | 3 |
Министерство образования Российской Федерации МАТЕМАТИЧЕСКИЕ МЕТОДЫ В ПРОИЗВОДСТВЕННОМ И ОПЕРАЦИОННОМ МЕНЕДЖМЕНТЕ Методические указания по использованию программы Excel УДК 658.5:336.24.018 Пособие соответствует государственному образовательному стандарту дисциплины «Математическая экономика» направления на подготовку специальности 0611 «Менеджмент».

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

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

Табл. 5. Ил. 36. Библиогр.: 7 назв.

Составители: Н.О. Сухарев, Е.А. Макарова Рецензент: главный бухгалтер ООО «Новатор» к.э.н., доцент Н.С. Циндин 2 СОДЕРЖАНИЕ ВВЕДЕНИЕ.......................................................................................................... 4 Глава 1.................................................................................................................. 8 ЗАДАЧА ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ............................... 8 1.1. Пример.................................................................................................... 8 1.2. Решение с помощью программы Excel................................................ 9 Глава 2............................................................................................................ 21 ЦЕЛОЧИСЛЕННАЯ ЗАДАЧА ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ........................................................................... 21 2.1. Решение с помощью программы Excel.............................................. 22 Глава 3............................................................................................................ ТРАНСПОРТНАЯ ЗАДАЧА................................................................... 3.1. Пример.................................................................................................. 3.2. Решение с помощью программы Excel.............................................. Глава 4............................................................................................................ ЗАДАЧА О НАЗНАЧЕНИЯХ.................................................................. 4.1. Пример.................................................................................................. 4.2. Решение с помощью программы Excel.............................................. СПИСОК ЛИТЕРАТУРЫ................................................................................. ВВЕДЕНИЕ Известно, что решения, обоснованные математически, на 5-15% - эффективнее принимаются лишь с опорой на опыт и интуицию.

Самым мощным инструментом, применяемым в сфере управления бизнесом, является линейное программирование.

Понятие линейное программирование (Linear Program ming LP) включает несколько взаимосвязанных математических методов, которые используются для оптимального распределения ограниченных ресурсов предприятия между его конкурирующими потребностями. Наиболее широко линейное программирование используется в методах, объединенных единым названием математические методы оптимизации.

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

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

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

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

Модель линейного программирования Формально выражаясь, задача линейного программирования связана с оптимизацией процесса, в ходе которого отбираются неотрицательные искомые переменные X1, Х2,..., Ху, используемые затем для максимизации (или минимизации) целевой функции в следующей форме.



Максимизировать (минимизировать) целевую функцию Z = C1X1 + C2X2 +... + Cn Xn при условии ограничений на количество ресурсов, выраженных в таком виде:

A11X1 + A12 X +... + A1n X B1, 2 n A21X1 + A22 X +... + A2n X B2, 2 n...

An1X1 + An2 X +... + Amn X Bm, 2 n где Сn, Amn, и Вm — заданные постоянные величины.

В зависимости от типа задачи ограничения могут указываться также с использованием знака равенства (=) или знака "больше или равно" ().

Для такого обоснования используются методы оптимизации [1,3] и специальное программное обеспечение, к которому относятся средства поиска решений и анализа данных в программе Microsoft Excel [2,4], Программа Microsoft Excel входит в обычный комплект программного обеспечения современных персональных компьютеров. Она обладает большим набором средств для прогнозирования и управления. К ним относятся:

• подбор параметров;

• поиск решений;

• таблицы подстановки.

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

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

Средство поиска решения в программе Excel Программа Excel (версии 5.0 и выше) включает средство поиска решения, позволяющее решать любые задачи математического программирования, которое должно быть установлено при инсталляции программы Excel на вашем компьютере. Чтобы убедиться в этом, раскройте меню Сервис — там должна быть команда Поиск решения. Если ее нет, выбе рите в меню Сервис команду Надстройки и в раскрывшемся списке установите флажок рядом с названием надстройки Поиск решения — соответствующая команда появится в меню Сервис. Если же в списке такой надстройки нет, повторите инсталляцию Excel, установив при этом и надстройку Поиск решения.

Глава ЗАДАЧА ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ 1.1. Пример Требуется определить план выпуска четырех видов продукции, обеспечивающий максимальную прибыль от ее реализации. На изготовление этой продукции расходуются трудовые ресурсы, сырье и финансы. С учетом рыночного спроса и производственно-технологических возможностей заданы предельные границы выпуска каждого вида продукции. Эти границы, наличие и нормы расхода ресурсов, а также маржинальная прибыль (разность между выручкой и переменными издержками) на единицу продукции приведены в таблице:

Ресурсы Прод. 1 Прод. 2 Прод. 3 Прод. 4 Наличие Трудовые 1 2 1 2 Сырье 7 4 5 4 Финансы 5 7 9 8 Прибыль 70 60 100 140 – Нижи. гр. 3 1 1 Верхи, гр. 5 – 3 Обозначив количество выпускаемых изделий через Х1, Х2, Х3,Х4, а целевую функцию (валовую маржинальную прибыль)— через F, построим математическую модель задачи:

F = 70x1 + 60x2 +110x3 +140x4 max, x1 + 2x2 + x3 + 2x4 19, 3 x1 5, 7x1 + 4x2 + 5x3 + 4x4 80, 1 x2, 5x1 + 7x2 + 9x3 + 8x4 100, 1 x3 3, 2 x4 4.

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

1.2. Решение с помощью программы Excel Ввод числовых данных Решим задачу, сформулированную в разделе 1.1. Для этого на рабочем листе введем ее числовые данные (рис. 1.1).

В первой строке таблицы находится заголовок, во второй — наименования продуктов. Третья строка отведена для оптимального решения, которое после вычислений появится в ячейках ВЗ:ЕЗ (в жирной рамке). В четвертой строке в ячейках В4:Е4 заданы коэффициенты целевой функции, а ячейка F4, в рамке, зарезервирована для вычисления значения целевой функции. Строки с 6-й по 15-ю содержат коэффициенты, знаки и правые части ограничений. В столбце Лев. часть после вычислений появятся левые части ограничений, а в столбце Разница — разность правых и левых частей.

Рис. 1.1. Ввод данных для решения задачи линейного программирования Предложенная форма таблицы удобна не только для выполнения расчетов, но и для документирования результатов моделирования, а также обмена идеями и улучшения взаимопонимания при работе «в команде»— группе лиц, ответственных за принятие решений. Наглядность таблицы обеспечивается использованием заголовков, полужирного шрифта, подчеркивания текста и центрирования. Денежные величины следует представлять в денежном или финансовом формате (с указанием символа используемой валюты).

Оформление таблицы облегчают кнопки панели форматирования, в частности: Объединить и поместить в центре (для центрирования заголовка, занимающего несколько ячеек), Денежный формат (для задания финансового формата), Увеличить разрядность или Уменьшить разрядность (для задания нужного числа дробных знаков). Чтобы отобразить знак > или <, наберите знак > или < и затем подчеркните его.

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

Ввод формул Формулы и ячейки, в которые их следует ввести, указаны в таблице:

Ячейка Формула Копировать в F4 =СУММПРОИЗВ($B$3:$E$3;B4:E4) F6:FI6 =H6-F6 I7:I8; I13:II9 =F9-H9 I10:IЧтобы ввести, например, формулу для вычисления целевой функции:





1. Укажите ячейку F4.

2. В строке формул щелкните кнопку Изменить формулу (со знаком равенства). Раскроется панель формул.

3. В левой части строки формул раскройте список функций и щелкните имя функции СУММПРОИЗВ. Если его там нет, выберите в списке пункт Другие функции. В открывшемся окне в категории Математические найдите функцию СУММПРОИЗВ и дважды щелкните ее имя. Панель формул примет вид, позволяющий задать аргументы этой функции (рис.

1.2).

4. Введите аргументы функции СУММПРОИЗВ.

5. Щелкните кнопку ОК.

Сразу после ввода формулы в ячейке F4 появится 0, так как формула вычисляется с нулевыми значениями переменных (ячейки ВЗ:ЕЗ пока пусты).

Рис. 1.2. Ввод формулы целевой функции задачи линейного программирования Функций СУММПРОИЗВ позволяет вычислить сумму произведений двух массивов, первый из которых содержит значения переменных, а второй — коэффициенты целевой функции. Чтобы указать соответствующие диапазоны, можно воспользоваться кнопками свертывания, расположен ными справа от полей ввода. Они позволяют временно убрать панель формул с экрана, чтобы удобнее было выделять диапазон на листе. Закончив выделение, щелкните кнопку снова для восстановления панели.

Ссылка на первый диапазон должна быть абсолютной, со знаками доллара. перед каждой буквой и цифрой: $В$3:$Е$3 (чтобы изменить относительную ссылку на абсолютную, нажмите клавишу F4 непосредственно после ввода этой ссылки; если это не сделать сразу, то в дальнейшем ссылку сначала понадобится выделить и лишь затем нажать клавишу F4).

Ссылка на второй диапазон В4:Е4, напротив, должна быть относительной:

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

После ввода формулы для вычисления целевой функции необходимо задать формулы левых частей ограничений. С этой целью скопируйте формулу из ячейки F4 в ячейки F6:F15. Чтобы копировалась только сама формула (без формата ячейки, к которому относятся, например, рамка и символ используемой валюты) можно воспользоваться специальной вставкой. Для этого щелкните ячейку F4 правой кнопкой мыши и в контекстном меню выберите команду Копировать, затем выделите диапазон F6:F15, щелкните его правой кнопкой мыши и в контекстном меню выберите команду Специальная вставка. В группе Вставить открывшегося окна выберите параметр формулы и щелкните кнопку ОК. Чтобы отменить движущуюся границу ячейки F4, нажмите клавишу Esc.

При копировании относительная ссылка В4:Е4 будет меняться, указывая на массивы коэффициентов соответствующих ограничений, а абсолютная — $В$3:$Е$3 — останется неизменной. Для проверки формулы в ячейке достаточно дважды щелкнуть эту ячейку. Формула будет отображена на экране, ссылки на ячейки— выделены цветом, а сами ячейки— обведены рамками соответствующих цветов (после просмотра нажмите клавишу Esc). Чтобы отображались все введенные формулы, выберите в меню Сервис команду Параметры и в открывшемся окне на вкладке Вид в группе Параметры окна установите флажок Формулы. Если таблицу с формулами необходимо напечатать, подберите ширину ее столбцов в соответствии с содержимым ячеек, как это было описано ранее.

Аналогично, с помощью копирования, вводятся формулы в ячейки I6:I15 для вычисления разницы правых и левых частей ограничений. Для ограничений со знаком < из правой части ограничения вычитается левая, а для ограничений со знаком >— наоборот. Таким образом, если ограничения выполняются и план допустим, то разница всегда неотрицательна.

После ввода формул вы можете подставлять в ячейки ВЗ:ЕЗ любые значения (количество выпускаемой продукции), получая соответствующую величину прибыли в ячейке F4 и объем израсходованных ресурсов в столбце Лев.часть. Столбец Разница покажет, допустим ли соответствующий производственный план, какие ресурсы дефицитны (для них разница равна нулю) и каков остаток недефицитных ресурсов (он равен положительной разнице).

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

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

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

• Равной — для выбора варианта оптимизации значения целевой ячейки (максимизация, минимизация или подбор заданного числа).

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

В нашем примере это ячейки, отведенные для значений переменных.

• Ограничения — для отображения списка ограничений поставленной задачи.

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

Добавление ограничений Дня ввода ограничений следует нажать кнопку Добавить, откры вающую окно Добавление ограничения (рис. 1.4.).

Рис. 1.4. Добавление ограничения В этом окне — два поля ввода:

• Ссылка на ячейку— для указания ячейки или диапазона ячеек, на значения которых необходимо наложить ограничение («левая часть ограничения»).

Pages:     || 2 | 3 |










© 2011 www.dissers.ru - «Бесплатная электронная библиотека»

Материалы этого сайта размещены для ознакомления, все права принадлежат их авторам.
Если Вы не согласны с тем, что Ваш материал размещён на этом сайте, пожалуйста, напишите нам, мы в течении 1-2 рабочих дней удалим его.