Файл: Учебное пособие для студентов специальностей 125 01 10 Коммерческая деятельность.doc

Требуется:

1. 
   составить экономико-математическую модель задачи, пользуясь которой, можно найти план товарооборота по кри­терию максимума дохода f;
   
2. 
   найти оптимальный план товарооборота и максимальную величину дохода с помощью инструмента Excel Поиск решения;
   
3. 
   выполнить анализ оптимального решения по следующим отчетам: отчет по результатам, отчет по устойчивости и отчет по пределам.
   

Решение задачи1 Экономико-математическая модель задачиИзвестно, что величина дохода линейно связана с объемом продажи товаров х₁, х₂ и х₃. В связи с этим целевую функцию можно записать таким образом:f = (3x₁ + 5х₂ + 4х₃) → max.Очевидно, что объем продажи товаров не может быть отрицатель­ной величиной. Поэтому x₁ ≥ 0, x₂ ≥ 0, x₃ ≥ 0. Учитывая нормы за­трат рабочего времени и то, что общие затраты в целом не должны превышать имеющихся ресурсов, запишем следующее ограничение:Исходя из торговой площади и общей площади запишем следую­щее ограничение:Поскольку известны ограничения по издержкам обращения, запи­шем последнее ограничение: Экономико-математическую формулировку и модель этой за­дачи в компактном виде можно представить таким образом: из существующего множества решений системы линейных ограничений по ресурсам_,,,x₁ ≥ 0, x₂ ≥ 0, x₃ ≥ 0,найти такие величины объемов продажи товаров x₁, x₂, x₃, ко­торые бы обеспечили максимальную величину дохода в линейной функции цели:f = (3x₁ + 5х₂ + 4х₃) → max.2 Решение задачи с помощью инструмента Excel Поиск решенияАлгоритм решения задачи состоит из нескольких этапов:

---

1. 
   Ввод исходных данных (таблица 1.1)
   

Таблица 1.1 - Постановка условий задачи

	А	В	С	D	E	F	G
1		Переменные
2	имя	Товар А	Товар В	Товар С
3	значение
4					ЦФ	направление
5	коэффициент в ЦФ	3	5	4		макс
6			Ограничения
7	вид	Товар А	Товар В	Товар С	левая часть	знак	правая часть
8	Рабочее время, чел.-час.	0,1	0,2	0,4		<	1100
9	Площадь торговых залов, м2	0,05	0,02	0,02		<	120
10	Издержки обращения, ден. ед.	3	1	2		<	8000

---

2) Ввод зависимости из математической моделиВвести зависимость для целевой функции:

• 
  курсор в ячейку Е5;
  
• 
  курсор на кнопку Мастер функций;
  
• 
  на экране диалоговое окно Мастер функций – шаг 1 из 2;
  
• 
  курсор в окно Категория на категорию Математические;
  
• 
  курсор в окно Функции на СУММПРОИЗВ;
  
• 
  на экране диалоговое окно (рисунок 1.1);
  
• 
  в массив 1 ввести В$3:D$3;
  
• 
  в массив 2 ввести B5:D5;
  
• 
  курсор на кнопку ОК;
  
• 
  на экране (таблица 1.2) в ячейке Е5 введены значения целевой функции.
  

Ввести зависимости для левых частей ограничений:

• 
  курсор в ячейку Е5;
  
• 
  курсор на кнопку Копировать;
  
• 
  курсор в ячейку Е8;
  
• 
  курсор на кнопку Вставить;
  
• 
  скопировать в Е9:Е10;
  

- на экране (таблица 1.2) в ячейки Е15:Е17 введены функции.Рисунок 1.1 - Мастер функцийТаблица 1.2 - Ввод зависимостей

	А	В	С	D	E	F	G
1		Переменные
2	имя	Товар А	Товар В	Товар С
3	значение
4					ЦФ	направление
5	коэффициент в ЦБ	3	5	4	=СУММПРОИЗВ (В$3:D$3;B5:D5)	макс
6		Ограничения
7	вид	Товар А	Товар В	Товар С	левая часть	знак	правая часть
8	Рабочее время, чел.-час.	0,1	0,2	0,4	=СУММПРОИЗВ (В$3:D$3;B8:D8)	<	1100
9	Площадь торговых залов, м2	0,05	0,02	0,02	=СУММПРОИЗВ (В$3:D$3;B9:D9)	<	120
10	Издержки обращения, ден. ед.	3	1	2	=СУММПРОИЗВ (В$3:D$3;B10:D10)	<	8000

---

3) Решение задачиВвести данные в окно поиска решения. Для этого:

• 
  выполнить команду Сервис → Поиск решения;
  
• 
  на экране диалоговое окно Поиск решения (рисунок 1.2);
  
• 
  в окне Поиск решения в поле Установить целевую ячейку ввести ссылку $E$5, щелкнув по ячейке с целевой функцией Е5 на рабочем листе;
  
