4.2 Современная ценность финансовой ренты. Функции пз, норма

Фирма посылает служащих на учебу и желает положить в банк, который выплачивает 10% годовых такую сумму, чтобы иметь возможность снимать с этого счета ежегодно по 10000 грн. для выплаты стипендии. Вклад должен быть исчерпан к концу 5 лет (деньги снимаются в конце каждого года). Какую сумму фирма должна положить в банк? (Ответ: 37907,87 грн.)

Решение:

Воспользуемся функцией ПЗ, которая возвращает текущий объем вклада. Текущий объем - это общая сумма, которую составят будущие платежи.

Функция ПЗ имеет следующий синтаксис:

ПЗ (ставка;кпер;выплата;бз;тип),

где Ставка -- это процентная ставка за период.

Кпер -- это общее число периодов выплат годовой ренты.

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

Бз -- требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (будущая стоимость займа, например, равна 0).

Тип -- это число 0 или 1, обозначающее, когда должна производиться выплата (в конце или в начале периода).

Есла подставить данные значения в функцию, то получим:

=ПЗ(10%;5;-10000) = 37907,87 грн.

то есть фирма должна положить в банк 37907,87 грн.

4.3 Амортизация

Фирма в течение пяти лет имела ежегодную валовую выручку 350000 грн. Составьте таблицу размеров налога, выплачиваемого этой фирмой, по годам, если амортизационные отчисления рассчитываются равномерно. Ставка налога 15% при величине дохода до 200000 грн. и 30% при доходе > 200000 грн.

Решение:

Воспользуемся функцией АМР, которая возвращает величину непосредственной амортизации имущества за один период.

Синтаксис:

АМР(стоимость;остаток;период),

где Нач_стоимость - это начальная стоимость имущества.

Ост_стоимость - это стоимость в конце периода амортизации (иногда называется остаточной стоимостью имущества).

Время_эксплуатации - это количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации).

Величина выручки

Ставка

Размер налогообложения

Стоимость в конце периода амортизации

1 год

350000

30%

105000

49000

2 год

350000-49000 = 301000

30%

90300

42140

3 год

301000-42140 = 258860

30%

77658

36240

4 год

258860-36240 = 222620

30%

66768

28992,4

5 год

222620 -28992,4 = 193627,6

15%

29044,14

25371,92

7. Функции «подбор параметра» и «поиск решения». Использование при решении экономических задач

2 Практическая часть

2.1 Пример решения задач с использованием функции “подбор параметра”

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

Рисунок 2 - Подбор параметра

Если в качестве начального значения в данном примере указать -3, тогда будет найдено второе решение уравнения: -0,5.

2.2 Задача: Анализ суммы выплат по вкладу

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

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

1. Нужно создать новой лист с именем Вклад.

2. В ячейку В4 ввести текст Размер вклада, а в С4 его значение 150000р.

3. В ячейку В6 ввести текст Срок вклада, а в С6 его значение 20.

4. В ячейку В8 ввести текст Процентная ставка, а в С8 его значение 5%.

5. В ячейку В10 ввести текст Коэффициент наращения, а в С10 формулу его вычисления =(1+С8)^С6.

6. В ячейку В13 ввести текст Сумма выплат, а в С13 формулу его вычисления =С10*С4.

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

Рисунок 3 - Анализ суммы выплат по вкладу

Используя Подбор параметра можно упростить процесс получения требуемого результата:

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

В поле Значение нужно ввести целевое значение 500 000, а в поле Изменяя значение ячейки ссылку на ячейку С4 и нажать ОК.

Рисунок 4 - Окно с результатами расчета

Появится окно с результатами расчета, которые после нажатия кнопки ОК будут внесены в таблицу. Рисунок 4. Как видно для получения суммы выплат в 500 000 руб. при 5% годовых за 20 лет требуется положить 188445 руб. Результат выполнения задачи показан на рисунке 5

