Заполняем формулами строку «Остаток»

Для этого у станавливаем курсор на пересечении строки, соответствующей 1-му исходному пункту и столбцу остаток (ячейка J12), нажимаем знак «=» на клавиатуре и выделяем ячейку на пересечении столбца «Запасы» и строки «Карьер №1» (соответственно ячейка Н12). Затем нажимаем знак «-» на клавиатуре и выделяем ячейку на пересечении столбца «Вывезено» и строки «Карьер №1» (соответственно ячейка I12), нажимаем клавишу «Enter» на клавиатуре.

Копируем формулу расчета остатка продукции, вывезенной из 1-го исходного пункта для остальных исходных пунктов. Для этого выделяем ячейку J12 и нажимаем иконку копировать на панели инструментов, а затем выделяем остальные исходные пункты (соответственно ячейки J13 и J14) и нажимаем иконку вставить на панели инструментов.

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

Для этого активируем ячейку, в которой будет размещаться значение целевой функции, (в нашем случае ячейка G17). Вызываем «Мастер функций» и в категории «Математические» выбираем «СУММПРОИЗВ», нажимаем «ОК» (рисунок 28)

В окне «Аргументы функции» в строку «Массив 1» (рисунок 29) вводим значения стоимости перевозок, для чего устанавливаем курсор в строку «Массив 1» и нажимаем на левую клавишу мыши.

Затем устанавливаем курсор в ячейку на пересечении исходного пункта №1 и 1-го пункта потребления в транспортной таблице, нажимаем левую клавишу мыши и, не отпуская клавишу мыши, «протаскиваем» курсор по диагонали до ячейки последнего исходного пункта и последнего пункта потребления включительно (в нашем случае от ячейки C4 до ячейки G6).

Рисунок 28 – Ввод формулы для расчета значения целевой функции

В окне «Аргументы функции» в строку «Массив 2» (рисунок 29) вводим значения искомых переменных, для чего сначала устанавливаем курсор в окне «Аргументы функции» в строку «Массив 2» и нажимаем на левую клавишу мыши.

Затем устанавливаем курсор в ячейку на пересечении исходного пункта №1 и 1-го пункта потребления во вспомогательной таблице. Нажимаем левую клавишу мыши и, не отпуская ее, протаскиваем курсор по диагонали до ячейки последнего исходного пункта и последнего пункта потребления включительно (в нашем случае от ячейки C12 до ячейки G14) (рисунок 29).

В окне «Аргументы функции» нажимаем «ОК». В ячейке, где должно размещаться значение целевой функции, будет «0». После проведения подготовительных действий переходим к расчёту плана перевозок.

Рисунок 29 – Ввод массивов функции «СУММПРОИЗВ»

Вычислительный этап

1). Запускаем программу «Поиск решения» к омандой Данные/Анализ / Поиск решения (Excel 2007) или Сервис/Поиск решения (Excel 2003 и ниже).[3]

2). Заполняем поля в появившемся окне.

В окне «Поиск решения» [4] устанавливаем курсор в строку «Установить целевую ячейку», а затем активируем ячейку, в которую мы хотим поместить значение целевой функции, установив на нее курсор и щелкнув левой кнопкой мыши. Эта ячейка будет мерцать (в нашем случае ячейка G17).

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

Для этого активируем радиокнопку «Минимальное значение». В разделе «Изменяя ячейки», вводим ячейки, соответствующие плану перевозок (C12:G14). Для их ввода устанавливаем курсор в ячейку, соответствующую пересечению исходного пункта № 1 и пункта потребления № 1 и щелкаем левой кнопкой мыши. «Протаскиваем» курсор с нажатой левой кнопкой мыши по диагонали до ячейки, соответствующей значению последнего пункта потребления и последнего исходного пункта включительно во вспомогательной таблице (в нашем случае от ячейки С12 до ячейки G14) (рисунок 30).

Рисунок 30 – Ввод данных в окне «Поиск решения»

Устанавливаем курсор в окно «Ограничения», щелкнув на нем левой кнопкой мыши. Нажимаем кнопку «Добавить». В окне «Ссылка на ячейку» вводим данные по количеству ввезенной продукции на каждую строительную площадку (пункт потребления), для чего помещаем курсор в строку «Ввезено» вспомогательной таблицы и ячейку, соответствующую 1-му пункту потребления. При нажатой левой кнопкой мыши «протаскиваем» курсор по строке «Ввезено» до ячейки, соответствующей последнему пункту потребления (в нашем случае от ячейки С16 до ячейки G16).

