Задание 2. Поиск Решения. Уменьшение затрат на перевозку грузов

Задание 1. Подбор параметров

ПОСТАНОВКА ЗАДАЧИ

Определить величину ежемесячной выплаты займа в 100 000 р., если он взят на 36 месяцев при процентной ставке, равной 50%.

РЕШЕНИЕ ЗАДАЧИ МЕТОДОМ ПОДБОРА ПАРАМЕТРОВ

1.Ведите данные как показано на рисунке 1. В ячейку В4 введите значение 0, 5 и установите тип Процент, с помощью кнопки Процентный стиль панели инструментов Форматирование. В ячейку В5 – 36 (например).

Изучите по Справке назначение и формат функции ПЛТ.

Таблица 3 – Подбор

2.В ячейку В6 введите формулу

=ПЛТ(В4/12; В5; -В3)

В ней получите размер ежемесячного платежа при сроке ссуды в 36 месяцев (количество ежемесячных выплат равно 36).

3.С помощью команды Сервис – Подбор параметра осуществите подбор такого количества ежемесячных выплат, при котором каждая выплата составит 4500р.:

Ø выделите ячейку В6;

Ø Сервис – Подбор параметра – появится диалоговое окно;

Ø в поле Установить в ячейке введите $B$6

Ø в поле Значение введите 4500;

Ø в поле Изменяя ячейку введите $B$5.

Нажав OK, получите в ячейке В5 значение.

Задание 2. Поиск Решения. Уменьшение затрат на перевозку грузов

ПОСТАНОВКА ЗАДАЧИ

Допустим, что ваша фирма занимается переработкой мяса на нескольких заводах, расположенных в разных районах Москвы. Мясо поставляется объединениями фермеров со складов, расположенных в нескольких городах Московской области. Стоимость мяса одинаковая, однако перевозка со склада на завод зависит от расстояния и отлича­ется для каждого склада и завода. Потребность заводов в мясе различ­на, и запасы на каждом складе ограничены. Требуется определить: с какого склада, на какой завод поставлять, сколько мяса для миними­зации общих затрат на перевозку.

ПОРЯДОК ВЫПОЛНЕНИЯ

Создайте на листе Транспортные расходы таблицу. С этой целью:

Ø В ячейку A1 введите текст «Оптимизация транспортных потоков».

Ø В ячейку B1 введите текст «Потребители - >».

Ø В ячейку C2: F2 введите название мясо перерабатывающих заводов.

Ø В ячейку A3 введите текст «Поставщики»; в ячейку A4: A8 названия складов.

Ø Установите курсор в ячейку B4 и нажмите кнопку Σ, после чего выделите ячейки с C4 по F4. В строке формул появится формула = СУММ(C4: F4). Нажмите кнопку , расположенную справа в строке формул, и формула будет введена.

Ø Скопируйте содержимое ячейки В4 в ячейки В5: В8.

Ø Выделите ячейки с С4 до F8. Введите цифру 1 и нажмите кнопку . Нажмите комбинацию клавиш Ctrl+D (автозаполнение столбцов в выделенной области), а затем нажмите Ctrl+R (автозаполнение строк в выделенной области). Все выделенные ячейки будут заполнены единицами. Установите формат ячеек выделенной области числовой.

Ø В ячейку А9 введите текст «Факт - >».

Ø В ячейку C9 введите формулу =СУММ(C4: C8). Скопируйте формулу в ячейки D9: F9.

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

Введите требуемые объемы поставок и цены поставок. С этой целью:

Ø Введите в ячейку А10 текст «Запросы - >». В десятой строке вводятся значения потребляемого каждым из заводов мяса в тоннах.

Ø В ячейки этой строки введите соответственно:

Ø Выделите ячейки с A4 по A8. Нажмите клавишу CTRL и, не отпуская ее, подведите курсор мыши к краю выделенного интервала, нажмите левую клавишу мыши и двигайте мышь. Появиться серый прямоугольник размером с выделенную область. Расположите его в ячейки с A11 по A15, затем отпустите клавишу мыши и клавишу CTRL. Названия складов будут скопированы.

Ø В ячейки второго столбца (B11: B15) занесите объемы месячных запасов мяса на различных складах в тоннах соответственно.

Ø В ячейки с C11 по F15 занесите стоимость перевозки тонны мяса с конкретного склада на конкретный завод. Для этого введите в ячейки с C11 по F15 следующие данные:

Ø В ячейку A16 введите текст «Всего». В ячейку C16 введите формулу

=C4*C11+C5*C12+C6*C13+C7*C14+C8*C15.

В ячейке C4 находится количество мяса, перевозимого со склада в Наро-Фоминске на завод в Лужниках, а в ячейке C11 – цена перевозки тонны груза по этому маршруту. Соответственно, первое слагаемое в формуле означает полную стоимость перевозок по данному маршруту. Вся же формула вычисляет полную стоимость перевозок мяса на завод в Лужниках.

Ø Скопируйте формулу из ячейки C16 в ячейки D16: F16.

Ø В ячейку B16 введите формулу = СУММ(C16: F16). В данной ячейке будет вычисляться общая стоимость перевозки мяса.

Ø В ячейку A18 введите текст «Всего на перевозки требуется», а в ячейку С18 – «млн. руб.» Для вычисления суммы в миллионах в ячейку В18 введите формулу = B16/1000000.

Выполните форматирование таблицы в соответствии с рисунком.

Скопируйте лист Транспортные расходы (Правка – переместить/скопировать лист) для возможного восстановления начального вида таблицы. Переименуйте скопированный лист, дав ему название Поиск решения.

Выполнить поиск решения (Сервис – Поиск решения), изменяя ячейки С4: F8 с целью определения минимальных затрат на перевозки при соблюдении следующих условий:

Ø Объем поставок с конкретного склада должен быть меньше или равен запасам на складе ($В$4: $В$8< =$В$11: $В$15).

Ø Объем перевозок не должен быть отрицательным ($С$4: $F$8> =0).

Ø Запросы заводов должны быть выполнены полностью. Перевыполнение поставок допустимо, а недовыполнение – нет ($C$9: $F$9> =$C$10: $F$10).

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