Решение мебельной задачи с помощью надстройки "поиск решения" в MS Exell

Размещено на http://www.allbest.ru/

Решение мебельной задачи с помощью надстройки поиск решения в MS Exell

Цель работы: изучить на практике метод нахождения оптимального решения в задаче распределения ресурсов в MS Excell.

Постановка задачи: Найти оптимальный план выпуска, дающий максимум прибыли при заданной цене материалов и известной сумме затрат (оборотный капитал). Известна матрица удельных затрат.

Ход решения

Матрица удельных затрат материалов на единицу продукции выглядит следующим образом:

Таблица 1. Матрица удельных затрат

Определим затраты на одно изделие каждого типа с помощью функции СУММПРОИЗВ. Синтаксис формулы Excel суммы произведений довольно прост. Функция берет в качестве аргументов один или более массивов данных и возвращает сумму произведений соответствующих значений.

Синтаксис функции выглядит следующим образом: =СУММПРОИЗВ (список1; список2 …)

Рассмотрим подробнее механизм работы этой математической функции на примере расчета стоимости одного стола:

=СУММПРОИЗВ(B9:B14;$G$9:$G$14)

Рис. 1

На рисунке видно, что под действием данной функции идет сначала перемножение данных из одного столбца на другой, а потом эти данные суммируются.

Найдем затраты на одно изделие каждого типа выпускаемой продукции (таблица 2):

Таблица 2. Затраты на одно изделие

2. Установим план выпускаемой продукции и определим затраты на все изделия по формуле:

Затраты на все изделия = план*затраты на одно изделие

А также доход от реализованной продукции:

доход = план*стоимость единицы продукции

В ячейках G22 и G23 разместим суммарные затраты на все изделия и суммарный доход соответственно( таблица 3)

Таблица 3. Затраты на все изделия и доход

3. В ячейке G25 разметим полученную прибыль, которую определим по формуле

Прибыль = доход-затраты (=G23-G22)

надстройка поиск решение exell

4. Необходимо найти максимальную прибыль при суммарных затратах не превышающих 100000 рублей. Получить нужные результаты мы можем при изменении плана выпускаемой продукции. Найти оптимальный план в ручную крайне сложно, поэтому воспользуемся поиском решений МS Exell. Поиск решений - надстройка Excel, которая помогает найти решение с помощью изменения значений целевых ячеек. Целью может быть минимизация, максимизация или достижение некоторого целевого значения. Проблема решается путем регулировки входных критериев или ограничений, определенных пользователем.

Далее выбираем команду Сервис, Поиск решения и заполняем открывшееся диалоговое окно.

Откроется вот такое окно:

Рис. 2

В этом окне нужно задать следующие настройки:

· Целевая ячейка - тут необходимо указать ячейку прибыли(G25). Целевую ячейку нужно максимизировать.

· Изменяемые ячейки - здесь укажем ячейки плана продукции (B21:F21), варьируя значения которых мы хотим добиться нашего результата -максимальной прибыли при затратах менее или равных 100000.

· Ограничения - список ограничений, которые надо учитывать при проведении оптимизации. В нашем случае это ограничения на то, что план должен быть целым и не должен быть отрицательным, так же зададим минимальный и максимальный план продукции, чтобы избежать производства только одного, самого выгодного вида продукции(столов). Добавим также условие, что затраты на изготовление продукции не должны превышать 100000.

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

После настройки всех необходимых параметров окно должно выглядеть следующим образом:

Рис. 3

Далее нажмем кнопку выполнить, и появится оптимальный план производимой продукции.

Так же появится окно, которое предлагает нам сохранить результаты поиска решения или оставить исходные значения.

Рис. 4

Сохраним найденное решение . Решение нашей задачи представленное в таблице:

Таблица 4. Оптимальное решение задачи

Вывод

Выполнив данную лабораторную работу, я ознакомилась с экономико-математической моделью задачи определения оптимальной структуры плана производства продукции в номенклатуре и стоимостных показателях по установленным критериям оптимальности на основе применения надстройки поиск решения, приобрела практические навыки в формализации и решения подобных задач.

В процессе выполнения работы попытки нахождения оптимального плана были выполнены несколькими методами: вручную и с помощью Поиска решений. Анализ полученных результатов показал, что наиболее точное решение дает Поиск решения.

Размещено на Allbest.ru