20

21

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

2000000

22

Непредвиденные расходы

100000

23

Страхование в пути

0.02

=B23*B9

24

Акциз

0.1

=B24*B9

25

НДС

0.1

=B25* (B9+C23+C24+C26+C27)

26

Таможенный сбор

0.01

=B26*B9

27

Таможенный налог

0.15

=B27*B9

28

29

Реализация товара и возврат кредита

30

31

Цена реализации за ед., руб.

25000

32

Цена реализации за партию, руб.

=B31*B8

33

Возврат кредита с %, руб.

=B16+B17*B14

34

Прибыль до вычёта налогов, руб.

=B32-B33

Рис. 2.2.1 Шаблон MS Excel для решения задачи

2. Использование надстройки MS Excel "Подбор параметра"

2.1 С заданными исходными данными значение прибыли до вычета налогов, рассчитанное в ячейке B34, является отрицательным. Если нужно обеспечить безубыточность операции, т.е. нулевое значение прибыли, меняют цену реализации (цену реализации в ячейке В31 измените на 27000 и убедитесь, что значение прибыли (убытка) в В34 изменилось). Можно изменять значение в ячейке В31 вручную, значение прибыли в ячейке В34 будет меняться, но такой вариант требует значительных затрат времени, не точен, а следовательно, неэффективен.

Значение цены реализации подбирают автоматически. Для этого вызовите надстройку Подбор параметра командой Сервис Подбор параметра и заполните в открывшемся окне поля так, как изображено на рис.2.2.2 После нажатия на кнопку ОК убедитесь (по сообщению в открывшемся окне), что решение найдено.

Рис. 2.2.2 Пример заполнения окна Подбор параметра

2.2 Решите по аналогии с п.2.1 следующую задачу: получите прибыль X руб. за счёт изменения параметра Y. Номер варианта задания выберите в соответствии с номером компьютера. Варианты заданий приведены в табл. 2.2.1.

Таблица 2.2.1

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

Номер варианта	Значение прибыли X, руб.	Название варьируемого параметра Y
1	10 000 000	Закупочная цена (B7)
2	3 000 000	% по кредиту (В13)
3	500 000	Страхование в пути (В23)
4	2 000 000	Акциз (В24)
5	2 500 000	НДС (В25)
6	300 000	Таможенный налог (В27)
7	8 000 000	Срок кредита (В14)
8	800 000	Страхование кредита (В15)
9	2 500 000	Транспортные расходы (В21)
10	200 000	Непредвиденные расходы (В22)
11	1 750 000	Таможенный сбор (В26)

Покажите полученную рабочую книгу MS Excel преподавателю.

2.3 Лабораторная работа № 3. Многовариантный анализ экономической информации

Цель работы: изучение технологий автоматизации процедур анализа многовариантных решений на примере анализа чувствительности экономических показателей проекта.

Постановка задачи. Анализ чувствительности - оценка влияния одного или нескольких аргументов в формуле на значение функции. Он позволяет получить ответ на вопрос типа "как (на какое значение? в какую сторону?) будет изменяться результат проекта, если изменять один или несколько исходных параметров в некоторых пределах (обычно линейно-арифметической прогрессией, логарифмически - геометрической, или из списка).

В лабораторной работе рассматривается инвестиционный проект, связанный с выпуском продукции. Основные показатели проекта приведены в табл. 2.3.1.

Таблица 2.3.1

Основные показатели проекта и диапазон их изменения

Наименование показателя	Обозначение	Ожидаемое наихудшее значение	Наиболее вероятное значение	Ожидаемое наилучшее значение
1	2	3	4	5
Объём выпуска изделий, шт.	Q	150	200	300
Цена одного изделия, руб.	P	35	50	55
Переменные затраты, руб.	V	40	30	25
Постоянные затраты, руб.	F	400	500	600
Амортизация, руб.	A	100	100	100
Налог на прибыль, %	T	60%	60%	30%
Норма дисконта, %	R	8%	10%	15%
Срок проекта, лет	N	5	5	7
Остаточная стоимость, руб.	S	50	200	500
Начальные инвестиции, руб.	I	2000	2000	2000

