Оптимизационные экономико-математические модели в среде MS Excel

   ВСЕРОССИЙСКИЙ ЗАОЧНЫЙ ФИНАНСОВО-ЭКОНОМИЧЕСКИЙ  ИНСТИТУТ 
 
 
 
 
 
 
 
 

   О Т Ч Е Т 

   о результатах выполнения

                                         лабораторной работы  

   Оптимизационные экономико-математические модели

     в среде MS Excel 

   Вариант № 1 
 
 
 
 

•                                                                 Исполнитель

    Факультет финансово-кредитный Группа дневная

    № зачетной книжки

    Преподаватель  
 
 
 
 
 
 
 
 


Орел 2009 г.

ЗАДАНИЕ I 


1. Условие задачи

    Хлебозавод  имеет возможность производить  различные хлебобулочные изделия. Нормы затрат различных типов сырья, их наличие и стоимость единицы продукции каждого вида приведены в таблице:

Сырье	Нормы затрат				Наличие, кг
	Хлеб  «Бородинский»	Хлеб  «Жито»	Батон  «Чайный»	Батон «Городской»
Мука  пшеничная	0,2	0,15	0,4	0,35	500
Мука  ржаная	0,25	0,3	0	0	250
Яйцо	0,02	0,025	0,04	0,035	100
Масло	0,01	0,03	0,1	0,15	200
Дрожжи	0,005	0,005	0,01	0,01	15
Вес изделия	0,65	0,85	0,7	0,6
Стоимость одного изделия	7	8	9	8

 


      После проведения маркетинговых исследований установлено, что ежедневный спрос на «Бородинский» хлеб колеблется в пределах от 150 до 300 кг; спрос на хлеб «Жито»  меняется соответственно от 300 до 450 кг; на батон «Чайный» -от 200 до 300 кг; на батон «Городской»- от 200 до 400 кг. Определить оптимальный ежедневный объем выпускаемой хлебобулочной продукции, обеспечивающий максимальную ее стоимость.

Экономико- математическая модель задачи

      Пусть х₁- кол-во хлеба «Бородинского», х₂- кол-во хлеба «Жито», х₃- кол-во батонов «Чайный», х₄- кол-во батонов «Городской», тогда целевая функция имеет вид:

       F=7x₁+8x₂+9x₃+8x₄ max

Ограничения задачи имеют вид:

      0,2x₁+0,15x₂+0,4x₃+0,35x₄ <=500

      0,25x₁+0,3x₂ <=250

      0,02x₁+0,0255x₂+00,4x₃+0,035x₄ <=100

      0,01x₁+0,03x₂+0,1x₃+0,15x₄ <=200

      0,005x₁+0,005x₂+0,01x₃+0,01x₄ <=15

      150<=0,65x₁ <=300

      300<=0,85x₂ <=450

      200<=0,7x₃ <=300

      200<=0,6x₄ <=400

      Х_1,2,3,4>=0- целое

Решение с помощью Excel

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

2. Вводим исходные данные задачи. 

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

4. Запускаем  команду Поиск решения –устанавливаем целевую ячейку, указываем адреса изменяемых ячеек, тип целевой функции- максимальное значение, вводим ограничения. В диалоговом окне Параметры поиска решения установим флажки в окна Линейная модель и Неотрицательные значения.

      

      5. В результате  поиска решения  было получено решение. 
 
 


Экономический вывод:

  Оптимальным ежедневным объемом выпускаемой продукции, обеспечивающем максимальную ее стоимость, будет выпуск хлеба «Бородинский» в количестве 237 кг или  365 изделий, хлеба «Жито» в количестве 450 кг или 529 изделий,  батонов «Чайных» в количестве 201 кг или 287 изделий,  батонов «Городских» в количестве 399 кг или 665 изделий.  Выручка от реализации составит 14690 у.е. 


                                       Задача 2

 

                                          1. Условие задачи.

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

    Числовые  данные для решения :

    Матрица планирования

Карьер	Участок работ					Предложение
	В1	В2	В3	В4	В5
А1	5	3	4	6	4	40
А2	3	4	10	5	7	20
А3	4	6	9	3	4	40
Потребности	25	10	20	30	15

    Требуется:

    1. Предложить план перевозки песка  на участки ремонта автодорог,  который обеспечивает минимальные  совокупные транспортные издержки.

    2. Определить, что произойдет с оптимальным планом, если изменятся условия перевозок: а) появится запрет на перевозки от первого карьера до второго участка работ; б) по этой коммуникации будет ограничен объем перевозок 3 тоннами.

Решение.

Экономико-математическая модель имеет вид: 
 
 
 
 


В данной задаче суммарные потребности равны суммарным запасам: 
 


    25+10+20+30+15=40+20+40

    100=100, транспортная задача, в которой  суммарные запасы и суммарные  потребности совпадают- является  закрытой.

    1. Вводим исходные данные.

    2. Создаем формы для решения задачи - создаем матрицу перевозок. Для этого обозначаем место, где место где после решения задачи  будет находиться распределение поставок, обеспечивающее минимальные материальные затраты на перевозку груза изменяемые ячейки В5:F7- в них будет записан оптимальный план перевозок х_ij.

    3. Вводим ограничения для всех поставщиков и всех потребителей (в матрице перевозок суммируем ячейки по столбцам и по строчкам).

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

    5. Запускаем команду Поиск решения – устанавливаем целевую ячейку, указываем адреса изменяемых ячеек, тип целевой функции - максимальное значение, вводим ограничения. В диалоговом окне Параметры поиска решения установим флажки в окна Линейная модель и Неотрицательные значения. Добавляем ограничения (рис.5).

    

    Рис.5

6. Решение задачи выполняется сразу после ввода данных, когда на экране находится диалоговое окно Поиск решения, после нажатия кнопки Выполнить: 
 


Рис.6

      Экономический вывод:

      План  перевозки означает, что с первого карьера перевозка песка составит по 10 тонн для 2-го и 5-го участков, а на 3-ий участок выведут 20 тонн; со второго карьера песок вывезут только на 1-ый участок в количестве 20 тонн; с третьего карьера перевозка песка составит по 5 тонн для 1-го и 5-го участков, а на 4-ий участок вывезут 30 тонн. Также можно сказать, что на 2-ой и третий участки выгодно перевозить песок только с первого карьера, а на 4-ый участок выгодно перевозить песок только с третьего карьера.  При этом все потребности в песке будут удовлетворены, а общие минимальные транспортные издержки составят 340 у.е

Дополнительные  условия

      А) При изменении условий задачи, в связи с запретом на перевозки от первого карьера к второму участку работ- увеличение перевозки 1 тонны до 1000 у.е., решение измениться в худшую сторону- минимальные совокупные издержки возрастут до 365 у.е, также изменяться условия перевозок на 1-ом и 2-ом участках. На 1-ый участок будет поставляться песок в количестве 5 тонн с первого карьера, и по 10 тонн со второго и третьего карьером. (рис.7) 


      Б) При ограничении объемов перевозки 3 тоннами на 2-ой участок с третьего карьера также измениться решение   в худшую сторону - минимальные совокупные издержки возрастут до 356 у.е, также изменяться условия перевозок на 1-ом, 2-ом и 5-ом  участках (рис.8). 
 


      Рис.7 


         


Рис.8