Раздел 3. Решение оптимизационных задач
Подбор параметра является частью блока задач, который иногда называют инструментами анализа "что-если" (Анализ «что-если». Процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул на листе, например изменение процентной ставки, используемой в таблице амортизации для определения сумм платежей.). При подборе параметра (Подбор параметра. Способ поиска определенного значения ячейки путем изменения значения в другой ячейке. При подборе параметра значение в ячейке изменяется до тех пор, пока формула, зависящая от этой ячейки, не вернет требуемый результат.) Microsoft Excel изменяет значение в одной конкретной ячейке до тех пор, пока формула, зависимая от этой ячейки, не возвращает нужный результат.
Воспользовавшись таблицами подстановок, можно легко проследить, как повлияет изменение данных на конечный результат, как будет развиваться система при различных условиях. Таблицей подстановки данных называется диапазон ячеек,, показывающий, как изменение ячеек подстановки влияет на возвращаемый формулой результат.
Поиск решения. С ее помощью можно определить, при каких значениях указанных влияющих ячеек формула в целевой ячейке принимает нужное значение (минимальное, максимальное или равное какой-либо величине). Для процедуры поиска решения можно задать ограничения, причем не обязательно, чтобы при этом использовались те же влияющие ячейки. Для расчета заданного значения применяются различные математические методы поиска.
Задание 3
Часть 1
Решить задачи на использование инструмента «Таблица подстановки» (ТП) и «Подбор параметра» (ПП) согласно примеру, описанному в файле ТаблПодстан-ПодборПарам.doc.
Вариант 5. Для ТП – Определите, какую сумму можно накопить, если вклад размером 40 тыс. руб. положен под 10,5% годовых на 6 лет, а проценты начисляются каждый квартал (функция БС). Проанализируйте возможность накопления суммы при условии различных процентных ставок и различного размера первоначального вклада.
Для ПП – Инвестиции размером 530 млн. руб. принесли годовые доходы в размере: 80,98,150, 250 360 млн. руб. (функция ЧПС) Определите процент окупаемости данного проекта.
Ход выполнения задания 3 ч-1.
Чтобы решить задачу с использованием метода Таблицы подстановки (ТП), необходимо ознакомится с условием задачи. Затем построить таблицу, используя эти данные.
.
Сперва нам необходимо найти накопленную сумму в ячейке, где написано БС. Для этого мы используем функцию БС. Делается это так: щелкаем левой кнопкой мыши в ячейку БС, нажимаем на Функции (она находится под сервисом меню), в категории выбираем Финансовые, БС, ок.
В аргументе функций необходимо задать параметры: в Ставке мы делим 10,5% на 4 т.к в одном году 4 квартала (по условии задачи), в Кпер 2 лет умножаем на 4, чтобы узнать сколько будит кварталов за 2 года, в Плт выбираем число -40000 т.к мы их отдаем. Получаем 1 314 153, 72р.
Теперь мы можем использовать таблицу подстановки: выделяем часть таблицы, где в строке прописаны различные ставки, а в столбцах взносы (эти числа мы придумываем сами), в меню-сервисе нажимае на Данные, анализ что-если, таблица данных. В таблице данных задаем следующие приориты: по столбцам выбираем ячейку из Взносы, по строкам – срок, Ок.
Для ПП – Инвестиции размером 530 млн. руб. принесли годовые доходы в размере: 60, 98, 110, 250 360 млн. руб. (функция ЧПС) Определите процент окупаемости данного проекта.
Ознакомившись с условием задачи, строим таблицу. Как и в первой задаче, мы задаем функцию ЧПС. Далее, появляется окно Аргументы функции. В ставке мы используем ячейку с 3%, в Значении1 используем ячейки с ежегодными доходами (их 4– по условии задачи). Нажимаем Ок.
Нам надо выяснить при какой процентной ставке данный проект окупится.
Здесь мы используем подбор параметров (данные - что-если – подбор параметров). В установить в ячейке мы используем ячейку с формулой ЧПС. В Значении вводим 530000000 (по условии задачи), и на Изменяя значения ячейки используем ячейку, где стоят 3%. Нажимаем Ок.