Необходимо выполнить анализ чувствительности чистой современной стоимости проекта (NPV) к изменению ключевых показателей. Чистая современная стоимость проекта NPV определяется следующим аналитическим выражением:

. (2.3.1)

Порядок решения задачи

1. Подготовка исходных данных

Для автоматизации процедуры оценки чувствительности NPV создайте шаблон в MS Excel (рис.2.3.1). Присвойте листу имя "Анализ чувствительности NPV".

	A	B	C	D
1	Анализ чувствительности NPV
2	Количество	0.00	Начальные инвестиции	0.00
3	Цена	0.00	Постоянные расходы	0.00
4	Переменные расходы	0.00	Амортизация	0.00
5	Норма дисконта	0.00	Остаточная стоимость	0.00
6	Срок реализации	0.00	Налог на прибыль	0.00
7
8
9	Чистые платежи	0.00		значение NPV
10			варьир. параметр	0.00

Рис. 2.3.1 Шаблон MS Excel для решения задачи анализа чувствительности

Значения параметров в шаблоне заполните наиболее вероятными значениями, взятыми из табл. 2.3.1.

Ячейкам, в которых располагаются параметры проекта, для улучшения визуализации формул, а также для оптимизации условий их копирования-перемещения присвойте символьные имена. Например, ячейка B2 должна именоваться не адресом на листе "Анализ чувствительности NPV" B2, а символьным именем "Количество". Последовательность присвоения имени: выделите ячейку B2, выполните команду Вставка Имя Присвоить, согласитесь с именем, предлагаемым MS Excel - нажмите кнопку OK. Если имя, предлагаемое MS Excel по умолчанию, Вас не устраивает, введите имя с клавиатуры; при этом не должно появиться совпадающих имен, а также имя ячейки не должно содержать пробелов. Символьные имена для ячеек, а которых располагаются параметры проекта, приведены в табл. 2.3.2.

Таблица 2.3.2

Символьные имена для ячеек в шаблоне MS Excel

Адрес ячейки	Имя переменной (ячейки)	Адрес ячейки	Имя переменной (ячейки)
В2	Количество	D2	Начальные_инвестиции
В3	Цена	D3	Постоянные_расходы
В4	Переменные_расходы	D4	Амортизация
В5	Норма_дисконта	D5	Остаточная_стоимость
В6	Срок_Реализации	D6	Налог_на_прибыль
B9	Чистые_платежи	D10	NPV

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

(2.3.2)

(2.3.3)

где ПС (ставка; кпер; выплата; бз; тип) - финансовая функция MS Excel, возвращающая текущий объём вклада. Текущий объём - это общая сумма, которую составят будущие платежи. Например, когда деньги берутся взаймы, заимствованная сумма и есть текущий объём для заимодавца. Более подробную информацию об аргументах функции можно получить в справочной системе MS Eхcel. В пакете MS Office 2000 эта функция носит имя ПЗ.

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

После ввода формул (2.3.2) и (2.3.3) ячейки B9 и D10 должны принять значения 1460 и 3658 руб. соответственно. Формат ячеек Денежный при желании можно задать соответствующей командой (Формат ячейки Число).

2. Однопараметрический анализ чувствительности

Пусть значение цены изделия будет меняться в диапазоне от 55 руб. до 35 руб. с шагом 5 руб. (см. табл. 2.3.1). Требуется определить функцию NPV при данных значениях цены:

- ячейки С11: С15 заполните значениями цены изделия 55, 50, 45, 40, 35 соответственно;

- выделите диапазон ячеек С10: D15;

- вызовите команду Данные Таблица подстановки. В появившемся окне (рис. 2.3.2) укажите, что по строкам выделенного диапазона располагаются значения цены изделия B3;

- после нажатия на кнопку ОК ячейки D11: D15 заполните значениями функции NPV, формула которой взята из ячейки D10, при различных значениях цены изделия, размещённой в ячейках С11: С15.

-

Рис. 2.3.2 Окно настроек команды Таблица подстановки

3. Двухпараметрический анализ чувствительности

