Методика работы с подбором параметра
Порядок выполнения и оформления задания №1
1. По своему шифру выберите вариант и условие задачи (номер варианта определяется по последним цифрам зачетной книжки).
2. На рабочий лист Excel введите условие задачи.
3. Оформите расчетные таблицы.
4. Выполните необходимые расчеты с использованием следующих средств Excel:
· финансовые функции (БС, ПС, ЧПС, ЧИСТНЗ);
· таблицы подстановки (ТП);
· подбор параметра.
5. Результаты оформите в виде отчета. Он должен содержать:
· титульный лист;
· условие задачи;
· фрагмент рабочего листа Excel (таблицу) с условием;
· фрагменты рабочего листа Excel с результатами - в обычном режиме и режиме формул;
· фрагменты настройки диалоговых окон таблиц подстановки и подбора параметра.
6. К отчету приложите дискету с выполненной задачей, сохраненной в виде книги Excel.
Методика работы с таблицами подстановки
Чтобы воспользоваться таблицами подстановки необходимо выполнить следующие действия:
· введите исходные данные на рабочий лист Excel;
· выполните вычисления, введите целевую функцию;
· определите одну или две ячейки подстановки (ввода), содержащие переменные. Начальное содержимое данных ячеек может быть произвольным, т.к. они нужны для того, чтобы определить переменные, от которых будет зависеть целевая функция (ЦФ).
Таблицы подстановки с одной переменной.При создании ТП с одной переменной необходимо серию вводимых значений располагать либо в столбце (ориентированные по столбцу), либо в строке (ориентированные по строке). Формула (ЦФ), используемая в таблицах подстановки с одной переменной, должна ссылаться на ячейку ввода. Выполните действия:
· в отдельный столбец, либо в отдельную строку введите список значений, которые следует подставлять в ячейку подстановки (ввода);
· если значения в таблице подстановки ориентированы по столбцу, введите ЦФ в ячейку, расположенную на одну строку выше и на одну ячейку правее первого значения;
· если значения в таблице подстановки ориентированы по строке, введите ЦФ в ячейку, расположенную на один столбец левее и на одну строку ниже первого значения;
· выделите диапазон ячеек, содержащий формулы и значения подстановки;
· в меню ДАННЫЕ выберите команду Таблица подстановки;
· если значения в таблице расположены по столбцам, введите в поле Подставлять значения по строкам в;
· если значения в таблице расположены по строкам, введите ссылку на ячейку в поле Подставлять значения по столбцам в;
- нажмите кнопку OK.
Таблицы подстановки с двумя переменными используют формулу с двумя наборами значений. ЦФ должна ссылаться на две различные ячейки подстановки (ввода). Выполните действия:
· постройте таблицу подстановки для двух переменных. Серии значений переменных должны располагаться в левой колонке и верхней строке. Во главу угла таблицы подстановки – в пустую ячейку - введите ЦФ;
· выделите диапазон ячеек, содержащий ЦФ и оба набора данных подстановки;
· в меню ДАННЫЕ выберите команду Таблица подстановки;
· в поле Подставлять значения по столбцам ввведите ссылку на ячейку ввода для значений подстановки в строке;
· в поле Подставлять значения по строкам в введите ссылку на ячейку ввода для значений подстановки в столбце;
· нажмите кнопку OK.
Методика работы с подбором параметра
Средство Подбор параметра предназначено для вычисления входных значений, влияющих на результат вычисления по заданной формуле. Достаточно указать формулу, ее значение и изменяемую ячейку, влияющую на эту формулу. Excel ищет решение путем последовательных итераций. Изменяемая ячейка должна содержать значение (не формулу) и должна влиять на результат, который требуется получить. Чтобы воспользоваться средством подбора параметра следует:
1. Выделить ячейку с целевой формулой, которую хотите подогнать под заданное значение.
2. Выполните команду СЕРВИС – ПОДБОР ПАРАМЕТРА. Появится диалоговое окно подбора параметра. В поле Установить в ячейке уже будет находиться ссылка на выделенную ячейку.
3. В поле Значение ввести величину, которую необходимо получить.
4. В поле Изменяя значение ячейки ввести ссылку на исходную ячейку. Эта ячейка должна влиять на, выбранную на шаге 1 целевую формулу. Средство Подбор параметра начнет итерационный процесс поиска решения.
5. Если необходимо приостановить или отменить затянувшийся процесс подбора параметра, надо нажать в открывшемся диалоговом окне результата подбора параметра кнопку Пауза или Отмена. После нажатия кнопки Пауза можно выполнять процесс поиска решения по шагам. Для этого используется кнопка Шаг. Для возобновления автоматического поиска нажать кнопку Продолжить.
6. После того, как решение найдено, надо нажать кнопку ОК или кнопку Отмена, чтобы сохранить прежние величины.
Варианты заданий 1-30
Вариант 1
Найти чистый дисконтированный доход проекта, требующего стартовых инвестиций в объеме 100 тыс. руб., денежный поток которого задан рис. 1, по ставке дисконтирования 10% годовых.
Рис. 1
Проанализируйте с помощью таблицы подстановки, как изменяется результат при изменении годовой процентной ставки в диапазоне 6 – 12 (%) с шагом изменения в 1 %.
Определите, при какой стартовой инвестиции чистый доход проекта составит 10 000 р.?
Вариант 2
Пять платежей по три рубля каждый нужно внести по схеме пренумерандо. Получатель аннуитета использует эти средства с доходностью 8% за период между платежами.
Какова будущая стоимость этого срочного аннуитета (срок n = 5) в конце пятого периода в результате начисления процентов на все поступившие платежи?
Проанализируйте с помощью таблицы подстановки, как изменяется результат при изменении годовой процентной ставки в диапазоне 6 – 12 (%) с шагом изменения в 1 % и периода от 4 до 10 лет с шагом 1 год.
Определите, при какой процентной ставке будущая стоимость срочного аннуитета составит 20 р.?
Вариант 3
Клиент банка с целью накопления капитала производит ежемесячные взносы на счет в размере 1 000 р. по ставке 9% годовых. Какова будет сумма вклада через два года?
Проанализируйте с помощью таблицы подстановки, как изменение ежемесячного взноса отражается на накоплении суммы.
Каким должен быть размер периодического платежа, чтобы внесение пяти одинаковых платежей такого размера по схеме постнумерандо позволило погасить долг 30 тыс. руб. по ставке 8% за период?
Вариант 4
Рассчитать, какая сумма окажется на счете, если 27 000 руб. положены на 33 года под 13.5% годовых. Проценты начисляются каждые полгода.
Проанализируйте результат с помощью таблицы подстановки при изменении суммы вклада от 15 000 р. до 42 000 р. с шагом 3 000 р.
Через сколько лет сумма вклада станет равной 2 000 000 р.?
Вариант 5
Рассчитать текущую стоимость вклада, который через три года составит 15 млн. руб. при начислении 20% в год.
Проанализируйте с помощью таблицы подстановки, как происходит изменение результата при различной процентной ставке от 12% до 20% с шагом 0,5%.
При какой процентной ставке результат достигнет 9 000 000,00 р.?
Вариант 6
Инвестиции в проект к концу первого года его реализации составят
10 000 руб. В последующие три года ожидаются годовые доходы по проекту 7 000 руб., -200 руб., 6 800 руб. Издержки привлечения капитала 10%.
Рассчитать чистую текущую стоимость проекта.
Проанализируйте с помощью таблицы подстановки, что происходит с результатом при изменении процентной ставки в диапазоне от 9 до 19% (шаг – 1%)?
Какой доход в четвертом периоде обеспечит получение результата в 1 200 р.?
Вариант 7
В течение четырех лет инвестируются средства в проект по следующему варианту: в начале каждого года под 26% годовых вносится 300 000 руб.
Определить, сколько денег окажется на счете в конце 4-го года.
Проанализируйте результаты с помощью таблицы подстановки при изменении годовой процентной ставки в интервале от 12% до 26% с шагом 1%.
При какой процентной ставке тот же результат будет достигнут за 5 лет?
Вариант 8
В течение 4-х лет инвестируются средства в проект по следующему варианту: в конце каждого года под 18% годовых вносится 300 000 руб.
Определить, сколько денег окажется на счете в конце 4-го года.
Проанализируйте результаты с помощью таблицы подстановки при изменении процентной ставки в интервале от 9% до 18% с шагом 1%. и периода от 2 до 7 лет.
Определить, какова должна быть сумма ежегодных взносов, чтобы результат достиг 1 000 000 р.?
Вариант 9
Вариант 9
Определитьчистую приведенную стоимость проекта:
| % ставка | 18% | |||||||||
| Год | - | |||||||||
| Денежный поток | -20000 |
Проанализируйте результат с помощью таблицы подстановки при изменении процентной ставки в интервале от 9% до 18% с шагом 1%.
Какой должен быть первоначальный вклад, чтобы результат был равен 1 000 р.?
Вариант 10
Определитьчистую приведенную стоимость проекта:
| % ставка | 18% | |||||||||
| Год | ||||||||||
| Денежный поток | -20000 |
Проанализируйте результат с помощью таблицы подстановки при изменении процентной ставки в интервале от 9% до 18% с шагом 1%.
Какой должен быть первоначальный взнос, чтобы результат был равен 1 000 р.?
Вариант 11
Оцените невыполненный баланс на ипотечной ссуде, которая заряжает 8 % годовых, требует двухсот платежей по 1000 $ в месяц, и также требует 10 000$ погашение кредита один раз полной суммой.
Проанализируйте результат с помощью таблицы подстановки при изменении процентной ставки в интервале от 3% до 12% с шагом 1%.
Сколько должно быть платежей, чтобы обеспечить погашение кредита в сумме 120 000 $. на тех же условиях?
Вариант 12
Вычислите будущую стоимость платежей по 3 р. по сложной ставке 8% годовых, производимых пренумерандо в конце срока в 5 периодов.
Проанализируйте с помощью таблицы подстановки, как изменяется результат при изменении годовой процентной ставки в диапазоне 6 – 12 (%) с шагом изменения в 1 % и периода от 5 до 10 лет с шагом 1.
Какой должен быть первоначальный взнос, чтобы результат был равен 100 р.?
Вариант 13
Какую сумму достаточно вложить на 5 периодов с начислением 8% сложных, чтобы в конце срока снять 19,01 руб.?
Проанализируйте результаты с помощью таблицы подстановки при изменении процентной ставки в интервале от 9% до 15% с шагом 1%. и периода от 3 до 10 лет.
На какой период, необходимо вложить сумму 5 руб., чтобы в конце срока получить 50 руб.?
Вариант 14
Какую сумму достаточно вложить на срок 10 лет единовременно, чтобы при доходности 15 процентов и при ежемесячном начислении сложных процентов накопить миллион рублей?
Проанализируйте с помощью таблицы подстановки, как происходит изменение результата при различной процентной ставке от 10% до 20% с шагом 1%.
При какой процентной ставке результат достигнет 500 000,00 р.?
Вариант 15
Необходимо рассчитать будущую сумму вклада в сумме 1 000 руб., внесенного на 10 лет с ежегодным начислением 10% .
Проанализируйте результаты с помощью таблицы подстановки при изменении процентной ставки в интервале от 5% до 15% с шагом 1%. и периода от 8 до 15 лет.
Определить, какова должна быть сумма ежегодных взносов, чтобы результат достиг 10 000 р.?
Вариант 16
Необходимо рассчитать будущую сумму вклада в сумме 1 000 руб., внесенного на 10 лет под 10% годовых но с ежегодным внесением 1 000 руб.
Проанализируйте результаты с помощью таблицы подстановки при изменении процентной ставки в интервале от 5% до 12% с шагом 1%. и периода от 5до 15 лет.
Определить, какова должна процентная ставка, чтобы результат достиг 35 000 р.?
Вариант 17
Необходимо рассчитать будущую сумму вклада при ежемесячном внесении 200 руб. в течение 8 лет с ежегодным начислением 6%. Начальный вклад равен 0.
Проанализируйте результат с помощью таблицы подстановки при изменении суммы вклада от 200 р. до 1000 р. с шагом 100 р.
Через сколько лет сумма вклада станет равной 50 000 р.?
Вариант 18
Требуется рассчитать, какую сумму можно занять на 8 лет под 6% годовых, если есть возможность выплачивать ежемесячно по 200 руб.?
Проанализируйте результаты с помощью таблицы подстановки при изменении процентной ставки ежемесячных выплат (шаг изменения параметров выбрать самостоятельно).
Какие суммы выплат обеспечит заем в 30 000 р. на тех же условия?
Вариант 19
Необходимо рассчитать величину вложения под 10 % годовых, которое будет ежегодно в течение 10 лет приносить доход 1 000 руб.
Проанализируйте результаты с помощью таблицы подстановки при изменении процентной ставки и числа лет (шаг изменения параметров выбрать самостоятельно).
Какой доход обеспечит вклад в сумме 10 000р.?
Вариант 20
Необходимо рассчитать величину вложения под 10 % годовых, которое через 10 лет принесет доход 10 000 руб.
Проанализируйте результаты с помощью таблицы подстановки при изменении процентной ставки и числа лет (шаг изменения параметров выбрать самостоятельно)
Какой доход обеспечит вклад в сумме 4 000р.?
Вариант 21
Найти чистую приведенную стоимость инвестиции. Данные приведены в таблице.
| 12% | Годовая ставка дисконтирования. Она может представлять собой темп инфляции или процентную ставку по конкурирующим инвестициям. |
| -25000 | 30 декабря 2007 |
| 1января 2008 | |
| 3 марта 2008 | |
| 12 апреля 2008 | |
| 12 000 | 30 декабря 2008 |
| 14 500 | 25 января 2009 |
Проанализируйте результаты с помощью таблицы подстановки при различных значениях годовой ставки дисконтирования.
Какой должен быть доход за последний период, чтобы полностью компенсировать начальные затраты?
Вариант 22
Найти чистую приведенную стоимость инвестиции с потерей 9000 на шестом году. Данные приведены в таблице.
| 10% | Годовая ставка дисконтирования. Она может представлять собой темп инфляции или процентную ставку по конкурирующим инвестициям. |
| -5 000 | Начальные затраты на инвестиции |
| 5 000 | Доход за первый год |
| 1 000 | Доход за второй год |
| 1 000 | Доход за третий год |
| 12 000 | Доход за четвертый год |
| 14 500 | Доход за пятый год |
Проанализируйте результаты с помощью таблицы подстановки при изменении процентной ставки (шаг изменения выбрать самостоятельно).
Определите возможную сумму потерь в шестой период, которая сможет обеспечить компенсацию начальных затрат.
Вариант 23
Банк выплачивает по сберегательным счетам 12% годовых, исчисляя сложные проценты каждый квартал. Вы открыли счет на 5 000 рублей. Сколько денег будет на вашем счету через 2 года?
Проанализируйте результаты с помощью таблицы подстановки при изменении процентной ставки и исходной суммы вклада (шаг изменения параметров выбрать самостоятельно).
Какая сумма вклада обеспечит результат 10 000 р. На тех же условиях?
Вариант 24
Определить сумму, которую сегодня следует положить в банк, чтобы через пять лет на счету было 100 000 рублей, и при этом ежегодно снимать с вклада 3 000 рублей. Процентная ставка банка 15% годовых.
Проанализируйте результаты с помощью таблицы подстановки при изменении процентной ставки и числа лет.
Если сумма вклада составит 60 000 рублей, сколько денег вы сможете получить на тех же условиях?
Вариант 25
Определите сумму вклада, чтобы через 3 года вы смогли получить в банке 50 000 рублей, ежегодно снимая 6000 рублей, процентная ставка банка 12% годовых.
Проанализируйте результаты с помощью таблицы подстановки при изменении ежегодно снимаемой сумы и числа лет (шаг изменения параметров выбрать самостоятельно).
Если сумма вклада составит 60 000 рублей, сколько денег вы сможете получить на тех же условиях?
Вариант 26
Илья Петрович в конце каждого месяца переводит 1,2 т.р. на счет в банк, начисляющий ежемесячно сложные проценты по номинальной ставке 6,5% годовых. Какая сумма накопится на счете за 3 года, при сохранении на это время всех указанных условий без изменения?
Проанализируйте результаты с помощью таблицы подстановки при изменении процентной ставки и числа лет (шаг изменения параметров выбрать самостоятельно).
Через сколько лет на счете накопится сумма 15 т.р. при сохранении ставки 6,5%?
Вариант 27
Инвестор вносит сумму 5 млн руб на счет с начислением сложных процентов по ставке 8% за период, и затем дважды в конце каждого расчетного периода добавляет на счет столько же.
Какую сумму удастся таким образом нарастить за 3 периода?
Проанализируйте результаты с помощью таблицы подстановки при изменении процентной ставки и числа лет.
При какой процентной ставке сумма вклада в конце расчетного периода достигнет 15,5 млн. руб.?
Вариант 28
Определить будущую величину вклада в 10 000,00 руб., помещенного в банк на 5 лет под 5% годовых, если начисление процентов осуществляется раз в году.
Проанализируйте результаты с помощью таблицы подстановки при изменении процентной ставки и числа лет.
При какой процентной ставке будущая величина вклада достигнет 15 000,00 руб., через 6 лет?
Вариант 29
Определить будущую величину вклада в 10 000,00 руб. помещенного в банк на 5 лет под 5% годовых, если начисление процентов осуществляется раз в месяц.
Проанализируйте результаты с помощью таблицы подстановки при изменении процентной ставки и числа лет.
При какой процентной ставке будущая величина вклада достигнет 15 000,00 руб., через 6 лет
Вариант 30
Чему равна текущая стоимость денежной суммы 200 т. руб., ожидаемой через три года при дисконтировании по ставке 9,5% годовых?
Проанализируйте результаты с помощью таблицы подстановки при изменении процентной ставки и числа лет.
Через сколько лет текущая стоимость вклада станет равной 150 т.руб при ставке дисконтирования 7%?