Лабораторная работа №5. Подбор параметра

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

Задание 1. Предполагается, что доходы по проекту в течение 5 лет составят: 120 000 000 руб., 200 000 000 руб., 300 000 000 руб., 250 000 000 руб. 320 000 000 руб. Определить первоначальные затраты на проект, чтобы обеспечить скорость оборота 12%.

Решение

Расчет внутренней скорости оборота инвестиций производится с помощью функции ВСД (в ранних версиях — ВНДОХ): ВСД (Значения; Предположения)

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

 

Ввод исходных данных производится в соответствии с рис. 16. Первоначально для расчета величина затрат на проект выбирается произвольно (ячейку для этой суммы можно оставить даже пустой) и производятся вычисления.

В ячейку В12 вводится формула =ВСД(В4:В9).

Далее, используя команду Сервис | Подбор параметра (рис. 17), находим величину первоначальных затрат на проект, обеспечивающих скорость оборота инвестиций в 12%. Результат для этого

примера представлен на рис. 18.

 

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

 

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

 

Пример расчета эффективности неравномерных капиталовложений

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

Решение

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

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

Ввод исходных данных производится в соответствии с рис. 19. Первоначально для расчета выбирается произвольный процент годовой учетной ставки (ячейку с этой величиной можно оставить даже пустой) и производятся вычисления. В ячейку В9 вводится формула: =ЧПС(В6;В2:В4)

 

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

 

В ячейку С7 можно ввести следующую формулу:

=ЕСЛИ(B7=1;"год";ЕСЛИ(И(B7>=2;B7<=4);"года";"лет"))

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

• в поле Установить в ячейке введите В9, т. е. адрес ячейки, в которой необходимо получить искомое значение суммы сделки (15000);

• в поле Значение введите 15000, т. е. само искомое значение суммы сделки;

•в поле Изменяя значение ячейки введите адрес ячейки — В8, в которой с помощью средства Подбор параметра будет получена необходимая процентная ставка для рассматриваемой задачи (в случае, если такая существует).

Окончательное решение задачи приведено на рис. 20.

 

Рис. 19. Окно Подбор параметра для задачи о неравномерных капиталовложениях

 

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

 

Лабораторная работа №6. Таблица подстановки

Таблица подстановки позволяет проводить анализ изменения результата при произвольном диапазоне исходных данных. На одном рабочем листе можно расположить несколько таблиц подстановок. Это дает возможность одновременно анализировать различные формулы и статистические данные.

Таблицу подстановки можно использовать для:

- изменения одного исходного значения, просматривая при этом результаты одной или нескольких формул;

- изменения двух исходных значений, просматривая результаты только одной формулы.

Использование таблицы подстановки с одной изменяющейся переменной и несколькими формулами

Рассмотрим эту методику на примере расчета ежемесячных выплат по займу (расчет происходит с помощью функции ПЛТ) и платежей по процентам (функция ПРОЦПЛАТ):

Решение такой задачи предполагает следующие шаги:

1. Создать или перейти на рабочий лист, где будет решаться анализируемая задача.

2. Организовать интерфейс таким образом, чтобы все вводимые данные были понятны пользователю:

• в соответствующие ячейки рабочего листа вводятся необходимые подписи и данные (рис. 21).

• в ячейку В5 — формула: =ПЛТ($В$4/12;$В$3*12;$В$2)

• в ячейку D6 — формула: =ПРОЦПЛАТ($D$4;$D$5;$D$3;$D$2)

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

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

3. После подготовки исходных данных перейти к тому месту рабочего листа, где будут располагаться рассчитываемые значения в зависимости от изменения одной переменной и от различных рассчитываемых формул (рис. 22).

В верхней строке будущей таблицы над местом расположения результатов указать адрес каждой формулы, для которой нужно получить список результатов. Вместо адреса можно поместить в ячейку саму формулу (ячейки B10 и C10 содержат формулы).

Слева расположить различные значения исходных данных, которые необходимо протестировать.

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

 

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

5. Воспользоваться командой Данные | Таблица подстановки и в диалоговом окне Таблица подстановки (рис. 23) указать, куда и какие значения необходимо подставлять.

В нашем примере — подстановка значений процентной ставки (столбец исходных значений А10:А19) происходит в ячейку В4, т. к. в этой ячейке изначально указывается величина процентной ставки, входящей в рассчитываемые формулы. Результаты вычислений будут помещены в незаполненные ячейки выделенного диапазона (рис. 24).

Рис. 23 Использование таблицы подстановки

 

Рис. 24 Рассчитанные значения для одномерной таблицы подстановки

Использование таблицы подстановки с двумя изменяющимися переменными и одной формулой

Рассмотрим эту методику на примере расчета ежемесячных выплат по займу в зависимости от различных сроков погашения и различных процентных ставок. Решение задачи предполагает

следующие шаги:

1. Организовать на рабочем листе соответствующий интерфейс пользователя для некоторого набора входных данных (рис. 25):

• конкретная процентная ставка — 3% (ячейка В4);

• конкретный срок погашения — 3 года (ячейка ВЗ);

• формула для ячейки В5:

=ПЛТ($В$4/12;$В$3*12;$В$2)

 

Рис. 25. Подготовка данных задачи

 

2. Подготовить следующую таблицу (рис. 26):

• изменяемые данные поместить в левый столбец и верхнюю строку— в нашем случае значения процентной ставки (ячейка В4) располагаются в диапазоне В10:В14, а значения срока погашения (ячейка ВЗ) — в диапазоне C9:F9;

• на пересечении строки и столбца в верхнем левом углу расположить необходимую формулу или ссылку на нее (ячейка В9 содержит формулу ячейки В5, в которой обязательна абсолютная адресация ячеек).

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

 

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

4. Выполнить команду Данные | Таблица подстановки и в появившемся окне (рис. 27) указать, куда и какие значения необходимо подставлять.

Рис. 27 Использование таблицы подстановки при расчетах по двум параметрам

 

В рассматриваемом примере подстановка значений процентной ставки (столбец исходных значений В10:В14) происходит в ячейку В4, т. к. в этой ячейке изначально указывается величина процентной ставки, входящей в рассчитываемую формулу, а подстановка значений сроков погашения (строка значений C9:F9) — в ячейку ВЗ. Результаты вычислений будут помещены в незаполненные ячейки выделенного диапазона (28).

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

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