Пусть одновременно будут изменяться значение цены в диапазоне от 55 руб. до 35 руб. с шагом 5 руб. и значение объёма выпуска изделий со 150 шт. до 300 шт. с шагом 50 шт. (см. табл.2.3.1). Требуется определить функцию NPV при любых комбинациях этих двух параметров:

- формулу для NPV переместите из ячейки D10 в ячейку С10 (Команды Копировать и Вставить выполняются для выделенной формулы в строке формул соответствующих ячеек);

- ячейки D10: Н10 заполните значениями объёма выпуска изделия 300, 250, 200, 150, 100 соответственно;

- выделите диапазон ячеек С10: H15;

- вызовите команду Данные Таблица подстановки. В открывшемся окне укажите, что по строкам выделенного диапазона располагаются значения цены изделия B3, а по столбцам - значения объёма выпуска B2, после чего нажмите кнопку ОК.

4. Построение диаграммы

По результатам анализа чувствительности функции NPV к значению параметров Цена изделия и Объём выпуска изделий строится объёмная гистограмма:

- выделите диапазон ячеек D11: H15;

- вызовите команду Вставка Диаграмма. В открывшемся окне последовательно выберите Объёмную гистограмму: во вкладке Ряд окна Исходные данные в поле "Подписи оси Х" внесите диапазон ячеек D10: H10 (выделите диапазон мышью); в окне Размещение диаграммы желательно установить переключатель в режим "На имеющемся листе", остальные настройки диаграммы выполните произвольно.

5. Изучение функции MS Excel "Диспетчер сценариев"

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

Рассчитайте значение функции NPV для трёх наборов входных переменных (сценариев), отличающихся значениями переменных Q, P, V, R, N, S:

- выделите диапазон ячеек В2: В6;

- вызовите команду Сервис Сценарии;

- в открывшемся окне Диспетчер сценариев после нажатия кнопки Добавить дайте название первому сценарию "Вероятный"; укажите диапазон изменяемых ячеек B2: B6; в следующем окне Значение ячеек сценария введите наиболее вероятные значения этих параметров из табл.2.3.1;

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

- не закрывая окна Диспетчер сценариев, нажмите кнопку Отчёт, выберите настройку Структура, а ячейка результата - С10. Полученный лист "Структура сценария" будет отображать значения величины NPV из ячейки С10 для трёх наборов исходных параметров проекта - вероятного, наилучшего, наихудшего.

- откройте окно Диспетчер сценариев на листе рабочей книги MS Excel "Анализ чувствительности NPV", убедитесь, что Ваш набор сценариев сохранен, и выведите отчёт в формате сводной таблицы для диапазона ячеек D11: H15. Оцените полученный лист отчёта.

6. Задание № 1 для самостоятельного выполнения

По образцу п.2 на листе рабочей книги MS Excel "Анализ чувствительности NPV" (в свободном месте) выполните однопараметрический анализ чувствительности NPV. Варианты заданий приведены в табл. 2.3.3 Диапазоны изменения параметров следует взять из табл. 2.3.1 Шаг изменяемого параметра выбирается самостоятельно так, чтобы количество значений варьируемого параметра в таблице подстановки было в пределах 4.8.

Таблица 2.3.3

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

Номер варианта	Название варьируемого параметра	Расположение варьируемого параметра
1	Объём выпуска изделий, шт.	по строкам
2	Норма дисконта, %	по строкам
3	Переменные затраты, руб.	по строкам
4	Постоянные затраты, руб.	по строкам
5	Остаточная стоимость, руб.	по строкам
6	Налог на прибыль, %	по строкам
7	Норма дисконта, %	по столбцам
8	Переменные затраты, руб.	по столбцам
9	Постоянные затраты, руб	по столбцам
10	Остаточная стоимость, руб.	по столбцам
11	Налог на прибыль, %	по столбцам

7. Задание № 2 для самостоятельного выполнения

По образцу п.3 на листе Вашей рабочей книги MS Excel "Анализ чувствительности NPV" выполните анализ чувствительности NPV к изменению одновременно двух входных параметров проекта. Варианты заданий приведены в табл. 2.3.4 Диапазоны изменения параметров следует взять из табл. 2.3.1 Шаг изменяемого параметра выбирается самостоятельно так, чтобы количество значений каждого варьируемого параметра в таблице подстановки было в пределах 3.6.

