Решение. 1. Поскольку при решении транспортно-складской задачи для компании ОАО «ПЕТМОЛ» необходимо учесть затраты на складские операции на каждую грузовую отправку

1. Поскольку при решении транспортно-складской задачи для компании ОАО «ПЕТМОЛ» необходимо учесть затраты на складские операции на каждую грузовую отправку , то целевая функция данной задачи принимает вид:

(2)

при ограничениях

(3)

Первые три неравенства в (3) – это ограничения по пропускной способности терминалов «Сервис», «Центр» и «Север» соответственно. Далее идут ограничения по спросу в соответствующих регионах. Равенство здесь означает, что спрос потребителей в каждом регионе должен быть полностью удовлетворен. Смысл последних трех ограничений очевиден и соответствует аналогичным ограничениям в (1).

2. С помощью программы MS Excel создадим новую рабочую книгу.

3. Исходные данные сформулированной выше задачи представлена на рис. 3 – 4. Здесь мы видим, что для представления исходных данных и результатов решения задачи на рабочем листе созданы четыре таблицы:

- Стоимость доставки, руб., содержащая данные о стоимости перевозок единицы товара от i -го поставщика к j -му потребителю (т.е. стоимость одного рейса грузового автомобиля);

- Исходные данные для распределительных центров, содержащая информацию о ежемесячных постоянных затратах, переменных затратах на отправку одного автомобиля, пропускной способности терминалов и строку Выбор, куда помещаются значения переменной ;

Рис. 3. Исходные данные транспортно-складской задачи

для компании ОАО «ПЕТМОЛ» (начало)

Рис. 4. Исходные данные транспортно-складской задачи

для компании ОАО «ПЕТМОЛ» (окончание)

- Таблица-план оптимального закрепления, содержащая информацию об ограничениях по спросу, вычисленных значениях переменной , в строку которой «Транспортные затраты, руб.», заносится результат вычисления величины транспортных затрат для соответствующего терминала.

В данную таблицу необходимо занести следующие расчетные формулы:

a) формулы расчета предложения поставщиков, которое равно ежемесячной пропускной способности терминалов умноженной на значение ячейки «Выбор» в таблице Исходные данные для распределительных центров, для чего необходимо в ячейку B59 занести формулу: =B52*B53 и распространить ее на весь диапазон ячеек, содержащих предложение поставщиков − B59:D59;

b) формулы расчета суммы по строкам таблицы для чего необходимо в ячейку E60 занести формулу: =СУММ(B60:D60) и распространить ее на весь диапазон ячеек, содержащих столбца «Сумма» − E60:E102;

c) формулы для расчета суммы по столбцам таблицы для чего необходимо в ячейку B101 занести формулу: =СУММ(B60:B100) и распространить ее на весь диапазон ячеек строки «Сумма» − B101:D101;

d) формулу для расчета транспортных затрат для чего необходимо в ячейку B102 занести формулу: =СУММПРОИЗВ(B5:B45;B60:B100) и распространить ее на весь диапазон ячеек, содержащих транспортные затраты − B102:D102;

- Мощность и затраты распределительных центров, содержащая результаты вычисления величины потоков (т.е. грузовых отправок) и общих издержек распределительных центров.

a) формулу для отображения результатов расчетов ежемесячной пропускной способности терминалов для чего необходимо в ячейку B108 занести формулу: =B59 и распространить ее на весь диапазон ячеек строки «Ежемесячная пропускная способность (грузовые отправки), ед.» − B108:D108;

b) формулу для расчета общих издержек распределительных центров для чего необходимо в ячейку B109 занести формулу: =B50*B53+B51*B101 и распространить ее на весь диапазон ячеек строки «Общие издержки распределительного центра, руб.» − B109:D109.

Отдельной строкой представлены Общие издержки обращения, руб., куда заносится целевая функция в виде: =СУММ(B102:D102;B109:D109).

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

4. Для оптимизации данной модели в окно Поиск решения должны быть внесены параметры оптимизации так, как это представлено на рис. 5.

