Пример содержания отчета по лабораторной работе №2 «Решение однокритериальной статической задачи в условиях определенности»
- Исходные данные:
Предприятие выпускает восемь видов товаров, реализуемых по известным ценам (таблица П1). Для производства указанных товаров используется 15 видов ресурсов (таблица П2). Рассматривалась проблема недостаточности дохода предприятия. Целью решения задачи является определение такого количество товаров каждого вида, которое максимизировало бы доход предприятия. Поэтому в качестве критерия рассматривается величина дохода от реализации все произведенной продукции.
- Решение:
Задача представляет собой разновидность производственной задачи или задачи распределения ресурсов. В рассматриваемом примере неконтролируемые параметры были выбраны случайным способом. Математическая запись задачи имеет вид:
.
При этом:
С помощью Excel исходные данные оформлены в виде таблиц П1 и П2:
Таблица П1. Выпускаемая продукция
| Товары | Товар 1 | Товар 2 | Товар 3 | Товар 4 | Товар 5 | Товар 6 | Товар 7 | Товар 8 | Доход |
| Количество (шт.) | |||||||||
| Цена (руб.) |
Таблица П2. Ресурсы
| Ресурсы | Товар 1 | Товар 2 | Товар 3 | Товар 4 | Товар 5 | Товар 6 | Товар 7 | Товар 8 | Расход ресурса | Знак нера-венства | Наличие |
| Ресурс 1 (ед. измерения) | <= | ||||||||||
| Ресурс 2 (ед. измерения) | 0,5 | 0,7 | 0,8 | 0,3 | 0,6 | <= | |||||
| Ресурс 3 (ед. измерения) | <= | ||||||||||
| Ресурс 4 (ед. измерения) | <= | ||||||||||
| Ресурс 5 (ед. измерения) | <= | ||||||||||
| Ресурс 6 (ед. измерения) | <= | ||||||||||
| Ресурс 7 (ед. измерения) | <= | ||||||||||
| Ресурс 8 (ед. измерения)) | 0,6 | 0,9 | 0,5 | 0,8 | 0,4 | 0,2 | 0,7 | <= | |||
| Ресурс 9 (ед. измерения) | <= | ||||||||||
| Ресурс 10 (ед. измерения) | <= | ||||||||||
| Ресурс 11 (ед. измерения) | 0,22 | 0,35 | 0,45 | 0,12 | 0,05 | 0,19 | 0,25 | 0,36 | <= | ||
| Ресурс 12 (ед. измерения) | <= | ||||||||||
| Ресурс 13 (ед. измерения) | <= | ||||||||||
| Ресурс 14 (ед. измерения) | <= | ||||||||||
| Ресурс 15 (ед. измерения) | <= |
С помощью функции СУММПРОИЗВ() в ячейке Доход рассчитана величина дохода (таблица П1), а также расход ресурсов (таблица П2) (первоначально они равны нулю, поскольку вектор количества товара еще не подобран).
После выполнения программы надстройки Поиск решения, где в качестве целевой функции была задана ячейка Доход, в качестве изменяемых параметров ячейки Количество (таблица П1), добавлены ограничения, а также во вкладке Параметры был установлен флажок Линейная модель в результате расчетов получаем:
Таблица П3. Оптимальный выпуск продукции
| Товары | Товар 1 | Товар 2 | Товар 3 | Товар 4 | Товар 5 | Товар 6 | Товар 7 | Товар 8 | Доход |
| Количество (шт.) | 13,93127 | 3,200906 | 5,386733 | 0,599527 | 20,1504 | ||||
| Цена (руб.) | 1034,812 |
При этом ресурсы расходуются в следующем количестве:
Таблица П4. Расход ресурсов на выпуск продукции
| Ресурсы | Товар 1 | Товар 2 | Товар 3 | Товар 4 | Товар 5 | Товар 6 | Товар 7 | Товар 8 | Расход ресурса | Знак нера-венства | Наличие |
| Ресурс 1 (ед. измерения) | <= | ||||||||||
| Ресурс 2 (ед. измерения) | 0,5 | 0,7 | 0,8 | 0,3 | 0,6 | 13,69 | <= | ||||
| Ресурс 3 (ед. измерения) | 546,25 | <= | |||||||||
| Ресурс 4 (ед. измерения) | <= | ||||||||||
| Ресурс 5 (ед. измерения) | 31,73 | <= | |||||||||
| Ресурс 6 (ед. измерения) | <= | ||||||||||
| Ресурс 7 (ед. измерения) | 351,68 | <= | |||||||||
| Ресурс 8 (ед. измерения)) | 0,6 | 0,9 | 0,5 | 0,8 | 0,4 | 0,2 | 0,7 | 9,12 | <= | ||
| Ресурс 9 (ед. измерения) | 368,90 | <= | |||||||||
| Ресурс 10 (ед. измерения) | 620,80 | <= | |||||||||
| Ресурс 11 (ед. измерения) | 0,22 | 0,35 | 0,45 | 0,12 | 0,05 | 0,19 | 0,25 | 0,36 | 9,90 | <= | |
| Ресурс 12 (ед. измерения) | <= | ||||||||||
| Ресурс 13 (ед. измерения) | 669,27 | <= | |||||||||
| Ресурс 14 (ед. измерения) | 69,20 | <= | |||||||||
| Ресурс 15 (ед. измерения) | <= |
При решении задачи средствами пакета Excel сформированы отчеты по результатам, по устойчивости и по пределам.
Отчет по результатам (Таблица П5) содержит начальные (Исходно) и конечные (Результат) значения целевой функции и изменяемых ячеек, а также сводку результатов использования ресурсов. В столбце Статус символами связанное или несвязанное обозначены соответственно полное или неполное использование соответствующего ресурса. В рассматриваемом примере полностью израсходованы Ресурс 1,4,6,12,15. Установлено, что следует обратить внимание на нерациональное управление запасами ресурсов 2,5,8,11 и 14, которые заказываются в гораздо большем количестве, чем требуется для производства продукции.
Таблица П5. Отчет по результатам
| Microsoft Excel 11.0 Отчет по результатам | |||||||
| Рабочий лист: [Книга1]Лист1 | |||||||
| Отчет создан: 24.01.2008 15:13:45 | |||||||
| Целевая ячейка (Максимум) | |||||||
| Ячейка | Имя | Исходное значение | Результат | ||||
| R3C10 | цена доход | 1034,81178 | |||||
| Изменяемые ячейки | |||||||
| Ячейка | Имя | Исходное значение | Результат | ||||
| R2C2 | количество товар 1 | 13,93127316 | |||||
| R2C3 | количество товар 2 | 3,200905723 | |||||
| R2C4 | количество товар 3 | ||||||
| R2C5 | количество товар 4 | 5,386733224 | |||||
| R2C6 | количество товар 5 | 0,599526554 | |||||
| R2C7 | количество товар 6 | ||||||
| R2C8 | количество товар 7 | 20,15039625 | |||||
| R2C9 | количество товар 8 | ||||||
| Ограничения | |||||||
| Ячейка | Имя | Значение | Формула | Статус | Разница | ||
| R7C10 | ресурс 1 расход | R7C10<=R7C12 | связанное | ||||
| R8C10 | ресурс 2 расход | 13,69551513 | R8C10<=R8C12 | не связан. | 51,30448487 | ||
| R9C10 | ресурс 3 расход | 546,252959 | R9C10<=R9C12 | не связан. | 103,747041 | ||
| R10C10 | ресурс 4 расход | R10C10<=R10C12 | связанное | ||||
| R11C10 | ресурс 5 расход | 31,72987855 | R11C10<=R11C12 | не связан. | 288,2701214 | ||
| R12C10 | ресурс 6 расход | R12C10<=R12C12 | связанное | ||||
| R13C10 | ресурс 7 расход | 351,6840778 | R13C10<=R13C12 | не связан. | 248,3159222 | ||
| R14C10 | ресурс 8 расход | 9,123610539 | R14C10<=R14C12 | не связан. | 480,8763895 | ||
| R15C10 | ресурс 9 расход | 368,9024547 | R15C10<=R15C12 | не связан. | 331,0975453 | ||
| R16C10 | ресурс 10 расход | 620,8033141 | R16C10<=R16C12 | не связан. | 129,1966859 | ||
| R17C10 | ресурс 11 расход | 9,899180476 | R17C10<=R17C12 | не связан. | 210,1008195 | ||
| R18C10 | ресурс 12 расход | R18C10<=R18C12 | связанное | ||||
| R19C10 | ресурс 13 расход | 669,2749074 | R19C10<=R19C12 | не связан. | 330,7250926 | ||
| R20C10 | ресурс 14 расход | 69,20093145 | R20C10<=R20C12 | не связан. | 380,7990685 | ||
| R21C10 | ресурс 15 расход | R21C10<=R21C12 | связанное | ||||
| R2C2 | количество товар 1 | 13,93127316 | R2C2>=0 | не связан. | 13,93127316 | ||
| R2C3 | количество товар 2 | 3,200905723 | R2C3>=0 | не связан. | 3,200905723 | ||
| R2C4 | количество товар 3 | R2C4>=0 | связанное | ||||
| R2C5 | количество товар 4 | 5,386733224 | R2C5>=0 | не связан. | 5,386733224 | ||
| R2C6 | количество товар 5 | 0,599526554 | R2C6>=0 | не связан. | 0,599526554 | ||
| R2C7 | количество товар 6 | R2C7>=0 | связанное | ||||
| R2C8 | количество товар 7 | 20,15039625 | R2C8>=0 | не связан. | 20,15039625 | ||
| R2C9 | количество товар 8 | R2C9>=0 | связанное | ||||
Таблица П6. Отчет по устойчивости
| Microsoft Excel 11.0 Отчет по устойчивости | |||||||
| Рабочий лист: [Книга1]Лист1 | |||||||
| Отчет создан: 24.01.2008 15:13:46 | |||||||
| Изменяемые ячейки | |||||||
| Результ. | Нормир. | Целевой | Допустимое | Допустимое | |||
| Ячейка | Имя | значение | стоимость | Коэффициент | Увеличение | Уменьшение | |
| R2C2 | количество товар 1 | 13,93127316 | 0,577405187 | 1,223531802 | |||
| R2C3 | количество товар 2 | 3,200905723 | 1,442382813 | 2,075922819 | |||
| R2C4 | количество товар 3 | -11,19164265 | 11,19164265 | 1E+30 | |||
| R2C5 | количество товар 4 | 5,386733224 | 0,954790997 | 1,240420272 | |||
| R2C6 | количество товар 5 | 0,599526554 | 3,905247181 | 1,339655172 | |||
| R2C7 | количество товар 6 | -2,761977666 | 2,761977666 | 1E+30 | |||
| R2C8 | количество товар 7 | 20,15039625 | 2,116417086 | 0,599347775 | |||
| R2C9 | количество товар 8 | -0,542237032 | 0,542237032 | 1E+30 | |||
| Ограничения | |||||||
| Результ. | Теневая | Ограничение | Допустимое | Допустимое | |||
| Ячейка | Имя | значение | Цена | Правая часть | Увеличение | Уменьшение | |
| R7C10 | ресурс 1 расход | 0,2317183 | 10,10407632 | 52,11972979 | |||
| R8C10 | ресурс 2 расход | 13,69551513 | 1E+30 | 51,30448487 | |||
| R9C10 | ресурс 3 расход | 546,252959 | 1E+30 | 103,747041 | |||
| R10C10 | ресурс 4 расход | 0,191012248 | 23,5330596 | 34,78747204 | |||
| R11C10 | ресурс 5 расход | 31,72987855 | 1E+30 | 288,2701214 | |||
| R12C10 | ресурс 6 расход | 0,139949568 | 123,4126984 | 5,738916256 | |||
| R13C10 | ресурс 7 расход | 351,6840778 | 1E+30 | 248,3159222 | |||
| R14C10 | ресурс 8 расход | 9,123610539 | 1E+30 | 480,8763895 | |||
| R15C10 | ресурс 9 расход | 368,9024547 | 1E+30 | 331,0975453 | |||
| R16C10 | ресурс 10 расход | 620,8033141 | 1E+30 | 129,1966859 | |||
| R17C10 | ресурс 11 расход | 9,899180476 | 1E+30 | 210,1008195 | |||
| R18C10 | ресурс 12 расход | 0,038004323 | 66,95402299 | 395,3180212 | |||
| R19C10 | ресурс 13 расход | 669,2749074 | 1E+30 | 330,7250926 | |||
| R20C10 | ресурс 14 расход | 69,20093145 | 1E+30 | 380,7990685 | |||
| R21C10 | ресурс 15 расход | 0,693713977 | 11,89989785 | 60,50583658 |
Отчет по устойчивости (Таблица П6) показывает значения нормированной стоимости оценок (Нормир. стоимость), определяющие насколько изменится целевая функция при принудительном включении в план единицы продукции. Кроме этого в отчете содержатся величины использованных ресурсов (Результ. значение), их теневые цены, показывающие насколько изменится целевая функция при увеличении соответствующего ресурса на единицу, а также используемые значения ограничений. Колонки Допустимое увеличение (уменьшение) задают диапазон изменения значений переменных и ограничений, сохраняющих общую структуру решения задачи.
Отчет по пределам (Таблица П7) показывает возможный диапазон изменения значений переменных, сохраняющий структуру оптимального решения, а также получающиеся в этом случае значения целевой функции.
Таблица П7. Отчет по пределам
| Microsoft Excel 11.0 Отчет по пределам | |||||||||
| Рабочий лист: [Книга1]Отчет по пределам 1 | |||||||||
| Отчет создан: 24.01.2008 15:13:46 | |||||||||
| Целевое | |||||||||
| Ячейка | Имя | Значение | |||||||
| R3C10 | цена доход | 1034,81178 | |||||||
| Изменяемое | Нижний | Целевой | Верхний | Целевой | |||||
| Ячейка | Имя | Значение | предел | результат | предел | результат | |||
| R2C2 | количество товар 1 | 13,93127316 | 686,5299506 | 13,93127316 | 1034,81178 | ||||
| R2C3 | количество товар 2 | 3,200905723 | 961,1909479 | 3,200905722 | 1034,81178 | ||||
| R2C4 | количество товар 3 | 1034,81178 | 1034,81178 | ||||||
| R2C5 | количество товар 4 | 5,386733224 | 916,3036486 | 5,386733221 | 1034,811779 | ||||
| R2C6 | количество товар 5 | 0,599526554 | 1024,020302 | 0,599526554 | 1034,81178 | ||||
| R2C7 | количество товар 6 | 1034,81178 | 1034,81178 | ||||||
| R2C8 | количество товар 7 | 20,15039625 | 551,2022695 | 20,15039625 | 1034,81178 | ||||
| R2C9 | количество товар 8 | 1034,81178 | 1034,81178 |
- Выводы:
В результате проведенных вычислений установлено, что для получения максимально возможного дохода, равного 1034,812 р., необходимо производить товары в следующем объеме:
· товар 1: 13,93127 единиц;
· товар 2: 3,200906 единиц;
· товар 4: 5,386733 единиц;
· товар 5: 0,599527 единиц;
· товар 7: 20,1504 единиц,
и отказаться от производства товаров 3,6 и 8.