Команда «Поиск решения»

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

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

В том случае, когда оптимизационная задача содержит несколько переменных величин, для анализа сценария необходимо воспользоваться надстройкой Поиск решения. “Поиск решения” позволяет использовать одновременно большое количество изменяемых ячеек (до 200) и задавать ограничения для изменяемых ячеек.

Общие свойства, которые характерны для задач, решаемых с помощью надстройки Поиск решения:

· Существует единственная целевая ячейка, содержащая формулу, значение которой должно быть сделано максимальным, минимальным или же равным, какому-то конкретному значению.

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

· Может быть задано некоторое количество ограничений -- условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек.

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

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

После того, как задача оптимизации будет подготовлена на листе, можно приступать к работе.

1. Выделите на листе целевую ячейку, в которую введена формула.

2. Выполните команду Сервис/Поиск решения. Открывается окно диалога Поиск решения. Поскольку была выделена ячейка, в текстовом поле «Установить целевую ячейку» появится правильная ссылка на ячейку. В группе «Равной» переключатель по умолчанию устанавливается в положение «Максимальному значению».

3. Перейдите к полю "Изменяя ячейки" и введите переменные ячейки листа

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

5. Когда оптимизационная задача будет готова к выполнению, можно нажать кнопку Выполнить для получения ответа. Появится окно диалога с описанием результатов процесса оптимизации.

6. Чтобы отобразить найденное решение в ячейках листа, установите переключатель "Сохранить найденное решение" и нажмите кнопку ОК. Найденная максимальная величина помещается в целевую ячейку, а переменные ячейки заполняются оптимальными значениями переменных, которые удовлетворяют установленным ограничениям.

3.3 Диспетчер сценариев «что-если»

При работе с командами Подбор параметра и Поиск решения не существует удобного способа сравнения результатов вычислений - при каждом изменении данных предыдущее значение пропадает.

Чтобы устранить эти ограничения, разработчики Excel создали Диспетчер сценариев, помогающий работать с несколькими моделями «что - если». Командой Сервис/Сценарии можно создавать новые и просматривать существующие сценарии для решения задач, и отображать консолидированные отчеты.

Создание сценария

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

Создание сценариев происходит следующим образом:

· Выполните команду Сервис/Сценарии. Открывается изображение окна диалога Диспетчер сценариев.

· Нажмите кнопку Добавить, чтобы создать первый сценарий. Откроется окно диалога Добавление сценария.

· Введите Лучший вариант (или любое другое имя) в поле Название сценария, затем с помощью окон диалога введите изменяемые ячейки. Когда этот сценарий будет готов, введите следующий.

· Нажмите кнопку Добавить, чтобы создать второй сценарий. Введите название Худший вариант. После завершения создания двух сценарием можно приступить к просмотру результатов.

· Закройте окно диалога Диспетчер сценариев кнопкой Закрыть.

Просмотр сценария

Excel сохраняет сценарии вместе с листом текущей книги, и просмотр их командой Сервис /Сценарии возможен только при открытии данного листа. Просмотр сценария выполняется следующим образом:

· Выполните команду Сервис/Сценарии. Открывается окно диалога:

· Выберите из списка сценарий для просмотра.

· Нажмите кнопку Вывести. Excel заменяет содержимое ячеек листа значениями из сценария и отображает результаты на листе.

· Выберите из списка другие сценарии и воспользуйтесь кнопкой Вывести для сравнения результатов моделей «что - если». После завершения нажмите кнопку Закрыть. Значения последнего активного сценария остаются в ячейках листа.

Создание отчетов по сценарию

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

Эту задачу можно выполнить с помощью кнопки Отчет в окне диалога Диспетчер сценариев. Созданный сводный отчет будет автоматически отформатирован и скопирован на новый лист текущей книги.

Создание отчета по сценарию происходит следующим образом:

· Выполните команду Сервис/Сценарии. Откроется окно диалога Диспетчер сценариев.

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