Таблица 2.3.4

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

Номер варианта	Название первого варьируемого параметра	Название второго варьируемого параметра
1	Объём выпуска изделий, шт.	Налог на прибыль, %
2	Норма дисконта, %	Переменные затраты, руб.
3	Переменные затраты, руб.	Объём выпуска изделий, шт.
4	Постоянные затраты, руб.	Переменные затраты, руб.
5	Остаточная стоимость, руб.	Норма дисконта, %
6	Налог на прибыль, %	Остаточная стоимость, руб.
7	Норма дисконта, %	Налог на прибыль, %
8	Переменные затраты, руб.	Остаточная стоимость, руб.
9	Постоянные затраты, руб.	Объём выпуска изделий, шт.
10	Остаточная стоимость, руб.	Объём выпуска изделий, шт.
11	Налог на прибыль, %	Переменные затраты, руб.

Результаты выполнения работы представьте преподавателю.

2.4 Лабораторная работа № 4. Решение оптимизационных задач

Цель работы: изучение технологии решения задач условной оптимизации функций нескольких переменных в среде MS Excel.

Постановка задачи. Рассматривается типичная модель сбыта продукции, отражающая зависимость прибыли от объёма продаж, зарплаты персонала, расходов на рекламу и т.д. Требуется максимизировать общую прибыль от операции, варьируя какие-либо параметры, например расходы на рекламу.

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

, (2.4.1)

где V₀ - нормирующий коэффициент; K_s - коэффициент сезонности; С = 3000 - постоянная; R - расходы на рекламу.

Данная модель отражает нелинейную зависимость эффективности рекламы. В соответствии с этой моделью вложения 5000 руб. в рекламу в первом квартале принесут увеличение объёмов продаж на 1092 единицы, тогда как такие же вложения во втором квартале увеличат объём продаж только на 775 единиц. Накладные расчёты исчисляются из расчёта 15 % от объёмов реализации. Затраты на сбыт определяются удельными затратами на одно изделие (25 руб.)

Порядок решения задачи

1. Подготовка исходных данных

1.1 Введите на рабочем листе Excel следующие исходные данные (рис. 2.4.1).

	A	B	C	D	E	F
1	Лабораторная работа № 4
2	Квартал	1	2	3	4	Всего
3	Сезонность	0.9	1.1	0.8	1.2
4
5	Объём продаж
6	Выручка от продажи
7	Затраты на сбыт
8	Валовая прибыль
9
10	Расходы на персонал	8000	8000	9000	9000
11	Расходы на рекламу	10000	10000	10000	10000
12	Накладные расходы
13	Суммарные расходы
14
15	Прибыль
16	Норма прибыли
17
18	Цена изделия	40
19	Затраты на сбыт	25

Рис. 2.4.1 Исходные данные для лабораторной работы № 4

1.2 Выполните ввод формул в столбце В (табл.2.4.1).

Таблица 2.4.1

Расчётные формулы для лабораторной работы № 4

Ячейка	Формула	Описание
1	2	3
B5	=35*B3* (B11+3000) ^0.5	Ожидаемое количество продаж, ед.
B6	=B5*$B$18	Выручка от реализации, руб.
B7	=B5*$B$19	Затраты на сбыт, руб.
B8	=B6-B7	Валовая прибыль, руб.
B12	=0.15*B6	Накладные расходы (15 % выручки), руб.
B13	=СУММ (B10: B12)	Суммарные расходы (персонал, реклама, накладные), руб.
B15	=B8-B13	Производственная прибыль, руб.
B16	=B15/B6	Норма прибыли - отношение прибыли к выручке

1.3 Введите аналогичные формулы в столбцах C, D, E.

1.4 В столбце F введите формулы для суммирования содержимого столбцов B, C, D, E.

2. Решение оптимизационных задач

2.1 Решение задачи однопараметрической безусловной оптимизации.

Цель оптимизации - максимизировать прибыль в первом квартале за счёт изменения затрат на рекламу в этом квартале. Последовательность операций в MS Excel для решения задачи следующая:

