Решение экономических задач средствами MS Excel

Задание

1. Изучить информацию о задачах линейного программирования и о средствах их решения в среде Excel (подбор параметра, поиск решения).

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

3. Продемонстрировать решение задачи в среде Excel и интерпретировать полученное решение с точки зрения смысла задачи.

Обязательные требования к оформлению решения

  1. Создайте папку под именем вида Фамилия_ЭЗ
  2. Каждую из предложенных задач решить в отдельной рабочей книге, которой присвоить имя Фамилия_№задачи и сохранить в созданной папке Фамилия_ЭЗ
  3. Каждая рабочая книга должна содержать четыре листа:

Распределение задач по варантам

№ варианта
1 2 3 4 5 6 7 8 9 10 11 12 13 14
1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8 1.9 1.10 1.11 1.12 1.13 1.14
2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 2.10 2.11 2.12 2.13 2.14

Задачи линейного программирования

1. Задачи о рациональном использовании сырья

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

1.1.

1.2.

1.3.

1.4.

1.5.

1.6.

1.7.

1.8.

1.9.

1.10.

1.11.

1.12.

1.13.

1.14.

2. Транспортные задачи

2.1. Имеются два мелькомбината I , II и три пекарни А, В, С. Производительность каждого из комбинатов равна 30 тонн. Потребность в муке каждой из пекарней А, В, С составляет 20 тонн. Стоимость перевозки 1 тонны муки с мелькомбината I в пекарни А, В, С равна соответственно 2, 5, 4 денежных единиц, с мелькомбината II – 1 , 3, 5 денежных единиц. Необходимо составить план перевозки муки, чтобы затраты на перевозку были минимальными.

2.2. В трех хранилищах А1, А2, А3 имеется соответственно 70, 90, 50 т топлива. Требуется спланировать перевозку топлива потребителям В1, В2, В3, В4, В5 спрос которых равен соответственно 50, 70, 40, 40, 10 т, так, чтобы затраты на транспортировку были минимальными. Стоимость перевозки указаны в таблице:

Хранилища

В1

В2

В3

В4

В5

Запас топлива

А1

5

2

3

6

0

70

А2

4

3

5

7

0

90

А3

2

4

1

5

0

50

Потребность

50

70

40

40

10

210

2.3. С трех складов А1, А2, А3 необходимо доставить овощи в пять торговых точек В1, В2, В3, В4, В5. Требуется закрепить склавды за торговыми точками так, чтобы общая сумма затрат на перевозку была минимальной. Стоимость перевозок приведена в таблице:

Склады

В1

В2

В3

В4

В5

Объем вывоза

А1

7

3

5

4

2

40

А2

6

2

3

1

7

150

А3

3

5

2

6

4

100

Объем вывоза

20

80

90

60

40

290

2.4. Составить план первозок зерна из районов А1, А2, А3, А4 республики, в которых запасы соответственно 800, 700, 1000 и 500 тыс. ц, на три элеватора В1, В2 И В3мощностью 1000, 1100 и 900 тыс. ц. Затраты на перевозку 1ц зерна из районов на элеваторы приведены в таблице:

Районы

В1

В2

В3

Запас

А1

3

5

6

800

А2

7

2

4

700

А3

4

3

5

1000

А4

6

4

7

500

Мощность элеватора

1000

1100

900

3000

2.5. Найти решение транспортной задачи, исходные данные которой приведены в таблице:

Пункты отправления

Пункты назначения

Запасы

В1

В2

В3

В4

В5

А1

5

3

2

4

8

160

А2

7

6

5

3

1

90

А3

8

9

4

5

2

140

Потребности

90

60

80

70

90

390

2.6. Найти решение транспортной задачи, исходные данные которой приведены в таблице

Заводы

Бензохранилища

Производство

В1

В2

В3

В4

А1

8

7

3

0

40

А2

2

4

9

1

80

А3

9

5

5

8

70

А4

3

4

9

11

40

Вместимость

40

60

50

80

230

2.7. С двух заводов А и В развозят бетон на три стройплощадки. Запасы завода А – 320 тонн, завода В – 380 тонн. Потребность стройплощадки I в бетоне 200 тонн, стройплощадки II – 280 тонн, стройплощадки III – 220 тонн. Стоимость перевозки одной тонны бетона с завода А на стройплощадку I – 2000 денежных единиц, на стройплощадку II – 4000 денежных единиц, на стройплощадку III – 6000 денежных единиц. Стоимость перевозки 1 тонны бетона с завода В на стройплощадку I – 5000 денежных единиц, на стройплощадку II – 5000 денежных единиц, на стройплощадку III – 3000 денежных единиц. Как спланировать снабжение стройплощадок так, чтобы затраты на перевозку были наименьшими?

2.8. Перевозится однородный груз из двух пунктов А и В к трем местам назначения – I , II , III . Ежедневно отправляется 65 тонн. В том числе из пункта А – 40 тонн, из В – 25 тонн. В пункты назначения должны поступить следующие количества груза: в пункт I – 10 тонн, во II – 35 тонн, в III – 20 тонн. Расстояние от пункта А до пунктов I , II , III равны соответственно 7, 2 и 4 км . Расстояние от пункта В до пунктов I , II , III равны соответственно 3, 8 и 9 км . Требуется составить план перевозки, обеспечивающий наименьший общий пробег грузов в тонно-километрах.

2.9. В пунктах А и В расположены кирпичные заводы, а в пунктах С и Д – карьеры, снабжающие их песком. Потребность заводов в песке не больше производительности карьеров и равна соответственно 40 и 50 тонн. Количество добываемого песка в каждом из карьеров и стоимость перевозки (в денежных единицах) одной тонны песка из каждого карьера к заводам представлены в виде таблицы

Карьеры

Заводы

Количество добываемого песка

А

В

С

2

6

70 тонн

Д

5

3

30 тонн

Как спланировать снабжение заводов песком, чтобы затраты были наименьшими?

2.10. Есть два пункта производства однородного продукта и пункты потребления М, Р и К, которым необходимо 15, 20, 10 единиц этого продукта. Первый пункт производит 25 единиц продукции, второй пункт – 20 единиц. Стоимость доставки единицы продукции в пункты потребления определена таблицей:

 

М

Р

К

1 пункт

1

3

5

2 пункт

2

4

3

Составить план распределения продукта при минимальных затратах.

2.11. Имеется два склада готовой продукции и три потребителя этой продукции. Склад I располагает 10 000 единиц продукции, а склад II – 5000 единиц продукции. Потребителям нужно соответственно: I –4000 единиц, II – 8000 единиц, III - 3000 единиц. Стоимость доставки единицы продукции с каждого склада каждому потребителю определена следующей таблицей:

 

1 потребитель

2 потребитель

3 потребитель

I склад

3

3

2

II склад

6

5

1

Составить такой план перевозок, который соответствует минимальной суммарной стоимости.

2.12. На трех складах ( I , II , III ) имеются соответственно 90, 70, 50 тонн муки, которую надо перевезти в магазины – 1, 2, 3, 4 соответственно в количестве 80, 60, 40, 30 тонн. Необходимо составить оптимальный план перевозки муки, если стоимость перевозки одной тонны в магазины 1. 2, 3, 4 со склада I равна соответственно 2, 1, 3, 2 денежных единицы, со склада II равна соответственно 2, 3, 3, 1 денежных единицы, со склада III равна соответственно 3, , 3, 2, 1 денежных единицы.

2.13. В некоторой местности в двух пунктах А и В имеется потребность в дополнительном транспорте. В пункте А требуется 5 дополнительных автобусов, а в пункте В- 7. Известно, что 3, 4, 5 автобусов могут быть получены соответственно из гаражей G1, G2, G3.
Как следует распределить эти автобусы между пунктами А и В, чтобы минимизировать их суммарный пробег? Расстояния от гаражей до пунктов А и В приведены в таблице:

Гараж

 

Расстояния до пунктов

А

В

G1

3

4

G2

1

3

G3

4

2

2.14. Три поставщика одного и того же продукта располагают в планируемый период следующими запасами этого продукта: первый- 120 условных единиц, второй- 100 и третий 80 единиц. Этот продукт должен быть перевезен к трем потребителям, спросы которых соответственно равны 90, 90 и 120 условных единиц. Приведенная ниже таблица содержит показатели затрат, связанных с перевозкой продукта из i-го пункта отправления в j-й пункт потребления.

Поставщики

Потребители и их спрос

Запасы

 

А

Б

В

 

I

7

6

4

120

II

3

8

5

100

III

2

3

7

80

Спрос

90

90

120

 

Требуется перевезти продукт с минимальными затратами.