Методика решения задачи в MS Excel

ИНФОРМАЦИОННЫЙ МЕНЕДЖМЕНТ

Методические указания

к выполнению контрольной работы

для студентов очной и заочной формы обучения

для бакалавриата по направлению

081100 «Государственное и муниципальное управление»

№ 080200 – Менеджмент

Всеволожск 2013

Составитель:

Старший преподаватель Т.П. Козинова

Утверждены на заседании кафедры

математических и естественно-научных дисциплин

филиала РГГУ в г. Всеволожске

01 июня 2012 г., протокол № 10

Оптимизация плана доставки товаров

Цель: приобрести навыки решения задач оптимизации

Основные сведения о принципах решения задач оптимизации

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

Методика решения задачи в MS Excel

Компания имеет 3 склада, территориально расположенных в различных районах города. Заказы на перевозку грузов поступают из сети 5 магазинов, расположенных по всей территории города.

Решение:

Цель задачи:

Удовлетворить потребность 5 розничных магазинов в товарах, находящихся на 3 складах и сохранить при этом общие расходы на перевозку на минимальном уровне.

Построение математической модели:

Обозначим

X_ij - количество продукции, отправляемой со склада i в магазин j

C_ij – стоимость перевозки единицы продукции со склада i в магазин j

Математическая модель будет состоять из ряда ограничений:

a) Исходя из физического смысла задачи X_ij ³0;C_ij³0

b) Ограничения по предложению (со склада нельзя вывезти продукции больше, чем там имеется).

(1)

c) Ограничения по спросу (в магазины следует завести не меньше продукции, чем её требуется):

(2)

Общая стоимость перевозок (целевая функция) равна: (3)

Необходимо определить такие неотрицательные значения переменных X_ij, которые удовлетворяют ограничениям (1) и (2) и обращают в минимум целевую функцию Z (3). В такой постановке задача является транспортной задачей линейного программирования.

Необходимым и достаточным условием разрешимости транспортной задачи является условие баланса:

(4)

Где - суммарное количество продукции на складах

(при этом - количество продукции на одном складе)

- суммарное количество продукции, требуемое в магазинах

(при этом - количество продукции, которое требуется j- му магазину).

В нашем случае

=15+20+20=60; =20+12+5+8+15=60

= =60

Следовательно, задача с балансом.

Разработка ЭТ с начальным планом решения

ЭТ приведена на рис. 1 – режим вычислений, рис.2- режим показа формул.

1. Подготовка блока ячеек с исходными данными

В ячейках В5:В7 помещаем сведения о наличии продукции на складах. В ячейках С9:G9- сведения о потребностях магазинов. В ячейках С5:G7 – данные о стоимости перевозок единицы продукции со складов в магазин.

2. Построим начальный план перевозок

Считаем, что с каждого склада в каждый магазин везут одну деталь (ячейки С11: G13 заполним единицами)

3. Вычислим количество перевозимой продукции

а) В ячейку В11 – введем формулу для вычисления количества продукции, вывозимой с 1-го склада:

=СУММ(С11:G11).

Аналогично в ячейки В12, В13 введём формулы для вычисления количества продукции, вывозимой со второго и третьего складов:

=СУММ(С12:G12).

=СУММ(С13:G13).

Для начального плана перевозок все суммы равны 5.

Б). В ячейку С15 введём формулу для вычисления количества продукции, которую везём в первый магазин

=СУММ(С11:С13).

Аналогично в ячейки D15:G15 введём формулы для вычисления количества продукции, которую везём во 2-ой, 3-ий, 4-й, 5-й магазины.

В ячейку D15 =СУММ(D11:D13)

В ячейку E15 =СУММ(E11:E13)

В ячейку F15 =СУММ(F11:F13)

В ячейку G15 =СУММ(G11:G13).

4. Определим стоимость перевозок в каждый из магазинов

Для определения стоимости перевозок в 1-й магазин т.е.

Z₁=X₁₁C₁₁+X₁₂C₁₂+X₁₃C₁₃

Введём в ячейку С16 формулу:

=СУММПРОИЗВ(С5:С7;С11:С13).

В ЯЧЕЙКУ D16 введём формулу для вычисления стоимости перевозок в остальные магазины: =СУММПРОИЗВ(D5:D7;D11:D13).

В ячейке E16 =СУММПРОИЗВ(E5:E7;E11:E13);

В ячейке F16 =СУММПРОИЗВ(F5:F7;F11:F13);

В ячейке G16 =СУММПРОИЗВ(G5:G7;G11:G13).

5. Определим общую стоимость перевозок (целевую функцию ЦФ)

Z=Z₁+Z₂+Z₃+Z₄+Z₅ (5)

Введём в ячейку В17 формулу:

=СУММ(С16:G16)

Для нашего начального плана получится 44.

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