В окне правее «Ссылка на ячейку» выбираем знак ограничения. В нашем случае для потребности это будет знак равенства «=». Вводим значение ограничения, для чего устанавливаем курсор в ячейку на пересечении строки потребностей и 1-го пункта потребления (в транспортной таблице). Щелкнув левой кнопкой мыши и не отпуская ее, «протаскиваем» курсор по строке потребностей до последнего пункта потребления включительно (в нашем случае от ячейки С7 до ячейки G7). Нажимаем ОК.

Переходим к вводу следующего ограничения, нажав на кнопку «Добавить». Для ввода ограничений по запасам поступаем аналогично. Активируем ячейку, соответствующую пересечению исходного пункта № 1 и столбца «Вывезено» во вспомогательной таблице (рисунок 31) и «протаскиваем» курсор по столбцу до ячейки, соответствующей последнему исходному пункту включительно (в нашем случае от ячейки I12 до ячейки I14). Переходим к вводу знака ограничений, для чего в окне, правее ссылки на ячейку выбираем соответствующий знак. В нашем случае это «<=». Вводим значение ограничения, установив курсор в окно ограничения и выделив после этого значение запасов в столбце «Запасы» (в исходной транспортной таблице) от 1-го пункта потребления до последнего пункта потребления включительно (в нашем случае от ячейки Н4 до ячейки Н6). Нажимаем «ОК» (рисунок 31).

Рисунок 31 – Ввод данных в окне «Добавление ограничения».

Переходим к вводу параметров решения, нажав кнопку «Параметры» (рисунок 32). В окне «Параметры поиска решения» оставляем стандартные условия без изменения. Ставим галочку в чекбоксах «Линейная модель» и «Неотрицательные значения переменных». После чего нажимаем «ОК» (рисунок 32)

В окне «Поиск решения» нажимаем «Выполнить». В окне «Результаты поиска решения» в зависимости от потребностей в анализе рассчитанного плана выбираем результаты, устойчивость, пределы. В нашем случае выбираем «Результат», «Сохранить найденное решение» и нажимаем «ОК» (рисунок 33).

Рисунок 32 – Работа в окне «Параметры поиска решения»

Анализ результатов расчетов

Во вспомогательной таблице (рисунок 33) в ячейках на пересечении исходных пунктов (карьеров) и пунктов потребления (участков работ) показаны оптимальные объемы перевозок.

В строке «Ввезено» – количество продукции (грузов), ввезенной в каждый пункт потребления (каждому потребителю). В столбце «Вывезено» размещены объемы продукции, вывезенной из каждого исходного пункта (от каждого поставщика) для удовлетворения потребностей пунктов потребления.

Рисунок 33 – Результаты вычислений[5].

В столбце «Остаток» показаны запасы продукции (грузов), имеющейся в исходных пунктах после удовлетворения потребностей пунктов потребления.

В ячейке «Расходы на перевозку» показана минимальная стоимость перевозок для рассчитанного плана (общие затраты).

3. ВАРИАНТЫ ЗАДАНИЙ НА КУРСОВУЮ РАБОТУ

3.1 Примерный перечень тем для разработки теоретического вопроса курсовой работы

1. Принципы, цели, функции и задачи интегрированного планирования и управления цепями поставок.

2. Основные показатели эффективности цепей поставок.

3. Стратегическое планирование цепей поставок.

4. Тактическое планирование в цепях поставок.

5. Оперативное планирование в цепях поставок.

6. История развития управления цепями поставок.

7. Последствия «эффекта хлыста» для участников цепи поставок.

8. Методы уменьшения экономических затрат и потерь от «эффекта хлыста».

9. Процесс интегрированного планирования цепей поставок на примере технологии MRPII

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

11. Оптимизация и балансировка планов продаж, производства, закупок.

12. Решение транспортно-складской задачи.

13. Решение производственно-транспортно-складской задачи.

14. Информационная поддержка интегрированного планирования в цепях поставок.

15. Маршрутизация и программы планирования транспортировки в цепях поставок.

16. Препятствия интеграции планов в цепи поставок.

17. Синхронизация прогноза спроса, план продаж, плана дистрибуции с контуром производственного планирования.

18. Метод выявления «узких мест» в цепях поставок

19. Планирование в тянущих и толкающих цепях поставок.

20. Внутри- и межфирменная интеграция и координация в управлении цепями поставок.

3.2 Задания для выполнения практического вопроса курсовой работы

Службе логистики крупного концерна пищевой промышленности, в состав которого входят три предприятия по производству прохладительного напитка «Дюшес», поставлена задача спланировать поставки сырья, объемы производства и распределение готовой продукции для реализации в магазинах розничной торговли, исходя из следующих исходных данных:

1) Имеется 6 магазинов розничной торговли с постоянным спросом на продукцию в каждом -м магазине. Спрос каждого -го магазина по вариантам написания курсовой работы приведен в таблице 3.

Таблица 3 – Спрос магазинов розничной торговли по вариантам

Спрос магазинов	Варианты
, т	1,6	3,5	0,2		0,4	2,3	1,7	0,2	0,7	4,1		2,4	2,2	0,8	5,2
, т	2,2	4,2		0,5	0,7	2,7	1,3	0,5	2,7	1,5		1,1	1,6	3,5	2,4
, т		1,5	5,3	2,2		3,1	4,5	1,4	1,3	1,8		5,3	1,1	2,2	1,9
, т	0,5	3,7	2,5	1,4	0,5	1,9		0,7	1,1		1,5	1,6		3,9	7,3
, т	1,5	2,6	1,7		0,2	1,7	1,5	0,2	0,5	1,1	0,5	0,5	0,8	2,5	2,6
, т	0,4	2,8	2,5	0,8	0,5	4,2	2,3	0,5	3,2	4,2		4,5	1,2		1,3

2) Обеспечение магазинов производится через оптовый склад, на котором хранится продукция всех предприятий. Расстояния между каждым из магазинов и оптовым складом известны и приведены в таблице 4. Значения переменных приведены по вариантам в таблице 5.

Таблица 4 – Расстояния между магазинами и оптовым складом

Магазины	1	2	3	4	5	6	опт. склад
1
2
3
4
5
6
опт. склад

Таблица 5 – Численные значения расстояний между каждым из магазинов и оптовым складом по вариантам

Расстояния, км

Варианты

;

;

;

;

;

;

;

;

;

;

;

;

;

;

;

;

;

;

;

;

3) Ежедневно со склада назначается только один автомобиль для развозки прохладительного напитка по сети магазинов розничной торговли. Автомобиль выбирается в зависимости от ежедневного суммарного спроса всех потребителей. Предлагаемые для использования автомобили и прицепы к ним и их грузоподъемность приведены в таблице 6.

Таблица 6 - Грузоподъемности автомобилей, имеющихся на оптовом складе

	Автомобили и прицепы
«Валдай»	КамАЗ-5320	IVECO Euro Cargo	МАЗ-6312	прицеп ГКБ- 8350 (к а/м КамАЗ)	прицеп МАЗ-892600-017 (к а/м МАЗ)
Грузоподъемность, т	3,5	8,0		14,3	8,0	8,24

4) Предприятия концерна расположены на сети дорог соединяющей их с оптовым складом. Схема сети дорог, размещения предприятий и оптового склада показана на рисунке 34, где вершина 1 – размещение оптового склада, вершины 2 – 4 – предприятия, производящие прохладительный напиток, вершины 5 – 14 – промежуточные узлы сети дорог

Рисунок 34 - Схема сети дорог, размещения предприятий и оптового склада

Значения расстояний приведены по вариантам в таблице 7.

Таблица 7 – Значения расстояний между каждым из магазинов и оптовым складом по вариантам

Расстояния, км

Варианты

5) Снабжением трех предприятий концентратом для производства напитка «Дюшес» занимается 5 поставщиков. При этом известны возможности поставщиков в объемах поставки концентрата напитка и потребности предприятий в данном концентрате , а также стоимость перевозки (или стоимость перевозки и закупки) одной единицы груза от -го поставщика -му потребителю, которая равна . Матрица стоимостей перевозок (закупок и перевозок) приведена в таблице 8.

Таблица 8 – Матрица стоимостей перевозок

Поставщики	Предприятия
№ 1	№ 2	№ 3
№ 1
№ 2
№ 3
№ 4
№ 5

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

Службе логистики требуется:

1) Разработать графическую модель цепи поставок логистической системы обеспечения, производства, хранения и распределения продукции;

