Лабораторная работа №8 решение задач с помощью средства Подбор параметра в ms excel

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

Методические указания

Средство «Подбор параметра» является основным инструментом анализа данных.

Подбор параметра позволяет найти аргумент, при котором получается задаваемое решение. Excel находит ответ с помощью последовательных итераций. Достаточно указать целевую ячейку, ее желаемое значение и изменяемую ячейку-параметр, влияющую на содержимое целевой ячейки. При этом ячейка-параметр должна содержать значение, но не формулу, и влиять на результат, который следует получить. Целевая же ячейка должна содержать формулу, включающую ссылку на ячейку параметр (прямо или опосредованно).

Для вызова Подбора параметра следует выделить ячейку с формулой, которая должна принять заданное значение и выбрать вкладку Данные – категорию Работа с данными – кнопку «Анализ «что если» – команду Подбор параметра (рис. 8.1).

Рис. 8.1. Диалоговое окно Подбор параметра

В диалоговом окне Подбор параметра в поле Значение ввести величину, которую необходимо получить в целевой ячейке, в поле Изменяя значение ячейки ввести ссылку на ячейку – параметр и нажать кнопку ОК. При этом откроется диалоговое окно Результат подбора параметра и начнется итерационный процесс поиска решения, каждый шаг которого дает следующее приближение к искомой величине. После того, как решение найдено нажмите кнопку ОК или Отмена, если ответ не устраивает. При использовании «Подбора параметра» флажок «Задать точность, как на экране» (Файл – Параметры Excel – категория Дополнительно – группа При пересчете этой книги) должен быть снят.

Пример 1. Используя «Подбор параметра», определите при каких значениях аргумента функция , примет значение 0,5.

Решение. Выделим ячейку с формулой, которая должна принять значение 0,5, затем выберем команду Сервис – Подбор параметра.

Для нахождения значения аргумента в диалоговом окне Подбор параметра введем ссылки на соответствующие ячейки (рис. 8.2) и нажмем кнопку ОК.

Рис. 8.2. Пример нахождения аргумента функции

В результате получим, что решение будет найдено (рис. 8.3) и аргумент примет значение (рис. 9.4) равное 1,250103.

Рис. 8.3. Результат подбора параметра

Рис. 8.4. Полученное значение аргумента X

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

Вас просят дать в долг 15000 руб. и обещают вернуть через год 3000 руб., через два – 5000 руб., через три – 9000 руб. При какой процентной ставке эта сделка выгодна?

Решение.

При решении данной задачи следует использовать функцию ЧПС:

ЧПС(ставка; значение 1; значение 2;…)

значение 1, значение 2 … – от 1 до 29 аргументов, представляющих доходы и расходы. Значение 1, значение 2, …должны быть равномерно распределены по времени и осуществляться в конце каждого периода. ЧПС использует порядок аргументов для определения порядка поступлений и платежей.

Первоначально для расчета выбирается произвольный процент годовой ставки дисконтирования (ячейку с величиной процента можно оставить пустой) и производятся вычисления. Ниже приведен рабочий лист MS Excel с исходными данными (рис. 8.5).

Рис. 8.5. Рабочий лист для решения задачи с неравномерными

капиталовложениями

Затем, используя команду Подбор параметра вкладка Данные кнопка «Анализ «что если», находим оптимальную процентную ставку (рис. 8.6):

Рис. 8.6 Окно диалога Подбор параметра

Результат работы надстройки Подбор параметра приведен ниже (рис. 8.7):

Рис. 8.7 Оптимальная процентная ставка