- вызовите диалоговое окно СервисПоиск Решения;

- в открывшемся окне задайте целевую ячейку B15 и изменяемую ячейку В11;

- запустите процесс поиска. Результатом решения должна стать квартальная прибыль, равная 17093 руб., соответствующая затратам на рекламу в первом квартале 15093 руб;

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

2.2 Решение задачи многопараметрической безусловной оптимизации.

Цель оптимизации: распределить расходы на рекламу поквартально таким образом, чтобы годовая прибыль от продажи продукции была максимальна. При этом затраты на рекламу ничем не ограничены. Особенности решения данной задачи по сравнению с задачей п.2.1 следующие. В диалоговом окне Поиск решения целевой должна быть установлена ячейка F15, а диапазон изменяемых ячеек B11: E11 (выделяется мышью).

Результатом решения должна стать годовая прибыль, равная 79706 руб., соответствующая годовым затратам на рекламу 89706 руб.

2.3 Решение задачи многопараметрической оптимизации с учётом ограничения - равенства.

Цель оптимизации: распределить фиксированный годовой бюджет расходов на рекламу поквартально таким образом, чтобы годовая прибыль была максимальна. Годовой бюджет расходов на рекламу требуется израсходовать без остатка. Особенности решения данной задачи по сравнению с задачей п.2.2 следующие. В диалоговом окне Поиск решения выбирается кнопка Добавить (ограничение). В открывшемся диалоговом окне в левом поле устанавливается ссылка на ячейку F11, в среднем - отношение =, а в правом - ограничение расходов на рекламу 40000.

Результатом решения должна стать годовая прибыль, равная 71447 руб., соответствующая годовым затратам на рекламу 40000 руб.

2.4 Решение задачи многопараметрической оптимизации с ограничениями-неравенствами.

Основное отличие цели оптимизации от задачи п.2.3 - средства, заложенные на рекламу, не требуется израсходовать в полном объёме. При этом в ограничении задачи устанавливается знак <= вместо =. Бюджет расходов установите равным 50000.

Результатом решения должна стать прибыль, равная 74817 руб., соответствующая затратам годовым затратам на рекламу 50000 руб.

2.5 Самостоятельно решите рассмотренную задачу с двумя ограничениями. Первое устанавливает минимальные расходы на рекламу 50000, второе - максимальные затраты на рекламу 100000.

Результаты выполнения работы представьте преподавателю. Прокомментируйте получившийся результат оптимизации расходов.

2.5 Лабораторная работа № 5. Решение транспортной задачи

Цель работы: изучение технологии решения транспортных задач линейного программирования с использованием инструмента поиска оптимальных решений табличного процессора MS Excel.

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

Исходные данные

1. Число поставщиков N и общее количество потребителей M. Для определённости принимается N = 5 и M = 3, что отражается в размерности последующих таблиц

2. Стоимости доставки единицы товара от каждого поставщика до каждого потребителя сведены в матрицу стоимостей перевозок С с элементами , где i = 1. M - номер потребителя; j = 1. N - номер поставщика.

Таблица 2.5.1

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

Стоимость перевозки единицы товара от поставщика к потребителю	Поставщики
	1	2	3	4	5
Потребители	А
	Б
	В

3. Каждый поставщик характеризуется наличием ресурса, где i = 1. N - номер поставщика.

4. Каждый потребитель характеризуется величиной потребности в ресурсе , где j = 1. M - номер потребителя

Описание решения

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

. (2.5.1)

Требуется определить такие элементы матрицы перевозок (i = 1. M - номер потребителя; j = 1. N - номер поставщика), чтобы все потребности потребителей были обеспечены, все ресурсы поставщиков были потрачены, а общая стоимость затрат на перевозки W (целевая функция) была минимальной.

Таблица 2.5.2

Таблица объёмов перевозок

Объем перевозки товара от поставщика к потребителю	Поставщики
	1	2	3	4	5	Потребность
Потребители	А
	Б
	В
	Объём располагаемого ресурса

Целевая функция имеет вид

. (2.5.2)

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

. (2.5.3)

