ЗАПАСЫ ТОВАРОВ НА СКЛАДАХ, ед.

Наименование товара	Ростов	Москва	Ставрополь	Краснодар
Товар1
Товар2		2 500	2 000	2 300
Товар3	1 000	6 000		2 000
Товар4	2 000	2 500		2 356
Товар5	3 600	25 000	2 355

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

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

Наименование товара	Ростов	Москва	Ставрополь	Краснодар
Товар1
Товар2
Товар3
Товар4
Товар5

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

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

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

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

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

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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Следовательно, второе ограничение будет выглядеть следующим образом:

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

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

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

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

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

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

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

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

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

После этого необходимо нажать OK для возвращения в основной диалог «Поиск решения».

- запускается процедура выполнения расчета: нажмите кнопку
«Выполнить».

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

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

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

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

è	Порядок выполнения работы

1. Изучение теоретического материала.

2. Выполнение вариантов заданий с помощью рассмотренных инструментов, средств, приемов и технологий

3. Составление отчета о проделанной работе. Отчет должен содержать следующие разделы:

- наименование работы;

- цель работы;

- пошаговое последовательное описание процесса выполнения варианта задания по видам выполняемых действий.

4. Результат выполнения варианта задания должен быть сохранен под именем ФИО_Работа№_Вариант№ (например, «ИвановНН_Работа1 _Вариант1.xls») на жесткий диск в папку «Мои документы\ИТ в экономике» и на дискету – в двух копиях (две копии одной и той же информации в разных папках на дискете).

5. Представление результатов выполнения работы (отчета и файлов на дискете) для проверки преподавателю.

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

7. Оценка преподавателем выполненной работы.

s	Контрольные вопросы

1. Опишите сущность транспортной задачи и ее математическую формулировку и как связываются математическое представление задачи с представлением исходных данных и результатов на листе Excel.

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

3. Опишите порядок выполнения работы, отраженный в примере решения задачи.

4. В чем состоит назначение механизма "Поиск решения"?

5. Как производится ввод граничных условий? Для чего необходимо вводить граничные условия?

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

Ä	Варианты заданий

Вариант 1

20 - 30 мин.

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

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

№ п.п.	Наименование завода	Объем производства, тыс. ед.
	Завод1
	Завод2
	Завод3
	Завод4
	Завод5

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

ТРАНСПОРТНЫЕ РАСХОДЫ

	Оптовая база1	Оптовая база2	Оптовая база3	Оптовая база4
Завод1		2,5
Завод2		2,5
Завод3	2,6
Завод4
Завод5	2,5			2,6

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

Вариант 2

20 - 30 мин.

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

СПРОС НА ТОВАРЫ В ПУНКТЕ N

№ п.п.	Наименование товара	Спрос, ед.
	Товар1	8 000
	Товар2	9 000
	Товар3	15 000

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

ЗАПАСЫ ТОВАРОВ НА СКЛАДАХ

№ п.п.	Наименование товара	Склад1	Склад2	Склад3	Склад4	Склад5
	Товар1	6 000				6 000
	Товар2	6 000	2 500	2 000	2 300	2 000
	Товар3	2 300	8 000		2 000	6 000

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

№ п.п.	Наименование товара	База1	База2	База3	База4	База5
	Товар1
	Товар2
	Товар3

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

Занятие №13

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

Цель работы

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

&	Теоретический материал