|
Содержание работы или список заданий
|
вариант 5.
Задание№1
Обучающий пример: Использование надстройки MS EXCEL «Поиск решения» для нахождения значения, при котором заданная величина максимальна.
Решение задачи рассматривается в MS EXCEL 2007.
Рассмотрим типичную модель сбыта, отражающую увеличение числа продаж от заданной величины (обусловленной, например, затратами на персонал) при увеличении затрат на рекламу и уменьшении прибыли.
Этот пример приводят разработчики EXCEL в файле SOLVSAMP.xls.
Предположим, что объемы продажи товара колеблются в зависимости от сезона: максимальны в 4 квартале (коэффициент сезонности – 1,2) и принимают наименьшее значение в 3 квартале (коэффициент сезонности 0,8). Изначально затраты на рекламу в каждый квартал запланированы в объеме 10 000 руб., затраты на торговый персонал планируются следующие: 8 000 руб. – в 1 и 2 квартале, 9 000 руб. – в 3 и 4 квартале.
Известны цена изделия и затраты на изделие.
Поясним содержимое ячеек таблицы (в первом столбце – номер строки; во втором – содержимое; в третьем – пояснения.)
Пусть эмпирически получена формула зависимости числа продаж от коэффициента сезонности и расходов на рекламу:
Для вычисления планируемого числа продаж в 1 квартал введем формулу в ячейку B5:
=35*B3*(B11+3000)^0,5
Заполним формулами оставшуюся часть таблицы, при этом учтем, что:
Выручка от реализации: произведение числа продаж на цену изделия.
Затраты на сбыт: произведение числа продаж и затрат на изделие.
Валовая прибыль: разность выручки от реализации и затрат на сбыт.
Косвенные затраты в фонд корпорации: 15% выручки от реализации.
Суммарные расходы: затраты на персонал, рекламу и косвенные затраты.
Производственная прибыль: валовая прибыль за вычетом суммарных затрат. Норма прибыли: отношение прибыли и выручки от реализации.
Поиск решения поможет определить необходимость увеличения рекламного бюджета или его перераспределения с учетом сезонной поправки.
Пусть, например, требуется определить расходы на рекламу для получения наибольшей прибыли в первом квартале. Необходимо добиться наибольшей прибыли, изменяя затраты на рекламу. Для этого:
1. Перейдите на вкладку ленты Данные, выполните команду Поиск решения.
Примечание: Для подключения надстройки Анализ данных необходимо:
Excel 2003, ХР: пункт меню Сервис → Надстройки → установить флажок Поиск решения;
Excel 2007: нажать последовательно кнопки Офис (в верхнем левом углу окна Excel) →Параметры Excel → Надстройки → Перейти → установить флажок Поиск решения;
Excel 2010: вкладка ленты Файл → Параметры → Надстройки → Перейти → установить флажок Поиск решения.
2. Задайте B15 в качестве результирующей ячейки (прибыль за первый квартал) Выберите поиск максимального значения и укажите в качестве изменяемой ячейки B11 (расходы на рекламу в первом квартале).
3. Запустите процесс поиска решения. В процессе решения задачи в строке состояния будут отображаться сообщения. Через некоторое время появится сообщение о том, что решение найдено.
В соответствии с найденным решением, затратив 17 093 руб. на рекламу в первом квартале можно получить наибольшую прибыль, которая составит 15 093 руб.
Отчет по результатам будет сформирован на новом листе. В Excel 2003 и Excel 2007 вид отчета следующий:
Согласно отчету по результатам, увеличив расходы на рекламу в 1 квартале с 10 000 руб. до 11 098 руб. возможно увеличение прибыли за 1 квартал от 15 761 руб. до 17 181 руб.
Сохранение модели задачи
При выполнении командыСохранить меню Файл последние заданные параметры задачи будут сохранены вместе с листом Excel. Однако, для листа Excel может быть определено несколько задач, если сохранять их по отдельности с помощью командыСохранить модель в диалоговом окне Параметры поиска решения.
Каждая модель задачи определяется ячейками и ограничениями, заданными в этом диалоговом окне. При сохранении модели предлагается выбрать интервал, включающий активную ячейку, используемый для сохранения модели. В интервал входят ячейки ограничений и две дополнительные ячейки. Убедитесь в том, что этот интервал на листе Excel не содержит данных. Порядок действий:
1. Перейдите на вкладку ленты Данные, выполните команду Поиск решения
2. Выполните команду Параметры.
3. Нажмите кнопкуСохранить модель.
4. В поле задания области модели укажите интервал ячеек H15:H17 и нажмите кнопку OK.
Обратите внимание на сохраненную информацию в ячейках H15:H17:
Таким образом, мы полностью сохранили модель: верхняя ячейка содержит информацию о ссылке на целевую ячейку и а том, что значение целевой функции необходимо максимизировать; следующая ячейка хранит адрес изменяемой ячейки; третья ячейка диапазона – это рабочая информация для Поиска решения.
Нахождение значения за счет изменения нескольких величин
Вернемся к исходным условиям задачи: изначально планировалось, что расходы на рекламу в каждом квартале будут равны 10 000 руб. Исправьте соответствующим образом данные в диапазоне B11:E11.
Имеется возможность поиска наибольшего или наименьшего значения для заданной величины, одновременно изменяя несколько других величин.
Например, можно определить бюджет на рекламу в каждом квартале, соответствующий наибольшей годовой прибыли. Поскольку задаваемая в 3 строке сезонная поправка входит в расчет числа продаж (строка 5) в качестве сомножителя, целесообразно увеличить затраты на рекламу в 4 квартале, когда прибыль от продаж наибольшая и уменьшить, соответственно, в 3 квартале. Поиск решения позволит найти наилучшее распределение затрат на рекламу по кварталам.
Для этого необходимо выполнить следующие действия:
1. Перейдите на вкладку ленты Данные, выполните команду Поиск решения. Задайте F15 (общая прибыль в год) в качестве результирующей ячейки.
2. Выберите поиск максимального значения и задайте в качестве изменяемых ячеек B11:E11 (расходы на рекламу в каждом квартале).
3. Сохраните модель, задав поле задания области модели интервал ячеек J15:J17.
4. Запустите процесс поиска решения. Сделайте запрос о выводе отчета по результатам.
Рассмотренная задача является нелинейной задачей оптимизации средней степени сложности; то есть поиск значения уравнения с четырьмя неизвестными в ячейках с B11 по E11. (Нелинейность уравнения связана с операцией возведения в степень в формуле строки 5). Результат этой оптимизации без ограничений говорит о возможности увеличения годовой прибыли до 79 706 руб. при годовых затратах на рекламу 89 706 руб. Наиболее близкие к жизни модели учитывают также ограничения, накладываемые на те или иные величины. Эти ограничения могут относиться к ячейкам результата, ячейкам изменяемых данных или другим величинам, используемых в формулах для этих ячеек.
Добавление ограничения
Вернемся к исходным условиям задачи: изначально планировалось, что расходы на рекламу в каждом квартале будут равны 10 000 руб. Исправьте соответствующим образом данные в диапазоне B11:E11.
Итак, бюджет покрывает расходы на рекламу и обеспечивает получение прибыли, однако, наблюдается тенденция к уменьшению эффективности вложений. Поскольку нет гарантии, что данная модель зависимости прибыли от затрат на рекламу будет работать в следующем году (учитывая существенное увеличение затрат), целесообразно ввести ограничение расходов, связанных с рекламой. Предположим, расходы на рекламу за четыре квартала не должны превышать 40 000 руб. Добавим рассмотренную задачу соответствующее ограничение. Для этого:
1. Перейдите на вкладку ленты Данные, выполните команду Поиск решения и нажмите кнопкуДобавить.
2. Задайте ссылку на ячейку ограничения F11 (общие расходы на рекламу). Содержимое этой ячейки не должно превышать 40 000 руб.
3. Требуется установить по умолчанию отношение <= (меньше или равно). В поле, расположенном справа, введите число 40 000.
4. Сохраните модель, задав поле задания области модели интервал ячеек J15:J18. В данном случае выделяется 4 ячейки, так как необходимо дополнительно сохранить информацию о добавленном ограничении.
5. Нажмите кнопку OK, а затем - Выполнить. Сделайте запрос о выводе Отчета по результатам.
В Excel 2010 полученный Отчет по результатам выглядит так:
В соответствии с найденным решением на рекламу будет выделено 7273 руб. в 1 квартале, 12 346 руб. во втором квартале, 5 117руб. в 3 квартале и 15 263 руб. - в 4. Прибыль увеличится с 69 662 руб. до 71 447 руб. без увеличения бюджета на рекламу.
Изменение ограничения
Вернемся к исходным условиям задачи: изначально планировалось, что расходы на рекламу в каждом квартале будут равны 10 000 руб. Исправьте соответствующим образом данные в диапазоне B11:E11.
Поиск решения позволяет экспериментировать с различными параметрами задачи, для определения наилучшего варианта решения. Например, изменив ограничения, можно оценить изменение результата. Попробуйте на листе примера изменить ограничение на рекламный бюджет с 40 000 руб. до 50 000 руб. и посмотреть, как изменится при этом общая прибыль.
1. Перейдите на вкладку ленты Данные, выполните команду Поиск решения
2. В списке Ограничения: уже задано ограничение $F$11<=40000.
3. Нажмите кнопкуИзменить. Измените в поле значение 40000 на 50000.
4. Сохраните модель, задав поле задания области модели интервал ячеек L15:L17.
5. Нажмите кнопку OK, а затем - Выполнить.
6. В диалоговом окне Результаты поиска решения сделайте запрос о выводе отчета по результатам и нажмите кнопку OK, чтобы сохранить результаты.
Найденное решение соответствует прибыли 74817 руб., что на 3370 руб. больше прежнего значения 71 447 руб.
Для большинства предприятий увеличение капиталовложений на 10 000 руб., приносящее 3 370 руб. (т.е. 33,7 % возврат вложений) является оправданным. Прибыль при таком решении будет на 4 889 руб. меньше, по сравнению с задачей без ограничений, однако при этом требуется и на 39 706 руб. капиталовложений меньше.
Загрузка модели задачи
Рассмотрим, как загружаются сохраненные модели. Загрузим первую сохраненную модель, информация о которой содержится в ячейках H15:H17.
Порядок действий:
1. Перейдите на вкладку ленты Данные, выполните команду Поиск решения
2. Выполните команду Параметры.
3. Нажмите кнопкуЗагрузить модель.
4. В поле задания области модели укажите интервал ячеек H15:H17 и нажмите кнопку OK. Восстановите исходное выделение. Обратите внимание, параметры решения в окне Поиск решения изменились до тех значений, которые использовались для решения первой задачи. Нажмите на кнопкуВыполнить.
Задание№2
Задача формирования оптимальной производственной программы
Решение задачи рассматривается в MS EXCEL 2010.
Рассмотрим поэтапное решение задачи на примере. В лабораторной работе необходимо решить подобную задачу.
Задача: Петр Иванов владеет небольшим мебельным магазином. Он изготавливает три типа столов: А, В, и С. При изготовлении каждого стола необходимо затратить определенное время на производство составных частей, сборку и покраску. Иванов может продать все собранные столы. Кроме того, модель С можно продавать без покраски. Иванов нанял несколько рабочих на условиях неполной занятости, поэтому время, затраченное на изготовление столов, может от недели к неделе меняться. Используя имеющиеся данные, определите ассортимент выпускаемой продукции, максимизирующий прибыль Иванова на следующей неделе.
Модель А В С Неокраш. столы С Ресурс рабочего времени
Изготовление частей, ч 3 1 4 4 150
Сборка, ч 4 2 5 5 200
Окраска, ч 5 5 4 0 300
Удельная прибыль, долл 25 20 50 30
Решение задачи предполагает выполнение шести последовательных этапов:
1 этап. Выбор управляемых переменных.
Управляемые переменные - те экономические показатели, которые являются неизвестными задачи.
В нашем случае - объемы выпуска продукции:
хi – объем выпуска i-того вида продукции, i = 1, 2, 3, 4.
2 этап. Анализ существенных ограничений.
Следует учесть только те факторы, которые оказывают существенное влияние на принимаемые решения.
В оптимизационных моделях различают три типа ограничений:
- ресурсные;
- плановые;
- технологические соотношения между группами управляемых переменных.
В нашем случае - ограничения ресурсные.
3 этап. Выбор целевой функции.
Целевая функция в задачах оптимизации – показатель эффективности, который следует оптимизировать.
В нашем случае – это общая прибыль, которую необходимо максимизировать.
4 этап. Построение математической модели.
На этом этапе в виде функции записывается цель задачи и в виде неравенств - ее ограничения.
В нашем случае, х1 – объем выпуска столов А, прибыль за единицу для данного вида изделия – $25, тогда 25∙х1 – это ожидаемая денежная прибыль от реализации всех произведенных столов А. Аналогично определяются ожидаемые денежные суммы, получаемые от реализации всех остальных изделий.
В результате можно записать математическое выражение для расчета общей прибыли Z, которую получит магазин после реализации продукции:
Z = 25 ∙x1+20 ∙x2+50 ∙x3+30 ∙x4max
Запишем выражение для расчета ожидаемых затрат времени для каждой операции.
На изготовление частей стола А затрачивается 3 часа рабочего времени, тогда на весь ожидаемый выпуск столов А (х1) будет затрачено 3∙х1 часов. Для изготовления столов B будет затрачено 1∙х2 часов и т.п. Т.о. выражение для расчета времени, требуемого на изготовление частей столов всех типов, имеет вид:
3 ∙x1+1 ∙x2+4 ∙x3+4 ∙x4 .
Поскольку затраты времени на изготовление частей не могут превысить имеющийся фонд, то:
3 ∙x1+1 ∙x2+4 ∙x3+4 ∙x4 ≤ 150
Рассуждая аналогично, запишем ограничения по использованию фонда на сборку и покраску столов.
Т.о. нами сформированы ограничения задачи:
3 ∙x1+1 ∙x2+4 ∙x3+4 ∙x4 ≤ 150
4 ∙x1+2 ∙x2+5 ∙x3+5∙x4 ≤ 200
5 ∙x1+5 ∙x2+4 ∙x3 ≤ 300
5 этап. Выбор метода решения и численное решение задачи.
Оптимизационная задача – это всегда задача математического программирования. В простейшем случае (как, например, в решаемой задаче) – это задача линейного программирования, для решения которой используется симплексный метод.
К сожалению, симплексный метод не всегда позволяет получить целочисленные значения управляемых переменных, поэтому в тех случаях, когда это необходимо, на переменные дополнительно накладываются ограничения целочисленности.
Однако в тех случаях, когда округление результатов не несет существенной погрешности, есть смысл свести задачу к задаче линейного программирования и использовать для ее решения симплексный метод, поскольку в этом случае исследователь получает гораздо более богатый материал для дальнейшего анализа.
Этапы решения задачи в Excel
Таблица с исходными данными задачи расположена на листе Excel и имеет вид:
Для получения численного решения задачи сформированную нами экономико-математическую модель следует записать с использованием адресов ячеек электронной таблицы.
1. Резервируются пустые ячейки под управляемые переменные (ячейки B8:E8):
Так, ячейка В8 предназначена для вычисления количества столов А (переменная х1), в ячейке С8 будет вычислено количество столов В и т.д.
2. Согласно математической модели в выбранные ячейки записываются формулы для расчета затрат ресурсов и для расчета целевой функции.
В данном случае для расчетов удобно использовать стандартную математическую функцию Excel: СУММПРОИЗВ(). Данная функция почленно перемножает два диапазона и складывает результаты умножения.
3. В Excel 2010 переходим на вкладку ленты Данные Поиск решения. (Если на ленте нет такой команды, то подключите надстройку Поиск решения выполнив следующую последовательность действий: Файл Параметры Надстройки Перейти Поиск решения).
4. В полеОптимизировать целевую функцию диалогового окна Поиск решения вводится адрес ячейки, содержащей значение целевой функции. В нашем случае, это ячейка B15, содержащая формулу для вычисления общей прибыли, которую по условиям задачи необходимо максимизировать. Опции областиДо: диалогового окна Поиск решения позволяют задать тип оптимизации. В строке вводаИзменяя значение переменных укажем диапазон ячеек с управляемыми переменными (ячейки B8:E8)
5. Для добавления ограничений необходимо нажать на кнопкуДобавить справа от окна Ограничения.
Нажав на ОК, вы вернетесь в окно Поиск решения. Далее в окне Поиск решения:
• Укажите в спискеВыберите метод решения:Поиск решения линейных задач симплекс-методом.
• Проверьте, что флажокСделать переменными без ограничений неотрицательными установлен. Таким образом вы укажете, что переменные задачи (в нашем случае это объемы выпуска изделий) – величины неотрицательные.
Теперь в диалоговом окне Поиск решения щелкните на кнопкеНайти решение. Появится окно диалога, в котором необходимо сделать запрос о выводе результатов решения и устойчивости решения.
Будут сформированы два новых листа: Отчет по результатам и Отчет по устойчивости.
|