Для сбалансированной задачи ограничениями при поиске целевой функции будут являться использование всех имеющихся ресурсов каждого поставщика (2.5.4) и обеспечение потребностей каждого покупателя (2.5.5):

для j = 1. M; (2.5.4)

для i = 1. N. (2.5.5)

Возможны два варианта несбалансированной задачи.

1. Суммарные потребности поставщиков превосходят общие потребности потребителей, т.е.

. (2.5.6)

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

2. Потребности потребителей превосходят имеющиеся ресурсы поставщиков, т.е.

. (2.5.7)

Тогда запросы потребителей будут обеспечены не полностью, что приведёт к изменению знака в условии (2.5.5) с равенства на неравенство.

В настоящей лабораторной работе транспортная задача будет решаться с помощью надстройки Поиск решения табличного процессора MS Excel.

Порядок решения задачи

1. Подготовка шаблона для решения задачи

По образцу фрагмента рабочего листа MS Excel, изображённого на рис.2.5.1, введите на новом листе матрицы объёмов и стоимостей перевозок. Транспортная задача пока не решена, поэтому матрица объёмов перевозок пока пуста. В рассчитываемые ячейки введите формулы, приведённые в табл.2.5.3

	A	B	C	D	E	F	G	H	I
1	Таблица стоимостей перевозок
2			Оптовые базы
3			1	2	3	4	5
4	Магазины	А	10	8	6	5	4
5		Б	6	5	4	3	6
6		В	3	4	5	5	9
7
8	Таблица объёмов перевозок
9			Оптовые базы
10			1	2	3	4	5	Всего	Потребность
11	Магазины	А							250
12		Б							350
13		В							200
14	Всего
15	Мощность	120	200	190	210	80
16
17	Целевая функция

Рис. 2.5.1 Шаблон MS Excel для решения транспортной задачи

Таблица 2.5.3

Формулы Excel для решения транспортной задачи

Ячейка	Формула	Описание
1	2	3
H11	=CУММ (С11: G11)	Расчёт объёмов перевозок ресурса от всех оптовых баз (поставщиков) в каждый магазин (потребитель)
H12	=CУММ (С12: G12)
H13	=CУММ (С13: G13)
С14	=СУММ (C11: C13)	Расчёт объёмов перевозок ресурса от каждой оптовой базы (поставщика) всем магазинам (потребителям)
D14	=СУММ (D11: D13)
E14	=СУММ (E11: E13)
F14	=СУММ (F11: F13)
G14	=СУММ (G11: G13)
C17	=СУММПРОИЗВ (C4: G6; C11: G13)	Расчёт значения целевой функции - суммарной стоимости перевозок

2. Настройка сервиса Поиск решения

Откройте окно Поиск решения, выполнив команду Сервис Поиск решения. Укажите в качестве целевой - ячейку С17, установите переключатель целевой ячейки в положение "минимальное значение". Введите в качестве изменяемых ячеек диапазон С11: G13.

Нажатием кнопки Добавить (условие) в окне Поиск решения последовательно введите ограничения поиска решения (2.5.3) - (2.5.5):

Таблица 2.5.4

Ограничения решения задачи для окна Поиск решения

Ссылка на ячейку	Знак	Ограничение
$C$11: $G$13	>=	0
$C$14: $G$14	=	$C$15: $G$15
$H$11: $H$13	=	$I$11: $I$13

Получившееся окно Поиска решения изображено на рис. 2.5.2.

Рис. 2.5.2 Настройки окна Поиск решения для сбалансированной транспортной задачи

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

3. Решение несбалансированной транспортной задачи: запросы потребителей превосходят возможности отправителей

