Вопрос 2. Технология использования средств Excel для финансово-экономических расчетов: анализ данных на основе использования Таблицы подстановки.

Электронные таблицы Microsoft Excel содержат более 400 функций, которые содержатся в Мастере функций. Среди них математические, статистические, финансовые, логические и др. Финансовые функции MS Excel позволяют вычислять скорость оборота вложения, амортизацию имущества за заданный период, анализировать инвестиции и решать другие задачи.

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

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

Например, требуется рассчитать с помощью финансовой функции ПЛТ размер ежемесячных фиксированных выплат за полученную ссуду, исходя из размера годовой процентной ставки 7%, срока ссуды 5 лет и размера ссуды 100000 рублей.

Занесем на рабочий лист в ячейки А2:В4 заголовки и данные задачи. В ячейке В6 с помощью Мастера функций вычислим значение ежемесячного платежа за полученную ссуду: ПЛТ(В2/12; В3*12; В4), которое будет равно -1980,12 руб.

С целью исследования динамики зависимости размеров платежа от процентной ставки обратимся к таблице подстановки с одним входом. Для этого занесем на рабочий лист в ячейки А7:А13 значения процентной ставки от 4% до 10% и выделим диапазон будущей таблицы подстановки А6:В13. Далее в меню выбираем Данные - Таблица подстановки. В диалоговом окне Таблица подстановки в строке «Подставлять значения по строкам в» установить адрес ячейки В2, содержащей значение процентной ставки.

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

Дополним полученную таблицу, введя в ячейку С6 формулу расчета общего объема выплат: =В6*В3*12. Полученное значение – 118807,19 руб.

Модифицируем таблицу подстановки, для чего выделим диапазон А6:С13 и выполним команды: Данные - Таблица подстановки - Подставлять значения по строкам в: В2 - ОК. Таблица подстановки заполнится значениями, показанными ниже.

Рассмотрим работу с таблицей подстановки с двумя входами с учетом тех же входных данных. Скопируем в те же ячейки на Лист2 диапазон ячеек А2:В4 и А7:А13. Расчетную функцию ПЛТ скопируем в ячейку А6. в интервал В8:G8 введем сроки возврата ссуды от 2 до 7 лет (см. ниже).

Выделим диапазон будущей таблицы подстановки А6:G13 и выполним следующие команды: Данные -Таблица подстановки. В окно Подставлять значения по строкам в: введем адрес ячейки В2; в окно Подставлять значения по столбцам в: введем адрес ячейки В3 - ОК.

Таблица подстановки заполнится новыми значениями в диапазоне В7:G13: