Файл: МУ для дневников.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 - Постановка условий задачи

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 - Ввод зависимостей

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 - Результаты расчета