Запасы товаров на складах, ед.

Известна также стоимость доставки единицы товара в денежных единицах из других городов в город N.

Стоимость доставки товаров в город N из других городов, ден. ед.

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

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

Задача решается в следующей последовательности:

1. Создается книга Excel, и на чистом листе создается рабочая форма (рис. 2.21).

Рис. 2.21. Создание рабочей формы

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

2. Введите формулы в соответствующие ячейки.

Ячейки с F22 по F26 должны содержать суммарный объем перевозок по каждому товару, т.е.:

F22 = СУММ(B22:E22);

F23 = СУММ(B23:E23);

F24 = СУММ(B24:E24);

F25 = СУММ(B25:E25);

F26= СУММ(B26:E26).

Ячейки с B27 по E27 должны содержать суммарный объем перевозок из каждого пункта отправления (т.е. из каждого города). Следовательно, должны содержать формулы:

B27 = СУММ(B22:B26);

C27 = СУММ(C22:C26);

D27 = СУММ(D22:D26);

E27 = СУММ(E22:E26).

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

H22= СУММ(B4:E4).

Соответственно для других товаров:

H23 = СУММ(B5:E5);

H24 = СУММ(B6:E6);

H25 = СУММ(B7:E7);

H26 = СУММ(B8:E8).

Для обеспечения читаемости формы и ее результатов введем текст «<=» в каждую ячейку диапазона G23:G26:

G23 <=

G24 <=

G25 <=

G26 <=

Так как необходимо спланировать такой объем перевозок, чтобы спрос на рынке был удовлетворен, то заполним другое ограничение на поиск решения – ограничение по спросу на товары в пункте назначения. Для обеспечения читаемости формы введем «=» в каждую ячейку диапазона I22:I26.

Определим формулу для функции цели. Функцией цели в данном случае является суммарная стоимость перевозок. Стоимость перевозки товара из пункта отправления в пункт назначения равна произведению стоимости перевозки единицы груза по данному маршруту на объем перевозки.

Таким образом, необходимо вычислить сумму произведений ячеек диапазона B13:E17 (стоимости перевозок) на соответствующие ячейки диапазона B22:E26 (объемы производства).

Следовательно, в ячейке B29 (целевая функция) необходимо ввести формулу для расчета целевой функции:

B29 = СУММПРОИЗВ(B13:E17;B22:E26).

Теперь необходимо ввести исходные данные в соответствующие области формы, как это показано на рис. 2.22. После ввода данных получим форму, готовую для поиска решения транспортной задачи.

Рис. 2.22. Форма, заполненная для решения транспортной задачи

3. Поиск решения. Запускается процесс (рис. 2.23) поиска решения (Сервис ® Поиск решения…)

Рис. 2.23. Форма, заполненная для решения транспортной задачи

Более подробно диалог «Поиск решения» рассмотрен в предыдущей работе.

В диалоге, показанном на рисунке, необходимо:

- в поле «Установить целевую ячейку» указать абсолютную ссылку или имя ячейки, содержащей целевую функцию (в данном примере - $B$29);

- установить переключатель «Равной»: минимальному значению для минимизации целевой функции;

- в поле «Изменяя ячейки» установить ссылку на диапазон ячеек, значения которых будут изменяться при оптимизации целевой функции (в данном примере - $B$22:$E$26);

- определить граничные условия в секции «Ограничения».

Для проведения расчета необходимо задать необходимые ограничения.

Первое ограничение:

Объем перевозок товара не может быть больше имеющегося в данный момент на складах количества данного товара. Следовательно, значения ячеек диапазона F22:F26 должны быть меньше или равны значениям соответствующих ячеек диапазона H22:H26.

Для определения граничных условий, как это уже было рассмотрено в предыдущей работе, необходимо воспользоваться кнопкой «Добавить»диалога «Поиск решения» (2.24).Введем необходимое ограничение: объем перевозок по каждому товару не должен превышать имеющегося на складах количества данного товара.

Рис. 2.24. Окно поиска решения (после добавления первого ограничения)

После ввода ограничения на поиск решения нажмите «Добавить».

Второе ограничение

Объем перевозок товара должен полностью удовлетворить спрос на товар в пункте назначения. Т.е. объем перевозок должен быть равен спросу на данный товар, чтобы полностью удовлетворить потребность в данном товаре. Таким образом, значения ячеек диапазона F22:F26 должны быть равны соответствующим ячейкам диапазона J22:J26.

Следовательно, второе ограничение будет выглядеть так:

Рис. 2.25. Окно «Добавление ограничения» (после добавления
второго ограничения)

Нажмите «Добавить».

Третье ограничение

Объем перевозок по каждому товару должен быть больше или равен нулю:

Рис. 2.26. Окно «Добавление ограничения» (после добавления третьего ограничения)

Нажмите «Добавить».

Четвертое ограничение

Объем перевозки по каждому товару из каждого пункта отправления не должен превышать имеющегося в данном пункте отправления запаса данного товара. В противном случае получилось бы, что можно увести со склада больше, чем на нем есть, что невозможно. Следовательно, значения ячеек диапазона B22:E26 (объемы перевозок) должны быть меньше или равными значениям соответствующих ячеек диапазона B4:E8 (остатки товаров на складах). Условие в диалоге добавления ограничения должно выглядеть следующим образом:

Рис. 2.27. Окно «Добавление ограничения» (после добавления четвертого ограничения)

Нажмите ОК для завершения ввода ограничений. Получим следующий вид диалога «Поиск решения» (рис. 2.28).

Рис. 2.28. Вид окна «Поиск решения» после ввода ограничений

Настраиваются параметры поиска решения:

нажмите кнопку Параметры. В появившемся диалоге «Параметры поиска решения» необходимо установить следующие параметры: Линейная модель, «Оценки»:линейная, «Разности»:прямые, «Метод поиска»:Ньютона.

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

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

Если при текущих исходных данных, заданных ограничениях или параметрах настройки расчета решение найти не удается, то на экран будет выведено соответствующее сообщение. В этом случае необходимо либо изменить исходные данные, либо изменить введенные условия-ограничения, либо изменить параметры диалога «Поиск решения». Наиболее часто причиной невозможности найти решение являются неправильно заданные ограничения. Если решение найдено, пользователь увидит следующий диалог (2.29):

Рис. 2.29. Вид окна «Результаты поиска решения»

Нажмите ОК. Результаты проведенной операции будут представлены в форме, приведенной на рис. 2.30.

Рис. 2.30. Вид окна «Решения поставленной задачи»

Из рисунка видно, что получен оптимальный по стоимости план перевозок в заданной системе ограничений. При этом, спрос на товары в пункте назначения полностью удовлетворен. Полученный результат позволяет сделать вывод, что «Товар1» и «Товар2» необходимо вести только из Ростова, «Товар3» – только из Краснодара, «Товар4» – в определенных количествах из каждого города, «Товар5» – в установленных количествах из пунктов Ростова, Москва и Ставрополь.

Варианты заданий для самостоятельного решения

Вариант1.

Компания «ОРИОН» имеет пять заводов, которые производят однородную продукцию в следующих объемах:

Объемы производства продукции «Товар1»

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

Транспортные расходы

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

Рис. 2.31. Требуемый вид результата решения задачи по Варианту 1

Вариант 2.

В пункте назначения N существует спрос на товары, представленный в следующей таблице:

Спрос на товары в пункте N

На оптовых базах существуют запасы товаров, представленные в следующей таблице:

Запасы товаров на складах

Потребность доставки товара в каждую точку:

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

Рис. 2.32. Требуемый вид результата решения задачи по Варианту 2

Вопросы для самоконтроля

1. Перечислите основные цели, для которых строится математическая модель сложного объекта. Раскройте их содержание. Назовите основные причины проведения модельного эксперимента.

2. Какие цели ставит перед собой теория исследования операций, ее роль в принятии управленческих решений? Раскройте основное содержание терминов: "процесс", "операция", "метод", "ЛПР", "задача".

3. Что означает понятие "решение" в теории исследования операций? Какие виды решений вы знаете?

4. Дайте определение модели. Почему говорят об ограниченном отражении моделью сторон моделируемого объекта? Представьте основные особенности математической модели. Почему исследование на математической модели проще и быстрее, чем экспериментальные исследования на реальном объекте?

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

6. В чем заключается роль и место моделирования при проектировании систем? Какие модели используются при принятии решений в менеджменте производственных систем?

7. Почему в процессе подготовки и принятия решений часто используются имитационные модели и системы? Что такое имитационная модель?

8. Дайте определение экономико-математической модели (ЭММ). Как делятся факторы по их роли в ЭММ? Что такое "параметр" объекта? Какая связь между переменными и характеристиками объекта?

9. Что такое "начальные условия", "критерий оптимальности", "целевая функция", "система ограничений"? Приведите примеры формализации целевой функции, критерия оптимальности, системы ограничений и полной постановки задачи оптимизации.

10. Что означает термин "решение математической модели"? Что такое "оптимальное" и "рациональное" решения? Приведите простые примеры.

11. По характеру цели исследования ЭММ делятся на оптимизационные (нормативные) и описательные (дескриптивные или ЭММ прямого счета). Дайте пояснения этим терминам. Что означают понятия "монокритериальные" и "многокритериальные" ЭММ?

