Решение. 1. Построим экономико-математическую модель представленной транспортной задачи.

1. Построим экономико-математическую модель представленной транспортной задачи.

Обозначим через – объём перевозки угля от i –ой шахты до j –ой перерабатывающей фабрики.

Тогда суммарные транспортные затраты на перевозку составят:

Заданные объемы добычи угольных шахт и производственные возможности перерабатывающих фабрик накладывают ограничения на значения объемов перевозок угля :

Мощность всех шахт должна быть реализована:

Потребности фабрик должны быть удовлетворены:

Объемы перевозимого угля не могут быть отрицательными:

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

2. Определим с помощью надстройки «Поиск решения» в Microsoft Excel оптимальное распределение поставок угля с угольных шахт на перерабатывающие фабрики, при котором совокупные транспортные издержки будут минимальны.

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

Далее на листе 1 новой книги Microsoft Excel, названной «Транспортная задача», в ячейки А3: Е12 введем исходные данные как на рис. 13.

Рис. 13. Ввод исходных данных задания 2

В ячейках А15: D19 сформируем матрицу оптимального распределения поставок угля, причем ячейки В16: D19 заполняем нулями (см. рис. 14).

В ячейку Е15 введем «Вывезенное из шахт количество угля», а в ячейку Е16 формулу, определяющую общее количество угля, вывезенного из первой шахты: «=СУММ(B16: D16)». Далее копируем формулу из ячейки Е16 в ячейки Е17: Е19, как на рис. 14.

В ячейку А20 введем «Поступившее на фабрики количество угля», а в ячейку В20 формулу, определяющую общее количество угля, поступившего из всех шахт на первую перерабатывающую фабрику: =СУММ(B16: B19)». Далее копируем формулу из ячейки В20 в ячейки С20: D20, как на рис. 14.

Рис. 14. Ввод формул задания 2

Затем в ячейку А22 введем «Целевая функция (суммарные транспортные затраты на перевозку угля):», а в ячейку Е22 – формулу:
«=B3*СУММПРОИЗВ(B8: D11; B16: D19)» (в поле «Массив 1» окна ввода аргументов функции СУММПРОИЗВ указываются ячейки B8: D11, содержащие расстояние от шахт до углеперерабатывающих фабрик, в поле «Массив 2» – ячейки B16: D19, содержащие оптимальное распределение поставок угля от шахт до углеперерабатывающих фабрик).

Результатом расчета суммарных транспортных затрат на перевозку угля в Microsoft Excel на данном этапе выполнения задания является значение, равное нулю (рис. 15). В дальнейшем (после выполнения некоторых манипуляций) в ячейке Е22 будут рассчитаны минимальные суммарные транспортные затраты на перевозку угля.

Рис. 15. Результат вычисления формул задания 2

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

Для этого необходимо выделить ячейку, содержащую значение целевой функции (ячейку Е22), и выполнить команду «Сервис»/ «Поиск решения…». В открывшемся окне надстройки «Поиск решения…» в поле «Целевая ячейка» уже будет указано: «$Е$22» (т.е. ячейка, содержащая значение суммарных транспортных затрат на перевозку угля), необходимо установить флажок «Равной минимальному значению», в поле «Изменяя ячейки» – выбрать «$В$16: $D$19», т.е. ячейки, содержащие матрицу оптимального распределения поставок угля (рис. 16). Далее указываем ограничения следующим образом: нажимаем кнопку «Добавить», и в открывшемся окне вводим ограничение по мощности всех угольных шахт (мощность всех шахт должна быть реализована) (рис. 17), еще раз нажимаем кнопку «Добавить» и вводим ограничение по производственным возможностям углеперерабатывающих фабрик (потребности фабрик должны быть удовлетворены) (рис. 18), еще раз нажимаем кнопку «Добавить» и вводим условие неотрицательности (объемы перевозимого угля не могут быть отрицательными) (рис. 19), нажимаем «ОК». В окне поиска решений (рис. 16) нажимаем кнопку «Параметры», в открывшемся окне «Параметры поиска решения» устанавливаем флажок «Линейная модель», нажимаем «ОК». В окне поиска решений (рис. 16) нажимаем «Выполнить».

Рис. 16. Окно " Поиск решений" (транспортная задача)

Рис. 17. Ввод ограничения по мощности всех угольных шахт

Рис. 18. Ввод ограничения по производственным возможностям углеперерабатывающих фабрик

Рис. 19. Ввод условия неотрицательности

В результате получим такое распределение поставок угля с шахт на перерабатывающие фабрики (ячейки В16: D19), при котором суммарные транспортные издержки принимают минимальное значение, равное 6 593, 4 тыс.руб. (ячейка Е22) (рис. 20).

Рис. 20. Результат поиска решения (оптимального распределения поставок угля)

3. Дадим экономическую интерпретацию полученного решения. Сформулируем оптимальное управленческое решение в описанных условиях.

Итак, для того, чтобы совокупные транспортные издержки на перевозку угля от шахт до перерабатывающих фабрик, осуществляющуюся с помощью железнодорожного транспорта, были минимальны и составили 6 593, 4 тыс.руб. необходимо придерживаться следующего оптимального плана распределения поставок угля:

– с первой шахты весь объем добытого за день угля (это 210 т.) необходимо перевезти на первую перерабатывающую фабрику;

– со второй шахты весь объем добытого за день угля (это 150 т.) необходимо перевезти на вторую перерабатывающую фабрику;

– с третьей шахты 80 т. необходимо перевезти на первую перерабатывающую фабрику и 50 т. – на третью;

– с четвертой шахты 60 т. необходимо перевезти на вторую перерабатывающую фабрику и 130 т. – на третью.

4. Найдем с помощью надстройки «Поиск решения» в Microsoft Excel оптимальное распределение поставок угля с угольных шахт на перерабатывающие фабрики при условии, что в регионе открылась пятая угольная шахта с объемом добычи угля в тонн в день, расстояние от которой до перерабатывающих фабрик составляет соответственно () км.

Дополнительные исходные данные транспортной задачи представлены в таблице:

Показатель
Значение

Сначала составим экономико-математическую модель представленной транспортной задачи с измененными условиями:

Проверим тип представленной транспортной задачи с измененными условиями.

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

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

После введения фиктивной фабрики задача становится закрытой, и её математическая модель будет иметь вид:

Далее на листе 2 книги «Транспортная задача» Microsoft Excel, в ячейки А1: F11 введем исходные данные дополненной задачи как на рис. 21.

Рис. 21. Ввод исходных данных транспортной задачи с измененными условиями

В ячейках А14: Е19 сформируем матрицу оптимального распределения поставок угля, причем ячейки В15: Е19 заполняем нулями (см. рис. 22).

В ячейку F14 введем «Вывезенное из шахт количество угля», а в ячейку F15 формулу, определяющую общее количество угля, вывезенного из первой шахты: «=СУММ(B15: Е15)». Далее копируем формулу из ячейки F15 в ячейки F16: F19, как на рис. 22.

В ячейку А20 введем «Поступившее на фабрики количество угля», а в ячейку В20 формулу, определяющую общее количество угля, поступившего из всех шахт на первую перерабатывающую фабрику: =СУММ(B15: B19)». Далее копируем формулу из ячейки В20 в ячейки С20: E20, как на рис. 22.

Рис. 22. Ввод формул транспортной задачи с измененными условиями

Затем в ячейку А22 введем «Целевая функция (суммарные транспортные затраты на перевозку угля):», а в ячейку Е22 – формулу:
«=B1*СУММПРОИЗВ(B6: E10; B15: E19)» (рис. 23).

Результатом расчета суммарных транспортных затрат на перевозку угля в Microsoft Excel на данном этапе выполнения задания является значение, равное нулю (рис. 23). В дальнейшем (после выполнения некоторых манипуляций) в ячейке Е22 будут рассчитаны минимальные суммарные транспортные затраты на перевозку угля.

Рис. 23. Результат вычисления формул задачи с измененными условиями

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

Для этого необходимо выделить ячейку, содержащую значение целевой функции (ячейку Е22), и выполнить команду «Сервис»/ «Поиск решения…». В открывшемся окне надстройки «Поиск решения…» в поле «Целевая ячейка» уже будет указано: «$Е$22» (т.е. ячейка, содержащая значение суммарных транспортных затрат на перевозку угля), необходимо установить флажок «Равной минимальному значению», в поле «Изменяя ячейки» – выбрать «$В$15: $E$19», т.е. ячейки, содержащие матрицу оптимального распределения поставок угля (рис. 24). Далее указываем ограничения по мощности всех угольных шахт, по производственным возможностям углеперерабатывающих фабрик, а также условие неотрицательности (рис.24) (аналогично тому, как это было показано ранее). В окне поиска решений (рис. 24) нажимаем кнопку «Параметры», в открывшемся окне «Параметры поиска решения» устанавливаем флажок «Линейная модель», нажимаем «ОК». В окне поиска решений (рис. 24) нажимаем «Выполнить».

Рис. 24. Окно " Поиск решений" (задача с измененными условиями)

В результате получим такое распределение поставок угля с шахт на перерабатывающие фабрики (ячейки В15: Е19), при котором суммарные транспортные издержки принимают минимальное значение, равное 6 193, 8 тыс.руб. (ячейка Е22) (рис. 25).

Рис. 25. Результат поиска решения (оптимального распределения поставок в задаче с измененными условиями)

Дадим экономическую интерпретацию полученного решения.

Итак, для того, чтобы совокупные транспортные издержки на перевозку угля от шахт до перерабатывающих фабрик, осуществляющуюся с помощью железнодорожного транспорта, были минимальны и составили 6 193, 8 тыс.руб. необходимо придерживаться следующего оптимального плана распределения поставок угля:

– с первой шахты весь объем добытого за день угля (это 210 т.) необходимо перевезти на первую перерабатывающую фабрику;

– со второй шахты весь объем добытого за день угля (это 150 т.) необходимо перевезти на вторую перерабатывающую фабрику;

– с третьей шахты 80 т. необходимо перевезти на первую перерабатывающую фабрику и 50 т. – на третью;

– с четвертой шахты 60 т. необходимо перевезти на вторую перерабатывающую фабрику и 10 т. – на третью, при этом 120 т. добытого угля так и не будет вывезено;

– с пятой шахты весь объем добытого за день угля (это 120 т.) необходимо перевезти на третью перерабатывающую фабрику.

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

Дополнительные исходные данные транспортной задачи представлены в таблице:

Показатель
Значение

Сначала составим экономико-математическую модель представленной транспортной задачи с измененными условиями:

Проверим тип представленной транспортной задачи с измененными условиями.

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

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

После введения фиктивной шахты задача становится закрытой, и её математическая модель будет иметь вид:

Далее на листе 3 книги «Транспортная задача» Microsoft Excel, в ячейки А1: F11 введем исходные данные дополненной задачи как на рис. 26.

Рис. 26. Ввод исходных данных второй задачи с измененными условиями

Далее выполняется последовательность действий, аналогичная п. 4.

В результате поиска решения получим такое распределение поставок угля с шахт на перерабатывающие фабрики (ячейки В15: Е19), при котором суммарные транспортные издержки принимают минимальное значение, равное 5 568, 5 тыс.руб. (ячейка Е22) (рис. 27).

Рис. 27. Результат поиска решения (оптимального распределения поставок во второй задаче с измененными условиями)

Дадим экономическую интерпретацию полученного решения.

Итак, для того, чтобы совокупные транспортные издержки на перевозку угля от шахт до перерабатывающих фабрик, осуществляющуюся с помощью железнодорожного транспорта, были минимальны и составили 5 568, 5 тыс.руб. необходимо придерживаться следующего оптимального плана распределения поставок угля:

– с первой шахты 150 т. добытого угля необходимо перевезти на первую перерабатывающую фабрику и 60 т. – на вторую;

– со второй шахты весь объем добытого за день угля (это 150 т.) необходимо перевезти на вторую перерабатывающую фабрику;

– с третьей шахты весь объем добытого за день угля (это 130 т.) необходимо перевезти на третью перерабатывающую фабрику;

– с четвертой шахты 40 т. добытого угля необходимо перевезти на третью перерабатывающую фабрику и 150 т. – на четвертую.

Кроме того, важно отметить, что согласно полученному оптимальному распределению перевозки угля производственная потребность первой перерабатывающей фабрики не будет удовлетворена на 140 т., а третьей фабрики – на 10 т.