Рис. 5. Окно Поиск решения с ограничениями

5. Создадим сценарии поиска решения для различных наборов исходных данных.

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

Сценарий Excel – это инструмент, позволяющий моделировать различные физические, экономические, математические и другие задачи. Он представляет собой зафиксированный в памяти компьютера набор значений ячеек рабочего листа. Используя сценарии, можно сохранить в памяти компьютера несколько наборов исходных данных так, чтобы их можно было быстро загрузить (и получить результат, соответствующий этому набору исходных данных).

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

В Microsoft Office Excel 2007 инструмент Диспетчер сценариев, который доступен через пункт меню Данные – Анализ «что – если» (рис. 6).

Рис. 6. Доступ к средству Диспетчер сценариев

Сценарий можно создать щелкнув на кнопке Добавить в окне диалога Диспетчер сценариев (рис. 7).

Рис. 7. Вид окно диалога Диспетчер сценариев

После чего открывается окно Изменение сценария, куда необходимо ввести название сценария, диапазон изменяемых ячеек и щелкнуть на кнопке OK (рис. 8).

Рис. 8. Окно диалога Изменение сценария

В открывшемся окне Значения ячеек сценария необходимо ввести значения каждой изменяемой ячейки и щелкнуть на кнопке OK (рис. 9).

Рис. 9. Окно диалога Значения ячеек сценария

Затем необходимо добавить другие сценарии поиска решения, после чего окно диалога Диспетчер сценариев приобретает вид, представленный на рис. 10.

Рис. 10. Вид окна диалога Диспетчер сценариев после создания сценариев транспортно-складской задачи

Создадим сценарии для транспортно-складской модели компании ОАО «ПЕТМОЛ». Очевидно, что в рамках данной модели возможны четыре сценария:

1) сценарий «Центр» – обслуживание всех клиентов компании только с центрального склада (переменные y_j принимаю значения {0;1;0});

2) сценарий «Центр+Сервис» – обслуживание всех клиентов компании с терминалов «Центр» и «Сервис» (переменные y_j принимаю значения {1;1;0});

3) сценарий «Центр+Север» – обслуживание всех клиентов компании с терминалов «Центр» и «Север» (переменные y_j принимаю значения {0;1;1});

4) сценарий «Центр+Сервис+Север» – использование всех трех складов для обслуживания клиентов (переменные y_j принимаю значения {1;1;1}).

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

Результаты решения данной задачи представлены на рис. 11 – 14.

Рис. 11. Результаты поиска решения по сценарию «Центр»

Рис. 12. Результаты поиска решения по сценарию «Центр+Сервис»

Рис. 13. Результаты поиска решения по сценарию

«Центр+Север»

Рис. 14. Результаты поиска решения по сценарию «Центр+Сервис+Север»

7. Проанализируем полученное решение, составив сводную таблицу результатов решения по вариантам (табл. 3).

Таблица 3

Сводная таблица результатов решения

Сценарий поиска решения	Ежемесячные затраты, тыс. руб.	Увеличение в сравнении с лучшим вариантом, %
складские	транспортные	общие
«Центр»				-
«Центр+Сервис»				5,3
«Центр+Север»				6,6
«Центр+Сервис+Север»				11,9

Анализ полученного решения показывает, что минимум транспортно-складских затрат имеет место при обслуживании всех клиентов компании только с центрального склада. В этом случае общие издержки обращения составят 12984 тыс. руб. Использование сценария «Центр+Сервис» дает стоимость решения равную 13675 тыс. руб., сценария «Центр+Север» – 13847 тыс. руб., сценария «Центр+Сервис+Север» – 14538 тыс. руб.

8. В данном случае оптимальное решение заключается в отказе от аренды дополнительных складских площадей. Следует учитывать, что при формировании данной модели мы не учли ограничение по пропускной способности для терминала «Центр». Очевидно, что при учете данного и других возможных ограничений будет получено иное оптимальное решение.