КАТЕГОРИИ: Архитектура-(3434)Астрономия-(809)Биология-(7483)Биотехнологии-(1457)Военное дело-(14632)Высокие технологии-(1363)География-(913)Геология-(1438)Государство-(451)Демография-(1065)Дом-(47672)Журналистика и СМИ-(912)Изобретательство-(14524)Иностранные языки-(4268)Информатика-(17799)Искусство-(1338)История-(13644)Компьютеры-(11121)Косметика-(55)Кулинария-(373)Культура-(8427)Лингвистика-(374)Литература-(1642)Маркетинг-(23702)Математика-(16968)Машиностроение-(1700)Медицина-(12668)Менеджмент-(24684)Механика-(15423)Науковедение-(506)Образование-(11852)Охрана труда-(3308)Педагогика-(5571)Полиграфия-(1312)Политика-(7869)Право-(5454)Приборостроение-(1369)Программирование-(2801)Производство-(97182)Промышленность-(8706)Психология-(18388)Религия-(3217)Связь-(10668)Сельское хозяйство-(299)Социология-(6455)Спорт-(42831)Строительство-(4793)Торговля-(5050)Транспорт-(2929)Туризм-(1568)Физика-(3942)Философия-(17015)Финансы-(26596)Химия-(22929)Экология-(12095)Экономика-(9961)Электроника-(8441)Электротехника-(4623)Энергетика-(12629)Юриспруденция-(1492)Ядерная техника-(1748) |
Работа с финансовыми функциями
Анализ «Что-если»
Цель работы: научиться работать с финансовыми функциями Excel и выполнять анализ "Что-если" Содержание работы: 1 Финансовые функции при экономических расчётах 2 Прогнозирование с помощью анализа "Что-если"
1 Финансовые функции при экономических расчётах 1 Функция ПЛТ. Расчёт величины ежемесячной выплаты кредита Функция ПЛТ определяет сумму периодического платежа для аннуитета[1] на основе постоянства сумм платежей и постоянства процентной ставки. Пример 1 Определить ежемесячный платёж, если банк предоставляет кредит в 140000р. с рассрочкой в 5 лет под 8,5% годовых с ежемесячной выплатой. Последний платёж должен составить 10000р. Введём данные в таблицу Excel согласно рис. 1) 1 Выделить ячейку В6 и щелкнуть по кнопке Вставка функции (знак fx слева от строки формул). Появится окно Мастера функций, выбрать категорию Финансовые. 2 Щелкнуть мышью по функции ПЛТ, перетащить окно ПЛТ на свободное место экрана, чтобы освободить таблицу и Рисунок 1 Расчёт аннуитета заполнить его поля: ▪ Поле Ставка – это процент в месяц, вводим 0,085, ▪ Кпер – количество периодов выплат, т.е. 5лет*12мес, вводим 5*12 ▪ Нз – общая сумма всех платежей с текущего момента, вводим 140000, ▪ Бс – будущая стоимость, вводится 130000 со знаком "-", т.к. платим мы, а не банк, § Тип – выплата в конце месяца, поэтому вводим 0 или ничего. 3 Нажать ОК.
Результат: около 2738 р. ежемесячно нужно выплачивать, чтобы погасить 130000 р. за 5 лет (в конце срока последним платежом ещё 10000р.) 2 Прогнозирование с помощью анализа "Что-если" Анализ «Что-если» позволяет прогнозировать значение какой-либо функции (математической, финансовой, статистической и др.) при изменении её аргументов. Существует три способа прогнозирования значений: с помощью таблиц подстановки данных, с помощью сценариев и с помощью подбора параметров и поиска решения.
1 способ. Таблица подстановки данных – это диапазон ячеек, показывающий, как изменение значений подстановки влияет на возвращаемый формулой результат. Если в какой-либо ячейке записана формула, содержащая элементы из других ячеек, то при изменении значения в какой-нибудь или нескольких ячейках изменится результат в ячейке, содержащей формулу. Пример 2 Компания сделала заём на 80 000 руб. сроком на 3 года. Определить: - ежемесячные выплаты при процентных ставках 7%, 8% и 9% годовых, - ежемесячные выплаты при процентной ставке 5%, сроке заема 5 лет и сумме заема 100 000р.
1 Введем таблицу подстановок в виде (рис. 2):
Рисунок 2 Таблица подстановок
2 Введём в ячейку D2 формулу платежа ПЛТ (В3/12;В4*12;В5) вручную или через окно ПЛТ из Мастера функций (см. пример 1), в D2 появится рассчитанное значение функции -2470,17р. 3 Изменим значение ячейки В3 на 8%, получим в D2 cумму платежа –2506,91р. 4 Изменим значение ячейки В3 на 9%, получим в D2 cумму платежа –2543,98р. 5 Изменим одновременно значения ячеек: В3на 5%, В4на 5 и В5 на 100000, получим в D2 cумму платежа –1887,12р.
Таблица подстановок должна обязательно в одной из ячеек содержать формулу.
2 способ. Сценарий – это набор значений подстановки, используемый для прогнозирования поведения модели. На одном листе Excel можно создать и сохранить несколько различных сценариев и переключаться на любой из них для просмотра результатов и выбора наилучшего. Пример 3 Оформим в виде сценариев варианты подстановки данных из пунктов 2 и 3 примера 2. Для создания сценария необходимо выполнить следующие действия: 1 Из меню Сервис выберете команду Сценарии. 2 В открывшемся окне Диспетчер сценариев нажмите кнопку Добавить.
3 Введите имя сценария., например "Ставка 7%"". 4 В поле Изменяемые ячейки задайте те ячейки (через двоеточие), которые Вы собираетесь изменить, в данном случае – ячейку В3. 5 Нажмите кнопку ОК. 6 В открывшемся диалоговом окне Значения сценария для каждой изменяемой ячейки введите новое значение или формулу, в данном случае вводим в В3число 0,07. Нажмите кнопку ОК. Исходную модель " что-если " желательно сохранить в виде сценария, присвоив ему, например, имя «Стартовые значения». В противном случае при задании новых изменяемых ячеек исходные данные будут потеряны. Для просмотра сценария необходимо воспользоваться кнопкой Вывести в окне Диспетчер сценариев. Щелкнув кнопку Итоги в диалоговом окне Диспетчер сценариев, можно получить итоговый отчет на отдельном рабочем листе с названием "Структура сценариев", показывающий влияние разных сценариев на одну или несколько результирующих ячеек. Знаки "+"("-") слева и сверху позволяют разворачивать (сворачивать) отдельные разделы отчёта. Серым выделены изменяемые поля. 3 способ. Подбор параметра. При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки не возвратит заданное значение. Пример 4 Условие примера 1. Компания может ежемесячно выплачивать не более 2500р. Определить, каким должен для этого быть последний платёж. 1.Выделим ячейку.В6: 2.В меню Сервис выбрать команду Подбор параметра. В окне Подбор параметра: -в поле Установить в ячейке – введено В6, -в поле Значение - ввести -2500 -в поле Изменяя значение ячейки – ввести В3 (ячейка последнего платежа), -нажать ОК. Результат: последний платёж = -27716 р. При подборе параметра одна из ячеек также обязательно должна содержать формулу, поскольку таблица является таблицей подстановок. Команда Поиск решения из меню Сервис используется для подбора одновременно нескольких параметров с целью максимизации или минимизации содержимого целевой ячейки и подробно рассматривается в лабораторной работе №7 (excel-7).
Контрольные вопросы 1 Как вывести на экран приложение Мастер функций? 2 Какую операцию выполняет функция ПЛТ, что вводится в её поля Норма, Кпер, Нз, Бс, Тип?
3 Назначение и способы анализа «Что если»? 4 Что такое «Таблица подстановок», каков состав её ячеек? 5 Что такое сценарий, как его создать, просмотреть, получить итоговый отчет на отдельном листе? 6 Сущность операции Подбор параметра, как она выполняется?
Задания 1 Выполнить задание примера 1, изменив сумму кредита на 140000· n, где n - номер студента в журнале преподавателя. Выполнить то же для новой суммы кредита, изменив годовой процент с 8,5% на 5%, а срок кредита с 5 на 10 лет. 2 Выполнить анализ "Что-если" по заданию таблицы подстановки примера 2, изменив сумму заёма на 80000·n, где n- номер студента в журнале преподавателя. 3 Оформить в виде сценариев все операции из п.1 (два сценария) и п.2 (четыре сценария) данного задания к лабораторной работе. 4 Выполнить задание примера 4, изменив сумму ежемесячной выплаты на n·100.
Содержание отчёта 1Название, цель, содержание работы 2 Письменные ответы на контрольные вопросы 3 Выводы по работе
На дискете должны быть сохранены результаты работы
Лабораторная работа №6 Создание макросов в Excel Цель работы: научиться использовать макросы для автоматизации выполняемых задач Содержание работы: 1 Запуск макроса с клавиатуры сочетанием клавиш. 2 Запуск макроса с помощью кнопки на панели инструментов. Создание новой панели инструментов. 3 Запуск макроса с помощью кнопки на рабочем листе. 4 Копирование макроса в другую книгу.
Если какое-то действие часто повторяется, его выполнение можно автоматизировать с помощью макроса. Макрос — это подпрограмма, написанная на языке Visual Basic, которая содержит серию команд и функций, хранящихся в модуле Visual Basic. Их можно выполнять всякий раз, когда необходимо выполнить данную задачу. Перед тем как записать или написать макрос, необходимо спланировать шаги и команды, которые он будет выполнять. Если при записи макроса была допущена ошибка, ее исправление будет также записано. Каждый раз при записи макроса, он сохраняется в новом модуле, присоединенном к книге.
Редактор Visual Basic позволяет изменять макросы, а также копировать их либо из одного модуля в другой, либо между различными книгами. Кроме того, можно переименовывать модули, в которых хранятся макросы, или переименовывать сами макросы.
Дата добавления: 2014-10-15; Просмотров: 499; Нарушение авторских прав?; Мы поможем в написании вашей работы! Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет |