Пример 8.3.

Предположим, есть два варианта инвестирования средств в течение 4 лет: в начале каждого года под 26% годовых или в конце каждого года под 38% годовых. Ежегодно вносится 300 тыс.руб. Определить, сколько денег окажется на счете в конце 4-го года для каждого варианта.

Исходные данные и аргументы

Расчетные формулы и результаты

Ответ: На счете окажется 2210, 53 тыс. р. для 1-го варианта и 2073, 74 тыс. р. для 2-го варианта.

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

Установив курсор в ячейку (C11), содержащую формулу с финансовой функцией (Цена), выполнить команду Данные ® Работа с данными®Анализ что-если®Подбор параметра. В появляющееся диалоговое окно (рис. 8.1) задается требуемое значение функции (желаемая или заданная ЦЕНА), а в поле “Изменяя значение ячейки” указывается адрес подбираемого аргумента (один из параметров исходных данных, например, по адресу $C$5 - СТАВКА).

Рис. 8.1. Диалоговое окно Подбор параметра

В Excel можно проводить исследование влияния некоторых исходных данных, принимающих различные значения, на результирующую функцию, т.е. проводить вариантныефинансовые расчеты. Это делается с помощью Диспетчера сценариев. Сценарий – именованная совокупность значений изменяемых ячеек, в которые вводятся различные значения аргументов. При работе со сценариями следует присвоить имена ячейкам с исходными данными, которые будут “варьироваться”. Например, для аргументов финансовой функции ЦЕНА ячейкам с исходными данными присвойте имена: $B$5 – Ставка, $B$6 – Доход, $B$8 – Частота, а ячейке-результату $B$10 – Цена.

Установите курсор на любую ячейку рабочего листа.

Команда Данные ® Работа с данными®Анализ что-если®® Диспедчер сценариев вызывает диалоговое окно Диспетчера сценариев для создания, редактирования, объединения, удаления и просмотра созданных сценариев расчета.

Новый сценарий создается кнопкой Добавить, появляется окно Добавление сценария (рис. 8.2), в котором название сценария задается в соответству­ющем поле, например 1. Поле Изменяемые ячейки заполняется с использованием клавиши CTRL, выделяя на рабочем листе ячейки, значения которых будут изменяться.

Рис. 8.2. Создание нового сценария

Так готовятся аргументы, и после нажатия кнопки ОК, выполняются вариантные расчеты для различных вводимых значений (рис. 8.3). Кнопка Добавить переводит на предыдущее окно для создания нового сценария, а кнопка ОК – на окно, в котором можно увидеть список всех сценариев и перейти на создание отчета.

Рис. 8.3. Задание значений изменяемым ячейкам сценария

Эти действия повторяются для каждого нового сценария.

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

В экономике и финансах всегда стоят задачи оптимального планирования штата сотрудников, фонда зарплаты, плана производства и рекламной деятельности. При этом всегда стоит проблема: как максимально удовлетворить потребности, соизмеряясь с возможностями. Excel решает задачи оптимизационного моделирования, требующего большого объема вычислений, используя мощный инструмент – Поиск решения (Данные – Анализ – Поиск решения). Если надстройка Поиск решения не отображается, то используя кнопку Офис откройте Параметры Excel и в Надстройках из списка Неактивные надстройки Excel выберите Поиск решения и нажмите кнопку Перейти.

Принятие оптимальных решений базируется на “трех китах”:

v Математическая модель (вводятся и описываются переменные и функции):

Ø целевая функция или критерий оптимизации показывает, в каком смысле решение должно быть оптимальным (максимизация, минимизация или назначение заданного значения);

Ø ограничения устанавливают зависимость между переменными (могут быть односторонними и двусторонними);

Ø граничные условия показывают, в каких пределах могут быть значения искомых переменных в оптимальном решении;

v Решение задачи на компьютере (через механизм Поиск решения);

v Подготовка исходных данных (их достоверность).