2) Определить оптимальный размер запасов на оптовом складе для обеспечения потребности всех магазинов в прохладительном напитке «Дюшес», если стоимость хранения 1 т напитка составляет 200 руб. в сутки. Ориентировочная стоимость оформления заказа, включая стоимость транспортировки 1 тонны 1000 руб. и стоимость закупки 1 т 10000 руб., определяется типом используемого транспортного средства и емкостью склада, а также интенсивностью спроса на прохладительный напиток.

3) Определить арендуемую площадь оптового склада, необходимую для хранения напитков, если они собраны в паллеты, каждая из которых занимает площадь 1,2 х 0,8 м массой по 0,5 т. Паллеты допускают возможность постановки их друг на друга в 2 яруса.

Таблица 9 –Значения возможностей поставщиков в объемах поставки концентрата напитка , потребностей предприятий в концентрате и стоимости перевозок по вариантам

Данные о поставках сырья

Варианты

Возможности поставщиков, кг

Потребности предприятий, кг

Стоимости перевозки от i -го поставщика j -му потребителю, руб./кг

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

5) Составить наикратчайший маршрут движения автомобиля, развозящего прохладительный напиток по сети магазинов розничной торговли.

6) Найти оптимальный план перевозок от поставщиков сырья предприятиям по критерию минимума затрат на перевозки.

4. ЛИТЕРАТУРА ДЛЯ ПОДГОТОВКИ И ВЫПОЛНЕНИЯ КУРСОВОЙ

РАБОТЫ

1. Дж. Шапиро. Моделирование цепи поставок. СПб.: Издательский дом «Питер», 2006 г., 720 с.

2. Бочкарев А.А. Планирование и моделирование цепи поставок: Учебно-практическое пособие.- М.: «Альфа-Пресс», 2008.

3. Иванов Д.А. Управление цепями поставок - С-Пб: Издательство СПбГПУ, 2009.

4. Уотерс Д. Логистика. Управление цепью поставок. – М.: ИНФРА-М, 2003.

5. Стерлигова А.Н. Управление запасами в цепях поставок: Учебник. - М.: ИНФРА-М, 2008.

6. Дыбская В.В. Управление складированием в цепях поставок. – М.: Альфа-пресс. 2009 - 720 с.

7. Дыбская В.В., Зайцев Е.И., Сергеев В.И., Стерлигова А.Н. Логистика: интеграция и оптмимизация логистических бизнес-процессов в цепях поставок / Учебник под ред. проф. В.И. Сергеева. – М.: Эксмо, 2008. 944 с.

8. Бочкарев А.А. Планирование и моделирование цепи поставок: Учебно-практическое пособие, – М.: «Альфа-пресс», 2008. – 192 с. гл.. 6.

9. Корпоративная логистика. 300 ответов на вопросы профессионалов/ Под общ. и научной редакцией проф. В.И. Сергеева. – М.: ИНФРА-М, 2004. – 976с., гл.8.

10.Таха Х.А. Введение в исследование операций. – М.: Издательский дом «Вильямс», 2005.

11.Ермошин Н.А., Егошин А.М. Практикум по экономике и организации перевозок в логистических цепях: Учебное пособие. СПб: Изд-во СПбГУСЭ, 2011. 95 с.

По данной дисциплине используются электронные средства обучения:

- http://www.gaudeamus.omskcity.com/my_PDF_library.html - Электронные библиотеки России;

- http://www.aup.ru/ – Административно-управленческий портал;

- http://eup.ru/ – Экономика и управление на предприятиях: научно-образовательный портал;

- http://www.kodeks.ru/manage/page/ – Справочно-правовая система;

- http://www.nalog.ru/index.php?topic=nalzak – Налоговое законодательство России;

- http://grebennikon.ru/cat.php – Электронная библиотека по менеджменту и логистике.

[1] Если программы поиск решений нет в меню, то ее следует подключить в меню «Надстройки».

[2] Если программы поиск решений нет в меню, то ее следует подключить в меню «Надстройки».

[3] Если программы поиск решений нет в меню, то ее следует подключить в меню «Надстройки».

[4] В Microsoft Office Excel 2003 вкладка «Поиск решения» находится в меню «Сервис», в Microsoft Office Excel 2007 вкладка «Поиск решения» находится в меню «Данные». Если в меню «Сервис» («Данные») отсутствует вкладка «Поиск решения», то открываем в меню «Сервис» вкладку «Надстройки» и в появившемся окне помечаем галочкой квадратик напротив строки «Поиск решения».

[5] В таблице значения данных приведены в тыс. ед.