Финансовые функции

Рассмотрим пример использования финансовых функций в Excel.

Пример. Расчет выплат по кредиту. Определить сумму месячного платежа при получении ссуды 10 000 000 руб. при 8% годовых и сроке возврата 2 года.

Для решения можно воспользоваться функцией ПЛТ (находится в разделе Финансовые), возвращает величину выплаты за один период годовой ренты.

Аргументы функции:

§ норма за период (месячный процент);

§ общее число периодов;

§ общая сумма всех платежей

Порядок действий и вид экрана приведены ниже:

§ В ячейке А7 введем текст : Процентная ставка, в ячейке В7 – 8%;

§ В ячейке А8 введем текст : Срок выплаты, в ячейке В8 – 24;

§ В ячейке А9 введем текст : Размер ссуды, в ячейке В9 – 10 000 000;

§ После этого в ячейку А10 введем текст : Сумма платежа, а в ячейку В10 – формулу: =ПЛТ(B7/12;B8;B9)

Получается, что при получении ссуды 10 000 000 руб. при 8% годовых и сроке возврата 2 года ежемесячно необходимо платить по 452 272,91 руб.

Финансовый анализ

В Microsoft Excel имеется также несколько инструментов, которые помогут вам спланировать ведение бизнеса. К примеру, сколько чашек кофе по    $ 1,75 нужно продать, чтобы получить доход $ 30 000? Что случится с результатом, если уменьшить цену на кофе с молоком, увеличив при этом расходы на рекламу?

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

Команда Подбор параметра

Для использования команды Подбор параметра лист должен содержать:

- формулу, для которой вычисляется подбор параметра;

- пустую ячейку, в которую будет помещён параметр;

- остальные значения, требуемые для получения результата при помощи формулы.

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

После запуска команды Подбор параметра Excel изменяет значение в одной конкретной ячейке до тех пор, пока формула, зависимая от этой ячейки, не возвращает нужный результат. Этот процесс называется итерацией. Если ответ с точностью до 0,01 не будет найден в течение ста итераций, процесс прекращается. Для изменения этого значения выберите команду Сервис | Параметры и на вкладке Вычисления введите новое значение в поле Предельное число итераций. Так как вычисления происходят довольно быстро, команда Подбор параметра может сэкономить значительное количество времени и усилий.

Рассмотрим пример использования этой команды.

1. Создайте лист, содержащий формулу, пустую ячейку, на которую есть ссылка в этой формуле, и прочие данные, необходимые для вычислений. Пример такого листа показан на рис. 4. Нам нужно вычислить, сколько чашек кофе по цене $ 1,75 нужно продать, чтобы получить $ 30 000.

Рис. 4. Пример данных, необходимых для использования

команды Подбор параметра

2. Выделите ячейку, содержащую формулу. Выберите команду Сервис | Подбор параметра, чтобы открыть диалоговое окно Подбор параметра. В поле Установить в ячейке появилось имя выделенной ячейки. При этом данная ячейка на листе окружена мерцающей рамкой. Заполните остальные поля в этом окне.

3. Введите в текстовое поле Значение итоговое число. В нашем случае это будет 30 000, так как именно этот доход планируется получить. В текстовое поле Изменяя значение ячейки поместите адрес ячейки, в которую нужно поместить ответ. В нашем случае это ячейка D6 (см. рис. 5).

Рис. 5. Результат выделения ячейки, значение которой вычисляется

5. Нажмите кнопку ОК, чтобы завершить процесс поиска решения. Появится диалоговое окно Результат подбора параметра с сообщением о завершении итераций. При этом в ячейке D 6 появится результат вычислений      (рис. 6).

Рис. 6. Результат завершения итераций

6. Нажмите кнопку ОК, чтобы закрыть диалоговое окно Результат подбора параметра.

Примечание. В процессе вычислений можно нажать кнопку Пауза, чтобы остановить процесс, или кнопку Шаг, чтобы выполнить итерацию пошагово.

Надстройка Поиск решения

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

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

В рассматриваемом примере в кафе продаются три сорта кофе: обычный, кофе со сливками и кофе с шоколадом. На данный момент цена обычного кофе $ 1,25, кофе со сливками – $ 2,00, а кофе с шоколадом – $ 2,25, но мы не знаем, каким может быть потенциальный доход и кофе какого сорта нужно продавать больше всего. Хотя кофе со сливками и с шоколадом имеют более высокую цену, входящие в них ингредиенты стоят дороже и, кроме того, на их приготовление тратится больше времени.

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

Постановка задачи

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

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

Рис. 7. Данные для использования надстройки Поиск решения

Ячейка G 4 является целевой и содержит формулу для вычисления суммарного дохода от продажи всех трёх видов кофе.

Ячейки D 5, D 9 и D 13 являются изменяемыми. Именно их значение будет вычисляться в процессе работы надстройки Поиск решения.

В правом нижнем углу экрана находится список ограничений, с помощью которых мы хотим сузить множество значений, используемых в модели. Величина ограничений определяется из практических соображений. В данном случае вы не в состоянии приготовлять более 500 чашек кофе в неделю. Из-за ограничения расходов на шоколад предельное число чашек кофе с шоколадом, которое можно приготовить, составляет 125. Кроме того, из-за ограниченного срока хранения молока невозможно изготовлять более 350 чашек кофе с добавками.