Сервисные функции в Microsoft Excel

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

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

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

Пример. Клиент желает открыть валютный счет в коммерческом банке под 9% процентов годовых и накопить на этом счете за пять лет сумму в $25 000. Клиент согласен производить ежегодные отчисления на свой счет в банке. Необходимо узнать размер этих ежегодных отчислений.

Для решения данной задачи необходимо вначале использовать функцию БЗ. Напомним, что функция БЗ находит будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки. Эта функция имеет три обязательных параметра - процентная ставка, общее число периодов выплат, и, наконец, размер одиночной выплаты. Так как нам неизвестно значение ежегодной выплаты, то примем это значение равное нулю. Введя исходные значения в таблицу, и произведя расчеты, мы получим, что размер вклада равен нулю:

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

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

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

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

Пример. Фирма имеет годовой доход 85 000 руб. Фирма желает взять ссуду в банке в размере 125 000 руб. под 9,2% годовых на 30 лет. Определить размер ежемесячных выплат и общую сумму выплат. После этого проанализировать, как влияет изменение процентной ставки и срока кредита на ежемесячные выплаты?

После ввода исходных значений и формул для расчета ежемесячной выплаты по ссуде (ячейка В7) и общей суммы выплат (ячейка В9), получим следующую таблицу:

= ППЛАТ(В5/12;В6*12;-В4). Формула для расчета общей суммы выплаты (ячейка В9): = В6*12*В7

Чтобы произвести анализ "что-если", будем изменять значения в ячейках В4, В5 и В6. При этом проанализируем, что произойдет, если процентная ставка уменьшится, или кредит будет взят на меньшее число лет или составит меньшую сумму? Как это повлияет на ежемесячные выплаты и общую сумму?

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

Создадим три сценария. Для создания сценария выберите, какие ячейки нужно изменять (В4, В5 и В6). Выберите команду Сервис - Сценарии, чтобы увидеть окно диалога Диспетчер
Сценариев. В дальнейшем здесь появится список сценариев, из которого можно выбирать вариант для конкретной задачи. Пока же он пуст, так как не создано ни одного сценария.

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

Затем можно подумать о защите сценариев от изменений. Флажок Запретить изменения означает, что никто, кроме вас, не сможет изменить сценарий. Скрыть означает, что он не появится в списке сценариев. Чтобы полностью исключить постороннее вмешательство, следует установить защиту и на сам рабочий лист.

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

Для создания следующего сценария, просто щелкните на кнопке Добавить, чтобы перейти к созданию следующего сценария. Появится окно диалога Добавление сценария, и вы сможете задать новое имя, указать другие изменяемые ячейки (если это нужно) и защитить сценарий от изменений. Введите название Минимум и щелкните на ОК. В окне диалога Значения ячеексценария нужно ввести новые значения в изменяющиеся ячейки, чтобы создать второй сценарий:

ячейка В4 - 75000

ячейка В5 - 0,05

ячейка В6 - 35

Добавление третьего сценария происходит аналогично:

название – Желаемые значения

ячейка В4 - 80000

ячейка В5 - 0,006

ячейка В6 - 25

После последнего набора чисел нужно щелкнуть на кнопке ОК, чтобы вернуть на экран окно Диспетчер Сценариев, на этот раз содержащее список из трех созданных сценариев.

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

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

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

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

1. Поиск решения позволяет использовать одновременно большое количество (в общей сложности до 200) изменяемых ячеек.

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

3. Поиск решения вычисляет оптимальное решение, т.е. наилучшее из возможных.

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

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

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

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

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

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

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

Задача о выборе оптимального меню. Имеется набор некоторых продуктов, обладающих некоторой калорийностью, а также известно количество белков, жиров и углеводов в каждом из этих продуктов и их стоимость. Требуется составить меню, удовлетворяющее требованиям калорийности и сбалансированности питательных продуктов, и при этом минимизирующее суммарную стоимость.

Задача о назначениях. Имеется несколько должностей и соответствующее количество претендентов на эти должности. Назначение i-того претендента на j-тую должность связано с затратами C[ij]. Требуется распределить претендентов по должностям так, чтобы суммарные затраты были бы минимальны.

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

Пример. В качестве примера рассмотрим уже знакомую задачу о ссуде. Исходные значения остаются без изменения, но вводится дополнительное условие - фирма может выплачивать только 850 руб. ежемесячно за взятую ранее ссуду

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

Замечание. В зависимости от того, как построен рабочий лист, количество значений переменных, приводящих к одному и тому же результату, может существенно увеличиться. В большинстве случаев нам интересен не сам результат, а способ его достижения. Excel наверняка сможет найти решение для ежемесячной выплаты 850р. при процентной ставке 1%. Но кто даст такой кредит?

Итак, для поиска решений, имеющих определенный смысл, необходимо задать определенные ограничения, которые будут учитываться при работе программы. В нашем примере используются следующие ограничения:

· Сумма ссуды не должна быть менее 80000р., но и не может более чем в 4 раза превышать доход

· Процентная ставка не превышает 10% (но и не меньше 6%)

· Срок возврата не превышает 30 лет

· Все эти условия будут помещены в поле Ограничения.

Ограничения записываются в виде формулы, возвращающей логическое значение ИСТИНА или ЛОЖЬ и проверяющей за раз только одно значение. Ограничение можно задавать как числом, так и ссылкой.

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

Укажите, хотите ли вы сохранить найденное решение или восстановить исходные значения. Если щелкнуть по кнопке Сохранить сценарий, то можно ввести его название, и использовать при помощи Диспетчера сценариев значения в изменяемых ячейках. Также можно создать для представления результатов отчета трех типов:

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

· Отчет Устойчивость содержит сведения о чувствительности решения к малым изменениям в формулах модели или в ограничениях.

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

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

КОМПЬЮТЕРНЫЕ СЕТИ