Решаемая в п.1 - 2 задача является сбалансированной, т.е. потребности получателей равны возможностям отправителей. В этом можно убедиться, введя функции =СУММ (I11: I13) и =СУММ (С15: G15) в ячейки I14 и С16 соответственно. Увеличьте потребность магазина Б до 550. При этом суммарная потребность всех магазинов будет равна 1000, а пропускная способность всех оптовых баз - 800. Естественно, что суммарный объём перевозок в этом случае не превысит 800, так как пропускная способность и потребителей, и поставщиков не может быть превышена исходя из требований здравого смысла. Для решения задачи в третьем ограничении (см. рис.2.5.2 или последнюю строку табл.2.5.4) знак равенства замените на знак <=. После окончания процесса решения задачи суммарная стоимость перевозок (целевая функция) принимает значение 2990, т.е. уменьшится по сравнению со сбалансированной задачей. Однако магазины А и Б не обеспечивают свои потребности, так как возможности оптовых баз ограничены. Снижение общей стоимости перевозок при неизменном их объёме (800) стало возможным за счёт преобладания самых дешёвых маршрутов доставки: так, поставки в магазин Б увеличены с 350 до 520 единиц, а в магазин А - снижены с 250 до 80 единиц, так как в магазин Б доставлять товар в целом дешевле, чем в магазин А.

4. Решение несбалансированной транспортной задачи: предложение поставщиков превосходит потребности потребителей

Подобное соотношение на практике встречается чаще, чем в случае, рассмотренном в п.3. Восстановите потребность магазина Б до исходного значения 350, а пропускную способность (мощность) оптовой базы 5 увеличьте с 80 до 180 единиц. При этом суммарная потребность всех магазинов будет равна 800, а пропускная способность всех оптовых баз - 900. Суммарный объём перевозок в этом случае также не превысит 800 исходя из требований здравого смысла. Для решения задачи в третьем ограничении (см. рис.2.5.2 или последнюю строку табл.2.5.4) восстановите знак равенства, а во втором ограничении (см. рис.2.5.2 или последнюю строку табл.2.5.4) замените равенство на знак <=. После окончания процесса решения задачи суммарная стоимость перевозок (целевая функция) примет значение 3030, т.е. также уменьшится по сравнению со сбалансированной задачей. Однако на оптовой базе 2 образуется резерв пропускной способности 100 единиц, так как все потребности потребителей (магазинов) уже исполнены. Снижение общей стоимости перевозок при неизменном их объёме (800) также стало возможным за счёт выбора самых дешёвых маршрутов доставки.

5. Задача для самостоятельного решения

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

Таблица 2.5.5

Исходные данные для самостоятельного решения транспортной задачи (вариант 1)

Исходные данные	Порт-отправитель	Пропускная способность получателя
	1	2	3	4
Порт - получатель	А	20	12	30	23	140
	В	47	14	38	18	160
	С	18	49	15	28	240
	D	23	34	22	30	180
	E	34	45	28	22	160
Пропускная способность отправителя	200	350	180	150

Таблица 2.5.6

Исходные данные для самостоятельного решения транспортной задачи (вариант 2)

Исходные данные	Порт-отправитель	Пропускная способность получателя
	1	2	3	4
Порт - получатель	А	40	24	60	46	70
	В	94	28	76	36	80
	С	38	98	29	56	120
	D	46	68	90	60	90
	E	68	90	60	44	80
Пропускная способность отправителя	100	175	90	75

5.1 Проверьте задачу на сбалансированность и решите её. Результаты решения сохраните на отдельном листе.

5.2 Решите несбалансированную задачу, аналогичную п.3. Для этого измените пропускную способность двух любых портов отправления на 20 единиц. Результаты решения сохраните на отдельном листе.

5.3 Решите несбалансированную задачу, аналогичную п.4. Для этого измените пропускную способность трёх любых портов назначения на 30 единиц. Результаты решения сохраните на отдельном листе.

Покажите полученную рабочую книгу MS Excel преподавателю.

Приложения

Приложение А

Таблица А.1

Соответствие команд MS Excel 2003 (и более ранних версий) и MS Excel 2007 (и более поздних версий)

Команда MS Excel 2003-	Команда MS Excel 2007+
Сервис Поиск решения	Данные Поиск решения
Сервис Таблица подстановки	Данные Анализ "что-если" Таблица Данных
Сервис Сценарии	Данные Анализ "что-если" Диспетчер сценариев
Сервис Подбор параметра	Данные Анализ "что-если" Подбор параметра
Вставка Имя Присвоить	Формулы Присвоить имя
Вставка Диаграмма	Вставка Гистограмма
Данные Сводная таблица	Вставка Сводная таблица

Размещено на Allbest.ru