• 
  в группе Равной установить опцию Максимальному значению;
  
• 
  в поле Изменяя ячейки ввести диапазон ячеек $B$3:$D$3, выделив их на рабочем листе;
  
• 
  нажать на кнопку Добавить для ввода ограничений;
  
• 
  в окне Добавление ограничения в поле Ссылка на ячейку ввести ссылку на ячейку, содержащую формулу левой части текущего ограничения, например $E$8, щелкнув по этой ячейке на рабочем листе. Затем выбрать из раскрывающегося списка в средней части поля нужное ограничение (<=). В правую часть поля ввести значение правой части ограничения (число 1100 или адрес G8 с этим числом). Далее нажать на кнопку Добавить, чтобы ввести следующие ограничения. После добавления всех ограничений нажать на кнопку ОК для возвращения в окно Поиск решения (рисунок 1.2);
  

Рисунок 1.2 - Диалоговое окно Поиск решения

• 
  если при вводе задачи возникает необходимость в изменении или удалении внесенных ограничений, то надо щелкнуть на кнопку Изменить или Удалить;
  
• 
  в окне Поиск решения щелкнуть на кнопку Параметры и установить параметры поиска решения (рисунок 1.3), т.е. установить флажок на Линейная модель и нажать на кнопку ОК;
  

Рисунок 1.3 - Диалоговое окно Параметры поиска решения

• 
  в окне Поиск решения нажать на кнопку Выполнить;
  
• 
  на экране появится диалоговое окно Результаты поиска решения (рисунок 1.4);
  
• 
  нажать на кнопку ОК.
  

Рисунок 1.4 - Диалоговое окно «Результаты поиска решения»Результат оптимального решения приведен в таблице (таблица 1.3).Таблица 1.3 - Результаты расчета

	А	В	С		D	E	F	G
1		Переменные
2	имя	Товар А		Товар В	Товар С
3	значение	250		5375	0
4						ЦФ	направление
5	коэффициент в ЦФ	3		5	4	27625	макс
6				Ограничения
7	вид	Товар А		Товар В	Товар С	левая часть	знак	правая часть
8	Рабочее время, чел.-час.	0,1		0,2	0,4	1100	<=	1100
9	Площадь торговых залов, м2	0,05		0,02	0,02	120	<=	120
10	Издержки обращения, ден. ед.	3		1	2	6125	<=	8000

---

3 Анализ оптимального решенияЧтобы вызвать отчеты анализа, необходимо в диалоговом окне Результаты поиска решения установить курсор на одном из отчетов и нажать кнопку ОК.Отчет по результатам (таблица 1.4) состоит из трех таблиц:

• 
  таблица 1 приводит сведения о целевой функции;
  
• 
  таблица 2 приводит значения искомых переменных, полученные в результате решения задачи;
  
• 
  таблица 3 показывает результаты оптимального решения для ограничений и для граничных условий.
  

Отчет по пределам (таблица 1.5) показывает, в каких пределах может изменяться объем реализации продукции, вошедшей в оптимальное решение, при сохранении структуры оптимального решения.Отчет по устойчивости (таблица 1.6) состоит из двух таблиц:

• 
  в таблице 1 приводятся значения для переменных: результат решения задачи; нормируемая стоимость, т.е. дополнительные двойственные переменные, показывающие, насколько изменяется целевая функция при принудительном включении единицы этой продукции в оптимальное решение; предельные значения приращения коэффициентов целевой функции, при которых сохраняется набор переменных, входящих в оптимальное решение;
  
• 
  в таблице 2 приводятся значения для ограничений: величина использованных ресурсов; теневая цена, т.е. двойственные оценки, показывающие, как изменится целевая функция при изменении ресурсов на единицу; значения приращения ресурсов, при которых сохраняется оптимальный набор переменных, входящих в оптимальное решение.
  

Таблица 1.4 - Отчет по результатам

Целевая ячейка (Максимум)
	Ячейка	Имя	Исходно	Результат
	$E$5	Коэффициент в ЦФ	0	27625
Изменяемые ячейки
	Ячейка	Имя	Исходно	Результат
	$B$3	значение Товар А	0	250
	$C$3	значение Товар В	0	5375
	$D$3	значение Товар С	0	0
Ограничения
	Ячейка	Имя	Значение	Формула	Статус	Разница
	$E$8	Рабочее время, чел.-час. левая часть	1100	$E$8<=$G$8	связанное	0
	$E$9	Площадь торговых залов, м2 левая часть	120	$E$9<=$G$9	связанное	0
	$E$10	Издержки обращения, ден. ед. левая часть	6125	$E$10<=$G$10	не связан.	1875
	$B$3	значение Товар А	250	$B$3>=0	не связан.	250
	$C$3	значение Товар В	5375	$C$3>=0	не связан.	5375
	$D$3	значение Товар С	0	$D$3>=0	связанное	0

---