12. Что такое "описательные" модели? Чем они отличаются от моделей прямого счета? Приведите примеры некоторых видов и типов описательных моделей и используемых для их решения методов.

13. В чем суть и отличия аналитического и алгоритмического моделирования? В чем отличия детерминированных и стохастических, статических и динамических ЭММ?

14. Назовите основные принципы, определяющие общие требования, которым должна удовлетворять правильно построенная модель. Что означают принципы "адекватность" и "соответствие"?

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

16. Объясните содержание принципов построения моделей: "многовариантность реализаций элементов модели", "блочное строение".

17. Какие подходы к построению моделей возможны в зависимости от конкретной ситуации? В чем заключается основная суть подхода "анализ исходных данных"?

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

19. Что означает понятие "точно идентифицируемая модель"? Каковы основные принципы построения модели для поддержки принятия решений? В чем заключается их противоречивость и каковы пути достижения компромисса между ними?

20. В чем основная суть имитационного моделирования? Что такое "имитационная система"? Из каких основных этапов состоит имитационный эксперимент?

21. Какие основные группы экономико-математических моделей вы знаете? Каково основное назначение моделей оптимизации?

22. Что вы знаете о вероятностно-статистических моделях? Какие разделы прикладной статистики и эконометрики вы знаете?

23. Что значит статистика объектов нечисловой природы? Дайте понятие экспертных оценок. Что вы можете сказать о неопределенностях и путях их описания?

24. Учитывая, что принятие решения относится к компетенции ЛПР, представьте общую схему принятия решения. Опишите содержание подэтапов: построение формализованной модели, анализ свойств модели, аналитическое и эмпирическое исследование модели.

25. Какие этапы составляют процесс построения концептуальной модели? Опишите их содержание.

26. Что значит понятие "решить модель"? Что составляет суть процесса разработки алгоритма модели? Представьте основные этапы этого процесса.

27. Опишите процесс разработки программы как этапа машинной реализации моделей.

28. Как проводится машинный эксперимент с моделью системы? Опишите основные этапы этого процесса. В каком виде представляются результаты выполнения этого этапа?

29. Перечислите основные этапы формирования статистических решений. В чем заключается основное содержание процесса сбора информации при статистических исследованиях? Чем обусловлены основные ошибки при реализации этого этапа?

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

31. Дайте понятие и назовите основные виды статистического наблюдения. Роль статистического наблюдения при статистических исследованиях.

32. Статистическая совокупность как категория статистических исследований. Что такое "признак" в статистике? Количественные и дискретные признаки, основные и второстепенные признаки. Дайте объяснение понятиям: "показатель", "вариация", "система показателей".

33. Что понимают под классификацией в статистике? Что вы знаете о классификации исходных данных при статистических исследованиях? Какие признаки группировок вы знаете?

34. В чем состоит суть метода статистического моделирования (или метода Монте-Карло) как способа исследования поведения вероятностных систем (экономических, технических и т.д.)? В чем состоит содержание основных этапов решения любой задачи методом статистического моделирования?

35. Представьте некоторые способы моделирования случайных величин, случайных событий и случайных функций.

36. Представьте основную суть моделирования систем массового обслуживания с использованием метода Монте-Карло.

37. Представьте основную суть моделирования потоков отказов элементов сложных технических систем.

38. Что вы знаете о методах и моделях корреляционно-регрессионного анализа (общие сведения)? Что такое регрессия и какие виды регрессии вы знаете? Что относят к задачам регрессионного анализа?

39. Что такое корреляция и какие виды корреляции вы знаете? Что относят к задачам корреляционного анализа?

40. Назовите основные исходные предпосылки регрессионного анализа и свойства оценок.

41. Перечислите основные этапы построения многофакторной корреляционной регрессионной модели и представьте их суть.

42. Дайте определение мультиколлинеарности. В чем заключается анализ мультиколлинеарности и каковы способы снижения влияния мультиколлинеарности на значимость модели?

43. Приведите основные этапы процедуры отбора главных факторов при проведении корреляционно-регрессионного анализа.

44. Что такое адекватность модели и как осуществляется ее проверка?

45. Дайте определение оптимизационной задачи. Что называется задачей линейного программирования? Опишите основные особенности применения метода линейного программирования при решении оптимизационных задач.

46. Приведите пример задачи линейного программирования с использованием аналитического метода.

47. Опишите особенности решения задач линейного программирования графическим методом. Приведите конкретный пример.

48. Представьте основные особенности решения задач линейного программирования с использованием симплекс-метода. Приведите простой пример.