Сельскохозяйственное предприятие специализируется на производстве зерна, сахарной свеклы и подсолнечника. Для возделывания этих сельскохозяйственных культур может быть выделено до 3 200 га пашни, дизельного топлива в объеме до 200 000 кг и минеральных удобрений в объеме до 400 000 кг действующего вещества. Требуется найти такое сочетание посевных площадей, которое обеспечило бы получение максимума прибыли.
Следует также учесть, что:
· площадь посева технических культур (сахарной свеклы и подсолнечника) не должна превышать 20% общей площади пашни;
· предприятием заключен договор на продажу зерна в объеме 105 000 ц.
Входная информация, необходимая для разработки экономико-математической модели приведена в таблице 1.
Таблица 1. Входная информация для разработки экономико-математической модели
| Показатели | Сельскохозяйственные культуры | ||
| зерновые | сахарная свекла | подсолнечник | |
| Урожайность, ц/га | |||
| Цена реализации 1 ц продукции, руб./ц | |||
| Выход товарной продукции с 1 га, тыс. руб. | 19,2 | 57,0 | 20,4 |
| Затраты на 1 га: материально-денежных средств, тыс. руб. | 9,8 | 42,5 | 10,1 |
| дизельного топлива, кг | |||
| минеральных удобрений, кг д. в-ва | 90,0 | 360,0 | |
| Прибыль с 1 га, руб. | 9,4 | 14,5 | 10,3 |
За неизвестные примем площади посева сельскохозяйственных культур по видам:
X1 - зерновых культур;
X2 - сахарной свеклы;
X3 – подсолнечника.
Для построения экономико-математической модели задачи необходимо учесть все условия. В данном случае по этим условиям можно составить пять ограничений:
- сумма площадей посева сельскохозяйственных культур не должна превышать площади, имеющейся в предприятии (3 200 га). Коэффициенты при неизвестных характеризуют расход пашни на возделывание 1 га соответствующей сельскохозяйственной культуры. В правой части ограничения указывается объем площади пашни
1) Х1+Х2+Х3<=3 200;
- сумма площадей посева технических культур не должна превышать площади, которая может быть отведена для этой цели (3200·20%=640 га). Коэффициенты при неизвестных характеризуют расход пашни, отводимой под технические культуры, на возделывание 1 га соответствующей технической культуры. В правой части ограничения указывается объем площади пашни, которая может быть отведена под технические культуры.
2) Х2+Х3<=640;
- третье и четвертое ограничения гарантируют, что использование ресурсов не превысит их наличия. То есть сумма произведений затрат ресурсов на 1 га на площади посева соответствующих сельскохозяйственных культур не должна превышать объемов ресурсов, имеющихся в предприятии. Коэффициенты при неизвестных характеризуют расход соответствующего ресурса на возделывание 1 га соответствующей сельскохозяйственной культуры. В правой части ограничений указывается наличие этих ресурсов в хозяйстве:
3) 50Х1+ 124Х2+68Х3<=200 000;
4) 90Х1+360Х2+120Х3<=350 000;
- пятое ограничение гарантирует производство запланированного объема реализации зерна. В качестве коэффициентов при переменных выступает выход зерна с 1 га площади посева зерновых культур. В правой части указывается запланированный объем реализации зерна
5) 40Х1>=105 000.
В результате получена система пяти линейных неравенств с тремя неизвестными. Требуется найти такие неотрицательные значения этих неизвестных Х1>=0; Х2>=0; Х3>=0, которые бы удовлетворяли данной системе неравенств и обеспечивали получение максимума прибыли:
Zmax = 9,4Х1+14,5Х2+10,3Х3.
В качестве коэффициентов при неизвестных в целевой функции выступает прибыль в расчете на 1 га посева сельскохозяйственных культур.
Поскольку данная задача решается с помощью MS Excel, то и подготовку всей входной информации для построения экономико-математической модели целесообразно осуществлять также с использованием этого табличного процессора. Эти расчеты представлены на листе «Исходные» файла MS Excel (рисунок 7). Это не только облегчает расчеты, связанные с подготовкой входной информации, но и дает возможность в дальнейшем автоматически обновлять параметры экономико-математической модели при реализации различных вариантов.
Экономико-математическая модель по оптимизации структуры посевных площадей в матричном виде заполняется на листе «Модель» файла MS Excel, уже содержащего лист «Исходные» (рисунок 8). Технико-экономические коэффициенты, оценки целевой функции (столбцы D, E, F), объемы ограничений (столбец I) рекомендуется определять через ссылки на соответствующие ячейки листа «Исходные».
Рисунок 7. Входная информация для разработки экономико-математической модели
Рисунок 8. Экономико-математическая модель по оптимизации
структуры посевных площадей
На рисунке 8 показано, каким образом произошло заполнение ячейки F9, описывающей затраты минеральных удобрений на 1 га посева подсолнечника (смотри строку «fx » рисунка 8).
В столбцы А (№), В (Ограничения), С (Единицы измерения) и H (Тип ограничений) вводятся соответствующие данные в текстовом формате. Они не используются в расчетах и служат для информативности и облегчения понимания содержания модели.
Для искомых величин переменных Х1, Х2, Х3 необходимо оставить пустые ячейки соответственно D5, E5, F5. Столбец G (Сумма произведений), предназначен для определения суммы произведений значений искомых неизвестных (ячейки D5, E5, F5) и технико-экономических коэффициентов по соответствующим ограничениям (строки 6-10) и целевой функции (строка 11). Формула нахождения суммы произведений для строки с целевой функцией (для ячейки G11) приведена в верхней строке рисунка 9.
Рисунок 9. Экономико-математическая модель по оптимизации
структуры посевных площадей
То есть, в столбце G будет определяться:
· количество используемых ресурсов (ячейка G6 – общей площади пашни; G7 – пашни, которая может быть использована под посевы технических культур; G8 – трудовых ресурсов; G9 – минеральных удобрений);
· количество произведенного зерна (ячейка G10);
· величина прибыли (ячейка G11).
Таким образом, построен опорный план и получено первое допустимое решение. Значения неизвестных Х1, Х2, Х3 равны нулю (ячейки D5, E5, F5 - пустые ячейки), ячейки столбца G «Сумма произведений» по всем ограничениям (строкам 6-10) и целевой строке (строка 11) также имеют нулевые значения.
Для оптимизации имеющегося плана воспользуемся инструментом Поиск решения, который находится в меню Сервис. Если нет такой команды в меню Сервис, необходимо в пункте Надстройка поставить галочку напротив Поиск решения. После этого данная процедура станет доступной в меню Сервис.
После выбора команды Поиск решения появится диалоговое окно.
Рисунок 10. Диалоговое окно команды Поиск решения
Поскольку в качестве критерия оптимизации нами выбрана максимизация прибыли, в поле Установить целевую ячейку необходимо ввести ссылку на ячейку, содержащую формулу расчета прибыли. В нашем случае это ячейка $G$11.
Рисунок 11. Диалоговое окно команды Поиск решения
Чтобы максимизировать значение конечной ячейки путем изменения значений влияющих ячеек (влияющими, в данном случае это и изменяемые ячейки, являются ячейки, которые предназначены для хранения значений искомых неизвестных), переключатель необходимо установить в положение Равной максимальному значению.
В поле Изменяя ячейки необходимо ввести ссылки на изменяемые ячейки, разделяя их запятыми, либо, если ячейки находятся рядом, указывая первую и последнюю ячейку, разделяя их двоеточием ($D$5:$F$5).
Рисунок 12. Диалоговое окно команды Поиск решения
В поле Ограничения необходимо ввести все ограничения, накладываемые на поиск решения.
В разделе Ограничения диалогового окна команды Поиск решения необходимо выбрать функцию Добавить. Появится следующее диалоговое окно.
Рисунок 13. Диалоговое окно команды Добавление ограничения
Добавление ограничений рассмотрим на примере всех ограничений. В поле Ссылка на ячейку необходимо ввести адрес одной ячейки (совокупности ячеек), на которую накладываются ограничения. В нашем случае это ячейки $G$6:$G$9, в которых находятся формулы расчета количества используемых ресурсов. Затем из раскрывающегося списка условных операторов необходимо выбрать необходимый знак, который должен располагаться между ссылкой на ячейку и ограничением. В нашем случае, это <=. В поле Ограничение необходимо ввести ссылки на ячейки, в которых находятся значения, характеризующие наличие ресурсов. В нашем случае это ячейки $I$6:$I$9.
В результате диалоговое окно примет следующий вид.
Рисунок 14. Диалоговое окно команды Добавление ограничения
После подтверждения добавления описанных ограничений (необходимо нажать кнопку Добавить), аналогично добавляется ограничение по обеспечению минимального объема производства зерна (строка 10).
Рисунок 15. Диалоговое окно команды Добавление ограничения
После описания последнего ограничения необходимо вернуться в диалоговое окно команды Поиск решения, нажав виртуальную кнопку OK.
Рисунок 16. Диалоговое окно команды Добавление ограничения
В случае внесения изменения в какое-либо из ограничений, необходимо выделить его в списке Ограничения и выбрать команду Изменить. Аналогичным способом можно удалить ненужное ограничение.
После добавления ограничений описываются параметры поиска решения. Для этого в диалоговом окне команды Поиск решения выбирается команда Параметры (рисунок 17).
Опция Линейная модель в диалоговом окне команды Параметры поиска решения служит для ускорения поиска решения линейной задачи оптимизации.
Опция Неотрицательные значения позволяет установить нулевую нижнюю границу для тех влияющих ячеек, для которых она не была указана в поле Ограничение диалогового окна Добавить ограничение.
Остальные параметры можно оставить без изменений либо установить необходимые параметры, используя опцию Справка.
Выбор параметров подтверждается нажатием виртуальной кнопки ОК
Рисунок 17. Диалоговое окно команды Параметры поиска решения
Для запуска задачи на решение необходимо нажать виртуальную кнопку Выполнить диалогового окна команды Поиск решения.
Рисунок 18. Диалоговое окно команды Результаты поиска решения
Для сохранения найденного решения на листе необходимо выбрать в диалоговом окне Результаты поиска решения опцию Сохранить найденное решение.
Для восстановления исходных данных применяется опция Восстановить исходные значения диалогового окна Результаты поиска решения.
Прерывание поиска решения осуществляется нажатием клавиши ESC.
В результате решения и сохранения результатов поиска модель примет вид, представленный на рисунке 19.
В ячейках D5:F5получены значения искомых неизвестных (площади посева равны: зерновых ‑ 2 625,0 га, сахарной свеклы – 186,5 га, подсолнечника – 388,5 га), в ячейках G6:G9 определены объемы используемых ресурсов (общей площади пашни – 3200 га; площади пашни, которая будет использоваться под посевы технических культур, – 575 га; дизельного топлива – 180 791,7 кг; минеральных удобрений – 350 000 кг д. в-ва), в ячейке G10 найдено количество зерна, которое может быть реализовано, ‑ 105 000 ц). При этом величина прибыли достигает 31 380,6 тыс. руб. (ячейка G11).
Рисунок 19. Экономико-математическая модель по оптимизации структуры посевных площадей с результатами решения
В случае если в результате поиска не было найдено решение, удовлетворяющее заданным условиям, в диалоговом окне Результаты поиска решения появится соответствующее сообщение
Рисунок 20. Диалоговое окно команды Результаты поиска решения
Одной из наиболее часто встречающихся причин невозможности найти оптимальное решение является невыполнение одного или нескольких ограничений. Сохранив найденное решение, требуется построчно сравнить полученные значения столбцов «Сумма произведений» и «Объем ограничений» и проверить, удовлетворяет ли отношение между ними ограничению, стоящему в столбце «Тип ограничений». Найдя, таким образом, невыполняемые ограничения, необходимо найти и ликвидировать причины, обусловливающие невозможность соблюдения данного конкретного условия (это может быть, например, слишком большие или, наоборот, очень маленькие запланированные объемы ограничений и т.п.).