Контрольная работа: MS Excel: надстройка "Поиск решения"
Максимизация прибыли универмага
Большой универсальный магазин собирается заказать новую коллекцию костюмов для весеннего сезона. Решено заказать 4 типа костюмов. Три типа – костюмы широкого потребления (из полиэстеровых смесей, шерстяные, хлопковые). Четвертый тип – дорогие импортные модельные костюмы из различных тканей. Имеющийся у менеджеров магазина опыт и специальные исследования позволяют оценить средние затраты рабочего времени продавцов на продажу одного костюма каждого типа, объём затрат на рекламу и площади в расчете на один костюм каждого типа. Все эти данные, а также прибыль от продажи одного костюма представлены в таблице.
| Тип костюма | Прибыль, дол | Время, час. | Реклама, у.е. | Площадь, м2 |
| Полиэстер | 35 | 0,4 | 2 | 1,00 |
| Шерсть | 47 | 0,5 | 4 | 1,50 |
| Хлопок | 30 | 0,3 | 3 | 1,25 |
| Эластик | 90 | 1,0 | 9 | 3,00 |
Предполагается, что весенний сезон будет длиться 90 дней. Магазин открыт 10 часов в день, 7 дней в неделю. Два продавца постоянно будут в отделе костюмов. Выделенная отделу костюмов площадь составляет прямоугольник 100 ´ 60 м2 . Бюджет, выделенный на рекламу всех костюмов на весенний сезон, составляет 15000 у. е.
1. Сколько костюмов каждого типа надо закупить, чтобы максимизировать прибыль?
2. Допустим, что менеджмент магазина считает необходимым закупить не менее 200 костюмов каждого типа. Как это требование повлияет на прибыль магазина?
При ответе на следующие вопросы сохраните ограничение (2).
3. Изменится ли оптимальное решение, если прибыль от продажи одного полиэстерового костюма переоценена (недооценена) на 1 у. е, на 2 у. е?
4. Обоснуйте, будет ли каждое из предлагаемых решений полезно для магазина:
отдать в распоряжение отдела костюмов 400 м2 от отдела женской спортивной одежды. Предполагается, что на этой площади магазин может получить прибыль всего лишь 750 у.е. за последующие 90 дней;
истратить дополнительно 400 у.е. на рекламу.
5. Если общее число закупленных костюмов не может превысить 5000 шт., то как такое ограничение повлияет на оптимальное решение?
Решение задачи 1.4
Обозначим через Х1 – количество костюмов из полиэстеровых смесей, Х2 – количество костюмов из шерсти, Х3 – количество костюмов из хлопка и Х4 – количество импортных костюмов.
Целевая функция, определяющая прибыль универмага имеет вид:
Ограничения задачи:
Ограничение по времени: 90´10´2=1800.
Решим задачу, с помощью табличного процессора MSExcel.
Значения переменных будем получать в ячейках B3:E3. Коэффициенты целевой функции вводим в ячейки B5:E5. Ячейка целевой функции – H5. Поместим в ней курсор, с помощью Мастера функций выберем Категорию Математические и оттуда введем СУММПРОИЗВ, в окне СУММПРОИЗВ указываем адреса массивов B3:E3 и B5:E5.
В ячейках B7:E9 вводим коэффициенты ограничений. В ячейках G7:G9 введем правые части системы ограничений, с помощью функции СУММПРОИЗВ. В Поиске решения введем направление целевой функции (максимальное значение), адреса искомых переменных B3:E3, добавим ограничения. Нажимаем кнопку Выполнить. На экране появится сообщение, что решение найдено.
1). Для получения максимальной прибыли в размере 171500 у.е. следует закупить:
500 костюмов из полиэстеровых смесей,
2000 костюмов из шерсти,
2000 костюмов из хлопка и
не закупать дорогие импортные костюмы.
2). При условии, что следует закупить не менее 200 костюмов каждого типа прибыль составит 170330 у.е., что меньше на 1170 у.е. При этом следует закупать:
733 костюма из полиэстеровых смесей,
1335 костюма из шерсти,
--> ЧИТАТЬ ПОЛНОСТЬЮ <--